Hello Tom

First of all thanks for your response. There's no doubt Export is not a backup. Nor do we treat that as one. I am a proud owner of your invaluable books (or should I call my bible) and I have no doubt that export can even be called a backup. However the situation we have demanded me to check with you if you had anything "magical" to resolve it. With our business needs, we cannot roll back using RMAN backups or even flashback to that state as we want to go 3 weeks backwards. I have already told the business that it's not just possible to restore it, let alone, cleanly. If there's only direction to go (either do or die), I said I could do it but wouldn't guarantee a clean restore.

My desperate strategy was:

1) I generate script for all foreign/unique/primary key constraints and back it up.
2) Delete all foreign key constraints (about 3345 of them)
3) truncate these 200 tables
4) import the data back from the dump
5) Create all the foreign keys but enable novalidate. If I have to make it validate, I am talking about few days here with all 250 GB partitioned tables. The total size of the database is 14 TB.

This will not only cause a problem to our restored tables but also to the entire application which is 24x7 and bread and butter of the business. But that's where we stand now.

Anyway, thanks for your invaluable time.

Regards

You can use this syntax

CREATE TABLE OT_DMP
ORGANIZATION EXTERNAL
( TYPE ORACLE_DATAPUMP
DEFAULT DIRECTORY STAGE_DIR
ACCESS PARAMETERS
( )
LOCATION (STAGE_DIR:'OT.DMP')
)
AS (SELECT * FROM OT);

where OT.DMP is an oracle datapump file and STAGE_DIR is an oracle directory object that point to the direcoty where is the OT.DMP file

Or this syntax :

CREATE TABLE OT_EXT
(
FLSYS VARCHAR2(1 BYTE),
CSI_ID NUMBER(16),
ISTA_VERSTATO NUMBER(16),
ITER_ID NUMBER(3),
AZ_ID VARCHAR2(2 BYTE),
OT_ID NUMBER(6),
VER_DTINI TIMESTAMP(0),
VER_DTFINE TIMESTAMP(0),
OT_DES VARCHAR2(70 BYTE),
OT_DESSTA VARCHAR2(250 BYTE),
SER_ID VARCHAR2(2 BYTE),
CATCOMSER_ID NUMBER(3),
COMSER_ID NUMBER(3),
OT_IDEXT VARCHAR2(25 BYTE),
OT_FLTIPOOT VARCHAR2(1 BYTE),
OT_NOTE VARCHAR2(250 BYTE),
SOG_ID NUMBER(10),
TSOG_ID NUMBER(3)
)
ORGANIZATION EXTERNAL
( TYPE ORACLE_DATAPUMP
DEFAULT DIRECTORY STAGE_DIR
ACCESS PARAMETERS
( LOGFILE 'ot_read.log' )
LOCATION (STAGE_DIR:'OT.DMP')
)
REJECT LIMIT 0;

You can read the manual for a full explain of syntax.

Best Regards

He said he has an exp, not a expdb. Doubt you can use an external table against that.

He could however, restore a backup to a test database to the desired point in time, Then use expdb, then use the external table, THEN write a bunch of custom code to put things back.

I think I'd rather fight Mike Tyson than go through that though.

If you have FK constraints referencing these 200 tables, and know that there will be 'missing' rows after you restore them, then you have no way to maintain consistency. Setting novalidate may not work as your application may not handle the missing parent keys gracefully, or it may not help in that your app will try to join tables together and the parentless rows will be invisible to the app.

One thought is to load the tables into a different schema and do some sort of MERGE operation to the 'live' tables, if that makes any logical sense for your schema.

Don't think I can run a merge on 200 tables and even if I manage to spend 20 hours writing scripts for that, there's no guarantee that I will be able to re-enable validate the foreign keys.

So we'll have to wait what Business have to say about this. If it was notified immediately, we could have done a restore of database up to that point but it's too late.

CREATE TABLE OT_DMP
ORGANIZATION EXTERNAL
( TYPE ORACLE_DATAPUMP
DEFAULT DIRECTORY "/dmfiles_new/Statements/"
ACCESS PARAMETERS
( )
LOCATION ("/dmfiles_new/Statements/":'OT.DMP')
)
AS (SELECT * FROM emp);


Hello here i want data from table into file so..in directory path "dup" file is generating but i am unable to see or open this file..
please help me how to see the data.i tried with txt file also..

now i able to see this dmp file but like this what is this and i need in txt format what to do for that.please reply
<ROWSET>
<ROW>
<STRMTABLE_T>
<VERS_MAJOR>1</VERS_MAJOR>
<VERS_MINOR>0 </VERS_MINOR>
<VERS_DPAPI>3</VERS_DPAPI>
<ENDIANNESS>0</ENDIANNESS>
<CHARSET>AL32UTF8</CHARSET>
<NCHARSET>AL16UTF16</NCHARSET>
<DBTIMEZONE>+05:30</DBTIMEZONE>
<OWNER_NAME>DM32TEST</OWNER_NAME>
<NAME>TEST_TEST1</NAME>
<COL_LIST>
<COL_LIST_ITEM>
<COL_NUM>1</COL_NUM>
<NAME>COUNTRY_CODE</NAME>
<TYPE_NUM>1</TYPE_NUM>
<LENGTH>5</LENGTH>
<PRECISION_NUM>0</PRECISION_NUM>
<SCALE>0</SCALE>
<CHARSETID>873</CHARSETID>
<CHARSETFORM>1</CHARSETFORM>
<CHARLENGTH>5</CHARLENGTH>
</COL_LIST_ITEM>
<COL_LIST_ITEM>
<COL_NUM>2</COL_NUM>
<NAME>COUNTRY_NAME</NAME>
<TYPE_NUM>1</TYPE_NUM>
<LENGTH>50</LENGTH>
<PRECISION_NUM>0</PRECISION_NUM>
<SCALE>0</SCALE>
<CHARSETID>873</CHARSETID>
<CHARSETFORM>1</CHARSETFORM>
<CHARLENGTH>50</CHARLENGTH>
</COL_LIST_ITEM>
<COL_LIST_ITEM>
<COL_NUM>3</COL_NUM>
<NAME>COUNTRY_LANGUAGE</NAME>
<TYPE_NUM>1</TYPE_NUM>
<LENGTH>50</LENGTH>
<PRECISION_NUM>0</PRECISION_NUM>
<SCALE>0</SCALE>
<CHARSETID>873</CHARSETID>
<CHARSETFORM>1</CHARSETFORM>
<CHARLENGTH>50</CHARLENGTH>
</COL_LIST_ITEM>
</COL_LIST>
</STRMTABLE_T>
</ROW>
</ROWSET>