Using Data Pump Export(expdp) Over Network

Data Pump Export can be used over Networks. Normally when data pump export utility(expdp) executes, it creates a dump file to the server where the database is installed. You can DIRECTORY parameters in order to specify operating system file path.
Suppose that you have 2 databases(srv1db and srv2db) in 2 different servers(srv1 and srv2). If you run expdp on srv2db, dump file shall be created in srv2 server. You can also run expdp on srv1db and dump file shall be created in srv2 server, with NETWORK_LINK expdp parameter. See demostration below:


--srv1db
SQL> select DIRECTORY_PATH from all_directories where directory_name = 'DATA_PUMP_DIR';

DIRECTORY_PATH
--------------------------------------------------------------------------------
/usr/users/oracle/DP

SQL>


--srv2db
SQL> select DIRECTORY_PATH from all_directories where directory_name = 'DATA_PUMP_DIR';

DIRECTORY_PATH
--------------------------------------------------------------------------------
/home/oracle/DP

SQL>

---srv1
uccs01 oracle@srv1:/usr/users/oracle/DP> ls -ltr


--srv2
bash-3.00$ ls -ltr /home/oracle/DP




--expdp on srv2
bash-3.00$ expdp mte/mte@srv1db tables=t directory=DATA_PUMP_DIR dumpfile=mte_t.dmp logfile=mte_t.log

Export: Release 10.2.0.2.0 - 64bit Production on Wednesday, 13 August, 2008 10:15:53

