学习笔记:Oracle dul数据挖掘 使用DUL数据恢复软件恢复分区表中

学习笔记:Oracle dul数据挖掘 使用DUL数据恢复软件恢复分区表中

使用Oracle dul数据恢复工具对Oracle数据库分区表中的数据进行恢复

创建SALES分区表案例

CREATE TABLE SALES 
( 
  PRODUCT_ID VARCHAR2(5), 
  SALES_DATE DATE, 
  SALES_COST NUMBER(10), 
  STATUS VARCHAR2(20) 
) 
PARTITION BY RANGE(SALES_DATE) 
  SUBPARTITION BY LIST (STATUS) 
  SUBPARTITION TEMPLATE 
  ( 
    SUBPARTITION SUB1 VALUES ('ACTIVE') , 
    SUBPARTITION SUB2 VALUES ('INACTIVE') 
  ) 
( 
  PARTITION P1 VALUES LESS THAN (TO_DATE('2003-01-01','YYYY-MM-DD')), 
  PARTITION P2 VALUES LESS THAN (TO_DATE('2003-03-01','YYYY-MM-DD'))
) 
/
INSERT INTO SALES VALUES('00001','01-Jan-02',100,'ACTIVE') 
/ 
INSERT INTO SALES VALUES('00002','01-Jan-01',200,'ACTIVE') 
/ 
INSERT INTO SALES VALUES('00003','01-Feb-03',300,'INACTIVE') 
/ 
INSERT INTO SALES VALUES('00004','04-Feb-03',300,'INACTIVE') 
/ 
INSERT INTO SALES VALUES('00005','04-Feb-02',300,'INACTIVE') 
/ 

查询结果

SQL> select * from sales;

PRODU SALES_DATE   SALES_COST STATUS
----- ------------ ---------- --------------------
00001 01-JAN-02           100 ACTIVE
00002 01-JAN-01           200 ACTIVE
00005 04-FEB-02           300 INACTIVE
00003 01-FEB-03           300 INACTIVE
00004 04-FEB-03           300 INACTIVE

SQL> select  * from sales PARTITION(p1);

PRODU SALES_DATE   SALES_COST STATUS
----- ------------ ---------- --------------------
00001 01-JAN-02           100 ACTIVE
00002 01-JAN-01           200 ACTIVE
00005 04-FEB-02           300 INACTIVE

SQL> select  * from sales PARTITION(p2);

PRODU SALES_DATE   SALES_COST STATUS
----- ------------ ---------- --------------------
00003 01-FEB-03           300 INACTIVE
00004 04-FEB-03           300 INACTIVE

SQL> select  * from sales SUBPARTITION(p1_sub1);

PRODU SALES_DATE   SALES_COST STATUS
----- ------------ ---------- --------------------
00001 01-JAN-02           100 ACTIVE
00002 01-JAN-01           200 ACTIVE

SQL> select  * from sales SUBPARTITION(p1_sub2);

PRODU SALES_DATE   SALES_COST STATUS
----- ------------ ---------- --------------------
00005 04-FEB-02           300 INACTIVE

SQL> select  * from sales SUBPARTITION(p2_sub1);

no rows selected

SQL> select  * from sales SUBPARTITION(p2_sub2);

PRODU SALES_DATE   SALES_COST STATUS
----- ------------ ---------- --------------------
00003 01-FEB-03           300 INACTIVE
00004 04-FEB-03           300 INACTIVE

启动dul

[oracle@xifenfei dul]$ ./dul

Data UnLoader: 10.2.0.5.20 - Internal Only - on Sat Jan 19 17:37:45 2013
with 64-bit io functions

Copyright (c) 1994 2013 Bernard van Duijnen All rights reserved.

 Strictly Oracle Internal Use Only


