Datafile resize

Hi all,

In my ProductionDB i have truncated table which contails 4.7GB.The table present in ISHDATA tablespace with datafiles
'/mnt/db/oracle/oradata/ISPRO/ishdata01.dbf' upto 10 files are there in Ishdata tablespace.

After truncating i tried to resize all the 10 datafiles, to show free space at OS level.(MD wants to see at OS level.)

So i tried as ..
SQL> alter database datafile '/mnt/db/oracle/oradata/ISPRO20/ishdata01.dbf' resize 1900m;
alter database datafile '/mnt/db/oracle/oradata/ISPRO20/ishdata01.dbf' resize 1900m
*
ERROR at line 1:
ORA-03297: file contains used data beyond requested RESIZE value
...

SQL> alter database datafile '/mnt/db/oracle/oradata/ISPRO20/ishdata10.dbf' resize 4000m;
alter database datafile '/mnt/db/oracle/oradata/ISPRO20/ishdata10.dbf' resize 4000m
*
ERROR at line 1:
ORA-03297: file contains used data beyond requested RESIZE value

spaces at datafiles reduces at different level ie., for one datafile 250MB released another 1800MB released.

So,please help me on this regard.

Thanks in advance.

you have other tables which are at the end of the file so the datafiles cant be resized

Query DBA_EXTENTS to find the segments at the high end of the data files.

So..,If i want to rezise i have to drop entire tablespace and recreate with new size.So i have to take backup of that tablespace(ishdata).

ok., In no way i can play in production.

Thanks to all.

Quote:

---

Originally Posted by ams-jamali

So..,If i want to rezise i have to drop entire tablespace and recreate with new size.

---

Nope.
You have to move offending objects to a different place, resize tablespace, move objects back.

PAVB, isn't it easyer, after the objects have been moved, just to drop the empty tablespace?
OK, say you do not have to drop it, but generally yes, that's the easyest way

Resizing tablespace... can't follow.

I can try as

alter tablespace [tbs] coalesce;

then i can resize datafile (am i right...)

Yes, I belive that is what PAVB means

Yes, then resize datafile. Sorry, I'd a senior moment.

It doesn't help me to release even 1GB.

I found free space using dba_data_files & dba_extents it shows only 154MB.
So instead of resizing i left that..,

Thanks to all.

Didn't you truncate a 4.7 Gig table?

In the affirmative case... did you use "drop storage" option on truncate statement?

No., I gave only
Truncate table tablename;

not used (drop storage) clause.

So what shall i do now to use that space;

drop storage option is default option for truncate...

did you aborted truncate after a while 'cause of truncate was not finishing?

Try deallocate unused space on target table...

alter table schema_name.table_name deallocate

You might want to check complete syntax.

No, i had not aborted in the middle.
It just completes its job within few seconds.

I think there is no need of using deallocate option after Truncate.

But you guys know better than me..,

So.., what else i can do further.

Look at dba_extents howmany extents the truncated table occupies and howmuch space

it nearly occupies 87 extents and only one extent has 7MB
and all the other has 1MB and some 0.628MB

That means the table occupies only around 100MB.

no bore..,

before truncating that table i checked the size using segment then i checked that result in toad too.
Same 4.7GB it shows,So i confirmed and then proceed to trucate.
now that table occupies
1886051 (present rec-count)rec within two weeks.
may be b'cos of this it might have shown this result.

"I found free space using dba_data_files & dba_extents it shows only 154MB.
So instead of resizing i left that..,"

Maybe u want to look at DBA_FREE_SPACE

bore..,
by using dba_data_files & dba_extents we can find how much one can resize the datafile(by giving block size).


So for resizing i joined these two tables and found the size(that can be resized).

dba_free_space gives you overall Freespace in that tablespace.
but from that one cannot resize the datafile because it may contain data.
Bore you know better than me.

if u need i will post that query how to find space in datafile that can be resized.without finding that how much can be resized in each datafile.If any tries to resize it shows error stating that

file contains used data beyond requested resize value..

On what version are you? If 10g you can simply do
alter table XXXXXX shrink space cascade;
this shrinks, compresses extents, drops High Water Mark. Very handy for online object rebuilds...

Quote:

---

Originally Posted by ams-jamali

