Why is no space released after an ALTER TABLE ... SHRINK?

  • xychong123

    2017-01-03 14:37:20
  • Oracle
  • 原创

APPLIES TO:

Oracle Database - Enterprise Edition - Version 10.1.0.2 and later
Information in this document applies to any platform.



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;