技术开发 频道
IT168首页 > 技术开发 > 技术开发技术 > 正文

如何从BasicFile迁移到SecureFile存储

作者：IT168 开心果 译 编辑： 晓熊 2009-04-16 10:33 来源：IT168
分享

　　有效地从BasicFile移植到SecureFile

　　现在已经将这些表进行了分区，以便在数据仓库环境更有效地使用，我也已经考虑过如何将LOB从BasicFile格式快速有效地转换成对应的SecureFile格式，幸运的是，Oracle 11g提供了两个方法：分区交换和在线重定义。

　　1、分区交换

　　分区交换载入(Partition Exchange Load，即PDL)有时也叫做分区转动，包含创建一个非分区表，它的格式和内容完全匹配目标分区表想要的分区，一旦原始表创建好了，就可以开始可已经分区的表的目标分区进行交换了，这个方法确实提供了许多好处，换句话说，原始表的本地索引在交换期间得到维护，它可以并行执行，这个方法可以重复多次以填充每个分区，它的主要缺点是接收数据的分区在进行交换时必须离线。

　　2、在线重定义

　　Oracle推荐使用DBMS_REDEFINITION包对源表和目标表执行在线重定义，因为它实现了分区交换方法相同的目标。但它不需要在重定义过程中将目标离线。在线重定义可以在表级也可以在分区级执行，它也可以同时并行为多个分区执行，我喜欢在线重定义的特性是在这个过程成功执行完毕后，源和目标对象将对真正地在数据库数据目录中转换定义，这意味着任何引用了新对象名的PL/SQL对象都不需要进行任何修改。清单3显示了如何使用TRBTKT.TICKETS表作为源对象，使用TRBTKT.SECURE_TICKETS表作为目标对象进行在线重定义的。从BasicFile LOB转换到SecureFile LOB最后一个注意事项是：一旦转换完成，LOB就不能再降级回到BasicFile LOB了。

　　管理SecureFile元数据

　　这些新的SecureFile特性无疑扩展了LOB的能力，对于DBA而言，也需要注意几个在传统的BasicFile LOB上增加的额外属性，幸运的是，Oracle 11g提供了多个方法跟踪BasicFile和SecureFile LOB的元数据。

　　1、数据字典视图

　　Oracle 11g也更新了多个关于SecureFile信息的数据字典视图，下面列出这些做了变动的视图。 

　　清单4显示了多条SQL*Plus查询，它们使用了这些数据字典视图返回关于SecureFile元数据的信息，报告1显示了从这些查询返回的结果。

　　清单4 查询BasicFile和SecureFile LOB的元数据

SET PAGESIZE 1000
SET LINESIZE 140
-- 视图: DBA_SEGMENTS
--显示关于BasicFile 和SecureFile 段的元数据
TTITLE 'LOB Segment Information|(from DBA_SEGMENTS)'
COL segment_name        FORMAT A30      HEADING 'Segment Name'
COL segment_type        FORMAT A20      HEADING 'Segment|Type'
COL segment_subtype     FORMAT A20      HEADING 'Segment|SubType'
COL partition_name      FORMAT A12      HEADING 'Partition|Name'
COL tablespace_name     FORMAT A12      HEADING 'Tablespace'
SELECT
    segment_name
   ,segment_type
   ,segment_subtype
   ,partition_name
   ,tablespace_name
  FROM dba_segments
WHERE owner = 'TRBTKT'
ORDER BY segment_name
;
TTITLE OFF
-- 视图: DBA_LOBS
--显示关于BasicFile和SecureFile LOB的元数据
TTITLE 'BasicFile and SecureFile LOBs Metadata|(from DBA_LOBS)'
COL table_name      FORMAT A14      HEADING 'Table'
COL segment_name    FORMAT A26      HEADING 'Segment'
COL column_name     FORMAT A10      HEADING 'Column'
COL tablespace_name FORMAT A12      HEADING 'Tablespace'
COL logging         FORMAT A08      HEADING 'Logging'
COL cache           FORMAT A10      HEADING 'Cacheing'
COL in_row          FORMAT A07      HEADING 'Stored|In Row'
COL encrypt         FORMAT A07      HEADING 'Encryp-|tion'
COL compression     FORMAT A07      HEADING 'Compre-|ssion'
COL deduplication   FORMAT A08      HEADING 'DeDupli-|cation'
COL securefile      FORMAT A07      HEADING 'Secure|File?'
COL partitioned     FORMAT A07      HEADING 'Parti-|tioned'
SELECT
    table_name
   ,column_name
   ,segment_name
   ,tablespace_name
   ,logging
   ,cache
   ,in_row
   ,encrypt
   ,compression
   ,deduplication
   ,securefile
   ,partitioned
  FROM dba_lobs
