自拍偷在线精品自拍偷,亚洲欧美中文日韩v在线观看不卡

Oracle中如何從BasicFile遷移到SecureFile

作者：開心果
運(yùn)維 數(shù)據(jù)庫(kù)運(yùn)維 Oracle
Oracle 11g新的SecureFile存儲(chǔ)特性擴(kuò)展了大對(duì)象(LOB)的靈活性和容量，本文研究如何有效地從BasicFile遷移到SecureFile存儲(chǔ)，如何測(cè)量SecureFile vs BasicFile LOB的效率，以及如何為SecureFile LOB使用不同的壓縮和重復(fù)數(shù)據(jù)刪除選項(xiàng)。

我創(chuàng)建了一個(gè)新表TRBTKT.SECURE_TICKETS重新定義了原始表TRBTKT.TICKETS的結(jié)構(gòu)，將原始的BasicFile LOB映射到對(duì)應(yīng)的SecureFile LOB，然后執(zhí)行了一條INSERT INTO … SELECT FROM DML語(yǔ)句將原始表中的內(nèi)容傳輸?shù)叫卤碇小?

在一個(gè)相對(duì)簡(jiǎn)單的應(yīng)用程序環(huán)境中，這種載入方式足以應(yīng)付，但目前在數(shù)據(jù)倉(cāng)庫(kù)和商業(yè)智能應(yīng)用程序中廣泛地使用了LOB，特別是存儲(chǔ)那些包含了復(fù)雜商務(wù)交易的XML文檔和醫(yī)院信息系統(tǒng)捕獲到的醫(yī)療影像數(shù)據(jù)。

為了便于說(shuō)明，我使用清單1中的代碼重新創(chuàng)建了這兩個(gè)表，我使用了列表分區(qū)對(duì)這兩個(gè)表進(jìn)行了分區(qū)，在清單2中，我使用更大的數(shù)據(jù)重新載入TRBTKT.TICKETS表。

清單1 使用列表分區(qū)重新創(chuàng)建這兩個(gè)表以模擬數(shù)據(jù)倉(cāng)庫(kù)環(huán)境

-- 重新創(chuàng)建表TRBTKT.TICKETS，這次包括了一個(gè)STATUS列

CREATE TABLE trbtkt.tickets (
     tkt_idNUMBER
    ,description    VARCHAR2(30)
    ,submit_dtm     TIMESTAMP
    ,statusVARCHAR2(8)
    ,document       BLOB
    ,scrnimg        BLOB
)
    LOB(document)   
        STORE AS BASICFILE (TABLESPACE basicfiles)
   ,LOB(scrnimg)    
        STORE AS BASICFILE (TABLESPACE basicfiles)
    PARTITION BY LIST (status) (
        PARTITION sts_open 
   VALUES ('OPEN')
       ,PARTITION sts_pending
   VALUES ('PENDING')
       ,PARTITION sts_closed
   VALUES ('CLOSED')
       ,PARTITION sts_other
   VALUES (DEFAULT)
    )
;


--注釋
COMMENT ON TABLE trbtkt.tickets
    IS 'Contains Trouble Ticket transaction data';
COMMENT ON COLUMN trbtkt.tickets.tkt_id
    IS 'Unique identifier for a Trouble Ticket';
COMMENT ON COLUMN trbtkt.tickets.description
    IS 'Trouble Ticket Description';
COMMENT ON COLUMN trbtkt.tickets.submit_dtm
    IS 'Trouble Ticket Submission Time Stamp';
COMMENT ON COLUMN trbtkt.tickets.status
    IS 'Trouble Ticket Status';
COMMENT ON COLUMN trbtkt.tickets.document
    IS 'Large Object (LOB) that contains a Microsoft Word document that provides detailed information about a Trouble Ticket';
COMMENT ON COLUMN trbtkt.tickets.scrnimg
    IS 'Large Object (LOB) that contains a screen print or other image that helps a problem solver identify how to act on a Trouble Ticket';


-- 創(chuàng)建索引和約束
CREATE UNIQUE INDEX trbtkt.tickets_pk_idx
    ON trbtkt.tickets(tkt_id)
    TABLESPACE users;


ALTER TABLE trbtkt.tickets
    ADD CONSTRAINT tickets_pk
    PRIMARY KEY (tkt_id);

--- 創(chuàng)建表TRBTKT.SECURE_TICKETS的一個(gè)改良版本，使用分區(qū)劃分SecureFile LOB的存儲(chǔ)，基于STATUS 的值設(shè)置合適的保留周期

DROP TABLE trbtkt.secure_tickets PURGE;
CREATE TABLE trbtkt.secure_tickets (
     tkt_idNUMBER
    ,description    VARCHAR2(30)
    ,submit_dtm     TIMESTAMP
    ,statusVARCHAR2(8)
    ,document       BLOB
    ,scrnimg        BLOB
)
    LOB(document)   
        STORE AS SECUREFILE (
   TABLESPACE securefiles
   DISABLE STORAGE IN ROW
   CACHE
        )
   ,LOB(scrnimg)    
        STORE AS SECUREFILE (
   TABLESPACE securefiles
   DISABLE STORAGE IN ROW
   CACHE READS
        )
    PARTITION BY LIST (status) (
        PARTITION sts_open 
   VALUES ('OPEN')
       LOB (document) STORE AS SECUREFILE (KEEP_DUPLICATES NOCOMPRESS)
      ,LOB (scrnimg)  STORE AS SECUREFILE (KEEP_DUPLICATES COMPRESS)
       ,PARTITION sts_pending
   VALUES ('PENDING')
       LOB (document) STORE AS SECUREFILE (KEEP_DUPLICATES NOCOMPRESS)
      ,LOB (scrnimg)  STORE AS SECUREFILE (DEDUPLICATE COMPRESS)
       ,PARTITION sts_closed
   VALUES ('CLOSED')
       LOB (document) STORE AS SECUREFILE (DEDUPLICATE COMPRESS)
      ,LOB (scrnimg)  STORE AS SECUREFILE (DEDUPLICATE COMPRESS HIGH)
       ,PARTITION sts_other
   VALUES (DEFAULT)
       LOB (document) STORE AS SECUREFILE (DEDUPLICATE COMPRESS HIGH)
      ,LOB (scrnimg)  STORE AS SECUREFILE (DEDUPLICATE COMPRESS HIGH)
    )
