首页 > 代码库 > 如何从BasicFile迁移到SecureFile存储(二)

如何从BasicFile迁移到SecureFile存储(二)

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


  2、DBMS_SPACE

  这是另一个Oracle古老支持包,它的SPACE_USAGE存储过程提供关于BasicFile和SecureFile LOB的空间利用率,我在TRBTKT.PKG_SECUREFILES包中引入了两个存储过程:CALC_SPACE_BASICFILES和CALC_SPACE_SECUREFILES。但遗憾的是,这个功能只能在启用了自动段空间管理(ASSM)的表空间上使用,而且,它也不会考虑任何BasicFile LOB块的空间利用率,在清单5中,我对表TRBTKT.TICKETS和TRBTKT.SECURE_TICKETS分别调用了这些存储过程,并返回了相应的输出。

  清单5 确定BasicFile和SecureFile LOB的空间利用率

SET SERVEROUTPUT ON
-- BasicFile存储利用率:
BEGIN
    trbtkt.pkg_securefiles.calc_space_basicfiles (
         ownname 
=> TRBTKT
        ,tabname 
=> SECURE_TICKETS
        ,colname 
=> DOCUMENT
        ,partname 
=> STS_OPEN
    );
    trbtkt.pkg_securefiles.calc_space_basicfiles (
         ownname 
=> TRBTKT
        ,tabname 
=> SECURE_TICKETS
        ,colname 
=> DOCUMENT
        ,partname 
=> STS_PENDING
    );
    trbtkt.pkg_securefiles.calc_space_basicfiles (
         ownname 
=> TRBTKT
        ,tabname 
=> SECURE_TICKETS
        ,colname 
=> DOCUMENT
        ,partname 
=> STS_CLOSED
    );
    trbtkt.pkg_securefiles.calc_space_basicfiles (
         ownname 
=> TRBTKT
        ,tabname 
=> SECURE_TICKETS
        ,colname 
=> DOCUMENT
        ,partname 
=> STS_OTHER
    );
    trbtkt.pkg_securefiles.calc_space_basicfiles (
         ownname 
=> TRBTKT
        ,tabname 
=> SECURE_TICKETS
        ,colname 
=> SCRNIMG
        ,partname 
=> STS_OPEN
    );
    trbtkt.pkg_securefiles.calc_space_basicfiles (
         ownname 
=> TRBTKT
        ,tabname 
=> SECURE_TICKETS
        ,colname 
=> SCRNIMG
        ,partname 
=> STS_PENDING
    );
    trbtkt.pkg_securefiles.calc_space_basicfiles (
         ownname 
=> TRBTKT
        ,tabname 
=> SECURE_TICKETS
        ,colname 
=> SCRNIMG
        ,partname 
=> STS_CLOSED
    );
    trbtkt.pkg_securefiles.calc_space_basicfiles (
         ownname 
=> TRBTKT
        ,tabname 
=> SECURE_TICKETS
        ,colname 
=> SCRNIMG
        ,partname 
=> STS_OTHER
    );
