There is an external network of the environment table space is seriously insufficient, customers do not want to expand, to clean up part of the data, are large pieces of blob. Table space is 1T, already using 0.99T, deleted before 2015 blob, through the attachment of the underlying information table to detect the 200G data, but the table space is not released (high watermark), this time there are several ways to deal with:
1. Create a new table, the rest of the data to import, not feasible, there is no such a large space .
2. Use the data pump or export large characters with exp, delete the table, and then import .
3. Use Shrink table.
The final choice with a third scenario, the following is the experiment, the BLOB experiment has also been tested, can be shrunk, how to insert a blob, refer to http://blog.csdn.net/stevendbaguo/article/details/28636369 here no longer repeat.
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 100m autoextend off;
CREATE TABLE Test as SELECT * from Dba_objects;
INSERT INTO Test SELECT * from dba_objects;--executes multiple times until tablespace usage is more than 90%
Select Upper (f.tablespace_name) "Table space name",
D.TOT_GROOTTE_MB "Table space 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), ' 990.99 ')
|| '% ' "use ratio"
From (SELECT Tablespace_name,
Round (Sum (BYTES)/(1024x768 * 1024x768), 2) Total_bytes,
Round (Max (BYTES)/(1024x768), 2) max_bytes
From SYS. Dba_free_space
GROUP by Tablespace_name) F,
(SELECT DD.) Tablespace_name,
Round (Sum (DD). BYTES)/(1024x768 * 1024x768), 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) free space (m) use ratio
------------- ------------- ------------- ----------- -----------
Testdb_tbs 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 table space again, Testdb_tbs space is not released
ALTER TABLE test shrink space cascade;
ALTER TABLE test SHRINK space Cascade
*
An error occurred on line 1th:
Ora-10636:row movement is not enabled
ALTER TABLE test enable row movement;
The table has changed.
ALTER TABLE test shrink space cascade;--shrink along with index
The table has changed.
Select Upper (f.tablespace_name) "Table space name",
D.TOT_GROOTTE_MB "Table space 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), ' 990.99 ')
|| '% ' "use ratio"
From (SELECT Tablespace_name,
Round (Sum (BYTES)/(1024x768 * 1024x768), 2) Total_bytes,
Round (Max (BYTES)/(1024x768), 2) max_bytes
From SYS. Dba_free_space
GROUP by Tablespace_name) F,
(SELECT DD.) Tablespace_name,
Round (Sum (DD). BYTES)/(1024x768 * 1024x768), 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) free space (m) use ratio
------------ ------------- ------------- ----------- ----------------
Testdb_tbs 90.94 9.06 90.94%
Oracle Shrink Table Shrink Tables usage Scenario
