51CTO首页
AI.x社区
博客
学堂
精品班
直播训练营
企业培训
鸿蒙开发者社区
WOT技术大会
AIGC创新中国行
公众号矩阵
移动端
短视频免费课程课程排行直播课软考学堂
全部课程厂商认证IT技术2024年软考PMP项目管理软考资讯
在线学习
文章资源问答课堂专栏直播
51CTO
鸿蒙开发者社区
51CTO技术栈
51CTO官微
51CTO学堂
51CTO博客
CTO训练营
鸿蒙开发者社区订阅号
51CTO题库小程序
51CTO学堂APP
51CTO学堂企业版APP
鸿蒙开发者社区视频号
账号设置 退出

Oracle中如何从BasicFile迁移到SecureFile

作者：开心果
运维 数据库运维 Oracle
Oracle 11g新的SecureFile存储特性扩展了大对象(LOB)的灵活性和容量，本文研究如何有效地从BasicFile迁移到SecureFile存储，如何测量SecureFile vs BasicFile LOB的效率，以及如何为SecureFile LOB使用不同的压缩和重复数据删除选项。

我创建了一个新表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_pending
   VALUES ('PENDING')
       ,PARTITION sts_closed
   VALUES ('CLOSED')
       ,PARTITION sts_other
   VALUES (DEFAULT)
    )
;


--注释
COMMENT ON TABLE trbtkt.tickets
    IS 'Contains Trouble Ticket transaction data';
COMMENT ON COLUMN trbtkt.tickets.tkt_id
    IS 'Unique identifier for a Trouble Ticket';
COMMENT ON COLUMN trbtkt.tickets.description
    IS 'Trouble Ticket Description';
COMMENT ON COLUMN trbtkt.tickets.submit_dtm
    IS 'Trouble Ticket Submission Time Stamp';
COMMENT ON COLUMN trbtkt.tickets.status
    IS 'Trouble Ticket Status';
COMMENT ON COLUMN trbtkt.tickets.document
    IS 'Large Object (LOB) that contains a Microsoft Word document that provides detailed information about a Trouble Ticket';
COMMENT ON COLUMN trbtkt.tickets.scrnimg
    IS '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_idx
    ON trbtkt.tickets(tkt_id)
    TABLESPACE users;


ALTER TABLE trbtkt.tickets
    ADD CONSTRAINT tickets_pk
    PRIMARY 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 securefiles
   DISABLE STORAGE IN ROW
   CACHE
        )
   ,LOB(scrnimg)    
        STORE AS SECUREFILE (
   TABLESPACE securefiles
   DISABLE STORAGE IN ROW
   CACHE 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_pending
   VALUES ('PENDING')
       LOB (document) STORE AS SECUREFILE (KEEP_DUPLICATES NOCOMPRESS)
      ,LOB (scrnimg)  STORE AS SECUREFILE (DEDUPLICATE COMPRESS)
       ,PARTITION sts_closed
   VALUES ('CLOSED')
       LOB (document) STORE AS SECUREFILE (DEDUPLICATE COMPRESS)
      ,LOB (scrnimg)  STORE AS SECUREFILE (DEDUPLICATE COMPRESS HIGH)
       ,PARTITION sts_other
   VALUES (DEFAULT)
       LOB (document) STORE AS SECUREFILE (DEDUPLICATE COMPRESS HIGH)
      ,LOB (scrnimg)  STORE AS SECUREFILE (DEDUPLICATE COMPRESS HIGH)
    )
;


--注释
COMMENT ON TABLE trbtkt.secure_tickets
    IS 'Contains Trouble Ticket transaction data';
COMMENT ON COLUMN trbtkt.secure_tickets.tkt_id
    IS 'Unique identifier for a Trouble Ticket';
COMMENT ON COLUMN trbtkt.secure_tickets.description
    IS 'Trouble Ticket Description';
COMMENT ON COLUMN trbtkt.secure_tickets.submit_dtm
    IS 'Trouble Ticket Submission Time Stamp';
