本文详细介绍了如何利用Oracle11g的SecureFile存储特性，从BasicFile迁移到SecureFile，以提高数据仓库和商业智能应用程序中LOB的效率。文章还探讨了SecureFile与BasicFile的比较、元数据管理、空间利用率评估及属性修改等关键点。
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列
CREATETABLEtrbtkt.tickets (
tkt_idNUMBER
,descriptionVARCHAR2(30)
,submit_dtmTIMESTAMP
,statusVARCHAR2(8)
,document BLOB
,scrnimgBLOB
)
LOB(document)
STOREASBASICFILE (TABLESPACE basicfiles)
,LOB(scrnimg)
STOREASBASICFILE (TABLESPACE basicfiles)
PARTITIONBYLIST (status) (
PARTITION sts_open
VALUES('OPEN')
,PARTITION sts_pending
VALUES('PENDING')
,PARTITION sts_closed
VALUES('CLOSED')
,PARTITION sts_other
VALUES(DEFAULT)
)
;
--注释
COMMENTONTABLEtrbtkt.tickets
IS'Contains Trouble Ticket transaction data';
COMMENTONCOLUMNtrbtkt.tickets.tkt_id
IS'Unique identifier for a Trouble Ticket';
COMMENTONCOLUMNtrbtkt.tickets.description
IS'Trouble Ticket Description';
COMMENTONCOLUMNtrbtkt.tickets.submit_dtm
IS'Trouble Ticket Submission Time Stamp';
COMMENTONCOLUMNtrbtkt.tickets.status
IS'Trouble Ticket Status';
COMMENTONCOLUMNtrbtkt.tickets.document
IS'Large Object (LOB) that contains a Microsoft Word document that provides detailed information about a Trouble Ticket';
COMMENTONCOLUMNtrbtkt.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';
--创建索引和约束
CREATEUNIQUEINDEXtrbtkt.tickets_pk_idx
ONtrbtkt.tickets(tkt_id)
TABLESPACE users;
ALTERTABLEtrbtkt.tickets
ADDCONSTRAINTtickets_pk
PRIMARYKEY(tkt_id);
-----
--创建表TRBTKT.SECURE_TICKETS的一个改良版本，使用分区划分SecureFile LOB的存储，基于STATUS 的值设置合适的保留周期
DROPTABLEtrbtkt.secure_tickets PURGE;
CREATETABLEtrbtkt.secure_tickets (
tkt_idNUMBER
,descriptionVARCHAR2(30)
,submit_dtmTIMESTAMP
,statusVARCHAR2(8)
,document BLOB
,scrnimgBLOB
)
LOB(document)
STOREASSECUREFILE (
TABLESPACE securefiles
DISABLE STORAGEINROW
CACHE
)
,LOB(scrnimg)
STOREASSECUREFILE (
TABLESPACE securefiles
DISABLE STORAGEINROW
CACHE READS
)
PARTITIONBYLIST (status) (
PARTITION sts_open
VALUES('OPEN')
LOB (document) STOREASSECUREFILE (KEEP_DUPLICATES NOCOMPRESS)
,LOB (scrnimg)STOREASSECUREFILE (KEEP_DUPLICATES COMPRESS)
,PARTITION sts_pending
VALUES('PENDING')
LOB (document) STOREASSECUREFILE (KEEP_DUPLICATES NOCOMPRESS)
,LOB (scrnimg)STOREASSECUREFILE (DEDUPLICATE COMPRESS)
,PARTITION sts_closed
VALUES('CLOSED')
LOB (document) STOREASSECUREFILE (DEDUPLICATE COMPRESS)
,LOB (scrnimg)STOREASSECUREFILE (DEDUPLICATE COMPRESS HIGH)
,PARTITION sts_other
VALUES(DEFAULT)
LOB (document) STOREASSECUREFILE (DEDUPLICATE COMPRESS HIGH)
,LOB (scrnimg)STOREASSECUREFILE (DEDUPLICATE COMPRESS HIGH)
)
;
--注释
COMMENTONTABLEtrbtkt.secure_tickets
IS'Contains Trouble Ticket transaction data';
COMMENTONCOLUMNtrbtkt.secure_tickets.tkt_id
IS'Unique identifier for a Trouble Ticket';
COMMENTONCOLUMNtrbtkt.secure_tickets.description
IS'Trouble Ticket Description';
COMMENTONCOLUMNtrbtkt.secure_tickets.submit_dtm
IS'Trouble Ticket Submission Time Stamp';
COMMENTONCOLUMNtrbtkt.secure_tickets.status
IS'Trouble Ticket Status';
COMMENTONCOLUMNtrbtkt.secure_tickets.document
IS'Large Object (LOB) that contains a Microsoft Word document that provides detailed information about a Trouble Ticket';
COMMENTONCOLUMNtrbtkt.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';
CREATETABLEtrbtkt.tickets (
tkt_idNUMBER
,descriptionVARCHAR2(30)
,submit_dtmTIMESTAMP
,statusVARCHAR2(8)
,document BLOB
,scrnimgBLOB
)
LOB(document)
STOREASBASICFILE (TABLESPACE basicfiles)
,LOB(scrnimg)
STOREASBASICFILE (TABLESPACE basicfiles)
PARTITIONBYLIST (status) (
PARTITION sts_open
VALUES('OPEN')
,PARTITION sts_pending
VALUES('PENDING')
,PARTITION sts_closed
VALUES('CLOSED')
,PARTITION sts_other
VALUES(DEFAULT)
)
;
--注释
COMMENTONTABLEtrbtkt.tickets
IS'Contains Trouble Ticket transaction data';
COMMENTONCOLUMNtrbtkt.tickets.tkt_id
IS'Unique identifier for a Trouble Ticket';
COMMENTONCOLUMNtrbtkt.tickets.description
IS'Trouble Ticket Description';
COMMENTONCOLUMNtrbtkt.tickets.submit_dtm
IS'Trouble Ticket Submission Time Stamp';
COMMENTONCOLUMNtrbtkt.tickets.status
IS'Trouble Ticket Status';
COMMENTONCOLUMNtrbtkt.tickets.document
IS'Large Object (LOB) that contains a Microsoft Word document that provides detailed information about a Trouble Ticket';
COMMENTONCOLUMNtrbtkt.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';
--创建索引和约束
CREATEUNIQUEINDEXtrbtkt.tickets_pk_idx
ONtrbtkt.tickets(tkt_id)
TABLESPACE users;
ALTERTABLEtrbtkt.tickets
ADDCONSTRAINTtickets_pk
PRIMARYKEY(tkt_id);
-----
--创建表TRBTKT.SECURE_TICKETS的一个改良版本，使用分区划分SecureFile LOB的存储，基于STATUS 的值设置合适的保留周期
DROPTABLEtrbtkt.secure_tickets PURGE;
CREATETABLEtrbtkt.secure_tickets (
tkt_idNUMBER
,descriptionVARCHAR2(30)
,submit_dtmTIMESTAMP
,statusVARCHAR2(8)
,document BLOB
,scrnimgBLOB
)
LOB(document)
STOREASSECUREFILE (
TABLESPACE securefiles
DISABLE STORAGEINROW
CACHE
)
,LOB(scrnimg)
STOREASSECUREFILE (
TABLESPACE securefiles
DISABLE STORAGEINROW
CACHE READS
)
PARTITIONBYLIST (status) (
PARTITION sts_open
VALUES('OPEN')
LOB (document) STOREASSECUREFILE (KEEP_DUPLICATES NOCOMPRESS)
,LOB (scrnimg)STOREASSECUREFILE (KEEP_DUPLICATES COMPRESS)
,PARTITION sts_pending
VALUES('PENDING')
LOB (document) STOREASSECUREFILE (KEEP_DUPLICATES NOCOMPRESS)
,LOB (scrnimg)STOREASSECUREFILE (DEDUPLICATE COMPRESS)
,PARTITION sts_closed
VALUES('CLOSED')
LOB (document) STOREASSECUREFILE (DEDUPLICATE COMPRESS)
,LOB (scrnimg)STOREASSECUREFILE (DEDUPLICATE COMPRESS HIGH)
,PARTITION sts_other
VALUES(DEFAULT)
LOB (document) STOREASSECUREFILE (DEDUPLICATE COMPRESS HIGH)
,LOB (scrnimg)STOREASSECUREFILE (DEDUPLICATE COMPRESS HIGH)
)
;
--注释
COMMENTONTABLEtrbtkt.secure_tickets
IS'Contains Trouble Ticket transaction data';
COMMENTONCOLUMNtrbtkt.secure_tickets.tkt_id
IS'Unique identifier for a Trouble Ticket';
COMMENTONCOLUMNtrbtkt.secure_tickets.description
IS'Trouble Ticket Description';
COMMENTONCOLUMNtrbtkt.secure_tickets.submit_dtm
IS'Trouble Ticket Submission Time Stamp';
COMMENTONCOLUMNtrbtkt.secure_tickets.status
IS'Trouble Ticket Status';
COMMENTONCOLUMNtrbtkt.secure_tickets.document
IS'Large Object (LOB) that contains a Microsoft Word document that provides detailed information about a Trouble Ticket';
COMMENTONCOLUMNtrbtkt.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
SETSERVEROUTPUTON
TRUNCATETABLEtrbtkt.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;
TRUNCATETABLEtrbtkt.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的元数据
SETPAGESIZE1000
SETLINESIZE140
--视图: DBA_SEGMENTS
--显示关于BasicFile 和SecureFile 段的元数据
TTITLE'LOB Segment Information|(from DBA_SEGMENTS)'
COL segment_nameFORMAT A30HEADING'Segment Name'
COL segment_typeFORMAT A20HEADING'Segment|Type'
COL segment_subtype FORMAT A20HEADING'Segment|SubType'
COL partition_nameFORMAT A12HEADING'Partition|Name'
COL tablespace_name FORMAT A12HEADING'Tablespace'
SELECT
segment_name
,segment_type
,segment_subtype
,partition_name
,tablespace_name
FROMdba_segments
WHEREowner='TRBTKT'
ORDERBYsegment_name
;
TTITLEOFF
--视图: DBA_LOBS
--显示关于BasicFile和SecureFile LOB的元数据
TTITLE'BasicFile and SecureFile LOBs Metadata|(from DBA_LOBS)'
COL table_nameFORMAT A14HEADING'Table'
COL segment_nameFORMAT A26HEADING'Segment'
COL column_name FORMAT A10HEADING'Column'
COL tablespace_name FORMAT A12HEADING'Tablespace'
COL logging FORMAT A08HEADING'Logging'
COL cache FORMAT A10HEADING'Cacheing'
COL in_rowFORMAT A07HEADING'Stored|In Row'
COL encrypt FORMAT A07HEADING'Encryp-|tion'
COL compression FORMAT A07HEADING'Compre-|ssion'
COL deduplication FORMAT A08HEADING'DeDupli-|cation'
COL securefileFORMAT A07HEADING'Secure|File?'
COL partitioned FORMAT A07HEADING'Parti-|tioned'
SELECT
table_name
,column_name
,segment_name
,tablespace_name
,logging
,cache
,in_row
,encrypt
,compression
,deduplication
,securefile
,partitioned
FROMdba_lobs
WHEREowner='TRBTKT'
ORDERBYtable_name, column_name
;
TTITLEOFF
--视图: DBA_PART_LOBS
--显示BasicFile和SecureFile LOB的默认值
TTITLE'BasicFile and SecureFile Partitioned LOB Default Settings|(from DBA_PART_LOBS)'
COL table_nameFORMAT A20HEADING'Table'
COL column_name FORMAT A12HEADING'Column'
COL def_cache FORMAT A12HEADING'Cached'
COL def_tablespace_name FORMAT A12HEADING'Tablespace'
COL def_securefileFORMAT A12HEADING'SecureFile'
COL def_encrypt FORMAT A12HEADING'Encrypted'
COL def_compressFORMAT A12HEADING'Compressed'
COL def_deduplicate FORMAT A12HEADING'DeDuplicated'
SELECT
table_name
,column_name
,def_cache
,def_tablespace_name
,def_securefile
,def_compress
,def_deduplicate
,def_encrypt
FROMdba_part_lobs
WHEREtable_owner='TRBTKT'
ORDERBYtable_name, column_name
;
TTITLEOFF
--视图: DBA_LOB_PARTITIONS
--在独立的LOB级描述BasicFile和SecureFile设置
TTITLE'BasicFile and SecureFile LOB Partitions|(from DBA_LOB_PARTITIONS)'
COL table_nameFORMAT A16HEADING'Table'
COL column_name FORMAT A12HEADING'Column'
COL partition_nameFORMAT A12HEADING'Stored in|Partition'
COL cache FORMAT A10HEADING'Cacheing'
COL in_rowFORMAT A10HEADING'Stored|In Row'
COL encrypt FORMAT A10HEADING'Encrypted'
COL compression FORMAT A10HEADING'Compressed'
COL deduplication FORMAT A10HEADING'DeDupli-|cated'
COL securefileFORMAT A10HEADING'SecureFile?'
SELECT
table_name
,column_name
,partition_name
,cache
,in_row
,encrypt
,compression
,deduplication
,securefile
FROMdba_lob_partitions
WHEREtable_owner='TRBTKT'
ORDERBYtable_name, column_name
;
TTITLEOFF
SETLINESIZE140
--视图: DBA_SEGMENTS
--显示关于BasicFile 和SecureFile 段的元数据
TTITLE'LOB Segment Information|(from DBA_SEGMENTS)'
COL segment_nameFORMAT A30HEADING'Segment Name'
COL segment_typeFORMAT A20HEADING'Segment|Type'
COL segment_subtype FORMAT A20HEADING'Segment|SubType'
COL partition_nameFORMAT A12HEADING'Partition|Name'
COL tablespace_name FORMAT A12HEADING'Tablespace'
SELECT
segment_name
,segment_type
,segment_subtype
,partition_name
,tablespace_name
FROMdba_segments
WHEREowner='TRBTKT'
ORDERBYsegment_name
;
TTITLEOFF
--视图: DBA_LOBS
--显示关于BasicFile和SecureFile LOB的元数据
TTITLE'BasicFile and SecureFile LOBs Metadata|(from DBA_LOBS)'
COL table_nameFORMAT A14HEADING'Table'
COL segment_nameFORMAT A26HEADING'Segment'
COL column_name FORMAT A10HEADING'Column'
COL tablespace_name FORMAT A12HEADING'Tablespace'
COL logging FORMAT A08HEADING'Logging'
COL cache FORMAT A10HEADING'Cacheing'
COL in_rowFORMAT A07HEADING'Stored|In Row'
COL encrypt FORMAT A07HEADING'Encryp-|tion'
COL compression FORMAT A07HEADING'Compre-|ssion'
COL deduplication FORMAT A08HEADING'DeDupli-|cation'
COL securefileFORMAT A07HEADING'Secure|File?'
COL partitioned FORMAT A07HEADING'Parti-|tioned'
SELECT
table_name
,column_name
,segment_name
,tablespace_name
,logging
,cache
,in_row
,encrypt
,compression
,deduplication
,securefile
,partitioned
FROMdba_lobs
WHEREowner='TRBTKT'
ORDERBYtable_name, column_name
;
TTITLEOFF
--视图: DBA_PART_LOBS
--显示BasicFile和SecureFile LOB的默认值
TTITLE'BasicFile and SecureFile Partitioned LOB Default Settings|(from DBA_PART_LOBS)'
COL table_nameFORMAT A20HEADING'Table'
COL column_name FORMAT A12HEADING'Column'
COL def_cache FORMAT A12HEADING'Cached'
COL def_tablespace_name FORMAT A12HEADING'Tablespace'
COL def_securefileFORMAT A12HEADING'SecureFile'
COL def_encrypt FORMAT A12HEADING'Encrypted'
COL def_compressFORMAT A12HEADING'Compressed'
COL def_deduplicate FORMAT A12HEADING'DeDuplicated'
SELECT
table_name
,column_name
,def_cache
,def_tablespace_name
,def_securefile
,def_compress
,def_deduplicate
,def_encrypt
FROMdba_part_lobs
WHEREtable_owner='TRBTKT'
ORDERBYtable_name, column_name
;
TTITLEOFF
--视图: DBA_LOB_PARTITIONS
--在独立的LOB级描述BasicFile和SecureFile设置
TTITLE'BasicFile and SecureFile LOB Partitions|(from DBA_LOB_PARTITIONS)'
COL table_nameFORMAT A16HEADING'Table'
COL column_name FORMAT A12HEADING'Column'
COL partition_nameFORMAT A12HEADING'Stored in|Partition'
COL cache FORMAT A10HEADING'Cacheing'
COL in_rowFORMAT A10HEADING'Stored|In Row'
COL encrypt FORMAT A10HEADING'Encrypted'
COL compression FORMAT A10HEADING'Compressed'
COL deduplication FORMAT A10HEADING'DeDupli-|cated'
COL securefileFORMAT A10HEADING'SecureFile?'
SELECT
table_name
,column_name
,partition_name
,cache
,in_row
,encrypt
,compression
,deduplication
,securefile
FROMdba_lob_partitions
WHEREtable_owner='TRBTKT'
ORDERBYtable_name, column_name
;
TTITLEOFF
报告1 从BasicFIle和SecureFile LOB元数据报告返回的结果
SETPAGESIZE1000
SETLINESIZE140
--视图: DBA_SEGMENTS
--显示关于BasicFile 和SecureFile 段的元数据
TTITLE'LOB Segment Information|(from DBA_SEGMENTS)'
COL segment_nameFORMAT A30HEADING'Segment Name'
COL segment_typeFORMAT A20HEADING'Segment|Type'
COL segment_subtype FORMAT A20HEADING'Segment|SubType'
COL partition_nameFORMAT A12HEADING'Partition|Name'
COL tablespace_name FORMAT A12HEADING'Tablespace'
SELECT
segment_name
,segment_type
,segment_subtype
,partition_name
,tablespace_name
FROMdba_segments
WHEREowner='TRBTKT'
ORDERBYsegment_name
;
TTITLEOFF
--视图: DBA_LOBS
--显示关于BasicFile和SecureFile LOB的元数据
TTITLE'BasicFile and SecureFile LOBs Metadata|(from DBA_LOBS)'
COL table_nameFORMAT A14HEADING'Table'
COL segment_nameFORMAT A26HEADING'Segment'
COL column_name FORMAT A10HEADING'Column'
COL tablespace_name FORMAT A12HEADING'Tablespace'
COL logging FORMAT A08HEADING'Logging'
COL cache FORMAT A10HEADING'Cacheing'
COL in_rowFORMAT A07HEADING'Stored|In Row'
COL encrypt FORMAT A07HEADING'Encryp-|tion'
COL compression FORMAT A07HEADING'Compre-|ssion'
COL deduplication FORMAT A08HEADING'DeDupli-|cation'
COL securefileFORMAT A07HEADING'Secure|File?'
COL partitioned FORMAT A07HEADING'Parti-|tioned'
SELECT
table_name
,column_name
,segment_name
,tablespace_name
,logging
,cache
,in_row
,encrypt
,compression
,deduplication
,securefile
,partitioned
FROMdba_lobs
WHEREowner='TRBTKT'
ORDERBYtable_name, column_name
;
TTITLEOFF
--视图: DBA_PART_LOBS
--显示BasicFile和SecureFile LOB的默认值
TTITLE'BasicFile and SecureFile Partitioned LOB Default Settings|(from DBA_PART_LOBS)'
COL table_nameFORMAT A20HEADING'Table'
COL column_name FORMAT A12HEADING'Column'
COL def_cache FORMAT A12HEADING'Cached'
COL def_tablespace_name FORMAT A12HEADING'Tablespace'
COL def_securefileFORMAT A12HEADING'SecureFile'
COL def_encrypt FORMAT A12HEADING'Encrypted'
COL def_compressFORMAT A12HEADING'Compressed'
COL def_deduplicate FORMAT A12HEADING'DeDuplicated'
SELECT
table_name
,column_name
,def_cache
,def_tablespace_name
,def_securefile
,def_compress
,def_deduplicate
,def_encrypt
FROMdba_part_lobs
WHEREtable_owner='TRBTKT'
ORDERBYtable_name, column_name
;
TTITLEOFF
--视图: DBA_LOB_PARTITIONS
--在独立的LOB级描述BasicFile和SecureFile设置
TTITLE'BasicFile and SecureFile LOB Partitions|(from DBA_LOB_PARTITIONS)'
COL table_nameFORMAT A16HEADING'Table'
COL column_name FORMAT A12HEADING'Column'
COL partition_nameFORMAT A12HEADING'Stored in|Partition'
COL cache FORMAT A10HEADING'Cacheing'
COL in_rowFORMAT A10HEADING'Stored|In Row'
COL encrypt FORMAT A10HEADING'Encrypted'
COL compression FORMAT A10HEADING'Compressed'
COL deduplication FORMAT A10HEADING'DeDupli-|cated'
COL securefileFORMAT A10HEADING'SecureFile?'
SELECT
table_name
,column_name
,partition_name
,cache
,in_row
,encrypt
,compression
,deduplication
,securefile
FROMdba_lob_partitions
WHEREtable_owner='TRBTKT'
ORDERBYtable_name, column_name
;
TTITLEOFF
报告1 从BasicFIle和SecureFile LOB元数据报告返回的结果
LOB段信息
(来自DBA_SEGMENTS)
SegmentSegmentPartition
Segment Name Type SubTypeName Tablespace
------------------------- -------------------- -------------------- ------------ ------------
SECURE_TICKETSTABLEPARTITIONASSM STS_PENDINGUSERS
SECURE_TICKETSTABLEPARTITIONASSM STS_CLOSED USERS
SECURE_TICKETSTABLEPARTITIONASSM STS_OPEN USERS
SECURE_TICKETSTABLEPARTITIONASSM STS_OTHERUSERS
SYS_IL0000072118C00005$$INDEXPARTITIONASSM SYS_IL_P180BASICFILES
SYS_IL0000072118C00005$$INDEXPARTITIONASSM SYS_IL_P179BASICFILES
SYS_IL0000072118C00005$$INDEXPARTITIONASSM SYS_IL_P178BASICFILES
SYS_IL0000072118C00005$$INDEXPARTITIONASSM SYS_IL_P177BASICFILES
SYS_IL0000072118C00006$$INDEXPARTITIONASSM SYS_IL_P185BASICFILES
SYS_IL0000072118C00006$$INDEXPARTITIONASSM SYS_IL_P188BASICFILES
SYS_IL0000072118C00006$$INDEXPARTITIONASSM SYS_IL_P187BASICFILES
SYS_IL0000072118C00006$$INDEXPARTITIONASSM SYS_IL_P186BASICFILES
SYS_IL0000072144C00005$$INDEXPARTITIONASSM SYS_IL_P194SECUREFILES
SYS_IL0000072144C00005$$INDEXPARTITIONASSM SYS_IL_P193SECUREFILES
SYS_IL0000072144C00005$$INDEXPARTITIONASSM SYS_IL_P195SECUREFILES
SYS_IL0000072144C00005$$INDEXPARTITIONASSM SYS_IL_P196SECUREFILES
SYS_IL0000072144C00006$$INDEXPARTITIONASSM SYS_IL_P204SECUREFILES
SYS_IL0000072144C00006$$INDEXPARTITIONASSM SYS_IL_P203SECUREFILES
SYS_IL0000072144C00006$$INDEXPARTITIONASSM SYS_IL_P202SECUREFILES
SYS_IL0000072144C00006$$INDEXPARTITIONASSM SYS_IL_P201SECUREFILES
SYS_LOB0000072118C00005$$LOB PARTITIONASSM SYS_LOB_P173 BASICFILES
SYS_LOB0000072118C00005$$LOB PARTITIONASSM SYS_LOB_P176 BASICFILES
SYS_LOB0000072118C00005$$LOB PARTITIONASSM SYS_LOB_P175 BASICFILES
SYS_LOB0000072118C00005$$LOB PARTITIONASSM SYS_LOB_P174 BASICFILES
SYS_LOB0000072118C00006$$LOB PARTITIONASSM SYS_LOB_P184 BASICFILES
SYS_LOB0000072118C00006$$LOB PARTITIONASSM SYS_LOB_P183 BASICFILES
SYS_LOB0000072118C00006$$LOB PARTITIONASSM SYS_LOB_P181 BASICFILES
SYS_LOB0000072118C00006$$LOB PARTITIONASSM SYS_LOB_P182 BASICFILES
SYS_LOB0000072144C00005$$LOB PARTITIONSECUREFILE SYS_LOB_P191 SECUREFILES
SYS_LOB0000072144C00005$$LOB PARTITIONSECUREFILE SYS_LOB_P192 SECUREFILES
SYS_LOB0000072144C00005$$LOB PARTITIONSECUREFILE SYS_LOB_P189 SECUREFILES
SYS_LOB0000072144C00005$$LOB PARTITIONSECUREFILE SYS_LOB_P190 SECUREFILES
SYS_LOB0000072144C00006$$LOB PARTITIONSECUREFILE SYS_LOB_P198 SECUREFILES
SYS_LOB0000072144C00006$$LOB PARTITIONSECUREFILE SYS_LOB_P199 SECUREFILES
SYS_LOB0000072144C00006$$LOB PARTITIONSECUREFILE SYS_LOB_P197 SECUREFILES
SYS_LOB0000072144C00006$$LOB PARTITIONSECUREFILE SYS_LOB_P200 SECUREFILES
TICKETSTABLEPARTITIONASSM STS_PENDINGUSERS
TICKETSTABLEPARTITIONASSM STS_OTHERUSERS
TICKETSTABLEPARTITIONASSM STS_OPEN USERS
TICKETSTABLEPARTITIONASSM STS_CLOSED USERS
TICKETS_PK_IDXINDEXASSMUSERS
BasicFile和SecureFile LOB元数据
(来自DBA_LOBS)
StoredEncryp-Compre-DeDupli-SecureParti-
TableColumn SegmentTablespace LoggingCacheingInRowtionssion cationFile? tioned
-------------- ---------- -------------------------- ------------ -------- ---------- ------- ------- ------- -------- ------- -------
SECURE_TICKETS DOCUMENT SYS_LOB0000072118C00005$$BASICFILES NONE NO YES NONENONENONE NOYES
SECURE_TICKETS SCRNIMGSYS_LOB0000072118C00006$$BASICFILES NONE NO YES NONENONENONE NOYES
TICKETSDOCUMENT SYS_LOB0000072144C00005$$SECUREFILESNONE YESNONONONO YES YES
TICKETSSCRNIMGSYS_LOB0000072144C00006$$SECUREFILESNONE CACHEREADS NONONONO YES YES
BasicFile和SecureFile分区LOB默认设置
(来自DBA_PART_LOBS)
TableColumn Cached Tablespace SecureFile Compressed DeDuplicated Encrypted
----- ------------ ------------ ------------ ------------ ------------ ------------ ------------
SECURE_TICKETS DOCUMENT NO BASICFILES NO NONE NONE NONE
SECURE_TICKETS SCRNIMGNO BASICFILES NO NONE NONE NONE
TICKETSDOCUMENT YESSECUREFILESYESNO NO NO
TICKETSSCRNIMGCACHEREADS SECUREFILESYESNO NO NO
BasicFile和SecureFile LOB分区
(来自DBA_LOB_PARTITIONS)
Storedin Stored DeDupli-
TableColumn PartitionCacheingInRow EncryptedCompressed catedSecureFile
---- ------------ ------------ ---------- ---------- ---------- ---------- ---------- ----------
SECURE_TICKETS DOCUMENT STS_OTHERNO YESNONE NONE NONE NO
SECURE_TICKETS DOCUMENT STS_CLOSED NO YESNONE NONE NONE NO
SECURE_TICKETS DOCUMENT STS_OPEN NO YESNONE NONE NONE NO
SECURE_TICKETS DOCUMENT STS_PENDINGNO YESNONE NONE NONE NO
SECURE_TICKETS SCRNIMGSTS_OTHERNO YESNONE NONE NONE NO
SECURE_TICKETS SCRNIMGSTS_CLOSED NO YESNONE NONE NONE NO
SECURE_TICKETS SCRNIMGSTS_PENDINGNO YESNONE NONE NONE NO
SECURE_TICKETS SCRNIMGSTS_OPEN NO YESNONE NONE NONE NO
TICKETSDOCUMENT STS_OTHERYESNO NO HIGH LOBYES
TICKETSDOCUMENT STS_PENDINGYESNO NO NO NO YES
TICKETSDOCUMENT STS_CLOSED YESNO NO MEDIUM LOBYES
TICKETSDOCUMENT STS_OPEN YESNO NO NO NO YES
TICKETSSCRNIMGSTS_PENDINGCACHEREADS NO NO MEDIUM LOBYES
TICKETSSCRNIMGSTS_OPEN CACHEREADS NO NO MEDIUM NO YES
TICKETSSCRNIMGSTS_CLOSED CACHEREADS NO NO HIGH LOBYES
TICKETSSCRNIMGSTS_OTHERCACHEREADS NO NO HIGH LOBYES
SETLINESIZE140
--视图: DBA_SEGMENTS
--显示关于BasicFile 和SecureFile 段的元数据
TTITLE'LOB Segment Information|(from DBA_SEGMENTS)'
COL segment_nameFORMAT A30HEADING'Segment Name'
COL segment_typeFORMAT A20HEADING'Segment|Type'
COL segment_subtype FORMAT A20HEADING'Segment|SubType'
COL partition_nameFORMAT A12HEADING'Partition|Name'
COL tablespace_name FORMAT A12HEADING'Tablespace'
SELECT
segment_name
,segment_type
,segment_subtype
,partition_name
,tablespace_name
FROMdba_segments
WHEREowner='TRBTKT'
ORDERBYsegment_name
;
TTITLEOFF
--视图: DBA_LOBS
--显示关于BasicFile和SecureFile LOB的元数据
TTITLE'BasicFile and SecureFile LOBs Metadata|(from DBA_LOBS)'
COL table_nameFORMAT A14HEADING'Table'
COL segment_nameFORMAT A26HEADING'Segment'
COL column_name FORMAT A10HEADING'Column'
COL tablespace_name FORMAT A12HEADING'Tablespace'
COL logging FORMAT A08HEADING'Logging'
COL cache FORMAT A10HEADING'Cacheing'
COL in_rowFORMAT A07HEADING'Stored|In Row'
COL encrypt FORMAT A07HEADING'Encryp-|tion'
COL compression FORMAT A07HEADING'Compre-|ssion'
COL deduplication FORMAT A08HEADING'DeDupli-|cation'
COL securefileFORMAT A07HEADING'Secure|File?'
COL partitioned FORMAT A07HEADING'Parti-|tioned'
SELECT
table_name
,column_name
,segment_name
,tablespace_name
,logging
,cache
,in_row
,encrypt
,compression
,deduplication
,securefile
,partitioned
FROMdba_lobs
WHEREowner='TRBTKT'
ORDERBYtable_name, column_name
;
TTITLEOFF
--视图: DBA_PART_LOBS
--显示BasicFile和SecureFile LOB的默认值
TTITLE'BasicFile and SecureFile Partitioned LOB Default Settings|(from DBA_PART_LOBS)'
COL table_nameFORMAT A20HEADING'Table'
COL column_name FORMAT A12HEADING'Column'
COL def_cache FORMAT A12HEADING'Cached'
COL def_tablespace_name FORMAT A12HEADING'Tablespace'
COL def_securefileFORMAT A12HEADING'SecureFile'
COL def_encrypt FORMAT A12HEADING'Encrypted'
COL def_compressFORMAT A12HEADING'Compressed'
COL def_deduplicate FORMAT A12HEADING'DeDuplicated'
SELECT
table_name
,column_name
,def_cache
,def_tablespace_name
,def_securefile
,def_compress
,def_deduplicate
,def_encrypt
FROMdba_part_lobs
WHEREtable_owner='TRBTKT'
ORDERBYtable_name, column_name
;
TTITLEOFF
--视图: DBA_LOB_PARTITIONS
--在独立的LOB级描述BasicFile和SecureFile设置
TTITLE'BasicFile and SecureFile LOB Partitions|(from DBA_LOB_PARTITIONS)'
COL table_nameFORMAT A16HEADING'Table'
COL column_name FORMAT A12HEADING'Column'
COL partition_nameFORMAT A12HEADING'Stored in|Partition'
COL cache FORMAT A10HEADING'Cacheing'
COL in_rowFORMAT A10HEADING'Stored|In Row'
COL encrypt FORMAT A10HEADING'Encrypted'
COL compression FORMAT A10HEADING'Compressed'
COL deduplication FORMAT A10HEADING'DeDupli-|cated'
COL securefileFORMAT A10HEADING'SecureFile?'
SELECT
table_name
,column_name
,partition_name
,cache
,in_row
,encrypt
,compression
,deduplication
,securefile
FROMdba_lob_partitions
WHEREtable_owner='TRBTKT'
ORDERBYtable_name, column_name
;
TTITLEOFF
报告1 从BasicFIle和SecureFile LOB元数据报告返回的结果
LOB段信息
(来自DBA_SEGMENTS)
SegmentSegmentPartition
Segment Name Type SubTypeName Tablespace
------------------------- -------------------- -------------------- ------------ ------------
SECURE_TICKETSTABLEPARTITIONASSM STS_PENDINGUSERS
SECURE_TICKETSTABLEPARTITIONASSM STS_CLOSED USERS
SECURE_TICKETSTABLEPARTITIONASSM STS_OPEN USERS
SECURE_TICKETSTABLEPARTITIONASSM STS_OTHERUSERS
SYS_IL0000072118C00005$$INDEXPARTITIONASSM SYS_IL_P180BASICFILES
SYS_IL0000072118C00005$$INDEXPARTITIONASSM SYS_IL_P179BASICFILES
SYS_IL0000072118C00005$$INDEXPARTITIONASSM SYS_IL_P178BASICFILES
SYS_IL0000072118C00005$$INDEXPARTITIONASSM SYS_IL_P177BASICFILES
SYS_IL0000072118C00006$$INDEXPARTITIONASSM SYS_IL_P185BASICFILES
SYS_IL0000072118C00006$$INDEXPARTITIONASSM SYS_IL_P188BASICFILES
SYS_IL0000072118C00006$$INDEXPARTITIONASSM SYS_IL_P187BASICFILES
SYS_IL0000072118C00006$$INDEXPARTITIONASSM SYS_IL_P186BASICFILES
SYS_IL0000072144C00005$$INDEXPARTITIONASSM SYS_IL_P194SECUREFILES
SYS_IL0000072144C00005$$INDEXPARTITIONASSM SYS_IL_P193SECUREFILES
SYS_IL0000072144C00005$$INDEXPARTITIONASSM SYS_IL_P195SECUREFILES
SYS_IL0000072144C00005$$INDEXPARTITIONASSM SYS_IL_P196SECUREFILES
SYS_IL0000072144C00006$$INDEXPARTITIONASSM SYS_IL_P204SECUREFILES
SYS_IL0000072144C00006$$INDEXPARTITIONASSM SYS_IL_P203SECUREFILES
SYS_IL0000072144C00006$$INDEXPARTITIONASSM SYS_IL_P202SECUREFILES
SYS_IL0000072144C00006$$INDEXPARTITIONASSM SYS_IL_P201SECUREFILES
SYS_LOB0000072118C00005$$LOB PARTITIONASSM SYS_LOB_P173 BASICFILES
SYS_LOB0000072118C00005$$LOB PARTITIONASSM SYS_LOB_P176 BASICFILES
SYS_LOB0000072118C00005$$LOB PARTITIONASSM SYS_LOB_P175 BASICFILES
SYS_LOB0000072118C00005$$LOB PARTITIONASSM SYS_LOB_P174 BASICFILES
SYS_LOB0000072118C00006$$LOB PARTITIONASSM SYS_LOB_P184 BASICFILES
SYS_LOB0000072118C00006$$LOB PARTITIONASSM SYS_LOB_P183 BASICFILES
SYS_LOB0000072118C00006$$LOB PARTITIONASSM SYS_LOB_P181 BASICFILES
SYS_LOB0000072118C00006$$LOB PARTITIONASSM SYS_LOB_P182 BASICFILES
SYS_LOB0000072144C00005$$LOB PARTITIONSECUREFILE SYS_LOB_P191 SECUREFILES
SYS_LOB0000072144C00005$$LOB PARTITIONSECUREFILE SYS_LOB_P192 SECUREFILES
SYS_LOB0000072144C00005$$LOB PARTITIONSECUREFILE SYS_LOB_P189 SECUREFILES
SYS_LOB0000072144C00005$$LOB PARTITIONSECUREFILE SYS_LOB_P190 SECUREFILES
SYS_LOB0000072144C00006$$LOB PARTITIONSECUREFILE SYS_LOB_P198 SECUREFILES
SYS_LOB0000072144C00006$$LOB PARTITIONSECUREFILE SYS_LOB_P199 SECUREFILES
SYS_LOB0000072144C00006$$LOB PARTITIONSECUREFILE SYS_LOB_P197 SECUREFILES
SYS_LOB0000072144C00006$$LOB PARTITIONSECUREFILE SYS_LOB_P200 SECUREFILES
TICKETSTABLEPARTITIONASSM STS_PENDINGUSERS
TICKETSTABLEPARTITIONASSM STS_OTHERUSERS
TICKETSTABLEPARTITIONASSM STS_OPEN USERS
TICKETSTABLEPARTITIONASSM STS_CLOSED USERS
TICKETS_PK_IDXINDEXASSMUSERS
BasicFile和SecureFile LOB元数据
(来自DBA_LOBS)
StoredEncryp-Compre-DeDupli-SecureParti-
TableColumn SegmentTablespace LoggingCacheingInRowtionssion cationFile? tioned
-------------- ---------- -------------------------- ------------ -------- ---------- ------- ------- ------- -------- ------- -------
SECURE_TICKETS DOCUMENT SYS_LOB0000072118C00005$$BASICFILES NONE NO YES NONENONENONE NOYES
SECURE_TICKETS SCRNIMGSYS_LOB0000072118C00006$$BASICFILES NONE NO YES NONENONENONE NOYES
TICKETSDOCUMENT SYS_LOB0000072144C00005$$SECUREFILESNONE YESNONONONO YES YES
TICKETSSCRNIMGSYS_LOB0000072144C00006$$SECUREFILESNONE CACHEREADS NONONONO YES YES
BasicFile和SecureFile分区LOB默认设置
(来自DBA_PART_LOBS)
TableColumn Cached Tablespace SecureFile Compressed DeDuplicated Encrypted
----- ------------ ------------ ------------ ------------ ------------ ------------ ------------
SECURE_TICKETS DOCUMENT NO BASICFILES NO NONE NONE NONE
SECURE_TICKETS SCRNIMGNO BASICFILES NO NONE NONE NONE
TICKETSDOCUMENT YESSECUREFILESYESNO NO NO
TICKETSSCRNIMGCACHEREADS SECUREFILESYESNO NO NO
BasicFile和SecureFile LOB分区
(来自DBA_LOB_PARTITIONS)
Storedin Stored DeDupli-
TableColumn PartitionCacheingInRow EncryptedCompressed catedSecureFile
---- ------------ ------------ ---------- ---------- ---------- ---------- ---------- ----------
SECURE_TICKETS DOCUMENT STS_OTHERNO YESNONE NONE NONE NO
SECURE_TICKETS DOCUMENT STS_CLOSED NO YESNONE NONE NONE NO
SECURE_TICKETS DOCUMENT STS_OPEN NO YESNONE NONE NONE NO
SECURE_TICKETS DOCUMENT STS_PENDINGNO YESNONE NONE NONE NO
SECURE_TICKETS SCRNIMGSTS_OTHERNO YESNONE NONE NONE NO
SECURE_TICKETS SCRNIMGSTS_CLOSED NO YESNONE NONE NONE NO
SECURE_TICKETS SCRNIMGSTS_PENDINGNO YESNONE NONE NONE NO
SECURE_TICKETS SCRNIMGSTS_OPEN NO YESNONE NONE NONE NO
TICKETSDOCUMENT STS_OTHERYESNO NO HIGH LOBYES
TICKETSDOCUMENT STS_PENDINGYESNO NO NO NO YES
TICKETSDOCUMENT STS_CLOSED YESNO NO MEDIUM LOBYES
TICKETSDOCUMENT STS_OPEN YESNO NO NO NO YES
TICKETSSCRNIMGSTS_PENDINGCACHEREADS NO NO MEDIUM LOBYES
TICKETSSCRNIMGSTS_OPEN CACHEREADS NO NO MEDIUM NO YES
TICKETSSCRNIMGSTS_CLOSED CACHEREADS NO NO HIGH LOBYES
TICKETSSCRNIMGSTS_OTHERCACHEREADS NO NO HIGH LOBYES
2、DBMS_SPACE
这是另一个Oracle古老支持包，它的SPACE_USAGE存储过程提供关于BasicFile和SecureFile LOB的空间利用率，我在TRBTKT.PKG_SECUREFILES包中引入了两个存储过程：CALC_SPACE_BASICFILES和CALC_SPACE_SECUREFILES。但遗憾的是，这个功能只能在启用了自动段空间管理(ASSM)的表空间上使用，而且，它也不会考虑任何BasicFile LOB块的空间利用率，在清单5中，我对表TRBTKT.TICKETS和TRBTKT.SECURE_TICKETS分别调用了这些存储过程，并返回了相应的输出。
清单5 确定BasicFile和SecureFile LOB的空间利用率
SETSERVEROUTPUTON
--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;
/
============================================================
SpaceUsageforBasicFile LOB TRBTKT.SECURE_TICKETS.DOCUMENT
Partition Name: STS_OPEN
------------------------------------------------------------
FullBlocks:123KB: .96
Unformatted Blocks:379KB:2.96
Total Blocks:123Total KB: .96
============================================================
============================================================
SpaceUsageforBasicFile LOB TRBTKT.SECURE_TICKETS.DOCUMENT
Partition Name: STS_PENDING
------------------------------------------------------------
FullBlocks:20KB: .16
Unformatted Blocks:482KB:3.77
Total Blocks:20Total KB: .16
============================================================
============================================================
SpaceUsageforBasicFile LOB TRBTKT.SECURE_TICKETS.DOCUMENT
Partition Name: STS_CLOSED
------------------------------------------------------------
FullBlocks:37KB: .29
Unformatted Blocks:465KB:3.63
Total Blocks:37Total KB: .29
============================================================
============================================================
SpaceUsageforBasicFile LOB TRBTKT.SECURE_TICKETS.DOCUMENT
Partition Name: STS_OTHER
------------------------------------------------------------
FullBlocks:0KB:0
Unformatted Blocks:0KB:0
Total Blocks:0Total KB:0
============================================================
============================================================
SpaceUsageforBasicFile LOB TRBTKT.SECURE_TICKETS.SCRNIMG
Partition Name: STS_OPEN
------------------------------------------------------------
FullBlocks:420KB:3.28
Unformatted Blocks:82KB: .64
Total Blocks:420Total KB:3.28
============================================================
============================================================
SpaceUsageforBasicFile LOB TRBTKT.SECURE_TICKETS.SCRNIMG
Partition Name: STS_PENDING
------------------------------------------------------------
FullBlocks:66KB: .52
Unformatted Blocks:436KB:3.41
Total Blocks:66Total KB: .52
============================================================
============================================================
SpaceUsageforBasicFile LOB TRBTKT.SECURE_TICKETS.SCRNIMG
Partition Name: STS_CLOSED
------------------------------------------------------------
FullBlocks:144KB:1.13
Unformatted Blocks:358KB:2.8
Total Blocks:144Total KB:1.13
============================================================
============================================================
SpaceUsageforBasicFile LOB TRBTKT.SECURE_TICKETS.SCRNIMG
Partition Name: STS_OTHER
------------------------------------------------------------
FullBlocks:0KB:0
Unformatted Blocks:0KB:0
Total Blocks:0Total 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;
/
============================================================
SpaceUsageforSecureFile LOB TRBTKT.TICKETS.DOCUMENT
Partition Name: STS_OPEN
------------------------------------------------------------
Segment Blocks:1024KB:8192
Used Blocks:124KB:992
Expired Blocks:882KB:7056
Unexpired Blocks:0KB:0
============================================================
============================================================
SpaceUsageforSecureFile LOB TRBTKT.TICKETS.DOCUMENT
Partition Name: STS_PENDING
------------------------------------------------------------
Segment Blocks:1024KB:8192
Used Blocks:21KB:168
Expired Blocks:985KB:7880
Unexpired Blocks:0KB:0
============================================================
============================================================
SpaceUsageforSecureFile LOB TRBTKT.TICKETS.DOCUMENT
Partition Name: STS_CLOSED
------------------------------------------------------------
Segment Blocks:1024KB:8192
Used Blocks:13KB:104
Expired Blocks:993KB:7944
Unexpired Blocks:0KB:0
============================================================
============================================================
SpaceUsageforSecureFile LOB TRBTKT.TICKETS.DOCUMENT
Partition Name: STS_OTHER
------------------------------------------------------------
Segment Blocks:512KB:4096
Used Blocks:501KB:4008
Expired Blocks:0KB:0
Unexpired Blocks:0KB:0
============================================================
============================================================
SpaceUsageforSecureFile LOB TRBTKT.TICKETS.SCRNIMG
Partition Name: STS_OPEN
------------------------------------------------------------
Segment Blocks:2560KB:20480
Used Blocks:405KB:3240
Expired Blocks:2134KB:17072
Unexpired Blocks:0KB:0
============================================================
============================================================
SpaceUsageforSecureFile LOB TRBTKT.TICKETS.SCRNIMG
Partition Name: STS_PENDING
------------------------------------------------------------
Segment Blocks:1024KB:8192
Used Blocks:62KB:496
Expired Blocks:944KB:7552
Unexpired Blocks:0KB:0
============================================================
============================================================
SpaceUsageforSecureFile LOB TRBTKT.TICKETS.SCRNIMG
Partition Name: STS_CLOSED
------------------------------------------------------------
Segment Blocks:1024KB:8192
Used Blocks:142KB:1136
Expired Blocks:864KB:6912
Unexpired Blocks:0KB:0
============================================================
============================================================
SpaceUsageforSecureFile LOB TRBTKT.TICKETS.SCRNIMG
Partition Name: STS_OTHER
------------------------------------------------------------
Segment Blocks:512KB:4096
Used Blocks:501KB:4008
Expired Blocks:0KB:0
Unexpired Blocks:0KB:0
============================================================
SETSERVEROUTPUTON
--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;
/
============================================================
SpaceUsageforBasicFile LOB TRBTKT.SECURE_TICKETS.DOCUMENT
Partition Name: STS_OPEN
------------------------------------------------------------
FullBlocks:123KB: .96
Unformatted Blocks:379KB:2.96
Total Blocks:123Total KB: .96
============================================================
============================================================
SpaceUsageforBasicFile LOB TRBTKT.SECURE_TICKETS.DOCUMENT
Partition Name: STS_PENDING
------------------------------------------------------------
FullBlocks:20KB: .16
Unformatted Blocks:482KB:3.77
Total Blocks:20Total KB: .16
============================================================
============================================================
SpaceUsageforBasicFile LOB TRBTKT.SECURE_TICKETS.DOCUMENT
Partition Name: STS_CLOSED
------------------------------------------------------------
FullBlocks:37KB: .29
Unformatted Blocks:465KB:3.63
Total Blocks:37Total KB: .29
============================================================
============================================================
SpaceUsageforBasicFile LOB TRBTKT.SECURE_TICKETS.DOCUMENT
Partition Name: STS_OTHER
------------------------------------------------------------
FullBlocks:0KB:0
Unformatted Blocks:0KB:0
Total Blocks:0Total KB:0
============================================================
============================================================
SpaceUsageforBasicFile LOB TRBTKT.SECURE_TICKETS.SCRNIMG
Partition Name: STS_OPEN
------------------------------------------------------------
FullBlocks:420KB:3.28
Unformatted Blocks:82KB: .64
Total Blocks:420Total KB:3.28
============================================================
============================================================
SpaceUsageforBasicFile LOB TRBTKT.SECURE_TICKETS.SCRNIMG
Partition Name: STS_PENDING
------------------------------------------------------------
FullBlocks:66KB: .52
Unformatted Blocks:436KB:3.41
Total Blocks:66Total KB: .52
============================================================
============================================================
SpaceUsageforBasicFile LOB TRBTKT.SECURE_TICKETS.SCRNIMG
Partition Name: STS_CLOSED
------------------------------------------------------------
FullBlocks:144KB:1.13
Unformatted Blocks:358KB:2.8
Total Blocks:144Total KB:1.13
============================================================
============================================================
SpaceUsageforBasicFile LOB TRBTKT.SECURE_TICKETS.SCRNIMG
Partition Name: STS_OTHER
------------------------------------------------------------
FullBlocks:0KB:0
Unformatted Blocks:0KB:0
Total Blocks:0Total 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;
/
============================================================
SpaceUsageforSecureFile LOB TRBTKT.TICKETS.DOCUMENT
Partition Name: STS_OPEN
------------------------------------------------------------
Segment Blocks:1024KB:8192
Used Blocks:124KB:992
Expired Blocks:882KB:7056
Unexpired Blocks:0KB:0
============================================================
============================================================
SpaceUsageforSecureFile LOB TRBTKT.TICKETS.DOCUMENT
Partition Name: STS_PENDING
------------------------------------------------------------
Segment Blocks:1024KB:8192
Used Blocks:21KB:168
Expired Blocks:985KB:7880
Unexpired Blocks:0KB:0
============================================================
============================================================
SpaceUsageforSecureFile LOB TRBTKT.TICKETS.DOCUMENT
Partition Name: STS_CLOSED
------------------------------------------------------------
Segment Blocks:1024KB:8192
Used Blocks:13KB:104
Expired Blocks:993KB:7944
Unexpired Blocks:0KB:0
============================================================
============================================================
SpaceUsageforSecureFile LOB TRBTKT.TICKETS.DOCUMENT
Partition Name: STS_OTHER
------------------------------------------------------------
Segment Blocks:512KB:4096
Used Blocks:501KB:4008
Expired Blocks:0KB:0
Unexpired Blocks:0KB:0
============================================================
============================================================
SpaceUsageforSecureFile LOB TRBTKT.TICKETS.SCRNIMG
Partition Name: STS_OPEN
------------------------------------------------------------
Segment Blocks:2560KB:20480
Used Blocks:405KB:3240
Expired Blocks:2134KB:17072
Unexpired Blocks:0KB:0
============================================================
============================================================
SpaceUsageforSecureFile LOB TRBTKT.TICKETS.SCRNIMG
Partition Name: STS_PENDING
------------------------------------------------------------
Segment Blocks:1024KB:8192
Used Blocks:62KB:496
Expired Blocks:944KB:7552
Unexpired Blocks:0KB:0
============================================================
============================================================
SpaceUsageforSecureFile LOB TRBTKT.TICKETS.SCRNIMG
Partition Name: STS_CLOSED
------------------------------------------------------------
Segment Blocks:1024KB:8192
Used Blocks:142KB:1136
Expired Blocks:864KB:6912
Unexpired Blocks:0KB:0
============================================================
============================================================
SpaceUsageforSecureFile LOB TRBTKT.TICKETS.SCRNIMG
Partition Name: STS_OTHER
------------------------------------------------------------
Segment Blocks:512KB:4096
Used Blocks:501KB:4008
Expired Blocks:0KB:0
Unexpired Blocks:0KB:0
============================================================
SETSERVEROUTPUTON
修改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>ALTERTABLETRBTKT.TICKETS
SQL> MODIFY LOB(DOCUMENT) (NOCOMPRESS);
Tablealtered.
SQL>ALTERTABLETRBTKT.TICKETS
SQL> MODIFY LOB(SCRNIMG) (KEEP_DUPLICATES);
Tablealtered.
SQL>ALTERTABLETRBTKT.TICKETS
SQL> MODIFY PARTITION STS_PENDING LOB(SCRNIMG) (COMPRESS HIGH);
Tablealtered.
SQL>ALTERTABLETRBTKT.TICKETS
SQL> MODIFY PARTITION STS_PENDING LOB(DOCUMENT) (DEDUPLICATE);
Tablealtered.
SQL> MODIFY LOB(DOCUMENT) (NOCOMPRESS);
Tablealtered.
SQL>ALTERTABLETRBTKT.TICKETS
SQL> MODIFY LOB(SCRNIMG) (KEEP_DUPLICATES);
Tablealtered.
SQL>ALTERTABLETRBTKT.TICKETS
SQL> MODIFY PARTITION STS_PENDING LOB(SCRNIMG) (COMPRESS HIGH);
Tablealtered.
SQL>ALTERTABLETRBTKT.TICKETS
SQL> MODIFY PARTITION STS_PENDING LOB(DOCUMENT) (DEDUPLICATE);
Tablealtered.
报告2 修改许多SecureFile LOB后查询DBA_LOB_PARTITIONS返回的结果
BasicFileandSecureFile LOB Partitions
(fromDBA_LOB_PARTITIONS)
Storedin Stored DeDupli-
TableColumn PartitionCacheingInRow EncryptedCompressed catedSecureFile
---------------- ------------ ------------ ---------- ---------- ---------- ---------- --------
SECURE_TICKETS DOCUMENT STS_OTHERNO YESNONE NONE NONE NO
SECURE_TICKETS DOCUMENT STS_CLOSED NO YESNONE NONE NONE NO
SECURE_TICKETS DOCUMENT STS_OPEN NO YESNONE NONE NONE NO
SECURE_TICKETS DOCUMENT STS_PENDINGNO YESNONE NONE NONE NO
SECURE_TICKETS SCRNIMGSTS_OTHERNO YESNONE NONE NONE NO
SECURE_TICKETS SCRNIMGSTS_CLOSED NO YESNONE NONE NONE NO
SECURE_TICKETS SCRNIMGSTS_PENDINGNO YESNONE NONE NONE NO
SECURE_TICKETS SCRNIMGSTS_OPEN NO YESNONE NONE NONE NO
TICKETSDOCUMENT STS_OTHERYESNO NO NO LOBYES
TICKETSDOCUMENT STS_PENDINGYESNO NO NO LOBYES
TICKETSDOCUMENT STS_CLOSED YESNO NO NO LOBYES
TICKETSDOCUMENT STS_OPEN YESNO NO NO NO YES
TICKETSSCRNIMGSTS_PENDINGCACHEREADS NO NO HIGH NO YES
TICKETSSCRNIMGSTS_OPEN CACHEREADS NO NO MEDIUM NO YES
TICKETSSCRNIMGSTS_CLOSED CACHEREADS NO NO HIGH NO YES
TICKETSSCRNIMGSTS_OTHERCACHEREADS NO NO HIGH NO YES
(fromDBA_LOB_PARTITIONS)
Storedin Stored DeDupli-
TableColumn PartitionCacheingInRow EncryptedCompressed catedSecureFile
---------------- ------------ ------------ ---------- ---------- ---------- ---------- --------
SECURE_TICKETS DOCUMENT STS_OTHERNO YESNONE NONE NONE NO
SECURE_TICKETS DOCUMENT STS_CLOSED NO YESNONE NONE NONE NO
SECURE_TICKETS DOCUMENT STS_OPEN NO YESNONE NONE NONE NO
SECURE_TICKETS DOCUMENT STS_PENDINGNO YESNONE NONE NONE NO
SECURE_TICKETS SCRNIMGSTS_OTHERNO YESNONE NONE NONE NO
SECURE_TICKETS SCRNIMGSTS_CLOSED NO YESNONE NONE NONE NO
SECURE_TICKETS SCRNIMGSTS_PENDINGNO YESNONE NONE NONE NO
SECURE_TICKETS SCRNIMGSTS_OPEN NO YESNONE NONE NONE NO
TICKETSDOCUMENT STS_OTHERYESNO NO NO LOBYES
TICKETSDOCUMENT STS_PENDINGYESNO NO NO LOBYES
TICKETSDOCUMENT STS_CLOSED YESNO NO NO LOBYES
TICKETSDOCUMENT STS_OPEN YESNO NO NO NO YES
TICKETSSCRNIMGSTS_PENDINGCACHEREADS NO NO HIGH NO YES
TICKETSSCRNIMGSTS_OPEN CACHEREADS NO NO MEDIUM NO YES
TICKETSSCRNIMGSTS_CLOSED CACHEREADS NO NO HIGH NO YES
TICKETSSCRNIMGSTS_OTHERCACHEREADS NO NO HIGH NO YES
最后，请记住任何对LOB属性进行修改都只会影响到新创建的LOB或新修改的LOB，例如，将列TRBTKT.SECURE_TICKETS.SCRNIMG的压缩方法从COMPRESS修改为NOCOMPRESS不会影响到现有的LOB条目。
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
