ITPub博客

首页 > Linux操作系统 > Linux操作系统 > ALTER TABLE...MOVE To Shrink A Table (BASICFILE/SECUREFILE LOBs)-1396120.1

ALTER TABLE...MOVE To Shrink A Table (BASICFILE/SECUREFILE LOBs)-1396120.1

原创 Linux操作系统 作者:rongshiyuan 时间:2013-10-10 05:01:26 0 删除 编辑

How To Use ALTER TABLE ... MOVE To Shrink A Table (Including BASICFILE / SECUREFILE LOBs) (文档 ID 1396120.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

This note will demonstrate the use of ALTER TABLE ... MOVE to shrink a table and its LOB segment

Solution

This method can be used with tables that have sufficient free space such that at least one extent can be deallocated for the table and or LOB segment

This method can also be used in place of ALTER TABLE ... SHRINK SPACE CASCADE when SECUREFILE LOBs are used as SECUREFILES cannot be shrunk with this command

The case studies for this note were executed on Oracle 11.2.0.2 on Linux 32 bit

CASE STUDY #1 - SYSTEM GERNEATED EXTENT SIZE (AUTOALLOCATE) - AUTOMATIC SEGMENT SPACE MANAGEMENT (ASSM) TABLESPACE - SECUREFILE LOB
CASE STUDY #2 - UNIFORM. EXTENT SIZE - AUTOMATIC SEGMENT SPACE MANAGEMENT (ASSM) TABLESPACE - SECUREFILE LOB
CASE STUDY #3 - SYSTEM GERNEATED EXTENT SIZE (AUTOALLOCATE) MANUAL SEGMENT SPACE MANAGEMENT (MSSM) TABLESPACE - BASICFILE LOB
CASE STUDY #4 - UNIFORM. EXTENT SIZE - MANUAL SEGMENT SPACE MANAGEMENT (MSSM) TABLESPACE - BASICFILE LOB

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

CASE STUDY #1 - SYSTEM GERNEATED EXTENT SIZE (AUTOALLOCATE) - AUTOMATIC SEGMENT SPACE MANAGEMENT (ASSM) TABLESPACE - SECUREFILE LOB


-- CREATE THE TABLESPACES FOR THE TEST

CREATE TABLESPACE TEST_AUTO DATAFILE '/u01/app/oracle/oradata/v11202/TEST_AUTO.DBF' SIZE 100M;

-- CREATE THE USER FOR THE TEST

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

-- CREATE THE TEST TABLES

CREATE TABLE test ( ID NUMBER, V_DATA VARCHAR2(4000), PHOTO BLOB) LOB (PHOTO) STORE AS SECUREFILE ;
CREATE TABLE test_bfile ( B_FILE BFILE) ;

CREATE or REPLACE DIRECTORY test as '/home/oracle/kbcook';

-- 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;
   tmp_varchar varchar2(4000):=null;
   dest_offset integer:=1;
   src_offset integer:=1;
begin
   for i in 1..4000 loop
      TMP_VARCHAR := TMP_VARCHAR||'A';
   end loop;
   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_varchar, tmp_blob);
   commit;
   end loop;
   DBMS_LOB.CLOSE(tmp_bfile);
end;
/

-- EXAMINE THE STORAGE CREATED BY THE PROCESS

column segment_name format a30
column tablespace_name format a13
set pagesiz 1000

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

SEGMENT_NAME                   BYTES      TABLESPACE_NA EXTENTS
------------------------------ ---------- ------------- ----------
SYS_IL0000066119C00003$$            65536 TEST_AUTO              1
SYS_LOB0000066119C00003$$        58851328 TEST_AUTO             57
TEST                               458752 TEST_AUTO              7
TEST_BFILE                          65536 TEST_AUTO              1


-- DELETE 1/2 OF THE ROWS IN THE TEST TABLE

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

COMMIT;

-- SHOW THAT NO CHANGE HAS OCCURRED WITH THE STORAGE FOR THE TABLE

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

SEGMENT_NAME                   BYTES      TABLESPACE_NA EXTENTS
------------------------------ ---------- ------------- ----------
SYS_IL0000066119C00003$$       65536      TEST_AUTO              1
SYS_LOB0000066119C00003$$   58851328      TEST_AUTO             57
TEST                          458752      TEST_AUTO              7
TEST_BFILE                     65536      TEST_AUTO              1


-- MOVE THE TABLE FROM TABLESPACE TEST_AUTO TO TEST_AUTO AND EXAMINE THE STORAGE

ALTER TABLE TEST MOVE TABLESPACE TEST_AUTO;

