Oracle Database Export

Hello,Step by step Oracle database export Schema, Tables, Procedure, Full Database etc.

For the examples to work we must first unlock the SYS account and create a directory object it can access.

The directory object is only a pointer to a physical directory, creating it does not actually create the physical directory on the file system of the database server.

We are creating a new directory for export dumpfile .  We authorize for ExpImp folder.

ermanTestDB:/oracle/export#mkdir -p /export/ExpImp
ermanTestDB:/oracle/export#cd /export/ExpImp/
ermanTestDB:/export/ExpImp#ls -la
total 0
drwxr-xr-x    2 oracle   dba             256 Jan 05 12:59 .
drwxr-xr-x    3 oracle   dba             256 Jan 05 12:59 ..
ermanTestDB:/export/ExpImp#chmod -R 777 .
ermanTestDB:/export/ExpImp#chmod -R 777 ..
ermanTestDB:/export/ExpImp#ls -la
total 0
drwxrwxrwx    2 oracle   dba             256 Jan 05 12:59 .
drwxrwxrwx    3 oracle   dba             256 Jan 05 12:59 ..
ermanTestDB:/export/ExpImp#

What is the Oracle Directory ?

An oracle directory  is a database object pointing to a operating system directory on the database server machine for reading and writing files.

Connect to sqlplus then run this script

CONN / AS SYSDBA
ALTER USER sysIDENTIFIED BY password ACCOUNT UNLOCK;
CREATE OR REPLACE DIRECTORY DBA  AS '/export/ExpImp';
GRANT READ, WRITE ON DIRECTORY DBA TO sys;

Or show Oracle Directoryies.

SELECT * FROM DBA_DIRECTORIES;

Now we starting database export ( Table, Schema, Trigger , Procedure , Function etc. )

We createting a new folder for Export ımport Scripts.

ermanTestDB:#mkdir scripts
ermanTestDB:#cd scripts
ermanTestDB:/export/ExpImp#ls -la
total 0
drwxr-xr-x    2 oracle   dba             256 Jan 05 12:59 .
drwxr-xr-x    3 oracle   dba             256 Jan 05 12:59 ..
ermanTestDB:#chmod -R 777 .
ermanTestDB:#chmod -R 777 ..
ermanTestDB:#ls -la
total 0
drwxrwxrwx    2 oracle   dba             256 Jan 05 12:59 .
drwxrwxrwx    3 oracle   dba             256 Jan 05 12:59 ..
ermanTestDB:#

We create empty touch.sh file which  name is erman.sh

ermanTestDB:/home/oracle/scripts#touch erman.sh
ermanTestDB:/home/oracle/scripts#ls
erman.sh              impXdslBf2_tbl.sh     nohup.out
expTah.sh             impXdslBf_tbl.sh      xdsl_bau_derleme.sql
ermanTestDB:/home/oracle/scripts#

syntax :

expdp username/password@DB_SID or userid=\”/ as sysdba\”
tables=Test
directory=DBA
dumpfile=dump_01052017.dmp
logfile=log_01052015.log

expdp userid=\"/ as sysdba\" tables=Test directory=DBA dumpfile=dump_01052015.dmp logfile=logfile_01052015.log

connect to nohup and run this .sh file

ermanTestDB:/home/oracle/scripts#vi erman.sh
percuermanTestDBla40:/home/oracle/scripts#nohup sh erman.sh &

Just open nohup.out to see output.

ermanTestDB:/home/oracle/scripts#tail -123f nohup.out

Export: Release 11.2.0.3.0 - Production on Fri Jan 6 12:30:56 2017

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
Master table "SYS"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_TABLE_01":  userid="/******** AS SYSDBA" DIRECTORY=IMPDIR DUMPFILE=dump_01052015.dmp LOGFILE=logfile_01052015.log  TABLES=Test
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . exported"CRM"."KURULUM_INSTANCE"                    5.445 KB       1 rows
. . exported"CRM"."OT_ISLEM_INSTANCE"                   5.515 KB       4 rows
Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "SYS"."SYS_IMPORT_TABLE_01" successfully completed at 12:33:01

This will export  CRM. KURULUM_INSTANCE and CRM.OT_ISLEM_INSTANCE .

dump and logfile location  /export/ExpImp

ermanTestDB:/export/ExpImp#ls -la
dumpfile=dump_01052017.dmp
logfile=log_01052015.log

Schema Export :

expdp userid=\"/ as sysdba\" schemas=name directory=name dumpfile=name.dmp logfile=name.log

Full Database Export :

expdp userid=\"/ as sysdba\"  full=Y directory=name dumpfile=name.dmp logfile=name.log

Tables Export :

expdp userid=\"/ as sysdba\"  tables=Owner.TableName,Owner.TableName directory=name dumpfile=name.dmp logfile=name.log

INCLUDE and EXCLUDE

expdp userid=\"/ as sysdba\"  schemas=name include=TABLE:"IN ('name', 'name')" directory=name dumpfile=name.dmp logfile=name.log
expdp userid=\"/ as sysdba\"  schemas=name exclude=TABLE:"= 'name'" directory=name dumpfile=name.dmp logfile=name.log

Parallel export Syntax

expdp userid=\"/ as sysdba\"
directory=dir_test
dumpfile=test_tables%u.dmp
logfile=test_tables.log
parallel=8

Hits: 2

Leave a Reply

Your email address will not be published. Required fields are marked *