Home » RDBMS Server » Server Utilities » Export Multiple tables only (Linux , oracle 9i)
Export Multiple tables only [message #560098] Tue, 10 July 2012 09:39 Go to next message
chandu208
Messages: 17
Registered: July 2012
Junior Member
can anyone please send me the command for exporting multiple tables(1000+) in Linux env. 9i db, i know we can do using spool command but dont know exactly how to put it. i know using Datapump but this is 9i.
any suggestions plzzzzz.

Thnaks in advance
Re: Export Multiple tables only [message #560100 is a reply to message #560098] Tue, 10 July 2012 09:45 Go to previous messageGo to next message
BlackSwan
Messages: 23070
Registered: January 2009
Senior Member
Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/

>can anyone please send me the command for exporting
answer depends upon exactly what you mean by "exporting"; which can be different from SPOOL.
consider using exp along with a control file.
Re: Export Multiple tables only [message #560101 is a reply to message #560100] Tue, 10 July 2012 09:56 Go to previous messageGo to next message
chandu208
Messages: 17
Registered: July 2012
Junior Member
I need to export schema tables which are 1000+ so exp usrname/psswd file=xx Tables=??????

don't know what to give in 'Tables'
Re: Export Multiple tables only [message #560102 is a reply to message #560101] Tue, 10 July 2012 09:59 Go to previous messageGo to next message
BlackSwan
Messages: 23070
Registered: January 2009
Senior Member
>don't know what to give in 'Tables'

exp usrname/psswd file=xx owner=<source_schema>
-- above will export all table owned by OWNER
Re: Export Multiple tables only [message #560103 is a reply to message #560102] Tue, 10 July 2012 10:04 Go to previous messageGo to next message
chandu208
Messages: 17
Registered: July 2012
Junior Member
Thank u for the quick response but it exports all schema objects but i need only Tables
Re: Export Multiple tables only [message #560104 is a reply to message #560103] Tue, 10 July 2012 10:11 Go to previous messageGo to next message
BlackSwan
Messages: 23070
Registered: January 2009
Senior Member
>Thank u for the quick response but it exports all schema objects but i need only Tables
then list just the tables in a control file.
Re: Export Multiple tables only [message #560764 is a reply to message #560104] Mon, 16 July 2012 19:18 Go to previous messageGo to next message
alan.kendall@nfl.com
Messages: 163
Registered: June 2012
Location: Culver City, California
Senior Member
I use the following to export files from 9i one at a time from either Unix or Windows. You can modify the script to also create the imports as well.

SCOTT > @cr8_exports_by_TABLE_NAME_on_Unix.sql

exp userid=myschema_login/myschema_password file=DEPT.dmp tables=\(DEPT\) log=DEPT.log
exp userid=myschema_login/myschema_password file=EMP.dmp tables=\(EMP\) log=EMP.log
exp userid=myschema_login/myschema_password file=BONUS.dmp tables=\(BONUS\) log=BONUS.log
exp userid=myschema_login/myschema_password file=SALGRADE.dmp tables=\(SALGRADE\) log=SALGRADE.log

SCOTT > list
  1  select 'exp userid=myschema_login/myschema_password file='||table_name||
  2* '.dmp tables=\('||table_name||'\) log='||table_name||'.log' from user_tables

SCOTT > @cr8_exports_by_TABLE_NAME_on_Windows.sql

exp userid=myschema_login/myschema_password file=DEPT.dmp tables=(DEPT) log=DEPT.log
exp userid=myschema_login/myschema_password file=EMP.dmp tables=(EMP) log=EMP.log
exp userid=myschema_login/myschema_password file=BONUS.dmp tables=(BONUS) log=BONUS.log
exp userid=myschema_login/myschema_password file=SALGRADE.dmp tables=(SALGRADE) log=SALGRADE.log

SCOTT > list
  1  select 'exp userid=myschema_login/myschema_password file='||table_name||
  2* '.dmp tables=('||table_name||') log='||table_name||'.log' from user_tables
Re: Export Multiple tables only [message #560765 is a reply to message #560764] Mon, 16 July 2012 19:45 Go to previous message
alan.kendall@nfl.com
Messages: 163
Registered: June 2012
Location: Culver City, California
Senior Member
I have used the following sql to generate exports with multiple tables in each export dump file. The backslash tells the Unix OS that the open parenthesis and the close parenthesis are not to parsed by the Unix OS. Also the backslash is added to each line that is continued on the next line. I am not sure what the continuation is on Windows.

SCOTT > @cr8_export_by_TABLE_NAME_good_for_RESTARTS_on_Unix.sql
exp userid=myschema_login/myschema_password file=export.dmp tables=\
\(\
,BONUS\
,DEPT\
,EMP\
,SALGRADE\
\)\
 log=export.log

cr8_export_by_TABLE_NAME_good_for_RESTARTS_on_Unix.sql looks like:
set pages 0
set feedback off
set lines 200
select 'exp userid=myschema_login/myschema_password file=export.dmp tables=\' from dual;
select '\(\' from dual;
select ','||table_name||'\'
from user_tables
order by table_name;
select '\)\' from dual;
select ' log=export.log' from dual;
set feedback on
set pages 40
csprdcdadb11(oracle)/home/oracle/alan>exp userid=myschema_login/myschema_password file=export.dmp tables=\
> \(\
> ,BONUS\
> ,DEPT\
> ,EMP\
> ,SALGRADE\
> \)\
>  log=export.log

Export: Release 11.2.0.3.0 - Production on Mon Jul 16 17:40:48 2012

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.


EXP-00056: ORACLE error 1017 encountered
ORA-01017: invalid username/password; logon denied
Username: scott/tiger

Connected to: Oracle Database 9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Tes
Export done in AL32UTF8 character set and AL16UTF16 NCHAR character set

About to export specified tables via Conventional Path ...
. . exporting table                          BONUS          0 rows exported
. . exporting table                           DEPT          4 rows exported
. . exporting table                            EMP         14 rows exported
. . exporting table                       SALGRADE          5 rows exported
Export terminated successfully without warnings.
csprdcdadb11(oracle)/home/oracle/alan>ls -ltr
total 88

-rw-r--r-- 1 oracle oinstall   792 Jul 16 17:40 export.log
-rw-r--r-- 1 oracle oinstall 24576 Jul 16 17:40 export.dmp
Previous Topic: error in exp/imp in datapump utility
Next Topic: import the table
Goto Forum:
  


Current Time: Sat Nov 29 01:50:11 CST 2014

Total time taken to generate the page: 0.08114 seconds