Oracle中如何从BasicFile迁移到SecureFile-51CTO.COM

我创建了一个新表TRBTKT.SECURE_TICKETS重新定义了原始表TRBTKT.TICKETS的结构，将原始的BasicFile LOB映射到对应的SecureFile LOB，然后执行了一条INSERT INTO … SELECT FROM DML语句将原始表中的内容传输到新表中。

在一个相对简单的应用程序环境中，这种载入方式足以应付，但目前在数据仓库和商业智能应用程序中广泛地使用了LOB，特别是存储那些包含了复杂商务交易的XML文档和医院信息系统捕获到的医疗影像数据。

为了便于说明，我使用清单1中的代码重新创建了这两个表，我使用了列表分区对这两个表进行了分区，在清单2中，我使用更大的数据重新载入TRBTKT.TICKETS表。

清单1 使用列表分区重新创建这两个表以模拟数据仓库环境

-- 重新创建表TRBTKT.TICKETS，这次包括了一个STATUS列

CREATE TABLE trbtkt.tickets (
     tkt_idNUMBER
    ,description    VARCHAR2(30)
    ,submit_dtm     TIMESTAMP
    ,statusVARCHAR2(8)
    ,document       BLOB
    ,scrnimg        BLOB
)
    LOB(document)   
        STORE AS BASICFILE (TABLESPACE basicfiles)
   ,LOB(scrnimg)    
        STORE AS BASICFILE (TABLESPACE basicfiles)
    PARTITION BY LIST (status) (
        PARTITION sts_open 
   VALUES ('OPEN')
       ,PARTITION sts_pending
   VALUES ('PENDING')
       ,PARTITION sts_closed
   VALUES ('CLOSED')
       ,PARTITION sts_other
   VALUES (DEFAULT)
    )
;
--注释
COMMENT ON TABLE trbtkt.tickets
    IS 'Contains Trouble Ticket transaction data';
COMMENT ON COLUMN trbtkt.tickets.tkt_id
    IS 'Unique identifier for a Trouble Ticket';
COMMENT ON COLUMN trbtkt.tickets.description
    IS 'Trouble Ticket Description';
COMMENT ON COLUMN trbtkt.tickets.submit_dtm
    IS 'Trouble Ticket Submission Time Stamp';
COMMENT ON COLUMN trbtkt.tickets.status
    IS 'Trouble Ticket Status';
COMMENT ON COLUMN trbtkt.tickets.document
    IS 'Large Object (LOB) that contains a Microsoft Word document that provides detailed information about a Trouble Ticket';
COMMENT ON COLUMN trbtkt.tickets.scrnimg
    IS 'Large Object (LOB) that contains a screen print or other image that helps a problem solver identify how to act on a Trouble Ticket';
-- 创建索引和约束
CREATE UNIQUE INDEX trbtkt.tickets_pk_idx
    ON trbtkt.tickets(tkt_id)
    TABLESPACE users;
ALTER TABLE trbtkt.tickets
    ADD CONSTRAINT tickets_pk
    PRIMARY KEY (tkt_id);1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.

--- 创建表TRBTKT.SECURE_TICKETS的一个改良版本，使用分区划分SecureFile LOB的存储，基于STATUS 的值设置合适的保留周期

DROP TABLE trbtkt.secure_tickets PURGE;
CREATE TABLE trbtkt.secure_tickets (
     tkt_idNUMBER
    ,description    VARCHAR2(30)
    ,submit_dtm     TIMESTAMP
    ,statusVARCHAR2(8)
    ,document       BLOB
    ,scrnimg        BLOB
)
    LOB(document)   
        STORE AS SECUREFILE (
   TABLESPACE securefiles
   DISABLE STORAGE IN ROW
   CACHE
        )
   ,LOB(scrnimg)    
        STORE AS SECUREFILE (
   TABLESPACE securefiles
   DISABLE STORAGE IN ROW
   CACHE READS
        )
    PARTITION BY LIST (status) (
        PARTITION sts_open 
   VALUES ('OPEN')
       LOB (document) STORE AS SECUREFILE (KEEP_DUPLICATES NOCOMPRESS)
      ,LOB (scrnimg)  STORE AS SECUREFILE (KEEP_DUPLICATES COMPRESS)
       ,PARTITION sts_pending
   VALUES ('PENDING')
       LOB (document) STORE AS SECUREFILE (KEEP_DUPLICATES NOCOMPRESS)
      ,LOB (scrnimg)  STORE AS SECUREFILE (DEDUPLICATE COMPRESS)
       ,PARTITION sts_closed
   VALUES ('CLOSED')
       LOB (document) STORE AS SECUREFILE (DEDUPLICATE COMPRESS)
      ,LOB (scrnimg)  STORE AS SECUREFILE (DEDUPLICATE COMPRESS HIGH)
       ,PARTITION sts_other
   VALUES (DEFAULT)
       LOB (document) STORE AS SECUREFILE (DEDUPLICATE COMPRESS HIGH)
      ,LOB (scrnimg)  STORE AS SECUREFILE (DEDUPLICATE COMPRESS HIGH)
    )
;
--注释
COMMENT ON TABLE trbtkt.secure_tickets
    IS 'Contains Trouble Ticket transaction data';
COMMENT ON COLUMN trbtkt.secure_tickets.tkt_id
    IS 'Unique identifier for a Trouble Ticket';
COMMENT ON COLUMN trbtkt.secure_tickets.description
    IS 'Trouble Ticket Description';
COMMENT ON COLUMN trbtkt.secure_tickets.submit_dtm
    IS 'Trouble Ticket Submission Time Stamp';
COMMENT ON COLUMN trbtkt.secure_tickets.status
    IS 'Trouble Ticket Status';
COMMENT ON COLUMN trbtkt.secure_tickets.document
    IS 'Large Object (LOB) that contains a Microsoft Word document that provides detailed information about a Trouble Ticket';
COMMENT ON COLUMN trbtkt.secure_tickets.scrnimg
    IS 'Large Object (LOB) that contains a screen print or other image that helps a problem solver identify how to act on a Trouble Ticket';1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.

清单2 使用附加数据重新载入表TRBTKT.TICKETS

