Learning Note: Oracle dul data Mining uses Dul data recovery software to recover partition tables

Recover Data from Oracle database partitioned tables using Oracle Dul Data Recovery Tool

Create a Sales Partition table case

CREATE TABLE SALES (   product_id VARCHAR2 (5),   sales_date DATE,   sales_cost number,   STATUS VARCHAR2 ( ) PARTITION by RANGE (sales_date) subpartition by   LIST (STATUS)   subpartition TEMPLATE   (     Subpartition SUB1 values (' ACTIVE '),     subpartition SUB2 values (' INACTIVE ')   ) (   PARTITION P1 values less THA N (to_date (' 2003-01-01 ', ' yyyy-mm-dd ')),   

Query results

Sql> select * from sales; Produ sales_date sales_cost STATUS-----------------------------------------------00001 01-jan-02 ACTIVE 00002 01-jan-01 INACTIVE00003 ACTIVE00005 04-feb-02 01-feb-03 INACTIVE00004 EB-03 inactivesql> SELECT * FROM Sales PARTITION (p1); Produ sales_date sales_cost STATUS-----------------------------------------------00001 01-jan-02 ACTIVE 00002 01-jan-01 ACTIVE00005 04-feb-02 inactivesql> select * FROM Sales PARTITION (p2); Produ sales_date sales_cost STATUS-----------------------------------------------00003 01-feb-03 VE00004 04-feb-03 inactivesql> SELECT * FROM Sales subpartition (P1_SUB1); Produ sales_date sales_cost STATUS-----------------------------------------------00001 01-jan-02 ACTIVE 00002 01-jan-01 activesql> SELECT * FROM Sales subpartition (p1_suB2); Produ sales_date sales_cost STATUS-----------------------------------------------00005 04-feb-02 Vesql> SELECT * FROM Sales subpartition (P2_SUB1), no rows selectedsql> select * FROM Sales subpartition (P2_SUB2); Produ sales_date sales_cost STATUS-----------------------------------------------00003 01-feb-03 VE00004 04-feb-03 INACTIVE

Start Dul

[[email protected] dul]$./duldata unloader:10.2.0.5.20-internal only-on Sat Jan 17:37:45 2013with 64-bit io Functionscopyright (c) 1994 Bernard van Duijnen All rights reserved. Strictly Oracle Internal use OnlyDUL:Warning:Recreating file "Dul.log" Reading USER.dat entries loadedreading OBJ.dat 74764 entries loaded and sorted 74764 entriesreading TAB.dat 2882 entries loadedreading COL.dat 94598 entries loaded and S orted 94598 entriesreading SEG.dat, entries loadedreading EXT.dat, entries loaded and sorted, entriesreading Tabpart . dat entries loaded and sorted entriesreading TABCOMPART.dat 3 entries loaded and sorted 3 entriesreading TABSUBPA RT.dat entries loaded and sorted, entriesreading INDPART.dat 169 entries loaded and sorted 169 entriesreading Indcomp ART.dat 0 Entries loaded and sorted 0 entriesreading INDSUBPART.dat 0 entries loaded and sorted 0 entriesreading IND.dat 5 Entries loadedreading LOB.datDUL:Warning:Increased the size of dc_LOBs from 1024x768 to 8192 entries 1286 entries loadedreading ICOL.dat 7569 entries loadedreading COLTYPE.dat 3003 Entries Loa Dedreading TYPE.dat 2872 Entries loadedreading ATTRIBUTE.dat 11127 entries loadedreading COLLECTION.dat 985 entries Loaded Reading COMPATSEG.dat 0 Entries loadedreading BOOTSTRAP.dat entries loadedreading LOBFRAG.dat 1 entries loaded and sort Ed 1 entriesreading LOBCOMPPART.dat 0 entries loaded and sorted 0 entriesreading UNDO.dat Entries loadedreading Entries loadedreading PROPS.dat entries loadeddatabase character set is Zhs16gbkdatabase national character set is A L16utf16found db_id = 4188950066Found db_name = ora11g

Unload syntax

UNLOAD [TABLE]  [  schema_name.]  table_name               [PARTITION (  partition_name)]              [Subpartition (  sub_partition_name)]              [(  column_ Definitions)]              [  cluster_clause  ]              [  storage_clause  ];

Unload entire Table

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[[email protected] dul]# ls-l chf_sales*-rw-r--r--1 Oracle oinstall 421 Jan 19 18: chf_sales.ctl-rw-r--r--1 Oracle Oinstall 251 Jan 18:09 chf_sales.dat[[email protected] dul]# more chf_sales.dat|000 01| |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 Partition Table

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[[email protected] dul]# ls-l chf_sales_p1*-rw-r--r--1 Oracle oinstall 424 Jan 19 1 8:10 chf_sales_p1.ctl-rw-r--r--1 Oracle oinstall 149 Jan 18:10 chf_sales_p1.dat[[email protected] 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 sub-partition table

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[[email protected] dul]# ls-l chf_sales_p2_sub2*-rw-r--r--1 Oracle oinstall 429 Jan  18:14 chf_sales_p2_sub2.ctl-rw-r--r--1 Oracle oinstall 102 Jan 18:14 chf_sales_p2_sub2.dat[[email protected] 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|

validation control files

[[email protected] dul]# ls-l chf_sales*-rw-r--r--1 Oracle oinstall 421 Jan 18:09 chf_sales.ctl-rw-r--r--1 ORAC Le Oinstall 251 Jan 18:09 chf_sales.dat-rw-r--r--1 Oracle oinstall 424 Jan 18:10 chf_sales_p1.ctl-rw-r--r--1 oracl E oinstall 149 Jan 18:10 chf_sales_p1.dat-rw-r--r--1 Oracle oinstall 429 Jan 18:14 chf_sales_p2_sub2.ctl-rw-r--r-- 1 Oracle Oinstall 102 Jan 18:14 chf_sales_p2_sub2.dat[[email protected] dul]# more chf_sales.ctlload DataCHARACTE RSET zhs16gbkinfile ' chf_sales.dat ' insertinto 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) Enclo sed by x ' 7C ', "STATUS" CHAR (8) enclosed by x ' 7C ') [[email protected] dul]# Mor E chf_sales_p1.ctlload datacharacterset zhs16gbkinfile ' chf_sales_p1.dat ' Insertinto 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) Enclo sed by x ' 7C ', "STATUS" CHAR (8) enclosed by x ' 7C ') [[email protected] dul]# Mor E chf_sales_p2_sub2.ctlload datacharacterset zhs16gbkinfile ' chf_sales_p2_sub2.dat ' insertinto 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) Enclo sed by x ' 7C ', "STATUS" CHAR (8) enclosed by x ' 7C ')

This proves that the table structure in all the control files is the structure of the whole table, not the partition table, in the actual process, you can consider the swap partition to implement

-----------------Tips--------------------operation is risky, hands-on need to be cautious Oracle Research Center http://www.oracleplus.net This article by the master cherish the original sharing, reproduced please try to retain the site website

--------------------------------------ORACLE-DBA----------------------------------------

The most authoritative and professional Oracle Case Resource Summary Learning Note: Oracle Dul data Mining uses Dul data recovery software to recover partition tables

Original URL: http://www.oracleplus.net/arch/oracle-20160522-215.html

Oracle Research Center Key Words:Oracle dul Data Mining uses Dul data recovery software to recover data from partitioned tables

Learning Note: Oracle dul data Mining uses Dul data recovery software to recover partition tables