COMMENT ON COLUMN trbtkt.secure_tickets.status
    IS 'Trouble Ticket Status';
COMMENT ON COLUMN trbtkt.secure_tickets.document
    IS 'Large Object (LOB) that contains a Microsoft Word document that provides detailed information about a Trouble Ticket';
COMMENT ON COLUMN trbtkt.secure_tickets.scrnimg
    IS '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 ON
TRUNCATE 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;
/
-- 收集优化器统计信息
BEGIN
    DBMS_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 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 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'
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_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'
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 encryptFORMAT 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 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'
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_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'
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 encryptFORMAT 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     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存储利用率:
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      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        NONONOLOB        YES
TICKETS DOCUMENT     STS_PENDING  YES        NONONOLOB        YES
TICKETS DOCUMENT     STS_CLOSED   YES        NONONOLOB        YES
TICKETS DOCUMENT     STS_OPEN     YES        NONONONOYES
TICKETS SCRNIMG      STS_PENDING  CACHEREADS NONOHIGH       NOYES
TICKETS SCRNIMG      STS_OPEN     CACHEREADS NONOMEDIUM     NOYES
TICKETS SCRNIMG      STS_CLOSED   CACHEREADS NONOHIGH       NOYES
TICKETS SCRNIMG      STS_OTHER    CACHEREADS NONOHIGH       NOYES

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

【编辑推荐】

  1. Oracle数据库开发经验浅谈
  2. Oracle存储过程开发实例
  3. Oracle数据库设计提升性能的五条法则

【责任编辑：彭凡 TEL：（010）68476606】

责任编辑：彭凡 来源： ITPUB
OracleBasicFileSecureFile
相关推荐
如何从ESX迁移到ESXi？
在本文的上半部分中，我们介绍了ESXi4.1的特点与功能，下面详细描述迁移到ESXi的好处与方法。

2010-07-20 09:48:33

Watchfinder为何从AWS迁移到Azure？如何迁移？
今年Watchfinder从AWS迁移到了Azure，此举不仅为了提高可管理性，还为了将机器学习引入到关键业务流程。

2016-10-26 16:44:44

WatchfinderAWS云计算
详解如何从SVN迁移到GIT
SVN是很多程序员在用的工具，今天我们讲讲如何从SVN迁移到GIT，希望对大家有所帮助。

2011-06-24 10:10:35

SVN
如何从Windows 7迁移到Windows 10
2020年1月14日将标志着时代的终结，从此日期起，Microsoft将不再支持Windows7。如果那时您不升级软件，则将无法访问任何更新，包括该软件的新版本。

2020-01-06 12:50:50

Windows 7迁移Windows 10
如何从MongoDB迁移到MySQL？这有现成经验！
本文会介绍作者在迁移数据库的过程中遇到的一些问题，并为各位读者提供需要停机迁移数据库的可行方案，如果需要不停机迁移数据库还是需要别的方案来解决，在这里提供的方案用于百万数据量的MongoDB，预计的停机时间在两小时左右，如果数据量在千万级别以上，过长的停机时间可能是无法接受的，应该设计不停机的迁移方案。

2017-10-20 08:45:15

数据库MongoDBMySQL
案例分析：FICO如何从VMware迁移到OpenStack
就在上周，300多岁的英国巴克莱银行投身OpenStack大家庭的消息引爆朋友圈。历经300年风雨的巴克莱银行尚能放开胸怀拥抱开源云平台，那些更年轻、遗留应用资产更少的企业为什么不来尝试一下呢

2015-06-17 13:26:41

FICOVMwareOpenStack
OpenLDAP迁移：从活动目录迁移到OpenLDAP
本文介绍了从活动目录将用户迁移到Ubuntu或其它操作系统上的OpenLDAP时，应遵循的步骤。

2010-09-29 11:06:21

活动目录OpenLDAP
将数据从MySQL迁移到Oracle的注意事项
今天主要介绍将数据从MySQL迁移到Oracle和Oracle移植到mysql注意事项。