WHERE owner = 'TRBTKT'
ORDER BY table_name, column_name
;
TTITLE OFF
--视图: DBA_PART_LOBS
--显示BasicFile和SecureFile LOB的默认值
TTITLE 'BasicFile and SecureFile Partitioned LOB Default Settings|(from DBA_PART_LOBS)'
COL table_name          FORMAT A20      HEADING 'Table'
COL column_name         FORMAT A12      HEADING 'Column'
COL def_cache           FORMAT A12      HEADING 'Cached'
COL def_tablespace_name FORMAT A12      HEADING 'Tablespace'
COL def_securefile      FORMAT A12      HEADING 'SecureFile'
COL def_encrypt         FORMAT A12      HEADING 'Encrypted'
COL def_compress        FORMAT A12      HEADING 'Compressed'
COL def_deduplicate     FORMAT A12      HEADING 'DeDuplicated'
SELECT
    table_name
   ,column_name
   ,def_cache
   ,def_tablespace_name
   ,def_securefile
   ,def_compress
   ,def_deduplicate
   ,def_encrypt
  FROM dba_part_lobs
WHERE table_owner = 'TRBTKT'
ORDER BY table_name, column_name
;
TTITLE OFF
--视图: DBA_LOB_PARTITIONS
--在独立的LOB级描述BasicFile和SecureFile设置
TTITLE 'BasicFile and SecureFile LOB Partitions|(from DBA_LOB_PARTITIONS)'
COL table_name      FORMAT A16      HEADING 'Table'
COL column_name     FORMAT A12      HEADING 'Column'
COL partition_name  FORMAT A12      HEADING 'Stored in|Partition'
COL cache           FORMAT A10      HEADING 'Cacheing'
COL in_row          FORMAT A10      HEADING 'Stored|In Row'
COL encrypt         FORMAT A10      HEADING 'Encrypted'
COL compression     FORMAT A10      HEADING 'Compressed'
COL deduplication   FORMAT A10      HEADING 'DeDupli-|cated'
COL securefile      FORMAT A10      HEADING 'SecureFile?'
SELECT
    table_name
   ,column_name
   ,partition_name
   ,cache
   ,in_row
   ,encrypt
   ,compression
   ,deduplication
   ,securefile
  FROM dba_lob_partitions
WHERE table_owner = 'TRBTKT'
ORDER BY table_name, column_name
;
TTITLE OFF

报告1 从BasicFIle和SecureFile LOB元数据报告返回的结果

SET PAGESIZE 1000
SET LINESIZE 140
-- 视图: DBA_SEGMENTS
--显示关于BasicFile 和SecureFile 段的元数据
TTITLE 'LOB Segment Information|(from DBA_SEGMENTS)'
COL segment_name        FORMAT A30      HEADING 'Segment Name'
COL segment_type        FORMAT A20      HEADING 'Segment|Type'
COL segment_subtype     FORMAT A20      HEADING 'Segment|SubType'
COL partition_name      FORMAT A12      HEADING 'Partition|Name'
COL tablespace_name     FORMAT A12      HEADING 'Tablespace'
SELECT
    segment_name
   ,segment_type
   ,segment_subtype
   ,partition_name
   ,tablespace_name
  FROM dba_segments
WHERE owner = 'TRBTKT'
ORDER BY segment_name
;
TTITLE OFF
-- 视图: DBA_LOBS
--显示关于BasicFile和SecureFile LOB的元数据
TTITLE 'BasicFile and SecureFile LOBs Metadata|(from DBA_LOBS)'
COL table_name      FORMAT A14      HEADING 'Table'
COL segment_name    FORMAT A26      HEADING 'Segment'
COL column_name     FORMAT A10      HEADING 'Column'
COL tablespace_name FORMAT A12      HEADING 'Tablespace'
COL logging         FORMAT A08      HEADING 'Logging'
COL cache           FORMAT A10      HEADING 'Cacheing'
COL in_row          FORMAT A07      HEADING 'Stored|In Row'
COL encrypt         FORMAT A07      HEADING 'Encryp-|tion'
COL compression     FORMAT A07      HEADING 'Compre-|ssion'
COL deduplication   FORMAT A08      HEADING 'DeDupli-|cation'
COL securefile      FORMAT A07      HEADING 'Secure|File?'
COL partitioned     FORMAT A07      HEADING 'Parti-|tioned'
SELECT
    table_name
   ,column_name
   ,segment_name
   ,tablespace_name
   ,logging
   ,cache
   ,in_row
   ,encrypt
   ,compression
   ,deduplication
   ,securefile
   ,partitioned
  FROM dba_lobs
