Shrink a SECUREFILE LOB Using Online Redefinition (DBMS_REDEFINITION)?1394613.1

rongshiyuan發表於2013-10-10
How to Shrink a SECUREFILE LOB Using Online Redefinition (DBMS_REDEFINITION)? (文件 ID 1394613.1)
 

In this Document
  Goal
  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: 11.1.0.6 to 11.2.0.3 - Release: 11.1 to 11.2
Information in this document applies to any platform.

Goal

Shrink a SECUREFILE LOB using DBMS_REDEFINITION


Solution


SECUREFILE LOB segments cannot be shrunk using ALTER TABLE ... SHRINK SPACE CASCADE

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

This limitation causes users to have to revert to using other methods of shrinking LOBs

* Export / drop the table / import
* CREATE TABLE ... AS SELECT
* ALTER TABLE ... MOVE (see Doc id: 1396120.1 for example)

All of these options require that the table containing the SECUREFILE LOB be offline for a period of time ... this is not very desirable

Online redefinition using DBMS_REDEFINITION can be used to shrink the size of SECUREFILE LOBs if there is sufficient disk space available to hold both the 'unshrunk' table as well as the 'shrunk' table


CASE STUDY

create user test identified by test;
grant dba to test;
alter user test default tablespace users;
connect test/test;

-- 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
-------------------------- ---------- ----------
TEST                          65536            1
TEST_BFILE                    65536            1
SYS_IL0000066057C00002$$      65536            1
SYS_LOB0000066057C00002$$  58851328           57

--NOTE the SECUREFILE LOB HAS 57 EXTENTS AFTER THE INSERT

-- DELETE 1/2 OF THE ROWS IN THE TEST TABLE TO FREE UP SPACE

delete from test where (id/2) = trunc(id/2);

COMMIT;

-- DEMONSTRATE THAT ALTER TABLE ... SHRINK SPACE CASCADE ... DOES NOT WORK WITH SECUREFILE LOBS

alter table test enable row movement;

alter table test shrink space cascade;

select segment_name, sum(bytes) BYTES, count(*) EXTENTS
from user_extents
group by segment_name;

SEGMENT_NAME               BYTES      EXTENTS
-------------------------- ---------- ----------
TEST                            65536          1
TEST_BFILE                      65536          1
SYS_IL0000066057C00002$$        65536          1
SYS_LOB0000066057C00002$$    58851328         57

NOTE: There was no change in storage

-- *** REDEFINE THE TABLE USING DBMS_REDEFINITION ***

-- DETERMINE IF THE TABLE CAN BE REDEFINED ONLINE

SET SERVEROUTPUT ON

BEGIN
    DBMS_REDEFINITION.CAN_REDEF_TABLE('TEST','TEST', DBMS_REDEFINITION.CONS_USE_ROWID);
END;
/

PL/SQL procedure successfully completed.

-- CREATE THE INTERIM TABLE

CREATE TABLE INTERIM AS SELECT * FROM TEST WHERE 1=2;

-- START THE REDEFINITION

BEGIN
   DBMS_REDEFINITION.START_REDEF_TABLE(
                uname => 'TEST',
                orig_table => 'TEST',
                int_table => 'INTERIM',
                options_flag => DBMS_REDEFINITION.CONS_USE_ROWID);
END;
/

-- FINISH THE REDEFINITION

exec DBMS_REDEFINITION.FINISH_REDEF_TABLE('TEST','TEST','INTERIM');

-- EXAMINE THE STORAGE AFTER THE REDEFINITION

select segment_name, sum(bytes) BYTES, count(*) EXTENTS
from user_extents
group by segment_name
ORDER BY 1;

SEGMENT_NAME                   BYTES      EXTENTS
------------------------------ ---------- ----------
INTERIM                        65536               1
SYS_IL0000066057C00002$$       65536               1
SYS_IL0000066061C00002$$       65536               1
SYS_LOB0000066057C00002$$   58851328              57
SYS_LOB0000066061C00002$$   27262976              41
TEST                           65536               1
TEST_BFILE                     65536               1

NOTE:  The new SECUREFILE LOB has 41 extents whereas the original has 57 extents ... thus the segment has been shrunk

References

NOTE:1357825.1 - Master Note: An overview of online redefinition of tables (DBMS_REDEFINITION)
NOTE:820043.1 - Why is no space released after an ALTER TABLE ... SHRINK?

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/17252115/viewspace-774028/，如需轉載，請註明出處，否則將追究法律責任。

相關文章