-1

I have database that i want export from one machine and import into another machine using sqlplus on linux terminal.

I have tried nothing

1
  • sqlplus is for issuing commands, running scripts, invoking procedures, etc.. it is not for data loading or unloading. Data loading is done with SQL*Loader or external tables, data unloading is done with client libraries (not Oracle provided), and surgical data migration is done with expdp/impdp, or custom CTASs over dblinks, or even, if the OS is identical, just copy all the datafiles, redo log files, control files, etc... using OS copy commands (while the source DB is shut down, of course).
    – Paul W
    May 13, 2023 at 11:45

2 Answers 2

3

SQL*Plus is a wrong tool for that.

It is Data Pump you need: export (on source database) and import (on target database).

Oracle 21 documentation is here so - have a look.

0

For exporting and importing you can use Datapump utility.

1. Create Data Pump Directory

The first step in Oracle Data Pump is to create an OS level directory which will be used by Oracle for performing exports and imports. Create directory at OS level

mkdir -p /u02/dp_exp_dir

2. Create directory inside the database

SQL> create directory datapump as '/u02/dp_exp_dir';

3. Grant permissions on directory

SQL> grant read,write on directory datapump to scott; 

4. View directory information

SQL> select * from dba_directories;
  • Get help on expdp or impdp utility

    expdp help=y impdp help=y

Take database level export

$ expdp directory=datapump dumpfile=fullprod.dmp logfile=fullprod.log full=y

Import full database

-- On source
SQL> select name from v$tablespace;
 
-- On target
SQL> select name from v$tablespace;
 

Create missing tablespaces on target, Make sure target tablespace has enough free space

$ impdp directory=datapump dumpfile=fullprod.dmp logfile=imp_fullprod.log full=y

Hope it helped !! :)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.