如何從BasicFile遷移到SecureFile存儲（一）

Oracle 11g新的SecureFile存儲特性擴(kuò)展了大對象(LOB)的靈活性和容量，本文研究如何有效地從BasicFile遷移到SecureFile存儲，如何測量SecureFile vs BasicFile LOB的效率，以及如何為SecureFile LOB使用不同的壓縮和重復(fù)數(shù)據(jù)刪除選項。

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

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

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

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

-- 重新創(chuàng)建表TRBTKT.TICKETS，這次包括了一個STATUS列
CREATE TABLE trbtkt.tickets (
     tkt_id         NUMBER
    ,description    VARCHAR2(30)
    ,submit_dtm     TIMESTAMP
    ,status         VARCHAR2(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 p_w_picpath 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的一個改良版本，使用分區(qū)劃分SecureFile LOB的存儲，基于STATUS 的值設(shè)置合適的保留周期
DROP TABLE trbtkt.secure_tickets PURGE;
CREATE TABLE trbtkt.secure_tickets (
     tkt_id         NUMBER
    ,description    VARCHAR2(30)
    ,submit_dtm     TIMESTAMP
    ,status         VARCHAR2(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 p_w_picpath 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)計信息
BEGIN
    DBMS_STATS.GATHER_SCHEMA_STATS(ownname => 'TRBTKT', CASCADE => TRUE);
END;
/

　　有效地從BasicFile移植到SecureFile

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

　　1、分區(qū)交換

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

　　2、在線重定義

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

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

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

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

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

　　清單4顯示了多條SQL*Plus查詢，它們使用了這些數(shù)據(jù)字典視圖返回關(guān)于SecureFile元數(shù)據(jù)的信息，報告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 logging         FORMAT A08      HEADING 'Logging'
COL cache           FORMAT A10      HEADING 'Cacheing'
COL in_row          FORMAT A07      HEADING 'Stored|In Row'
COL encrypt         FORMAT 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_name         FORMAT 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_encrypt         FORMAT A12      HEADING 'Encrypted'
COL def_compress        FORMAT A12      HEADING 'Compressed'
COL def_deduplicate     FORMAT A12      HEADING 'DeDuplicated'
SELECT
    table_name
   ,column_name
   ,def_cache
   ,def_tablespace_name
   ,def_securefile
   ,def_compress
   ,def_deduplicate
   ,def_encrypt
  FROM dba_part_lobs
WHERE table_owner = 'TRBTKT'
ORDER BY table_name, column_name
;
TTITLE OFF
--視圖: DBA_LOB_PARTITIONS
--在獨立的LOB級描述BasicFile和SecureFile設(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 encrypt         FORMAT A10      HEADING 'Encrypted'
COL compression     FORMAT A10      HEADING 'Compressed'
COL deduplication   FORMAT A10      HEADING 'DeDupli-|cated'
COL securefile      FORMAT A10      HEADING 'SecureFile?'
SELECT
    table_name
   ,column_name
   ,partition_name
   ,cache
   ,in_row
   ,encrypt
   ,compression
   ,deduplication
   ,securefile
  FROM dba_lob_partitions
WHERE table_owner = 'TRBTKT'
ORDER BY table_name, column_name
;
TTITLE OFF

報告1 從BasicFIle和SecureFile LOB元數(shù)據(jù)報告返回的結(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 logging         FORMAT A08      HEADING 'Logging'
COL cache           FORMAT A10      HEADING 'Cacheing'
COL in_row          FORMAT A07      HEADING 'Stored|In Row'
COL encrypt         FORMAT 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_name         FORMAT 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_encrypt         FORMAT A12      HEADING 'Encrypted'
COL def_compress        FORMAT A12      HEADING 'Compressed'
COL def_deduplicate     FORMAT A12      HEADING 'DeDuplicated'
SELECT
    table_name
   ,column_name
   ,def_cache
   ,def_tablespace_name
   ,def_securefile
   ,def_compress
   ,def_deduplicate
   ,def_encrypt
  FROM dba_part_lobs
WHERE table_owner = 'TRBTKT'
ORDER BY table_name, column_name
;
TTITLE OFF
--視圖: DBA_LOB_PARTITIONS
--在獨立的LOB級描述BasicFile和SecureFile設(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 encrypt         FORMAT A10      HEADING 'Encrypted'
COL compression     FORMAT A10      HEADING 'Compressed'
COL deduplication   FORMAT A10      HEADING 'DeDupli-|cated'
COL securefile      FORMAT A10      HEADING 'SecureFile?'
SELECT
    table_name
   ,column_name
   ,partition_name
   ,cache
   ,in_row
   ,encrypt
   ,compression
   ,deduplication
   ,securefile
  FROM dba_lob_partitions
WHERE table_owner = 'TRBTKT'
ORDER BY table_name, column_name
;
TTITLE OFF
報告1 從BasicFIle和SecureFile LOB元數(shù)據(jù)報告返回的結(jié)果
LOB段信息
                                                            (來自DBA_SEGMENTS)

                               Segment              Segment              Partition                                                          
Segment Name                   Type                 SubType              Name         Tablespace                                            
------------------------- -------------------- -------------------- ------------ ------------                                          
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