;


--注釋
COMMENT ON TABLE trbtkt.secure_tickets
    IS 'Contains Trouble Ticket transaction data';
COMMENT ON COLUMN trbtkt.secure_tickets.tkt_id
    IS 'Unique identifier for a Trouble Ticket';
COMMENT ON COLUMN trbtkt.secure_tickets.description
    IS 'Trouble Ticket Description';
COMMENT ON COLUMN trbtkt.secure_tickets.submit_dtm
    IS 'Trouble Ticket Submission Time Stamp';
COMMENT ON COLUMN trbtkt.secure_tickets.status
    IS 'Trouble Ticket Status';
COMMENT ON COLUMN trbtkt.secure_tickets.document
    IS 'Large Object (LOB) that contains a Microsoft Word document that provides detailed information about a Trouble Ticket';
COMMENT ON COLUMN trbtkt.secure_tickets.scrnimg
    IS 'Large Object (LOB) that contains a screen print or other image that helps a problem solver identify how to act on a Trouble Ticket';

清單2 使用附加數(shù)據(jù)重新載入表TRBTKT.TICKETS 

SET SERVEROUTPUT ON
TRUNCATE TABLE trbtkt.tickets;


BEGIN


    trbtkt.pkg_securefiles.AddTroubleTicket (
tkt_id => 101
        ,description => 'Trouble Ticket 101'
        ,submit_dts => '2008-12-31 23:45:00'
        ,status => 'OPEN'
        ,docFileName => 'New_101.doc'
        ,imgFileName => 'DBRIssues.jpg'
    );


    trbtkt.pkg_securefiles.AddTroubleTicket (
tkt_id => 102
        ,description => 'Trouble Ticket 102'
        ,submit_dts => '2009-01-04 00:00:00'
        ,status => 'OPEN'
        ,docFileName => 'New_102.doc'
        ,imgFileName => 'Unresolved.jpg'
    );


    trbtkt.pkg_securefiles.AddTroubleTicket (
tkt_id => 103
        ,description => 'Trouble Ticket 103'
        ,submit_dts => '2009-01-02 00:00:00'
        ,status => 'OPEN'
        ,docFileName => 'New_103.doc'
        ,imgFileName => 'Unresolved.jpg'
     );


    trbtkt.pkg_securefiles.AddTroubleTicket (
tkt_id => 104
        ,description => 'Trouble Ticket 104'
        ,submit_dts => '2009-01-14 12:30:00'
        ,status => 'OPEN'
        ,docFileName => 'New_104.doc'
        ,imgFileName => 'DBRIssues.jpg'
   );


    trbtkt.pkg_securefiles.AddTroubleTicket (
tkt_id => 105
        ,description => 'Trouble Ticket 105'
        ,submit_dts => '2009-01-09 00:00:00'
        ,status => 'OPEN'
        ,docFileName => 'New_105.doc'
        ,imgFileName => 'Unresolved.jpg'
    );


    trbtkt.pkg_securefiles.AddTroubleTicket (
tkt_id => 106
        ,description => 'Trouble Ticket 106'
        ,submit_dts => '2009-01-11 00:00:00'
        ,status => 'OPEN'
        ,docFileName => 'New_106.doc'
        ,imgFileName => 'Unresolved.jpg'
    );
    
    trbtkt.pkg_securefiles.AddTroubleTicket (
tkt_id => 107
        ,description => 'Trouble Ticket 107'
        ,submit_dts => '2009-01-16 00:00:00'
        ,status => 'OPEN'
        ,docFileName => 'New_107.doc'
        ,imgFileName => 'DBRIssues.jpg'
    );
    
    trbtkt.pkg_securefiles.AddTroubleTicket (
tkt_id => 108
        ,description => 'Trouble Ticket 108'
        ,submit_dts => '2009-01-12 00:00:00'
        ,status => 'OPEN'
        ,docFileName => 'New_108.doc'
        ,imgFileName => 'Unresolved.jpg'
    );
    
    trbtkt.pkg_securefiles.AddTroubleTicket (
tkt_id => 109
        ,description => 'Trouble Ticket 109'
        ,submit_dts => '2009-01-02 00:00:00'
        ,status => 'OPEN'
        ,docFileName => 'New_109.doc'
        ,imgFileName => 'Unresolved.jpg'
     );


    trbtkt.pkg_securefiles.AddTroubleTicket (
tkt_id => 110
        ,description => 'Trouble Ticket 110'
        ,submit_dts => '2009-01-14 12:45:00'
        ,status => 'OPEN'
        ,docFileName => 'New_110.doc'
        ,imgFileName => 'DBRIssues.jpg'
   );


    trbtkt.pkg_securefiles.AddTroubleTicket (
tkt_id => 201
        ,description => 'Trouble Ticket 201'
        ,submit_dts => '2008-12-31 23:45:00'
        ,status => 'PENDING'
        ,docFileName => 'New_101.doc'
        ,imgFileName => 'DBRIssues.jpg'
    );


    trbtkt.pkg_securefiles.AddTroubleTicket (
tkt_id => 202
        ,description => 'Trouble Ticket 202'
        ,submit_dts => '2009-01-04 00:00:00'
        ,status => 'OPEN'
        ,docFileName => 'New_102.doc'
        ,imgFileName => 'Unresolved.jpg'
    );


    trbtkt.pkg_securefiles.AddTroubleTicket (
tkt_id => 203
        ,description => 'Trouble Ticket 203'
        ,submit_dts => '2009-01-02 00:00:00'
        ,status => 'CLOSED'
        ,docFileName => 'New_103.doc'
        ,imgFileName => 'Unresolved.jpg'
     );


    trbtkt.pkg_securefiles.AddTroubleTicket (
tkt_id => 204
        ,description => 'Trouble Ticket 204'
        ,submit_dts => '2009-01-14 12:30:00'
        ,status => 'OPEN'
        ,docFileName => 'New_104.doc'
        ,imgFileName => 'DBRIssues.jpg'
   );


    trbtkt.pkg_securefiles.AddTroubleTicket (
tkt_id => 205
        ,description => 'Trouble Ticket 205'
        ,submit_dts => '2009-01-09 00:00:00'
        ,status => 'OPEN'
        ,docFileName => 'New_105.doc'
        ,imgFileName => 'Unresolved.jpg'
    );


    trbtkt.pkg_securefiles.AddTroubleTicket (
tkt_id => 206
        ,description => 'Trouble Ticket 206'
        ,submit_dts => '2009-01-11 00:00:00'
        ,status => 'PENDING'
        ,docFileName => 'New_106.doc'
        ,imgFileName => 'Unresolved.jpg'
    );
    
    trbtkt.pkg_securefiles.AddTroubleTicket (
tkt_id => 207
        ,description => 'Trouble Ticket 207'
        ,submit_dts => '2009-01-16 00:00:00'
        ,status => 'OPEN'
        ,docFileName => 'New_107.doc'
        ,imgFileName => 'DBRIssues.jpg'
    );
    
    trbtkt.pkg_securefiles.AddTroubleTicket (
tkt_id => 208
        ,description => 'Trouble Ticket 208'
        ,submit_dts => '2009-01-12 00:00:00'
        ,status => 'OPEN'
        ,docFileName => 'New_108.doc'
        ,imgFileName => 'Unresolved.jpg'
    );
    
    trbtkt.pkg_securefiles.AddTroubleTicket (
tkt_id => 209
        ,description => 'Trouble Ticket 209'
        ,submit_dts => '2009-01-02 00:00:00'
        ,status => 'PENDING'
        ,docFileName => 'New_109.doc'
        ,imgFileName => 'Unresolved.jpg'
     );


    trbtkt.pkg_securefiles.AddTroubleTicket (
tkt_id => 210
        ,description => 'Trouble Ticket 210'
        ,submit_dts => '2009-01-14 12:45:00'
        ,status => 'OPEN'
        ,docFileName => 'New_110.doc'
        ,imgFileName => 'DBRIssues.jpg'
   );


    trbtkt.pkg_securefiles.AddTroubleTicket (
tkt_id => 301
        ,description => 'Trouble Ticket 301'
        ,submit_dts => '2008-12-31 23:45:00'
        ,status => 'CLOSED'
        ,docFileName => 'New_101.doc'
        ,imgFileName => 'DBRIssues.jpg'
    );


    trbtkt.pkg_securefiles.AddTroubleTicket (
tkt_id => 302
        ,description => 'Trouble Ticket 302'
        ,submit_dts => '2009-01-04 00:00:00'
        ,status => 'OPEN'
        ,docFileName => 'New_102.doc'
        ,imgFileName => 'Unresolved.jpg'
    );


    trbtkt.pkg_securefiles.AddTroubleTicket (
tkt_id => 303
        ,description => 'Trouble Ticket 303'
        ,submit_dts => '2009-01-02 00:00:00'
        ,status => 'OPEN'
        ,docFileName => 'New_103.doc'
        ,imgFileName => 'Unresolved.jpg'
     );


    trbtkt.pkg_securefiles.AddTroubleTicket (
tkt_id => 304
        ,description => 'Trouble Ticket 304'
        ,submit_dts => '2009-01-14 12:30:00'
        ,status => 'CLOSED'
        ,docFileName => 'New_104.doc'
        ,imgFileName => 'DBRIssues.jpg'
   );


    trbtkt.pkg_securefiles.AddTroubleTicket (
tkt_id => 305
        ,description => 'Trouble Ticket 305'
        ,submit_dts => '2009-01-09 00:00:00'
        ,status => 'PENDING'
        ,docFileName => 'New_105.doc'
        ,imgFileName => 'Unresolved.jpg'
    );


    trbtkt.pkg_securefiles.AddTroubleTicket (
tkt_id => 306
        ,description => 'Trouble Ticket 306'
        ,submit_dts => '2009-01-11 00:00:00'
        ,status => 'CLOSED'
        ,docFileName => 'New_106.doc'
        ,imgFileName => 'Unresolved.jpg'
    );
    
    trbtkt.pkg_securefiles.AddTroubleTicket (
tkt_id => 307
        ,description => 'Trouble Ticket 307'
        ,submit_dts => '2009-01-16 00:00:00'
        ,status => 'OPEN'
        ,docFileName => 'New_107.doc'
        ,imgFileName => 'DBRIssues.jpg'
    );
    
    trbtkt.pkg_securefiles.AddTroubleTicket (
tkt_id => 308
        ,description => 'Trouble Ticket 308'
        ,submit_dts => '2009-01-12 00:00:00'
        ,status => 'OPEN'
        ,docFileName => 'New_108.doc'
        ,imgFileName => 'Unresolved.jpg'
    );
    
    trbtkt.pkg_securefiles.AddTroubleTicket (
tkt_id => 309
        ,description => 'Trouble Ticket 309'
        ,submit_dts => '2009-01-02 00:00:00'
        ,status => 'CLOSED'
        ,docFileName => 'New_109.doc'
        ,imgFileName => 'Unresolved.jpg'
     );


    trbtkt.pkg_securefiles.AddTroubleTicket (
tkt_id => 310
        ,description => 'Trouble Ticket 310'
        ,submit_dts => '2009-01-14 12:45:00'
        ,status => 'CLOSED'
        ,docFileName => 'New_110.doc'
        ,imgFileName => 'DBRIssues.jpg'
   );


    COMMIT;
   
