Oracle shrink table use case, oracleshrink
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 choose to use the third scheme, the following is the test, blob experiment also tested, can contract, how to insert blob, refer to the http://blog.csdn.net/stevendbaguo/article/details/28636369 here will not go into detail.
Select * from v $ version;
BANNER
-------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0-64bit Production
PL/SQL Release 11.2.0.1.0-Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0-Production
NLSRTL Version 11.2.0.1.0-Production
Create user TEST_DB
Identified by TEST_DB
Default tablespace testdb_tbs
Temporary 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 above 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 "Idle space (M )",
To_char (Round (D. TOT_GROOTTE_MB-F. TOTAL_BYTES)/D. TOT_GROOTTE_MB * 100, 2), '2014. 99 ')
| '%' "Usage ratio"
FROM (SELECT TABLESPACE_NAME,
Round (Sum (BYTES)/(1024*1024), 2) TOTAL_BYTES,
Round (Max (BYTES)/(1024*1024), 2) MAX_BYTES
From sys. DBA_FREE_SPACE
Group by TABLESPACE_NAME) F,
(Select dd. TABLESPACE_NAME,
Round (Sum (DD. BYTES)/(1024*1024), 2) TOT_GROOTTE_MB
From sys. DBA_DATA_FILES DD
Having DD. TABLESPACE_NAME = 'testdb _ TBS'
Group by dd. TABLESPACE_NAME) D
Where 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)
----------
802835
Delete 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 "Idle space (M )",
To_char (Round (D. TOT_GROOTTE_MB-F. TOTAL_BYTES)/D. TOT_GROOTTE_MB * 100, 2), '2014. 99 ')
| '%' "Usage ratio"
FROM (SELECT TABLESPACE_NAME,
Round (Sum (BYTES)/(1024*1024), 2) TOTAL_BYTES,
Round (Max (BYTES)/(1024*1024), 2) MAX_BYTES
From sys. DBA_FREE_SPACE
Group by TABLESPACE_NAME) F,
(Select dd. TABLESPACE_NAME,
Round (Sum (DD. BYTES)/(1024*1024), 2) TOT_GROOTTE_MB
From sys. DBA_DATA_FILES DD
Having DD. TABLESPACE_NAME = 'testdb _ TBS'
Group by dd. TABLESPACE_NAME) D
Where 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 90.94%