DUL: Warning: Recreating file "dul.log"
Reading USER.dat 91 entries loaded
Reading OBJ.dat 74764 entries loaded and sorted 74764 entries
Reading TAB.dat 2882 entries loaded
Reading COL.dat 94598 entries loaded and sorted 94598 entries
Reading SEG.dat 17 entries loaded
Reading EXT.dat 43 entries loaded and sorted 43 entries
Reading TABPART.dat 150 entries loaded and sorted 150 entries
Reading TABCOMPART.dat 3 entries loaded and sorted 3 entries
Reading TABSUBPART.dat 36 entries loaded and sorted 36 entries
Reading INDPART.dat 169 entries loaded and sorted 169 entries
Reading INDCOMPART.dat 0 entries loaded and sorted 0 entries
Reading INDSUBPART.dat 0 entries loaded and sorted 0 entries
Reading IND.dat 5150 entries loaded
Reading LOB.dat
DUL: Warning: Increased the size of DC_LOBS from 1024 to 8192 entries
 1286 entries loaded
Reading ICOL.dat 7569 entries loaded
Reading COLTYPE.dat 3003 entries loaded
Reading TYPE.dat 2872 entries loaded
Reading ATTRIBUTE.dat 11127 entries loaded
Reading COLLECTION.dat 985 entries loaded
Reading COMPATSEG.dat 0 entries loaded
Reading BOOTSTRAP.dat 60 entries loaded
Reading LOBFRAG.dat 1 entries loaded and sorted 1 entries
Reading LOBCOMPPART.dat 0 entries loaded and sorted 0 entries
Reading UNDO.dat 21 entries loaded
Reading TS.dat 10 entries loaded
Reading PROPS.dat 36 entries loaded
Database character set is ZHS16GBK
Database national character set is AL16UTF16
Found db_id = 4188950066
Found db_name = ORA11G

unload 语法

UNLOAD [TABLE]  [  schema_name . ]  table_name 
              [ PARTITION(  partition_name ) ]
              [ SUBPARTITION(  sub_partition_name ) ]
              [ (  column_definitions ) ]
              [  cluster_clause  ]
              [  storage_clause  ] ;

unload整个表

DUL> unload table chf.SALES;
. unloading table                     SALES
 . Unloading partition                      P1
 .   Unloading sub partition                 P1_SUB1
 .   Unloading sub partition                 P1_SUB2
 . Unloading partition                      P2
 .   Unloading sub partition                 P2_SUB1
 .   Unloading sub partition                 P2_SUB2
.           table SALES total        5 rows unloaded

[root@xifenfei dul]# ls -l CHF_SALES*
-rw-r--r-- 1 oracle oinstall 421 Jan 19 18:09 CHF_SALES.ctl
-rw-r--r-- 1 oracle oinstall 251 Jan 19 18:09 CHF_SALES.dat
[root@xifenfei dul]# more CHF_SALES.dat
|00001| |01-JAN-2002 AD 00:00:00| |100| |ACTIVE|
|00002| |01-JAN-2001 AD 00:00:00| |200| |ACTIVE|
|00005| |04-FEB-2002 AD 00:00:00| |300| |INACTIVE|
|00003| |01-FEB-2003 AD 00:00:00| |300| |INACTIVE|
|00004| |04-FEB-2003 AD 00:00:00| |300| |INACTIVE|

unload 分区表

DUL> unload table chf.SALES PARTITION(p1);
. unloading table                     SALES
 . Unloading partition                      P1
 .   Unloading sub partition                 P1_SUB1
 .   Unloading sub partition                 P1_SUB2
.           table SALES total        3 rows unloaded

[root@xifenfei dul]# ls -l CHF_SALES_P1*
-rw-r--r-- 1 oracle oinstall 424 Jan 19 18:10 CHF_SALES_P1.ctl
-rw-r--r-- 1 oracle oinstall 149 Jan 19 18:10 CHF_SALES_P1.dat
[root@xifenfei dul]# more CHF_SALES_P1.dat
|00001| |01-JAN-2002 AD 00:00:00| |100| |ACTIVE|
|00002| |01-JAN-2001 AD 00:00:00| |200| |ACTIVE|
|00005| |04-FEB-2002 AD 00:00:00| |300| |INACTIVE|

