ITPub博客

首页 > Linux操作系统 > Linux操作系统 > Why is no space released after an ALTER TABLE ... SHRINK? (文档 ID 820043.1)

Why is no space released after an ALTER TABLE ... SHRINK? (文档 ID 820043.1)

原创 Linux操作系统 作者:rongshiyuan 时间:2013-10-10 04:54:57 0 删除 编辑
Why is no space released after an ALTER TABLE ... SHRINK? (文档 ID 820043.1)
 
In this Document

 Symptoms
 Cause
 Solution
 References
--------------------------------------------------------------------------------

Applies to:
Oracle Server - Enterprise Edition - Version 10.1.0.2 and later
Information in this document applies to any platform.
***Checked for relevance on 30-Mar-2011***
 
Symptoms
An ALTER TABLE ... SHRINK has been done and no space seems to have been released
Cause
Misunderstanding of how Oracle Storage works
Solution
SECTION 1: Understanding what ALTER TABLE ... SHRINK does (and does not do)
It is a common misunderstanding that an ALTER TABLE ... SHRINK will reduce the size of tablespaces ... datafiles or extents ...
        this command will not reduce the size of these structures
ALTER TABLE ... SHRINK may be used to 'gather' all rows in a table into as few extents as possible and then empty extents can be freed
  NOTE: If an extent contains one or more rows the extent cannot be freed

Here is what ALTER TABLE ... SHRINK does
From the Oracle?? Database SQL Language Reference
ALTER TABLE
shrink_clause
The shrink clause lets you manually shrink space in a table, index-organized table or its overflow segment,index, partition,
  subpartition, LOB segment, materialized view, or materialized view log.
This clause is valid only for segments in tablespaces with automatic segment management.
By default, Oracle Database compacts the segment, adjusts the high water mark, and releases the recuperated space
  immediately.
Compacting the segment requires row movement. Therefore, you must enable row movement for the object you want
  to shrink before specifying this clause.
              Note: Do not attempt to enable row movement for an index-organized table before specifying the shrink_clause.
                        The ROWID of an index-organized table is its primary key, which never changes.
                         Therefore, row movement is neither relevant nor valid for such tables.
COMPACT
If you specify COMPACT, then Oracle Database only defragments the segment space and compacts the table rows for
  subsequent release. The database does not readjust the high water mark and does not release the space immediately.
  You must issue another ALTER TABLE ... SHRINK SPACE statement later to complete the operation. This clause is useful if
  you want to accomplish the shrink operation in two shorter steps rather than one longer step.