2016-11-11 00:00:16

MySQLOracle数据
从MySQL迁移到MariaDB(CentOS)
MariaDB是一个采用Maria存储引擎的MySQL分支版本，是由原来MySQL的作者MichaelWidenius创办的公司开发的免费开源的数据库服务器。RedHat企业发行版RHEL7将用MariaDB替代MySQL，一旦RHEL完成切换，基于RHEL的社区版CentOS将会紧跟着作出改变。所以呢，使用CentOS系统的这个VPS或迟或早都是要切换到MariaDB的，大势所趋啊。

2013-06-21 13:49:08

MariaDB
实战：从Unix迁移到Linux
从Unix向Linux迁移所要做的第一个步骤就是准备好一个测试环境。你的团队可能缺乏Linux的相关经验，有一个测试环境用来学习是非常必要的（不用担心误操作破坏任何东西）。

2012-05-21 10:23:36

如何从复杂单体应用快速迁移到微服务？
想必你已知道了微服务及其工作原理，现在是时候探讨如向微服务转变这个关键话题了。

2019-01-07 08:10:54

微服务单体 Web
一篇学会如何从Webpack迁移到Vite
如果你迁移的是一个较小或中等规模的项目，Vite确实能提供一些便利。无论是服务器启动还是热模块替换，它的速度都能显著提高开发效率。其配置的简洁性也是一个受欢迎的优势，而且它在设计时考虑到了原生ES模块和现代框架的兼容性，这为它的未来发展奠定了良好的基础。

2023-12-07 07:14:36

WebpackVite
OpenJDK 项目从 Mercurial 迁移到 GitHub
OpenJDK项目正从Mercurial迁移到GitHub，切换到Git源代码控制系统，预计迁移将在2020年9月完成。

2020-07-27 11:35:26

GitHub代码开发者
从 Windows 迁移到 Ubuntu 终极指南
在本教程中，我将引导您完成在UbuntuServer22.04上安装Caddy的步骤，以及如何创建一个简单的静态站点。

2022-06-06 07:24:09

Caddy开源Ubuntu
我们从 UmiJS 迁移到了 Vite
我们从UmiJS迁移到Vite已经上线半年多了。目前很多人对Vite跃跃欲试，Vite开发体验到底怎么样，今天来叙叙迁移到Vite的亲身经历。

2021-11-29 09:44:03

UmiJSVite前端
Twitter从Rails迁移到了Java
本文是从TwittermovesfromRailstoJava这篇文章翻译而来。

2011-04-25 09:35:31

TwitterJava
Linux下从MySQL迁移到MariaDB
ArchLinux昨天宣布将使用MariaDB作为MySQL的默认提供包，openSUSE已经在新近发布的12.3版中默认使用MariaDB,连Fedora也正在考虑更换为MariaDB.

2013-05-03 09:49:38

MySQLMariaDB
从Unix迁移到Linux是否可行？
从高端Unix系统迁移到x86Linux平台似乎成为一种很流行的观点，至少在理论上非常流行,但事实证明并非每个人都认为完全依靠Linux是最好的解决办法.

2009-02-06 10:32:00

UnixLinux服务器
如何准备迁移到云平台
如今的组织渴望云计算的可访问性和灵活性，但如果运营不安全，这些好处最终将毫无意义。一台配置错误的服务器和企业可能会面临需要数年才能克服的财务或声誉损失。

2021-10-08 11:07:54

云计算数据中心IT
你应该从SVN迁移到Git吗？
最近整个技术社区都在讨论Git。很多人都推崇Git，说它是多么多么的牛逼，如果你还不从SVN或者CVS迁移到就会被淘汰云云。我并不同意这个观点。我承认Git的设计比SVN要好，但是真的好到让你马上不顾一切的迁移所有的SVN代码库到Git吗？我看来看去，Git真的比SVN强的地方就两点。

2012-02-24 09:34:48

SVNGit
点赞
收藏

51CTO技术栈公众号

业务
速览
在线客服