unload 子分区表

DUL> unload table chf.SALES SUBPARTITION(p2_SUB2);
. unloading table                     SALES
 . Unloading partition                      P1
 . Unloading partition                      P2
 .   Unloading sub partition                 P2_SUB2
.           table SALES total        2 rows unloaded

[root@xifenfei dul]# ls -l CHF_SALES_P2_SUB2*
-rw-r--r-- 1 oracle oinstall 429 Jan 19 18:14 CHF_SALES_P2_SUB2.ctl
-rw-r--r-- 1 oracle oinstall 102 Jan 19 18:14 CHF_SALES_P2_SUB2.dat
[root@xifenfei dul]# more CHF_SALES_P2_SUB2.dat
|00003| |01-FEB-2003 AD 00:00:00| |300| |INACTIVE|
|00004| |04-FEB-2003 AD 00:00:00| |300| |INACTIVE|

验证控制文件

[root@xifenfei dul]# ls -l CHF_SALES*
-rw-r--r-- 1 oracle oinstall 421 Jan 19 18:09 CHF_SALES.ctl
-rw-r--r-- 1 oracle oinstall 251 Jan 19 18:09 CHF_SALES.dat
-rw-r--r-- 1 oracle oinstall 424 Jan 19 18:10 CHF_SALES_P1.ctl
-rw-r--r-- 1 oracle oinstall 149 Jan 19 18:10 CHF_SALES_P1.dat
-rw-r--r-- 1 oracle oinstall 429 Jan 19 18:14 CHF_SALES_P2_SUB2.ctl
-rw-r--r-- 1 oracle oinstall 102 Jan 19 18:14 CHF_SALES_P2_SUB2.dat
[root@xifenfei dul]# more CHF_SALES.ctl
load data
CHARACTERSET ZHS16GBK
infile 'CHF_SALES.dat'
insert
into table "CHF"."SALES"
fields terminated by whitespace
(
  "PRODUCT_ID"                       CHAR(5) enclosed by X'7C'       
 ,"SALES_DATE"                       DATE "DD-MON-YYYY AD HH24:MI:SS" enclosed by X'7C'
 ,"SALES_COST"                       CHAR(3) enclosed by X'7C'       
 ,"STATUS"                           CHAR(8) enclosed by X'7C'       
)
[root@xifenfei dul]# more CHF_SALES_P1.ctl
load data
CHARACTERSET ZHS16GBK
infile 'CHF_SALES_P1.dat'
insert
into table "CHF"."SALES"
fields terminated by whitespace
(
  "PRODUCT_ID"                       CHAR(5) enclosed by X'7C'       
 ,"SALES_DATE"                       DATE "DD-MON-YYYY AD HH24:MI:SS" enclosed by X'7C'
 ,"SALES_COST"                       CHAR(3) enclosed by X'7C'       
 ,"STATUS"                           CHAR(8) enclosed by X'7C'       
)
[root@xifenfei dul]# more CHF_SALES_P2_SUB2.ctl
load data
CHARACTERSET ZHS16GBK
infile 'CHF_SALES_P2_SUB2.dat'
insert
into table "CHF"."SALES"
fields terminated by whitespace
(
  "PRODUCT_ID"                       CHAR(5) enclosed by X'7C'       
 ,"SALES_DATE"                       DATE "DD-MON-YYYY AD HH24:MI:SS" enclosed by X'7C'
 ,"SALES_COST"                       CHAR(3) enclosed by X'7C'       
 ,"STATUS"                           CHAR(8) enclosed by X'7C'       
)

这里证明所有的控制文件中的表结构都是整个表的结构,而不是分区表,在实际处理过程中,可以考虑交换分区来实现
 

-----------------温馨提示--------------------
操作有风险,动手需谨慎
Oracle研究中心
http://www.oracleplus.net
本文由大师惜分飞原创分享,转载请尽量保留本站网址

 

