How to Export Schema Without Data in PL/SQL?

In database management, there are times when you need to export the structure of your database objects such as tables, views, and procedures without including the data. This can be useful for creating backups, migrating databases, setting up development or testing environments, or sharing your schema structure with others.

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.

Exporting Schema Structure in Oracle without Data

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 methods that help us to clone a schema without data are as follows:

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:

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.

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

Oracle’s DBMS_METADATA package allows you to extract the Data Definition Language (DDL) for database objects. This package is ideal for capturing the metadata definitions of objects such as tables, views, and more.

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).

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

You can use SQL queries to directly extract schema object definitions from Oracle’s data dictionary views. This method is useful for fetching details such as column names, constraints, indexes, etc.

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).

Explanation:

• This query fetches the column names of the specified table from Oracle’s data dictionary views.
• In this case, it retrieves the column names for 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.