Oracle 10g tables dumps


 
Thread Tools Search this Thread
Top Forums Programming Oracle 10g tables dumps
# 1  
Old 03-01-2012
Oracle 10g tables dumps

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  
Old 03-01-2012
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  
Old 03-02-2012
You should be able to accomplish that using Data Pump not the old export utility
# 4  
Old 03-03-2012
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  
Old 03-05-2012
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.
Login or Register to Ask a Question

Previous Thread | Next Thread

7 More Discussions You Might Find Interesting

1. Solaris

need help in oracle upgrade from 10g to 11g

Hi , situation has come where in i want to upgrade my database from 10g to 11g ,my code heavily depends on Pro*C , i just want to know if i upgrade my DB from 10g to 11g , will there be any changes in my pro*c compiler ..? if so then wat are the prerequisites i need to check ..? thanks ... (2 Replies)
Discussion started by: senkerth
2 Replies

2. AIX

Oracle 10g on AIX7

Hi, anyone know if Oracle Database 10g is AIX 7 certified??? thanks in advance. Regards. Mario (2 Replies)
Discussion started by: Zio Bill
2 Replies

3. Solaris

How can i install oracle 10g on x86

Please guide me to install oracle 10g in x86 machine running solaris10. I tried the method that is distributed with the Oracle cd, but i finds it to be too complicated for me to complete. Please help. (2 Replies)
Discussion started by: Sesha
2 Replies

4. Solaris

Installing oracle 10g on solaris

Thanks all for your help. Now here is another problem i have, i have been trying to install oracle 10g on solaris but i keep getting this error message "there is not enough memory on the volume you specify to copy the starter database files the oracle universal installer discover that you have... (8 Replies)
Discussion started by: mutiat
8 Replies

5. AIX

oracle 10g on AIX 5

hi can anyone help me by telling step by stem oracle10g installation in AIX 5 . Munir mondolsoft Bangladesh (3 Replies)
Discussion started by: dbamunir
3 Replies

6. Red Hat

Help installing Oracle 9i/10g on RedHat?

If anybody can help would be greatly appreciated... I get the same error with Oracle 9i & 10g when installing on Red Hat Fedora 4, it's java related... Exception in thread "main" java.lang.UnsatisfiedLinkError: /tmp/OraInstall2007-03-16_01-09PM/jre/lib/i386/libawt.so: connat restore segment... (0 Replies)
Discussion started by: Joncamp
0 Replies

7. UNIX for Advanced & Expert Users

oracle 10g

I have download Oracle 10g for Solaris and I have configurate environment than I will start runInstaller and I have a messege that my version of solaris is not 5.7 , 5.8 and 5,9 I have 5.10 Solaris version what I shoud do I have download most actuall Oracle databases. (4 Replies)
Discussion started by: Deux
4 Replies
Login or Register to Ask a Question