SET SERVEROUTPUT ON
TRUNCATE TABLE trbtkt.tickets;
BEGIN
    trbtkt.pkg_securefiles.AddTroubleTicket (
tkt_id => 101
        ,description => 'Trouble Ticket 101'
        ,submit_dts => '2008-12-31 23:45:00'
        ,status => 'OPEN'
        ,docFileName => 'New_101.doc'
        ,imgFileName => 'DBRIssues.jpg'
    );
    trbtkt.pkg_securefiles.AddTroubleTicket (
tkt_id => 102
        ,description => 'Trouble Ticket 102'
        ,submit_dts => '2009-01-04 00:00:00'
        ,status => 'OPEN'
        ,docFileName => 'New_102.doc'
        ,imgFileName => 'Unresolved.jpg'
    );
    trbtkt.pkg_securefiles.AddTroubleTicket (
tkt_id => 103
        ,description => 'Trouble Ticket 103'
        ,submit_dts => '2009-01-02 00:00:00'
        ,status => 'OPEN'
        ,docFileName => 'New_103.doc'
        ,imgFileName => 'Unresolved.jpg'
     );
    trbtkt.pkg_securefiles.AddTroubleTicket (
tkt_id => 104
        ,description => 'Trouble Ticket 104'
        ,submit_dts => '2009-01-14 12:30:00'
        ,status => 'OPEN'
        ,docFileName => 'New_104.doc'
        ,imgFileName => 'DBRIssues.jpg'
   );
    trbtkt.pkg_securefiles.AddTroubleTicket (
tkt_id => 105
        ,description => 'Trouble Ticket 105'
        ,submit_dts => '2009-01-09 00:00:00'
        ,status => 'OPEN'
        ,docFileName => 'New_105.doc'
        ,imgFileName => 'Unresolved.jpg'
    );
    trbtkt.pkg_securefiles.AddTroubleTicket (
tkt_id => 106
        ,description => 'Trouble Ticket 106'
        ,submit_dts => '2009-01-11 00:00:00'
        ,status => 'OPEN'
        ,docFileName => 'New_106.doc'
        ,imgFileName => 'Unresolved.jpg'
    );
    
    trbtkt.pkg_securefiles.AddTroubleTicket (
tkt_id => 107
        ,description => 'Trouble Ticket 107'
        ,submit_dts => '2009-01-16 00:00:00'
        ,status => 'OPEN'
        ,docFileName => 'New_107.doc'
        ,imgFileName => 'DBRIssues.jpg'
    );
    
    trbtkt.pkg_securefiles.AddTroubleTicket (
tkt_id => 108
        ,description => 'Trouble Ticket 108'
        ,submit_dts => '2009-01-12 00:00:00'
        ,status => 'OPEN'
        ,docFileName => 'New_108.doc'
        ,imgFileName => 'Unresolved.jpg'
    );
    
    trbtkt.pkg_securefiles.AddTroubleTicket (
tkt_id => 109
        ,description => 'Trouble Ticket 109'
        ,submit_dts => '2009-01-02 00:00:00'
        ,status => 'OPEN'
        ,docFileName => 'New_109.doc'
        ,imgFileName => 'Unresolved.jpg'
     );
    trbtkt.pkg_securefiles.AddTroubleTicket (
tkt_id => 110
        ,description => 'Trouble Ticket 110'
        ,submit_dts => '2009-01-14 12:45:00'
        ,status => 'OPEN'
        ,docFileName => 'New_110.doc'
        ,imgFileName => 'DBRIssues.jpg'
   );
    trbtkt.pkg_securefiles.AddTroubleTicket (
tkt_id => 201
        ,description => 'Trouble Ticket 201'
        ,submit_dts => '2008-12-31 23:45:00'
        ,status => 'PENDING'
        ,docFileName => 'New_101.doc'
        ,imgFileName => 'DBRIssues.jpg'
    );
    trbtkt.pkg_securefiles.AddTroubleTicket (
tkt_id => 202
        ,description => 'Trouble Ticket 202'
        ,submit_dts => '2009-01-04 00:00:00'
        ,status => 'OPEN'
        ,docFileName => 'New_102.doc'
        ,imgFileName => 'Unresolved.jpg'
    );
    trbtkt.pkg_securefiles.AddTroubleTicket (
tkt_id => 203
        ,description => 'Trouble Ticket 203'
        ,submit_dts => '2009-01-02 00:00:00'
        ,status => 'CLOSED'
        ,docFileName => 'New_103.doc'
        ,imgFileName => 'Unresolved.jpg'
     );
    trbtkt.pkg_securefiles.AddTroubleTicket (
tkt_id => 204
        ,description => 'Trouble Ticket 204'
        ,submit_dts => '2009-01-14 12:30:00'
        ,status => 'OPEN'
        ,docFileName => 'New_104.doc'
        ,imgFileName => 'DBRIssues.jpg'
   );
    trbtkt.pkg_securefiles.AddTroubleTicket (
tkt_id => 205
        ,description => 'Trouble Ticket 205'
        ,submit_dts => '2009-01-09 00:00:00'
        ,status => 'OPEN'
        ,docFileName => 'New_105.doc'
        ,imgFileName => 'Unresolved.jpg'
    );
    trbtkt.pkg_securefiles.AddTroubleTicket (
tkt_id => 206
        ,description => 'Trouble Ticket 206'
        ,submit_dts => '2009-01-11 00:00:00'
        ,status => 'PENDING'
        ,docFileName => 'New_106.doc'
        ,imgFileName => 'Unresolved.jpg'
    );
    
    trbtkt.pkg_securefiles.AddTroubleTicket (
tkt_id => 207
        ,description => 'Trouble Ticket 207'
        ,submit_dts => '2009-01-16 00:00:00'
        ,status => 'OPEN'
        ,docFileName => 'New_107.doc'
        ,imgFileName => 'DBRIssues.jpg'
    );
    
    trbtkt.pkg_securefiles.AddTroubleTicket (
tkt_id => 208
        ,description => 'Trouble Ticket 208'
        ,submit_dts => '2009-01-12 00:00:00'
        ,status => 'OPEN'
        ,docFileName => 'New_108.doc'
        ,imgFileName => 'Unresolved.jpg'
    );
    
    trbtkt.pkg_securefiles.AddTroubleTicket (
tkt_id => 209
        ,description => 'Trouble Ticket 209'
        ,submit_dts => '2009-01-02 00:00:00'
        ,status => 'PENDING'
        ,docFileName => 'New_109.doc'
        ,imgFileName => 'Unresolved.jpg'
     );
    trbtkt.pkg_securefiles.AddTroubleTicket (
tkt_id => 210
        ,description => 'Trouble Ticket 210'
        ,submit_dts => '2009-01-14 12:45:00'
        ,status => 'OPEN'
        ,docFileName => 'New_110.doc'
        ,imgFileName => 'DBRIssues.jpg'
   );
    trbtkt.pkg_securefiles.AddTroubleTicket (
tkt_id => 301
        ,description => 'Trouble Ticket 301'
        ,submit_dts => '2008-12-31 23:45:00'
        ,status => 'CLOSED'
        ,docFileName => 'New_101.doc'
        ,imgFileName => 'DBRIssues.jpg'
    );
    trbtkt.pkg_securefiles.AddTroubleTicket (
tkt_id => 302
        ,description => 'Trouble Ticket 302'
        ,submit_dts => '2009-01-04 00:00:00'
        ,status => 'OPEN'
        ,docFileName => 'New_102.doc'
        ,imgFileName => 'Unresolved.jpg'
    );
    trbtkt.pkg_securefiles.AddTroubleTicket (
tkt_id => 303
        ,description => 'Trouble Ticket 303'
        ,submit_dts => '2009-01-02 00:00:00'
        ,status => 'OPEN'
        ,docFileName => 'New_103.doc'
        ,imgFileName => 'Unresolved.jpg'
     );
    trbtkt.pkg_securefiles.AddTroubleTicket (
tkt_id => 304
        ,description => 'Trouble Ticket 304'
        ,submit_dts => '2009-01-14 12:30:00'
        ,status => 'CLOSED'
        ,docFileName => 'New_104.doc'
        ,imgFileName => 'DBRIssues.jpg'
   );
    trbtkt.pkg_securefiles.AddTroubleTicket (
tkt_id => 305
        ,description => 'Trouble Ticket 305'
        ,submit_dts => '2009-01-09 00:00:00'
        ,status => 'PENDING'
        ,docFileName => 'New_105.doc'
        ,imgFileName => 'Unresolved.jpg'
    );
    trbtkt.pkg_securefiles.AddTroubleTicket (
tkt_id => 306
        ,description => 'Trouble Ticket 306'
        ,submit_dts => '2009-01-11 00:00:00'
        ,status => 'CLOSED'
        ,docFileName => 'New_106.doc'
        ,imgFileName => 'Unresolved.jpg'
    );
    
    trbtkt.pkg_securefiles.AddTroubleTicket (
tkt_id => 307
        ,description => 'Trouble Ticket 307'
        ,submit_dts => '2009-01-16 00:00:00'
        ,status => 'OPEN'
        ,docFileName => 'New_107.doc'
        ,imgFileName => 'DBRIssues.jpg'
    );
    
    trbtkt.pkg_securefiles.AddTroubleTicket (
tkt_id => 308
        ,description => 'Trouble Ticket 308'
        ,submit_dts => '2009-01-12 00:00:00'
        ,status => 'OPEN'
        ,docFileName => 'New_108.doc'
        ,imgFileName => 'Unresolved.jpg'
    );
    
    trbtkt.pkg_securefiles.AddTroubleTicket (
tkt_id => 309
        ,description => 'Trouble Ticket 309'
        ,submit_dts => '2009-01-02 00:00:00'
        ,status => 'CLOSED'
        ,docFileName => 'New_109.doc'
        ,imgFileName => 'Unresolved.jpg'
     );
    trbtkt.pkg_securefiles.AddTroubleTicket (
tkt_id => 310
        ,description => 'Trouble Ticket 310'
        ,submit_dts => '2009-01-14 12:45:00'
        ,status => 'CLOSED'
        ,docFileName => 'New_110.doc'
        ,imgFileName => 'DBRIssues.jpg'
   );
    COMMIT;
   