ALTER TABLE TEST MOVE LOB(PHOTO) STORE AS SECUREFILE (TABLESPACE TEST_AUTO);

-- EXAMINE THE STORAGE AFTER THE MOVE

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

SEGMENT_NAME                   BYTES      TABLESPACE_NA EXTENTS
------------------------------ ---------- ------------- ----------
SYS_IL0000066119C00003$$            65536 TEST_AUTO              1
SYS_LOB0000066119C00003$$        27394048 TEST_AUTO             27
TEST                               262144 TEST_AUTO              4
TEST_BFILE                          65536 TEST_AUTO              1


-- NOTICE THAT
--                       TEST (TABLE SEGMENT) HAS SHRUNK FROM 7 EXTENTS TO 4 EXTENTS
--                       THE LOB SEGMENT HAS SHRUNK FROM 57 TO 27 EXTENTS


-- CLEANUP

CONNECT / AS SYSDBA;

DROP USER TEST CASCADE;
DROP TABLESPACE TEST_AUTO INCLUDING CONTENTS AND DATAFILES;


-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

CASE STUDY #2 - UNIFORM. EXTENT SIZE - AUTOMATIC SEGMENT SPACE MANAGEMENT (ASSM) TABLESPACE - SECUREFILE LOB

-- CREATE THE TABLESPACES FOR THE TEST

CREATE TABLESPACE TEST_UNIFORM. DATAFILE '/u01/app/oracle/oradata/v11202/TEST_UNIFORM.DBF' SIZE 100M EXTENT MANAGEMENT LOCAL UNIFORM. SIZE 1M;

-- CREATE THE USER FOR THE TEST

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

-- CREATE THE TEST TABLES

CREATE TABLE test ( ID NUMBER, V_DATA VARCHAR2(4000), PHOTO BLOB) LOB (PHOTO) STORE AS SECUREFILE ;
CREATE TABLE test_bfile ( B_FILE BFILE) ;

CREATE or REPLACE DIRECTORY test as '/home/oracle/kbcook';

-- 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;
   tmp_varchar varchar2(4000):=null;
   dest_offset integer:=1;
   src_offset integer:=1;
begin
   for i in 1..4000 loop
      TMP_VARCHAR := TMP_VARCHAR||'A';
   end loop;
   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_varchar, tmp_blob);
   commit;
   end loop;
   DBMS_LOB.CLOSE(tmp_bfile);
end;
/

-- ADD AN UNNEEDED EXTENT TO THE TABLE SEGMENT TO SIMULATE TABLE SEGMENT GROWTH

ALTER TABLE TEST ALLOCATE EXTENT;

-- EXAMINE THE STORAGE CREATED BY THE PROCESS

column segment_name format a30
column tablespace_name format a13
set pagesiz 1000

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

SEGMENT_NAME                   BYTES      TABLESPACE_NA EXTENTS
------------------------------ ---------- ------------- ----------
SYS_IL0000066127C00003$$          1048576 TEST_UNIFORM.           1
SYS_LOB0000066127C00003$$        58720256 TEST_UNIFORM          56
TEST                              2097152 TEST_UNIFORM           2
TEST_BFILE                        1048576 TEST_UNIFORM           1

-- DELETE 1/2 OF THE ROWS IN THE TEST TABLE

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

COMMIT;

-- SHOW THAT NO CHANGE HAS OCCURRED WITH THE STORAGE FOR THE TABLE

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

SEGMENT_NAME                   BYTES      TABLESPACE_NA EXTENTS
------------------------------ ---------- ------------- ----------
SYS_IL0000066127C00003$$          1048576 TEST_UNIFORM.           1
SYS_LOB0000066127C00003$$        58720256 TEST_UNIFORM.          56
TEST                              2097152 TEST_UNIFORM.           2
TEST_BFILE                        1048576 TEST_UNIFORM           1

-- MOVE THE TABLE FROM TABLESPACE TEST_UNIFORM. TO TEST_UNIFORM. AND EXAMINE THE STORAGE

ALTER TABLE TEST MOVE TABLESPACE TEST_UNIFORM;

ALTER TABLE TEST MOVE LOB(PHOTO) STORE AS SECUREFILE (TABLESPACE TEST_UNIFORM);

-- EXAMINE THE STORAGE AFTER THE MOVE

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

SEGMENT_NAME                   BYTES      TABLESPACE_NA EXTENTS
------------------------------ ---------- ------------- ----------
SYS_IL0000066127C00003$$          1048576 TEST_UNIFORM           1
SYS_LOB0000066127C00003$$        27262976 TEST_UNIFORM          26
TEST                              1048576 TEST_UNIFORM           1
TEST_BFILE                        1048576 TEST_UNIFORM           1

