Darker logo image
Edit Content
Darker logo image
© oraclegg.com. 2023. Powered by Web1o1

Oracle Clone Schema without Data

Facebook
Twitter
LinkedIn
Email

Oracle Clone Schema without Data

In Oracle to clone schema without data, we can use either Oracle Data Pump or SQL scripts to create the schema structure in the target database.

With Oracle Data Pump, we can export the schema definition from the source database and import it into the target database using the SCHEMA_ONLY parameter to exclude the data. When exporting or importing data using Data Pump, need access on server side to the Data Pump directory and need certain database privileges to run the expdp & impdp. If not then might need to depend on administrator.

Alternatively, there are SQL scripts to generate a schema definition script for the source schema, copy it to the target database server, and then run the script on the target database to create a new schema with the same structure as the source schema.

In this article, I demonstrate how to create a shell script that automates the process of cloning an Oracle schema without data from the source database to the target database. The script extracts the metadata from the source database and uses it to create an empty schema on the target database. This eliminates the need for manual intervention and saves time in the cloning process

In both methods, it’s important to ensure that the schema structure in the target database is identical to the source database before performing any data replication or migration.
Oracle Clone Schema Without Data
Clone Schema Without Data

prerequisites

Before creating the target schema, there are a few prerequisites that you need to ensure are in place. These include:

  • Tablespace existence : It is important to ensure that the target tablespace exists and has enough space available. Additionally, the tablespace name should be the same as that of the source database. If the tablespace name is different, you can edit the generated SQL output to change the tablespace name accordingly.

  • User privileges: Ensure that the user performing the DDL’s has the necessary privileges to execute the CREATE TABLE, CREATE INDEX, ALTER TABLE, CREATE SEQUENCES, CREATE PACKAGE, CREATE PROCEDURES, CREATE TRIGGERS etc. to be added as required

  • SQLPLUS Client and Bash : SQLPLUS Client tool & Bash has to be in place to execute this script.

  • Source & Target Connectivity : Make sure to add source and target database TNS entry in tnsnames.ora and test the connectivity.

  • Parameters : In the script 3 Parameters needs to be set.
    1.SOURCE DB Connection
    2.Target DB Connection
    3.Schema Name to be cloned.

Master Script - Schema_Clone.sh

				
					#!/bin/bash

## 3 Parameters needs to be set. 1:SOURCE DB Connection  2: Target DB Connection 3:Schema Name to be cloned.
## All the Target Scripts would be generated under Target_Scripts directory. 
## All the spool files will be genereated under Target Scripts directory. 
## Default password of the user would be Password#_123. Please change it accordingly. 


##################----------------------------------------------------------#######
#Set the username/password@Source_database - From Schema to Clone
source_db_conn=system/Password@ORCL_SOURCE
#Set the username/password@destination_database - TO Schema to Clone
target_db_conn=system/Password@ORCL_TARGET
# Set the destination schema name to be cloned
schema_name=SOE
##################----------------------------------------------------------#######


if [ ! -d "Target_Scripts" ]; then
  mkdir "Target_Scripts"
fi

transform_param="begin
       dbms_metadata.set_transform_param (dbms_metadata.session_transform, 'SQLTERMINATOR', true);
       dbms_metadata.set_transform_param (dbms_metadata.session_transform, 'PRETTY', true);
       dbms_metadata.set_transform_param (dbms_metadata.session_transform, 'SEGMENT_ATTRIBUTES', true);
       dbms_metadata.set_transform_param (dbms_metadata.session_transform, 'STORAGE', false);
       dbms_metadata.set_transform_param(dbms_metadata.session_transform, 'CONSTRAINTS',false);
       dbms_metadata.set_transform_param(dbms_metadata.session_transform, 'REF_CONSTRAINTS',false);
end;
/
"

transform_param_const="begin
       dbms_metadata.set_transform_param (dbms_metadata.session_transform, 'SQLTERMINATOR', true);
       dbms_metadata.set_transform_param (dbms_metadata.session_transform, 'PRETTY', true);
       dbms_metadata.set_transform_param (dbms_metadata.session_transform, 'SEGMENT_ATTRIBUTES', true);
       dbms_metadata.set_transform_param (dbms_metadata.session_transform, 'STORAGE', false);
end;
/
"


# Connect to the source database and generate the DDL for the schema
sqlplus -S $source_db_conn <<EOF > Target_Scripts/"$schema_name"_SCHEMA_CREATE.sql
SET HEADING OFF
SET FEEDBACK OFF
SET PAGESIZE 0
SET LINESIZE 5000
SET LONG 90000
SELECT DBMS_METADATA.GET_DDL('USER', '$schema_name') FROM DUAL;
EOF

# Connect to the source database and generate the DDL for the objects Tables
sqlplus -S $source_db_conn <<EOF > Target_Scripts/2_"$schema_name"_TABLE_CREATE_ddl.sql
set long 20000 longchunksize 20000 pagesize 0 linesize 1000 feedback off verify off trimspool on
$transform_param
exec dbms_metadata.set_transform_param(DBMS_METADATA.SESSION_TRANSFORM, 'CONSTRAINTS', FALSE);
SELECT DBMS_METADATA.GET_DDL('TABLE', table_name, owner) AS ddl_Tables FROM dba_tables WHERE owner = '$schema_name';
EOF

