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 valueSo. 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