How to Export Schema Without Data in PL/SQL?

In Databases, sometimes you need to export database objects like tables, views, and procedures. You might do this for different reasons like making backups, moving data to a new place, or it with someone else. There are different ways to do this.

In this article, we will learn about How to clone a schema without data by understanding various methods along with the examples and so on.

How to Export Only Schema Structure in Oracle?

Exporting schema without data in PL/SQL is like having a snapshot of how our database is organized, without including the actual information. It’s useful for making blueprints of our database for things like documentation or setting up the same structures in different places.

PL/SQL gives us different ways to do this, so we can choose what works best for us. Below are the method which help us to clone a schema without data are as follows:

1. Using Data Pump
2. Using DBMS_METADATA Package
3. Using SQL Queries

1. Exporting Schema with Data Pump

We will export the SYSTEM schema from XE database using utility “expdp”. We provide login credentials (username: SYSTEM, password: 1234) and specify the database identifier (XE) for connection. The export dump file will be stored in the directory (DATA_PUMP_DIR) with the name “expotingSchema.dmp”. Only objects which belongs to the SYSTEM schema will be exported.

Syntax:

expdp username/password@database_name DIRECTORY=directory_name 
DUMPFILE=dumpfile_name.dmp SCHEMAS=schema_name

Example:

expdp SYSTEM/1234@XE DIRECTORY=DATA_PUMP_DIR 
DUMPFILE=expotingSchema11.dmp SCHEMAS=SYSTEM

Note: The above command should be executed in command prompt in the “bin” directory of SQL where it is installed. If you want to execute it in sql command line then add “host” before the command.

Output:

Exporting Schema with Data Pump 1

Explanation:

• The command initiates a Data Pump export operation for the “SYSTEM” schema.
• It connects to the XE database using the provided credentials.
• The export process estimates size and begins processing schema objects.

Exporting Schema with Data Pump 2

Explanation:

• Objects belonging to the “SYSTEM” schema, including the “SALES” object, have been successfully exported.
• The export process was completed without errors, as indicated by “successfully loaded/unloaded.”
• A dump file for the export operation has been generated at the specified location.
• The job for exporting the “SYSTEM” schema was successfully completed, with the export of rows of data.

2. Using the DBMS_METADATA Package

Here we will demonstrate how to use the DBMS_METADATA package to extract metadata definitions of database objects. We’ll customize the metadata extraction process to suit our requirements.

Syntax:

SELECT DBMS_METADATA.GET_DDL('object_type', 'object_name') FROM DUAL;

Example:

SELECT DBMS_METADATA.GET_DDL('TABLE', 'EMPLOYEES') FROM DUAL;

Output:

The output of the SELECT query is the DDL statement for the specified database object (EMPLOYEES table).

Export schema without data in PL/SQL Using the DBMS_METADATA Package

Explanation: In this example, we’re using the DBMS_METADATA.GET_DDL function to retrieve the Data Definition Language (DDL) for the EMPLOYEES table.

3. Using SQL Queries

Here we will utilize SQL Queries to directly extract schema object definitions from Oracle’s data dictionary views.

Syntax:

SELECT column_name FROM all_tab_columns WHERE table_name = 'Name_of_table';

Example:

This query will fetch the column names of a specified table from the data dictionary views.

SELECT column_name FROM all_tab_columns WHERE table_name = 'EMPLOYEES';

Output: The output of the SELECT query is the column names of the specified table (EMPLOYEES).

Export schema without data in PL/SQL Using SQL Queries

Explanation: In this example, we’re querying the all_tab_columns view to retrieve the column names of the EMPLOYEES table.

Conclusion

In this article, we explored three different methods for exporting schema objects in Oracle databases: Data Pump, DBMS_METADATA Package, and SQL Queries. Each method offers its own advantages and use cases, depending on the specific requirements of the task at hand. By understanding these methods, you can choose the most appropriate approach for your database export as per needs.