CODEWITHSUNDEEP
oracle-databaseimportexport
Lotzi11
Lotzi11

Reputation: 549

Oracle Importing/Exporting with Command Line

I am trying to learn how to import/export data into Oracle using the command line. From what I found, it looks like I should use the sqlldr.exe file in order to import and export, but I'm not sure what parameters are needed other than userid. Could someone explain to me what parameters are necessary and what are optional?

Upvotes: 2

Views: 36506

Answers (3)

mehmet sahin
mehmet sahin

Reputation: 812

Follow this steps:

EXPORT:

1- Create a export directory on source server. mkdir /path/path

2- Grant oracle user. chown oracle /path/path

3- Create a directory in database. CREATE DIRECTORY Your_Dir_Name as '/path/path';

4- Add your Oracle user to EXP_FULL_DATABASE role. Grant EXP_FULL_DATABASE to your_user;

5- Grant your created directory in database to role. GRANT READ, WRITE ON DIRECTORY Your_Dir_Name TO EXP_FULL_DATABASE ;

6- Execute expdp command with oracle user. expdp your_db_user/password schemas=Your_Schema_Name tables=table_name directory=Your_Dir_Name version=your_version_for_target_db dumpfile=data.dmp logfile=data.log (EXPDP command takes a lot of parameter I wrote examples. check all parameters https://oracle-base.com/articles/10g/oracle-data-pump-10g)

IMPORT:

1- Create a import directory on target server. mkdir /path/path

2- Grant oracle user. chown oracle /path/path

3- Create a directory in target database. CREATE DIRECTORY Your_Dir_Name as '/path/path';

4- Add your Oracle user to IMP_FULL_DATABASE role. Grant IMP_FULL_DATABASE to your_user;

5- Grant your created directory in database to role. GRANT READ, WRITE ON DIRECTORY Your_Dir_Name TO IMP_FULL_DATABASE ;

6- Execute impdp command with oracle user. impdp your_db_user/password directory=Your_Dir_Name dumpfile=data.dmp logfile=data.log (IMPDP command takes a lot of parameter I wrote examples. check all parameters https://oracle-base.com/articles/10g/oracle-data-pump-10g)(If you want rename schema,tablespace,table use remap parameter).

Upvotes: 3

Lotzi11
Lotzi11

Reputation: 549

After talk to mehmet sahin, we found that the following command will import into Oracle.

imp user/pwd file=[Path to dmp file]\import.dmp full=y

You can export with the following command.

exp user/pwd file=[Path to dmp file]\export.dmp

Both commands will also take in .exp files.

Upvotes: 2

gvenzl
gvenzl

Reputation: 1891

There are a couple of ways how to export/import data in Oracle. The tool you mentioned, sqlldr is called SQL*Loader. You can also and actually should use Oracle Data Pump, the Export/Import utility which superseded the old Export/Import tool. All of those tools are documented in full in the Database Utilities book of the Oracle Database Documentation.

Upvotes: 2

Related Questions