————————————–ORACLE-DBA—————————————-

最权威、专业的Oracle案例资源汇总之学习笔记:Oracle dul数据挖掘 使用DUL数据恢复软件恢复分区表中

原文唯一网址:http://www.oracleplus.net/arch/oracle-20160522-215.html

Oracle研究中心

关键词:

Oracle dul数据挖掘

使用DUL数据恢复软件恢复分区表中的数据

转载于:https://www.cnblogs.com/piguanjia/p/5608281.html

版权声明:本文内容由互联网用户自发贡献,该文观点仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 举报,一经查实,本站将立刻删除。

发布者:全栈程序员-用户IM,转载请注明出处:https://javaforall.cn/108962.html原文链接:https://javaforall.cn

【正版授权,激活自己账号】: Jetbrains全家桶Ide使用,1年售后保障,每天仅需1毛

【官方授权 正版激活】: 官方授权 正版激活 支持Jetbrains家族下所有IDE 使用个人JB账号...

(0)


相关推荐

  • XLSTransformer+模板 导出 Excel「建议收藏」

    XLSTransformer+模板 导出 Excel「建议收藏」java快速导出Excel 利用XLSTransformer+模板

  • sigaction信号处理

    sigaction信号处理1.sigactionintsigaction(intsignum,conststructsigaction*act,structsigaction*oldact);signum:可以指定SIGKILL和SIGSTOP以外的所有信号2.structsigactionstructsigaction{void(*sa_handler)(int);

  • idea实用插件大全_intellij idea插件

    idea实用插件大全_intellij idea插件?常用?MyBatisLogPlugin(控制台SQL转换成可执行SQL)?KeyPromoterX(快捷键提示)?CodeGlance(代码小地图)?FreeMybatisplugin(mapper和xml文件跳转)?GrepConsole(控制台日志过滤查看)?Lombok(简化类代码)?StringManipulation(字符串转换)?AlibabaJavaCodingGuidelines(阿里巴巴代码规约扫描)?JRebel(热部署)

  • 中兴F450光猫设置桥接_电信光猫改桥接后上不了网络

    中兴F450光猫设置桥接_电信光猫改桥接后上不了网络前几天突然想搞外网访问,但是电信这款光猫DMZ不能用让我很愁,后来经过一番了解可以让光猫只负责光数转换,剩下的事情交给路由,但是要把光猫设置成桥接模式这个光猫比较特殊不需要进入超级管理员只需要下面这个链接:http://192.168.1.1:8080/bridge_route.gch会出现这个页面,点击桥接复原就行,我是已经点过了,所以置灰了。…

  • html页面导出为pdf(jsPDF、iText、wkhtmltopdf)「建议收藏」

    html页面导出为pdf(jsPDF、iText、wkhtmltopdf)「建议收藏」html页面导出pdf,本来是一件很简单的事情,在浏览器直接打印(Mac快捷键为⌘+p;Windows快捷键为ctrl+p),就可以把页面另存为pdf文件,但对于要经常把页面导出为pdf的用户来说并不友好。调研了几种html导出pdf的实现方式,这里把要点记录下来分享下。调研对象优点缺点分页图片表格链接中文特殊字符、样式导出…

  • macbook如何安装双系统_双系统安装方法

    macbook如何安装双系统_双系统安装方法1.官网https://www.microsoft.com/zh-cn/software-download/windows10ISO进入官网下载win10(因为win10仅支持win10专业版不要下家庭中文版)语言:简体中文64位2.用迅雷下否则直接下载时间会很长Mac迅雷-轻体验,大改变(拖动迅雷至文件夹,即可安装,macOS10.15安装后请到应用程序找到图标,右键打开)点击迅雷的加号新建任务在浏览器复制之后自动弹出迅雷下载对话保存位置至桌面点击下载打开浏览器.

发表回复

您的电子邮箱地址不会被公开。

关注全栈程序员社区公众号