# Connect to the source database and generate the DDL for the objects Indexes
sqlplus -S $source_db_conn <<EOF > Target_Scripts/3_"$schema_name"_INDEXES_CREATE_ddl.sql
set long 20000 longchunksize 20000 pagesize 0 linesize 1000 feedback off verify off trimspool on
$transform_param
SELECT DBMS_METADATA.GET_DDL('INDEX', INDEX_name, owner) AS ddl_Indexes FROM dba_indexes WHERE owner = '$schema_name';
EOF

# Connect to the source database and generate the DDL for the objects like Views & Sequences in the schema
sqlplus -S $source_db_conn <<EOF > Target_Scripts/4_"$schema_name"_VIEWS_SEQUENCES_ddl.sql
set long 20000 longchunksize 20000 pagesize 0 linesize 1000 feedback off verify off trimspool on
$transform_param
SELECT DBMS_METADATA.GET_DDL('VIEW', view_name, owner) AS ddl_Views FROM dba_views WHERE owner = '$schema_name';
SELECT DBMS_METADATA.GET_DDL('SEQUENCE', sequence_name, SEQUENCE_OWNER) AS ddl_Sequences FROM dba_sequences WHERE SEQUENCE_OWNER = '$schema_name';
EOF

# Connect to the source database and generate the DDL for the objects like Constraints in the schema
sqlplus -S $source_db_conn <<EOF > Target_Scripts/5_"$schema_name"_CONSTRAINTS_ddl.sql
set long 20000 longchunksize 20000 pagesize 0 linesize 1000 feedback off verify off trimspool on
$transform_param_const
SELECT DBMS_METADATA.get_ddl (CASE WHEN constraint_type = 'R' THEN 'REF_CONSTRAINT' ELSE 'CONSTRAINT' END, constraint_name, owner) FROM dba_constraints where owner='$schema_name';
EOF

# Connect to the source database and generate the DDL for the objects like PACKAGE, PACKAGE_BODY, PROCEDURE,TRIGGER & FUNCTION in the schema
sqlplus -S $source_db_conn <<EOF > Target_Scripts/6_"$schema_name"_PACKAGE_ddl.sql
set long 100000 longchunksize 100000 pagesize 0 linesize 1000 feedback off verify off trimspool on
$transform_param
SELECT DBMS_METADATA.GET_DDL('PACKAGE', object_name, owner) FROM dba_objects WHERE object_type = 'PACKAGE' AND  owner = '$schema_name';
SELECT DBMS_METADATA.GET_DDL('PACKAGE_BODY', object_name, owner) FROM dba_objects WHERE object_type = 'PACKAGE BODY' AND  owner = '$schema_name';
EOF


# Connect to the source database and generate the DDL for the objects like PACKAGE, PACKAGE_BODY, PROCEDURE,TRIGGER & FUNCTION in the schema
sqlplus -S $source_db_conn <<EOF > Target_Scripts/7_"$schema_name"_PROC_FUNC_ddl.sql
set long 100000 longchunksize 100000 pagesize 0 linesize 1000 feedback off verify off trimspool on
$transform_param
SELECT DBMS_METADATA.GET_DDL('PROCEDURE', object_name, owner) FROM dba_objects WHERE object_type = 'PROCEDURE' AND  owner = '$schema_name';
SELECT DBMS_METADATA.GET_DDL('FUNCTION', object_name, owner) FROM dba_objects WHERE object_type = 'FUNCTION' AND  owner ='$schema_name';
EOF

# Connect to the source database and generate the DDL for the objects like PACKAGE, PACKAGE_BODY, PROCEDURE,TRIGGER & FUNCTION in the schema
sqlplus -S $source_db_conn <<EOF > Target_Scripts/8_"$schema_name"_TRIG_ddl.sql
set long 100000 longchunksize 100000 pagesize 0 linesize 1000 feedback off verify off trimspool on
$transform_param
SELECT DBMS_METADATA.GET_DDL('TRIGGER', object_name, owner) FROM dba_objects WHERE object_type = 'TRIGGER' AND  owner = '$schema_name';
EOF



DTS=`grep -i -w 'DEFAULT TABLESPACE' Target_Scripts/"$schema_name"_SCHEMA_CREATE.sql`
TTS=`grep -i -w 'TEMPORARY TABLESPACE' Target_Scripts/"$schema_name"_SCHEMA_CREATE.sql`
TSN=`echo $DTS |cut -d' ' -f3 | head -1`

touch Target_Scripts/1_"$schema_name"_SCHEMA_CREATE.sql

# add content to file
echo "CREATE USER $schema_name IDENTIFIED BY "Password#_123" $DTS $TTS QUOTA unlimited ON $TSN;" >> Target_Scripts/1_"$schema_name"_SCHEMA_CREATE.sql

rm -r Target_Scripts/"$schema_name"_SCHEMA_CREATE.sql


