In this article we are going to see the Oracle 19c Datapump- Sqlfile
Sqlfile option will show you the DDL in the text format.Sometimes customer provides us data pump export file, by that time we will use SQLFILE parameter and find out DDL’s (Schema names) and perform data pump import accordingly.
[oracle@oracle19c ~]$ impdp directory=My_Dir dumpfile=table01.dmp logfile=table01.log tables='table01' sqlfile=tab.sql
Import: Release 19.0.0.0.0 - Production on Mon Jan 25 09:30:30 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Username: data
Password:
Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Master table "DATA"."SYS_SQL_FILE_TABLE_01" successfully loaded/unloaded
Starting "DATA"."SYS_SQL_FILE_TABLE_01": data/******** directory=My_Dir dumpfile=table01.dmp logfile=table01.log tables=table01 sqlfile=tab.sql
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Job "DATA"."SYS_SQL_FILE_TABLE_01" successfully completed at Mon Jan 25 09:30:36 2021 elapsed 0 00:00:01
[oracle@oracle19c ~]$ cd /u01/app/oracle/oradata/
[oracle@oracle19c oradata]$ ls
compressed1.dmp con01.dmp con.log CONTINUE.dmp My_Dir schema.dmp table01.dmp tablespace.log tabs.log
compressed2.dmp con01.log CONTINUE1.dmp CONTINUE.log ORACLE19C schema.log table01.log tabs00.log tab.sql
compressed.dmp con.dmp CONTINUE1.log export.log ORACLE19CCON tab00.dmp tablespace01.log tabs.dmp
sqlfile activity completed successfully and ‘tab.sql‘ file has created in the specified dump directory location.
[oracle@oracle19c oradata]$ vi tab.sql
Thank you for giving your valuable time to read the above information.
If you want to be updated with all our articles send us the Invitation or Follow us:
Ramkumar’s LinkedIn: https://www.linkedin.com/in/ramkumardba/
LinkedIn Group: https://www.linkedin.com/in/ramkumar-m-0061a0204/
Facebook Page: https://www.facebook.com/Oracleagent-344577549964301
Ramkumar’s Twitter : https://twitter.com/ramkuma02877110
Ramkumar’s Telegram: https://t.me/oracleageant
Ramkumar’s Facebook: https://www.facebook.com/ramkumarram8
In this article, we are going to see how to export and Import table space using Datapump.
Tablespaces are the logical storage units which are used by the database to store separate objects, such as tables, types, PL/SQL code, and so on. Typically, related objects are grouped together and stored in the same tablespace.
Using expdp export utility of data pump we can export tablespaces. Exporting tablespace is also a way of taking logical backup of the tablespace of your database. Exporting tablespace means only the tables contained in a specified set of tablespace are unloaded along with its dependent objects.
Exporting tablespace means
Only the tables contained in a specified set of tablespace are unloaded
If a table is unloaded, then its dependent objects are also unloaded
Tablespace export unloads both object metadata and Data.
DIRECTORY.
Create a directory anywhere in your system or on your network where expdp export utility can save the exported files such as dump files and log files.
TABLE SPACE:
Using this TABLESPACES parameter we can specify the list of tablespace names which you want to export. For example here I have specified USERS.
[oracle@oracle19c ~]$ expdp directory=My_Dir tablespaces=users dumpfile=tab00.dmp logfile=tabs00.log
Export: Release 19.0.0.0.0 - Production on Mon Jan 25 09:22:37 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Username: data
Password:
Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Starting "DATA"."SYS_EXPORT_TABLESPACE_01": data/******** directory=My_Dir tablespaces=users dumpfile=tab00.dmp logfile=tabs00.log
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/COMMENT
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
. . exported "DATA"."TABLE01" 5.515 KB 5 rows
Master table "DATA"."SYS_EXPORT_TABLESPACE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for DATA.SYS_EXPORT_TABLESPACE_01 is:
/u01/app/oracle/oradata/tab00.dmp
Job "DATA"."SYS_EXPORT_TABLESPACE_01" successfully completed at Mon Jan 25 09:22:51 2021 elapsed 0 00:00:09
Export activity completed successfully.
[oracle@oracle19c ~]$ !sq
sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Mon Jan 25 09:23:37 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> conn data/pump
Connected.
SQL> select * from table01;
ID NAME
---------- ------------------------------
1 aaa
2 bbb
3 ccc
4 ddd
5 eee
SQL> drop table table01;
Table dropped.
SQL> select * from Table01;
select * from Table01
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
Import the tablespaces using the Dumpfile
[oracle@oracle19c ~]$ impdp directory=My_Dir dumpfile=tab00.dmp logfile=tabs00.log
Import: Release 19.0.0.0.0 - Production on Mon Jan 25 09:24:28 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Username: data
Password:
Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Master table "DATA"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "DATA"."SYS_IMPORT_FULL_01": data/******** directory=My_Dir dumpfile=tab00.dmp logfile=tabs00.log
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "DATA"."TABLE01" 5.515 KB 5 rows
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Job "DATA"."SYS_IMPORT_FULL_01" successfully completed at Mon Jan 25 09:24:36 2021 elapsed 0 00:00:03
Import Activity successfully completed.Now we can access the tablespaces.
[oracle@oracle19c ~]$ !sq
sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Mon Jan 25 09:24:51 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> conn data/pump
Connected.
SQL> select * from table01;
ID NAME
---------- ------------------------------
1 aaa
2 bbb
3 ccc
4 ddd
5 eee
Thank you for giving your valuable time to read the above information.
If you want to be updated with all our articles send us the Invitation or Follow us:
Ramkumar’s LinkedIn: https://www.linkedin.com/in/ramkumardba/
LinkedIn Group: https://www.linkedin.com/in/ramkumar-m-0061a0204/
Facebook Page: https://www.facebook.com/Oracleagent-344577549964301
Ramkumar’s Twitter : https://twitter.com/ramkuma02877110
Ramkumar’s Telegram: https://t.me/oracleageant
Ramkumar’s Facebook: https://www.facebook.com/ramkumarram8
Using expdp utility provided by data pump we can export data or say unload data or metadata from one database to another. By using this utility we can either export the complete database or a subset of databases such as Database Schema, Table spaces or even individual tables.
DATABASE VERSION:
19.3.0.0.
DIRECTORY:
create a directory by using CREATE DIRECTORY command.
FULL:
This parameter FULL indicates that you want to perform a full database export. This parameter can have YES or NO values. If you set this parameter to YES that means expdp utility will export all the data and metadata of the database.
DUMPFILE:
Using DUMPFILE parameter you specify the names, and optionally, the directory objects of dump files for an export job.
A full export does not export system schemas that contain Oracle-managed data and metadata.
Grants on objects owned by the SYS schema are never exported.
Thank you for giving your valuable time to read the above information.
If you want to be updated with all our articles send us the Invitation or Follow us:
Ramkumar’s LinkedIn: https://www.linkedin.com/in/ramkumardba/
LinkedIn Group: https://www.linkedin.com/in/ramkumar-m-0061a0204/
Facebook Page: https://www.facebook.com/Oracleagent-344577549964301
Ramkumar’s Twitter : https://twitter.com/ramkuma02877110
Ramkumar’s Telegram: https://t.me/oracleageant
Ramkumar’s Facebook: https://www.facebook.com/ramkumarram8
