My test environment: HP RP7410 host, HP-UNIX11.11 OS, Oracle8.1.7.4 database, a test table space TBS_TEST with approximately 100G of 90 tables.
The question is proposed: Oracle is using Drop tableSpace
Test start:
1. Use the drop tablespace
SQL> SET LINESIZE 132SQL> Set Pagezie 0sql> Set Timing Onsql> Drop TableSpace TBS_TEST INCLUDING Contents 2 /
Time: 03: 35: 39.10
After I was waiting patiently, spent three and a half hours.
2. Test the same table space to convert it to the deletion efficiency of local mode management.
a, put the TBS_TEST back to recovery.
b, transform TBS_TEST to local management.
SQL> SET TIMING ONSQL> EXEC SYS.DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL ('TBS_TEST');
Time: 00: 06: 33.25
C, delete this space.
SQL> Drop TableSpace TBS_TEST INCLUDING Contents 2 / Hand: 00: 00: 45.56
It can be seen that a total of 7 minutes is more than 7 minutes.
3. Test the same table space, first delete the object, and then delete this type of time efficiency.
a, put the TBS_TEST back to recovery.
b, form a statement of deleting tables
SQL> set linesize 132SQL> set pagezie 0SQL> set timing offSQL> spool drop_test_tables.sqlSQL> 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 Onsql> Drop TableSpace TBS_TEST INCLUDING CONTENTS;
Time: 00: 07: 35.53 It can be seen that the total cost is expensive for nearly 8 minutes.
Summary: When we do delete the tablespace managed by the data dictionary, we'd better delete objects in the tablespace and then delete the tablespace operation. It is also possible to convert it to local (local) management. However, it is necessary to supplement that local management space is available in the version after 8i.