Copyright (c) 2003, 2005, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
Starting "MTE"."SYS_EXPORT_TABLE_01": mte/********@srv1db tables=t directory=DATA_PUMP_DIR dumpfile=mte_t.dmp logfile=mte_t.log
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "MTE"."T" 5.125 KB 31 rows
Master table "MTE"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for MTE.SYS_EXPORT_TABLE_01 is:
/usr/users/oracle/DP/mte_t.dmp
Job "MTE"."SYS_EXPORT_TABLE_01" successfully completed at 10:12:11



---srv1
uccs01 oracle@srv1:/usr/users/oracle/DP> ls -ltr
total 2455
-rw-r----- 1 oracle dba 69632 Aug 13 10:12 mte_t.dmp
-rw-r--r-- 1 oracle dba 981 Aug 13 10:12 mte_t.log
uccs01 oracle@srv1:/usr/users/oracle/DP>


--srv2
bash-3.00$ ls -ltr /home/oracle/DP




--srv2db, create a dblink for srv1db
SQL>create public database link srv1db_LINK.ORC
connect to USER1
using 'srv1db.ORC';


--expdp on srv2
bash-3.00$ expdp USER1/USER1@srv2db tables=t directory=DATA_PUMP_DIR dumpfile=mte_t2.dmp logfile=mte_t2.log network_link=srv1db_link

Export: Release 10.2.0.2.0 - 64bit Production on Wednesday, 13 August, 2008 10:22:09

Copyright (c) 2003, 2005, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
Starting "USER1"."SYS_EXPORT_TABLE_01": USER1/********@srv2db tables=t directory=DATA_PUMP_DIR dumpfile=mte_t2.dmp logfile=mte_t2.log network_link=srv1db_link
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "USER1"."T" 5.351 KB 16 rows
Master table "USER1"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for USER1.SYS_EXPORT_TABLE_01 is:
/home/oracle/DP/mte_t2.dmp
Job "USER1"."SYS_EXPORT_TABLE_01" successfully completed at 10:22:45

bash-3.00$



---srv1
uccs01 oracle@srv1:/usr/users/oracle/DP> ls -ltr
total 2455
-rw-r----- 1 oracle dba 69632 Aug 13 10:12 mte_t.dmp
-rw-r--r-- 1 oracle dba 981 Aug 13 10:12 mte_t.log
uccs01 oracle@srv1:/usr/users/oracle/DP>


--srv2
bash-3.00$ ls -ltr /home/oracle/DP
-rw-r--r-- 1 oracle oinstall 0 Aug 13 10:22 mte_t2.log
-rw-r----- 1 oracle oinstall 4096 Aug 13 10:22 mte_t2.dmp
bash-3.00$

Workaround for ORA-39014, ORA-12801, ORA-29913

There in an Oracle Bug(5472417 , Note:438608.1) about parallelism mechanism of data pump export. Workaround fort his may be removing parallelism parameter from export(running as a single process)

bash-3.00$ expdp MT/MT full=y directory=DATA_PUMP_DIR dumpfile=orcl_full_imp_p%u.dmp logfile=orcl_full_imp.log parallel=4 network_link=orcl_link

Export: Release 10.2.0.2.0 - 64bit Production on Wednesday, 13 August, 2008 10:49:49

Copyright (c) 2003, 2005, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
Starting "MT"."SYS_EXPORT_FULL_01": MT/******** full=y directory=DATA_PUMP_DIR dumpfile=orcl_full_imp_p%u.dmp logfile=orcl_full_imp.log parallel=4 network_link=orcl_link
Estimate in progress using BLOCKS method...
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 13.06 GB
Processing object type DATABASE_EXPORT/TABLESPACE
Processing object type DATABASE_EXPORT/PASSWORD_VERIFY_FUNCTION
Processing object type DATABASE_EXPORT/PROFILE
Processing object type DATABASE_EXPORT/SYS_USER/USER
Processing object type DATABASE_EXPORT/SCHEMA/USER
Processing object type DATABASE_EXPORT/ROLE
Processing object type DATABASE_EXPORT/GRANT/SYSTEM_GRANT/PROC_SYSTEM_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/GRANT/SYSTEM_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/ROLE_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/DEFAULT_ROLE
Processing object type DATABASE_EXPORT/SCHEMA/TABLESPACE_QUOTA
Processing object type DATABASE_EXPORT/RESOURCE_COST
Processing object type DATABASE_EXPORT/SCHEMA/DB_LINK
Processing object type DATABASE_EXPORT/TRUSTED_DB_LINK
Processing object type DATABASE_EXPORT/SCHEMA/SEQUENCE/SEQUENCE
Processing object type DATABASE_EXPORT/SCHEMA/SEQUENCE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type DATABASE_EXPORT/DIRECTORY/DIRECTORY
Processing object type DATABASE_EXPORT/DIRECTORY/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type DATABASE_EXPORT/DIRECTORY/GRANT/WITH_GRANT_OPTION/OBJECT_GRANT
Processing object type DATABASE_EXPORT/DIRECTORY/GRANT/CROSS_SCHEMA/OBJECT_GRANT
Processing object type DATABASE_EXPORT/CONTEXT
Processing object type DATABASE_EXPORT/SCHEMA/PUBLIC_SYNONYM/SYNONYM
Processing object type DATABASE_EXPORT/SCHEMA/SYNONYM
Processing object type DATABASE_EXPORT/SCHEMA/TYPE/TYPE_SPEC
Processing object type DATABASE_EXPORT/SCHEMA/TYPE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/PRE_SYSTEM_ACTIONS/PROCACT_SYSTEM
Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/POST_SYSTEM_ACTIONS/PROCACT_SYSTEM
Processing object type DATABASE_EXPORT/SCHEMA/PROCACT_SCHEMA
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/PRE_TABLE_ACTION
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/GRANT/CROSS_SCHEMA/OBJECT_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/INDEX
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/CONSTRAINT/CONSTRAINT
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/COMMENT
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/RLS_POLICY
Processing object type DATABASE_EXPORT/SCHEMA/PACKAGE/PACKAGE_SPEC
Processing object type DATABASE_EXPORT/SCHEMA/PACKAGE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/FUNCTION/FUNCTION
Processing object type DATABASE_EXPORT/SCHEMA/FUNCTION/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/PROCEDURE/PROCEDURE
Processing object type DATABASE_EXPORT/SCHEMA/PROCEDURE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPEC
Processing object type DATABASE_EXPORT/SCHEMA/FUNCTION/ALTER_FUNCTION
Processing object type DATABASE_EXPORT/SCHEMA/PROCEDURE/ALTER_PROCEDURE
Processing object type DATABASE_EXPORT/SCHEMA/VIEW/VIEW
Processing object type DATABASE_EXPORT/SCHEMA/VIEW/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/VIEW/GRANT/CROSS_SCHEMA/OBJECT_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/VIEW/COMMENT
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type DATABASE_EXPORT/SCHEMA/PACKAGE_BODIES/PACKAGE/PACKAGE_BODY
Processing object type DATABASE_EXPORT/SCHEMA/TYPE/TYPE_BODY
Processing object type DATABASE_EXPORT/SCHEMA/JAVA_CLASS/JAVA_CLASS
Processing object type DATABASE_EXPORT/SCHEMA/JAVA_RESOURCE/JAVA_RESOURCE
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/FUNCTIONAL_AND_BITMAP/INDEX
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/STATISTICS/FUNCTIONAL_AND_BITMAP/INDEX_STATISTICS
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/POST_TABLE_ACTION
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TRIGGER
Processing object type DATABASE_EXPORT/SCHEMA/VIEW/TRIGGER
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/POST_INSTANCE/PROCACT_INSTANCE
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/POST_INSTANCE/PROCDEPOBJ
Processing object type DATABASE_EXPORT/SCHEMA/POST_SCHEMA/PROCOBJ
Processing object type DATABASE_EXPORT/SCHEMA/POST_SCHEMA/PROCACT_SCHEMA
ORA-39014: One or more workers have prematurely exited.
ORA-39029: worker 4 with process name "DW04" prematurely terminated
ORA-31671: Worker process DW04 had an unhandled exception.
ORA-12801: error signaled in parallel query server
ORA-29913: error in executing ODCIEXTTABLEPOPULATE callout
ORA-29400: data cartridge error
KUP-04038: internal error: kupax-meta1
ORA-06512: at "SYS.ORACLE_DATAPUMP", line 78
ORA-06512: at "SYS.KUPW$WORKER", line 1345
ORA-06512: at line 2

Job "MT"."SYS_EXPORT_FULL_01" stopped due to fatal error at 11:13:47

Solution of ORA-39002, ORA-39070, ORA-39087

The errors that i mentioned in the subject, can be occured during data pump export/import(expdp/impdp executables). If the directory object does not have necessary rigths, will fire an exception. Giving rights(read - write) will be a solution.

bash-3.00$ expdp mte/mte@test02 tables=t directory=DATA_PUMP_DIR dumpfile=mte_t.dmp logfile=mte_t.log

Export: Release 10.2.0.2.0 - 64bit Production on Wednesday, 13 August, 2008 10:09:48

Copyright (c) 2003, 2005, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
ORA-39002: invalid operation
ORA-39070: Unable to open the log file.
ORA-39087: directory name DATA_PUMP_DIR is invalid




>sqlplus
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.2.0
Connected as HR

SQL> conn mte/mte@test02;
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.2.0
Connected as mte

SQL> select directory_name from all_directories;

DIRECTORY_NAME
------------------------------
HLR_DIR

SQL> conn HR/HR@test02;
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.2.0
Connected as HR

SQL> grant read, write on directory DATA_PUMP_DIR to mte;

Grant succeeded

SQL> conn mte/mte@test02;
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.2.0
Connected as mte

SQL> select directory_name from all_directories;

DIRECTORY_NAME
------------------------------
DATA_PUMP_DIR
HLR_DIR

SQL>



bash-3.00$ expdp mte/mte@test02 tables=t directory=DATA_PUMP_DIR dumpfile=mte_t.dmp logfile=mte_t.log

Export: Release 10.2.0.2.0 - 64bit Production on Wednesday, 13 August, 2008 10:15:53

Copyright (c) 2003, 2005, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
Starting "MTE"."SYS_EXPORT_TABLE_01": mte/********@test02 tables=t directory=DATA_PUMP_DIR dumpfile=mte_t.dmp logfile=mte_t.log
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "MTE"."T" 5.125 KB 31 rows
Master table "MTE"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for MTE.SYS_EXPORT_TABLE_01 is:
/usr/users/oracle/DP/mte_t.dmp
Job "MTE"."SYS_EXPORT_TABLE_01" successfully completed at 10:12:11

How to Export Table Data to Flat File :: Oracle UnLoader, Text File Exporter

As every developer knows, data always transfers between platforms. One advantage of XML is also exchanging and/or transferring data. One day, your boss may want to see some sales data in excel sheet. So, if you have a program that exports data in xls format, this will not be problem; but if you do not have one, you may some how fullfill the requirements, if you do not want loose your job :)
 
Using SQL*Plus may be one of solutions. If you have to do it programatically, you can use Oracle's scheduler. First you have to create an SQL file that specifies how exporting will be. One example may be:
set heading off;
set feedback off;
spool e:\log.txt
select * from &1;
spool off;
exit;
If you save above code E:\exp_table.sql as, you are ready to execute scheduler:
--create program
BEGIN
DBMS_SCHEDULER.create_program(program_name => 'EXP_DATA_PRG',
program_type => 'EXECUTABLE',
program_action => 'E:\oracle\product\10.2.0\db_2\BIN\sqlplus.exe hr/hr@ORCL@"E:\exp_table.sql" jobs',
number_of_arguments => 0,
enabled => FALSE,
comments => 'Export data Program');
END;
/

--create job
BEGIN
DBMS_SCHEDULER.create_job(job_name => 'EXP_DATA_JOB',
program_name => 'EXP_DATA_PRG',
start_date => NULL,
repeat_interval => NULL,
end_date => NULL,
enabled => FALSE,
auto_drop => FALSE,
comments => 'Export data Job');
END;
/
--enable program and job
BEGIN
DBMS_SCHEDULER.enable(NAME => 'EXP_DATA_PRG');
DBMS_SCHEDULER.enable(NAME => 'EXP_DATA_JOB');
END;
/
--run job
BEGIN
DBMS_SCHEDULER.RUN_JOB(job_name => 'EXP_DATA_JOB',use_current_session => TRUE);
END;
/
After job executes, e:\log.txt will be your export file. You can play with parameters, as your requirements.
 
As you think (like me) this is not a good solution. So, i started to write some codes to generate more flexible text exporter for Oracle. I developed a package, UTL_EXP, for this job.
There are two program in this package. Specs are below:
FUNCTION ExportTableData
(
--table name that will be exported
pis_TableName IN ALL_TABLES.TABLE_NAME%TYPE,
--filter criteria,if exists
pis_WhereCondition IN VARCHAR2 DEFAULT NULL,
--if table has more columns how fields will be seperated
pis_FieldDelimiter IN VARCHAR2 DEFAULT ',',
--row delimiter, CHR(10) for newline
pis_RecordDelimiter IN VARCHAR2 DEFAULT CHR(10),
--if some columns will not be included
pis_ExceptedFieldList IN VARCHAR2 DEFAULT NULL,
--1 for fields enclosed by ", such as "column_value"
pin_FieldEnclosedByQuote IN NUMBER DEFAULT 0,
--if column name will not be included, set as 0
pin_IncludeColumnName IN NUMBER DEFAULT 0,
--if field names included
pis_FieldNameDelimiter IN VARCHAR2 DEFAULT ':'
) RETURN CLOB;
PROCEDURE ExportFile
(
--oracle directory name
pis_DirectoryName in VARCHAR2,
--file name
pis_FileName in VARCHAR2,
--table name that will be exported
pis_TableName IN ALL_TABLES.TABLE_NAME%TYPE,
--filter criteria,if exists
pis_WhereCondition IN VARCHAR2 DEFAULT NULL,
--if table has more columns how fields will be seperated
pis_FieldDelimiter IN VARCHAR2 DEFAULT ',',
--row delimiter, CHR(10) for newline
pis_RecordDelimiter IN VARCHAR2 DEFAULT CHR(10),
--if some columns will not be included
pis_ExceptedFieldList IN VARCHAR2 DEFAULT NULL,
--1 for fields enclosed by ", such as "column_value"
pin_FieldEnclosedByQuote IN NUMBER DEFAULT 0,
--if column name will not be included, set as 0
pin_IncludeColumnName IN NUMBER DEFAULT 0,
--if field names included
pis_FieldNameDelimiter IN VARCHAR2 DEFAULT ':'
) ;
 
On exporting data you have some options:

• specify some criterias
• not to export some columns
• field and row delimiters
• put column values between quotes
• include table column name

A simple demostration will show how to use UTL_EXP package.
 
SQL> drop table t;
Table dropped
SQL> create table t(i number, v varchar2(12), d date);
Table created
SQL> insert into t values(1,'test data 1', sysdate + 1);
1 row inserted
SQL> insert into t values(2,'test data 2', sysdate + 2);
1 row inserted
SQL> insert into t values(3,'test data 3', sysdate + 3);
1 row inserted
SQL> SELECT * FROM t;
I V D
---------- ------------ -----------
1 test data 1 08.07.2007
2 test data 2 09.07.2007
3 test data 3 10.07.2007
SQL> set long 10000000;
SQL> SELECT UTL_EXP.ExportTableData('t','i > 0',';',chr(10),'i',1, 0,null) FROM dual;
UTL_EXP.EXPORTTABLEDATA('T','I
--------------------------------------------------------------------------------
"test data 1";"08/07/2007";
"test data 2";"09/07/2007";
"test data 3";"10/07/2007";

SQL> DECLARE
2 c CLOB;
3 BEGIN
4 c := UTL_EXP.ExportTableData('t','i > 0',';',chr(10),'i',1, 0,null);
5 utl_utl.PutLine(c);
6 END;
7 /
PL/SQL procedure successfully completed
SQL> SELECT DIRECTORY_NAME FROM all_directories;
DIRECTORY_NAME
------------------------------
DATA_PUMP_DIR
ORACLECLRDIR
TEST_FILE_DIR
 
SQL> BEGIN
2 UTL_EXP.ExportFile('TEST_FILE_DIR', 't.csv','t','i > 0',';',chr(10),'i',1, 0,null);
3 END;
4 /
PL/SQL procedure successfully completed
SQL>
After executing the procedure t.csv will contain:
"test data 1";"08/07/2007";
"test data 2";"09/07/2007";
"test data 3";"10/07/2007";
You can generate more complex export files with UTL_EXP package.

Oracle Export Import Utilities

Oracle has powerful utilities such as export and import for transferring data between different hardware/software platfroms. In $ORACLE_HOME/BIN directory exp and imp utilities are used for this fashion.

Oracle içinde verilerin başka platformlara taşınması için export ve import kullanılmaktadır. Bu sayede veriler gerek yazılım gerek donanım açısından farklı sistemler arasında taşınabilmektedir. ORACLE_HOME dizini içindeki BIN dizinde exp ve imp adlı programcıklar bu işlemler için kullanılmaktadır.

Transfer operation has done via files which called dump. Dump files are created with exp and can be loaded via emp utilities. The important thing is that transferring of dump files must be done via binary mode ftp. Otherwise dump file can be corrupted. For ASCII files you are able to use SQL*Loader utility.

Taşınma işlemi binary formatında dump adı verilen dosyalar ile yapılmaktadır. Bu dosyalar exp ile oluşturulup imp ile istenen yere yüklenebilecek şekildedir. Oluşturulan dump dosyası ftp aracılığı ile taşınıyor ise, bu taşımayı binary ftp modu ile yapmak gerekmektedir. Aksi takdirde dosyanın bozulmasına neden olunabilinir. Buna dikkat edilmelidir. ASCII formatında taşıma için SQL*Loader kullanılmaktadır.

With export the object and all of its dependent objects are taken. You can backup your database via export/import whereas it is not recommended. For backup issues utilities such as RMAN might be used.

Export ile alacağınız nesne ile birlikte ona bağlı bütün neseneler de alınır. Java kaynak dosyalarını export ile almak mümkün değildir. Full bir export ile veritabanının bir nevi backup alınmış olsa da bu tavsiye edilen bir yol değildir. Büyük veritabanları için RMAN gibi özelleşmiş araçlar kullanılmalıdır.

You can detect any of corruption data with export.

Veritabanı içindeki verilerin okunamaması(data corruption) gibi bir durumun tespit edilebilmesi için de export kullanılabilir.

Transferring data with SCN or time with setting FLASHBACK_SCN, FLASHBACK_TIME parameters.

Export ile belirli bir SCN’e veya zamana göre de taşıma yapılabilmektedir. Bunun için gerekli parametrelerin uygun bir şekilde çağırılması gerekir.(FLASHBACK_SCN, FLASHBACK_TIME)

Not only full table, data based on condition can be exported.

Sadece belirli bir tablonun değil, tablo içinde belirli kritere bağlı kayıtların da gelmesi sağlanabilmektedir.(QUERY)

You can increase performance of export/import via defining COMMIT and ANALYZE parameters.

Export edilirken commit edilip edilmemesini veya ilgili nesnelerin analiz edilip edilmemesi belirlenebilmektedir.(COMMIT, ANALYZE) Bu kimi zaman performans arttrıcı olarak kullanılabilmektedir.

I described how to export a table from Microsoft system to Unix system below:

Aşağıda Microsoft tabanlı bir sistemden bulunan bir tablonun belirli kayıtlarının export edilip Unix tabanlı bir sisteme nasıl import edildiği belirtilmiştir.

On Windows side a table is created

Windows tarafında bir tablo oluşturulur:

create table hr.sample_emp as select * from hr.employees

Parameter file(exp_params.txt) is cretaed

Export ile kullanılacak parametre dosyası(exp_params.txt) hazırlanır.

FILE=dept50_emp.dmp

LOG=dept50_emp_exp.log

QUERY='where department_id = 50'

TABLES=hr.sample_emp

ROWS=y

COMPRESS=y

With this file

Bu dosya ile

select * from hr.sample_emp 'where department_id = 50

query result taken from hr schema and exported to dept50_emp.dmp file.

sorgusu ile gelecek kayıtları ve hr.sample_emp tablo yapısı ile birlikte dept50_emp.dmp dosyasına export edileceği belirtilmektedir.

With command prompt needed calls are done

Command prompt açılır ve gerekli çağırımlar yapılarak işlemin yapılması sağlanır:

E:\oracle\product\10.2.0\db_2\BIN>exp 'sys/orcl@ORCL as SYSDBA' PARFILE=exp_params.txt

Export: Release 10.2.0.1.0 - Production on Wed Aug 16 14:21:38 2006

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, OLAP and Data Mining options

Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set

About to export specified tables via Conventional Path ...

Current user changed to HR

. . exporting table SAMPLE_EMP 45 rows exported

Export terminated successfully without warnings.

E:\oracle\product\10.2.0\db_2\BIN>

exp 'sys/orcl@ORCL as SYSDBA' PARFILE=exp_params.txt

line does export operation

satırı export işlemini yapmaktadır.

dept50_emp_exp.log and dept50_emp.dmp files are created via this command.

Bu komut ile dept50_emp_exp.log ve dept50_emp.dmp adlı dosyalar oluşmaktadır.

For import, dept50_emp.dmp file is transferred via binary mode ftp.

Import için dept50_emp.dmp dosyası Unix ortamına binary modda ftp ile atılır.

Parameter file(imp_params.txt) is created for import.

İmport işlemi için kullanılacak parametre dosyası(imp_params.txt) hazırlanır.

FILE=dept50_emp.dmp

SHOW=n

IGNORE=n

GRANTS=y

FROMUSER=hr

TOUSER=sys

TABLES=sample_emp

ANALYZE=n

LOG=dept50_emp_imp.log

Via this file, sample_emp table from hr schema imported to sys schema.

Bu dosya ile hr şemasından alınan sample_emp adlı tablonun sys şeması altına alınacağı belirtilmiştir.

On Unix environment needed sheel calls are done

Unix ortamından açılan bir shell ile gerekli komutllar çağırılır:

oracle@zerg_shp01> imp \'sys/general@general as sysdba\' PARFILE=imp_params.txt

Import: Release 10.1.0.2.0 - Production on Wed Aug 16 14:16:43 2006

Copyright (c) 1982, 2004, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production

With the Partitioning, OLAP and Data Mining options

Export file created by EXPORT:V10.02.01 via conventional path

import done in WE8ISO8859P9 character set and AL16UTF16 NCHAR character set

import server uses WE8ISO8859P1 character set (possible charset conversion)

export client uses WE8MSWIN1252 character set (possible charset conversion)

IMP-00008: unrecognized statement in the export file:

. . importing table "SAMPLE_EMP" 45 rows imported

Import terminated successfully with warnings.

oracle@zerg_shp01>

imp \'sys/sys@general as sysdba\' PARFILE=imp_params.txt

import has been done via this command

komutu ile import işlemi yapılmıştır.

With a query you can check the process.

Veritabanından bir sorgu ile işlemin doğru yapılıp yapılmadığı kontrol edilebilir:

select * from sys.sample_emp

For more information please visit

http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14215/exp_imp.htm