END;
/
-- 收集優(yōu)化器統(tǒng)計(jì)信息
BEGIN
    DBMS_STATS.GATHER_SCHEMA_STATS(ownname => 'TRBTKT', CASCADE => TRUE);
END;
/
有效地從BasicFile移植到SecureFile

現(xiàn)在已經(jīng)將這些表進(jìn)行了分區(qū)，以便在數(shù)據(jù)倉(cāng)庫(kù)環(huán)境更有效地使用，我也已經(jīng)考慮過(guò)如何將LOB從BasicFile格式快速有效地轉(zhuǎn)換成對(duì)應(yīng)的SecureFile格式，幸運(yùn)的是，Oracle 11g提供了兩個(gè)方法：分區(qū)交換和在線重定義。

1、分區(qū)交換

分區(qū)交換載入(Partition Exchange Load，即PDL)有時(shí)也叫做分區(qū)轉(zhuǎn)動(dòng)，包含創(chuàng)建一個(gè)非分區(qū)表，它的格式和內(nèi)容完全匹配目標(biāo)分區(qū)表想要的分區(qū)，一旦原始表創(chuàng)建好了，就可以開始可已經(jīng)分區(qū)的表的目標(biāo)分區(qū)進(jìn)行交換了，這個(gè)方法確實(shí)提供了許多好處，換句話說(shuō)，原始表的本地索引在交換期間得到維護(hù)，它可以并行執(zhí)行，這個(gè)方法可以重復(fù)多次以填充每個(gè)分區(qū)，它的主要缺點(diǎn)是接收數(shù)據(jù)的分區(qū)在進(jìn)行交換時(shí)必須離線。