END;
/
-- 收集优化器统计信息
BEGIN
    DBMS_STATS.GATHER_SCHEMA_STATS(ownname => 'TRBTKT', CASCADE => TRUE);
END;
/
有效地从BasicFile移植到SecureFile1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
83.
84.
85.
86.
87.
88.
89.
90.
91.
92.
93.
94.
95.
96.
97.
98.
99.
100.
101.
102.
103.
104.
105.
106.
107.
108.
109.
110.
111.
112.
113.
114.
115.
116.
117.
118.
119.
120.
121.
122.
123.
124.
125.
126.
127.
128.
129.
130.
131.
132.
133.
134.
135.
136.
137.
138.
139.
140.
141.
142.
143.
144.
145.
146.
147.
148.
149.
150.
151.
152.
153.
154.
155.
156.
157.
158.
159.
160.
161.
162.
163.
164.
165.
166.
167.
168.
169.
170.
171.
172.
173.
174.
175.
176.
177.
178.
179.
180.
181.
182.
183.
184.
185.
186.
187.
188.
189.
190.
191.
192.
193.
194.
195.
196.
197.
198.
199.
200.
201.
202.
203.
204.
205.
206.
207.
208.
209.
210.
211.
212.
213.
214.
215.
216.
217.
218.
219.
220.
221.
222.
223.
224.
225.
226.
227.
228.
229.
230.
231.
232.
233.
234.
235.
236.
237.
238.
239.
240.
241.
242.
243.
244.
245.
246.
247.
248.
249.
250.
251.
252.
253.
254.
255.
256.
257.
258.
259.
260.
261.
262.
263.
264.
265.
266.
267.
268.
269.
270.
271.
272.
273.
274.
275.
276.
277.
278.
279.
280.
281.
282.
283.
284.
285.
286.
287.
288.
289.
290.
291.
292.
293.
294.
295.
296.
297.
298.
299.
300.
301.
302.
303.
304.
305.
306.
307.
308.
309.
310.
311.
312.
313.
314.
315.
316.
317.
318.
319.
320.
321.
322.
323.
324.
325.
326.
327.
328.
329.
330.
331.
332.
333.
334.
335.
336.
337.
338.
339.
340.
341.
342.
343.
344.
345.
346.
347.
348.
349.
350.
351.
352.
353.
354.

现在已经将这些表进行了分区，以便在数据仓库环境更有效地使用，我也已经考虑过如何将LOB从BasicFile格式快速有效地转换成对应的SecureFile格式，幸运的是，Oracle 11g提供了两个方法：分区交换和在线重定义。

1、分区交换

分区交换载入(Partition Exchange Load，即PDL)有时也叫做分区转动，包含创建一个非分区表，它的格式和内容完全匹配目标分区表想要的分区，一旦原始表创建好了，就可以开始可已经分区的表的目标分区进行交换了，这个方法确实提供了许多好处，换句话说，原始表的本地索引在交换期间得到维护，它可以并行执行，这个方法可以重复多次以填充每个分区，它的主要缺点是接收数据的分区在进行交换时必须离线。

2、在线重定义

