有效地从BasicFile移植到SecureFile
现在已经将这些表进行了分区，以便在数据仓库环境更有效地使用，我也已经考虑过如何将LOB从BasicFile格式快速有效地转换成对应的SecureFile格式，幸运的是，Oracle 11g提供了两个方法：分区交换和在线重定义。
1、分区交换
分区交换载入(Partition Exchange Load，即PDL)有时也叫做分区转动，包含创建一个非分区表，它的格式和内容完全匹配目标分区表想要的分区，一旦原始表创建好了，就可以开始可已经分区的表的目标分区进行交换了，这个方法确实提供了许多好处，换句话说，原始表的本地索引在交换期间得到维护，它可以并行执行，这个方法可以重复多次以填充每个分区，它的主要缺点是接收数据的分区在进行交换时必须离线。
2、在线重定义
Oracle推荐使用DBMS_REDEFINITION包对源表和目标表执行在线重定义，因为它实现了分区交换方法相同的目标。但它不需要在重定义过程中将目标离线。在线重定义可以在表级也可以在分区级执行，它也可以同时并行为多个分区执行，我喜欢在线重定义的特性是在这个过程成功执行完毕后，源和目标对象将对真正地在数据库数据目录中转换定义，这意味着任何引用了新对象名的PL/SQL对象都不需要进行任何修改。清单3显示了如何使用TRBTKT.TICKETS表作为源对象，使用TRBTKT.SECURE_TICKETS表作为目标对象进行在线重定义的。从BasicFile LOB转换到SecureFile LOB最后一个注意事项是：一旦转换完成，LOB就不能再降级回到BasicFile LOB了。
管理SecureFile元数据
这些新的SecureFile特性无疑扩展了LOB的能力，对于DBA而言，也需要注意几个在传统的BasicFile LOB上增加的额外属性，幸运的是，Oracle 11g提供了多个方法跟踪BasicFile和SecureFile LOB的元数据。
1、数据字典视图
Oracle 11g也更新了多个关于SecureFile信息的数据字典视图，下面列出这些做了变动的视图。
清单4显示了多条SQL*Plus查询，它们使用了这些数据字典视图返回关于SecureFile元数据的信息，报告1显示了从这些查询返回的结果。
清单4 查询BasicFile和SecureFile LOB的元数据
SET PAGESIZE 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 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的默认值 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设置 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元数据报告返回的结果
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 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的默认值 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设置 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元数据报告返回的结果 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 SYS_IL0000072118C00005$$ INDEX PARTITION ASSM SYS_IL_P177 BASICFILES SYS_IL0000072118C00006$$ INDEX PARTITION ASSM SYS_IL_P185 BASICFILES SYS_IL0000072118C00006$$ INDEX PARTITION ASSM SYS_IL_P188 BASICFILES SYS_IL0000072118C00006$$ INDEX PARTITION ASSM SYS_IL_P187 BASICFILES SYS_IL0000072118C00006$$ INDEX PARTITION ASSM SYS_IL_P186 BASICFILES SYS_IL0000072144C00005$$ INDEX PARTITION ASSM SYS_IL_P194 SECUREFILES SYS_IL0000072144C00005$$ INDEX PARTITION ASSM SYS_IL_P193 SECUREFILES SYS_IL0000072144C00005$$ INDEX PARTITION ASSM SYS_IL_P195 SECUREFILES SYS_IL0000072144C00005$$ INDEX PARTITION ASSM SYS_IL_P196 SECUREFILES SYS_IL0000072144C00006$$ INDEX PARTITION ASSM SYS_IL_P204 SECUREFILES SYS_IL0000072144C00006$$ INDEX PARTITION ASSM SYS_IL_P203 SECUREFILES SYS_IL0000072144C00006$$ INDEX PARTITION ASSM SYS_IL_P202 SECUREFILES SYS_IL0000072144C00006$$ INDEX PARTITION ASSM SYS_IL_P201 SECUREFILES SYS_LOB0000072118C00005$$ LOB PARTITION ASSM SYS_LOB_P173 BASICFILES SYS_LOB0000072118C00005$$ LOB PARTITION ASSM SYS_LOB_P176 BASICFILES SYS_LOB0000072118C00005$$ LOB PARTITION ASSM SYS_LOB_P175 BASICFILES SYS_LOB0000072118C00005$$ LOB PARTITION ASSM SYS_LOB_P174 BASICFILES SYS_LOB0000072118C00006$$ LOB PARTITION ASSM SYS_LOB_P184 BASICFILES SYS_LOB0000072118C00006$$ LOB PARTITION ASSM SYS_LOB_P183 BASICFILES SYS_LOB0000072118C00006$$ LOB PARTITION ASSM SYS_LOB_P181 BASICFILES SYS_LOB0000072118C00006$$ LOB PARTITION ASSM SYS_LOB_P182 BASICFILES SYS_LOB0000072144C00005$$ LOB PARTITION SECUREFILE SYS_LOB_P191 SECUREFILES SYS_LOB0000072144C00005$$ LOB PARTITION SECUREFILE SYS_LOB_P192 SECUREFILES SYS_LOB0000072144C00005$$ LOB PARTITION SECUREFILE SYS_LOB_P189 SECUREFILES SYS_LOB0000072144C00005$$ LOB PARTITION SECUREFILE SYS_LOB_P190 SECUREFILES SYS_LOB0000072144C00006$$ LOB PARTITION SECUREFILE SYS_LOB_P198 SECUREFILES SYS_LOB0000072144C00006$$ LOB PARTITION SECUREFILE SYS_LOB_P199 SECUREFILES SYS_LOB0000072144C00006$$ LOB PARTITION SECUREFILE SYS_LOB_P197 SECUREFILES SYS_LOB0000072144C00006$$ LOB PARTITION SECUREFILE SYS_LOB_P200 SECUREFILES TICKETS TABLE PARTITION ASSM STS_PENDING USERS TICKETS TABLE PARTITION ASSM STS_OTHER USERS TICKETS TABLE PARTITION ASSM STS_OPEN USERS TICKETS TABLE PARTITION ASSM STS_CLOSED USERS TICKETS_PK_IDX INDEX ASSM USERS BasicFile和SecureFile LOB元数据 (来自DBA_LOBS) Stored Encryp- Compre- DeDupli- Secure Parti- Table Column Segment Tablespace Logging Cacheing In Row tion ssion cation File? tioned ————– ———- ————————– ———— ——– ———- ——- ——- ——- ——– ——- ——- SECURE_TICKETS DOCUMENT SYS_LOB0000072118C00005$$ BASICFILES NONE NO YES NONE NONE NONE NO YES SECURE_TICKETS SCRNIMG SYS_LOB0000072118C00006$$ BASICFILES NONE NO YES 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 NONE NONE NONE SECURE_TICKETS SCRNIMG NO BASICFILES NO NONE NONE NONE TICKETS DOCUMENT YES SECUREFILES YES NO NO NO TICKETS SCRNIMG CACHEREADS SECUREFILES YES NO NO NO BasicFile和SecureFile LOB分区 (来自DBA_LOB_PARTITIONS) Stored in Stored DeDupli- Table Column Partition Cacheing In Row Encrypted Compressed cated SecureFile —- ———— ———— ———- ———- ———- ———- ———- ———- SECURE_TICKETS DOCUMENT STS_OTHER NO YES NONE NONE NONE NO SECURE_TICKETS DOCUMENT STS_CLOSED NO YES NONE NONE NONE NO SECURE_TICKETS DOCUMENT STS_OPEN NO YES NONE NONE NONE NO SECURE_TICKETS DOCUMENT STS_PENDING NO YES NONE NONE NONE NO SECURE_TICKETS SCRNIMG STS_OTHER NO YES NONE NONE NONE NO SECURE_TICKETS SCRNIMG STS_CLOSED NO YES NONE NONE NONE NO SECURE_TICKETS SCRNIMG STS_PENDING NO YES NONE NONE NONE NO SECURE_TICKETS SCRNIMG STS_OPEN NO YES NONE NONE NONE NO TICKETS DOCUMENT STS_OTHER YES NO NO HIGH LOB YES TICKETS DOCUMENT STS_PENDING YES NO NO NO NO YES TICKETS DOCUMENT STS_CLOSED YES NO NO MEDIUM LOB YES TICKETS DOCUMENT STS_OPEN YES NO NO NO NO YES TICKETS SCRNIMG STS_PENDING CACHEREADS NO NO MEDIUM LOB YES TICKETS SCRNIMG STS_OPEN CACHEREADS NO NO MEDIUM NO YES TICKETS SCRNIMG STS_CLOSED CACHEREADS NO NO HIGH LOB YES TICKETS SCRNIMG STS_OTHER CACHEREADS NO NO HIGH LOB YES |
我们一直都在努力坚持原创.......请不要一声不吭，就悄悄拿走。
我原创，你原创，我们的内容世界才会更加精彩！
【所有原创内容版权均属TechTarget，欢迎大家转发分享。但未经授权，严禁任何媒体（平面媒体、网络媒体、自媒体等）以及微信公众号复制、转载、摘编或以其他方式进行使用。】
微信公众号
TechTarget
官方微博
TechTarget中国
翻译
相关推荐
-
甲骨文自治数据库亮相 带来云计算新希望
早前甲骨文还不在云计算公司之列，而现在该公司正在迅速弥补其失去的时间。甲骨文的云计算核心是甲骨文自治数据库（O […]
-
2017年12月数据库流行度排行榜 定格岁末排名瞬间
数据库知识网站DB-engines最近更新的2017年12月份数据库流行度排名情况是否能提供更多的看点呢？TechTarget数据库网站将与您分享12月份的榜单排名情况，让我们拭目以待。
-
2017年11月数据库流行度排行榜 半数以上数据库积分减少
数据库知识网站DB-engines更新了2016年11月份的数据库流行度排行榜。TechTarget数据库网站将与您一同关注11月份的榜单排名情况。
-
控制合约 不再畏惧Oracle
许多公司都与Oracle有无限制授权协议，他们害怕离开这个协议，所以就证明他们在使用Oracle的软件，即使因为需求单独购买部分授权许可也可能总体是省钱的。