Oracle quickly deletes data dictionary management table space
My test environment: HP RP7410 host, HP-UNIX11.11 OS, Oracle 8.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 Including Contents; there is a big efficiency problem when deleting data dictionary management table space.
Test start:
1. Use the Drop TableSpace Including Contents; the way is directly deleted.
SQL> SET LINESIZE 132
SQL> SET PageZie 0
SQL> Set Timing On
SQL> 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 On
SQL> 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 /
Time: 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 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 the table
SQL> @DOP_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: 00: 07: 35.53
It can be seen that a total of 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.