WHERE owner = 'TRBTKT'
ORDER BY table_name, column_name
;
TTITLE OFF
--视图: DBA_PART_LOBS
--显示BasicFile和SecureFile LOB的默认值
TTITLE 'BasicFile and SecureFile Partitioned LOB Default Settings|(from DBA_PART_LOBS)'
COL table_name          FORMAT A20      HEADING 'Table'
COL column_name         FORMAT A12      HEADING 'Column'
COL def_cache           FORMAT A12      HEADING 'Cached'
COL def_tablespace_name FORMAT A12      HEADING 'Tablespace'
COL def_securefile      FORMAT A12      HEADING 'SecureFile'
COL def_encrypt         FORMAT A12      HEADING 'Encrypted'
COL def_compress        FORMAT A12      HEADING 'Compressed'
COL def_deduplicate     FORMAT A12      HEADING 'DeDuplicated'
SELECT
    table_name
   ,column_name
   ,def_cache
   ,def_tablespace_name
   ,def_securefile
   ,def_compress
   ,def_deduplicate
   ,def_encrypt
  FROM dba_part_lobs
WHERE table_owner = 'TRBTKT'
ORDER BY table_name, column_name
;
TTITLE OFF
--视图: DBA_LOB_PARTITIONS
--在独立的LOB级描述BasicFile和SecureFile设置
TTITLE 'BasicFile and SecureFile LOB Partitions|(from DBA_LOB_PARTITIONS)'
COL table_name      FORMAT A16      HEADING 'Table'
COL column_name     FORMAT A12      HEADING 'Column'
COL partition_name  FORMAT A12      HEADING 'Stored in|Partition'
COL cache           FORMAT A10      HEADING 'Cacheing'
COL in_row          FORMAT A10      HEADING 'Stored|In Row'
COL encrypt         FORMAT A10      HEADING 'Encrypted'
COL compression     FORMAT A10      HEADING 'Compressed'
COL deduplication   FORMAT A10      HEADING 'DeDupli-|cated'
COL securefile      FORMAT A10      HEADING 'SecureFile?'
SELECT
    table_name
   ,column_name
   ,partition_name
   ,cache
   ,in_row
   ,encrypt
   ,compression
   ,deduplication
   ,securefile
  FROM dba_lob_partitions
WHERE table_owner = 'TRBTKT'
ORDER BY table_name, column_name
;
TTITLE OFF
报告1 从BasicFIle和SecureFile LOB元数据报告返回的结果
LOB段信息
                                                            (来自DBA_SEGMENTS)

                               Segment              Segment              Partition                                                          
