How to create a list of tables for export using SELECT statement?

Using the SQL one can create a list of tables to pass as a parameter to export (exp).

set heading off
set pages 0

– create the list for a specific user
SELECT DECODE( rownum, 1, ‘tables=(‘, ‘,’ ), owner || ‘.’ || table_name FROM dba_tables WHERE owner IN ( ‘SCOTT’ )
UNION ALL
SELECT ‘)’, null FROM dual;

Output:
tables=( SCOTT.USERS
, SCOTT.DEPT
, SCOTT.EMP
, SCOTT.BONUS
, SCOTT.SALGRADE
, SCOTT.TEST_OBJECTS
, SCOTT.STATS_BACKUP
, SCOTT.DUMMY1
, SCOTT.BIGEMP
, SCOTT.MYDUAL
)

– in the example below it will create list of tables that begin with F but using the example you can create
SELECT DECODE( rownum, 1, ‘tables=(‘, ‘,’ ), owner || ‘.’ || table_name FROM dba_tables WHERE table_name like ‘F%’
UNION ALL
select ‘)’, null from dual
/

Export/Import to/from a compressed file

To reduce the amount of space used when exporting one can compress the data as creating export file and also import data from a compressed file.

Exporting data directly to a compressed file
#!/bin/ksh

PIPE_FILE=/tmp/exp.pipe
EXP_FILE=exp.dmp.gz

# delete pipe file
rm -f $PIPE_FILE
# create a pipe file
$ mknod $PIPE_FILE p
$ gzip < $PIPE_FILE > $EXP_FILE &
$ exp file=$PIPE_FILE ….

Importing data directly from a compressed file
#!/bin/ksh

rm -f $PIPE_FILE
PIPE_FILE=/tmp/imp.pipe
EXP_FILE=exp.dmp.gz

$ mknod $PIPE_FILE p
$ gunzip < $EXP_FILE > $PIPE_FILE &
$ imp file=$PIPE_FILE ….

If you are using Oracle 10g datapump utility you can’t use the above step as the exdp checks if a file exists before writing and generates an error as the file exists.

How to monitor datapump?

When using datapump one can run the following query to monitor the progress by running the following SQL.

select sid, serial#, sofar, totalwork,
dp.owner_name, dp.state, dp.job_mode
from gv$session_longops sl, gv$datapump_job dp
where sl.opname = dp.job_name and sofar != totalwork;

SID SERIAL# SOFAR TOTALWORK OWNER_NAME STATE JOB_MODE
———- ———- ———- ———- —————————— —————————— ——————————
122 64151 1703 2574 SYSTEM EXECUTING FULL

Setting NLS_LANG for export/import

Last updated: 17-Nov-2008

When exporting/importing one can minimize risk of losing data during import/export by setting NLS_LANG.

- Before starting export set NLS_LANG to be the same character set of the database being exported which means no conversion takes place, all the data will be stored in the export file as it was stored in the database.
- Before starting import set NLS_LANG to be the same value as the it was set during export which means no conversion will take place in the import session, but if the character set of the target database is different the data will automatically be converted when import inserts the data in the database.

OR

- Before starting export set NLS_LANG to be the same character set of the database being imported to which means conversion takes place at this step it will automatically convert during export.
- Before starting import set NLS_LANG to be the same value as the it was set during import which means no conversion will take place as it was already converted during export.

To find the NLS_LANG click on the following link

ORA-19206 when running export (exp)

On Oracle9i if you get the following error when running export (exp) utility, to fix this issue one would need to run the $ORACLE_HOME/rdbms/admin/catmeta.sql as sys to fix this issue.

About to export SYSTEM’s objects …
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
EXP-00056: ORACLE error 19206 encountered
ORA-19206: Invalid value for query or REF CURSOR parameter
ORA-06512: at “SYS.DBMS_XMLGEN”, line 83
ORA-06512: at “SYS.DBMS_METADATA”, line 345
ORA-06512: at “SYS.DBMS_METADATA”, line 410
ORA-06512: at “SYS.DBMS_METADATA”, line 449
ORA-06512: at “SYS.DBMS_METADATA”, line 1156
ORA-06512: at “SYS.DBMS_METADATA”, line 1141
ORA-06512: at line 1