Oracle推荐使用DBMS_REDEFINITION包对源表和目标表执行在线重定义，因为它实现了分区交换方法相同的目标。但它不需要在重定义过程中将目标离线。在线重定义可以在表级也可以在分区级执行，它也可以同时并行为多个分区执行，我喜欢在线重定义的特性是在这个过程成功执行完毕后，源和目标对象将对真正地在数据库数据目录中转换定义，这意味着任何引用了新对象名的PL/SQL对象都不需要进行任何修改。清单3显示了如何使用TRBTKT.TICKETS表作为源对象，使用TRBTKT.SECURE_TICKETS表作为目标对象进行在线重定义的。从BasicFile LOB转换到SecureFile LOB最后一个注意事项是：一旦转换完成，LOB就不能再降级回到BasicFile LOB了。

管理SecureFile元数据

这些新的SecureFile特性无疑扩展了LOB的能力，对于DBA而言，也需要注意几个在传统的BasicFile LOB上增加的额外属性，幸运的是，Oracle 11g提供了多个方法跟踪BasicFile和SecureFile LOB的元数据。

1、数据字典视图

Oracle 11g也更新了多个关于SecureFile信息的数据字典视图，下面列出这些做了变动的视图。

清单4显示了多条SQL*Plus查询，它们使用了这些数据字典视图返回关于SecureFile元数据的信息，报告1显示了从这些查询返回的结果。

清单4 查询BasicFile和SecureFile LOB的元数据

SET PAGESIZE 1000
SET LINESIZE 140 
-- 视图: DBA_SEGMENTS
--显示关于BasicFile 和SecureFile 段的元数据
TTITLE 'LOB Segment Information|(from DBA_SEGMENTS)'
COL segment_name        FORMAT A30      HEADING 'Segment Name'
COL segment_type        FORMAT A20      HEADING 'Segment|Type'
COL segment_subtype     FORMAT A20      HEADING 'Segment|SubType'
COL partition_name      FORMAT A12      HEADING 'Partition|Name'
COL tablespace_name     FORMAT A12      HEADING 'Tablespace'
SELECT 
    segment_name
   ,segment_type
   ,segment_subtype
   ,partition_name
   ,tablespace_name
  FROM dba_segments
WHERE owner = 'TRBTKT'
ORDER BY segment_name
;
TTITLE OFF
-- 视图: DBA_LOBS
--显示关于BasicFile和SecureFile LOB的元数据
TTITLE 'BasicFile and SecureFile LOBs Metadata|(from DBA_LOBS)'
COL table_name      FORMAT A14      HEADING 'Table'
COL segment_name    FORMAT A26      HEADING 'Segment'
COL column_name     FORMAT A10      HEADING 'Column'
COL tablespace_name FORMAT A12      HEADING 'Tablespace'
COL loggingFORMAT A08      HEADING 'Logging'
COL cache  FORMAT A10      HEADING 'Cacheing'
COL in_row FORMAT A07      HEADING 'Stored|In Row'
COL encryptFORMAT A07      HEADING 'Encryp-|tion'
COL compression     FORMAT A07      HEADING 'Compre-|ssion'
COL deduplication   FORMAT A08      HEADING 'DeDupli-|cation'
COL securefile      FORMAT A07      HEADING 'Secure|File?'
COL partitioned     FORMAT A07      HEADING 'Parti-|tioned'
SELECT
    table_name
   ,column_name
   ,segment_name
   ,tablespace_name
   ,logging
   ,cache
   ,in_row
   ,encrypt
   ,compression
   ,deduplication
   ,securefile
   ,partitioned
  FROM dba_lobs
WHERE owner = 'TRBTKT'
ORDER BY table_name, column_name
;
TTITLE OFF
--视图: DBA_PART_LOBS
--显示BasicFile和SecureFile LOB的默认值
TTITLE 'BasicFile and SecureFile Partitioned LOB Default Settings|(from DBA_PART_LOBS)'
COL table_name FORMAT A20      HEADING 'Table'
COL column_nameFORMAT A12      HEADING 'Column'
COL def_cache  FORMAT A12      HEADING 'Cached'
COL def_tablespace_name FORMAT A12      HEADING 'Tablespace'
COL def_securefile      FORMAT A12      HEADING 'SecureFile'
COL def_encryptFORMAT A12      HEADING 'Encrypted'
COL def_compress        FORMAT A12      HEADING 'Compressed'
COL def_deduplicate     FORMAT A12      HEADING 'DeDuplicated'
SELECT
    table_name
   ,column_name
   ,def_cache
   ,def_tablespace_name
   ,def_securefile
   ,def_compress
   ,def_deduplicate
   ,def_encrypt
  FROM dba_part_lobs
WHERE table_owner = 'TRBTKT'
ORDER BY table_name, column_name
;
TTITLE OFF
--视图: DBA_LOB_PARTITIONS
--在独立的LOB级描述BasicFile和SecureFile设置
TTITLE 'BasicFile and SecureFile LOB Partitions|(from DBA_LOB_PARTITIONS)'
COL table_name      FORMAT A16      HEADING 'Table'
COL column_name     FORMAT A12      HEADING 'Column'
COL partition_name  FORMAT A12      HEADING 'Stored in|Partition'
COL cache  FORMAT A10      HEADING 'Cacheing'
COL in_row FORMAT A10      HEADING 'Stored|In Row'
COL encryptFORMAT A10      HEADING 'Encrypted'
COL compression     FORMAT A10      HEADING 'Compressed'
COL deduplication   FORMAT A10      HEADING 'DeDupli-|cated'
COL securefile      FORMAT A10      HEADING 'SecureFile?'
SELECT
    table_name
   ,column_name
   ,partition_name
   ,cache
   ,in_row
   ,encrypt
   ,compression
   ,deduplication
   ,securefile
  FROM dba_lob_partitions
WHERE table_owner = 'TRBTKT'
ORDER BY table_name, column_name
;
TTITLE OFF
报告1 从BasicFIle和SecureFile LOB元数据报告返回的结果
SET PAGESIZE 1000
SET LINESIZE 140 
-- 视图: DBA_SEGMENTS
--显示关于BasicFile 和SecureFile 段的元数据
TTITLE 'LOB Segment Information|(from DBA_SEGMENTS)'
COL segment_name        FORMAT A30      HEADING 'Segment Name'
COL segment_type        FORMAT A20      HEADING 'Segment|Type'
COL segment_subtype     FORMAT A20      HEADING 'Segment|SubType'
COL partition_name      FORMAT A12      HEADING 'Partition|Name'
COL tablespace_name     FORMAT A12      HEADING 'Tablespace'
SELECT 
    segment_name
   ,segment_type
   ,segment_subtype
   ,partition_name
   ,tablespace_name
  FROM dba_segments