Segment Name                   Type                 SubType              Name         Tablespace                                            
------------------------- -------------------- -------------------- ------------ ------------                                          
SECURE_TICKETS                 TABLE PARTITION      ASSM                 STS_PENDING  USERS                                                
SECURE_TICKETS                 TABLE PARTITION      ASSM                 STS_CLOSED   USERS                                                
SECURE_TICKETS                 TABLE PARTITION      ASSM                 STS_OPEN     USERS                                                
SECURE_TICKETS                 TABLE PARTITION      ASSM                 STS_OTHER    USERS                                                
SYS_IL0000072118C00005$$       INDEX PARTITION      ASSM                 SYS_IL_P180  BASICFILES                                            
SYS_IL0000072118C00005$$       INDEX PARTITION      ASSM                 SYS_IL_P179  BASICFILES                                            
SYS_IL0000072118C00005$$       INDEX PARTITION      ASSM                 SYS_IL_P178  BASICFILES                                            
SYS_IL0000072118C00005$$       INDEX PARTITION      ASSM                 SYS_IL_P177  BASICFILES                                            
SYS_IL0000072118C00006$$       INDEX PARTITION      ASSM                 SYS_IL_P185  BASICFILES                                            
SYS_IL0000072118C00006$$       INDEX PARTITION      ASSM                 SYS_IL_P188  BASICFILES                                            
SYS_IL0000072118C00006$$       INDEX PARTITION      ASSM                 SYS_IL_P187  BASICFILES                                            
SYS_IL0000072118C00006$$       INDEX PARTITION      ASSM                 SYS_IL_P186  BASICFILES                                            
SYS_IL0000072144C00005$$       INDEX PARTITION      ASSM                 SYS_IL_P194  SECUREFILES                                          
SYS_IL0000072144C00005$$       INDEX PARTITION      ASSM                 SYS_IL_P193  SECUREFILES                                          
SYS_IL0000072144C00005$$       INDEX PARTITION      ASSM                 SYS_IL_P195  SECUREFILES                                          
SYS_IL0000072144C00005$$       INDEX PARTITION      ASSM                 SYS_IL_P196  SECUREFILES                                          
SYS_IL0000072144C00006$$       INDEX PARTITION      ASSM                 SYS_IL_P204  SECUREFILES                                          
SYS_IL0000072144C00006$$       INDEX PARTITION      ASSM                 SYS_IL_P203  SECUREFILES                                          
SYS_IL0000072144C00006$$       INDEX PARTITION      ASSM                 SYS_IL_P202  SECUREFILES                                          
SYS_IL0000072144C00006$$       INDEX PARTITION      ASSM                 SYS_IL_P201  SECUREFILES                                          
SYS_LOB0000072118C00005$$      LOB PARTITION        ASSM                 SYS_LOB_P173 BASICFILES                                            
SYS_LOB0000072118C00005$$      LOB PARTITION        ASSM                 SYS_LOB_P176 BASICFILES                                            
SYS_LOB0000072118C00005$$      LOB PARTITION        ASSM                 SYS_LOB_P175 BASICFILES                                            
SYS_LOB0000072118C00005$$      LOB PARTITION        ASSM                 SYS_LOB_P174 BASICFILES                                            
SYS_LOB0000072118C00006$$      LOB PARTITION        ASSM                 SYS_LOB_P184 BASICFILES                                            
SYS_LOB0000072118C00006$$      LOB PARTITION        ASSM                 SYS_LOB_P183 BASICFILES                                            
SYS_LOB0000072118C00006$$      LOB PARTITION        ASSM                 SYS_LOB_P181 BASICFILES                                            
SYS_LOB0000072118C00006$$      LOB PARTITION        ASSM                 SYS_LOB_P182 BASICFILES                                            
SYS_LOB0000072144C00005$$      LOB PARTITION        SECUREFILE           SYS_LOB_P191 SECUREFILES                                          
SYS_LOB0000072144C00005$$      LOB PARTITION        SECUREFILE           SYS_LOB_P192 SECUREFILES                                          
SYS_LOB0000072144C00005$$      LOB PARTITION        SECUREFILE           SYS_LOB_P189 SECUREFILES                                          
SYS_LOB0000072144C00005$$      LOB PARTITION        SECUREFILE           SYS_LOB_P190 SECUREFILES                                          
SYS_LOB0000072144C00006$$      LOB PARTITION        SECUREFILE           SYS_LOB_P198 SECUREFILES                                          
SYS_LOB0000072144C00006$$      LOB PARTITION        SECUREFILE           SYS_LOB_P199 SECUREFILES                                          
SYS_LOB0000072144C00006$$      LOB PARTITION        SECUREFILE           SYS_LOB_P197 SECUREFILES                                          
SYS_LOB0000072144C00006$$      LOB PARTITION        SECUREFILE           SYS_LOB_P200 SECUREFILES                                          
TICKETS                        TABLE PARTITION      ASSM                 STS_PENDING  USERS                                                
TICKETS                        TABLE PARTITION      ASSM                 STS_OTHER    USERS                                                
TICKETS                        TABLE PARTITION      ASSM                 STS_OPEN     USERS                                                
TICKETS                        TABLE PARTITION      ASSM                 STS_CLOSED   USERS                                                
TICKETS_PK_IDX                 INDEX                ASSM                              USERS                                                

                                                   BasicFile和SecureFile LOB元数据
                                                              (来自DBA_LOBS)

                                                                                      Stored  Encryp- Compre- DeDupli- Secure  Parti-      
