Let me make an example:
SQL> create tablespace tb_test
2 datafile 'c:\temp\tb_test.dbf' size 100 K autoextend on;
Tablespace created.
SQL> select count(*) from dba_extents where tablespace_name='TB_TEST';
COUNT(*)
----------
0
SQL> select bytes from v$datafile where name = 'C:\TEMP\TB_TEST.DBF';
BYTES
----------
106496
SQL> create table t_big tablespace tb_test as
2 select a.* from all_objects a, dba_users for_cartesian;
Table created.
SQL> select count(*) from dba_extents where tablespace_name='TB_TEST';
COUNT(*)
----------
34
SQL> select bytes from v$datafile where name = 'C:\TEMP\TB_TEST.DBF';
BYTES
----------
19963904Now we have datafile quite big. Let's drop table and shrink file:
SQL> drop table t_big;
Table dropped.
SQL> select count(*) from dba_extents where tablespace_name='TB_TEST';
COUNT(*)
----------
0
SQL> alter database datafile 'c:\temp\tb_test.dbf' resize 100 K;
alter database datafile 'c:\temp\tb_test.dbf' resize 100 K
*
ERROR at line 1:
ORA-03297: file contains used data beyond requested RESIZE value
So. even there is nothing reported in DBA_EXTENT, file cannot be shinked.
Let's purge recyclebin and try again:
SQL> purge recyclebin; Recyclebin purged. SQL> alter database datafile 'c:\temp\tb_test.dbf' resize 100 K; Database altered.Now works fine! Don't forget to clear after tests :)
SQL> drop tablespace tb_test including contents and datafiles; Tablespace dropped.
Cheers, Paweł



2 comments:
Hi
Thank you very much for your explanation.
Shweta
bd.shweta@gmail.com
Thank you.Good one
Post a Comment