WHERE owner = 'TRBTKT'
ORDER BY segment_name
;
TTITLE OFF
-- 视图: DBA_LOBS
--显示关于BasicFile和SecureFile LOB的元数据
TTITLE 'BasicFile and SecureFile LOBs Metadata|(from DBA_LOBS)'
COL table_name      FORMAT A14      HEADING 'Table'
COL segment_name    FORMAT A26      HEADING 'Segment'
COL column_name     FORMAT A10      HEADING 'Column'
COL tablespace_name FORMAT A12      HEADING 'Tablespace'
COL loggingFORMAT A08      HEADING 'Logging'
COL cache  FORMAT A10      HEADING 'Cacheing'
COL in_row FORMAT A07      HEADING 'Stored|In Row'
COL encryptFORMAT A07      HEADING 'Encryp-|tion'
COL compression     FORMAT A07      HEADING 'Compre-|ssion'
COL deduplication   FORMAT A08      HEADING 'DeDupli-|cation'
COL securefile      FORMAT A07      HEADING 'Secure|File?'
COL partitioned     FORMAT A07      HEADING 'Parti-|tioned'
SELECT
    table_name
   ,column_name
   ,segment_name
   ,tablespace_name
   ,logging
   ,cache
   ,in_row
   ,encrypt
   ,compression
   ,deduplication
   ,securefile
   ,partitioned
  FROM dba_lobs
WHERE owner = 'TRBTKT'
ORDER BY table_name, column_name
;
TTITLE OFF
--视图: DBA_PART_LOBS
--显示BasicFile和SecureFile LOB的默认值
TTITLE 'BasicFile and SecureFile Partitioned LOB Default Settings|(from DBA_PART_LOBS)'
COL table_name FORMAT A20      HEADING 'Table'
COL column_nameFORMAT A12      HEADING 'Column'
COL def_cache  FORMAT A12      HEADING 'Cached'
COL def_tablespace_name FORMAT A12      HEADING 'Tablespace'
COL def_securefile      FORMAT A12      HEADING 'SecureFile'
COL def_encryptFORMAT A12      HEADING 'Encrypted'
COL def_compress        FORMAT A12      HEADING 'Compressed'
COL def_deduplicate     FORMAT A12      HEADING 'DeDuplicated'
SELECT
    table_name
   ,column_name
   ,def_cache
   ,def_tablespace_name
   ,def_securefile
   ,def_compress
   ,def_deduplicate
   ,def_encrypt
  FROM dba_part_lobs
WHERE table_owner = 'TRBTKT'
ORDER BY table_name, column_name
;
TTITLE OFF
--视图: DBA_LOB_PARTITIONS
--在独立的LOB级描述BasicFile和SecureFile设置
TTITLE 'BasicFile and SecureFile LOB Partitions|(from DBA_LOB_PARTITIONS)'
COL table_name      FORMAT A16      HEADING 'Table'
COL column_name     FORMAT A12      HEADING 'Column'
COL partition_name  FORMAT A12      HEADING 'Stored in|Partition'
COL cache  FORMAT A10      HEADING 'Cacheing'
COL in_row FORMAT A10      HEADING 'Stored|In Row'
COL encryptFORMAT A10      HEADING 'Encrypted'
COL compression     FORMAT A10      HEADING 'Compressed'
COL deduplication   FORMAT A10      HEADING 'DeDupli-|cated'
COL securefile      FORMAT A10      HEADING 'SecureFile?'
SELECT
    table_name
   ,column_name
   ,partition_name
   ,cache
   ,in_row
   ,encrypt
   ,compression
   ,deduplication
   ,securefile
  FROM dba_lob_partitions
WHERE table_owner = 'TRBTKT'
ORDER BY table_name, column_name
;
TTITLE OFF
报告1 从BasicFIle和SecureFile LOB元数据报告返回的结果
LOB段信息
      (来自DBA_SEGMENTS)
Segment     Segment Segment Name Type ------------------------- SECURE_TICKETS SECURE_TICKETS SECURE_TICKETS SECURE_TICKETS SYS_IL0000072118C00005$$ SYS_IL0000072118C00005$$ SYS_IL0000072118C00005$$ SYS_IL0000072118C00005$$ SYS_IL0000072118C00006$$ SYS_IL0000072118C00006$$ SYS_IL0000072118C00006$$ SYS_IL0000072118C00006$$ SYS_IL0000072144C00005$$ SYS_IL0000072144C00005$$ SYS_IL0000072144C00005$$ SYS_IL0000072144C00005$$ SYS_IL0000072144C00006$$ SYS_IL0000072144C00006$$ SYS_IL0000072144C00006$$ SYS_IL0000072144C00006$$ SYS_LOB0000072118C00005$$ SYS_LOB0000072118C00005$$ SYS_LOB0000072118C00005$$ SYS_LOB0000072118C00005$$ SYS_LOB0000072118C00006$$ SYS_LOB0000072118C00006$$ SYS_LOB0000072118C00006$$ SYS_LOB0000072118C00006$$ SYS_LOB0000072144C00005$$ SYS_LOB0000072144C00005$$ SYS_LOB0000072144C00005$$ SYS_LOB0000072144C00005$$ SYS_LOB0000072144C00006$$ SYS_LOB0000072144C00006$$ SYS_LOB0000072144C00006$$ SYS_LOB0000072144C00006$$ TICKETS      TABLE PARTITION TICKETS      TABLE PARTITION TICKETS      TABLE PARTITION TICKETS      TABLE PARTITION TICKETS_PK_IDX

