oracle shrink table command,oracle free space in table – complete reference for the new oracle dba

you are a new oracle dba and you want to find out how much free space exists in your table segment. you also know that if you shrink a table then you can free some extents. your manager asked you to shrink some huge tables and so you can save lot of space. you want to do the shrink but you dont know how to do it.

Below collection of articles explain in detail about how you can shrink a table and about how you can find the free space in a table and has different case studies on how to manage the space of different types of oracle objects.

Main thing to remember is that you cant shrink cluster tables.

Subject:	Why is no space released after an ALTER TABLE … SHRINK?
	Doc ID:	820043.1	Type:	PROBLEM
	Modified Date:	18-SEP-2009	Status:	MODERATED

In this Document
  Symptoms
  Cause
  Solution
  References

This document is being delivered to you via Oracle Support’s Rapid Visibility (RaV) process, and therefore has not been subject to an independent technical review.

Applies to:

Oracle Server – Enterprise Edition – Version: 10.1.0.2 to 11.2.0.2
This problem can occur on 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(
         ‘<SCHEMA>’,
         ‘<OBJECT NAME>’,
         ‘<OBJECT TYPE>’,
         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(‘OBJECT_NAME = FREELIST_T’);
     dbms_output.put_line(‘———————————–‘);
     dbms_output.put_line(‘TOTAL_BLOCKS = ‘||TOTAL_BLOCKS);
     dbms_output.put_line(‘UNUSED_BLOCKS = ‘||UNUSED_BLOCKS);
end;
/

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(

‘UD2’,

‘UDICE’,

‘TABLE PARTITION’,

TOTAL_BLOCKS,

TOTAL_BYTES,

UNUSED_BLOCKS,

UNUSED_BYTES,

LAST_USED_EXTENT_FILE_ID,

LAST_USED_EXTENT_BLOCK_ID,

LAST_USED_BLOCK,

‘T_UD2_DND_200907’);

dbms_output.put_line(‘OBJECT_NAME = FREELIST_T’);

dbms_output.put_line(‘———————————–‘);

dbms_output.put_line(‘TOTAL_BLOCKS = ‘||TOTAL_BLOCKS);

dbms_output.put_line(‘UNUSED_BLOCKS = ‘||UNUSED_BLOCKS);

end;

OBJECT_NAME = FREELIST_T

———————————–

TOTAL_BLOCKS = 770048

UNUSED_BLOCKS = 0

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 (
       ‘<SCHEMA>’,
       ‘<OBJECT NAME>’,
       ‘<OBJECT TYPE>’,
       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
CASE STUDY #4 – Demonstrate a shrink occuring in an Index-Organized Table (IOT)

Setup for case study

create tablespace test datafile ‘<path>/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(‘OBJECT_NAME = FREELIST_T’);
dbms_output.put_line(‘———————————–‘);
dbms_output.put_line(‘TOTAL_BLOCKS = ‘||TOTAL_BLOCKS);
dbms_output.put_line(‘UNUSED_BLOCKS = ‘||UNUSED_BLOCKS);
end;
/

— OBJECT_NAME = FREELIST_T
— ———————————–
— 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(‘OBJECT_NAME = FREELIST_T’);
dbms_output.put_line(‘———————————–‘);
dbms_output.put_line(‘TOTAL_BLOCKS = ‘||TOTAL_BLOCKS);
dbms_output.put_line(‘UNUSED_BLOCKS = ‘||UNUSED_BLOCKS);
end;
/

— OBJECT_NAME = FREELIST_T
— ———————————–
— 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(‘OBJECT_NAME = FREELIST_T’);
dbms_output.put_line(‘———————————–‘);
dbms_output.put_line(‘TOTAL_BLOCKS = ‘||TOTAL_BLOCKS);
dbms_output.put_line(‘UNUSED_BLOCKS = ‘||UNUSED_BLOCKS);
end;
/

— OBJECT_NAME = FREELIST_T
— ———————————–
— 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

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

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

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(‘OBJECT_NAME = FREELIST_T’);
dbms_output.put_line(‘———————————–‘);
dbms_output.put_line(‘TOTAL_BLOCKS = ‘||TOTAL_BLOCKS);
dbms_output.put_line(‘UNUSED_BLOCKS = ‘||UNUSED_BLOCKS);
end;
/

— OBJECT_NAME = FREELIST_T
— ———————————–
— 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;

dbms_output.put_line(‘OBJECT_NAME = FREELIST_T’);
dbms_output.put_line(‘———————————–‘);
dbms_output.put_line(‘TOTAL_BLOCKS = ‘||TOTAL_BLOCKS);
dbms_output.put_line(‘UNUSED_BLOCKS = ‘||UNUSED_BLOCKS);
end;
/

— OBJECT_NAME = FREELIST_T
— ———————————–
— 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;

dbms_output.put_line(‘OBJECT_NAME = FREELIST_T’);
dbms_output.put_line(‘———————————–‘);
dbms_output.put_line(‘TOTAL_BLOCKS = ‘||TOTAL_BLOCKS);
dbms_output.put_line(‘UNUSED_BLOCKS = ‘||UNUSED_BLOCKS);
end;
/

— OBJECT_NAME = FREELIST_T
— ————————-
— 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

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(‘OBJECT_NAME = FREELIST_T’);
dbms_output.put_line(‘———————————–‘);
dbms_output.put_line(‘TOTAL_BLOCKS = ‘||TOTAL_BLOCKS);
dbms_output.put_line(‘UNUSED_BLOCKS = ‘||UNUSED_BLOCKS);
end;
/

— OBJECT_NAME = FREELIST_T
— ———————————–
— 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(‘OBJECT_NAME = FREELIST_T’);
dbms_output.put_line(‘———————————–‘);
dbms_output.put_line(‘TOTAL_BLOCKS = ‘||TOTAL_BLOCKS);
dbms_output.put_line(‘UNUSED_BLOCKS = ‘||UNUSED_BLOCKS);
end;
/

— OBJECT_NAME = FREELIST_T
— ———————————–
— TOTAL_BLOCKS = 256
— UNUSED_BLOCKS = 0

— NOTE THAT THE STORAGE IS THE SAME FOR test (table) AND FOR test_pk (index)

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

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.