Oracle中如何从BasicFile迁移到SecureFile

4/21/2009来源:Oracle教程人气:6106

     我创建了一个新表TRBTKT.SECURE_TICKETS重新定义了原始表TRBTKT.TICKETS的结构,将原始的BasicFile LOB映射到对应的SecureFile LOB,然后执行了一条INSERT INTO … SELECT FROM DML语句将原始表中的内容传输到新表中。
    在一个相对简单的应用程序环境中,这种载入方式足以应付,但目前在数据仓库和商业智能应用程序中广泛地使用了LOB,特别是存储那些包含了复杂商务交易的xml文档和医院信息系统捕获到的医疗影像数据。

    为了便于说明,我使用清单1中的代码重新创建了这两个表,我使用了列表分区对这两个表进行了分区,在清单2中,我使用更大的数据重新载入TRBTKT.TICKETS表。

    清单1 使用列表分区重新创建这两个表以模拟数据仓库环境

    -- 重新创建表TRBTKT.TICKETS,这次包括了一个STATUS列

 

CREATE table trbtkt.tickets (tkt_idNUMBER,description    VARCHAR2(30),submit_dtm     TIMESTAMP,statusVARCHAR2(8),document       BLOB,scrnimg        BLOB)LOB(document)   STORE AS BASICFILE (TABLESPACE basicfiles),LOB(scrnimg)    STORE AS BASICFILE (TABLESPACE basicfiles)PARTITION BY LIST (status) (PARTITION sts_open VALUES ('OPEN'),PARTITION sts_pendingVALUES ('PENDING'),PARTITION sts_closedVALUES ('CLOSED'),PARTITION sts_otherVALUES (DEFAULT));
--注释COMMENT ON table trbtkt.ticketsIS 'Contains Trouble Ticket transaction data';COMMENT ON COLUMN trbtkt.tickets.tkt_idIS 'Unique identifier for a Trouble Ticket';COMMENT ON COLUMN trbtkt.tickets.descriptionIS 'Trouble Ticket Description';COMMENT ON COLUMN trbtkt.tickets.submit_dtmIS 'Trouble Ticket Submission Time Stamp';COMMENT ON COLUMN trbtkt.tickets.statusIS 'Trouble Ticket Status';COMMENT ON COLUMN trbtkt.tickets.documentIS 'Large Object (LOB) that contains a microsoft Word document that PRovides detailed information about a Trouble Ticket';COMMENT ON COLUMN trbtkt.tickets.scrnimgIS 'Large Object (LOB) that contains a screen print or other image that helps a problem solver identify how to act on a Trouble Ticket';
-- 创建索引和约束CREATE UNIQUE INDEX trbtkt.tickets_pk_idxON trbtkt.tickets(tkt_id)TABLESPACE users;
ALTER table trbtkt.ticketsADD CONSTRAINT tickets_pkPRIMARY KEY (tkt_id);

 

    --- 创建表TRBTKT.SECURE_TICKETS的一个改良版本,使用分区划分SecureFile LOB的存储,基于STATUS 的值设置合适的保留周期

 

DROP table trbtkt.secure_tickets PURGE;CREATE table trbtkt.secure_tickets (tkt_idNUMBER,description    VARCHAR2(30),submit_dtm     TIMESTAMP,statusVARCHAR2(8),document       BLOB,scrnimg        BLOB)LOB(document)   STORE AS SECUREFILE (TABLESPACE securefilesDISABLE STORAGE IN ROWCACHE),LOB(scrnimg)    STORE AS SECUREFILE (TABLESPACE securefilesDISABLE STORAGE IN ROWCACHE READS)PARTITION BY LIST (status) (PARTITION sts_open VALUES ('OPEN')LOB (document) STORE AS SECUREFILE (KEEP_DUPLICATES NOCOMPRESS),LOB (scrnimg)  STORE AS SECUREFILE (KEEP_DUPLICATES COMPRESS),PARTITION sts_pendingVALUES ('PENDING')LOB (document) STORE AS SECUREFILE (KEEP_DUPLICATES NOCOMPRESS),LOB (scrnimg)  STORE AS SECUREFILE (DEDUPLICATE COMPRESS),PARTITION sts_closedVALUES ('CLOSED')LOB (document) STORE AS SECUREFILE (DEDUPLICATE COMPRESS),LOB (scrnimg)  STORE AS SECUREFILE (DEDUPLICATE COMPRESS HIGH),PARTITION sts_otherVALUES (DEFAULT)LOB (document) STORE AS SECUREFILE (DEDUPLICATE COMPRESS HIGH),LOB (scrnimg)  STORE AS SECUREFILE (DEDUPLICATE COMPRESS HIGH));
--注释COMMENT ON table trbtkt.secure_ticketsIS 'Contains Trouble Ticket transaction data';COMMENT ON COLUMN trbtkt.secure_tickets.tkt_idIS 'Unique identifier for a Trouble Ticket';COMMENT ON COLUMN trbtkt.secure_tickets.descriptionIS 'Trouble Ticket Description';COMMENT ON COLUMN trbtkt.secure_tickets.submit_dtmIS 'Trouble Ticket Submission Time Stamp';COMMENT ON COLUMN trbtkt.secure_tickets.statusIS 'Trouble Ticket Status';COMMENT ON COLUMN trbtkt.secure_tickets.documentIS 'Large Object (LOB) that contains a microsoft Word document that provides detailed information about a Trouble Ticket';COMMENT ON COLUMN trbtkt.secure_tickets.scrnimgIS 'Large Object (LOB) that contains a screen print or other image that helps a problem solver identify how to act on a Trouble Ticket';

    清单2 使用附加数据重新载入表TRBTKT.TICKETS

 

 

 

