Narendra -- Thanks for the question regarding "Reclaim space", version 9.2.0.1
You Asked
Hi,
A very happy & prosperous new year!!!
I want to reduce the size of USERS datafile. I had created a table with
50000000 records in USERS tablespace. So the size of datafile is around 4.5 GB.
I dropped the table. However this had no affect on datafile. I tried taking
datafile/tablespace offline and bring it back online. Still no change. I tried
shutting and restarting ORACLE instance. Still no change.
When I tried to manually reduce the datafile size (using OEM), it allowed me to
reduce the size by only few KBs. Any subsequent attempt to reduce the size
resulted in failure saying it needs the space.
How do I reduce the size of USERS datafile so that it is only as big as size of
database objects and their corresponding data ?
Thanks
and we said...
http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:153612348067
The only thing that'll make a file "shrink" is to alter the database and shrink
the datafile - datafiles will NOT shrink on their own due to "offline/online"
or anything like that.
But in your case - if OEM cannot shrink it further, neither will my script. A
file contains extents of segments. You have an extent for some allocated
object out there.
Say you have tables T1 and T2 in that tablespace.
T1 is represented by X
T2 is represented by Y
free space in the tablespace is represented by f
You created T1 and T2, your datafile in that tablespace might look like this:
XYffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
you could shrink that file now and get rid of all of the f's. But now table T1
grows and we have:
XYXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXfff
Now, you can shrink that file and get rid of just three f's (rest of the file
is full of data). Now, table T2 runs out of room and extends:
XYXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXYff
Now, if you shrank the file, you would lose two f's of space. However, you
drop table T1 - the datafile looks like this:
fYfffffffffffffffffffffffffffffffffffffffffffffffffffffffffffYff
You can STILL only shrink two f's away at the end - there is a Y extent way out
there and we cannot shrink over it. What you can do is:
alter table t2 MOVE and hopefully the datafile will end up looking like this:
YfYffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
and you can shrink the file (or just move t2 into another tablespace all
together)
see
http://www.oracle.com/technology/oramag/oracle/04-sep/o54asktom.html
for "What's at the end of a file"
Here is an example showing what I tried to draw above:
ops$tkyte@ORA9IR2> create tablespace shrink_me
2 datafile '/tmp/shrink_me.dbf' size 704k
3 segment space management manual
4 uniform size 64k
5 /
Tablespace created.
we have exactly 10 64k extents we can use. (the 11th 64k block of space is
used by Oracle to manage these locally managed tablespaces in the datafile)
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create table t1 ( x int, a char(2000) default 'a', b
char(2000) default 'b', c char(2000) default 'c') tablespace shrink_me;
Table created.
ops$tkyte@ORA9IR2> create table t2 ( x int, a char(2000) default 'a', b
char(2000) default 'b', c char(2000) default 'c') tablespace shrink_me;
Table created.
Each row in these tables will consume a block (8 rows/extent - but don't
forget the first block is borrowed by Oracle to manage space in the
segment...)
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select file_id, block_id first_block, block_id+blocks-1
last_block, segment_name
2 from dba_extents
3 where tablespace_name = 'SHRINK_ME'
4 union all
5 select file_id, block_id, block_id+blocks-1, 'free'
6 from dba_free_space
7 where tablespace_name = 'SHRINK_ME'
8 order by file_id, first_block
9 /
FILE_ID FIRST_BLOCK LAST_BLOCK SEGMENT_NAME
---------- ----------- ---------- ------------------------------
13 9 16 T1
13 17 24 T2
13 25 88 free
so, we have the starting scenario - T1 has an extent, T2 has one and the rest
of the file is "free space", now lets fill up t1:
ops$tkyte@ORA9IR2> insert into t1 (x) select rownum from all_objects where
rownum <= 56;
56 rows created.
ops$tkyte@ORA9IR2> commit;
Commit complete.
ops$tkyte@ORA9IR2> select file_id, block_id first_block, block_id+blocks-1
last_block, segment_name
2 from dba_extents
3 where tablespace_name = 'SHRINK_ME'
4 union all
5 select file_id, block_id, block_id+blocks-1, 'free'
6 from dba_free_space
7 where tablespace_name = 'SHRINK_ME'
8 order by file_id, first_block
9 /
FILE_ID FIRST_BLOCK LAST_BLOCK SEGMENT_NAME
---------- ----------- ---------- ------------------------------
13 9 16 T1
13 17 24 T2
13 25 32 T1
13 33 40 T1
13 41 48 T1
13 49 56 T1
13 57 64 T1
13 65 72 T1
13 73 80 T1
13 81 88 free
10 rows selected.
we have the middle scenario - if we dropped T1 now, all of the T1's would
become free space and we could shrink the file, however:
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> insert into t2 (x) select rownum from all_objects where
rownum <= 8;
8 rows created.
ops$tkyte@ORA9IR2> commit;
Commit complete.
ops$tkyte@ORA9IR2> select file_id, block_id first_block, block_id+blocks-1
last_block, segment_name
2 from dba_extents
3 where tablespace_name = 'SHRINK_ME'
4 union all
5 select file_id, block_id, block_id+blocks-1, 'free'
6 from dba_free_space
7 where tablespace_name = 'SHRINK_ME'
8 order by file_id, first_block
9 /
FILE_ID FIRST_BLOCK LAST_BLOCK SEGMENT_NAME
---------- ----------- ---------- ------------------------------
13 9 16 T1
13 17 24 T2
13 25 32 T1
13 33 40 T1
13 41 48 T1
13 49 56 T1
13 57 64 T1
13 65 72 T1
13 73 80 T1
13 81 88 T2
10 rows selected.
Now the entire tablespace is full - no more free space - but we drop t1 and
get LOTS of free space:
ops$tkyte@ORA9IR2> drop table t1;
Table dropped.
ops$tkyte@ORA9IR2> select file_id, block_id first_block, block_id+blocks-1
last_block, segment_name
2 from dba_extents
3 where tablespace_name = 'SHRINK_ME'
4 union all
5 select file_id, block_id, block_id+blocks-1, 'free'
6 from dba_free_space
7 where tablespace_name = 'SHRINK_ME'
8 order by file_id, first_block
9 /
FILE_ID FIRST_BLOCK LAST_BLOCK SEGMENT_NAME
---------- ----------- ---------- ------------------------------
13 9 16 free
13 17 24 T2
13 25 80 free
13 81 88 T2
the tablespace is 80% empty - but we cannot shrink it - we cannot shrink it
at all, let alone to 300k - because the LAST EXTENT is taken by T2
ops$tkyte@ORA9IR2> alter database datafile '/tmp/shrink_me.dbf' resize 300k;
alter database datafile '/tmp/shrink_me.dbf' resize 300k
*
ERROR at line 1:
ORA-03297: file contains used data beyond requested RESIZE value
but lets move T2 around...
ops$tkyte@ORA9IR2> alter table t2 move;
Table altered.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select file_id, block_id first_block, block_id+blocks-1
last_block, segment_name
2 from dba_extents
3 where tablespace_name = 'SHRINK_ME'
4 union all
5 select file_id, block_id, block_id+blocks-1, 'free'
6 from dba_free_space
7 where tablespace_name = 'SHRINK_ME'
8 order by file_id, first_block
9 /
FILE_ID FIRST_BLOCK LAST_BLOCK SEGMENT_NAME
---------- ----------- ---------- ------------------------------
13 9 16 T2
13 17 24 free
13 25 32 T2
13 33 88 free
now we have lots of free space at the end of the file and we can resize:
ops$tkyte@ORA9IR2> alter database datafile '/tmp/shrink_me.dbf' resize 300k;
Database altered.
ops$tkyte@ORA9IR2> select file_id, block_id first_block, block_id+blocks-1
last_block, segment_name
2 from dba_extents
3 where tablespace_name = 'SHRINK_ME'
4 union all
5 select file_id, block_id, block_id+blocks-1, 'free'
6 from dba_free_space
7 where tablespace_name = 'SHRINK_ME'
8 order by file_id, first_block
9 /
FILE_ID FIRST_BLOCK LAST_BLOCK SEGMENT_NAME
---------- ----------- ---------- ------------------------------
13 9 16 T2
13 17 24 free
13 25 32 T2
don't forget, if you move a table, you have to then rebuild the indexes