当前位置：七道奇 → 文章资讯 → 数据防范 → Oracle防范
日期：2011-01-25 22:55:00  来源：本站整理

<b>Oracle中若何从BasicFile迁移到SecureFile-开辟技术</b>[Oracle防范]

赞助商链接



　　本文“<b>Oracle中若何从BasicFile迁移到SecureFile-开辟技术</b>[Oracle防范]”是由七道奇为您精心收集，来源于网络转载，文章版权归文章作者所有，本站不对其观点以及内容做任何评价，请读者自行判断，以下是其具体内容：

    我成立了一个新表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);

    --- 成立表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';

    清单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移植到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了.


　　以上是“<b>Oracle中若何从BasicFile迁移到SecureFile-开辟技术</b>[Oracle防范]”的内容，如果你对以上该文章内容感兴趣，你可以看看七道奇为您推荐以下文章：
  • <b>hosts是什么 hosts文件在什么位置 若何改正hosts</b>
  • <b>在 Windows 8 中手动安装语言包</b>
  • <b>五个常见 PHP数据库问题</b>
  • Windows中Alt键的12个高效快速的利用本领介绍
  • <b>MySQL ORDER BY 的实现解析</b>
  • <b>详解MySQL存储历程参数有三种范例(in、out、inout)</b>
  • <b>Win8系统恢复出来经典的开始菜单的办法</b>
  • <b>Win8系统花屏怎么办 Win8系统花屏的办理办法</b>
  • <b>Windows 7系统下无线网卡安装</b>
  • <b>为什么 Linux不需求碎片整理</b>
  • <b>Windows 8中删除账户的几种办法(图)</b>
  • <b>教你如安在win7下配置路由器</b>
    •
    本文地址： 与您的QQ/BBS好友分享!
    [] [返回上一页] [打 印]
    • 好的评价　如果您觉得此文章好，就请您
        0%(0)
    • 差的评价　如果您觉得此文章差，就请您
        0%(0)

    文章评论评论内容只代表网友观点，与本站立场无关！

       评论摘要(共 0 条，得分 0 分，平均 0 分) 查看完整评论

    栏目导航

    赞助商链接

    免责条款 - 广告合作 - 下载声明 - 欢迎投稿 - 友情连接 -
    Copyright © 2020-2022 www.xiamiku.com. All Rights Reserved .