Partition SubType     NameTablespace -------------------- -------------------- ------------ ------------ TABLE PARTITION      ASSM        STS_PENDING  USERS TABLE PARTITION      ASSM        STS_CLOSED   USERS TABLE PARTITION      ASSM        STS_OPEN     USERS TABLE PARTITION      ASSM        STS_OTHER    USERS INDEX PARTITION      ASSM        SYS_IL_P180  BASICFILES INDEX PARTITION      ASSM        SYS_IL_P179  BASICFILES INDEX PARTITION      ASSM        SYS_IL_P178  BASICFILES INDEX PARTITION      ASSM        SYS_IL_P177  BASICFILES INDEX PARTITION      ASSM        SYS_IL_P185  BASICFILES INDEX PARTITION      ASSM        SYS_IL_P188  BASICFILES INDEX PARTITION      ASSM        SYS_IL_P187  BASICFILES INDEX PARTITION      ASSM        SYS_IL_P186  BASICFILES INDEX PARTITION      ASSM        SYS_IL_P194  SECUREFILES INDEX PARTITION      ASSM        SYS_IL_P193  SECUREFILES INDEX PARTITION      ASSM        SYS_IL_P195  SECUREFILES INDEX PARTITION      ASSM        SYS_IL_P196  SECUREFILES INDEX PARTITION      ASSM        SYS_IL_P204  SECUREFILES INDEX PARTITION      ASSM        SYS_IL_P203  SECUREFILES INDEX PARTITION      ASSM        SYS_IL_P202  SECUREFILES INDEX PARTITION      ASSM        SYS_IL_P201  SECUREFILES LOB PARTITION        ASSM        SYS_LOB_P173 BASICFILES LOB PARTITION        ASSM        SYS_LOB_P176 BASICFILES LOB PARTITION        ASSM        SYS_LOB_P175 BASICFILES LOB PARTITION        ASSM        SYS_LOB_P174 BASICFILES LOB PARTITION        ASSM        SYS_LOB_P184 BASICFILES LOB PARTITION        ASSM        SYS_LOB_P183 BASICFILES LOB PARTITION        ASSM        SYS_LOB_P181 BASICFILES LOB PARTITION        ASSM        SYS_LOB_P182 BASICFILES LOB PARTITION        SECUREFILE  SYS_LOB_P191 SECUREFILES LOB PARTITION        SECUREFILE  SYS_LOB_P192 SECUREFILES LOB PARTITION        SECUREFILE  SYS_LOB_P189 SECUREFILES LOB PARTITION        SECUREFILE  SYS_LOB_P190 SECUREFILES LOB PARTITION        SECUREFILE  SYS_LOB_P198 SECUREFILES LOB PARTITION        SECUREFILE  SYS_LOB_P199 SECUREFILES LOB PARTITION        SECUREFILE  SYS_LOB_P197 SECUREFILES LOB PARTITION        SECUREFILE  SYS_LOB_P200 SECUREFILES ASSM        STS_PENDING  USERS ASSM        STS_OTHER    USERS ASSM        STS_OPEN     USERS ASSM        STS_CLOSED   USERS 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 
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
83.
84.
85.
86.
87.
88.
89.
90.
91.
92.
93.
94.
95.
96.
97.
98.
99.
100.
101.
102.
103.
104.
105.
106.
107.
108.
109.
110.
111.
112.
113.
114.
115.
116.
117.
118.
119.
120.
121.
122.
123.
124.
125.
126.
127.
128.
129.
130.
131.
132.
133.
134.
135.
136.
137.
138.
139.
140.
141.
142.
143.
144.
145.
146.
147.
148.
149.
150.
151.
152.
153.
154.
155.
156.
157.
158.
159.
160.
161.
162.
163.
164.
165.
166.
167.
168.
169.
170.
171.
172.
173.
174.
175.
176.
177.
178.
179.
180.
181.
182.
183.
184.
185.
186.
187.
188.
189.
190.
191.
192.
193.
194.
195.
196.
197.
198.
199.
200.
201.
202.
203.
204.
205.
206.
207.
208.
209.
210.
211.
212.
213.
214.
215.
216.
217.
218.
219.
220.
221.
222.
223.
224.
225.
226.
227.
228.
229.
230.
231.
232.
233.
234.
235.
236.
237.
238.
239.
240.
241.
242.
243.
244.
245.
246.
247.
248.
249.
250.
251.
252.
253.
254.
255.
256.
257.
258.
259.
260.
261.
262.
263.
264.
265.
266.
267.
268.
269.
270.
271.
272.
273.
274.
275.
276.
277.
278.
279.
280.
281.
282.
283.
284.
285.
286.
287.
288.
289.
290.
291.
292.
293.
294.
295.
296.
297.
298.
299.
300.
301.
302.
303.
304.
305.
306.
307.
308.
309.
310.
311.
312.
313.
314.
315.
316.
317.
318.
319.
320.
321.
322.
323.
324.
325.
326.
327.
328.
329.
330.

这是另一个Oracle古老支持包，它的SPACE_USAGE存储过程提供关于BasicFile和SecureFile LOB的空间利用率，我在TRBTKT.PKG_SECUREFILES包中引入了两个存储过程：CALC_SPACE_BASICFILES和CALC_SPACE_SECUREFILES。但遗憾的是，这个功能只能在启用了自动段空间管理(ASSM)的表空间上使用，而且，它也不会考虑任何BasicFile LOB块的空间利用率，在清单5中，我对表TRBTKT.TICKETS和TRBTKT.SECURE_TICKETS分别调用了这些存储过程，并返回了相应的输出。

清单5 确定BasicFile和SecureFile LOB的空间利用率
SET SERVEROUTPUT ON
-- BasicFile存储利用率:
BEGIN
    trbtkt.pkg_securefiles.calc_space_basicfiles (
ownname => 'TRBTKT'
        ,tabname => 'SECURE_TICKETS'
        ,colname => 'DOCUMENT'
        ,partname => 'STS_OPEN'
    );
    trbtkt.pkg_securefiles.calc_space_basicfiles (
ownname => 'TRBTKT'
        ,tabname => 'SECURE_TICKETS'
        ,colname => 'DOCUMENT'
        ,partname => 'STS_PENDING'
    );
    trbtkt.pkg_securefiles.calc_space_basicfiles (
ownname => 'TRBTKT'
        ,tabname => 'SECURE_TICKETS'
        ,colname => 'DOCUMENT'
        ,partname => 'STS_CLOSED'
    );
    trbtkt.pkg_securefiles.calc_space_basicfiles (
ownname => 'TRBTKT'
        ,tabname => 'SECURE_TICKETS'
        ,colname => 'DOCUMENT'
        ,partname => 'STS_OTHER'
    );
    trbtkt.pkg_securefiles.calc_space_basicfiles (
ownname => 'TRBTKT'
        ,tabname => 'SECURE_TICKETS'
        ,colname => 'SCRNIMG'
        ,partname => 'STS_OPEN'
    );
    trbtkt.pkg_securefiles.calc_space_basicfiles (
ownname => 'TRBTKT'
        ,tabname => 'SECURE_TICKETS'
        ,colname => 'SCRNIMG'
        ,partname => 'STS_PENDING'
    );
    trbtkt.pkg_securefiles.calc_space_basicfiles (
ownname => 'TRBTKT'
        ,tabname => 'SECURE_TICKETS'
        ,colname => 'SCRNIMG'
        ,partname => 'STS_CLOSED'
    );
    trbtkt.pkg_securefiles.calc_space_basicfiles (
ownname => 'TRBTKT'
        ,tabname => 'SECURE_TICKETS'
        ,colname => 'SCRNIMG'
        ,partname => 'STS_OTHER'
    );
