oracle| data
My test environment: Hp rp7410 Host, hp-unix11.11 os,oracle8.1.7.4 database, a test table space tbs_test with 90 tables around 100G.
Issue: There is a significant efficiency problem in Oracle using drop tablespace <tablespace_name> including contents to remove data dictionary-managed tablespaces.
Test start:
1. Use drop tablespace <tablespace_name> including contents to remove directly.
Sql>set Linesize 132
Sql>set Pagezie 0
Sql>set Timing on
Sql>drop tablespace tbs_test including contents
2/
Time used: 03:35:39.10
After I waited patiently, it took me 3.5 hours.
2, measured the same table space, to convert it to the local management of the deletion efficiency.
A, put the tbs_test back by return.
b, the way to convert tbs_test into local management.
Sql>set Timing on
sql> exec sys.dbms_space_admin.tablespace_migrate_to_local (' tbs_test ');
Time used: 00:06:33.25
C, delete this space.
Sql> drop tablespace tbs_test including contents
2/
Time used: 00:00:45.56
You can see that it only took 7 minutes.
3, measured the same table space, first delete the object, and then delete the table space efficiency.
A, put the tbs_test back by return.
B, form the statement to delete the table
Sql>set Linesize 132
Sql>set Pagezie 0
Sql>set Timing Off
Sql>spool Drop_test_tables.sql
Sql>select ' Drop table ' | | table_name| | '; ' From Dba_tables WHERE tablespace_name= ' tbs_test ';
Sql>spool off
C, Delete table
sql> @drop_test_tables. sql
This step takes about 20 seconds.
D, delete this space.
Sql>set Timing on
Sql> drop tablespace tbs_test including contents;
Time used: 00:07:35.53
You can see that it only took nearly 8 minutes.
Summary: We do in the data dictionary management of the table space to delete, it is best to delete the table space object before you delete the table space operation. You can also convert it to local (locally) managed space before you delete it. However, it is necessary to add that the local management of the space in the 8i version is available.