Same 4.7GB it shows,So i confirmed and then proceed to trucate.
now that table occupies
1886051 (present rec-count)rec within two weeks.

---

So... table is alive and growing back to original size.

Do you expect this table to grow back to the original size?

If Yes... you don't want to resize any datafile and we can stop this insanity right now :)

If No... are you sure you want to resize a datafile to reclaim less than 5 Gig? what's the total size of your database?

* This table actually stores error message,when the user fails to register in our program.

* I don't expect to grow to its original size.

* Database size is 90GB(It is now 76% full and doing purging process other tables i had deleted gives me 70Mb,10Mb,.. free space).This was the only table released 4.7Gb space.

My boss want to see at OS level.So am tring this.,

Iam using Oracle9.2 version.

but i don't think resize datafile changed in 10G release.

Quote:

---

Originally Posted by ams-jamali

My boss want to see at OS level.So am tring this.,

---

Why is it important for your boss? Explain to him that the space has been released and that is the way the DB is supposed to work. That space is now available internally for the DB to re-use.

Ixion..,

I had already told him about that,then showed him in GUI mode too, space that was released comparing with previous report but asked said try to repli @ OS level.
If there is no other way, i can go and say to him,it can not be replicated at OS level for such a small sizes.

Does your boss comes from a SQLServer shop or is he just one of these pointy-haired bosses?

SQLServer allows to setup autoshrink=Yes which actually shrinks datafiles on-the-fly (database size becomes variable) then, reclaimed space can be seen at OS level.

If this is the case -SQLServer trained boss, tell him "welcome to the wonderful world of Oracle", if not think of it as "serving time", you are winning credits and your next life might be better :)

Quote:

---

Originally Posted by ams-jamali

Ixion..,

I had already told him about that,then showed him in GUI mode too, space that was released comparing with previous report but asked said try to repli @ OS level.
If there is no other way, i can go and say to him,it can not be replicated at OS level for such a small sizes.

---

That is the way Oracle functions. Releasing space back to the OS in general is a bad idea.

Here is why, When oracle creates a datafile it does so in one fell swoop, claiming pages from the OS as available(unless your using raw). Hopefully these pages are relatively contiguous as to avoid disk fragmentation. So releasing and acquiring disk pages by shrinking and expanding the oracle datafiles leads to disk fragmentation..

This is from the asktom web site it shows how much you could reduce your files to, again as mentioned by other ppl not always worth it as the db will grow again, maybe something to show your boss in the mean time.

I did also have a script which shows which extents are at the end a datafile, i will try to dig that out and post that up as well

Alternatively you could mail me at crichards@unicle.co.uk I can send you the script (if i can find it)

Cheers

----------- maxshrink.sql ----------------------------------

set verify off
column file_name format a50 word_wrapped
column smallest format 999,990 heading "Smallest|Size|Poss."
column currsize format 999,990 heading "Current|Size"
column savings format 999,990 heading "Poss.|Savings"
break on report
compute sum of savings on report

column value new_val blksize
select value from v$parameter where name = 'db_block_size'
/

select file_name,
ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) smallest,
ceil( blocks*&&blksize/1024/1024) currsize,
ceil( blocks*&&blksize/1024/1024) -
ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) savings
from dba_data_files a,
( select file_id, max(block_id+blocks-1) hwm
from dba_extents
group by file_id ) b
where a.file_id = b.file_id(+)
/

column cmd format a75 word_wrapped

select 'alter database datafile '''||file_name||''' resize ' ||
ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) || 'm;' cmd
from dba_data_files a,
( select file_id, max(block_id+blocks-1) hwm
from dba_extents
group by file_id ) b
where a.file_id = b.file_id(+)
and ceil( blocks*&&blksize/1024/1024) -
ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) > 0
/

select owner, segment_name, segment_type,
(select file_name from dba_data_files b
where b.file_id = a.file_id
and b.relative_fno = b.relative_fno ) file_name
from (select owner, segment_name, segment_type, file_id, relative_fno, block_id,
max(block_id) over (partition by file_id, relative_fno) max_block_id
from dba_extents
) a
where block_id = max_block_id
/

Craig thanks for your script..,
But i have other two scripts.,So now i got another from you.

Ixion and Pavb i spoke to my boss and he accepts.
Again., thanks to both of you.