For an index or index-organized table, specifying ALTER [INDEX | TABLE] ... SHRINK SPACE COMPACT is equivalent to specifying
  ALTER [INDEX | TABLE ... COALESCE. The shrink_clause can be cascaded (refer to the CASCADE clause, which follows) and
  compacts the segment more densely than does a coalesce operation, which can improve performance. However, if you do not
  want to release the unused space, then you can use the appropriate COALESCE clause.
Restrictions on the shrink_clause
* You cannot combine this clause with any other clauses in the same ALTER TABLE statement.
* You cannot specify this clause for a cluster, a clustered table, or any object with a LONG column.
* Segment shrink is not supported for tables with function-based indexes, domain indexes, or bitmap join indexes.
* This clause does not shrink mapping tables of index-organized tables, even if you specify CASCADE.
* You cannot specify this clause for a compressed table.
* You cannot shrink a table that is the master table of an ON COMMIT materialized view. Rowid materialized views must be rebuilt
      after the shrink operation.
 
From this we know that we must have ROW MOVEMENT enabled in order for SHRINK to work ... but what is ROW MOVEMENT?
 
From the Oracle?? Database SQL Language Reference
CREATE  TABLE
row_movement_clause
The row_movement_clause lets you specify whether the database can move a table row. It is possible for a row to move, for example,
  during table compression or an update operation on partitioned data.
Caution:
If you need static rowids for data access, then do not enable row movement. For a normal (heap-organized) table, moving a row changes
  the rowid of the row. For a moved row in an index-organized table, the logical rowid remains valid, although the physical guess component
  of the logical rowid becomes inaccurate.
* Specify ENABLE to allow the database to move a row, thus changing the rowid.
* Specify DISABLE if you want to prevent the database from moving a row, thus preventing a change of rowid.
If you omit this clause, then the database disables row movement.
 
Why does ROW MOVEMENT have to be enabled for an ALTER TABLE ... SHRINK to succeed?
Row Movement allows operations to move rows to a different extent ...
Operations :
* Partition redefinition
* Compression of tables (different from SHRINK)
* Shrinking of tables (with compact)
can require that a row be moved to a different extent
 
SUMMARY
ALTER TABLE ... SHRINK requires
    * The table to reside in a Locally Managed Tablespace (in order to allow Automatic Segment Space Management to be available)
    * The tablespace in which the table resides must use Automatic Segment Space Management
    * Row Movement must be enabled for the table (unless the table is index-organized)
ALTER TABLE ... SHRINK does not reduce the size of datafiles / tablespaces
 
 
SECTION 2: Understanding ALTER TABLE ... SHRINK and EXTENTS
ALTER TABLE ... SHIRNK will ... if possible ... move rows such that unneeded extents can be deallocated
Considerations need to given to the actual extent sizes as these can cause the appearance of a failure to shrink
SCENARIO:
          Suppose we have a table with two (2) 50mb extents
          The table is 60% full and meets the requirement for shrinking
           A shrink of such a table would result in rows being moved such that one extent is 100% full ... and the other extent is only 20% full
           No extents could be released ... giving the appearance that nothing occurred
 

BOTTOM LINE:  If the SHRINK cannot move enough rows out of an extent so that it can be deallocate then no reduction in space
                                    will be noted in DBA_FREE_SPACE
 
SECTION 3: Understanding how to examine free space within a table in an ASSM tablespace
DBA_FREE_SPACE only shows what free space exists in a tablespace ... not within a table
 
The following process may be used at any time to determine the amount of free space within a table in an ASSM tablespace
set serveroutput on
declare
TOTAL_BLOCKS number;
TOTAL_BYTES number;
UNUSED_BLOCKS number;
UNUSED_BYTES number;
LAST_USED_EXTENT_FILE_ID number;
LAST_USED_EXTENT_BLOCK_ID number;
LAST_USED_BLOCK number;
begin
     dbms_space.unused_space(
         '',
         '',
         '',
         TOTAL_BLOCKS,
         TOTAL_BYTES,
         UNUSED_BLOCKS,
         UNUSED_BYTES,
         LAST_USED_EXTENT_FILE_ID,
         LAST_USED_EXTENT_BLOCK_ID,
         LAST_USED_BLOCK);
     dbms_output.put_line('TOTAL_BLOCKS = '||TOTAL_BLOCKS);
     dbms_output.put_line('UNUSED_BLOCKS = '||UNUSED_BLOCKS);
end;
/

The following procedure may be used at any time to see the block allocations for a table stored in an ASSM tablespace
set serveroutput on
declare
v_unformatted_blocks number;
v_unformatted_bytes number;
v_fs1_blocks number;
v_fs1_bytes number;
v_fs2_blocks number;
v_fs2_bytes number;
v_fs3_blocks number;
v_fs3_bytes number;
v_fs4_blocks number;
v_fs4_bytes number;
v_full_blocks number;
v_full_bytes number;
begin
     dbms_space.space_usage (
       '',
       '',
       '',
       v_unformatted_blocks,
       v_unformatted_bytes,
       v_fs1_blocks,
       v_fs1_bytes,
       v_fs2_blocks,
       v_fs2_bytes,
       v_fs3_blocks,
       v_fs3_bytes,
       v_fs4_blocks,
       v_fs4_bytes,
       v_full_blocks,
       v_full_bytes);
     dbms_output.put_line('Unformatted Blocks                       = '||v_unformatted_blocks);
     dbms_output.put_line('Blocks with 00-25% free space   = '||v_fs1_blocks);
     dbms_output.put_line('Blocks with 26-50% free space   = '||v_fs2_blocks);
     dbms_output.put_line('Blocks with 51-75% free space   = '||v_fs3_blocks);
     dbms_output.put_line('Blocks with 76-100% free space = '||v_fs4_blocks);
     dbms_output.put_line('Full Blocks                                        = '||v_full_blocks);
end;
/
 
 
CASE STUDIES

CASE STUDY #1 - Demonstrate block usage / free before and after a shrink operation
CASE STUDY #2 - Demonstrate how blocks are allocated (free / used) in a table using ASSM
CASE STUDY #3 - Demonstrate a shrink of a LOB segment when using Basicfile
CASE STUDY #4 - Demonstrate the inability to shrink a LOB segment when using Securefile
CASE STUDY #5 - Demonstrate a shrink occuring in an Index-Organized Table (IOT)
 
Setup for case study
create tablespace test datafile '/test01.dbf' size 200m extent management local uniform. size 1m;
Setup for each case study:

create user test identified by test default tablespace test;
grant dba to test;
connect test/test;
 
CASE STUDY #1
Demonstrate block usage / free before and after a shrink operation
 
-- CREATE THE TEST TABLE AND DETERMINE WHAT STORAGE WAS USED
create table test as select * from sys.obj$;
set serveroutput on
declare
TOTAL_BLOCKS number;
TOTAL_BYTES number;
UNUSED_BLOCKS number;
UNUSED_BYTES number;
LAST_USED_EXTENT_FILE_ID number;
LAST_USED_EXTENT_BLOCK_ID number;
LAST_USED_BLOCK number;
begin dbms_space.unused_space('TEST', 'TEST', 'TABLE',TOTAL_BLOCKS, TOTAL_BYTES, UNUSED_BLOCKS,
                                                          UNUSED_BYTES,LAST_USED_EXTENT_FILE_ID, LAST_USED_EXTENT_BLOCK_ID,
                                                          LAST_USED_BLOCK);
dbms_output.put_line('TOTAL_BLOCKS = '||TOTAL_BLOCKS);
dbms_output.put_line('UNUSED_BLOCKS = '||UNUSED_BLOCKS);
end;
/
-- TOTAL_BLOCKS = 896
-- UNUSED_BLOCKS = 61
select count(*), bytes from user_extents where segment_name = 'TEST' group by bytes;
 
-- COUNT(*)    BYTES
-- ----------  ----------
-- 7           1048576
-- DELETE FROM THE TEST TABLE TO FREE UP SPACE
delete from test;
-- 66524 rows deleted.
commit;
-- Commit complete.
 
-- EXAMINE THE CHANGES IN STORAGE
declare
TOTAL_BLOCKS number;
TOTAL_BYTES number;
UNUSED_BLOCKS number;
UNUSED_BYTES number;
LAST_USED_EXTENT_FILE_ID number;
LAST_USED_EXTENT_BLOCK_ID number;
LAST_USED_BLOCK number;
begin dbms_space.unused_space('TEST', 'TEST', 'TABLE',TOTAL_BLOCKS, TOTAL_BYTES, UNUSED_BLOCKS,
                                                          UNUSED_BYTES,LAST_USED_EXTENT_FILE_ID, LAST_USED_EXTENT_BLOCK_ID,
                                                          LAST_USED_BLOCK);
dbms_output.put_line('TOTAL_BLOCKS = '||TOTAL_BLOCKS);
dbms_output.put_line('UNUSED_BLOCKS = '||UNUSED_BLOCKS);
end;
/
-- TOTAL_BLOCKS = 896
-- UNUSED_BLOCKS = 61
select count(*), bytes from user_extents where segment_name = 'TEST' group by bytes;
 
-- COUNT(*)    BYTES
-- ----------  ----------
-- 7           1048576
-- MODIFY THE TABLE SO THAT IT CAN BE SHRUNK
 
alter table test enable row movement;

-- SHRINK THE TABLE
alter table test shrink space;
 
-- EXAMINE THE CHANGES IN STORAGE
declare
TOTAL_BLOCKS number;
TOTAL_BYTES number;
UNUSED_BLOCKS number;
UNUSED_BYTES number;
LAST_USED_EXTENT_FILE_ID number;
LAST_USED_EXTENT_BLOCK_ID number;
LAST_USED_BLOCK number;
begin dbms_space.unused_space('TEST', 'TEST', 'TABLE',TOTAL_BLOCKS, TOTAL_BYTES, UNUSED_BLOCKS,
                                                          UNUSED_BYTES,LAST_USED_EXTENT_FILE_ID, LAST_USED_EXTENT_BLOCK_ID,
                                                          LAST_USED_BLOCK);
dbms_output.put_line('TOTAL_BLOCKS = '||TOTAL_BLOCKS);
dbms_output.put_line('UNUSED_BLOCKS = '||UNUSED_BLOCKS);
end;
/
-- TOTAL_BLOCKS = 128
-- UNUSED_BLOCKS = 123
select count(*), bytes from user_extents where segment_name = 'TEST' group by bytes;
 
-- COUNT(*)   BYTES
-- ---------- ----------
-- 1          1048576
CASE STUDY #2
Demonstrate how blocks are allocated (free / used) in a table using ASSM
 
-- CREATE THE TEST TABLE AND DETERMINE WHAT STORAGE WAS USED
create table test as select * from sys.obj$ where obj# < 10000;
select count(*) from user_extents where segment_name = 'TEST';
-- COUNT(*)
-- ----------
--         1
SELECT SUM(BYTES) FROM DBA_FREE_SPACE WHERE TABLESPACE_NAME = 'TEST';
-- SUM(BYTES)
-- ----------
-- 207618048
set serveroutput on
declare
v_unformatted_blocks number;
v_unformatted_bytes number;
v_fs1_blocks number;
v_fs1_bytes number;
v_fs2_blocks number;
v_fs2_bytes number;
v_fs3_blocks number;
v_fs3_bytes number;
v_fs4_blocks number;
v_fs4_bytes number;
v_full_blocks number;
v_full_bytes number;
begin
dbms_space.space_usage (
'TEST',
'TEST',
'TABLE',
v_unformatted_blocks,
v_unformatted_bytes,
v_fs1_blocks,
v_fs1_bytes,
v_fs2_blocks,
v_fs2_bytes,
v_fs3_blocks,
v_fs3_bytes,
v_fs4_blocks,
v_fs4_bytes,
v_full_blocks,
v_full_bytes);
dbms_output.put_line('Unformatted Blocks                      = '||v_unformatted_blocks);
dbms_output.put_line('Blocks with 00-25% free space   = '||v_fs1_blocks);
dbms_output.put_line('Blocks with 26-50% free space   = '||v_fs2_blocks);
dbms_output.put_line('Blocks with 51-75% free space   = '||v_fs3_blocks);
dbms_output.put_line('Blocks with 76-100% free space = '||v_fs4_blocks);
dbms_output.put_line('Full Blocks                                    = '||v_full_blocks);
end;
/
-- Unformatted Blocks              = 0
-- Blocks with 00-25% free space   = 0
-- Blocks with 26-50% free space   = 0
-- Blocks with 51-75% free space   = 0
-- Blocks with 76-100% free space  = 0
-- Full Blocks                     = 113 delete from test;
-- 9841 rows deleted.
commit;
 
-- Commit complete.select count(*) from user_extents where segment_name = 'TEST';
-- COUNT(*)
-- ----------
--            1
SELECT SUM(BYTES) FROM DBA_FREE_SPACE WHERE TABLESPACE_NAME = 'TEST';
 
-- SUM(BYTES)
-- ----------
-- 207618048 -- NOTE THAT NO SPACE IS FREED
 
declare
v_unformatted_blocks number;
v_unformatted_bytes number;
v_fs1_blocks number;
v_fs1_bytes number;
v_fs2_blocks number;
v_fs2_bytes number;
v_fs3_blocks number;
v_fs3_bytes number;
v_fs4_blocks number;
v_fs4_bytes number;
v_full_blocks number;
v_full_bytes number;
begin
dbms_space.space_usage (
'TEST',
'TEST',
'TABLE',
v_unformatted_blocks,
v_unformatted_bytes,
v_fs1_blocks,
v_fs1_bytes,
v_fs2_blocks,
v_fs2_bytes,
v_fs3_blocks,
v_fs3_bytes,
v_fs4_blocks,
v_fs4_bytes,
v_full_blocks,
v_full_bytes);
dbms_output.put_line('Unformatted Blocks                     = '||v_unformatted_blocks);
dbms_output.put_line('Blocks with 00-25% free space   = '||v_fs1_blocks);
dbms_output.put_line('Blocks with 26-50% free space   = '||v_fs2_blocks);
dbms_output.put_line('Blocks with 51-75% free space   = '||v_fs3_blocks);
dbms_output.put_line('Blocks with 76-100% free space = '||v_fs4_blocks);
dbms_output.put_line('Full Blocks                                    = '||v_full_blocks);
end;
/
-- Unformatted Blocks              = 0
-- Blocks with 00-25% free space   = 0
-- Blocks with 26-50% free space   = 0
-- Blocks with 51-75% free space   = 0
-- Blocks with 76-100% free space  = 113
-- Full Blocks                     = 0
-- NOTE THAT ALL OF OUR SPACE MOVED TO THE fs4 bucket AS WE ARE NOW 100% FREE
 

-- SHRINK THE TABLE
alter table test enable row movement;
-- Table altered.
alter table test shrink space;
 
-- Table altered.
-- EXAMINE THE CHANGE IN STORAGE
select count(*) from user_extents where segment_name = 'TEST';
-- COUNT(*)
-- ----------
-- 1
SELECT SUM(BYTES) FROM DBA_FREE_SPACE WHERE TABLESPACE_NAME = 'TEST';
 
-- SUM(BYTES)
-- ----------
-- 207618048
-- NOTE THAT NO SPACE IS FREED
declare
v_unformatted_blocks number;
v_unformatted_bytes number;
v_fs1_blocks number;
v_fs1_bytes number;
v_fs2_blocks number;
v_fs2_bytes number;
v_fs3_blocks number;
v_fs3_bytes number;
v_fs4_blocks number;
v_fs4_bytes number;
v_full_blocks number;
v_full_bytes number;
begin
dbms_space.space_usage (
'TEST',
'TEST',
'TABLE',
v_unformatted_blocks,
v_unformatted_bytes,
v_fs1_blocks,
v_fs1_bytes,
v_fs2_blocks,
v_fs2_bytes,
v_fs3_blocks,
v_fs3_bytes,
v_fs4_blocks,
v_fs4_bytes,
v_full_blocks,
v_full_bytes);
dbms_output.put_line('Unformatted Blocks                    = '||v_unformatted_blocks);
dbms_output.put_line('Blocks with 00-25% free space  = '||v_fs1_blocks);
dbms_output.put_line('Blocks with 26-50% free space  = '||v_fs2_blocks);
dbms_output.put_line('Blocks with 51-75% free space  = '||v_fs3_blocks);
dbms_output.put_line('Blocks with 76-100% free space = '||v_fs4_blocks);
dbms_output.put_line('Full Blocks                                   = '||v_full_blocks);
end;
/
-- Unformatted Blocks              = 0
-- Blocks with 00-25% free space   = 0
-- Blocks with 26-50% free space   = 0
-- Blocks with 51-75% free space   = 0
-- Blocks with 76-100% free space  = 1
-- Full Blocks                     = 0
-- NOTE THAT THE HIGHWATERMARK FOR THE TABLE HAS BEEN RESET AND WE NOW HAVE ONLY 1 BLOCK

CASE STUDY #3
Demonstrate a shrink of a LOB segment
The photo (1.jpg) used for this case study is 1021884 bytes in size

-- CREATE THE TEST TABLES
CREATE TABLE test ( ID NUMBER, PHOTO BLOB ) ;
CREATE TABLE test_bfile ( B_FILE BFILE) ;
-- CREATE THE DIRECTORY IN WHICH THE BLOB (PHOTO) RESIDES
CREATE or REPLACE DIRECTORY test as '/home/oracle';
-- MODIFY THE LOB TO NOT USE RETENTION OR PCTVERSION (ie remove consistent read copies)
ALTER TABLE TEST MODIFY LOB (PHOTO) (PCTVERSION 0);
-- INSERT THE BFILE LOCATOR FOR THE PHOTO
insert into test_bfile values ( bfilename('TEST','1.jpg'));
commit;
-- INSERT 100 COPIES OF THE PHOTO INTO THE TEST TABLE
declare
    tmp_blob blob default EMPTY_BLOB();
    tmp_bfile bfile:=null;
    dest_offset integer:=1;
    src_offset integer:=1;
begin
     select b_file into tmp_bfile from test_bfile;
     DBMS_LOB.OPEN (tmp_bfile, DBMS_LOB.FILE_READONLY);
     dbms_lob.createtemporary(tmp_blob, TRUE);
     DBMS_LOB.LOADBLOBFROMFILE(tmp_blob,tmp_bfile,DBMS_LOB.LOBMAXSIZE,dest_offset,src_offset);
     for i in 1..100 loop
          insert into test values(i,tmp_blob);
          commit;
     end loop;
     DBMS_LOB.CLOSE(tmp_bfile);
end;
/
-- EXAMINE THE STORAGE USED BY THE PROCESS
column segment_name format a30
set pagesiz 1000
select segment_name, sum(bytes) BYTES, count(*) EXTENTS
from user_extents
group by segment_name;
 
-- SEGMENT_NAME                BYTES    EXTENTS
-- --------------------------------------------------------------
-- SYS_IL0000479629C00002$$      1048576      1
-- SYS_LOB0000479629C00002$$   104857600    100
-- TEST                          1048576      1
-- TEST_BFILE                    1048576      1
SET SERVEROUTPUT ON;
declare
TOTAL_BLOCKS number;
TOTAL_BYTES number;
UNUSED_BLOCKS number;
UNUSED_BYTES number;
LAST_USED_EXTENT_FILE_ID number;
LAST_USED_EXTENT_BLOCK_ID number;
LAST_USED_BLOCK number;
begin dbms_space.unused_space('TEST', 'SYS_LOB0000479629C00002$$', 'LOB',TOTAL_BLOCKS, TOTAL_BYTES, UNUSED_BLOCKS,
                                                          UNUSED_BYTES,LAST_USED_EXTENT_FILE_ID, LAST_USED_EXTENT_BLOCK_ID,
                                                          LAST_USED_BLOCK);
dbms_output.put_line('TOTAL_BLOCKS = '||TOTAL_BLOCKS);
dbms_output.put_line('UNUSED_BLOCKS = '||UNUSED_BLOCKS);
end;
/
 
-- TOTAL_BLOCKS = 12800
-- UNUSED_BLOCKS = 0
-- DELETE 1/3 OF THE ROWS IN OUR TEST TABLE
delete from test where (id/3) = trunc(id/3);
COMMIT;
-- EXAMINE THE CHANGE IN STORAGE
declare
TOTAL_BLOCKS number;
TOTAL_BYTES number;
UNUSED_BLOCKS number;
UNUSED_BYTES number;
LAST_USED_EXTENT_FILE_ID number;
LAST_USED_EXTENT_BLOCK_ID number;
LAST_USED_BLOCK number;
begin dbms_space.unused_space('TEST', 'SYS_LOB0000479629C00002$$', 'LOB',TOTAL_BLOCKS, TOTAL_BYTES, UNUSED_BLOCKS,
                                                           UNUSED_BYTES,LAST_USED_EXTENT_FILE_ID, LAST_USED_EXTENT_BLOCK_ID,
                                                           LAST_USED_BLOCK);
dbms_output.put_line('TOTAL_BLOCKS = '||TOTAL_BLOCKS);
dbms_output.put_line('UNUSED_BLOCKS = '||UNUSED_BLOCKS);
end;
/
 
-- TOTAL_BLOCKS = 12800
-- UNUSED_BLOCKS = 0
-- NOTE ... JUST LIKE WITH NON LOB TABLES ... THE LOB DOES NOT SHOW REDUCTION IN SPACE AFTER A DELETE

-- SHRINK THE TABLE
alter table test enable row movement;
alter table test shrink space cascade;
-- EXAMINE THE CHANGE IN STORAGE
declare
TOTAL_BLOCKS number;
TOTAL_BYTES number;
UNUSED_BLOCKS number;
UNUSED_BYTES number;
LAST_USED_EXTENT_FILE_ID number;
LAST_USED_EXTENT_BLOCK_ID number;
LAST_USED_BLOCK number;
begin dbms_space.unused_space('TEST', 'SYS_LOB0000479629C00002$$', 'LOB',TOTAL_BLOCKS, TOTAL_BYTES, UNUSED_BLOCKS,
                                                          UNUSED_BYTES,LAST_USED_EXTENT_FILE_ID, LAST_USED_EXTENT_BLOCK_ID,
                                                          LAST_USED_BLOCK);
dbms_output.put_line('TOTAL_BLOCKS = '||TOTAL_BLOCKS);
dbms_output.put_line('UNUSED_BLOCKS = '||UNUSED_BLOCKS);
end;
/
 
-- TOTAL_BLOCKS = 8832
-- UNUSED_BLOCKS = 2

WHY WASN'T dbms_space.space_usage USED FOR THIS CASE STUDY?
     LOBs are not stored using blocks ... they are stored using CHUNKS ... as such ... the FS1-FS4 buckets are not maintained for LOB Segments

CASE STUDY #4
-- CREATE THE TEST TABLES
CREATE TABLE test ( ID NUMBER, PHOTO BLOB) LOB (PHOTO) STORE AS SECUREFILE ;
CREATE TABLE test_bfile ( B_FILE BFILE) ;
CREATE or REPLACE DIRECTORY test as '/home/oracle/kbcook';
-- MODIFY THE LOB TO NOT USE RETENTION OR PCTVERSION (ie remove consistent read copies)
ALTER TABLE TEST MODIFY LOB (COL2) (PCTVERSION 0);
-- INSERT THE BFILE LOCATOR FOR THE PHOTO
insert into test_bfile values ( bfilename('TEST','1.jpg'));
commit;
-- INSERT 50 COPIES OF THE PHOTO INTO THE TEST TABLE
declare
tmp_blob blob default EMPTY_BLOB();
tmp_bfile bfile:=null;
dest_offset integer:=1;
src_offset integer:=1;
begin
select b_file into tmp_bfile from test_bfile;
DBMS_LOB.OPEN (tmp_bfile, DBMS_LOB.FILE_READONLY);
dbms_lob.createtemporary(tmp_blob, TRUE);
DBMS_LOB.LOADBLOBFROMFILE(tmp_blob,tmp_bfile,DBMS_LOB.LOBMAXSIZE,dest_offset,src_offset);
for i in 1..50 loop
insert into test values(i,tmp_blob);
commit;
end loop;
DBMS_LOB.CLOSE(tmp_bfile);
end;
/
-- EXAMINE THE STORAGE USED BY THE PROCESS
column segment_name format a30
set pagesiz 1000
select segment_name, sum(bytes) BYTES, count(*) EXTENTS
from user_extents
group by segment_name;
 
-- SEGMENT_NAME                   BYTES      EXTENTS
-- ------------------------------ ---------- ----------
-- SYS_IL0000065938C00002$$            65536          1
-- SYS_LOB0000065938C00002$$        58916864         58
-- TEST                                65536          1
-- TEST_BFILE                          65536          1
-- DELETE 1/2 OF THE ROWS IN OUR TEST TABLE
delete from test where (id/2) = trunc(id/2);
COMMIT;
--SHRINK THE TABLE
alter table test enable row movement;
alter table test shrink space cascade;
-- EXAMINE THE CHANGE IN STORAGE
select segment_name, sum(bytes) BYTES, count(*) EXTENTS
from user_extents
group by segment_name;
 
-- SEGMENT_NAME                   BYTES      EXTENTS
-- ------------------------------ ---------- ----------
-- SYS_IL0000065938C00002$$            65536          1
-- SYS_LOB0000065938C00002$$        58916864         58
-- TEST                                65536          1
-- TEST_BFILE                          65536          1
WHY DOES NOT WORK WITH SECUREFILES?
Per ... Oracle?? Database Administrator's Guide 11g Release 2 (11.2) Part Number E25494-01
Shrinking Database Segments Online
...
Shrink operations can be performed only on segments in locally managed tablespaces with automatic segment space management (ASSM). Within an ASSM tablespace, all segment types are eligible for online segment shrink except these:
     IOT mapping tables
     Tables with rowid based materialized views
     Tables with function-based indexes
     SECUREFILE LOBs
     Compressed tables
 
CASE STUDY #5
Demonstrate a shrink occuring in an Index-Organized Table (IOT)
In addtion ... demonstrate that an IOT actually stores its data in the Index segment
         rather than the Table segment
-- CREATE OUR TEST TABLE
CREATE TABLE TEST(
   COL1 NUMBER,
   COL2 VARCHAR2(10),
   CONSTRAINT TEST_PK
   PRIMARY KEY (COL1))
ORGANIZATION INDEX
INCLUDING COL2
PCTTHRESHOLD 2
OVERFLOW;

-- EXAMINE THE STORAGE FOR THE TEST TABLE
COLUMN SEGMENT_NAME FORMAT A30
SELECT SEGMENT_NAME, SEGMENT_TYPE FROM USER_SEGMENTS;
 
-- SEGMENT_NAME         SEGMENT_TYPE
-- -------------------- --------------------------
-- TEST_PK              INDEX
-- SYS_IOT_OVER_479639  TABLE
SELECT SEGMENT_NAME, BYTES, SEGMENT_TYPE FROM USER_EXTENTS;
 
-- SEGMENT_NAME        BYTES   SEGMENT_TYPE
-- ------------------- ------- ----------------------------
-- SYS_IOT_OVER_479639 1048576 TABLE
-- TEST_PK             1048576 INDEX
-- INSERT 100,000 ROWS INTO THE TEST TABLE
begin
    for i in 1..100000 loop
        insert into test values(i,to_char(i));
    end loop;
    commit;
end;
/
-- EXAMINE THE CHANGE IN STORAGE
SELECT SEGMENT_NAME, BYTES, SEGMENT_TYPE FROM USER_EXTENTS;
 
-- SEGMENT_NAME        BYTES   SEGMENT_TYPE
-- ------------------- ------- ----------------------------
-- SYS_IOT_OVER_479639 1048576 TABLE
-- TEST_PK             1048576 INDEX
-- TEST_PK             1048576 INDEX
declare
TOTAL_BLOCKS number;
TOTAL_BYTES number;
UNUSED_BLOCKS number;
UNUSED_BYTES number;
LAST_USED_EXTENT_FILE_ID number;
LAST_USED_EXTENT_BLOCK_ID number;
LAST_USED_BLOCK number;
begin dbms_space.unused_space('TEST', 'TEST', 'TABLE',TOTAL_BLOCKS, TOTAL_BYTES, UNUSED_BLOCKS,
                                                          UNUSED_BYTES,LAST_USED_EXTENT_FILE_ID, LAST_USED_EXTENT_BLOCK_ID,
                                                          LAST_USED_BLOCK);
dbms_output.put_line('TOTAL_BLOCKS = '||TOTAL_BLOCKS);
dbms_output.put_line('UNUSED_BLOCKS = '||UNUSED_BLOCKS);
end;
/
-- TOTAL_BLOCKS = 256
-- UNUSED_BLOCKS = 0

declare
TOTAL_BLOCKS number;
TOTAL_BYTES number;
UNUSED_BLOCKS number;
UNUSED_BYTES number;
LAST_USED_EXTENT_FILE_ID number;
LAST_USED_EXTENT_BLOCK_ID number;
LAST_USED_BLOCK number;
begin dbms_space.unused_space('TEST', 'TEST_PK', 'INDEX',TOTAL_BLOCKS, TOTAL_BYTES, UNUSED_BLOCKS,
                                                          UNUSED_BYTES,LAST_USED_EXTENT_FILE_ID, LAST_USED_EXTENT_BLOCK_ID,
                                                          LAST_USED_BLOCK);
dbms_output.put_line('TOTAL_BLOCKS = '||TOTAL_BLOCKS);
dbms_output.put_line('UNUSED_BLOCKS = '||UNUSED_BLOCKS);
end;
/
 
-- TOTAL_BLOCKS = 256
-- UNUSED_BLOCKS = 0
-- NOTE THAT THE STORAGE IS THE SAME FOR test (table) AND FOR test_pk (index)

declare
v_unformatted_blocks number;
v_unformatted_bytes number;
v_fs1_blocks number;
v_fs1_bytes number;
v_fs2_blocks number;
v_fs2_bytes number;
v_fs3_blocks number;
v_fs3_bytes number;
v_fs4_blocks number;
v_fs4_bytes number;
v_full_blocks number;
v_full_bytes number;
begin
dbms_space.space_usage (
'TEST',
'TEST',
'TABLE',
v_unformatted_blocks,
v_unformatted_bytes,
v_fs1_blocks,
v_fs1_bytes,
v_fs2_blocks,
v_fs2_bytes,
v_fs3_blocks,
v_fs3_bytes,
v_fs4_blocks,
v_fs4_bytes,
v_full_blocks,
v_full_bytes);
dbms_output.put_line('Unformatted Blocks                     = '||v_unformatted_blocks);
dbms_output.put_line('Blocks with 00-25% free space   = '||v_fs1_blocks);
dbms_output.put_line('Blocks with 26-50% free space   = '||v_fs2_blocks);
dbms_output.put_line('Blocks with 51-75% free space   = '||v_fs3_blocks);
dbms_output.put_line('Blocks with 76-100% free space = '||v_fs4_blocks);
dbms_output.put_line('Full Blocks                                    = '||v_full_blocks);
end;
/
 
-- Unformatted Blocks              = 0
-- Blocks with 00-25% free space   = 0
-- Blocks with 26-50% free space   = 27
-- Blocks with 51-75% free space   = 0
-- Blocks with 76-100% free space  = 0
-- Full Blocks                     = 223

declare
v_unformatted_blocks number;
v_unformatted_bytes number;
v_fs1_blocks number;
v_fs1_bytes number;
v_fs2_blocks number;
v_fs2_bytes number;
v_fs3_blocks number;
v_fs3_bytes number;
v_fs4_blocks number;
v_fs4_bytes number;
v_full_blocks number;
v_full_bytes number;
begin
dbms_space.space_usage (
'TEST',
'TEST_PK',
'INDEX',
v_unformatted_blocks,
v_unformatted_bytes,
v_fs1_blocks,
v_fs1_bytes,
v_fs2_blocks,
v_fs2_bytes,
v_fs3_blocks,
v_fs3_bytes,
v_fs4_blocks,
v_fs4_bytes,
v_full_blocks,
v_full_bytes);
dbms_output.put_line('Unformatted Blocks                     = '||v_unformatted_blocks);
dbms_output.put_line('Blocks with 00-25% free space   = '||v_fs1_blocks);
dbms_output.put_line('Blocks with 26-50% free space   = '||v_fs2_blocks);
dbms_output.put_line('Blocks with 51-75% free space   = '||v_fs3_blocks);
dbms_output.put_line('Blocks with 76-100% free space = '||v_fs4_blocks);
dbms_output.put_line('Full Blocks                                    = '||v_full_blocks);
end;
/
 
-- Unformatted Blocks              = 0
-- Blocks with 00-25% free space   = 0
-- Blocks with 26-50% free space   = 27
-- Blocks with 51-75% free space   = 0
-- Blocks with 76-100% free space  = 0
-- Full Blocks                     = 223
-- NOTE THAT THE STORAGE IS THE SAME FOR test (table) AND FOR test_pk (index)
 
-- DELETE 50% OF THE ROWS IN THE TEST TABLE

DELETE FROM TEST WHERE (COL1/2) = TRUNC(COL1/2);
-- 50000 rows deleted.
COMMIT;
 
-- EXAMINE THE CHANGE IN STORAGE
declare
TOTAL_BLOCKS number;
TOTAL_BYTES number;
UNUSED_BLOCKS number;
UNUSED_BYTES number;
LAST_USED_EXTENT_FILE_ID number;
LAST_USED_EXTENT_BLOCK_ID number;
LAST_USED_BLOCK number;
begin dbms_space.unused_space('TEST', 'TEST', 'TABLE',TOTAL_BLOCKS, TOTAL_BYTES, UNUSED_BLOCKS,
                                                          UNUSED_BYTES,LAST_USED_EXTENT_FILE_ID, LAST_USED_EXTENT_BLOCK_ID,
                                                          LAST_USED_BLOCK);
dbms_output.put_line('TOTAL_BLOCKS = '||TOTAL_BLOCKS);
dbms_output.put_line('UNUSED_BLOCKS = '||UNUSED_BLOCKS);
end;
/

-- TOTAL_BLOCKS = 256
-- UNUSED_BLOCKS = 0
declare
TOTAL_BLOCKS number;
TOTAL_BYTES number;
UNUSED_BLOCKS number;
UNUSED_BYTES number;
LAST_USED_EXTENT_FILE_ID number;
LAST_USED_EXTENT_BLOCK_ID number;
LAST_USED_BLOCK number;
begin dbms_space.unused_space('TEST', 'TEST_PK', 'INDEX',TOTAL_BLOCKS, TOTAL_BYTES, UNUSED_BLOCKS,
                                                           UNUSED_BYTES,LAST_USED_EXTENT_FILE_ID, LAST_USED_EXTENT_BLOCK_ID,
                                                           LAST_USED_BLOCK);
dbms_output.put_line('TOTAL_BLOCKS = '||TOTAL_BLOCKS);
dbms_output.put_line('UNUSED_BLOCKS = '||UNUSED_BLOCKS);
end;
/

-- TOTAL_BLOCKS = 256
-- UNUSED_BLOCKS = 0
-- NOTE THAT THE STORAGE IS THE SAME FOR test (table) AND FOR test_pk (index)

declare
v_unformatted_blocks number;
v_unformatted_bytes number;
v_fs1_blocks number;
v_fs1_bytes number;
v_fs2_blocks number;
v_fs2_bytes number;
v_fs3_blocks number;
v_fs3_bytes number;
v_fs4_blocks number;
v_fs4_bytes number;
v_full_blocks number;
v_full_bytes number;
begin
dbms_space.space_usage (
'TEST',
'TEST',
'TABLE',
v_unformatted_blocks,
v_unformatted_bytes,
v_fs1_blocks,
v_fs1_bytes,
v_fs2_blocks,
v_fs2_bytes,
v_fs3_blocks,
v_fs3_bytes,
v_fs4_blocks,
v_fs4_bytes,
v_full_blocks,
v_full_bytes);
dbms_output.put_line('Unformatted Blocks                     = '||v_unformatted_blocks);
dbms_output.put_line('Blocks with 00-25% free space   = '||v_fs1_blocks);
dbms_output.put_line('Blocks with 26-50% free space   = '||v_fs2_blocks);
dbms_output.put_line('Blocks with 51-75% free space   = '||v_fs3_blocks);
dbms_output.put_line('Blocks with 76-100% free space = '||v_fs4_blocks);
dbms_output.put_line('Full Blocks                                    = '||v_full_blocks);
end;
/
 
-- Unformatted Blocks              = 0
-- Blocks with 00-25% free space   = 0
-- Blocks with 26-50% free space   = 27
-- Blocks with 51-75% free space   = 0
-- Blocks with 76-100% free space  = 0
-- Full Blocks                     = 223

declare
v_unformatted_blocks number;
v_unformatted_bytes number;
v_fs1_blocks number;
v_fs1_bytes number;
v_fs2_blocks number;
v_fs2_bytes number;
v_fs3_blocks number;
v_fs3_bytes number;
v_fs4_blocks number;
v_fs4_bytes number;
v_full_blocks number;
v_full_bytes number;
begin
dbms_space.space_usage (
'TEST',
'TEST_PK',
'INDEX',
v_unformatted_blocks,
v_unformatted_bytes,
v_fs1_blocks,
v_fs1_bytes,
v_fs2_blocks,
v_fs2_bytes,
v_fs3_blocks,
v_fs3_bytes,
v_fs4_blocks,
v_fs4_bytes,
v_full_blocks,
v_full_bytes);
dbms_output.put_line('Unformatted Blocks                     = '||v_unformatted_blocks);
dbms_output.put_line('Blocks with 00-25% free space   = '||v_fs1_blocks);
dbms_output.put_line('Blocks with 26-50% free space   = '||v_fs2_blocks);
dbms_output.put_line('Blocks with 51-75% free space   = '||v_fs3_blocks);
dbms_output.put_line('Blocks with 76-100% free space = '||v_fs4_blocks);
dbms_output.put_line('Full Blocks                                    = '||v_full_blocks);
end;
/
 
-- Unformatted Blocks              = 0
-- Blocks with 00-25% free space   = 0
-- Blocks with 26-50% free space   = 27
-- Blocks with 51-75% free space   = 0
-- Blocks with 76-100% free space  = 0
-- Full Blocks                     = 223
-- NOTE THAT THE STORAGE IS THE SAME FOR test (table) AND FOR test_pk (index)
 
-- SHRINK THE TEST TABLE

SQL> ALTER TABLE TEST ENABLE ROW MOVEMENT;
 
ALTER TABLE TEST ENABLE ROW MOVEMENT
*
ERROR at line 1:
ORA-14066: illegal option for a non-partitioned index-organized table
-- REMEMBER THE RESTRICTION DETAILED ABOVE ABOUT ROW MOVEMENT AND IOT'S?
 

ALTER TABLE TEST SHRINK SPACE;
-- Table altered.
 
-- EXAMINE THE CHANGE IN STORAGE
declare
TOTAL_BLOCKS number;
TOTAL_BYTES number;
UNUSED_BLOCKS number;
UNUSED_BYTES number;
LAST_USED_EXTENT_FILE_ID number;
LAST_USED_EXTENT_BLOCK_ID number;
LAST_USED_BLOCK number;
begin dbms_space.unused_space('TEST', 'TEST', 'TABLE',TOTAL_BLOCKS, TOTAL_BYTES, UNUSED_BLOCKS,
                                                           UNUSED_BYTES,LAST_USED_EXTENT_FILE_ID, LAST_USED_EXTENT_BLOCK_ID,
                                                            LAST_USED_BLOCK);
dbms_output.put_line('TOTAL_BLOCKS = '||TOTAL_BLOCKS);
dbms_output.put_line('UNUSED_BLOCKS = '||UNUSED_BLOCKS);
end;
/
 
-- TOTAL_BLOCKS = 256
-- UNUSED_BLOCKS = 124
declare
TOTAL_BLOCKS number;
TOTAL_BYTES number;
UNUSED_BLOCKS number;
UNUSED_BYTES number;
LAST_USED_EXTENT_FILE_ID number;
LAST_USED_EXTENT_BLOCK_ID number;
LAST_USED_BLOCK number;
begin dbms_space.unused_space('TEST', 'TEST_PK', 'INDEX',TOTAL_BLOCKS, TOTAL_BYTES, UNUSED_BLOCKS,
                                                          UNUSED_BYTES,LAST_USED_EXTENT_FILE_ID, LAST_USED_EXTENT_BLOCK_ID,
                                                          LAST_USED_BLOCK);
dbms_output.put_line('TOTAL_BLOCKS = '||TOTAL_BLOCKS);
dbms_output.put_line('UNUSED_BLOCKS = '||UNUSED_BLOCKS);
end;
/
 
-- TOTAL_BLOCKS = 256
-- UNUSED_BLOCKS = 124
-- NOTE THAT THE STORAGE IS THE SAME FOR test (table) AND FOR test_pk (index)
 
declare
v_unformatted_blocks number;
v_unformatted_bytes number;
v_fs1_blocks number;
v_fs1_bytes number;
v_fs2_blocks number;
v_fs2_bytes number;
v_fs3_blocks number;
v_fs3_bytes number;
v_fs4_blocks number;
v_fs4_bytes number;
v_full_blocks number;
v_full_bytes number;
begin
dbms_space.space_usage (
'TEST',
'TEST',
'TABLE',
v_unformatted_blocks,
v_unformatted_bytes,
v_fs1_blocks,
v_fs1_bytes,
v_fs2_blocks,
v_fs2_bytes,
v_fs3_blocks,
v_fs3_bytes,
v_fs4_blocks,
v_fs4_bytes,
v_full_blocks,
v_full_bytes);
dbms_output.put_line('Unformatted Blocks                     = '||v_unformatted_blocks);
dbms_output.put_line('Blocks with 00-25% free space   = '||v_fs1_blocks);
dbms_output.put_line('Blocks with 26-50% free space   = '||v_fs2_blocks);
dbms_output.put_line('Blocks with 51-75% free space   = '||v_fs3_blocks);
dbms_output.put_line('Blocks with 76-100% free space = '||v_fs4_blocks);
dbms_output.put_line('Full Blocks                                    = '||v_full_blocks);
end;
/
 
-- Unformatted Blocks              = 0
-- Blocks with 00-25% free space   = 0
-- Blocks with 26-50% free space   = 0
-- Blocks with 51-75% free space   = 0
-- Blocks with 76-100% free space  = 0
-- Full Blocks                     = 126
declare
v_unformatted_blocks number;
v_unformatted_bytes number;
v_fs1_blocks number;
v_fs1_bytes number;
v_fs2_blocks number;
v_fs2_bytes number;
v_fs3_blocks number;
v_fs3_bytes number;
v_fs4_blocks number;
v_fs4_bytes number;
v_full_blocks number;
v_full_bytes number;
begin
dbms_space.space_usage (
'TEST',
'TEST_PK',
'INDEX',
v_unformatted_blocks,
v_unformatted_bytes,
v_fs1_blocks,
v_fs1_bytes,
v_fs2_blocks,
v_fs2_bytes,
v_fs3_blocks,
v_fs3_bytes,
v_fs4_blocks,
v_fs4_bytes,
v_full_blocks,
v_full_bytes);
dbms_output.put_line('Unformatted Blocks                      = '||v_unformatted_blocks);
dbms_output.put_line('Blocks with 00-25% free space   = '||v_fs1_blocks);
dbms_output.put_line('Blocks with 26-50% free space   = '||v_fs2_blocks);
dbms_output.put_line('Blocks with 51-75% free space   = '||v_fs3_blocks);
dbms_output.put_line('Blocks with 76-100% free space = '||v_fs4_blocks);
dbms_output.put_line('Full Blocks                                    = '||v_full_blocks);
end;
/
 
-- Unformatted Blocks              = 0
-- Blocks with 00-25% free space   = 0
-- Blocks with 26-50% free space   = 0
-- Blocks with 51-75% free space   = 0
-- Blocks with 76-100% free space  = 0
-- Full Blocks                     = 126
-- NOTE THAT THE STORAGE IS THE SAME FOR test (table) AND FOR test_pk (index)
 
SELECT SEGMENT_NAME, SEGMENT_TYPE FROM USER_SEGMENTS;
 
-- SEGMENT_NAME       SEGMENT_TYPE
-- ------------------- -----------
-- TEST_PK             INDEX
-- SYS_IOT_OVER_479639 TABLE
SELECT SEGMENT_NAME, BYTES, SEGMENT_TYPE FROM USER_EXTENTS;
 
-- SEGMENT_NAME        BYTES    SEGMENT_TYPE
-- ---------------------------- ------------------
-- SYS_IOT_OVER_479639 1048576 TABLE
-- TEST_PK             1048576 INDEX
-- TEST_PK             1048576 INDEX

-- CLEANUP FROM CASE STUDIES
connect / as sysdba;
drop user test cascade;
drop tablespace test including contents and datafiles;
References
NOTE:116923.1 - DBMS_SPACE.UNUSED_SPACE Usage
NOTE:1394613.1 - How to Shrink a SECUREFILE LOB Using Online Redefinition (DBMS_REDEFINITION)?
NOTE:149516.1 - BMB versus Freelist Segment: DBMS_SPACE.UNUSED_SPACE and DBA_TABLES.EMPTY_BLOCKS
NOTE:386341.1 - How to determine the actual size of the LOB segments and how to free the deleted/unused space above/below the HWM

    相关内容 … 
  
   产品 … 
  
?Oracle Database Products > Oracle Database > Oracle Database > Oracle Database - Enterprise Edition > RDBMS > Segment Storage
   关键字 … 
  
HIGHWATER   错误 …  
  

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/17252115/viewspace-774027/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论