【转】如何从BasicFile迁移到SecureFile存储
Oracle 11g新的SecureFile存储特性扩展了大对象(LOB)的灵活性和容量，本文研究如何有效地从BasicFile迁移到SecureFile存储，如何测量SecureFile vs BasicFile LOB的效率，以及如何为SecureFile LOB使用不同的压缩和重复数据删除选项。
我创建了一个新表TRBTKT.SECURE_TICKETS重新定义了原始表TRBTKT.TICKETS的结构，将原始的BasicFile LOB映射到对应的SecureFile LOB，然后执行了一条INSERT INTO … SELECT FROM DML语句将原始表中的内容传输到新表中。
在一个相对简单的应用程序环境中，这种载入方式足以应付，但目前在数据仓库和商业智能应用程序中广泛地使用了LOB，特别是存储那些包含了复杂商务交易的XML文档和医院信息系统捕获到的医疗影像数据。
为了便于说明，我使用清单1中的代码重新创建了这两个表，我使用了列表分区对这两个表进行了分区，在清单2中，我使用更大的数据重新载入TRBTKT.TICKETS表。
清单1 使用列表分区重新创建这两个表以模拟数据仓库环境
1 -- 重新创建表TRBTKT.TICKETS，这次包括了一个STATUS列 2 CREATE TABLE trbtkt.tickets ( 3 tkt_id NUMBER 4 ,description VARCHAR2(30) 5 ,submit_dtm TIMESTAMP 6 ,status VARCHAR2(8) 7 ,document BLOB 8 ,scrnimg BLOB 9 ) 10 LOB(document) 11 STORE AS BASICFILE (TABLESPACE basicfiles) 12 ,LOB(scrnimg) 13 STORE AS BASICFILE (TABLESPACE basicfiles) 14 PARTITION BY LIST (status) ( 15 PARTITION sts_open 16 VALUES ('OPEN') 17 ,PARTITION sts_pending 18 VALUES ('PENDING') 19 ,PARTITION sts_closed 20 VALUES ('CLOSED') 21 ,PARTITION sts_other 22 VALUES (DEFAULT) 23 ) 24 ; 25 26 --注释 27 COMMENT ON TABLE trbtkt.tickets 28 IS 'Contains Trouble Ticket transaction data'; 29 COMMENT ON COLUMN trbtkt.tickets.tkt_id 30 IS 'Unique identifier for a Trouble Ticket'; 31 COMMENT ON COLUMN trbtkt.tickets.description 32 IS 'Trouble Ticket Description'; 33 COMMENT ON COLUMN trbtkt.tickets.submit_dtm 34 IS 'Trouble Ticket Submission Time Stamp'; 35 COMMENT ON COLUMN trbtkt.tickets.status 36 IS 'Trouble Ticket Status'; 37 COMMENT ON COLUMN trbtkt.tickets.document 38 IS 'Large Object (LOB) that contains a Microsoft Word document that provides detailed information about a Trouble Ticket'; 39 COMMENT ON COLUMN trbtkt.tickets.scrnimg 40 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'; 41 42 -- 创建索引和约束 43 CREATE UNIQUE INDEX trbtkt.tickets_pk_idx 44 ON trbtkt.tickets(tkt_id) 45 TABLESPACE users; 46 47 ALTER TABLE trbtkt.tickets 48 ADD CONSTRAINT tickets_pk 49 PRIMARY KEY (tkt_id); 50 51 ----- 52 -- 创建表TRBTKT.SECURE_TICKETS的一个改良版本，使用分区划分SecureFile LOB的存储，基于STATUS 的值设置合适的保留周期 53 DROP TABLE trbtkt.secure_tickets PURGE; 54 CREATE TABLE trbtkt.secure_tickets ( 55 tkt_id NUMBER 56 ,description VARCHAR2(30) 57 ,submit_dtm TIMESTAMP 58 ,status VARCHAR2(8) 59 ,document BLOB 60 ,scrnimg BLOB 61 ) 62 LOB(document) 63 STORE AS SECUREFILE ( 64 TABLESPACE securefiles 65 DISABLE STORAGE IN ROW 66 CACHE 67 ) 68 ,LOB(scrnimg) 69 STORE AS SECUREFILE ( 70 TABLESPACE securefiles 71 DISABLE STORAGE IN ROW 72 CACHE READS 73 ) 74 PARTITION BY LIST (status) ( 75 PARTITION sts_open 76 VALUES ('OPEN') 77 LOB (document) STORE AS SECUREFILE (KEEP_DUPLICATES NOCOMPRESS) 78 ,LOB (scrnimg) STORE AS SECUREFILE (KEEP_DUPLICATES COMPRESS) 79 ,PARTITION sts_pending 80 VALUES ('PENDING') 81 LOB (document) STORE AS SECUREFILE (KEEP_DUPLICATES NOCOMPRESS) 82 ,LOB (scrnimg) STORE AS SECUREFILE (DEDUPLICATE COMPRESS) 83 ,PARTITION sts_closed 84 VALUES ('CLOSED') 85 LOB (document) STORE AS SECUREFILE (DEDUPLICATE COMPRESS) 86 ,LOB (scrnimg) STORE AS SECUREFILE (DEDUPLICATE COMPRESS HIGH) 87 ,PARTITION sts_other 88 VALUES (DEFAULT) 89 LOB (document) STORE AS SECUREFILE (DEDUPLICATE COMPRESS HIGH) 90 ,LOB (scrnimg) STORE AS SECUREFILE (DEDUPLICATE COMPRESS HIGH) 91 ) 92 ; 93 94 --注释 95 COMMENT ON TABLE trbtkt.secure_tickets 96 IS 'Contains Trouble Ticket transaction data'; 97 COMMENT ON COLUMN trbtkt.secure_tickets.tkt_id 98 IS 'Unique identifier for a Trouble Ticket'; 99 COMMENT ON COLUMN trbtkt.secure_tickets.description 100 IS 'Trouble Ticket Description'; 101 COMMENT ON COLUMN trbtkt.secure_tickets.submit_dtm 102 IS 'Trouble Ticket Submission Time Stamp'; 103 COMMENT ON COLUMN trbtkt.secure_tickets.status 104 IS 'Trouble Ticket Status'; 105 COMMENT ON COLUMN trbtkt.secure_tickets.document 106 IS 'Large Object (LOB) that contains a Microsoft Word document that provides detailed information about a Trouble Ticket'; 107 COMMENT ON COLUMN trbtkt.secure_tickets.scrnimg 108 IS 'Large Object (LOB) that contains a screen print or other image that helps a problem solver identify how to act on a Trouble Ticket';
清单2 使用附加数据重新载入表TRBTKT.TICKETS
1 SET SERVEROUTPUT ON 2 TRUNCATE TABLE trbtkt.tickets; 3 4 BEGIN 5 6 trbtkt.pkg_securefiles.AddTroubleTicket ( 7 tkt_id => 101 8 ,description => 'Trouble Ticket 101' 9 ,submit_dts => '2008-12-31 23:45:00' 10 ,status => 'OPEN' 11 ,docFileName => 'New_101.doc' 12 ,imgFileName => 'DBRIssues.jpg' 13 ); 14 15 trbtkt.pkg_securefiles.AddTroubleTicket ( 16 tkt_id => 102 17 ,description => 'Trouble Ticket 102' 18 ,submit_dts => '2009-01-04 00:00:00' 19 ,status => 'OPEN' 20 ,docFileName => 'New_102.doc' 21 ,imgFileName => 'Unresolved.jpg' 22 ); 23 24 trbtkt.pkg_securefiles.AddTroubleTicket ( 25 tkt_id => 103 26 ,description => 'Trouble Ticket 103' 27 ,submit_dts => '2009-01-02 00:00:00' 28 ,status => 'OPEN' 29 ,docFileName => 'New_103.doc' 30 ,imgFileName => 'Unresolved.jpg' 31 ); 32 33 trbtkt.pkg_securefiles.AddTroubleTicket ( 34 tkt_id => 104 35 ,description => 'Trouble Ticket 104' 36 ,submit_dts => '2009-01-14 12:30:00' 37 ,status => 'OPEN' 38 ,docFileName => 'New_104.doc' 39 ,imgFileName => 'DBRIssues.jpg' 40 ); 41 42 trbtkt.pkg_securefiles.AddTroubleTicket ( 43 tkt_id => 105 44 ,description => 'Trouble Ticket 105' 45 ,submit_dts => '2009-01-09 00:00:00' 46 ,status => 'OPEN' 47 ,docFileName => 'New_105.doc' 48 ,imgFileName => 'Unresolved.jpg' 49 ); 50 51 trbtkt.pkg_securefiles.AddTroubleTicket ( 52 tkt_id => 106 53 ,description => 'Trouble Ticket 106' 54 ,submit_dts => '2009-01-11 00:00:00' 55 ,status => 'OPEN' 56 ,docFileName => 'New_106.doc' 57 ,imgFileName => 'Unresolved.jpg' 58 ); 59 60 trbtkt.pkg_securefiles.AddTroubleTicket ( 61 tkt_id => 107 62 ,description => 'Trouble Ticket 107' 63 ,submit_dts => '2009-01-16 00:00:00' 64 ,status => 'OPEN' 65 ,docFileName => 'New_107.doc' 66 ,imgFileName => 'DBRIssues.jpg' 67 ); 68 69 trbtkt.pkg_securefiles.AddTroubleTicket ( 70 tkt_id => 108 71 ,description => 'Trouble Ticket 108' 72 ,submit_dts => '2009-01-12 00:00:00' 73 ,status => 'OPEN' 74 ,docFileName => 'New_108.doc' 75 ,imgFileName => 'Unresolved.jpg' 76 ); 77 78 trbtkt.pkg_securefiles.AddTroubleTicket ( 79 tkt_id => 109 80 ,description => 'Trouble Ticket 109' 81 ,submit_dts => '2009-01-02 00:00:00' 82 ,status => 'OPEN' 83 ,docFileName => 'New_109.doc' 84 ,imgFileName => 'Unresolved.jpg' 85 ); 86 87 trbtkt.pkg_securefiles.AddTroubleTicket ( 88 tkt_id => 110 89 ,description => 'Trouble Ticket 110' 90 ,submit_dts => '2009-01-14 12:45:00' 91 ,status => 'OPEN' 92 ,docFileName => 'New_110.doc' 93 ,imgFileName => 'DBRIssues.jpg' 94 ); 95 96 trbtkt.pkg_securefiles.AddTroubleTicket ( 97 tkt_id => 201 98 ,description => 'Trouble Ticket 201' 99 ,submit_dts => '2008-12-31 23:45:00' 100 ,status => 'PENDING' 101 ,docFileName => 'New_101.doc' 102 ,imgFileName => 'DBRIssues.jpg' 103 ); 104 105 trbtkt.pkg_securefiles.AddTroubleTicket ( 106 tkt_id => 202 107 ,description => 'Trouble Ticket 202' 108 ,submit_dts => '2009-01-04 00:00:00' 109 ,status => 'OPEN' 110 ,docFileName => 'New_102.doc' 111 ,imgFileName => 'Unresolved.jpg' 112 ); 113 114 trbtkt.pkg_securefiles.AddTroubleTicket ( 115 tkt_id => 203 116 ,description => 'Trouble Ticket 203' 117 ,submit_dts => '2009-01-02 00:00:00' 118 ,status => 'CLOSED' 119 ,docFileName => 'New_103.doc' 120 ,imgFileName => 'Unresolved.jpg' 121 ); 122 123 trbtkt.pkg_securefiles.AddTroubleTicket ( 124 tkt_id => 204 125 ,description => 'Trouble Ticket 204' 126 ,submit_dts => '2009-01-14 12:30:00' 127 ,status => 'OPEN' 128 ,docFileName => 'New_104.doc' 129 ,imgFileName => 'DBRIssues.jpg' 130 ); 131 132 trbtkt.pkg_securefiles.AddTroubleTicket ( 133 tkt_id => 205 134 ,description => 'Trouble Ticket 205' 135 ,submit_dts => '2009-01-09 00:00:00' 136 ,status => 'OPEN' 137 ,docFileName => 'New_105.doc' 138 ,imgFileName => 'Unresolved.jpg' 139 ); 140 141 trbtkt.pkg_securefiles.AddTroubleTicket ( 142 tkt_id => 206 143 ,description => 'Trouble Ticket 206' 144 ,submit_dts => '2009-01-11 00:00:00' 145 ,status => 'PENDING' 146 ,docFileName => 'New_106.doc' 147 ,imgFileName => 'Unresolved.jpg' 148 ); 149 150 trbtkt.pkg_securefiles.AddTroubleTicket ( 151 tkt_id => 207 152 ,description => 'Trouble Ticket 207' 153 ,submit_dts => '2009-01-16 00:00:00' 154 ,status => 'OPEN' 155 ,docFileName => 'New_107.doc' 156 ,imgFileName => 'DBRIssues.jpg' 157 ); 158 159 trbtkt.pkg_securefiles.AddTroubleTicket ( 160 tkt_id => 208 161 ,description => 'Trouble Ticket 208' 162 ,submit_dts => '2009-01-12 00:00:00' 163 ,status => 'OPEN' 164 ,docFileName => 'New_108.doc' 165 ,imgFileName => 'Unresolved.jpg' 166 ); 167 168 trbtkt.pkg_securefiles.AddTroubleTicket ( 169 tkt_id => 209 170 ,description => 'Trouble Ticket 209' 171 ,submit_dts => '2009-01-02 00:00:00' 172 ,status => 'PENDING' 173 ,docFileName => 'New_109.doc' 174 ,imgFileName => 'Unresolved.jpg' 175 ); 176 177 trbtkt.pkg_securefiles.AddTroubleTicket ( 178 tkt_id => 210 179 ,description => 'Trouble Ticket 210' 180 ,submit_dts => '2009-01-14 12:45:00' 181 ,status => 'OPEN' 182 ,docFileName => 'New_110.doc' 183 ,imgFileName => 'DBRIssues.jpg' 184 ); 185 186 trbtkt.pkg_securefiles.AddTroubleTicket ( 187 tkt_id => 301 188 ,description => 'Trouble Ticket 301' 189 ,submit_dts => '2008-12-31 23:45:00' 190 ,status => 'CLOSED' 191 ,docFileName => 'New_101.doc' 192 ,imgFileName => 'DBRIssues.jpg' 193 ); 194 195 trbtkt.pkg_securefiles.AddTroubleTicket ( 196 tkt_id => 302 197 ,description => 'Trouble Ticket 302' 198 ,submit_dts => '2009-01-04 00:00:00' 199 ,status => 'OPEN' 200 ,docFileName => 'New_102.doc' 201 ,imgFileName => 'Unresolved.jpg' 202 ); 203 204 trbtkt.pkg_securefiles.AddTroubleTicket ( 205 tkt_id => 303 206 ,description => 'Trouble Ticket 303' 207 ,submit_dts => '2009-01-02 00:00:00' 208 ,status => 'OPEN' 209 ,docFileName => 'New_103.doc' 210 ,imgFileName => 'Unresolved.jpg' 211 ); 212 213 trbtkt.pkg_securefiles.AddTroubleTicket ( 214 tkt_id => 304 215 ,description => 'Trouble Ticket 304' 216 ,submit_dts => '2009-01-14 12:30:00' 217 ,status => 'CLOSED' 218 ,docFileName => 'New_104.doc' 219 ,imgFileName => 'DBRIssues.jpg' 220 ); 221 222 trbtkt.pkg_securefiles.AddTroubleTicket ( 223 tkt_id => 305 224 ,description => 'Trouble Ticket 305' 225 ,submit_dts => '2009-01-09 00:00:00' 226 ,status => 'PENDING' 227 ,docFileName => 'New_105.doc' 228 ,imgFileName => 'Unresolved.jpg' 229 ); 230 231 trbtkt.pkg_securefiles.AddTroubleTicket ( 232 tkt_id => 306 233 ,description => 'Trouble Ticket 306' 234 ,submit_dts => '2009-01-11 00:00:00' 235 ,status => 'CLOSED' 236 ,docFileName => 'New_106.doc' 237 ,imgFileName => 'Unresolved.jpg' 238 ); 239 240 trbtkt.pkg_securefiles.AddTroubleTicket ( 241 tkt_id => 307 242 ,description => 'Trouble Ticket 307' 243 ,submit_dts => '2009-01-16 00:00:00' 244 ,status => 'OPEN' 245 ,docFileName => 'New_107.doc' 246 ,imgFileName => 'DBRIssues.jpg' 247 ); 248 249 trbtkt.pkg_securefiles.AddTroubleTicket ( 250 tkt_id => 308 251 ,description => 'Trouble Ticket 308' 252 ,submit_dts => '2009-01-12 00:00:00' 253 ,status => 'OPEN' 254 ,docFileName => 'New_108.doc' 255 ,imgFileName => 'Unresolved.jpg' 256 ); 257 258 trbtkt.pkg_securefiles.AddTroubleTicket ( 259 tkt_id => 309 260 ,description => 'Trouble Ticket 309' 261 ,submit_dts => '2009-01-02 00:00:00' 262 ,status => 'CLOSED' 263 ,docFileName => 'New_109.doc' 264 ,imgFileName => 'Unresolved.jpg' 265 ); 266 267 trbtkt.pkg_securefiles.AddTroubleTicket ( 268 tkt_id => 310 269 ,description => 'Trouble Ticket 310' 270 ,submit_dts => '2009-01-14 12:45:00' 271 ,status => 'CLOSED' 272 ,docFileName => 'New_110.doc' 273 ,imgFileName => 'DBRIssues.jpg' 274 ); 275 276 COMMIT; 277 278 END; 279 / 280 -- 收集优化器统计信息 281 BEGIN 282 DBMS_STATS.GATHER_SCHEMA_STATS(ownname => 'TRBTKT', CASCADE => TRUE); 283 END; 284 /
有效地从BasicFile移植到SecureFile
现在已经将这些表进行了分区，以便在数据仓库环境更有效地使用，我也已经考虑过如何将LOB从BasicFile格式快速有效地转换成对应的SecureFile格式，幸运的是，Oracle 11g提供了两个方法：分区交换和在线重定义。
1、分区交换
分区交换载入(Partition Exchange Load，即PDL)有时也叫做分区转动，包含创建一个非分区表，它的格式和内容完全匹配目标分区表想要的分区，一旦原始表创建好了，就可以开始可已经分 区的表的目标分区进行交换了，这个方法确实提供了许多好处，换句话说，原始表的本地索引在交换期间得到维护，它可以并行执行，这个方法可以重复多次以填充 每个分区，它的主要缺点是接收数据的分区在进行交换时必须离线。
2、在线重定义
Oracle推荐使用 DBMS_REDEFINITION包对源表和目标表执行在线重定义，因为它实现了分区交换方法相同的目标。但它不需要在重定义过程中将目标离线。在线重 定义可以在表级也可以在分区级执行，它也可以同时并行为多个分区执行，我喜欢在线重定义的特性是在这个过程成功执行完毕后，源和目标对象将对真正地在数据 库数据目录中转换定义，这意味着任何引用了新对象名的PL/SQL对象都不需要进行任何修改。清单3显示了如何使用TRBTKT.TICKETS表作为源 对象，使用TRBTKT.SECURE_TICKETS表作为目标对象进行在线重定义的。从BasicFile LOB转换到SecureFile LOB最后一个注意事项是：一旦转换完成，LOB就不能再降级回到BasicFile LOB了。
管理SecureFile元数据
这些新的SecureFile特性无疑扩展了LOB的能力，对于DBA而言，也需要注意几个在传统的BasicFile LOB上增加的额外属性，幸运的是，Oracle 11g提供了多个方法跟踪BasicFile和SecureFile LOB的元数据。
1、数据字典视图
Oracle 11g也更新了多个关于SecureFile信息的数据字典视图，下面列出这些做了变动的视图。
清单4显示了多条SQL*Plus查询，它们使用了这些数据字典视图返回关于SecureFile元数据的信息，报告1显示了从这些查询返回的结果。
清单4 查询BasicFile和SecureFile LOB的元数据
1 SET PAGESIZE 1000 2 SET LINESIZE 140 3 -- 视图: DBA_SEGMENTS 4 --显示关于BasicFile 和SecureFile 段的元数据 5 TTITLE 'LOB Segment Information|(from DBA_SEGMENTS)' 6 COL segment_name FORMAT A30 HEADING 'Segment Name' 7 COL segment_type FORMAT A20 HEADING 'Segment|Type' 8 COL segment_subtype FORMAT A20 HEADING 'Segment|SubType' 9 COL partition_name FORMAT A12 HEADING 'Partition|Name' 10 COL tablespace_name FORMAT A12 HEADING 'Tablespace' 11 SELECT 12 segment_name 13 ,segment_type 14 ,segment_subtype 15 ,partition_name 16 ,tablespace_name 17 FROM dba_segments 18 WHERE owner ='TRBTKT' 19 ORDERBY segment_name 20 ; 21 TTITLE OFF 22 -- 视图: DBA_LOBS 23 --显示关于BasicFile和SecureFile LOB的元数据 24 TTITLE 'BasicFile and SecureFile LOBs Metadata|(from DBA_LOBS)' 25 COL table_name FORMAT A14 HEADING 'Table' 26 COL segment_name FORMAT A26 HEADING 'Segment' 27 COL column_name FORMAT A10 HEADING 'Column' 28 COL tablespace_name FORMAT A12 HEADING 'Tablespace' 29 COL logging FORMAT A08 HEADING 'Logging' 30 COL cache FORMAT A10 HEADING 'Cacheing' 31 COL in_row FORMAT A07 HEADING 'Stored|In Row' 32 COL encrypt FORMAT A07 HEADING 'Encryp-|tion' 33 COL compression FORMAT A07 HEADING 'Compre-|ssion' 34 COL deduplication FORMAT A08 HEADING 'DeDupli-|cation' 35 COL securefile FORMAT A07 HEADING 'Secure|File?' 36 COL partitioned FORMAT A07 HEADING 'Parti-|tioned' 37 SELECT 38 table_name 39 ,column_name 40 ,segment_name 41 ,tablespace_name 42 ,logging 43 ,cache 44 ,in_row 45 ,encrypt 46 ,compression 47 ,deduplication 48 ,securefile 49 ,partitioned 50 FROM dba_lobs 51 WHERE owner ='TRBTKT' 52 ORDERBY table_name, column_name 53 ; 54 TTITLE OFF 55 --视图: DBA_PART_LOBS 56 --显示BasicFile和SecureFile LOB的默认值 57 TTITLE 'BasicFile and SecureFile Partitioned LOB Default Settings|(from DBA_PART_LOBS)' 58 COL table_name FORMAT A20 HEADING 'Table' 59 COL column_name FORMAT A12 HEADING 'Column' 60 COL def_cache FORMAT A12 HEADING 'Cached' 61 COL def_tablespace_name FORMAT A12 HEADING 'Tablespace' 62 COL def_securefile FORMAT A12 HEADING 'SecureFile' 63 COL def_encrypt FORMAT A12 HEADING 'Encrypted' 64 COL def_compress FORMAT A12 HEADING 'Compressed' 65 COL def_deduplicate FORMAT A12 HEADING 'DeDuplicated' 66 SELECT 67 table_name 68 ,column_name 69 ,def_cache 70 ,def_tablespace_name 71 ,def_securefile 72 ,def_compress 73 ,def_deduplicate 74 ,def_encrypt 75 FROM dba_part_lobs 76 WHERE table_owner ='TRBTKT' 77 ORDERBY table_name, column_name 78 ; 79 TTITLE OFF 80 --视图: DBA_LOB_PARTITIONS 81 --在独立的LOB级描述BasicFile和SecureFile设置 82 TTITLE 'BasicFile and SecureFile LOB Partitions|(from DBA_LOB_PARTITIONS)' 83 COL table_name FORMAT A16 HEADING 'Table' 84 COL column_name FORMAT A12 HEADING 'Column' 85 COL partition_name FORMAT A12 HEADING 'Stored in|Partition' 86 COL cache FORMAT A10 HEADING 'Cacheing' 87 COL in_row FORMAT A10 HEADING 'Stored|In Row' 88 COL encrypt FORMAT A10 HEADING 'Encrypted' 89 COL compression FORMAT A10 HEADING 'Compressed' 90 COL deduplication FORMAT A10 HEADING 'DeDupli-|cated' 91 COL securefile FORMAT A10 HEADING 'SecureFile?' 92 SELECT 93 table_name 94 ,column_name 95 ,partition_name 96 ,cache 97 ,in_row 98 ,encrypt 99 ,compression 100 ,deduplication 101 ,securefile 102 FROM dba_lob_partitions 103 WHERE table_owner ='TRBTKT' 104 ORDERBY table_name, column_name 105 ; 106 TTITLE OFF
报告1 从BasicFIle和SecureFile LOB元数据报告返回的结果
1 SET PAGESIZE 1000 2 SET LINESIZE 140 3 -- 视图: DBA_SEGMENTS 4 --显示关于BasicFile 和SecureFile 段的元数据 5 TTITLE 'LOB Segment Information|(from DBA_SEGMENTS)' 6 COL segment_name FORMAT A30 HEADING 'Segment Name' 7 COL segment_type FORMAT A20 HEADING 'Segment|Type' 8 COL segment_subtype FORMAT A20 HEADING 'Segment|SubType' 9 COL partition_name FORMAT A12 HEADING 'Partition|Name' 10 COL tablespace_name FORMAT A12 HEADING 'Tablespace' 11 SELECT 12 segment_name 13 ,segment_type 14 ,segment_subtype 15 ,partition_name 16 ,tablespace_name 17 FROM dba_segments 18 WHERE owner ='TRBTKT' 19 ORDERBY segment_name 20 ; 21 TTITLE OFF 22 -- 视图: DBA_LOBS 23 --显示关于BasicFile和SecureFile LOB的元数据 24 TTITLE 'BasicFile and SecureFile LOBs Metadata|(from DBA_LOBS)' 25 COL table_name FORMAT A14 HEADING 'Table' 26 COL segment_name FORMAT A26 HEADING 'Segment' 27 COL column_name FORMAT A10 HEADING 'Column' 28 COL tablespace_name FORMAT A12 HEADING 'Tablespace' 29 COL logging FORMAT A08 HEADING 'Logging' 30 COL cache FORMAT A10 HEADING 'Cacheing' 31 COL in_row FORMAT A07 HEADING 'Stored|In Row' 32 COL encrypt FORMAT A07 HEADING 'Encryp-|tion' 33 COL compression FORMAT A07 HEADING 'Compre-|ssion' 34 COL deduplication FORMAT A08 HEADING 'DeDupli-|cation' 35 COL securefile FORMAT A07 HEADING 'Secure|File?' 36 COL partitioned FORMAT A07 HEADING 'Parti-|tioned' 37 SELECT 38 table_name 39 ,column_name 40 ,segment_name 41 ,tablespace_name 42 ,logging 43 ,cache 44 ,in_row 45 ,encrypt 46 ,compression 47 ,deduplication 48 ,securefile 49 ,partitioned 50 FROM dba_lobs 51 WHERE owner ='TRBTKT' 52 ORDERBY table_name, column_name 53 ; 54 TTITLE OFF 55 --视图: DBA_PART_LOBS 56 --显示BasicFile和SecureFile LOB的默认值 57 TTITLE 'BasicFile and SecureFile Partitioned LOB Default Settings|(from DBA_PART_LOBS)' 58 COL table_name FORMAT A20 HEADING 'Table' 59 COL column_name FORMAT A12 HEADING 'Column' 60 COL def_cache FORMAT A12 HEADING 'Cached' 61 COL def_tablespace_name FORMAT A12 HEADING 'Tablespace' 62 COL def_securefile FORMAT A12 HEADING 'SecureFile' 63 COL def_encrypt FORMAT A12 HEADING 'Encrypted' 64 COL def_compress FORMAT A12 HEADING 'Compressed' 65 COL def_deduplicate FORMAT A12 HEADING 'DeDuplicated' 66 SELECT 67 table_name 68 ,column_name 69 ,def_cache 70 ,def_tablespace_name 71 ,def_securefile 72 ,def_compress 73 ,def_deduplicate 74 ,def_encrypt 75 FROM dba_part_lobs 76 WHERE table_owner ='TRBTKT' 77 ORDERBY table_name, column_name 78 ; 79 TTITLE OFF 80 --视图: DBA_LOB_PARTITIONS 81 --在独立的LOB级描述BasicFile和SecureFile设置 82 TTITLE 'BasicFile and SecureFile LOB Partitions|(from DBA_LOB_PARTITIONS)' 83 COL table_name FORMAT A16 HEADING 'Table' 84 COL column_name FORMAT A12 HEADING 'Column' 85 COL partition_name FORMAT A12 HEADING 'Stored in|Partition' 86 COL cache FORMAT A10 HEADING 'Cacheing' 87 COL in_row FORMAT A10 HEADING 'Stored|In Row' 88 COL encrypt FORMAT A10 HEADING 'Encrypted' 89 COL compression FORMAT A10 HEADING 'Compressed' 90 COL deduplication FORMAT A10 HEADING 'DeDupli-|cated' 91 COL securefile FORMAT A10 HEADING 'SecureFile?' 92 SELECT 93 table_name 94 ,column_name 95 ,partition_name 96 ,cache 97 ,in_row 98 ,encrypt 99 ,compression 100 ,deduplication 101 ,securefile 102 FROM dba_lob_partitions 103 WHERE table_owner ='TRBTKT' 104 ORDERBY table_name, column_name 105 ; 106 TTITLE OFF 107 报告1 从BasicFIle和SecureFile LOB元数据报告返回的结果 108 LOB段信息 109 (来自DBA_SEGMENTS) 110 111 Segment Segment Partition 112 Segment Name Type SubType Name Tablespace 113 ------------------------- -------------------- -------------------- ------------ ------------ 114 SECURE_TICKETS TABLE PARTITION ASSM STS_PENDING USERS 115 SECURE_TICKETS TABLE PARTITION ASSM STS_CLOSED USERS 116 SECURE_TICKETS TABLE PARTITION ASSM STS_OPEN USERS 117 SECURE_TICKETS TABLE PARTITION ASSM STS_OTHER USERS 118 SYS_IL0000072118C00005$$ INDEX PARTITION ASSM SYS_IL_P180 BASICFILES 119 SYS_IL0000072118C00005$$ INDEX PARTITION ASSM SYS_IL_P179 BASICFILES 120 SYS_IL0000072118C00005$$ INDEX PARTITION ASSM SYS_IL_P178 BASICFILES 121 SYS_IL0000072118C00005$$ INDEX PARTITION ASSM SYS_IL_P177 BASICFILES 122 SYS_IL0000072118C00006$$ INDEX PARTITION ASSM SYS_IL_P185 BASICFILES 123 SYS_IL0000072118C00006$$ INDEX PARTITION ASSM SYS_IL_P188 BASICFILES 124 SYS_IL0000072118C00006$$ INDEX PARTITION ASSM SYS_IL_P187 BASICFILES 125 SYS_IL0000072118C00006$$ INDEX PARTITION ASSM SYS_IL_P186 BASICFILES 126 SYS_IL0000072144C00005$$ INDEX PARTITION ASSM SYS_IL_P194 SECUREFILES 127 SYS_IL0000072144C00005$$ INDEX PARTITION ASSM SYS_IL_P193 SECUREFILES 128 SYS_IL0000072144C00005$$ INDEX PARTITION ASSM SYS_IL_P195 SECUREFILES 129 SYS_IL0000072144C00005$$ INDEX PARTITION ASSM SYS_IL_P196 SECUREFILES 130 SYS_IL0000072144C00006$$ INDEX PARTITION ASSM SYS_IL_P204 SECUREFILES 131 SYS_IL0000072144C00006$$ INDEX PARTITION ASSM SYS_IL_P203 SECUREFILES 132 SYS_IL0000072144C00006$$ INDEX PARTITION ASSM SYS_IL_P202 SECUREFILES 133 SYS_IL0000072144C00006$$ INDEX PARTITION ASSM SYS_IL_P201 SECUREFILES 134 SYS_LOB0000072118C00005$$ LOB PARTITION ASSM SYS_LOB_P173 BASICFILES 135 SYS_LOB0000072118C00005$$ LOB PARTITION ASSM SYS_LOB_P176 BASICFILES 136 SYS_LOB0000072118C00005$$ LOB PARTITION ASSM SYS_LOB_P175 BASICFILES 137 SYS_LOB0000072118C00005$$ LOB PARTITION ASSM SYS_LOB_P174 BASICFILES 138 SYS_LOB0000072118C00006$$ LOB PARTITION ASSM SYS_LOB_P184 BASICFILES 139 SYS_LOB0000072118C00006$$ LOB PARTITION ASSM SYS_LOB_P183 BASICFILES 140 SYS_LOB0000072118C00006$$ LOB PARTITION ASSM SYS_LOB_P181 BASICFILES 141 SYS_LOB0000072118C00006$$ LOB PARTITION ASSM SYS_LOB_P182 BASICFILES 142 SYS_LOB0000072144C00005$$ LOB PARTITION SECUREFILE SYS_LOB_P191 SECUREFILES 143 SYS_LOB0000072144C00005$$ LOB PARTITION SECUREFILE SYS_LOB_P192 SECUREFILES 144 SYS_LOB0000072144C00005$$ LOB PARTITION SECUREFILE SYS_LOB_P189 SECUREFILES 145 SYS_LOB0000072144C00005$$ LOB PARTITION SECUREFILE SYS_LOB_P190 SECUREFILES 146 SYS_LOB0000072144C00006$$ LOB PARTITION SECUREFILE SYS_LOB_P198 SECUREFILES 147 SYS_LOB0000072144C00006$$ LOB PARTITION SECUREFILE SYS_LOB_P199 SECUREFILES 148 SYS_LOB0000072144C00006$$ LOB PARTITION SECUREFILE SYS_LOB_P197 SECUREFILES 149 SYS_LOB0000072144C00006$$ LOB PARTITION SECUREFILE SYS_LOB_P200 SECUREFILES 150 TICKETS TABLE PARTITION ASSM STS_PENDING USERS 151 TICKETS TABLE PARTITION ASSM STS_OTHER USERS 152 TICKETS TABLE PARTITION ASSM STS_OPEN USERS 153 TICKETS TABLE PARTITION ASSM STS_CLOSED USERS 154 TICKETS_PK_IDX INDEX ASSM USERS 155 156 BasicFile和SecureFile LOB元数据 157 (来自DBA_LOBS) 158 159 Stored Encryp- Compre- DeDupli- Secure Parti- 160 Table Column Segment Tablespace Logging Cacheing In Row tion ssion cation File? tioned 161 -------------- ---------- -------------------------- ------------ -------- ---------- ------- ------- ------- -------- ------- ------- 162 SECURE_TICKETS DOCUMENT SYS_LOB0000072118C00005$$ BASICFILES NONE NO YES NONE NONE NONE NO YES 163 SECURE_TICKETS SCRNIMG SYS_LOB0000072118C00006$$ BASICFILES NONE NO YES NONE NONE NONE NO YES 164 TICKETS DOCUMENT SYS_LOB0000072144C00005$$ SECUREFILES NONE YES NO NO NO NO YES YES 165 TICKETS SCRNIMG SYS_LOB0000072144C00006$$ SECUREFILES NONE CACHEREADS NO NO NO NO YES YES 166 167 BasicFile和SecureFile分区LOB默认设置 168 (来自DBA_PART_LOBS) 169 170 Table Column Cached Tablespace SecureFile Compressed DeDuplicated Encrypted 171 ----- ------------ ------------ ------------ ------------ ------------ ------------ ------------ 172 SECURE_TICKETS DOCUMENT NO BASICFILES NO NONE NONE NONE 173 SECURE_TICKETS SCRNIMG NO BASICFILES NO NONE NONE NONE 174 TICKETS DOCUMENT YES SECUREFILES YES NO NO NO 175 TICKETS SCRNIMG CACHEREADS SECUREFILES YES NO NO NO 176 177 BasicFile和SecureFile LOB分区 178 (来自DBA_LOB_PARTITIONS) 179 180 Stored in Stored DeDupli- 181 Table Column Partition Cacheing In Row Encrypted Compressed cated SecureFile 182 ---- ------------ ------------ ---------- ---------- ---------- ---------- ---------- ---------- 183 SECURE_TICKETS DOCUMENT STS_OTHER NO YES NONE NONE NONE NO 184 SECURE_TICKETS DOCUMENT STS_CLOSED NO YES NONE NONE NONE NO 185 SECURE_TICKETS DOCUMENT STS_OPEN NO YES NONE NONE NONE NO 186 SECURE_TICKETS DOCUMENT STS_PENDING NO YES NONE NONE NONE NO 187 SECURE_TICKETS SCRNIMG STS_OTHER NO YES NONE NONE NONE NO 188 SECURE_TICKETS SCRNIMG STS_CLOSED NO YES NONE NONE NONE NO 189 SECURE_TICKETS SCRNIMG STS_PENDING NO YES NONE NONE NONE NO 190 SECURE_TICKETS SCRNIMG STS_OPEN NO YES NONE NONE NONE NO 191 TICKETS DOCUMENT STS_OTHER YES NO NO HIGH LOB YES 192 TICKETS DOCUMENT STS_PENDING YES NO NO NO NO YES 193 TICKETS DOCUMENT STS_CLOSED YES NO NO MEDIUM LOB YES 194 TICKETS DOCUMENT STS_OPEN YES NO NO NO NO YES 195 TICKETS SCRNIMG STS_PENDING CACHEREADS NO NO MEDIUM LOB YES 196 TICKETS SCRNIMG STS_OPEN CACHEREADS NO NO MEDIUM NO YES 197 TICKETS SCRNIMG STS_CLOSED CACHEREADS NO NO HIGH LOB YES 198 TICKETS SCRNIMG STS_OTHER CACHEREADS NO NO HIGH LOB YES
2、DBMS_SPACE
这是另一个Oracle古老支持包，它的SPACE_USAGE存储过 程提供关于BasicFile和SecureFile LOB的空间利用率，我在TRBTKT.PKG_SECUREFILES包中引入了两个存储过程：CALC_SPACE_BASICFILES和 CALC_SPACE_SECUREFILES。但遗憾的是，这个功能只能在启用了自动段空间管理(ASSM)的表空间上使用，而且，它也不会考虑任何 BasicFile LOB块的空间利用率，在清单5中，我对表TRBTKT.TICKETS和TRBTKT.SECURE_TICKETS分别调用了这些存储过程，并返回了 相应的输出。
清单5 确定BasicFile和SecureFile LOB的空间利用率
1 SET SERVEROUTPUT ON 2 -- BasicFile存储利用率: 3 BEGIN 4 trbtkt.pkg_securefiles.calc_space_basicfiles ( 5 ownname =>'TRBTKT' 6 ,tabname =>'SECURE_TICKETS' 7 ,colname =>'DOCUMENT' 8 ,partname =>'STS_OPEN' 9 ); 10 trbtkt.pkg_securefiles.calc_space_basicfiles ( 11 ownname =>'TRBTKT' 12 ,tabname =>'SECURE_TICKETS' 13 ,colname =>'DOCUMENT' 14 ,partname =>'STS_PENDING' 15 ); 16 trbtkt.pkg_securefiles.calc_space_basicfiles ( 17 ownname =>'TRBTKT' 18 ,tabname =>'SECURE_TICKETS' 19 ,colname =>'DOCUMENT' 20 ,partname =>'STS_CLOSED' 21 ); 22 trbtkt.pkg_securefiles.calc_space_basicfiles ( 23 ownname =>'TRBTKT' 24 ,tabname =>'SECURE_TICKETS' 25 ,colname =>'DOCUMENT' 26 ,partname =>'STS_OTHER' 27 ); 28 trbtkt.pkg_securefiles.calc_space_basicfiles ( 29 ownname =>'TRBTKT' 30 ,tabname =>'SECURE_TICKETS' 31 ,colname =>'SCRNIMG' 32 ,partname =>'STS_OPEN' 33 ); 34 trbtkt.pkg_securefiles.calc_space_basicfiles ( 35 ownname =>'TRBTKT' 36 ,tabname =>'SECURE_TICKETS' 37 ,colname =>'SCRNIMG' 38 ,partname =>'STS_PENDING' 39 ); 40 trbtkt.pkg_securefiles.calc_space_basicfiles ( 41 ownname =>'TRBTKT' 42 ,tabname =>'SECURE_TICKETS' 43 ,colname =>'SCRNIMG' 44 ,partname =>'STS_CLOSED' 45 ); 46 trbtkt.pkg_securefiles.calc_space_basicfiles ( 47 ownname =>'TRBTKT' 48 ,tabname =>'SECURE_TICKETS' 49 ,colname =>'SCRNIMG' 50 ,partname =>'STS_OTHER' 51 ); 52 END; 53 / 54 ============================================================ 55 Space Usage for BasicFile LOB TRBTKT.SECURE_TICKETS.DOCUMENT 56 Partition Name: STS_OPEN 57 ------------------------------------------------------------ 58 Full Blocks: 123 KB: .96 59 Unformatted Blocks: 379 KB: 2.96 60 Total Blocks: 123 Total KB: .96 61 ============================================================ 62 ============================================================ 63 Space Usage for BasicFile LOB TRBTKT.SECURE_TICKETS.DOCUMENT 64 Partition Name: STS_PENDING 65 ------------------------------------------------------------ 66 Full Blocks: 20 KB: .16 67 Unformatted Blocks: 482 KB: 3.77 68 Total Blocks: 20 Total KB: .16 69 ============================================================ 70 ============================================================ 71 Space Usage for BasicFile LOB TRBTKT.SECURE_TICKETS.DOCUMENT 72 Partition Name: STS_CLOSED 73 ------------------------------------------------------------ 74 Full Blocks: 37 KB: .29 75 Unformatted Blocks: 465 KB: 3.63 76 Total Blocks: 37 Total KB: .29 77 ============================================================ 78 ============================================================ 79 Space Usage for BasicFile LOB TRBTKT.SECURE_TICKETS.DOCUMENT 80 Partition Name: STS_OTHER 81 ------------------------------------------------------------ 82 Full Blocks: 0 KB: 0 83 Unformatted Blocks: 0 KB: 0 84 Total Blocks: 0 Total KB: 0 85 ============================================================ 86 ============================================================ 87 Space Usage for BasicFile LOB TRBTKT.SECURE_TICKETS.SCRNIMG 88 Partition Name: STS_OPEN 89 ------------------------------------------------------------ 90 Full Blocks: 420 KB: 3.28 91 Unformatted Blocks: 82 KB: .64 92 Total Blocks: 420 Total KB: 3.28 93 ============================================================ 94 ============================================================ 95 Space Usage for BasicFile LOB TRBTKT.SECURE_TICKETS.SCRNIMG 96 Partition Name: STS_PENDING 97 ------------------------------------------------------------ 98 Full Blocks: 66 KB: .52 99 Unformatted Blocks: 436 KB: 3.41 100 Total Blocks: 66 Total KB: .52 101 ============================================================ 102 ============================================================ 103 Space Usage for BasicFile LOB TRBTKT.SECURE_TICKETS.SCRNIMG 104 Partition Name: STS_CLOSED 105 ------------------------------------------------------------ 106 Full Blocks: 144 KB: 1.13 107 Unformatted Blocks: 358 KB: 2.8 108 Total Blocks: 144 Total KB: 1.13 109 ============================================================ 110 ============================================================ 111 Space Usage for BasicFile LOB TRBTKT.SECURE_TICKETS.SCRNIMG 112 Partition Name: STS_OTHER 113 ------------------------------------------------------------ 114 Full Blocks: 0 KB: 0 115 Unformatted Blocks: 0 KB: 0 116 Total Blocks: 0 Total KB: 0 117 ============================================================ 118 -- SecureFile存储利用率: 119 BEGIN 120 trbtkt.pkg_securefiles.calc_space_securefiles ( 121 ownname =>'TRBTKT' 122 ,tabname =>'TICKETS' 123 ,colname =>'DOCUMENT' 124 ,partname =>'STS_OPEN' 125 ); 126 trbtkt.pkg_securefiles.calc_space_securefiles ( 127 ownname =>'TRBTKT' 128 ,tabname =>'TICKETS' 129 ,colname =>'DOCUMENT' 130 ,partname =>'STS_PENDING' 131 ); 132 trbtkt.pkg_securefiles.calc_space_securefiles ( 133 ownname =>'TRBTKT' 134 ,tabname =>'TICKETS' 135 ,colname =>'DOCUMENT' 136 ,partname =>'STS_CLOSED' 137 ); 138 trbtkt.pkg_securefiles.calc_space_securefiles ( 139 ownname =>'TRBTKT' 140 ,tabname =>'TICKETS' 141 ,colname =>'DOCUMENT' 142 ,partname =>'STS_OTHER' 143 ); 144 trbtkt.pkg_securefiles.calc_space_securefiles ( 145 ownname =>'TRBTKT' 146 ,tabname =>'TICKETS' 147 ,colname =>'SCRNIMG' 148 ,partname =>'STS_OPEN' 149 ); 150 trbtkt.pkg_securefiles.calc_space_securefiles ( 151 ownname =>'TRBTKT' 152 ,tabname =>'TICKETS' 153 ,colname =>'SCRNIMG' 154 ,partname =>'STS_PENDING' 155 ); 156 trbtkt.pkg_securefiles.calc_space_securefiles ( 157 ownname =>'TRBTKT' 158 ,tabname =>'TICKETS' 159 ,colname =>'SCRNIMG' 160 ,partname =>'STS_CLOSED' 161 ); 162 trbtkt.pkg_securefiles.calc_space_securefiles ( 163 ownname =>'TRBTKT' 164 ,tabname =>'TICKETS' 165 ,colname =>'SCRNIMG' 166 ,partname =>'STS_OTHER' 167 ); 168 END; 169 / 170 171 ============================================================ 172 Space Usage for SecureFile LOB TRBTKT.TICKETS.DOCUMENT 173 Partition Name: STS_OPEN 174 ------------------------------------------------------------ 175 Segment Blocks: 1024 KB: 8192 176 Used Blocks: 124 KB: 992 177 Expired Blocks: 882 KB: 7056 178 Unexpired Blocks: 0 KB: 0 179 ============================================================ 180 ============================================================ 181 Space Usage for SecureFile LOB TRBTKT.TICKETS.DOCUMENT 182 Partition Name: STS_PENDING 183 ------------------------------------------------------------ 184 Segment Blocks: 1024 KB: 8192 185 Used Blocks: 21 KB: 168 186 Expired Blocks: 985 KB: 7880 187 Unexpired Blocks: 0 KB: 0 188 ============================================================ 189 ============================================================ 190 Space Usage for SecureFile LOB TRBTKT.TICKETS.DOCUMENT 191 Partition Name: STS_CLOSED 192 ------------------------------------------------------------ 193 Segment Blocks: 1024 KB: 8192 194 Used Blocks: 13 KB: 104 195 Expired Blocks: 993 KB: 7944 196 Unexpired Blocks: 0 KB: 0 197 ============================================================ 198 ============================================================ 199 Space Usage for SecureFile LOB TRBTKT.TICKETS.DOCUMENT 200 Partition Name: STS_OTHER 201 ------------------------------------------------------------ 202 Segment Blocks: 512 KB: 4096 203 Used Blocks: 501 KB: 4008 204 Expired Blocks: 0 KB: 0 205 Unexpired Blocks: 0 KB: 0 206 ============================================================ 207 ============================================================ 208 Space Usage for SecureFile LOB TRBTKT.TICKETS.SCRNIMG 209 Partition Name: STS_OPEN 210 ------------------------------------------------------------ 211 Segment Blocks: 2560 KB: 20480 212 Used Blocks: 405 KB: 3240 213 Expired Blocks: 2134 KB: 17072 214 Unexpired Blocks: 0 KB: 0 215 ============================================================ 216 ============================================================ 217 Space Usage for SecureFile LOB TRBTKT.TICKETS.SCRNIMG 218 Partition Name: STS_PENDING 219 ------------------------------------------------------------ 220 Segment Blocks: 1024 KB: 8192 221 Used Blocks: 62 KB: 496 222 Expired Blocks: 944 KB: 7552 223 Unexpired Blocks: 0 KB: 0 224 ============================================================ 225 ============================================================ 226 Space Usage for SecureFile LOB TRBTKT.TICKETS.SCRNIMG 227 Partition Name: STS_CLOSED 228 ------------------------------------------------------------ 229 Segment Blocks: 1024 KB: 8192 230 Used Blocks: 142 KB: 1136 231 Expired Blocks: 864 KB: 6912 232 Unexpired Blocks: 0 KB: 0 233 ============================================================ 234 ============================================================ 235 Space Usage for SecureFile LOB TRBTKT.TICKETS.SCRNIMG 236 Partition Name: STS_OTHER 237 ------------------------------------------------------------ 238 Segment Blocks: 512 KB: 4096 239 Used Blocks: 501 KB: 4008 240 Expired Blocks: 0 KB: 0 241 Unexpired Blocks: 0 KB: 0 242 ============================================================ 243 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属性
1 SQL>ALTERTABLE TRBTKT.TICKETS 2 SQL> MODIFY LOB(DOCUMENT) (NOCOMPRESS); 3 4 Table altered. 5 6 7 SQL>ALTERTABLE TRBTKT.TICKETS 8 SQL> MODIFY LOB(SCRNIMG) (KEEP_DUPLICATES); 9 10 Table altered. 11 12 SQL>ALTERTABLE TRBTKT.TICKETS 13 SQL> MODIFY PARTITION STS_PENDING LOB(SCRNIMG) (COMPRESS HIGH); 14 15 Table altered. 16 17 SQL>ALTERTABLE TRBTKT.TICKETS 18 SQL> MODIFY PARTITION STS_PENDING LOB(DOCUMENT) (DEDUPLICATE); 19 20 Table altered.
报告2 修改许多SecureFile LOB后查询DBA_LOB_PARTITIONS返回的结果
1 BasicFile and SecureFile LOB Partitions 2 (from DBA_LOB_PARTITIONS) 3 Stored in Stored DeDupli- 4 Table Column Partition Cacheing In Row Encrypted Compressed cated SecureFile 5 ---------------- ------------ ------------ ---------- ---------- ---------- ---------- -------- 6 SECURE_TICKETS DOCUMENT STS_OTHER NO YES NONE NONE NONE NO 7 SECURE_TICKETS DOCUMENT STS_CLOSED NO YES NONE NONE NONE NO 8 SECURE_TICKETS DOCUMENT STS_OPEN NO YES NONE NONE NONE NO 9 SECURE_TICKETS DOCUMENT STS_PENDING NO YES NONE NONE NONE NO 10 SECURE_TICKETS SCRNIMG STS_OTHER NO YES NONE NONE NONE NO 11 SECURE_TICKETS SCRNIMG STS_CLOSED NO YES NONE NONE NONE NO 12 SECURE_TICKETS SCRNIMG STS_PENDING NO YES NONE NONE NONE NO 13 SECURE_TICKETS SCRNIMG STS_OPEN NO YES NONE NONE NONE NO 14 TICKETS DOCUMENT STS_OTHER YES NO NO NO LOB YES 15 TICKETS DOCUMENT STS_PENDING YES NO NO NO LOB YES 16 TICKETS DOCUMENT STS_CLOSED YES NO NO NO LOB YES 17 TICKETS DOCUMENT STS_OPEN YES NO NO NO NO YES 18 TICKETS SCRNIMG STS_PENDING CACHEREADS NO NO HIGH NO YES 19 TICKETS SCRNIMG STS_OPEN CACHEREADS NO NO MEDIUM NO YES 20 TICKETS SCRNIMG STS_CLOSED CACHEREADS NO NO HIGH NO YES 21 TICKETS SCRNIMG STS_OTHER CACHEREADS NO NO HIGH NO YES
最后，请记住任何对LOB属性进行修改都只会影响到新创建的LOB或新修改的LOB，例如，将列TRBTKT.SECURE_TICKETS.SCRNIMG的压缩方法从COMPRESS修改为NOCOMPRESS不会影响到现有的LOB条目。
http://tech.it168.com/a2009/0416/272/000000272412.shtml
