Oracle-deallocate unused releases High Water level Space (2)
Deallocate unused: only suitable for releasing space above high water level in HWM, but not below high water level; for example, pre-allocated space to table
Instructions and methods are provided in the official document, as follows:
Use the deallocate_unused_clause to explicitly deallocate unused space at the end of
A database object segment and make the space available for other segments in the
Tablespace.
You can deallocate unused space using the following statements:
■ ALTER CLUSTER (see ALTER CLUSTER on page 10-5)
■ ALTER INDEX: to deallocate unused space from the index, an index partition, or an
Index subpartition (see ALTER INDEX on page 10-78)
■ ALTER MATERIALIZED VIEW: to deallocate unused space from the overflow segment
Of an index-organized materialized view (see ALTER MATERIALIZED VIEW on
Page 11-3)
■ ALTER TABLE: to deallocate unused space from the table, a table partition, a table
Subpartition, the mapping table of an index-organized table, the overflow segment
Of an index-organized table, ora LOB storage segment (see ALTER TABLE on
Page 12-2)
1. Test environment:
Oracle11.2.0.4
Use admin users to create a new test partition table in the test tablespace, pre-allocate some space to the subpartitions in advance, then insert a small amount of data, and finally use: alter table. Dealership unused; to free up unused space
2. Start testing
①: view the tablespace usage of the test environment:
SQL > set linesize 2500SQL > set pagesize 300SQL > select a.tablespace_name "Tablespace name", 100-round ((nvl (b.bytes_free, 0) / a.bytes_alloc) * 100,2) "occupancy (%)", round (a.bytes_alloc / 1024 / 1024, 2) "capacity (M)", round (nvl (b.bytes_free, 0) / 1024 / 1024, 2) "Free (M)", round ((a.bytes_alloc-nvl (b.bytes_free) 0)) / 1024 / 1024, 2) "use (M)", to_char (sysdate, 'yyyy-mm-dd hh34:mi:ss') "sampling time" from (select f. Tablespacetimes) bytes_alloc,sum (decode (f.autoextensible,' YES', f.maxbytes, 'NO', f.bytes)) maxbytes from dba_data_files f group by tablespace_name) a, (select f.tablespace_name Sum (f.bytes) bytes_free from dba_free_space f group by tablespace_name) b where a.tablespace_name = b.tablespace_name order by 2 desc ? (%)? (M)? (M)? (M)- -- SYSAUX 94.41 510 28.5 481.5 2017-08-07 17:01:20SYSTEM 93.24800 54.06 745.94 2017-08-07 17:01:20USERS 26.25 5 3.69 1.31 2017-08-07 17:01:20UNDOTBS1 17.97 470 385.56 84.44 2017-08-07 17:01:20TEST .05 20480 20470 10 2017-08-07 17:01:20SQL > show userUSER is "ADMIN"-users for testing
②: create test partition table: test_emp
SQL > create table test_emp (TMP_UPSTATE_CASEKEY char (14), TMP_NUM_STATUS_ID number (38), updated_date date) 2 partition by range (TMP_NUM_STATUS_ID) 3 (partition pt_1 values less than (1000000)) nologging; Table created.
③: pre-allocate 10G of space to the pt_1 partition table:
SQL > alter table test_EMP modify PARTITION pt_1 ALLOCATE EXTENT (size 10240m); Table altered.
④: collect statistics for the partition table and view the block block occupancy of the table
SQL > exec dbms_stats.gather_table_stats (ownname = > 'ADMIN',tabname = >' TEST_EMP',cascade= > TRUE); PL/SQL procedure successfully completed.SQL > select B.SEGMENT_NAME, B.blocksMagi B. blocks * 8096 / 1024 / 1024, A. blocks * 8096 / 1024 / 1024, A.EMPTY_BLOCKS from user_tables a, USER_SEGMENTS B WHERE TABLE_NAME = 'TEST_EMP' AND A.TABLE_NAME = B.SEGMENT_NAME SEGMENT_NAME BLOCKS B.BLOCKS*8096/1024/1024 BLOCKS A.BLOCKS*8096/1024/1024 EMPTY_BLOCKS -TEST_EMP 1315840 10159.5313 0 0SQL > SELECT segment_name SUM (bytes) / 1024 / 1024 Mbytes FROM dba_segments WHERE PARTITION_NAME = 'PT_1' GROUP BY segment_name SEGMENT_NAME MBYTES -- TEST_EMP 10288BIN$VibHVCPfDL/gU8gCqMDDfw==$0 8
-as you can see from the above, the size of the test_emp partition table is 10G, while there are 1315840 blocks occupied
⑤: insert some data into the partitioned table and view the size of the table
SQL > create or replace procedure proc_casekey_upstate 2 as 3 casekey char (14); 4 begin 5 for i in 1.. 10000 loop 6 casekey: = 'TMP' | lpad (casekey, 1, sysdate); 8 end loop; 9 commit; 10 end; 11 / Procedure created.SQL > exec proc_casekey_upstate -execute stored procedure to insert data PL/SQL procedure successfully completed.SQL > exec dbms_stats.gather_table_stats (ownname = > 'ADMIN',tabname = >' TEST_EMP',cascade= > TRUE) -collect the statistical information of the table PL/SQL procedure successfully completed.SQL > select B.SEGMENT_NAME, B.blocksMagi B. blocks * 8096 / 1024 / 1024, A.BLOCKS WHERE TABLE_NAME A.blocks * 8096 / 1024 / 1024, A.EMPTY_BLOCKS from user_tables a, USER_SEGMENTS B WHERE TABLE_NAME = 'TEST_EMP' AND A.TABLE_NAME = B.SEGMENT_NAME SEGMENT_NAME BLOCKS B.BLOCKS*8096/1024/1024 BLOCKS A.BLOCKS*8096/1024/1024 EMPTY_BLOCKS -TEST_EMP 1315840 10159.5313 46. 355163574 0SQL > SELECT segment_name SUM (bytes) / 1024 / 1024 Mbytes FROM dba_segments WHERE PARTITION_NAME = 'PT_1' GROUP BY segment_name SEGMENT_NAME MBYTES -- TEST_EMP 10288BIN$VibHVCPfDL/gU8gCqMDDfw==$0 8
Note: as you can see from the above, the size of the table is 10G, but there are only 46 blocks occupied by the table, so obviously there is a lot of space unused
⑥: free up unused space (I have passed the 10046 event for this operation, in practice, you only need to execute: alter table test_emp modify partition pt_1 deallocate unused;)
SQL > alter session set tracefile_identifier='10046';SQL > alter session set events' 10046 trace name context forever,level 12 hours;-Open the 10046 event Session altered.SQL > SQL > alter table test_emp modify partition pt_1 deallocate unused;-execute this command to release the unused space of the subpartition Table altered.SQL > alter session set events' 10046 trace name context off';-close 10046 event Session altered.SQL > select value from v$diag_info where name='Default Trace File' VALUE- - - - -/ opt/oracle/diag/rdbms/dbs/dbs/trace/dbs_ora_3263_10046.trc
⑦: verify that the space is freed:
SQL > select B.SEGMENT_NAME, B.blocksMagi B. WHERE TABLE_NAME * 8096 / 1024 / 1024, A.BLOCKSMagazine A. blocks * 8096 / 1024 / 1024, A.EMPTY_BLOCKS from user_tables a, blockB WHERE TABLE_NAME = 'TEST_EMP' AND A.TABLE_NAME = B.SEGMENT_NAME SEGMENT_NAME BLOCKS B.BLOCKS*8096/1024/1024 BLOCKS A.BLOCKS*8096/1024/1024 EMPTY_BLOCKS -TEST_EMP 1024 7.90625 46. 355163574 0SQL > SELECT segment_name SUM (bytes) / 1024 / 1024 Mbytes FROM dba_segments WHERE PARTITION_NAME = 'PT_1' GROUP BY segment_name SEGMENT_NAME MBYTES -- TEST_EMP 16BIN$VibHVCPfDL/gU8gCqMDDfw==$0 8 muri- -- SYSAUX 94.44 510 28.38 481.63 2017-08-07 17:19:30SYSTEM 93.24 800 54.06 745.94 2017-08-07 17:19:30USERS 26.25 5 3.69 1.31 2017-08-07 17:19:30UNDOTBS1 5.73 470 443.06 26.94 2017-08-07 17:19:30TEST .05 20480 20470 10 2017-08-07 17:19:30
As you can see from the above, the size of the table has become 16m, and there are 1024 blocks; the unused space of the table has been freed, and the available size of the table space has become larger:
