Oracle shrinktable shrink table Use Cases

Oracle shrinktable shrink table Use Cases

There is a serious shortage of External table space in the site, and the customer does not want to resize it. To clean up some data, it is a large field blob. the tablespace is 1 TB and 0.99 TB has been used. After the blob before December 31, is deleted, GB of data is found in the basic information table of the attachment. However, the tablespace is not released (high water level line ), there are several ways to handle this:

1. Create a new table and import the remaining data, which is not feasible. There is no such large space.

2. Use a data pump or exp to export a large field, delete the table, and then import it.

3. Use shrink table.

Finally, we chose to use the third method. The following is an experiment. The blob experiment has also been tested, which can be shrunk. How to insert blob is not described here.

Select * from v $ version; BANNER implements Oracle Database 11g Enterprise Edition Release 11.2.0.1.0-64bit ProductionPL/SQL Release 11.2.0.1.0-ProductionCORE 11.2.0.1.0 ProductionTNS for Linux: version 11.2.0.1.0-ProductionNLSRTL Version 11.2.0.1.0-Productioncreate user TEST_DBidentified by TEST_DBdefault tablespace using tablespace TEMP; grant connect to TEST_DB; grant dba to TEST_DB; grant resource to TEST_DB; grant select any dictionary to TEST_DB; create tablespace testdb_tbs datafile '/home/oracle/app/oradata/orcl/testdb_tbs.dbf' size 100 m autoextend off; create table test as select * from dba_objects; insert into test select * from dba_objects; -- execute multiple times until the tablespace usage is more than 90% SELECT Upper (F. TABLESPACE_NAME) "tablespace name", D. TOT_GROOTTE_MB "tablespace size (M)", D. TOT_GROOTTE_MB-F. TOTAL_BYTES "used space (M)", F. TOTAL_BYTES "Free Space (M)", To_char (Round (D. TOT_GROOTTE_MB-F. TOTAL_BYTES)/D. TOT_GROOTTE_MB * 100, 2), '2017. 99 ') |' % '"usage ratio" FROM (SELECT TABLESPACE_NAME, Round (Sum (BYTES)/(1024*1024), 2) TOTAL_BYTES, Round (Max (BYTES) /(1024*1024), 2) MAX_BYTESFROM SYS. DBA_FREE_SPACEGROUP BY TABLESPACE_NAME) F, (select dd. TABLESPACE_NAME, Round (Sum (DD. BYTES)/(1024*1024), 2) TOT_GROOTTE_MBFROM SYS. DBA_DATA_FILES DDhaving DD. TABLESPACE_NAME = 'testdb _ TBS 'group by dd. TABLESPACE_NAME) dwhere d. TABLESPACE_NAME = F. TABLESPACE_NAME;

Tablespace name tablespace size (M) used space (M) idle space (M) usage ratio

------------- ------------- ------------- ----------- -----------TESTDB_TBS 100 97 3 97.00%select count(1) from test;COUNT(1)----------802835delete from test where rownum <=2835;commit;select count(1) from test;COUNT(1)---------800000

-- Query the table space again. The TESTDB_TBS space is not released.

Alter table test shrink space cascade;
Alter table test shrink space cascade
*
Row 3 has an error:
ORA-10636: row movement is not enabled


Alter table test enable row movement;
The table has been changed.

Alter table test shrink space cascade;-- Shrink together with index
The table has been changed.

SELECT Upper (F. TABLESPACE_NAME) "tablespace name", D. TOT_GROOTTE_MB "tablespace size (M)", D. TOT_GROOTTE_MB-F. TOTAL_BYTES "used space (M)", F. TOTAL_BYTES "Free Space (M)", To_char (Round (D. TOT_GROOTTE_MB-F. TOTAL_BYTES)/D. TOT_GROOTTE_MB * 100, 2), '2017. 99 ') |' % '"usage ratio" FROM (SELECT TABLESPACE_NAME, Round (Sum (BYTES)/(1024*1024), 2) TOTAL_BYTES, Round (Max (BYTES) /(1024*1024), 2) MAX_BYTESFROM SYS. DBA_FREE_SPACEGROUP BY TABLESPACE_NAME) F, (select dd. TABLESPACE_NAME, Round (Sum (DD. BYTES)/(1024*1024), 2) TOT_GROOTTE_MBFROM SYS. DBA_DATA_FILES DDhaving DD. TABLESPACE_NAME = 'testdb _ TBS 'group by dd. TABLESPACE_NAME) dwhere d. TABLESPACE_NAME = F. TABLESPACE_NAME; tablespace name tablespace size (M) used space (M) idle space (M) usage ratio ------------ ------------- ---------------- TESTDB_TBS 100 90.94 9.06