-- NOTICE THAT
--                      TEST (TABLE SEGMENT) HAS SHRUNK FROM 2 EXTENTS TO 1 EXTENT
--                      THE LOB SEGMENT HAS SHRUNK FROM 56 TO 26 EXTENTS

-- CLEANUP

CONNECT / AS SYSDBA;

DROP USER TEST CASCADE;
DROP TABLESPACE TEST_UNIFORM. INCLUDING CONTENTS AND DATAFILES;



-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

CASE STUDY #3 - SYSTEM GERNEATED EXTENT SIZE (AUTOALLOCATE) MANUAL SEGMENT SPACE MANAGEMENT (MSSM) TABLESPACE - BASICFILE LOB

-- CREATE THE TABLESPACES FOR THE TEST

CREATE TABLESPACE TEST_AUTO DATAFILE '/u01/app/oracle/oradata/v11202/TEST_AUTO.DBF' SIZE 100M SEGMENT SPACE MANAGEMENT MANUAL;

-- CREATE THE USER FOR THE TEST

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

-- CREATE THE TEST TABLES

CREATE TABLE test ( ID NUMBER, V_DATA VARCHAR2(4000), PHOTO BLOB) LOB (PHOTO) STORE AS BASICFILE;
CREATE TABLE test_bfile ( B_FILE BFILE) ;

CREATE or REPLACE DIRECTORY test as '/home/oracle/kbcook';

-- 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;
   tmp_varchar varchar2(4000):=null;
   dest_offset integer:=1;
   src_offset integer:=1;
begin
   for i in 1..4000 loop
      TMP_VARCHAR := TMP_VARCHAR||'A';
   end loop;
   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_varchar, tmp_blob);
   commit;
   end loop;
   DBMS_LOB.CLOSE(tmp_bfile);
end;
/

-- EXAMINE THE STORAGE CREATED BY THE PROCESS

column segment_name format a30
column tablespace_name format a13
set pagesiz 1000

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

SEGMENT_NAME                   BYTES      TABLESPACE_NA EXTENTS
------------------------------ ---------- ------------- ----------
SYS_IL0000066153C00003$$            65536 TEST_AUTO              1
SYS_LOB0000066153C00003$$        52428800 TEST_AUTO             65
TEST                               458752 TEST_AUTO              7
TEST_BFILE                          65536 TEST_AUTO              1


-- DELETE 1/2 OF THE ROWS IN THE TEST TABLE

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

COMMIT;

-- SHOW THAT NO CHANGE HAS OCCURRED WITH THE STORAGE FOR THE TABLE

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

SEGMENT_NAME                   BYTES      TABLESPACE_NA EXTENTS
------------------------------ ---------- ------------- ----------
SYS_IL0000066153C00003$$            65536 TEST_AUTO              1
SYS_LOB0000066153C00003$$        52428800 TEST_AUTO             65
TEST                               458752 TEST_AUTO              7
TEST_BFILE                          65536 TEST_AUTO              1

-- MOVE THE TABLE FROM TABLESPACE TEST_AUTO TO TEST_AUTO AND EXAMINE THE STORAGE

ALTER TABLE TEST MOVE TABLESPACE TEST_AUTO;

ALTER TABLE TEST MOVE LOB(PHOTO) STORE AS BASICFILE (TABLESPACE TEST_AUTO);

-- EXAMINE THE STORAGE AFTER THE MOVE

select segment_name, sum(bytes) BYTES, TABLESPACE_NAME, count(*) EXTENTS
from user_extents
group by segment_name, TABLESPACE_NAME
order by 1;
SEGMENT_NAME                   BYTES      TABLESPACE_NA EXTENTS
------------------------------ ---------- ------------- ----------
SYS_IL0000066153C00003$$            65536 TEST_AUTO              1
SYS_LOB0000066153C00003$$        26214400 TEST_AUTO             40
TEST                               262144 TEST_AUTO              4
TEST_BFILE                          65536 TEST_AUTO              1

-- NOTICE THAT
--                      TEST (TABLE SEGMENT) HAS SHRUNK FROM 7 EXTENTS TO 4 EXTENTS
--                      THE LOB SEGMENT HAS SHRUNK FROM 65 TO 40 EXTENTS


-- CLEANUP

CONNECT / AS SYSDBA;

DROP USER TEST CASCADE;
DROP TABLESPACE TEST_AUTO INCLUDING CONTENTS AND DATAFILES;


-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

