Oracle中如何从BasicFile迁移到SecureFile

Oracle中如何从BasicFile迁移到SecureFile
      我创建了一个新表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条目。
相关阅读:
PythonStudy——epoll 模块实现异步IO模型
 MySQLStudy——Mac下MySQL 允许用户远程访问数据库
 MySQLStudy——MySQL 基础语句
 MySQLStudy——MySQL 概念
 MySQLStudy——Mac下MySQL 常用命令启动关闭重启服务查看版本
 PythonStudy——IO模型
 PythonStudy——非阻塞IO模型
 PythonStudy——多路复用IO select实现
 restfull api
斜体菜单
原文地址：https://www.cnblogs.com/EricChan/p/1439342.html

最新文章
JS
HTML定位
 HTML
数据备份和恢复
 Mysql （基本操作，索引）知识点补充
 单例模式
 元类补充
 ORM + 数据库链接池
 vue学习day02
vue学习day01