Oracle 10g tables dumps

Applications Programming
1

HI All,
I am using Oracle 10g . Want to take dumps(.dmp log) from many tables with where clause having same schema.

ex :
TB_MTH_ORA_CAB "WHERE TRUNC(dw_entry_dt )= TO_DATE('01-JAN-2011')"
TB_AM_AT_OSS_MAT "WHERE TRUNC(dw_entry_date )>= TO_DATE('01-JAN-2011') AND TRUNC(dw_entry_date )<= TO_DATE('03-JAN-2011') "

I have many tables with different conditions and running them individually through export command is taking time .
Is there any way where we can take dumps all at once for all tables .Any help appreciated .Thanks

2

Are you talking about Oracles export utility? If you have different conditions I do not think there is a way to do it all in one go.

3

You should be able to accomplish that using Data Pump not the old export utility

4

Hokem,

Could you please let me on how to implement for my posted tables using
Data pump. I never used pump to take export of table before.
Please guide .ty

5

It would be easier for me just to point you to the documentation but I don't have enough posts to stick a URL in here...

Basically for what you want to accomplish it would be easier for you to create a parameter file and use the QUERY keyword to filter the data. You will also need to create a directory object within Oracle (which requires the CREATE ANY DIRECTORY privilege or a role allowing you to do it) and grant read,write to the user doing the export. So...

SQL> create or replace directory exp_dir as '<path to directory>';
SQL> grant read,write on directory expdir to test_man; --assuming this user didn't create it

The contents of the parameter file would look something like this (you can call it whatever you want):

TABLES=test_man.test_tbl,test_man.test_tbl_02 -- comma separated list of tables
DUMPFILE=test_dmp.dmp
QUERY=test_man.test_tbl:"WHERE T_ROW1 >1",test_man.test_tbl_02:"WHERE owner !='SYS'" -- table_name:"WHERE CLAUSE",table_name:"WHERE CLAUSE",...

Whatever user you use to call the data pump export will need create table permissions as the data pump job needs to create a temp master table.

To execute from the command line you would do:

testuser@test>expdp username/password directory=exp_dir parfile=<path to paramter file>

Hope this helps. Let me know if you need some clarification.