CASE STUDY #4 - UNIFORM. EXTENT SIZE - MANUAL SEGMENT SPACE MANAGEMENT (MSSM) TABLESPACE - BASICFILE LOB

-- CREATE THE TABLESPACES FOR THE TEST

CREATE TABLESPACE TEST_UNIFORM. DATAFILE '/u01/app/oracle/oradata/v11202/TEST_UNIFORM.DBF' SIZE 100M
EXTENT MANAGEMENT LOCAL UNIFORM. SIZE 1M
SEGMENT SPACE MANAGEMENT MANUAL;

-- CREATE THE USER FOR THE TEST
     
create user test identified by test;
grant dba to test;
alter user test default tablespace test_uniform;
connect test/test;

-- CREATE THE TEST TABLES

CREATE TABLE test ( ID NUMBER, V_DATA VARCHAR2(4000), PHOTO BLOB) LOB (PHOTO) STORE AS BASICFILE ;
CREATE TABLE test_bfile ( B_FILE BFILE) ;

CREATE or REPLACE DIRECTORY test as '/home/oracle/kbcook';

-- 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;
   tmp_varchar varchar2(4000):=null;
   dest_offset integer:=1;
   src_offset integer:=1;
begin
   for i in 1..4000 loop
       TMP_VARCHAR := TMP_VARCHAR||'A';
   end loop;
   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_varchar, tmp_blob);
   commit;
   end loop;
   DBMS_LOB.CLOSE(tmp_bfile);
end;
/

-- ADD AN UNNEEDED EXTENT TO THE TABLE SEGMENT TO SIMULATE TABLE SEGMENT GROWTH

ALTER TABLE TEST ALLOCATE EXTENT;

-- EXAMINE THE STORAGE CREATED BY THE PROCESS

column segment_name format a30
column tablespace_name format a13
set pagesiz 1000

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

SEGMENT_NAME                   BYTES      TABLESPACE_NA EXTENTS
------------------------------ ---------- ------------- ----------
SYS_IL0000066169C00003$$          1048576 TEST_UNIFORM           1
SYS_LOB0000066169C00003$$        52428800 TEST_UNIFORM.          50
TEST                              2097152 TEST_UNIFORM           2
TEST_BFILE                        1048576 TEST_UNIFORM.           1

-- DELETE 1/2 OF THE ROWS IN THE TEST TABLE

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

COMMIT;

-- SHOW THAT NO CHANGE HAS OCCURRED WITH THE STORAGE FOR THE TABLE

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

SEGMENT_NAME                   BYTES      TABLESPACE_NA EXTENTS
------------------------------ ---------- ------------- ----------
SYS_IL0000066169C00003$$          1048576 TEST_UNIFORM           1
SYS_LOB0000066169C00003$$        52428800 TEST_UNIFORM          50
TEST                              2097152 TEST_UNIFORM           2
TEST_BFILE                        1048576 TEST_UNIFORM           1

-- MOVE THE TABLE FROM TABLESPACE TEST_UNIFORM. TO TEST_UNIFORM. AND EXAMINE THE STORAGE

ALTER TABLE TEST MOVE TABLESPACE TEST_UNIFORM;

ALTER TABLE TEST MOVE LOB(PHOTO) STORE AS BASICFILE (TABLESPACE TEST_UNIFORM);

-- EXAMINE THE STORAGE AFTER THE MOVE

select segment_name, sum(bytes) BYTES, TABLESPACE_NAME, count(*) EXTENTS
from user_extents
group by segment_name, TABLESPACE_NAME
order by 1;
SEGMENT_NAME                   BYTES      TABLESPACE_NA EXTENTS
------------------------------ ---------- ------------- ----------
SYS_IL0000066169C00003$$          1048576 TEST_UNIFORM           1
SYS_LOB0000066169C00003$$        26214400 TEST_UNIFORM.          25
TEST                              1048576 TEST_UNIFORM.           1
TEST_BFILE                        1048576 TEST_UNIFORM           1

-- NOTICE THAT
--                      TEST (TABLE SEGMENT) HAS SHRUNK FROM 2 EXTENTS TO 1 EXTENT
--                      THE LOB SEGMENT HAS SHRUNK FROM 50 TO 25 EXTENTS


-- CLEANUP

CONNECT / AS SYSDBA;

DROP USER TEST CASCADE;
DROP TABLESPACE TEST_UNIFORM. INCLUDING CONTENTS AND DATAFILES;


References

NOTE:1394613.1 - How to Shrink a Securefile LOB Using Online Redefinition (DBMS_REDEFINITION)?

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

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

注册时间:2009-11-24

  • 博文量
    798
  • 访问量
    3198661