2、在線重定義

Oracle推薦使用DBMS_REDEFINITION包對(duì)源表和目標(biāo)表執(zhí)行在線重定義，因?yàn)樗鼘?shí)現(xiàn)了分區(qū)交換方法相同的目標(biāo)。但它不需要在重定義過(guò)程中將目標(biāo)離線。在線重定義可以在表級(jí)也可以在分區(qū)級(jí)執(zhí)行，它也可以同時(shí)并行為多個(gè)分區(qū)執(zhí)行，我喜歡在線重定義的特性是在這個(gè)過(guò)程成功執(zhí)行完畢后，源和目標(biāo)對(duì)象將對(duì)真正地在數(shù)據(jù)庫(kù)數(shù)據(jù)目錄中轉(zhuǎn)換定義，這意味著任何引用了新對(duì)象名的PL/SQL對(duì)象都不需要進(jìn)行任何修改。清單3顯示了如何使用TRBTKT.TICKETS表作為源對(duì)象，使用TRBTKT.SECURE_TICKETS表作為目標(biāo)對(duì)象進(jìn)行在線重定義的。從BasicFile LOB轉(zhuǎn)換到SecureFile LOB最后一個(gè)注意事項(xiàng)是：一旦轉(zhuǎn)換完成，LOB就不能再降級(jí)回到BasicFile LOB了。

管理SecureFile元數(shù)據(jù)

這些新的SecureFile特性無(wú)疑擴(kuò)展了LOB的能力，對(duì)于DBA而言，也需要注意幾個(gè)在傳統(tǒng)的BasicFile LOB上增加的額外屬性，幸運(yùn)的是，Oracle 11g提供了多個(gè)方法跟蹤BasicFile和SecureFile LOB的元數(shù)據(jù)。

1、數(shù)據(jù)字典視圖

Oracle 11g也更新了多個(gè)關(guān)于SecureFile信息的數(shù)據(jù)字典視圖，下面列出這些做了變動(dòng)的視圖。

清單4顯示了多條SQL*Plus查詢，它們使用了這些數(shù)據(jù)字典視圖返回關(guān)于SecureFile元數(shù)據(jù)的信息，報(bào)告1顯示了從這些查詢返回的結(jié)果。

清單4 查詢BasicFile和SecureFile LOB的元數(shù)據(jù)

SET PAGESIZE 1000
SET LINESIZE 140 
-- 視圖: DBA_SEGMENTS
--顯示關(guān)于BasicFile 和SecureFile 段的元數(shù)據(jù)
TTITLE 'LOB Segment Information|(from DBA_SEGMENTS)'
COL segment_name        FORMAT A30      HEADING 'Segment Name'
COL segment_type        FORMAT A20      HEADING 'Segment|Type'
COL segment_subtype     FORMAT A20      HEADING 'Segment|SubType'
COL partition_name      FORMAT A12      HEADING 'Partition|Name'
COL tablespace_name     FORMAT A12      HEADING 'Tablespace'
SELECT 
    segment_name
   ,segment_type
   ,segment_subtype
   ,partition_name
   ,tablespace_name
  FROM dba_segments
WHERE owner = 'TRBTKT'
ORDER BY segment_name
;
TTITLE OFF
-- 視圖: DBA_LOBS
--顯示關(guān)于BasicFile和SecureFile LOB的元數(shù)據(jù)
TTITLE 'BasicFile and SecureFile LOBs Metadata|(from DBA_LOBS)'
COL table_name      FORMAT A14      HEADING 'Table'
COL segment_name    FORMAT A26      HEADING 'Segment'
COL column_name     FORMAT A10      HEADING 'Column'
COL tablespace_name FORMAT A12      HEADING 'Tablespace'
COL loggingFORMAT A08      HEADING 'Logging'
COL cache  FORMAT A10      HEADING 'Cacheing'
COL in_row FORMAT A07      HEADING 'Stored|In Row'
COL encryptFORMAT A07      HEADING 'Encryp-|tion'
COL compression     FORMAT A07      HEADING 'Compre-|ssion'
COL deduplication   FORMAT A08      HEADING 'DeDupli-|cation'
COL securefile      FORMAT A07      HEADING 'Secure|File?'
COL partitioned     FORMAT A07      HEADING 'Parti-|tioned'
SELECT
    table_name
   ,column_name
   ,segment_name
   ,tablespace_name
   ,logging
   ,cache
   ,in_row
   ,encrypt
   ,compression
   ,deduplication
   ,securefile
   ,partitioned
  FROM dba_lobs