sed -i '/\<PCTFREE\>/d' Target_Scripts/2_"$schema_name"_TABLE_CREATE_ddl.sql
sed -i '/\<PCTFREE\>/d' Target_Scripts/3_"$schema_name"_INDEXES_CREATE_ddl.sql

sed -i "1iSPOOL 1_"$schema_name"_SCHEMA_CREATE.out" Target_Scripts/1_"$schema_name"_SCHEMA_CREATE.sql
sed -i '$aSPOOL OFF' Target_Scripts/1_"$schema_name"_SCHEMA_CREATE.sql

sed -i "1iSPOOL 2_"$schema_name"_TABLE_CREATE_ddl.out" Target_Scripts/2_"$schema_name"_TABLE_CREATE_ddl.sql
sed -i '$aSPOOL OFF' Target_Scripts/2_"$schema_name"_TABLE_CREATE_ddl.sql

sed -i "1iSPOOL 3_"$schema_name"_INDEXES_CREATE_ddl.out" Target_Scripts/3_"$schema_name"_INDEXES_CREATE_ddl.sql
sed -i '$aSPOOL OFF' Target_Scripts/3_"$schema_name"_INDEXES_CREATE_ddl.sql

sed -i "1iSPOOL 4_"$schema_name"_VIEWS_SEQUENCES_ddl.out" Target_Scripts/4_"$schema_name"_VIEWS_SEQUENCES_ddl.sql
sed -i '$aSPOOL OFF' Target_Scripts/4_"$schema_name"_VIEWS_SEQUENCES_ddl.sql

sed -i "1iSPOOL 5_"$schema_name"_CONSTRAINTS_ddl.out" Target_Scripts/5_"$schema_name"_CONSTRAINTS_ddl.sql
sed -i '$aSPOOL OFF' Target_Scripts/5_"$schema_name"_CONSTRAINTS_ddl.sql

sed -i "1iSPOOL 6_"$schema_name"_PACKAGE_ddl.out" Target_Scripts/6_"$schema_name"_PACKAGE_ddl.sql
sed -i '$aSPOOL OFF' Target_Scripts/6_"$schema_name"_PACKAGE_ddl.sql

sed -i "1iSPOOL 7_"$schema_name"_PROC_FUNC_ddl.out" Target_Scripts/7_"$schema_name"_PROC_FUNC_ddl.sql
sed -i '$aSPOOL OFF' Target_Scripts/7_"$schema_name"_PROC_FUNC_ddl.sql

sed -i "1iSPOOL 8_"$schema_name"_TRIG_ddl.out" Target_Scripts/8_"$schema_name"_TRIG_ddl.sql
sed -i '$aSPOOL OFF' Target_Scripts/8_"$schema_name"_TRIG_ddl.sql

sed -i '1i\\set echo on' Target_Scripts/1_"$schema_name"_SCHEMA_CREATE.sql
sed -i '1i\\set echo on' Target_Scripts/2_"$schema_name"_TABLE_CREATE_ddl.sql
sed -i '1i\\set echo on' Target_Scripts/3_"$schema_name"_INDEXES_CREATE_ddl.sql
sed -i '1i\\set echo on' Target_Scripts/4_"$schema_name"_VIEWS_SEQUENCES_ddl.sql
sed -i '1i\\set echo on' Target_Scripts/5_"$schema_name"_CONSTRAINTS_ddl.sql
sed -i '1i\\set echo on' Target_Scripts/6_"$schema_name"_PACKAGE_ddl.sql
sed -i '1i\\set echo on' Target_Scripts/7_"$schema_name"_PROC_FUNC_ddl.sql
sed -i '1i\\set echo on' Target_Scripts/8_"$schema_name"_TRIG_ddl.sql


touch Target_Scripts/Target_"$schema_name"_Create_DDL.sh

# add content to file
echo "#!/bin/bash
sqlplus -S $target_db_conn <<EOF
@1_"$schema_name"_SCHEMA_CREATE.sql
@2_"$schema_name"_TABLE_CREATE_ddl.sql
@3_"$schema_name"_INDEXES_CREATE_ddl.sql
@4_"$schema_name"_VIEWS_SEQUENCES_ddl.sql
@5_"$schema_name"_CONSTRAINTS_ddl.sql
@6_"$schema_name"_PACKAGE_ddl.sql
@7_"$schema_name"_PROC_FUNC_ddl.sql
@8_"$schema_name"_TRIG_ddl.sql
EOF">> Target_Scripts/Target_"$schema_name"_Create_DDL.sh

chmod 755 Target_Scripts/Target_"$schema_name"_Create_DDL.sh

echo "Please check the Target_Scripts Directory for the sql files. 
Execute one by one sql files or 
Everything in one shot by using Target_"$schema_name"_Create_DDL.sh"
Output
Output 1
Output2
Summary : Once the schema structure on target is created and in place, you can perform the GoldenGate initial load process to replicate the data from the source to the target database.

Further Reading You can find the complete official oracle documentation about DBMS_METADA here.

Hope you found this article useful.
Disclaimer: The views expressed on this document are my own and do not necessarily reflect the views of Oracle..
Stay Updated
Sign up for email notifications whenever a new blog post is published.
© oraclegg.com. 2024. Powered by Web1o1