Oracle Export Metadata only And Import the

Metadata only Guide – Step-by-Step Tutorial

Scenario: Client is wanted to export and import the metadata

In this guide, we will learn how to use expdp (Data Pump Export) and impdp (Data Pump Import) in Oracle Database to back up metadata and restore the same only.

To create a backup, first we have to set up a physical backup location.

Creating a Backup location:

On Linux server:

mkdir -p /data/backup




On Oracle Database:

Command:

create a directory TEST_DIR2 as '/data/backup';

Here, TEST_DIR2 is a logical directory pointing to the physical backup location.

To verify the directory:

SELECT * FROM dba_directories WHERE directory_name = 'TEST_DIR2';





Exporting a Metadata using expdp:

To backup the Metadata for Schema U2, 

expdp schemas=U2 directory=TEST_DIR2 dumpfile=schdata_only2.dmp logfile=schdata_only.log content=METADATA_ONLY

Note: 

Dump File (.dump): A binary file generated by expdp that contains exported schema objects, data, and metadata. It is used for backup and migration.

Log File (.log): A text file that records details of the export/import process, including progress, errors, and warnings. It helps in troubleshooting.

After running the command, you will be prompted to connect the database.

Connect using sys user and press enter when asked for a password.

Export schema U2 Metadata completes successfully.






Before importing specific schema U2 metadata we must drop the same schema if already exists.

Why? If the schema already exists, we cannot re-import the same table without errors.

why cascade?

using cascade you will be drop the everything in the schema U2.







Now Import the Schema U2 Metadata only using impdb:

impdp schemas=U2 directory=TEST_DIR2 dumpfile=schdata_only2.dmp logfile=schdata_onlyimp.log content=METADATA_ONLY

Connected using the sys user when prompted.

Imported successfully.








Imported metadata only as of now not the rows










Now let us see to export and import only data

Exporting:

expdp schemas=U2 directory=TEST_DIR2 dumpfile=dataonly.dmp logfile=schdata_only.log content=DATA_ONLY









Importing:

impdp schemas=U2 directory=TEST_DIR2 dumpfile=schdata_only2.dmp logfile=schdata_onlyimp.log content=METADATA_ONLY









Imported data of User U2 as well now









Summary of Steps

✔ Created a backup directory (/data/backup) on the Linux server.

✔ Created a logical directory (TEST_DIR2) in the Oracle Database.

✔ Exported schema metadata only of U2 schema using expdp.

✔ Deleted the existing schema with cascading before importing.

✔ Restored metadata of U1 using impdp and verified the objects.

Final Thoughts

By following these steps, you can successfully back up metadata only of U1 and restore metadata only in Oracle using Data Pump Export (expdp) and Import (impdp). This method is useful for migrating data between servers or restoring accidental deletions.

👉"Did this guide help you? Have questions? Drop a comment below, and I'll be happy to help!"

👉"Want more Oracle database tutorials? Follow this blog for the latest updates!

More useful links:

[Oracle Export Specific Tablespace And Import Specific Table only Guide – Step-by-Step Tutorial]: (https://arbnumberone.blogspot.com/2025/03/oracle-export-specific-tablespace-and.html)

[Oracle Export Schema or User And Import Schema or User Guide – Step-by-Step Tutorial]: (https://arbnumberone.blogspot.com/2025/03/oracle-database-schema-export-and.html)

[Oracle Database Export Table & Import Table Guide – Step-by-Step Tutorial]: (https://arbnumberone.blogspot.com/2025/03/export-and-import-how-to-works-in.html)










Comments

Post a Comment

Popular posts from this blog

Image
How to Enable Archive Log Mode in Oracle (Switch to ARCHIVELOG Mode) If you want to disable archive log mode (switch to NOARCHIVELOG mode), follow these steps: 1. Check Current Archive Log Mode Before making changes, check if your database is in NO ARCHIVELOG mode: command -> select log_mode from v$database; The database in noarchivelog mode   If the output is NO ARCHIVELOG , it means archive logging is disabled. 2. Shutdown the Database You cannot switch to ARCHIVELOG mode while the database is running, so first shut it down: commnd -> shut immediate; 3. Start the Database in MOUNT Mode the database should be in mount stage to disable or enable it 4. Enable Archive Log Mode (Switch to ARCHIVELOG Mode) Run the following command: ALTER DATABASE ARCHIVELOG; 5. Open the Database Normally ALTER DATABASE OPEN; 6. Verify the Change Run this command again to confirm the mode: SELECT log_mode FROM v$database; Impact of Enabling Archive Log Mode ✅ Point-in-Time Recovery: With...
Read more
Image
Oracle Export Previous SCN Data & Import into an Existing Table – Step-by-Step Guide Scenario: In this guide, we will explore how to use Oracle Data Pump Export (expdp) and Import (impdp) to back up historical data from a previous SCN (System Change Number) and restore it into an existing table. This approach is useful when you need to retrieve older data while preserving the table structure. Step 1: Setting Up a Backup Location Before exporting data, we need to create a physical backup location where the dump files will be stored. On Linux server: mkdir -p /data/backup On Oracle Database: We create a logical directory pointing to the physical backup location: Command: create a directory TEST_DIR2 as '/data/backup'; TEST_DIR2 -->  Logical directory pointing to the physical backup location. To verify the directory: SELECT * FROM dba_directories WHERE directory_name = 'TEST_DIR2'; Step 2: Checking Current Table Data Inserted few more records Before exporting, ...
Read more