Hi guys, in this blog I am going to explain you about In-Depth Oracle Import and Export.
Export In 0racle-
In the context of databases, “export” refers to the process of extracting data from a database and saving it to an external file or another storage format. This process allows you to create a snapshot of the data in a form that can be easily transported or backed up. In Oracle databases, the export process is typically performed using utilities like EXP (export) or EXPDP (Data Pump Export).
Here are some common use cases for exporting data in Oracle:
Backup and Recovery-
Creating a backup of the database by exporting its contents allows for recovery in case of data loss or database corruption. The exported data can be later used to restore the database to a specific point in time.
Data Migration: –
When moving data from one Oracle database to another, exporting data from the source database and then importing it into the target database is a common practice. This is useful during database upgrades, system migrations, or when consolidating data from multiple sources.
Data Archiving-
Archiving historical data is often done by exporting older records to separate files. This helps in managing the size of the active database and allows for more efficient data retrieval.
Sharing Data-
Exporting data to a portable format makes it easy to share datasets with others or transfer data between different systems and environments.
Database Cloning-
Exporting and then importing a database or specific schemas allows for creating a copy (clone) of a database. This is useful for creating test environments that mimic the structure and data of the production database.
Create Data pump Directory-
The creation of an OS level directory, which Oracle will utilize for exports and imports, is the first step in using Oracle Data Pump. At the OS level, create a directory.
mkdir -p /u05/do_exp_dir
Create directory inside the database-
create directory datapump as ‘/u02/dp_exp_dir’;
Grant permissions on director-
grant read,write on directory datapump to scott;
View directory information
select * from dba_directories;
Import in Oracle-
The process of loading data into a database from an external source is referred to as “import” in the context of databases. It is the inverse of “export,” which is the process of removing data from a database and saving it in an external file or another storage type.
The import process is commonly used for the following purposes:
Restoring Database Backups-
If you previously exported a database or specific data, you can use the import method to restore the data from the exported files. This is critical for recovery and backup plans.
Migrating Data-
When migrating data from one database system to another or moving data across instances, the import process can be used to get data into the new environment.
Importing Data-
Importing data is frequently part of the initial setup of a database. Instead of entering enormous datasets manually, you can prepare them in an external file and then import them into the database.
Data Warehousing-
Import is used in data warehousing to load data into the data warehouse from numerous source systems.
In Oracle databases, the import process is typically performed using utilities like IMP (import) or IMPDP (Data Pump Import). These tools allow you to bring data from dump files, which are generated during the export process, back into the database.
Table Level Import and Export-
Step 1- To Check the Process in Oracle like pmon.
[oracle@clota-labl08 ~]$ ps -ef | grep -i pmon
oracle 2207 1 0 Sep26 ? 00:00:05 ora_pmon_clotaDB
oracle 43671 37353 0 04:47 pts/4 00:00:00 grep –color=auto -i pmon
Step 2 – To Set the Environment for the Oracle
[oracle@clota-labl08 ~]$ . oraenv
ORACLE_SID = [DB1] ? clotaDB
The Oracle base remains unchanged with value /u01/app/oracle
Step 3 – Login as sys account.
[oracle@clota-labl08 ~]$ sqlplus / as sysdba
Step 4 – With Oracle Create an individual User.
SQL> create user Abhi10 identified by Abhi10;
Step 5 – Give grant to create session for particular User
SQL> grant create session to Abhi10;
Step 6- Give grant to create table
SQL> grant create table to Abhi10;
Step 7 – Give unlimited Quota for users’ tablespace for particular user.
SQL> alter user Abhi10 quota unlimited on users;
Step 8- In an Oracle instance, create a table.
SQL> create table Abhi10.emp (id NUMBER PRIMARY KEY, name VARCHAR2(50), age NUMBER);
Table created.
SQL> create table Abhi10.emp (emp_id NUMBER PRIMARY KEY, department VARCHAR2(50), salary NUMBER);
Table created.
Step 9- Insert some values in Abhi.10emp table.
SQL> INSERT INTO Abhi10.emp (id, name, age) VALUES (1, ‘John’, 25);
SQL> set lines 300 pages 300/
SQL> Select * from Abhi10.emp;
ID NAME AGE
———- ————————————————– ———-
1 John 25
2 Alice 30
3 Ali 38
4 Jv 35
5 Glen 32
Step 10- Describe dba directories
SQL> desc dba_directories;
SQL> set lines 300 pages 300
SQL> col OWNER for a20
SQL> col DIRECTORY_NAME for a20
SQL> col DIRECTORY_PATH for a20
SQL> /
OWNER DIRECTORY_NAME DIRECTORY_PATH
——————– ——————– ——————–
SYS EXPORT_IMPORT /u03/backup/
SYS SDO_DIR_WORK
SYS SDO_DIR_ADMIN /u05/app/oracle/product/19.3.0.0/dbhome_1/md/admin
SYS XMLDIR /u05/app/oracle/product/19.3.0.0/dbhome_1/rdbms/xml
SYS XSDDIR /u05/app/oracle/product/19.3.0.0/dbhome_1/rdbms/xml/schema
Step 11- Consider the export of the Oracle-compatible Schemas.
[oracle@clota-labl08 ~]$ expdp directory=EXPORT_IMPORT dumpfile=Abhi10_export.dmp logfile=Abhi10_export.log tables=’Abhi10.emp,Abhi10.emp1′
Step 12- To move the export file to a different server.
Scp Abhi10_export* [email protected]:/u02/hari_dir/
On clota-labl03-
Step 13- To Check the Process in Oracle like pmon.
[oracle@clota-labl03 ~]$ ps -ef | grep -i pmon
oracle 3911 1 0 Sep26 ? 00:00:04 ora_pmon_testdb
oracle 339154 338684 0 14:56 pts/2 00:00:00 grep –color=auto -i pmon
Step 14- To Set the Environment for the Oracle.
[oracle@clota-labl03 ~]$ . oraenv
ORACLE_SID = [cdb1] ? testdb
The Oracle base remains unchanged with value /u01/app/oracle
Step 15- With Oracle Create an individual Use
SQL> Create user Abhi11 identified by Abhi11;
User created.
Step 16- Give grant to create session and create table for particular User
SQL> grant create session, create table to Abhi11;
Grant succeeded.
Step 17 – Give unlimited Quota for users’ tablespace for particular user.
SQL> alter user Abhi11 quota unlimited on users;
User altered.
Step 18- Give a specific user permission to read and write to the directory
SQL> grant read, write on directory IMPORT_EXPORT to Abhi11;
Grant succeeded.
Step 19- Consider importing Oracle-compatible schemas and remapping the schema and tablespace on the Oracle instance.
[oracle@clota-labl03 ~]$ impdp directory=hari_dir dumpfile=Abhi10_export.dmp logfile=Abhi10_import.log remap_schema=Abhi10:Abhi11 remap_tablespace=users:users
Step 20- Select the data from the emp table.
SQL> select * from emp;
ID NAME ADDRESS
1 abcd xyz
2 abcd2 xyz2
3 abcd3 xyz3
Step 20- Select the data from the emp table.
SQL> select * from Abhi11.emp;
ID NAME AGE
———- ————————————– ———-
1 John 25
2 Alice 30
3 Ali 38
4 Jv 35
5 Glen 32
Leave a Reply