END;
/
============================================================
Space Usage for BasicFile LOB TRBTKT.SECURE_TICKETS.DOCUMENT
Partition Name: STS_OPEN                                    
------------------------------------------------------------
Full Blocks: 123 KB: .96                                    
Unformatted Blocks: 
379 KB: 2.96                            
Total Blocks: 
123 Total KB: .96                             
============================================================
============================================================
Space Usage for BasicFile LOB TRBTKT.SECURE_TICKETS.DOCUMENT
Partition Name: STS_PENDING                                 
------------------------------------------------------------
Full Blocks: 20 KB: .16                                     
Unformatted Blocks: 
482 KB: 3.77                            
Total Blocks: 
20 Total KB: .16                              
============================================================
============================================================
Space Usage for BasicFile LOB TRBTKT.SECURE_TICKETS.DOCUMENT
Partition Name: STS_CLOSED                                  
------------------------------------------------------------
Full Blocks: 37 KB: .29                                     
Unformatted Blocks: 
465 KB: 3.63                            
Total Blocks: 
37 Total KB: .29                              
============================================================
============================================================
Space Usage for BasicFile LOB TRBTKT.SECURE_TICKETS.DOCUMENT
Partition Name: STS_OTHER                                   
------------------------------------------------------------
Full Blocks: 0 KB: 0                                        
Unformatted Blocks: 
0 KB: 0                                 
Total Blocks: 
0 Total KB: 0                                 
============================================================
============================================================
Space Usage for BasicFile LOB TRBTKT.SECURE_TICKETS.SCRNIMG 
Partition Name: STS_OPEN                                    
------------------------------------------------------------
Full Blocks: 420 KB: 3.28                                   
Unformatted Blocks: 
82 KB: .64                              
Total Blocks: 
420 Total KB: 3.28                            
============================================================
============================================================
Space Usage for BasicFile LOB TRBTKT.SECURE_TICKETS.SCRNIMG 
Partition Name: STS_PENDING                                 
------------------------------------------------------------
Full Blocks: 66 KB: .52                                     
Unformatted Blocks: 
436 KB: 3.41                            
Total Blocks: 
66 Total KB: .52                              
============================================================
============================================================
Space Usage for BasicFile LOB TRBTKT.SECURE_TICKETS.SCRNIMG 
Partition Name: STS_CLOSED                                  
------------------------------------------------------------
Full Blocks: 144 KB: 1.13                                   
Unformatted Blocks: 
358 KB: 2.8                             
Total Blocks: 
144 Total KB: 1.13                            
============================================================
============================================================
Space Usage for BasicFile LOB TRBTKT.SECURE_TICKETS.SCRNIMG 
Partition Name: STS_OTHER                                   
------------------------------------------------------------
Full Blocks: 0 KB: 0                                        
Unformatted Blocks: 
0 KB: 0                                 
Total Blocks: 
0 Total KB: 0                                 
============================================================
-- SecureFile存储利用率:
BEGIN
    trbtkt.pkg_securefiles.calc_space_securefiles (
         ownname 
=> TRBTKT
        ,tabname 
=> TICKETS
        ,colname 
=> DOCUMENT
        ,partname 
=> STS_OPEN
    );
    trbtkt.pkg_securefiles.calc_space_securefiles (
         ownname 
=> TRBTKT
        ,tabname 
=> TICKETS
        ,colname 
=> DOCUMENT
        ,partname 
=> STS_PENDING
    );
    trbtkt.pkg_securefiles.calc_space_securefiles (
         ownname 
=> TRBTKT
        ,tabname 
=> TICKETS
        ,colname 
=> DOCUMENT
        ,partname 
=> STS_CLOSED
    );
    trbtkt.pkg_securefiles.calc_space_securefiles (
         ownname 
=> TRBTKT
        ,tabname 
=> TICKETS
        ,colname 
=> DOCUMENT
        ,partname 
=> STS_OTHER
    );
    trbtkt.pkg_securefiles.calc_space_securefiles (
         ownname 
=> TRBTKT
        ,tabname 
=> TICKETS
        ,colname 
=> SCRNIMG
        ,partname 
=> STS_OPEN
    );
    trbtkt.pkg_securefiles.calc_space_securefiles (
         ownname 
=> TRBTKT
        ,tabname 
=> TICKETS
        ,colname 
=> SCRNIMG
        ,partname 
=> STS_PENDING
    );
    trbtkt.pkg_securefiles.calc_space_securefiles (
         ownname 
=> TRBTKT
        ,tabname 
=> TICKETS
        ,colname 
=> SCRNIMG
        ,partname 
=> STS_CLOSED
    );
    trbtkt.pkg_securefiles.calc_space_securefiles (
         ownname 
=> TRBTKT
        ,tabname 
=> TICKETS
        ,colname 
=> SCRNIMG
        ,partname 
=> STS_OTHER
    );
END;
/