Table          Column     Segment                    Tablespace   Logging  Cacheing   In Row  tion    ssion   cation   File?   tioned      
-------------- ---------- -------------------------- ------------ -------- ---------- ------- ------- ------- -------- ------- -------      
SECURE_TICKETS DOCUMENT   SYS_LOB0000072118C00005$$  BASICFILES   NONE     NO         YES     NONE    NONE    NONE     NO      YES          
SECURE_TICKETS SCRNIMG    SYS_LOB0000072118C00006$$  BASICFILES   NONE     NO         YES     NONE    NONE    NONE     NO      YES          
TICKETS        DOCUMENT   SYS_LOB0000072144C00005$$  SECUREFILES  NONE     YES        NO      NO      NO      NO       YES     YES          
TICKETS        SCRNIMG    SYS_LOB0000072144C00006$$  SECUREFILES  NONE     CACHEREADS NO      NO      NO      NO       YES     YES          

                                         BasicFile和SecureFile分区LOB默认设置
                                                            (来自DBA_PART_LOBS)

Table     Column       Cached       Tablespace   SecureFile   Compressed   DeDuplicated Encrypted                                
----- ------------ ------------ ------------ ------------ ------------ ------------ ------------                            
SECURE_TICKETS     DOCUMENT     NO           BASICFILES   NO           NONE         NONE         NONE                                    
SECURE_TICKETS     SCRNIMG      NO           BASICFILES   NO           NONE         NONE         NONE                                    
TICKETS              DOCUMENT     YES          SECUREFILES  YES          NO           NO           NO                                      
TICKETS              SCRNIMG      CACHEREADS   SECUREFILES  YES          NO           NO           NO                                      

                                                  BasicFile和SecureFile LOB分区
                                                         (来自DBA_LOB_PARTITIONS)

                              Stored in               Stored                           DeDupli-                                            
Table    Column     Partition    Cacheing   In Row     Encrypted  Compressed cated      SecureFile                                
---- ------------ ------------ ---------- ---------- ---------- ---------- ---------- ----------                                
SECURE_TICKETS   DOCUMENT     STS_OTHER    NO         YES        NONE       NONE       NONE       NO                                        
SECURE_TICKETS   DOCUMENT     STS_CLOSED   NO         YES        NONE       NONE       NONE       NO                                        
SECURE_TICKETS   DOCUMENT     STS_OPEN     NO         YES        NONE       NONE       NONE       NO                                        
SECURE_TICKETS   DOCUMENT     STS_PENDING  NO         YES        NONE       NONE       NONE       NO                                        
SECURE_TICKETS   SCRNIMG      STS_OTHER    NO         YES        NONE       NONE       NONE       NO                                        
SECURE_TICKETS   SCRNIMG      STS_CLOSED   NO         YES        NONE       NONE       NONE       NO                                        
SECURE_TICKETS   SCRNIMG      STS_PENDING  NO         YES        NONE       NONE       NONE       NO                                        
SECURE_TICKETS   SCRNIMG      STS_OPEN     NO         YES        NONE       NONE       NONE       NO                                        
TICKETS          DOCUMENT     STS_OTHER    YES        NO         NO         HIGH       LOB        YES                                      
TICKETS          DOCUMENT     STS_PENDING  YES        NO         NO         NO         NO         YES                                      
TICKETS          DOCUMENT     STS_CLOSED   YES        NO         NO         MEDIUM     LOB        YES                                      
TICKETS          DOCUMENT     STS_OPEN     YES        NO         NO         NO         NO         YES                                      
TICKETS          SCRNIMG      STS_PENDING  CACHEREADS NO         NO         MEDIUM     LOB        YES                                      
TICKETS          SCRNIMG      STS_OPEN     CACHEREADS NO         NO         MEDIUM     NO         YES                                      
TICKETS          SCRNIMG      STS_CLOSED   CACHEREADS NO         NO         HIGH       LOB        YES                                      
TICKETS          SCRNIMG      STS_OTHER    CACHEREADS NO         NO         HIGH       LOB        YES
0
1234查看全文
第1页：摘要第2页：有效地从BasicFile移植到SecureFile第3页：DBMS_SPACE第4页：修改SecureFile属性
相关文章
    盛拓传媒简介 关于IT168 广告服务 使用条款 投稿指南 联系我们
    北京盛拓优讯信息技术有限公司. 版权所有 中华人民共和国增值电信业务经营许可证 编号：京B2-20170206 北京市公安局海淀分局网监中心备案编号：11010802020118
    广播电视节目制作经营许可证:(京) 字第25315号 信息系统安全等级保护备案：11010813655-00001 京公网安备11010802020118号
    违法和不良信息举报电话 :010-58859066,18101376593,shengtuo20@it168.com