WHERE owner = 'TRBTKT'
ORDER BY table_name, column_name
;
TTITLE OFF
--視圖: DBA_PART_LOBS
--顯示BasicFile和SecureFile LOB的默認(rèn)值
TTITLE 'BasicFile and SecureFile Partitioned LOB Default Settings|(from DBA_PART_LOBS)'
COL table_name FORMAT A20      HEADING 'Table'
COL column_nameFORMAT A12      HEADING 'Column'
COL def_cache  FORMAT A12      HEADING 'Cached'
COL def_tablespace_name FORMAT A12      HEADING 'Tablespace'
COL def_securefile      FORMAT A12      HEADING 'SecureFile'
COL def_encryptFORMAT A12      HEADING 'Encrypted'
COL def_compress        FORMAT A12      HEADING 'Compressed'
COL def_deduplicate     FORMAT A12      HEADING 'DeDuplicated'
SELECT
    table_name
   ,column_name
   ,def_cache
   ,def_tablespace_name
   ,def_securefile
   ,def_compress
   ,def_deduplicate
   ,def_encrypt
  FROM dba_part_lobs
WHERE table_owner = 'TRBTKT'
ORDER BY table_name, column_name
;
TTITLE OFF
--視圖: DBA_LOB_PARTITIONS
--在獨(dú)立的LOB級(jí)描述BasicFile和SecureFile設(shè)置
TTITLE 'BasicFile and SecureFile LOB Partitions|(from DBA_LOB_PARTITIONS)'
COL table_name      FORMAT A16      HEADING 'Table'
COL column_name     FORMAT A12      HEADING 'Column'
COL partition_name  FORMAT A12      HEADING 'Stored in|Partition'
COL cache  FORMAT A10      HEADING 'Cacheing'
COL in_row FORMAT A10      HEADING 'Stored|In Row'
COL encryptFORMAT A10      HEADING 'Encrypted'
COL compression     FORMAT A10      HEADING 'Compressed'
COL deduplication   FORMAT A10      HEADING 'DeDupli-|cated'
COL securefile      FORMAT A10      HEADING 'SecureFile?'
SELECT
    table_name
   ,column_name
   ,partition_name
   ,cache
   ,in_row
   ,encrypt
   ,compression
   ,deduplication
   ,securefile
  FROM dba_lob_partitions
WHERE table_owner = 'TRBTKT'
ORDER BY table_name, column_name
;
TTITLE OFF
報(bào)告1 從BasicFIle和SecureFile LOB元數(shù)據(jù)報(bào)告返回的結(jié)果


SET PAGESIZE 1000
SET LINESIZE 140 
-- 視圖: DBA_SEGMENTS
--顯示關(guān)于BasicFile 和SecureFile 段的元數(shù)據(jù)
TTITLE 'LOB Segment Information|(from DBA_SEGMENTS)'
COL segment_name        FORMAT A30      HEADING 'Segment Name'
COL segment_type        FORMAT A20      HEADING 'Segment|Type'
COL segment_subtype     FORMAT A20      HEADING 'Segment|SubType'
COL partition_name      FORMAT A12      HEADING 'Partition|Name'
COL tablespace_name     FORMAT A12      HEADING 'Tablespace'
SELECT 
    segment_name
   ,segment_type
   ,segment_subtype
   ,partition_name
   ,tablespace_name
  FROM dba_segments
WHERE owner = 'TRBTKT'
ORDER BY segment_name
;
TTITLE OFF
-- 視圖: DBA_LOBS
--顯示關(guān)于BasicFile和SecureFile LOB的元數(shù)據(jù)
TTITLE 'BasicFile and SecureFile LOBs Metadata|(from DBA_LOBS)'
COL table_name      FORMAT A14      HEADING 'Table'
COL segment_name    FORMAT A26      HEADING 'Segment'
COL column_name     FORMAT A10      HEADING 'Column'
COL tablespace_name FORMAT A12      HEADING 'Tablespace'
COL loggingFORMAT A08      HEADING 'Logging'
COL cache  FORMAT A10      HEADING 'Cacheing'
COL in_row FORMAT A07      HEADING 'Stored|In Row'
COL encryptFORMAT A07      HEADING 'Encryp-|tion'
COL compression     FORMAT A07      HEADING 'Compre-|ssion'
COL deduplication   FORMAT A08      HEADING 'DeDupli-|cation'
COL securefile      FORMAT A07      HEADING 'Secure|File?'
COL partitioned     FORMAT A07      HEADING 'Parti-|tioned'
SELECT
    table_name
   ,column_name
   ,segment_name
   ,tablespace_name
   ,logging
   ,cache
   ,in_row
   ,encrypt
   ,compression
   ,deduplication
   ,securefile
   ,partitioned
  FROM dba_lobs
WHERE owner = 'TRBTKT'
ORDER BY table_name, column_name
;
TTITLE OFF
--視圖: DBA_PART_LOBS
--顯示BasicFile和SecureFile LOB的默認(rèn)值
TTITLE 'BasicFile and SecureFile Partitioned LOB Default Settings|(from DBA_PART_LOBS)'
COL table_name FORMAT A20      HEADING 'Table'
COL column_nameFORMAT A12      HEADING 'Column'
COL def_cache  FORMAT A12      HEADING 'Cached'
COL def_tablespace_name FORMAT A12      HEADING 'Tablespace'
COL def_securefile      FORMAT A12      HEADING 'SecureFile'
COL def_encryptFORMAT A12      HEADING 'Encrypted'
COL def_compress        FORMAT A12      HEADING 'Compressed'
COL def_deduplicate     FORMAT A12      HEADING 'DeDuplicated'
SELECT
    table_name
   ,column_name
   ,def_cache
   ,def_tablespace_name
   ,def_securefile
   ,def_compress
   ,def_deduplicate
   ,def_encrypt
  FROM dba_part_lobs