END;
/
============================================================
Space Usage for BasicFile LOB TRBTKT.SECURE_TICKETS.DOCUMENT
Partition Name: STS_OPEN
------------------------------------------------------------
Full Blocks: 123 KB: .96
Unformatted Blocks: 379 KB: 2.96 
Total Blocks: 123 Total KB: .96  
============================================================
============================================================
Space Usage for BasicFile LOB TRBTKT.SECURE_TICKETS.DOCUMENT
Partition Name: STS_PENDING      
------------------------------------------------------------
Full Blocks: 20 KB: .16 
Unformatted Blocks: 482 KB: 3.77 
Total Blocks: 20 Total KB: .16   
============================================================
============================================================
Space Usage for BasicFile LOB TRBTKT.SECURE_TICKETS.DOCUMENT
Partition Name: STS_CLOSED       
------------------------------------------------------------
Full Blocks: 37 KB: .29 
Unformatted Blocks: 465 KB: 3.63 
Total Blocks: 37 Total KB: .29   
============================================================
============================================================
Space Usage for BasicFile LOB TRBTKT.SECURE_TICKETS.DOCUMENT
Partition Name: STS_OTHER        
------------------------------------------------------------
Full Blocks: 0 KB: 0    
Unformatted Blocks: 0 KB: 0      
Total Blocks: 0 Total KB: 0      
============================================================
============================================================
Space Usage for BasicFile LOB TRBTKT.SECURE_TICKETS.SCRNIMG 
Partition Name: STS_OPEN
------------------------------------------------------------
Full Blocks: 420 KB: 3.28        
Unformatted Blocks: 82 KB: .64   
Total Blocks: 420 Total KB: 3.28 
============================================================
============================================================
Space Usage for BasicFile LOB TRBTKT.SECURE_TICKETS.SCRNIMG 
Partition Name: STS_PENDING      
------------------------------------------------------------
Full Blocks: 66 KB: .52 
Unformatted Blocks: 436 KB: 3.41 
Total Blocks: 66 Total KB: .52   
============================================================
============================================================
Space Usage for BasicFile LOB TRBTKT.SECURE_TICKETS.SCRNIMG 
Partition Name: STS_CLOSED       
------------------------------------------------------------
Full Blocks: 144 KB: 1.13        
Unformatted Blocks: 358 KB: 2.8  
Total Blocks: 144 Total KB: 1.13 
============================================================
============================================================
Space Usage for BasicFile LOB TRBTKT.SECURE_TICKETS.SCRNIMG 
Partition Name: STS_OTHER        
------------------------------------------------------------
Full Blocks: 0 KB: 0    
Unformatted Blocks: 0 KB: 0      
Total Blocks: 0 Total KB: 0      
============================================================
-- SecureFile存储利用率:
BEGIN
    trbtkt.pkg_securefiles.calc_space_securefiles (
ownname => 'TRBTKT'
        ,tabname => 'TICKETS'
        ,colname => 'DOCUMENT'
        ,partname => 'STS_OPEN'
    );
    trbtkt.pkg_securefiles.calc_space_securefiles (
ownname => 'TRBTKT'
        ,tabname => 'TICKETS'
        ,colname => 'DOCUMENT'
        ,partname => 'STS_PENDING'
    );
    trbtkt.pkg_securefiles.calc_space_securefiles (
ownname => 'TRBTKT'
        ,tabname => 'TICKETS'
        ,colname => 'DOCUMENT'
        ,partname => 'STS_CLOSED'
    );
    trbtkt.pkg_securefiles.calc_space_securefiles (
ownname => 'TRBTKT'
        ,tabname => 'TICKETS'
        ,colname => 'DOCUMENT'
        ,partname => 'STS_OTHER'
    );
    trbtkt.pkg_securefiles.calc_space_securefiles (
ownname => 'TRBTKT'
        ,tabname => 'TICKETS'
        ,colname => 'SCRNIMG'
        ,partname => 'STS_OPEN'
    );
    trbtkt.pkg_securefiles.calc_space_securefiles (
ownname => 'TRBTKT'
        ,tabname => 'TICKETS'
        ,colname => 'SCRNIMG'
        ,partname => 'STS_PENDING'
    );
    trbtkt.pkg_securefiles.calc_space_securefiles (
ownname => 'TRBTKT'
        ,tabname => 'TICKETS'
        ,colname => 'SCRNIMG'
        ,partname => 'STS_CLOSED'
    );
    trbtkt.pkg_securefiles.calc_space_securefiles (
ownname => 'TRBTKT'
        ,tabname => 'TICKETS'
        ,colname => 'SCRNIMG'
        ,partname => 'STS_OTHER'
    );
