我創建了一個新表TRBTKT.SECURE_TICKETS重新定義了原始表TRBTKT.TICKETS的結構，將原始的BasicFile LOB映射到對應的SecureFile LOB，然后執行了一條INSERT INTO … SELECT FROM DML語句將原始表中的內容傳輸到新表中。
在一個相對簡單的應用程序環境中，這種載入方式足以應付，但目前在數據倉庫和商業智能應用程序中廣泛地使用了LOB，特別是存儲那些包含了復雜商務交易的xml文檔和醫院信息系統捕獲到的醫療影像數據。
為了便于說明，我使用清單1中的代碼重新創建了這兩個表，我使用了列表分區對這兩個表進行了分區，在清單2中，我使用更大的數據重新載入TRBTKT.TICKETS表。
清單1 使用列表分區重新創建這兩個表以模擬數據倉庫環境
-- 重新創建表TRBTKT.TICKETS，這次包括了一個STATUS列
CREATE table trbtkt.tickets (tkt_idNUMBER,description VARCHAR2(30),submit_dtm TIMESTAMP,statusVARCHAR2(8),document BLOB,scrnimg BLOB)LOB(document) STORE AS BASICFILE (TABLESPACE basicfiles),LOB(scrnimg) STORE AS BASICFILE (TABLESPACE basicfiles)PARTITION BY LIST (status) (PARTITION sts_open VALUES ('OPEN'),PARTITION sts_pendingVALUES ('PENDING'),PARTITION sts_closedVALUES ('CLOSED'),PARTITION sts_otherVALUES (DEFAULT));
--注釋COMMENT ON table trbtkt.ticketsIS 'Contains Trouble Ticket transaction data';COMMENT ON COLUMN trbtkt.tickets.tkt_idIS 'Unique identifier for a Trouble Ticket';COMMENT ON COLUMN trbtkt.tickets.descriptionIS 'Trouble Ticket Description';COMMENT ON COLUMN trbtkt.tickets.submit_dtmIS 'Trouble Ticket Submission Time Stamp';COMMENT ON COLUMN trbtkt.tickets.statusIS 'Trouble Ticket Status';COMMENT ON COLUMN trbtkt.tickets.documentIS 'Large Object (LOB) that contains a microsoft Word document that PRovides detailed information about a Trouble Ticket';COMMENT ON COLUMN trbtkt.tickets.scrnimgIS 'Large Object (LOB) that contains a screen print or other image that helps a problem solver identify how to act on a Trouble Ticket';
-- 創建索引和約束CREATE UNIQUE INDEX trbtkt.tickets_pk_idxON trbtkt.tickets(tkt_id)TABLESPACE users;
ALTER table trbtkt.ticketsADD CONSTRAINT tickets_pkPRIMARY KEY (tkt_id);
--- 創建表TRBTKT.SECURE_TICKETS的一個改良版本，使用分區劃分SecureFile LOB的存儲，基于STATUS 的值設置合適的保留周期
DROP table trbtkt.secure_tickets PURGE;CREATE table trbtkt.secure_tickets (tkt_idNUMBER,description VARCHAR2(30),submit_dtm TIMESTAMP,statusVARCHAR2(8),document BLOB,scrnimg BLOB)LOB(document) STORE AS SECUREFILE (TABLESPACE securefilesDISABLE STORAGE IN ROWCACHE),LOB(scrnimg) STORE AS SECUREFILE (TABLESPACE securefilesDISABLE STORAGE IN ROWCACHE READS)PARTITION BY LIST (status) (PARTITION sts_open VALUES ('OPEN')LOB (document) STORE AS SECUREFILE (KEEP_DUPLICATES NOCOMPRESS),LOB (scrnimg) STORE AS SECUREFILE (KEEP_DUPLICATES COMPRESS),PARTITION sts_pendingVALUES ('PENDING')LOB (document) STORE AS SECUREFILE (KEEP_DUPLICATES NOCOMPRESS),LOB (scrnimg) STORE AS SECUREFILE (DEDUPLICATE COMPRESS),PARTITION sts_closedVALUES ('CLOSED')LOB (document) STORE AS SECUREFILE (DEDUPLICATE COMPRESS),LOB (scrnimg) STORE AS SECUREFILE (DEDUPLICATE COMPRESS HIGH),PARTITION sts_otherVALUES (DEFAULT)LOB (document) STORE AS SECUREFILE (DEDUPLICATE COMPRESS HIGH),LOB (scrnimg) STORE AS SECUREFILE (DEDUPLICATE COMPRESS HIGH));
--注釋COMMENT ON table trbtkt.secure_ticketsIS 'Contains Trouble Ticket transaction data';COMMENT ON COLUMN trbtkt.secure_tickets.tkt_idIS 'Unique identifier for a Trouble Ticket';COMMENT ON COLUMN trbtkt.secure_tickets.descriptionIS 'Trouble Ticket Description';COMMENT ON COLUMN trbtkt.secure_tickets.submit_dtmIS 'Trouble Ticket Submission Time Stamp';COMMENT ON COLUMN trbtkt.secure_tickets.statusIS 'Trouble Ticket Status';COMMENT ON COLUMN trbtkt.secure_tickets.documentIS 'Large Object (LOB) that contains a microsoft Word document that provides detailed information about a Trouble Ticket';COMMENT ON COLUMN trbtkt.secure_tickets.scrnimgIS 'Large Object (LOB) that contains a screen print or other image that helps a problem solver identify how to act on a Trouble Ticket';
清單2 使用附加數據重新載入表TRBTKT.TICKETS
SET SERVEROUTPUT ONTRUNCATE table trbtkt.tickets;
BEGIN
trbtkt.pkg_securefiles.AddTroubleTicket (tkt_id => 101,description => 'Trouble Ticket 101',submit_dts => '2008-12-31 23:45:00',status => 'OPEN',docFileName => 'New_101.doc',imgFileName => 'DBRIssues.jpg');
trbtkt.pkg_securefiles.AddTroubleTicket (tkt_id => 102,description => 'Trouble Ticket 102',submit_dts => '2009-01-04 00:00:00',status => 'OPEN',docFileName => 'New_102.doc',imgFileName => 'Unresolved.jpg');
trbtkt.pkg_securefiles.AddTroubleTicket (tkt_id => 103,description => 'Trouble Ticket 103',submit_dts => '2009-01-02 00:00:00',status => 'OPEN',docFileName => 'New_103.doc',imgFileName => 'Unresolved.jpg');
trbtkt.pkg_securefiles.AddTroubleTicket (tkt_id => 104,description => 'Trouble Ticket 104',submit_dts => '2009-01-14 12:30:00',status => 'OPEN',docFileName => 'New_104.doc',imgFileName => 'DBRIssues.jpg');
trbtkt.pkg_securefiles.AddTroubleTicket (tkt_id => 105,description => 'Trouble Ticket 105',submit_dts => '2009-01-09 00:00:00',status => 'OPEN',docFileName => 'New_105.doc',imgFileName => 'Unresolved.jpg');
trbtkt.pkg_securefiles.AddTroubleTicket (tkt_id => 106,description => 'Trouble Ticket 106',submit_dts => '2009-01-11 00:00:00',status => 'OPEN',docFileName => 'New_106.doc',imgFileName => 'Unresolved.jpg');trbtkt.pkg_securefiles.AddTroubleTicket (tkt_id => 107,description => 'Trouble Ticket 107',submit_dts => '2009-01-16 00:00:00',status => 'OPEN',docFileName => 'New_107.doc',imgFileName => 'DBRIssues.jpg');trbtkt.pkg_securefiles.AddTroubleTicket (tkt_id => 108,description => 'Trouble Ticket 108',submit_dts => '2009-01-12 00:00:00',status => 'OPEN',docFileName => 'New_108.doc',imgFileName => 'Unresolved.jpg');trbtkt.pkg_securefiles.AddTroubleTicket (tkt_id => 109,description => 'Trouble Ticket 109',submit_dts => '2009-01-02 00:00:00',status => 'OPEN',docFileName => 'New_109.doc',imgFileName => 'Unresolved.jpg');
trbtkt.pkg_securefiles.AddTroubleTicket (tkt_id => 110,description => 'Trouble Ticket 110',submit_dts => '2009-01-14 12:45:00',status => 'OPEN',docFileName => 'New_110.doc',imgFileName => 'DBRIssues.jpg');
trbtkt.pkg_securefiles.AddTroubleTicket (tkt_id => 201,description => 'Trouble Ticket 201',submit_dts => '2008-12-31 23:45:00',status => 'PENDING',docFileName => 'New_101.doc',imgFileName => 'DBRIssues.jpg');
trbtkt.pkg_securefiles.AddTroubleTicket (tkt_id => 202,description => 'Trouble Ticket 202',submit_dts => '2009-01-04 00:00:00',status => 'OPEN',docFileName => 'New_102.doc',imgFileName => 'Unresolved.jpg');
trbtkt.pkg_securefiles.AddTroubleTicket (tkt_id => 203,description => 'Trouble Ticket 203',submit_dts => '2009-01-02 00:00:00',status => 'CLOSED',docFileName => 'New_103.doc',imgFileName => 'Unresolved.jpg');
trbtkt.pkg_securefiles.AddTroubleTicket (tkt_id => 204,description => 'Trouble Ticket 204',submit_dts => '2009-01-14 12:30:00',status => 'OPEN',docFileName => 'New_104.doc',imgFileName => 'DBRIssues.jpg');
trbtkt.pkg_securefiles.AddTroubleTicket (tkt_id => 205,description => 'Trouble Ticket 205',submit_dts => '2009-01-09 00:00:00',status => 'OPEN',docFileName => 'New_105.doc',imgFileName => 'Unresolved.jpg');
trbtkt.pkg_securefiles.AddTroubleTicket (tkt_id => 206,description => 'Trouble Ticket 206',submit_dts => '2009-01-11 00:00:00',status => 'PENDING',docFileName => 'New_106.doc',imgFileName => 'Unresolved.jpg');trbtkt.pkg_securefiles.AddTroubleTicket (tkt_id => 207,description => 'Trouble Ticket 207',submit_dts => '2009-01-16 00:00:00',status => 'OPEN',docFileName => 'New_107.doc',imgFileName => 'DBRIssues.jpg');trbtkt.pkg_securefiles.AddTroubleTicket (tkt_id => 208,description => 'Trouble Ticket 208',submit_dts => '2009-01-12 00:00:00',status => 'OPEN',docFileName => 'New_108.doc',imgFileName => 'Unresolved.jpg');trbtkt.pkg_securefiles.AddTroubleTicket (tkt_id => 209,description => 'Trouble Ticket 209',submit_dts => '2009-01-02 00:00:00',status => 'PENDING',docFileName => 'New_109.doc',imgFileName => 'Unresolved.jpg');
trbtkt.pkg_securefiles.AddTroubleTicket (tkt_id => 210,description => 'Trouble Ticket 210',submit_dts => '2009-01-14 12:45:00',status => 'OPEN',docFileName => 'New_110.doc',imgFileName => 'DBRIssues.jpg');
trbtkt.pkg_securefiles.AddTroubleTicket (tkt_id => 301,description => 'Trouble Ticket 301',submit_dts => '2008-12-31 23:45:00',status => 'CLOSED',docFileName => 'New_101.doc',imgFileName => 'DBRIssues.jpg');
trbtkt.pkg_securefiles.AddTroubleTicket (tkt_id => 302,description => 'Trouble Ticket 302',submit_dts => '2009-01-04 00:00:00',status => 'OPEN',docFileName => 'New_102.doc',imgFileName => 'Unresolved.jpg');
trbtkt.pkg_securefiles.AddTroubleTicket (tkt_id => 303,description => 'Trouble Ticket 303',submit_dts => '2009-01-02 00:00:00',status => 'OPEN',docFileName => 'New_103.doc',imgFileName => 'Unresolved.jpg');
trbtkt.pkg_securefiles.AddTroubleTicket (tkt_id => 304,description => 'Trouble Ticket 304',submit_dts => '2009-01-14 12:30:00',status => 'CLOSED',docFileName => 'New_104.doc',imgFileName => 'DBRIssues.jpg');
trbtkt.pkg_securefiles.AddTroubleTicket (tkt_id => 305,description => 'Trouble Ticket 305',submit_dts => '2009-01-09 00:00:00',status => 'PENDING',docFileName => 'New_105.doc',imgFileName => 'Unresolved.jpg');
trbtkt.pkg_securefiles.AddTroubleTicket (tkt_id => 306,description => 'Trouble Ticket 306',submit_dts => '2009-01-11 00:00:00',status => 'CLOSED',docFileName => 'New_106.doc',imgFileName => 'Unresolved.jpg');trbtkt.pkg_securefiles.AddTroubleTicket (tkt_id => 307,description => 'Trouble Ticket 307',submit_dts => '2009-01-16 00:00:00',status => 'OPEN',docFileName => 'New_107.doc',imgFileName => 'DBRIssues.jpg');trbtkt.pkg_securefiles.AddTroubleTicket (tkt_id => 308,description => 'Trouble Ticket 308',submit_dts => '2009-01-12 00:00:00',status => 'OPEN',docFileName => 'New_108.doc',imgFileName => 'Unresolved.jpg');trbtkt.pkg_securefiles.AddTroubleTicket (tkt_id => 309,description => 'Trouble Ticket 309',submit_dts => '2009-01-02 00:00:00',status => 'CLOSED',docFileName => 'New_109.doc',imgFileName => 'Unresolved.jpg');
trbtkt.pkg_securefiles.AddTroubleTicket (tkt_id => 310,description => 'Trouble Ticket 310',submit_dts => '2009-01-14 12:45:00',status => 'CLOSED',docFileName => 'New_110.doc',imgFileName => 'DBRIssues.jpg');
COMMIT;END;/-- 收集優化器統計信息BEGINDBMS_STATS.GATHER_SCHEMA_STATS(ownname => 'TRBTKT', CASCADE => TRUE);END;/有效地從BasicFile移植到SecureFile
現在已經將這些表進行了分區，以便在數據倉庫環境更有效地使用，我也已經考慮過如何將LOB從BasicFile格式快速有效地轉換成對應的SecureFile格式，幸運的是，Oracle 11g提供了兩個方法：分區交換和在線重定義。
1、分區交換
分區交換載入(Partition exchange Load，即PDL)有時也叫做分區轉動，包含創建一個非分區表，它的格式和內容完全匹配目標分區表想要的分區，一旦原始表創建好了，就可以開始可已經分區的表的目標分區進行交換了，這個方法確實提供了許多好處，換句話說，原始表的本地索引在交換期間得到維護，它可以并行執行，這個方法可以重復多次以填充每個分區，它的主要缺點是接收數據的分區在進行交換時必須離線。
2、在線重定義
Oracle推薦使用DBMS_REDEFINITION包對源表和目標表執行在線重定義，因為它實現了分區交換方法相同的目標。但它不需要在重定義過程中將目標離線。在線重定義可以在表級也可以在分區級執行，它也可以同時并行為多個分區執行，我喜歡在線重定義的特性是在這個過程成功執行完畢后，源和目標對象將對真正地在數據庫數據目錄中轉換定義，這意味著任何引用了新對象名的PL/SQL對象都不需要進行任何修改。清單3顯示了如何使用TRBTKT.TICKETS表作為源對象，使用TRBTKT.SECURE_TICKETS表作為目標對象進行在線重定義的。從BasicFile LOB轉換到SecureFile LOB最后一個注意事項是：一旦轉換完成，LOB就不能再降級回到BasicFile LOB了。
管理SecureFile元數據
這些新的SecureFile特性無疑擴展了LOB的能力，對于DBA而言，也需要注意幾個在傳統的BasicFile LOB上增加的額外屬性，幸運的是，Oracle 11g提供了多個方法跟蹤BasicFile和SecureFile LOB的元數據。
1、數據字典視圖
oracle 11g也更新了多個關于SecureFile信息的數據字典視圖，下面列出這些做了變動的視圖。
清單4顯示了多條SQL*Plus查詢，它們使用了這些數據字典視圖返回關于SecureFile元數據的信息，報告1顯示了從這些查詢返回的結果。
清單4 查詢BasicFile和SecureFile LOB的元數據
SET PAGESIZE 1000SET LINESIZE 140 -- 視圖: DBA_SEGMENTS--顯示關于BasicFile 和SecureFile 段的元數據TTITLE 'LOB Segment Information|(from DBA_SEGMENTS)'COL segment_name FORMAT A30 HEADING 'Segment Name'COL segment_type FORMAT A20 HEADING 'Segment|Type'COL segment_subtype FORMAT A20 HEADING 'Segment|SubType'COL partition_name FORMAT A12 HEADING 'Partition|Name'COL tablespace_name FORMAT A12 HEADING 'Tablespace'SELECT segment_name,segment_type,segment_subtype,partition_name,tablespace_nameFROM dba_segmentsWHERE owner = 'TRBTKT'ORDER BY segment_name;TTITLE OFF-- 視圖: DBA_LOBS--顯示關于BasicFile和SecureFile LOB的元數據TTITLE 'BasicFile and SecureFile LOBs Metadata|(from DBA_LOBS)'COL table_name FORMAT A14 HEADING 'Table'COL segment_name FORMAT A26 HEADING 'Segment'COL column_name FORMAT A10 HEADING 'Column'COL tablespace_name FORMAT A12 HEADING 'Tablespace'COL loggingFORMAT A08 HEADING 'Logging'COL cache FORMAT A10 HEADING 'Cacheing'COL in_row FORMAT A07 HEADING 'Stored|In Row'COL encryptFORMAT A07 HEADING 'Encryp-|tion'COL compression FORMAT A07 HEADING 'Compre-|ssion'COL deduplication FORMAT A08 HEADING 'DeDupli-|cation'COL securefile FORMAT A07 HEADING 'Secure|File?'COL partitioned FORMAT A07 HEADING 'Parti-|tioned'SELECTtable_name,column_name,segment_name,tablespace_name,logging,cache,in_row,encrypt,compression,deduplication,securefile,partitionedFROM dba_lobsWHERE owner = 'TRBTKT'ORDER BY table_name, column_name;TTITLE OFF--視圖: DBA_PART_LOBS--顯示BasicFile和SecureFile LOB的默認值TTITLE 'BasicFile and SecureFile Partitioned LOB Default Settings|(from DBA_PART_LOBS)'COL table_name FORMAT A20 HEADING 'Table'COL column_nameFORMAT A12 HEADING 'Column'COL def_cache FORMAT A12 HEADING 'Cached'COL def_tablespace_name FORMAT A12 HEADING 'Tablespace'COL def_securefile FORMAT A12 HEADING 'SecureFile'COL def_encryptFORMAT A12 HEADING 'Encrypted'COL def_compress FORMAT A12 HEADING 'Compressed'COL def_deduplicate FORMAT A12 HEADING 'DeDuplicated'SELECTtable_name,column_name,def_cache,def_tablespace_name,def_securefile,def_compress,def_deduplicate,def_encryptFROM dba_part_lobsWHERE table_owner = 'TRBTKT'ORDER BY table_name, column_name;TTITLE OFF--視圖: DBA_LOB_PARTITIONS--在獨立的LOB級描述BasicFile和SecureFile設置TTITLE 'BasicFile and SecureFile LOB Partitions|(from DBA_LOB_PARTITIONS)'COL table_name FORMAT A16 HEADING 'Table'COL column_name FORMAT A12 HEADING 'Column'COL partition_name FORMAT A12 HEADING 'Stored in|Partition'COL cache FORMAT A10 HEADING 'Cacheing'COL in_row FORMAT A10 HEADING 'Stored|In Row'COL encryptFORMAT A10 HEADING 'Encrypted'COL compression FORMAT A10 HEADING 'Compressed'COL deduplication FORMAT A10 HEADING 'DeDupli-|cated'COL securefile FORMAT A10 HEADING 'SecureFile?'SELECTtable_name,column_name,partition_name,cache,in_row,encrypt,compression,deduplication,securefileFROM dba_lob_partitionsWHERE table_owner = 'TRBTKT'ORDER BY table_name, column_name;TTITLE OFF報告1 從BasicFIle和SecureFile LOB元數據報告返回的結果
SET PAGESIZE 1000SET LINESIZE 140 -- 視圖: DBA_SEGMENTS--顯示關于BasicFile 和SecureFile 段的元數據TTITLE 'LOB Segment Information|(from DBA_SEGMENTS)'COL segment_name FORMAT A30 HEADING 'Segment Name'COL segment_type FORMAT A20 HEADING 'Segment|Type'COL segment_subtype FORMAT A20 HEADING 'Segment|SubType'COL partition_name FORMAT A12 HEADING 'Partition|Name'COL tablespace_name FORMAT A12 HEADING 'Tablespace'SELECT segment_name,segment_type,segment_subtype,partition_name,tablespace_nameFROM dba_segmentsWHERE owner = 'TRBTKT'ORDER BY segment_name;TTITLE OFF-- 視圖: DBA_LOBS--顯示關于BasicFile和SecureFile LOB的元數據TTITLE 'BasicFile and SecureFile LOBs Metadata|(from DBA_LOBS)'COL table_name FORMAT A14 HEADING 'Table'COL segment_name FORMAT A26 HEADING 'Segment'COL column_name FORMAT A10 HEADING 'Column'COL tablespace_name FORMAT A12 HEADING 'Tablespace'COL loggingFORMAT A08 HEADING 'Logging'COL cache FORMAT A10 HEADING 'Cacheing'COL in_row FORMAT A07 HEADING 'Stored|In Row'COL encryptFORMAT A07 HEADING 'Encryp-|tion'COL compression FORMAT A07 HEADING 'Compre-|ssion'COL deduplication FORMAT A08 HEADING 'DeDupli-|cation'COL securefile FORMAT A07 HEADING 'Secure|File?'COL partitioned FORMAT A07 HEADING 'Parti-|tioned'SELECTtable_name,column_name,segment_name,tablespace_name,logging,cache,in_row,encrypt,compression,deduplication,securefile,partitionedFROM dba_lobsWHERE owner = 'TRBTKT'ORDER BY table_name, column_name;TTITLE OFF--視圖: DBA_PART_LOBS--顯示BasicFile和SecureFile LOB的默認值TTITLE 'BasicFile and SecureFile Partitioned LOB Default Settings|(from DBA_PART_LOBS)'COL table_name FORMAT A20 HEADING 'Table'COL column_nameFORMAT A12 HEADING 'Column'COL def_cache FORMAT A12 HEADING 'Cached'COL def_tablespace_name FORMAT A12 HEADING 'Tablespace'COL def_securefile FORMAT A12 HEADING 'SecureFile'COL def_encryptFORMAT A12 HEADING 'Encrypted'COL def_compress FORMAT A12 HEADING 'Compressed'COL def_deduplicate FORMAT A12 HEADING 'DeDuplicated'SELECTtable_name,column_name,def_cache,def_tablespace_name,def_securefile,def_compress,def_deduplicate,def_encryptFROM dba_part_lobsWHERE table_owner = 'TRBTKT'ORDER BY table_name, column_name;TTITLE OFF--視圖: DBA_LOB_PARTITIONS--在獨立的LOB級描述BasicFile和SecureFile設置TTITLE 'BasicFile and SecureFile LOB Partitions|(from DBA_LOB_PARTITIONS)'COL table_name FORMAT A16 HEADING 'Table'COL column_name FORMAT A12 HEADING 'Column'COL partition_name FORMAT A12 HEADING 'Stored in|Partition'COL cache FORMAT A10 HEADING 'Cacheing'COL in_row FORMAT A10 HEADING 'Stored|In Row'COL encryptFORMAT A10 HEADING 'Encrypted'COL compression FORMAT A10 HEADING 'Compressed'COL deduplication FORMAT A10 HEADING 'DeDupli-|cated'COL securefile FORMAT A10 HEADING 'SecureFile?'SELECTtable_name,column_name,partition_name,cache,in_row,encrypt,compression,deduplication,securefileFROM dba_lob_partitionsWHERE table_owner = 'TRBTKT'ORDER BY table_name, column_name;TTITLE OFF報告1 從BasicFIle和SecureFile LOB元數據報告返回的結果LOB段信息(來自DBA_SEGMENTS)
Segment Segment Partition Segment Name Type SubType NameTablespace ------------------------- -------------------- -------------------- ------------ ------------ SECURE_TICKETS table PARTITION ASSM STS_PENDING USERS SECURE_TICKETS table PARTITION ASSM STS_CLOSED USERS SECURE_TICKETS table PARTITION ASSM STS_OPEN USERS SECURE_TICKETS table PARTITION ASSM STS_OTHER USERS SYS_IL0000072118C00005$$ INDEX PARTITION ASSM SYS_IL_P180 BASICFILES SYS_IL0000072118C00005$$ INDEX PARTITION ASSM SYS_IL_P179 BASICFILES SYS_IL0000072118C00005$$ INDEX PARTITION ASSM SYS_IL_P178 BASICFILES SYS_IL0000072118C00005$$ INDEX PARTITION ASSM SYS_IL_P177 BASICFILES SYS_IL0000072118C00006$$ INDEX PARTITION ASSM SYS_IL_P185 BASICFILES SYS_IL0000072118C00006$$ INDEX PARTITION ASSM SYS_IL_P188 BASICFILES SYS_IL0000072118C00006$$ INDEX PARTITION ASSM SYS_IL_P187 BASICFILES SYS_IL0000072118C00006$$ INDEX PARTITION ASSM SYS_IL_P186 BASICFILES SYS_IL0000072144C00005$$ INDEX PARTITION ASSM SYS_IL_P194 SECUREFILES SYS_IL0000072144C00005$$ INDEX PARTITION ASSM SYS_IL_P193 SECUREFILES SYS_IL0000072144C00005$$ INDEX PARTITION ASSM SYS_IL_P195 SECUREFILES SYS_IL0000072144C00005$$ INDEX PARTITION ASSM SYS_IL_P196 SECUREFILES SYS_IL0000072144C00006$$ INDEX PARTITION ASSM SYS_IL_P204 SECUREFILES SYS_IL0000072144C00006$$ INDEX PARTITION ASSM SYS_IL_P203 SECUREFILES SYS_IL0000072144C00006$$ INDEX PARTITION ASSM SYS_IL_P202 SECUREFILES SYS_IL0000072144C00006$$ INDEX PARTITION ASSM SYS_IL_P201 SECUREFILES SYS_LOB0000072118C00005$$ LOB PARTITION ASSM SYS_LOB_P173 BASICFILES SYS_LOB0000072118C00005$$ LOB PARTITION ASSM SYS_LOB_P176 BASICFILES SYS_LOB0000072118C00005$$ LOB PARTITION ASSM SYS_LOB_P175 BASICFILES SYS_LOB0000072118C00005$$ LOB PARTITION ASSM SYS_LOB_P174 BASICFILES SYS_LOB0000072118C00006$$ LOB PARTITION ASSM SYS_LOB_P184 BASICFILES SYS_LOB0000072118C00006$$ LOB PARTITION ASSM SYS_LOB_P183 BASICFILES SYS_LOB0000072118C00006$$ LOB PARTITION ASSM SYS_LOB_P181 BASICFILES SYS_LOB0000072118C00006$$ LOB PARTITION ASSM SYS_LOB_P182 BASICFILES SYS_LOB0000072144C00005$$ LOB PARTITION SECUREFILE SYS_LOB_P191 SECUREFILES SYS_LOB0000072144C00005$$ LOB PARTITION SECUREFILE SYS_LOB_P192 SECUREFILES SYS_LOB0000072144C00005$$ LOB PARTITION SECUREFILE SYS_LOB_P189 SECUREFILES SYS_LOB0000072144C00005$$ LOB PARTITION SECUREFILE SYS_LOB_P190 SECUREFILES SYS_LOB0000072144C00006$$ LOB PARTITION SECUREFILE SYS_LOB_P198 SECUREFILES SYS_LOB0000072144C00006$$ LOB PARTITION SECUREFILE SYS_LOB_P199 SECUREFILES SYS_LOB0000072144C00006$$ LOB PARTITION SECUREFILE SYS_LOB_P197 SECUREFILES SYS_LOB0000072144C00006$$ LOB PARTITION SECUREFILE SYS_LOB_P200 SECUREFILES TICKETS table PARTITION ASSM STS_PENDING USERS TICKETS table PARTITION ASSM STS_OTHER USERS TICKETS table PARTITION ASSM STS_OPEN USERS TICKETS table PARTITION ASSM STS_CLOSED USERS TICKETS_PK_IDX INDEX ASSM USERS
BasicFile和SecureFile LOB元數據(來自DBA_LOBS)
Stored Encryp- Compre- DeDupli- Secure Parti- table Column Segment Tablespace Logging Cacheing In Row tion ssion cation File? tioned -------------- ---------- -------------------------- ------------ -------- ---------- ------- ------- ------- -------- ------- ------- SECURE_TICKETS DOCUMENT SYS_LOB0000072118C00005$$ BASICFILES NONE NOYES NONE NONE NONE NO YES SECURE_TICKETS SCRNIMG SYS_LOB0000072118C00006$$ BASICFILES NONE NOYES NONE NONE NONE NO YES TICKETS DOCUMENT SYS_LOB0000072144C00005$$ SECUREFILES NONE YES NO NO NO NO YES YES TICKETS SCRNIMG SYS_LOB0000072144C00006$$ SECUREFILES NONE CACHEREADS NO NO NO NO YES YES
BasicFile和SecureFile分區LOB默認設置(來自DBA_PART_LOBS)
table Column Cached Tablespace SecureFile Compressed DeDuplicated Encrypted ----- ------------ ------------ ------------ ------------ ------------ ------------ ------------ SECURE_TICKETS DOCUMENT NO BASICFILES NO NONENONENONE SECURE_TICKETS SCRNIMG NO BASICFILES NO NONENONENONE TICKETS DOCUMENT YES SECUREFILES YES NO NO NO TICKETS SCRNIMG CACHEREADS SECUREFILES YES NO NO NO
BasicFile和SecureFile LOB分區(來自DBA_LOB_PARTITIONS)
Stored in StoredDeDupli-table Column Partition Cacheing In Row Encrypted Compressed cated SecureFile ---- ------------ ------------ ---------- ---------- ---------- ---------- ---------- ---------- SECURE_TICKETS DOCUMENT STS_OTHER NOYES NONE NONE NONE NO SECURE_TICKETS DOCUMENT STS_CLOSED NOYES NONE NONE NONE NO SECURE_TICKETS DOCUMENT STS_OPEN NOYES NONE NONE NONE NO SECURE_TICKETS DOCUMENT STS_PENDING NOYES NONE NONE NONE NO SECURE_TICKETS SCRNIMG STS_OTHER NOYES NONE NONE NONE NO SECURE_TICKETS SCRNIMG STS_CLOSED NOYES NONE NONE NONE NO SECURE_TICKETS SCRNIMG STS_PENDING NOYES NONE NONE NONE NO SECURE_TICKETS SCRNIMG STS_OPEN NOYES NONE NONE NONE NO TICKETS DOCUMENT STS_OTHER YES NONOHIGH LOB YES TICKETS DOCUMENT STS_PENDING YES NONONONOYES TICKETS DOCUMENT STS_CLOSED YES NONOMEDIUM LOB YES TICKETS DOCUMENT STS_OPEN YES NONONONOYES TICKETS SCRNIMG STS_PENDING CACHEREADS NONOMEDIUM LOB YES TICKETS SCRNIMG STS_OPEN CACHEREADS NONOMEDIUM NOYES TICKETS SCRNIMG STS_CLOSED CACHEREADS NONOHIGH LOB YES TICKETS SCRNIMG STS_OTHER CACHEREADS NONOHIGH LOB YES2、DBMS_SPACE
這是另一個Oracle古老支持包，它的SPACE_USAGE存儲過程提供關于BasicFile和SecureFile LOB的空間利用率，我在TRBTKT.PKG_SECUREFILES包中引入了兩個存儲過程：CALC_SPACE_BASICFILES和CALC_SPACE_SECUREFILES。但遺憾的是，這個功能只能在啟用了自動段空間管理(ASSM)的表空間上使用，而且，它也不會考慮任何BasicFile LOB塊的空間利用率，在清單5中，我對表TRBTKT.TICKETS和TRBTKT.SECURE_TICKETS分別調用了這些存儲過程，并返回了相應的輸出。
清單5 確定BasicFile和SecureFile LOB的空間利用率
SET SERVEROUTPUT ON-- BasicFile存儲利用率:BEGINtrbtkt.pkg_securefiles.calc_space_basicfiles (ownname => 'TRBTKT',tabname => 'SECURE_TICKETS',colname => 'DOCUMENT',partname => 'STS_OPEN');trbtkt.pkg_securefiles.calc_space_basicfiles (ownname => 'TRBTKT',tabname => 'SECURE_TICKETS',colname => 'DOCUMENT',partname => 'STS_PENDING');trbtkt.pkg_securefiles.calc_space_basicfiles (ownname => 'TRBTKT',tabname => 'SECURE_TICKETS',colname => 'DOCUMENT',partname => 'STS_CLOSED');trbtkt.pkg_securefiles.calc_space_basicfiles (ownname => 'TRBTKT',tabname => 'SECURE_TICKETS',colname => 'DOCUMENT',partname => 'STS_OTHER');trbtkt.pkg_securefiles.calc_space_basicfiles (ownname => 'TRBTKT',tabname => 'SECURE_TICKETS',colname => 'SCRNIMG',partname => 'STS_OPEN');trbtkt.pkg_securefiles.calc_space_basicfiles (ownname => 'TRBTKT',tabname => 'SECURE_TICKETS',colname => 'SCRNIMG',partname => 'STS_PENDING');trbtkt.pkg_securefiles.calc_space_basicfiles (ownname => 'TRBTKT',tabname => 'SECURE_TICKETS',colname => 'SCRNIMG',partname => 'STS_CLOSED');trbtkt.pkg_securefiles.calc_space_basicfiles (ownname => 'TRBTKT',tabname => 'SECURE_TICKETS',colname => 'SCRNIMG',partname => 'STS_OTHER');END;/============================================================Space Usage for BasicFile LOB TRBTKT.SECURE_TICKETS.DOCUMENTPartition Name: STS_OPEN------------------------------------------------------------Full Blocks: 123 KB: .96Unformatted Blocks: 379 KB: 2.96 Total Blocks: 123 Total KB: .96 ========================================================================================================================Space Usage for BasicFile LOB TRBTKT.SECURE_TICKETS.DOCUMENTPartition Name: STS_PENDING ------------------------------------------------------------Full Blocks: 20 KB: .16 Unformatted Blocks: 482 KB: 3.77 Total Blocks: 20 Total KB: .16 ========================================================================================================================Space Usage for BasicFile LOB TRBTKT.SECURE_TICKETS.DOCUMENTPartition Name: STS_CLOSED ------------------------------------------------------------Full Blocks: 37 KB: .29 Unformatted Blocks: 465 KB: 3.63 Total Blocks: 37 Total KB: .29 ========================================================================================================================Space Usage for BasicFile LOB TRBTKT.SECURE_TICKETS.DOCUMENTPartition Name: STS_OTHER ------------------------------------------------------------Full Blocks: 0 KB: 0 Unformatted Blocks: 0 KB: 0 Total Blocks: 0 Total KB: 0 ========================================================================================================================Space Usage for BasicFile LOB TRBTKT.SECURE_TICKETS.SCRNIMG Partition Name: STS_OPEN------------------------------------------------------------Full Blocks: 420 KB: 3.28 Unformatted Blocks: 82 KB: .64 Total Blocks: 420 Total KB: 3.28 ========================================================================================================================Space Usage for BasicFile LOB TRBTKT.SECURE_TICKETS.SCRNIMG Partition Name: STS_PENDING ------------------------------------------------------------Full Blocks: 66 KB: .52 Unformatted Blocks: 436 KB: 3.41 Total Blocks: 66 Total KB: .52 ========================================================================================================================Space Usage for BasicFile LOB TRBTKT.SECURE_TICKETS.SCRNIMG Partition Name: STS_CLOSED ------------------------------------------------------------Full Blocks: 144 KB: 1.13 Unformatted Blocks: 358 KB: 2.8 Total Blocks: 144 Total KB: 1.13 ========================================================================================================================Space Usage for BasicFile LOB TRBTKT.SECURE_TICKETS.SCRNIMG Partition Name: STS_OTHER ------------------------------------------------------------Full Blocks: 0 KB: 0 Unformatted Blocks: 0 KB: 0 Total Blocks: 0 Total KB: 0 ============================================================-- SecureFile存儲利用率:BEGINtrbtkt.pkg_securefiles.calc_space_securefiles (ownname => 'TRBTKT',tabname => 'TICKETS',colname => 'DOCUMENT',partname => 'STS_OPEN');trbtkt.pkg_securefiles.calc_space_securefiles (ownname => 'TRBTKT',tabname => 'TICKETS',colname => 'DOCUMENT',partname => 'STS_PENDING');trbtkt.pkg_securefiles.calc_space_securefiles (ownname => 'TRBTKT',tabname => 'TICKETS',colname => 'DOCUMENT',partname => 'STS_CLOSED');trbtkt.pkg_securefiles.calc_space_securefiles (ownname => 'TRBTKT',tabname => 'TICKETS',colname => 'DOCUMENT',partname => 'STS_OTHER');trbtkt.pkg_securefiles.calc_space_securefiles (ownname => 'TRBTKT',tabname => 'TICKETS',colname => 'SCRNIMG',partname => 'STS_OPEN');trbtkt.pkg_securefiles.calc_space_securefiles (ownname => 'TRBTKT',tabname => 'TICKETS',colname => 'SCRNIMG',partname => 'STS_PENDING');trbtkt.pkg_securefiles.calc_space_securefiles (ownname => 'TRBTKT',tabname => 'TICKETS',colname => 'SCRNIMG',partname => 'STS_CLOSED');trbtkt.pkg_securefiles.calc_space_securefiles (ownname => 'TRBTKT',tabname => 'TICKETS',colname => 'SCRNIMG',partname => 'STS_OTHER');END;/
============================================================Space Usage for SecureFile LOB TRBTKT.TICKETS.DOCUMENT Partition Name: STS_OPEN------------------------------------------------------------Segment Blocks: 1024 KB: 8192 Used Blocks: 124 KB: 992Expired Blocks: 882 KB: 7056 Unexpired Blocks: 0 KB: 0 ========================================================================================================================Space Usage for SecureFile LOB TRBTKT.TICKETS.DOCUMENT Partition Name: STS_PENDING ------------------------------------------------------------Segment Blocks: 1024 KB: 8192 Used Blocks: 21 KB: 168 Expired Blocks: 985 KB: 7880 Unexpired Blocks: 0 KB: 0 ========================================================================================================================Space Usage for SecureFile LOB TRBTKT.TICKETS.DOCUMENT Partition Name: STS_CLOSED ------------------------------------------------------------Segment Blocks: 1024 KB: 8192 Used Blocks: 13 KB: 104 Expired Blocks: 993 KB: 7944 Unexpired Blocks: 0 KB: 0 ========================================================================================================================Space Usage for SecureFile LOB TRBTKT.TICKETS.DOCUMENT Partition Name: STS_OTHER ------------------------------------------------------------Segment Blocks: 512 KB: 4096 Used Blocks: 501 KB: 4008 Expired Blocks: 0 KB: 0 Unexpired Blocks: 0 KB: 0 ========================================================================================================================Space Usage for SecureFile LOB TRBTKT.TICKETS.SCRNIMG Partition Name: STS_OPEN------------------------------------------------------------Segment Blocks: 2560 KB: 20480 Used Blocks: 405 KB: 3240 Expired Blocks: 2134 KB: 17072 Unexpired Blocks: 0 KB: 0 ========================================================================================================================Space Usage for SecureFile LOB TRBTKT.TICKETS.SCRNIMG Partition Name: STS_PENDING ------------------------------------------------------------Segment Blocks: 1024 KB: 8192 Used Blocks: 62 KB: 496 Expired Blocks: 944 KB: 7552 Unexpired Blocks: 0 KB: 0 ========================================================================================================================Space Usage for SecureFile LOB TRBTKT.TICKETS.SCRNIMG Partition Name: STS_CLOSED ------------------------------------------------------------Segment Blocks: 1024 KB: 8192 Used Blocks: 142 KB: 1136 Expired Blocks: 864 KB: 6912 Unexpired Blocks: 0 KB: 0 ========================================================================================================================Space Usage for SecureFile LOB TRBTKT.TICKETS.SCRNIMG Partition Name: STS_OTHER ------------------------------------------------------------Segment Blocks: 512 KB: 4096 Used Blocks: 501 KB: 4008 Expired Blocks: 0 KB: 0 Unexpired Blocks: 0 KB: 0 ============================================================
SET SERVEROUTPUT ON修改SecureFile屬性
當我創建TRBTKT.SECURE_TICKETS表時，你可能注意到我為每個LOB列在它們對應的分區中執行壓縮和重復刪除，重要的是我還使用了ALTER TABLE語句進行修正，另外，如果需要的話，我還可以在每個LOB獨立的分區上應用改變到SecureFile LOB。清單6中的代碼顯示了4個修改TRBTKT.TICKETS表的SecureFile LOB屬性的例子，注意這個表現在已經用清單3中的代碼和TRBTKT.SECURE_TICKETS表執行了交換，我也從DBA_PART_LOBS創建了一個報告顯示在修改這些LOB屬性前后的狀態，結果輸出如報告2所示。
清單6 管理SecureFile LOB屬性
sql> ALTER table TRBTKT.TICKETSsql> MODIFY LOB(DOCUMENT) (NOCOMPRESS);
table altered.
sql> ALTER table TRBTKT.TICKETSsql> MODIFY LOB(SCRNIMG) (KEEP_DUPLICATES);
table altered.
sql> ALTER table TRBTKT.TICKETSsql> MODIFY PARTITION STS_PENDING LOB(SCRNIMG) (COMPRESS HIGH);table altered.sql> ALTER table TRBTKT.TICKETSsql> MODIFY PARTITION STS_PENDING LOB(DOCUMENT) (DEDUPLICATE);table altered.報告2 修改許多SecureFile LOB后查詢DBA_LOB_PARTITIONS返回的結果
BasicFile and SecureFile LOB Partitions(from DBA_LOB_PARTITIONS)Stored in StoredDeDupli-table Column Partition Cacheing In Row Encrypted Compressed cated SecureFile---------------- ------------ ------------ ---------- ---------- ---------- ---------- --------SECURE_TICKETS DOCUMENT STS_OTHER NOYES NONE NONE NONE NOSECURE_TICKETS DOCUMENT STS_CLOSED NOYES NONE NONE NONE NOSECURE_TICKETS DOCUMENT STS_OPEN NOYES NONE NONE NONE NOSECURE_TICKETS DOCUMENT STS_PENDING NOYES NONE NONE NONE NOSECURE_TICKETS SCRNIMG STS_OTHER NOYES NONE NONE NONE NOSECURE_TICKETS SCRNIMG STS_CLOSED NOYES NONE NONE NONE NOSECURE_TICKETS SCRNIMG STS_PENDING NOYES NONE NONE NONE NOSECURE_TICKETS SCRNIMG STS_OPEN NOYES NONE NONE NONE NOTICKETS DOCUMENT STS_OTHER YES NONONOLOB YESTICKETS DOCUMENT STS_PENDING YES NONONOLOB YESTICKETS DOCUMENT STS_CLOSED YES NONONOLOB YESTICKETS DOCUMENT STS_OPEN YES NONONONOYESTICKETS SCRNIMG STS_PENDING CACHEREADS NONOHIGH NOYESTICKETS SCRNIMG STS_OPEN CACHEREADS NONOMEDIUM NOYESTICKETS SCRNIMG STS_CLOSED CACHEREADS NONOHIGH NOYESTICKETS SCRNIMG STS_OTHER CACHEREADS NONOHIGH NOYES
最后，請記住任何對LOB屬性進行修改都只會影響到新創建的LOB或新修改的LOB，例如，將列TRBTKT.SECURE_TICKETS.SCRNIMG的壓縮方法從COMPRESS修改為NOCOMPRESS不會影響到現有的LOB條目。
新聞熱點
疑難解答
