Copying only schema definition to another schema

Here is the command to copy only the schema definition from one schema to another schema.

First export the schema definition from user tfr_rep to empty_schema.dmp file

exp tfr_rep/welcome@datagen1-ajaved:1521/tfrdb file=empty_schema.dmp log=export.log rows=n constraints=n owner=tfr_rep

Then copy the dump file to ahsan_tfr user from tfr_rep user as follows.

imp userid=ahsan_tfr/welcome@tfindiadbzone:1521/tfindia file=empty_schema.dmp log=import.log rows=n fromuser=tfr_rep touser=ahsan_tfr

NOTE: Names of tablespaces on both the schema should be same, otherwise import will fail indicating oracle error

Sphere: Related Content

Script to export/import oracle schema

Here is the bash script that does the job of export and import of an oracle schema to another schema.

Save this file as EXPORT_IMPORT.

For exporting the schema type
EXPORT_IMPORT EXP

This file generates the schema dump in exp_database.dmp file and it also makes log of export process in exp_database.log file.

For importing the same exported exp_database.dmp dump file to another schema type
EXPORT_IMPORT IMP

if [ $# -lt 1 ]; then
echo "First argument to this file should be either EXP or IMP."
exit
fi
if [ "$1" = "EXP" ]; then
if [ $# != 2 ]; then
echo "No of arguments to this file should be two."
echo "USAGE: EXPORT_IMPORT EXP "
exit
fi
exp "$2" FILE=exp_database.dmp COMPRESS=N CONSISTENT=N CONSTRAINTS=Y GRANTS=Y INDEXES=Y RECORD=Y ROWS=Y Feedback=1000 LOG=exp_database.log
fi
if [ "$1" = "IMP" ]; then
if [ $# != 3 ]; then
echo "No of arguments to this file should be three."
echo "USAGE: EXPORT_IMPORT IMP "
exit
fi
from_user=`echo "$2" | cut -f1 -d'/'`
to_user=`echo "$3" | cut -f1 -d'/'`
imp userid="$3" fromuser="$from_user" touser="$to_user" file=exp_database.dmp buffer=64000 commit=y
fi

Sphere: Related Content

Create a new schema in Oracle

Here we will first create a tablespace and then we will create a user for that tablespace.

create tablespace ahsan
add datafile 'e:\ahsan.dbf' size 100 m;

create user ahsan identified by welcome
default tablespace ahsan temporary tablespace temp;

grant create session, connect, resource to ahsan;

Hence now you are ready with the new tablespace and a new user that belongs to the ahsan tablespace;

Sphere: Related Content