WHERE table_owner = 'TRBTKT'
ORDER BY table_name, column_name
;
TTITLE OFF
--視圖: DBA_LOB_PARTITIONS
--在獨(dú)立的LOB級(jí)描述BasicFile和SecureFile設(shè)置
TTITLE 'BasicFile and SecureFile LOB Partitions|(from DBA_LOB_PARTITIONS)'
COL table_name      FORMAT A16      HEADING 'Table'
COL column_name     FORMAT A12      HEADING 'Column'
COL partition_name  FORMAT A12      HEADING 'Stored in|Partition'
COL cache  FORMAT A10      HEADING 'Cacheing'
COL in_row FORMAT A10      HEADING 'Stored|In Row'
COL encryptFORMAT A10      HEADING 'Encrypted'
COL compression     FORMAT A10      HEADING 'Compressed'
COL deduplication   FORMAT A10      HEADING 'DeDupli-|cated'
COL securefile      FORMAT A10      HEADING 'SecureFile?'
SELECT
    table_name
   ,column_name
   ,partition_name
   ,cache
   ,in_row
   ,encrypt
   ,compression
   ,deduplication
   ,securefile
  FROM dba_lob_partitions
WHERE table_owner = 'TRBTKT'
ORDER BY table_name, column_name
;
TTITLE OFF
報(bào)告1 從BasicFIle和SecureFile LOB元數(shù)據(jù)報(bào)告返回的結(jié)果
LOB段信息
      (來(lái)自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元數(shù)據(jù)
        (來(lái)自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分區(qū)LOB默認(rèn)設(shè)置
      (來(lái)自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分區(qū)
   (來(lái)自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

這是另一個(gè)Oracle古老支持包，它的SPACE_USAGE存儲(chǔ)過(guò)程提供關(guān)于BasicFile和SecureFile LOB的空間利用率，我在TRBTKT.PKG_SECUREFILES包中引入了兩個(gè)存儲(chǔ)過(guò)程：CALC_SPACE_BASICFILES和CALC_SPACE_SECUREFILES。但遺憾的是，這個(gè)功能只能在啟用了自動(dòng)段空間管理(ASSM)的表空間上使用，而且，它也不會(huì)考慮任何BasicFile LOB塊的空間利用率，在清單5中，我對(duì)表TRBTKT.TICKETS和TRBTKT.SECURE_TICKETS分別調(diào)用了這些存儲(chǔ)過(guò)程，并返回了相應(yīng)的輸出。 

清單5 確定BasicFile和SecureFile LOB的空間利用率


SET SERVEROUTPUT ON
-- BasicFile存儲(chǔ)利用率:
BEGIN
    trbtkt.pkg_securefiles.calc_space_basicfiles (
ownname => 'TRBTKT'
        ,tabname => 'SECURE_TICKETS'
        ,colname => 'DOCUMENT'
        ,partname => 'STS_OPEN'
    );
    trbtkt.pkg_securefiles.calc_space_basicfiles (
ownname => 'TRBTKT'
        ,tabname => 'SECURE_TICKETS'
        ,colname => 'DOCUMENT'
        ,partname => 'STS_PENDING'
    );
    trbtkt.pkg_securefiles.calc_space_basicfiles (
ownname => 'TRBTKT'
        ,tabname => 'SECURE_TICKETS'
        ,colname => 'DOCUMENT'
        ,partname => 'STS_CLOSED'
    );
    trbtkt.pkg_securefiles.calc_space_basicfiles (
ownname => 'TRBTKT'
        ,tabname => 'SECURE_TICKETS'
        ,colname => 'DOCUMENT'
        ,partname => 'STS_OTHER'
    );
    trbtkt.pkg_securefiles.calc_space_basicfiles (
ownname => 'TRBTKT'
        ,tabname => 'SECURE_TICKETS'
        ,colname => 'SCRNIMG'
        ,partname => 'STS_OPEN'
    );
    trbtkt.pkg_securefiles.calc_space_basicfiles (
ownname => 'TRBTKT'
        ,tabname => 'SECURE_TICKETS'
        ,colname => 'SCRNIMG'
        ,partname => 'STS_PENDING'
    );
    trbtkt.pkg_securefiles.calc_space_basicfiles (
ownname => 'TRBTKT'
        ,tabname => 'SECURE_TICKETS'
        ,colname => 'SCRNIMG'
        ,partname => 'STS_CLOSED'
    );
    trbtkt.pkg_securefiles.calc_space_basicfiles (
ownname => 'TRBTKT'
        ,tabname => 'SECURE_TICKETS'
        ,colname => 'SCRNIMG'
        ,partname => 'STS_OTHER'
    );
END;
/
============================================================
Space Usage for BasicFile LOB TRBTKT.SECURE_TICKETS.DOCUMENT
Partition Name: STS_OPEN
------------------------------------------------------------
Full Blocks: 123 KB: .96
Unformatted Blocks: 379 KB: 2.96 
Total Blocks: 123 Total KB: .96  
============================================================
============================================================
Space Usage for BasicFile LOB TRBTKT.SECURE_TICKETS.DOCUMENT
Partition Name: STS_PENDING      
------------------------------------------------------------
Full Blocks: 20 KB: .16 
Unformatted Blocks: 482 KB: 3.77 
Total Blocks: 20 Total KB: .16   
============================================================
============================================================
Space Usage for BasicFile LOB TRBTKT.SECURE_TICKETS.DOCUMENT
Partition Name: STS_CLOSED       
------------------------------------------------------------
Full Blocks: 37 KB: .29 
Unformatted Blocks: 465 KB: 3.63 
Total Blocks: 37 Total KB: .29   
============================================================
============================================================
Space Usage for BasicFile LOB TRBTKT.SECURE_TICKETS.DOCUMENT
Partition Name: STS_OTHER        
------------------------------------------------------------
Full Blocks: 0 KB: 0    
Unformatted Blocks: 0 KB: 0      
Total Blocks: 0 Total KB: 0      
============================================================
============================================================
Space Usage for BasicFile LOB TRBTKT.SECURE_TICKETS.SCRNIMG 
Partition Name: STS_OPEN
------------------------------------------------------------
Full Blocks: 420 KB: 3.28        
Unformatted Blocks: 82 KB: .64   
Total Blocks: 420 Total KB: 3.28 
============================================================
============================================================
Space Usage for BasicFile LOB TRBTKT.SECURE_TICKETS.SCRNIMG 
Partition Name: STS_PENDING      
------------------------------------------------------------
Full Blocks: 66 KB: .52 
Unformatted Blocks: 436 KB: 3.41 
Total Blocks: 66 Total KB: .52   
============================================================
============================================================
Space Usage for BasicFile LOB TRBTKT.SECURE_TICKETS.SCRNIMG 
Partition Name: STS_CLOSED       
------------------------------------------------------------
Full Blocks: 144 KB: 1.13        
Unformatted Blocks: 358 KB: 2.8  
Total Blocks: 144 Total KB: 1.13 
============================================================
============================================================
Space Usage for BasicFile LOB TRBTKT.SECURE_TICKETS.SCRNIMG 
Partition Name: STS_OTHER        
------------------------------------------------------------
Full Blocks: 0 KB: 0    
Unformatted Blocks: 0 KB: 0      
Total Blocks: 0 Total KB: 0      
============================================================
-- SecureFile存儲(chǔ)利用率:
BEGIN
    trbtkt.pkg_securefiles.calc_space_securefiles (
ownname => 'TRBTKT'
        ,tabname => 'TICKETS'
        ,colname => 'DOCUMENT'
        ,partname => 'STS_OPEN'
    );
    trbtkt.pkg_securefiles.calc_space_securefiles (
ownname => 'TRBTKT'
        ,tabname => 'TICKETS'
        ,colname => 'DOCUMENT'
        ,partname => 'STS_PENDING'
    );
    trbtkt.pkg_securefiles.calc_space_securefiles (
ownname => 'TRBTKT'
        ,tabname => 'TICKETS'
        ,colname => 'DOCUMENT'
        ,partname => 'STS_CLOSED'
    );
    trbtkt.pkg_securefiles.calc_space_securefiles (
ownname => 'TRBTKT'
        ,tabname => 'TICKETS'
        ,colname => 'DOCUMENT'
        ,partname => 'STS_OTHER'
    );
    trbtkt.pkg_securefiles.calc_space_securefiles (
ownname => 'TRBTKT'
        ,tabname => 'TICKETS'
        ,colname => 'SCRNIMG'
        ,partname => 'STS_OPEN'
    );
    trbtkt.pkg_securefiles.calc_space_securefiles (
ownname => 'TRBTKT'
        ,tabname => 'TICKETS'
        ,colname => 'SCRNIMG'
        ,partname => 'STS_PENDING'
    );
    trbtkt.pkg_securefiles.calc_space_securefiles (
ownname => 'TRBTKT'
        ,tabname => 'TICKETS'
        ,colname => 'SCRNIMG'
        ,partname => 'STS_CLOSED'
    );
    trbtkt.pkg_securefiles.calc_space_securefiles (
ownname => 'TRBTKT'
        ,tabname => 'TICKETS'
        ,colname => 'SCRNIMG'
        ,partname => 'STS_OTHER'
    );
END;
/


============================================================
Space Usage for SecureFile LOB TRBTKT.TICKETS.DOCUMENT      
Partition Name: STS_OPEN
------------------------------------------------------------
Segment Blocks: 1024 KB: 8192    
Used Blocks: 124 KB: 992
Expired Blocks: 882 KB: 7056     
Unexpired Blocks: 0 KB: 0        
============================================================
============================================================
Space Usage for SecureFile LOB TRBTKT.TICKETS.DOCUMENT      
Partition Name: STS_PENDING      
------------------------------------------------------------
Segment Blocks: 1024 KB: 8192    
Used Blocks: 21 KB: 168 
Expired Blocks: 985 KB: 7880     
Unexpired Blocks: 0 KB: 0        
============================================================
============================================================
Space Usage for SecureFile LOB TRBTKT.TICKETS.DOCUMENT      
Partition Name: STS_CLOSED       
------------------------------------------------------------
Segment Blocks: 1024 KB: 8192    
Used Blocks: 13 KB: 104 
Expired Blocks: 993 KB: 7944     
Unexpired Blocks: 0 KB: 0        
============================================================
============================================================
Space Usage for SecureFile LOB TRBTKT.TICKETS.DOCUMENT      
Partition Name: STS_OTHER        
------------------------------------------------------------
Segment Blocks: 512 KB: 4096     
Used Blocks: 501 KB: 4008        
Expired Blocks: 0 KB: 0 
Unexpired Blocks: 0 KB: 0        
============================================================
============================================================
Space Usage for SecureFile LOB TRBTKT.TICKETS.SCRNIMG       
Partition Name: STS_OPEN
------------------------------------------------------------
Segment Blocks: 2560 KB: 20480   
Used Blocks: 405 KB: 3240        
Expired Blocks: 2134 KB: 17072   
Unexpired Blocks: 0 KB: 0        
============================================================
============================================================
Space Usage for SecureFile LOB TRBTKT.TICKETS.SCRNIMG       
Partition Name: STS_PENDING      
------------------------------------------------------------
Segment Blocks: 1024 KB: 8192    
Used Blocks: 62 KB: 496 
Expired Blocks: 944 KB: 7552     
Unexpired Blocks: 0 KB: 0        
============================================================
============================================================
Space Usage for SecureFile LOB TRBTKT.TICKETS.SCRNIMG       
Partition Name: STS_CLOSED       
------------------------------------------------------------
Segment Blocks: 1024 KB: 8192    
Used Blocks: 142 KB: 1136        
Expired Blocks: 864 KB: 6912     
Unexpired Blocks: 0 KB: 0        
============================================================
============================================================
Space Usage for SecureFile LOB TRBTKT.TICKETS.SCRNIMG       
Partition Name: STS_OTHER        
------------------------------------------------------------
Segment Blocks: 512 KB: 4096     
Used Blocks: 501 KB: 4008        
Expired Blocks: 0 KB: 0 
Unexpired Blocks: 0 KB: 0        
============================================================

SET SERVEROUTPUT ON修改SecureFile屬性

當(dāng)我創(chuàng)建TRBTKT.SECURE_TICKETS表時(shí)，你可能注意到我為每個(gè)LOB列在它們對(duì)應(yīng)的分區(qū)中執(zhí)行壓縮和重復(fù)刪除，重要的是我還使用了ALTER TABLE語(yǔ)句進(jìn)行修正，另外，如果需要的話，我還可以在每個(gè)LOB獨(dú)立的分區(qū)上應(yīng)用改變到SecureFile LOB。清單6中的代碼顯示了4個(gè)修改TRBTKT.TICKETS表的SecureFile LOB屬性的例子，注意這個(gè)表現(xiàn)在已經(jīng)用清單3中的代碼和TRBTKT.SECURE_TICKETS表執(zhí)行了交換，我也從DBA_PART_LOBS創(chuàng)建了一個(gè)報(bào)告顯示在修改這些LOB屬性前后的狀態(tài)，結(jié)果輸出如報(bào)告2所示。

清單6 管理SecureFile LOB屬性

SQL> ALTER TABLE TRBTKT.TICKETS
SQL>     MODIFY LOB(DOCUMENT) (NOCOMPRESS);


Table altered.



SQL> ALTER TABLE TRBTKT.TICKETS
SQL>     MODIFY LOB(SCRNIMG) (KEEP_DUPLICATES);


Table altered.


SQL> ALTER TABLE TRBTKT.TICKETS
SQL>     MODIFY PARTITION STS_PENDING LOB(SCRNIMG) (COMPRESS HIGH);
       
Table altered.
       
SQL> ALTER TABLE TRBTKT.TICKETS
SQL>     MODIFY PARTITION STS_PENDING LOB(DOCUMENT) (DEDUPLICATE);
       
Table altered.
報(bào)告2 修改許多SecureFile LOB后查詢DBA_LOB_PARTITIONS返回的結(jié)果


  BasicFile and SecureFile LOB Partitions
   (from DBA_LOB_PARTITIONS)
   Stored in      StoredDeDupli-
Table   Column       Partition    Cacheing   In Row     Encrypted  Compressed cated      SecureFile
---------------- ------------ ------------ ---------- ---------- ---------- ---------- --------
SECURE_TICKETS   DOCUMENT     STS_OTHER    NOYES        NONE       NONE       NONE       NO
SECURE_TICKETS   DOCUMENT     STS_CLOSED   NOYES        NONE       NONE       NONE       NO
SECURE_TICKETS   DOCUMENT     STS_OPEN     NOYES        NONE       NONE       NONE       NO
SECURE_TICKETS   DOCUMENT     STS_PENDING  NOYES        NONE       NONE       NONE       NO
SECURE_TICKETS   SCRNIMG      STS_OTHER    NOYES        NONE       NONE       NONE       NO
SECURE_TICKETS   SCRNIMG      STS_CLOSED   NOYES        NONE       NONE       NONE       NO
SECURE_TICKETS   SCRNIMG      STS_PENDING  NOYES        NONE       NONE       NONE       NO
SECURE_TICKETS   SCRNIMG      STS_OPEN     NOYES        NONE       NONE       NONE       NO
TICKETS DOCUMENT     STS_OTHER    YES        NONONOLOB        YES
TICKETS DOCUMENT     STS_PENDING  YES        NONONOLOB        YES
TICKETS DOCUMENT     STS_CLOSED   YES        NONONOLOB        YES
TICKETS DOCUMENT     STS_OPEN     YES        NONONONOYES
TICKETS SCRNIMG      STS_PENDING  CACHEREADS NONOHIGH       NOYES
TICKETS SCRNIMG      STS_OPEN     CACHEREADS NONOMEDIUM     NOYES
TICKETS SCRNIMG      STS_CLOSED   CACHEREADS NONOHIGH       NOYES
TICKETS SCRNIMG      STS_OTHER    CACHEREADS NONOHIGH       NOYES

最后，請(qǐng)記住任何對(duì)LOB屬性進(jìn)行修改都只會(huì)影響到新創(chuàng)建的LOB或新修改的LOB，例如，將列TRBTKT.SECURE_TICKETS.SCRNIMG的壓縮方法從COMPRESS修改為NOCOMPRESS不會(huì)影響到現(xiàn)有的LOB條目。

【編輯推薦】

  1. Oracle數(shù)據(jù)庫(kù)開發(fā)經(jīng)驗(yàn)淺談
  2. Oracle存儲(chǔ)過(guò)程開發(fā)實(shí)例
  3. Oracle數(shù)據(jù)庫(kù)設(shè)計(jì)提升性能的五條法則

【責(zé)任編輯：彭凡 TEL：（010）68476606】

責(zé)任編輯：彭凡 來(lái)源： ITPUB
OracleBasicFileSecureFile
相關(guān)推薦

2010-07-20 09:48:33

2016-10-26 16:44:44

WatchfinderAWS云計(jì)算

2011-06-24 10:10:35

SVN

2020-01-06 12:50:50

Windows 7遷移Windows 10

2016-11-11 00:00:16

MySQLOracle數(shù)據(jù)

2010-09-29 11:06:21

活動(dòng)目錄OpenLDAP

2017-10-20 08:45:15

數(shù)據(jù)庫(kù)MongoDBMySQL

2015-06-17 13:26:41

FICOVMwareOpenStack

2012-05-21 10:23:36

2013-06-21 13:49:08

MariaDB

2019-01-07 08:10:54

微服務(wù)單體 Web

2023-12-07 07:14:36

WebpackVite

2020-07-27 11:35:26

GitHub代碼開發(fā)者

2021-11-29 09:44:03

UmiJSVite前端

2022-06-06 07:24:09

Caddy開源Ubuntu

2009-02-06 10:32:00

UnixLinux服務(wù)器

2011-04-25 09:35:31

TwitterJava

2013-05-03 09:49:38

MySQLMariaDB

2012-02-24 09:34:48

SVNGit

2022-07-27 22:48:29

消息中間件RocketMQ架構(gòu)設(shè)計(jì)
點(diǎn)贊
收藏

51CTO技術(shù)棧公眾號(hào)