SET SERVEROUTPUT ONTRUNCATE table trbtkt.tickets;
BEGIN
trbtkt.pkg_securefiles.AddTroubleTicket (tkt_id => 101,description => 'Trouble Ticket 101',submit_dts => '2008-12-31 23:45:00',status => 'OPEN',docFileName => 'New_101.doc',imgFileName => 'DBRIssues.jpg');
trbtkt.pkg_securefiles.AddTroubleTicket (tkt_id => 102,description => 'Trouble Ticket 102',submit_dts => '2009-01-04 00:00:00',status => 'OPEN',docFileName => 'New_102.doc',imgFileName => 'Unresolved.jpg');
trbtkt.pkg_securefiles.AddTroubleTicket (tkt_id => 103,description => 'Trouble Ticket 103',submit_dts => '2009-01-02 00:00:00',status => 'OPEN',docFileName => 'New_103.doc',imgFileName => 'Unresolved.jpg');
trbtkt.pkg_securefiles.AddTroubleTicket (tkt_id => 104,description => 'Trouble Ticket 104',submit_dts => '2009-01-14 12:30:00',status => 'OPEN',docFileName => 'New_104.doc',imgFileName => 'DBRIssues.jpg');
trbtkt.pkg_securefiles.AddTroubleTicket (tkt_id => 105,description => 'Trouble Ticket 105',submit_dts => '2009-01-09 00:00:00',status => 'OPEN',docFileName => 'New_105.doc',imgFileName => 'Unresolved.jpg');
trbtkt.pkg_securefiles.AddTroubleTicket (tkt_id => 106,description => 'Trouble Ticket 106',submit_dts => '2009-01-11 00:00:00',status => 'OPEN',docFileName => 'New_106.doc',imgFileName => 'Unresolved.jpg');trbtkt.pkg_securefiles.AddTroubleTicket (tkt_id => 107,description => 'Trouble Ticket 107',submit_dts => '2009-01-16 00:00:00',status => 'OPEN',docFileName => 'New_107.doc',imgFileName => 'DBRIssues.jpg');trbtkt.pkg_securefiles.AddTroubleTicket (tkt_id => 108,description => 'Trouble Ticket 108',submit_dts => '2009-01-12 00:00:00',status => 'OPEN',docFileName => 'New_108.doc',imgFileName => 'Unresolved.jpg');trbtkt.pkg_securefiles.AddTroubleTicket (tkt_id => 109,description => 'Trouble Ticket 109',submit_dts => '2009-01-02 00:00:00',status => 'OPEN',docFileName => 'New_109.doc',imgFileName => 'Unresolved.jpg');
trbtkt.pkg_securefiles.AddTroubleTicket (tkt_id => 110,description => 'Trouble Ticket 110',submit_dts => '2009-01-14 12:45:00',status => 'OPEN',docFileName => 'New_110.doc',imgFileName => 'DBRIssues.jpg');
trbtkt.pkg_securefiles.AddTroubleTicket (tkt_id => 201,description => 'Trouble Ticket 201',submit_dts => '2008-12-31 23:45:00',status => 'PENDING',docFileName => 'New_101.doc',imgFileName => 'DBRIssues.jpg');
trbtkt.pkg_securefiles.AddTroubleTicket (tkt_id => 202,description => 'Trouble Ticket 202',submit_dts => '2009-01-04 00:00:00',status => 'OPEN',docFileName => 'New_102.doc',imgFileName => 'Unresolved.jpg');
trbtkt.pkg_securefiles.AddTroubleTicket (tkt_id => 203,description => 'Trouble Ticket 203',submit_dts => '2009-01-02 00:00:00',status => 'CLOSED',docFileName => 'New_103.doc',imgFileName => 'Unresolved.jpg');
trbtkt.pkg_securefiles.AddTroubleTicket (tkt_id => 204,description => 'Trouble Ticket 204',submit_dts => '2009-01-14 12:30:00',status => 'OPEN',docFileName => 'New_104.doc',imgFileName => 'DBRIssues.jpg');
trbtkt.pkg_securefiles.AddTroubleTicket (tkt_id => 205,description => 'Trouble Ticket 205',submit_dts => '2009-01-09 00:00:00',status => 'OPEN',docFileName => 'New_105.doc',imgFileName => 'Unresolved.jpg');
trbtkt.pkg_securefiles.AddTroubleTicket (tkt_id => 206,description => 'Trouble Ticket 206',submit_dts => '2009-01-11 00:00:00',status => 'PENDING',docFileName => 'New_106.doc',imgFileName => 'Unresolved.jpg');trbtkt.pkg_securefiles.AddTroubleTicket (tkt_id => 207,description => 'Trouble Ticket 207',submit_dts => '2009-01-16 00:00:00',status => 'OPEN',docFileName => 'New_107.doc',imgFileName => 'DBRIssues.jpg');trbtkt.pkg_securefiles.AddTroubleTicket (tkt_id => 208,description => 'Trouble Ticket 208',submit_dts => '2009-01-12 00:00:00',status => 'OPEN',docFileName => 'New_108.doc',imgFileName => 'Unresolved.jpg');trbtkt.pkg_securefiles.AddTroubleTicket (tkt_id => 209,description => 'Trouble Ticket 209',submit_dts => '2009-01-02 00:00:00',status => 'PENDING',docFileName => 'New_109.doc',imgFileName => 'Unresolved.jpg');
trbtkt.pkg_securefiles.AddTroubleTicket (tkt_id => 210,description => 'Trouble Ticket 210',submit_dts => '2009-01-14 12:45:00',status => 'OPEN',docFileName => 'New_110.doc',imgFileName => 'DBRIssues.jpg');
trbtkt.pkg_securefiles.AddTroubleTicket (tkt_id => 301,description => 'Trouble Ticket 301',submit_dts => '2008-12-31 23:45:00',status => 'CLOSED',docFileName => 'New_101.doc',imgFileName => 'DBRIssues.jpg');
trbtkt.pkg_securefiles.AddTroubleTicket (tkt_id => 302,description => 'Trouble Ticket 302',submit_dts => '2009-01-04 00:00:00',status => 'OPEN',docFileName => 'New_102.doc',imgFileName => 'Unresolved.jpg');
trbtkt.pkg_securefiles.AddTroubleTicket (tkt_id => 303,description => 'Trouble Ticket 303',submit_dts => '2009-01-02 00:00:00',status => 'OPEN',docFileName => 'New_103.doc',imgFileName => 'Unresolved.jpg');
trbtkt.pkg_securefiles.AddTroubleTicket (tkt_id => 304,description => 'Trouble Ticket 304',submit_dts => '2009-01-14 12:30:00',status => 'CLOSED',docFileName => 'New_104.doc',imgFileName => 'DBRIssues.jpg');
trbtkt.pkg_securefiles.AddTroubleTicket (tkt_id => 305,description => 'Trouble Ticket 305',submit_dts => '2009-01-09 00:00:00',status => 'PENDING',docFileName => 'New_105.doc',imgFileName => 'Unresolved.jpg');
trbtkt.pkg_securefiles.AddTroubleTicket (tkt_id => 306,description => 'Trouble Ticket 306',submit_dts => '2009-01-11 00:00:00',status => 'CLOSED',docFileName => 'New_106.doc',imgFileName => 'Unresolved.jpg');trbtkt.pkg_securefiles.AddTroubleTicket (tkt_id => 307,description => 'Trouble Ticket 307',submit_dts => '2009-01-16 00:00:00',status => 'OPEN',docFileName => 'New_107.doc',imgFileName => 'DBRIssues.jpg');trbtkt.pkg_securefiles.AddTroubleTicket (tkt_id => 308,description => 'Trouble Ticket 308',submit_dts => '2009-01-12 00:00:00',status => 'OPEN',docFileName => 'New_108.doc',imgFileName => 'Unresolved.jpg');trbtkt.pkg_securefiles.AddTroubleTicket (tkt_id => 309,description => 'Trouble Ticket 309',submit_dts => '2009-01-02 00:00:00',status => 'CLOSED',docFileName => 'New_109.doc',imgFileName => 'Unresolved.jpg');
trbtkt.pkg_securefiles.AddTroubleTicket (tkt_id => 310,description => 'Trouble Ticket 310',submit_dts => '2009-01-14 12:45:00',status => 'CLOSED',docFileName => 'New_110.doc',imgFileName => 'DBRIssues.jpg');
COMMIT;END;/-- 收集优化器统计信息BEGINDBMS_STATS.GATHER_SCHEMA_STATS(ownname => 'TRBTKT', CASCADE => TRUE);END;/有效地从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 1000SET 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_nameFROM dba_segmentsWHERE 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 loggingFORMAT A08      HEADING 'Logging'COL cache  FORMAT A10      HEADING 'Cacheing'COL in_row FORMAT A07      HEADING 'Stored|In Row'COL encryptFORMAT 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'SELECTtable_name,column_name,segment_name,tablespace_name,logging,cache,in_row,encrypt,compression,deduplication,securefile,partitionedFROM dba_lobsWHERE 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_nameFORMAT 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_encryptFORMAT A12      HEADING 'Encrypted'COL def_compress        FORMAT A12      HEADING 'Compressed'COL def_deduplicate     FORMAT A12      HEADING 'DeDuplicated'SELECTtable_name,column_name,def_cache,def_tablespace_name,def_securefile,def_compress,def_deduplicate,def_encryptFROM dba_part_lobsWHERE 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 encryptFORMAT A10      HEADING 'Encrypted'COL compression     FORMAT A10      HEADING 'Compressed'COL deduplication   FORMAT A10      HEADING 'DeDupli-|cated'COL securefile      FORMAT A10      HEADING 'SecureFile?'SELECTtable_name,column_name,partition_name,cache,in_row,encrypt,compression,deduplication,securefileFROM dba_lob_partitionsWHERE table_owner = 'TRBTKT'ORDER BY table_name, column_name;TTITLE OFF报告1 从BasicFIle和SecureFile LOB元数据报告返回的结果
SET PAGESIZE 1000SET 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_nameFROM dba_segmentsWHERE 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 loggingFORMAT A08      HEADING 'Logging'COL cache  FORMAT A10      HEADING 'Cacheing'COL in_row FORMAT A07      HEADING 'Stored|In Row'COL encryptFORMAT 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'SELECTtable_name,column_name,segment_name,tablespace_name,logging,cache,in_row,encrypt,compression,deduplication,securefile,partitionedFROM dba_lobsWHERE 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_nameFORMAT 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_encryptFORMAT A12      HEADING 'Encrypted'COL def_compress        FORMAT A12      HEADING 'Compressed'COL def_deduplicate     FORMAT A12      HEADING 'DeDuplicated'SELECTtable_name,column_name,def_cache,def_tablespace_name,def_securefile,def_compress,def_deduplicate,def_encryptFROM dba_part_lobsWHERE 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 encryptFORMAT A10      HEADING 'Encrypted'COL compression     FORMAT A10      HEADING 'Compressed'COL deduplication   FORMAT A10      HEADING 'DeDupli-|cated'COL securefile      FORMAT A10      HEADING 'SecureFile?'SELECTtable_name,column_name,partition_name,cache,in_row,encrypt,compression,deduplication,securefileFROM dba_lob_partitionsWHERE 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     NameTablespace        ------------------------- -------------------- -------------------- ------------ ------------      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     NOYES     NONE    NONE    NONE     NO      YES SECURE_TICKETS SCRNIMG    SYS_LOB0000072118C00006$$  BASICFILES   NONE     NOYES     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  NONENONENONE SECURE_TICKETS     SCRNIMG      NO  BASICFILES   NO  NONENONENONE TICKETS     DOCUMENT     YES SECUREFILES  YES NO  NO  NO   TICKETS     SCRNIMG      CACHEREADS   SECUREFILES  YES NO  NO  NO  
BasicFile和SecureFile LOB分区(来自DBA_LOB_PARTITIONS)
Stored in      StoredDeDupli-table    Column     Partition    Cacheing   In Row     Encrypted  Compressed cated      SecureFile     ---- ------------ ------------ ---------- ---------- ---------- ---------- ---------- ----------     SECURE_TICKETS   DOCUMENT     STS_OTHER    NOYES        NONE       NONE       NONE       NO    SECURE_TICKETS   DOCUMENT     STS_CLOSED   NOYES        NONE       NONE       NONE       NO    SECURE_TICKETS   DOCUMENT     STS_OPEN     NOYES        NONE       NONE       NONE       NO    SECURE_TICKETS   DOCUMENT     STS_PENDING  NOYES        NONE       NONE       NONE       NO    SECURE_TICKETS   SCRNIMG      STS_OTHER    NOYES        NONE       NONE       NONE       NO    SECURE_TICKETS   SCRNIMG      STS_CLOSED   NOYES        NONE       NONE       NONE       NO    SECURE_TICKETS   SCRNIMG      STS_PENDING  NOYES        NONE       NONE       NONE       NO    SECURE_TICKETS   SCRNIMG      STS_OPEN     NOYES        NONE       NONE       NONE       NO    TICKETS DOCUMENT     STS_OTHER    YES        NONOHIGH       LOB        YES   TICKETS DOCUMENT     STS_PENDING  YES        NONONONOYES   TICKETS DOCUMENT     STS_CLOSED   YES        NONOMEDIUM     LOB        YES   TICKETS DOCUMENT     STS_OPEN     YES        NONONONOYES   TICKETS SCRNIMG      STS_PENDING  CACHEREADS NONOMEDIUM     LOB        YES   TICKETS SCRNIMG      STS_OPEN     CACHEREADS NONOMEDIUM     NOYES   TICKETS SCRNIMG      STS_CLOSED   CACHEREADS NONOHIGH       LOB        YES   TICKETS SCRNIMG      STS_OTHER    CACHEREADS NONOHIGH       LOB        YES2、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存储利用率:BEGINtrbtkt.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.DOCUMENTPartition Name: STS_OPEN------------------------------------------------------------Full Blocks: 123 KB: .96Unformatted Blocks: 379 KB: 2.96 Total Blocks: 123 Total KB: .96  ========================================================================================================================Space Usage for BasicFile LOB TRBTKT.SECURE_TICKETS.DOCUMENTPartition 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.DOCUMENTPartition 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.DOCUMENTPartition 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存储利用率:BEGINtrbtkt.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: 992Expired 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.TICKETSsql>     MODIFY LOB(DOCUMENT) (NOCOMPRESS);
table altered.
sql> ALTER table TRBTKT.TICKETSsql>     MODIFY LOB(SCRNIMG) (KEEP_DUPLICATES);
table altered.
sql> ALTER table TRBTKT.TICKETSsql>     MODIFY PARTITION STS_PENDING LOB(SCRNIMG) (COMPRESS HIGH);table altered.sql> ALTER table TRBTKT.TICKETSsql>     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      StoredDeDupli-table   Column       Partition    Cacheing   In Row     Encrypted  Compressed cated      SecureFile---------------- ------------ ------------ ---------- ---------- ---------- ---------- --------SECURE_TICKETS   DOCUMENT     STS_OTHER    NOYES        NONE       NONE       NONE       NOSECURE_TICKETS   DOCUMENT     STS_CLOSED   NOYES        NONE       NONE       NONE       NOSECURE_TICKETS   DOCUMENT     STS_OPEN     NOYES        NONE       NONE       NONE       NOSECURE_TICKETS   DOCUMENT     STS_PENDING  NOYES        NONE       NONE       NONE       NOSECURE_TICKETS   SCRNIMG      STS_OTHER    NOYES        NONE       NONE       NONE       NOSECURE_TICKETS   SCRNIMG      STS_CLOSED   NOYES        NONE       NONE       NONE       NOSECURE_TICKETS   SCRNIMG      STS_PENDING  NOYES        NONE       NONE       NONE       NOSECURE_TICKETS   SCRNIMG      STS_OPEN     NOYES        NONE       NONE       NONE       NOTICKETS DOCUMENT     STS_OTHER    YES        NONONOLOB        YESTICKETS DOCUMENT     STS_PENDING  YES        NONONOLOB        YESTICKETS DOCUMENT     STS_CLOSED   YES        NONONOLOB        YESTICKETS DOCUMENT     STS_OPEN     YES        NONONONOYESTICKETS SCRNIMG      STS_PENDING  CACHEREADS NONOHIGH       NOYESTICKETS SCRNIMG      STS_OPEN     CACHEREADS NONOMEDIUM     NOYESTICKETS SCRNIMG      STS_CLOSED   CACHEREADS NONOHIGH       NOYESTICKETS SCRNIMG      STS_OTHER    CACHEREADS NONOHIGH       NOYES

 

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