ITPub博客

首页 > Linux操作系统 > Linux操作系统 > HOW TO ONLINE CONVERT TO/FROM SECUREFILES FROM/TO BASICFILES (文档 ID 728758.1)

HOW TO ONLINE CONVERT TO/FROM SECUREFILES FROM/TO BASICFILES (文档 ID 728758.1)

原创 Linux操作系统 作者:rongshiyuan 时间:2013-10-20 18:09:18 0 删除 编辑
HOW TO ONLINE CONVERT TO/FROM SECUREFILES FROM/TO BASICFILES (文档 ID 728758.1)
 

In this Document
  Goal
  Solution


Applies to:

Oracle Server - Enterprise Edition - Version: 11.1.0.6 to 11.2.0.2 - Release: 11.1 to 11.2
Information in this document applies to any platform.
***Checked for relevance on 12-Nov-2010***

Goal

This note will demonstrate how to convert BasicFiles to SecureFiles and SecureFiles to BasicFiles

Solution

The following reference discusses conversion TO Securefiles FROM BasicFIles ...


Oracle� Database SecureFiles and Large Objects Developer's Guide 11g Release 1 (11.1) Part Number B28393-03
    4 Using Oracle SecureFiles 
        Example 4-1 Example of Online Redefinition


The reverese of the method may be used to convert from SecureFiles

The following example is a modified version of the example from the above reference


Convert to SecureFiles from Basicfiles

connect / as sysdba

create user pm identified by pm;

GRANT EXECUTE ON DBMS_REDEFINITION TO pm;
GRANT ALTER ANY TABLE TO pm;
GRANT DROP ANY TABLE TO pm;
GRANT LOCK ANY TABLE TO pm;
GRANT CREATE ANY TABLE TO pm;
GRANT SELECT ANY TABLE TO pm;
GRANT CREATE SESSION TO pm;
ALTER USER pm QUOTA UNLIMITED ON ;

--Privileges required to perform. cloning of dependent objects.

GRANT CREATE ANY TRIGGER TO pm;
GRANT CREATE ANY INDEX TO pm;

CONNECT pm/pm

-- CREATE OUR TABLE TO BE CONVERTED TO SECUREFILE

CREATE TABLE cust (
c_id NUMBER PRIMARY KEY,
c_zip NUMBER,
c_name VARCHAR(30) DEFAULT NULL,
c_lob CLOB
);

-- INSERT A ROW INTO THIS TABLE

INSERT INTO cust VALUES (1, 94065, 'hhh', 'ttt');

COMMIT;

-- CREATE OUR 'INTERIM' TABLE

CREATE TABLE cust_int (
c_id NUMBER NOT NULL,
c_zip NUMBER,
c_name VARCHAR(30) DEFAULT NULL,
c_lob CLOB
) LOB(c_LOB) STORE AS SECUREFILE (NOCACHE FILESYSTEM_LIKE_LOGGING);

-- START THE REDEFINITION

DECLARE
   col_mapping VARCHAR2(1000);
BEGIN
-- map all the columns in the interim table to the original table
   col_mapping := 'c_id c_id , '|| 'c_zip c_zip , '|| 'c_name c_name, '|| 'c_lob c_lob';
   DBMS_REDEFINITION.START_REDEF_TABLE('pm', 'cust', 'cust_int', col_mapping);
END;
/

-- COPY THE CONSTRAINTS FROM OUR ORIGINAL TABLE ... TO THE INTERIM TABLE

DECLARE
   error_count pls_integer := 0;
BEGIN
   DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS('pm', 'cust', 'cust_int', dbms_redefinition.cons_orig_params, TRUE,TRUE,TRUE,FALSE, error_count);
   DBMS_OUTPUT.PUT_LINE('errors := ' || TO_CHAR(error_count));
END;
/

-- FINISH OUR REDEFINITION WHICH WILL SWAP THE ORIGINAL TABLE AND THE INTERIM TABLE

EXEC DBMS_REDEFINITION.FINISH_REDEF_TABLE('pm', 'cust', 'cust_int');

-- DROP THE INTERIM TABLE (THE FORMER ORIGINAL TABLE)

DROP TABLE cust_int;

-- TRY TO INSERT A ROW INTO OUR NEW SECUREFILE TABLE TO PROVE THAT THE PRIMARY KEY WAS PROPERLY MOVED

INSERT INTO cust VALUES(1, 94065, 'hhh', 'ttt');

-- VERIFY THAT OUR NEW TABLE IS NOW USING SecureFiles

SELECT SECUREFILE FROM USER_LOBS WHERE TABLE_NAME = 'CUST';




Convert to Basicfiles from SecureFiles

-- CREATE OUR 'INTERIM' TABLE
-- NOTE: no SecureFile option and no primary key

CREATE TABLE cust_int (
c_id NUMBER,
c_zip NUMBER,
c_name VARCHAR(30) DEFAULT NULL,
c_lob CLOB
);

-- START THE REDEFINITION

DECLARE
   col_mapping VARCHAR2(1000);
BEGIN
-- map all the columns in the interim table to the original table
   col_mapping := 'c_id c_id , '|| 'c_zip c_zip , '|| 'c_name c_name, '|| 'c_lob c_lob';
   DBMS_REDEFINITION.START_REDEF_TABLE('pm', 'cust', 'cust_int', col_mapping);
END;
/

-- COPY THE CONSTRAINTS FROM OUR ORIGINAL TABLE ... TO THE INTERIM TABLE

DECLARE
   error_count pls_integer := 0;
BEGIN
   DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS('pm', 'cust', 'cust_int', dbms_redefinition.cons_orig_params, TRUE,TRUE,TRUE,FALSE, error_count);
   DBMS_OUTPUT.PUT_LINE('errors := ' || TO_CHAR(error_count));
END;
/

-- FINISH OUR REDEFINITION WHICH WILL SWAP THE ORIGINAL TABLE AND THE INTERIM TABLE

EXEC DBMS_REDEFINITION.FINISH_REDEF_TABLE('pm', 'cust', 'cust_int');

-- DROP THE INTERIM TABLE (THE FORMER ORIGINAL TABLE)

DROP TABLE cust_int;

-- TRY TO INSERT A ROW INTO OUR NEW SECUREFILE TABLE TO PROVE THAT THE PRIMARY KEY WAS PROPERLY MOVED

INSERT INTO cust VALUES(1, 94065, 'hhh', 'ttt');

-- VERIFY THAT OUR NEW TABLE IS NOW USING BasicFIles

SELECT SECUREFILE FROM USER_LOBS WHERE TABLE_NAME = 'CUST';



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

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

注册时间:2009-11-24

  • 博文量
    798
  • 访问量
    3243504