============================================================
Space Usage for SecureFile LOB TRBTKT.TICKETS.DOCUMENT      
Partition Name: STS_OPEN                                    
------------------------------------------------------------
Segment Blocks: 1024 KB: 8192                               
Used Blocks: 
124 KB: 992                                    
Expired Blocks: 
882 KB: 7056                                
Unexpired Blocks: 
0 KB: 0                                   
============================================================
============================================================
Space Usage for SecureFile LOB TRBTKT.TICKETS.DOCUMENT      
Partition Name: STS_PENDING                                 
------------------------------------------------------------
Segment Blocks: 1024 KB: 8192                               
Used Blocks: 
21 KB: 168                                     
Expired Blocks: 
985 KB: 7880                                
Unexpired Blocks: 
0 KB: 0                                   
============================================================
============================================================
Space Usage for SecureFile LOB TRBTKT.TICKETS.DOCUMENT      
Partition Name: STS_CLOSED                                  
------------------------------------------------------------
Segment Blocks: 1024 KB: 8192                               
Used Blocks: 
13 KB: 104                                     
Expired Blocks: 
993 KB: 7944                                
Unexpired Blocks: 
0 KB: 0                                   
============================================================
============================================================
Space Usage for SecureFile LOB TRBTKT.TICKETS.DOCUMENT      
Partition Name: STS_OTHER                                   
------------------------------------------------------------
Segment Blocks: 512 KB: 4096                                
Used Blocks: 
501 KB: 4008                                   
Expired Blocks: 
0 KB: 0                                     
Unexpired Blocks: 
0 KB: 0                                   
============================================================
============================================================
Space Usage for SecureFile LOB TRBTKT.TICKETS.SCRNIMG       
Partition Name: STS_OPEN                                    
------------------------------------------------------------
Segment Blocks: 2560 KB: 20480                              
Used Blocks: 
405 KB: 3240                                   
Expired Blocks: 
2134 KB: 17072                              
Unexpired Blocks: 
0 KB: 0                                   
============================================================
============================================================
Space Usage for SecureFile LOB TRBTKT.TICKETS.SCRNIMG       
Partition Name: STS_PENDING                                 
------------------------------------------------------------
Segment Blocks: 1024 KB: 8192                               
Used Blocks: 
62 KB: 496                                     
Expired Blocks: 
944 KB: 7552                                
Unexpired Blocks: 
0 KB: 0                                   
============================================================
============================================================
Space Usage for SecureFile LOB TRBTKT.TICKETS.SCRNIMG       
Partition Name: STS_CLOSED                                  
------------------------------------------------------------
Segment Blocks: 1024 KB: 8192                               
Used Blocks: 
142 KB: 1136                                   
Expired Blocks: 
864 KB: 6912                                
Unexpired Blocks: 
0 KB: 0                                   
============================================================
============================================================
Space Usage for SecureFile LOB TRBTKT.TICKETS.SCRNIMG       
Partition Name: STS_OTHER                                   
------------------------------------------------------------
Segment Blocks: 512 KB: 4096                                
Used Blocks: 
501 KB: 4008                                   
Expired Blocks: 
0 KB: 0                                     
Unexpired Blocks: 
0 KB: 0                                   
============================================================
SET SERVEROUTPUT ON


  修改SecureFile属性

  当我创建TRBTKT.SECURE_TICKETS表时,你可能注意到我为每个LOB列在它们对应的分区中执行压缩和重复删除,重要的是我还使用了ALTER TABLE语句进行修正,另外,如果需要的话,我还可以在每个LOB独立的分区上应用改变到SecureFile LOB。清单6中的代码显示了4个修改TRBTKT.TICKETS表的SecureFile LOB属性的例子,注意这个表现在已经用清单3中的代码和TRBTKT.SECURE_TICKETS表执行了交换,我也从DBA_PART_LOBS创建了一个报告显示在修改这些LOB属性前后的状态,结果输出如报告2所示。

  清单6 管理SecureFile LOB属性

SQL> ALTER TABLE TRBTKT.TICKETS
SQL
>     MODIFY LOB(DOCUMENT) (NOCOMPRESS);

Table altered.


SQL
> ALTER TABLE TRBTKT.TICKETS
SQL
>     MODIFY LOB(SCRNIMG) (KEEP_DUPLICATES);

Table altered.

SQL
> ALTER TABLE TRBTKT.TICKETS
SQL
>     MODIFY PARTITION STS_PENDING LOB(SCRNIMG) (COMPRESS HIGH);
                                                                      
Table altered.
                                                                      
SQL
> ALTER TABLE TRBTKT.TICKETS
SQL
>     MODIFY PARTITION STS_PENDING LOB(DOCUMENT) (DEDUPLICATE);
                                                                      
Table altered.

报告2 修改许多SecureFile LOB后查询DBA_LOB_PARTITIONS返回的结果

  BasicFile and SecureFile LOB Partitions
                                                         (
from 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         NO         LOB        YES
TICKETS          DOCUMENT     STS_PENDING  YES        NO         NO         NO         LOB        YES
TICKETS          DOCUMENT     STS_CLOSED   YES        NO         NO         NO         LOB        YES
TICKETS          DOCUMENT     STS_OPEN     YES        NO         NO         NO         NO         YES
TICKETS          SCRNIMG      STS_PENDING  CACHEREADS NO         NO         HIGH       NO         YES
TICKETS          SCRNIMG      STS_OPEN     CACHEREADS NO         NO         MEDIUM     NO         YES
TICKETS          SCRNIMG      STS_CLOSED   CACHEREADS NO         NO         HIGH       NO         YES
TICKETS          SCRNIMG      STS_OTHER    CACHEREADS NO         NO         HIGH       NO         YES

         最后,请记住任何对LOB属性进行修改都只会影响到新创建的LOB或新修改的LOB,例如,将列TRBTKT.SECURE_TICKETS.SCRNIMG的压缩方法从COMPRESS修改为NOCOMPRESS不会影响到现有的LOB条目。


如何从BasicFile迁移到SecureFile存储(二)