END;
/
============================================================
Space Usage for SecureFile LOB TRBTKT.TICKETS.DOCUMENT      
Partition Name: STS_OPEN
------------------------------------------------------------
Segment Blocks: 1024 KB: 8192    
Used Blocks: 124 KB: 992
Expired Blocks: 882 KB: 7056     
Unexpired Blocks: 0 KB: 0        
============================================================
============================================================
Space Usage for SecureFile LOB TRBTKT.TICKETS.DOCUMENT      
Partition Name: STS_PENDING      
------------------------------------------------------------
Segment Blocks: 1024 KB: 8192    
Used Blocks: 21 KB: 168 
Expired Blocks: 985 KB: 7880     
Unexpired Blocks: 0 KB: 0        
============================================================
============================================================
Space Usage for SecureFile LOB TRBTKT.TICKETS.DOCUMENT      
Partition Name: STS_CLOSED       
------------------------------------------------------------
Segment Blocks: 1024 KB: 8192    
Used Blocks: 13 KB: 104 
Expired Blocks: 993 KB: 7944     
Unexpired Blocks: 0 KB: 0        
============================================================
============================================================
Space Usage for SecureFile LOB TRBTKT.TICKETS.DOCUMENT      
Partition Name: STS_OTHER        
------------------------------------------------------------
Segment Blocks: 512 KB: 4096     
Used Blocks: 501 KB: 4008        
Expired Blocks: 0 KB: 0 
Unexpired Blocks: 0 KB: 0        
============================================================
============================================================
Space Usage for SecureFile LOB TRBTKT.TICKETS.SCRNIMG       
Partition Name: STS_OPEN
------------------------------------------------------------
Segment Blocks: 2560 KB: 20480   
Used Blocks: 405 KB: 3240        
Expired Blocks: 2134 KB: 17072   
Unexpired Blocks: 0 KB: 0        
============================================================
============================================================
Space Usage for SecureFile LOB TRBTKT.TICKETS.SCRNIMG       
Partition Name: STS_PENDING      
------------------------------------------------------------
Segment Blocks: 1024 KB: 8192    
Used Blocks: 62 KB: 496 
Expired Blocks: 944 KB: 7552     
Unexpired Blocks: 0 KB: 0        
============================================================
============================================================
Space Usage for SecureFile LOB TRBTKT.TICKETS.SCRNIMG       
Partition Name: STS_CLOSED       
------------------------------------------------------------
Segment Blocks: 1024 KB: 8192    
Used Blocks: 142 KB: 1136        
Expired Blocks: 864 KB: 6912     
Unexpired Blocks: 0 KB: 0        
============================================================
============================================================
Space Usage for SecureFile LOB TRBTKT.TICKETS.SCRNIMG       
Partition Name: STS_OTHER        
------------------------------------------------------------
Segment Blocks: 512 KB: 4096     
Used Blocks: 501 KB: 4008        
Expired Blocks: 0 KB: 0 
Unexpired Blocks: 0 KB: 0        
============================================================
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
83.
84.
85.
86.
87.
88.
89.
90.
91.
92.
93.
94.
95.
96.
97.
98.
99.
100.
101.
102.
103.
104.
105.
106.
107.
108.
109.
110.
111.
112.
113.
114.
115.
116.
117.
118.
119.
120.
121.
122.
123.
124.
125.
126.
127.
128.
129.
130.
131.
132.
133.
134.
135.
136.
137.
138.
139.
140.
141.
142.
143.
144.
145.
146.
147.
148.
149.
150.
151.
152.
153.
154.
155.
156.
157.
158.
159.
160.
161.
162.
163.
164.
165.
166.
167.
168.
169.
170.
171.
172.
173.
174.
175.
176.
177.
178.
179.
180.
181.
182.
183.
184.
185.
186.
187.
188.
189.
190.
191.
192.
193.
194.
195.
196.
197.
198.
199.
200.
201.
202.
203.
204.
205.
206.
207.
208.
209.
210.
211.
212.
213.
214.
215.
216.
217.
218.
219.
220.
221.
222.
223.
224.
225.
226.
227.
228.
229.
230.
231.
232.
233.
234.
235.
236.
237.
238.
239.
240.
241.
242.
243.
244.
245.
246.
247.
248.
249.
250.

SET SERVEROUTPUT ON修改SecureFile属性

当我创建TRBTKT.SECURE_TICKETS表时，你可能注意到我为每个LOB列在它们对应的分区中执行压缩和重复删除，重要的是我还使用了ALTER TABLE语句进行修正，另外，如果需要的话，我还可以在每个LOB独立的分区上应用改变到SecureFile LOB。清单6中的代码显示了4个修改TRBTKT.TICKETS表的SecureFile LOB属性的例子，注意这个表现在已经用清单3中的代码和TRBTKT.SECURE_TICKETS表执行了交换，我也从DBA_PART_LOBS创建了一个报告显示在修改这些LOB属性前后的状态，结果输出如报告2所示。

清单6 管理SecureFile LOB属性

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

SQL> ALTER TABLE TRBTKT.TICKETS
SQL>     MODIFY LOB(SCRNIMG) (KEEP_DUPLICATES);
Table altered.
SQL> ALTER TABLE TRBTKT.TICKETS
SQL>     MODIFY PARTITION STS_PENDING LOB(SCRNIMG) (COMPRESS HIGH);
       
Table altered.
       
SQL> ALTER TABLE TRBTKT.TICKETS
SQL>     MODIFY PARTITION STS_PENDING LOB(DOCUMENT) (DEDUPLICATE);
       
Table altered.
报告2 修改许多SecureFile LOB后查询DBA_LOB_PARTITIONS返回的结果
  BasicFile and SecureFile LOB Partitions
   (from DBA_LOB_PARTITIONS)
   Stored in      StoredDeDupli-
Table   Column       Partition    Cacheing   In Row     Encrypted  Compressed cated      SecureFile
---------------- ------------ ------------ ---------- ---------- ---------- ---------- --------
SECURE_TICKETS   DOCUMENT     STS_OTHER    NOYES        NONE       NONE       NONE       NO
SECURE_TICKETS   DOCUMENT     STS_CLOSED   NOYES        NONE       NONE       NONE       NO
SECURE_TICKETS   DOCUMENT     STS_OPEN     NOYES        NONE       NONE       NONE       NO
SECURE_TICKETS   DOCUMENT     STS_PENDING  NOYES        NONE       NONE       NONE       NO
SECURE_TICKETS   SCRNIMG      STS_OTHER    NOYES        NONE       NONE       NONE       NO
SECURE_TICKETS   SCRNIMG      STS_CLOSED   NOYES        NONE       NONE       NONE       NO
SECURE_TICKETS   SCRNIMG      STS_PENDING  NOYES        NONE       NONE       NONE       NO
SECURE_TICKETS   SCRNIMG      STS_OPEN     NOYES        NONE       NONE       NONE       NO
TICKETS DOCUMENT     STS_OTHER    YES        NONONOLOB        YES
TICKETS DOCUMENT     STS_PENDING  YES        NONONOLOB        YES
TICKETS DOCUMENT     STS_CLOSED   YES        NONONOLOB        YES
TICKETS DOCUMENT     STS_OPEN     YES        NONONONOYES
TICKETS SCRNIMG      STS_PENDING  CACHEREADS NONOHIGH       NOYES
TICKETS SCRNIMG      STS_OPEN     CACHEREADS NONOMEDIUM     NOYES
TICKETS SCRNIMG      STS_CLOSED   CACHEREADS NONOHIGH       NOYES
TICKETS SCRNIMG      STS_OTHER    CACHEREADS NONOHIGH       NOYES1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.

最后，请记住任何对LOB属性进行修改都只会影响到新创建的LOB或新修改的LOB，例如，将列TRBTKT.SECURE_TICKETS.SCRNIMG的压缩方法从COMPRESS修改为NOCOMPRESS不会影响到现有的LOB条目。

【编辑推荐】

【责任编辑：彭凡 TEL：（010）68476606】