Search
Calendar
June 2025
S M T W T F S
« May    
1234567
891011121314
15161718192021
22232425262728
2930  
Archives

PostHeaderIcon Copy all tables from a schema to another one

Case:

I need copy the content of all tables from one Oracle schema to another one, specifically: I must copy raw data from a production DB to a developper one.

Context:

  • The DBA do not allow DBLinks
  • Oracle source and destination schemas share the same structure (name of the tables, names and types of columns, etc.

Fix:

Here is a small script I wrote, of course it can be improved:

#!/usr/bin/bash
SRC_LOGIN=XYZ
SRC_PASSWORD=XYZ
SRC_DB=production
DBDEST_LOGIN=XYZ
DEST_PASSWORD=XYZ
DEST_DB=developmentDB

echo "select object_name from user_objects where object_type = 'TABLE' order by object_name;" | \
	sqlplus $SRC_LOGIN/$SRC_PASSWORD@$SRC_DB | \
	grep -v OBJECT_NAME | grep -v "-" | grep -v "^$" | \
	sed "1,9d" | tac | sed "1,3d" | tac | \
	sort | uniq > allTables.txt

for table in `more allTables.txt`
do
rm -rf $table.sql
echo "exporting $table"
exp $SRC_LOGIN/$SRC_PASSWORD@$SRC_DB file=$table.sql buffer=10485867 tables="$table" direct=y
echo "importing $table"
echo "truncate table $table;" | \
    sqlplus $DEST_LOGIN/$DEST_PASSWORD@$DEST_DB
imp $DEST_LOGIN/$DEST_PASSWORD@$DEST_DB file=$table.sql \
    buffer=10485867 tables="$table" \
    fromuser=$SRC_LOGIN touser=$DEST_LOGIN ignore=y
rm -rf $table.sql
done

rm -rf allTables.txt

A little more explanations:

  • echo "select object_name from user_objects where object_type = 'TABLE';" | sqlplus $SRC_LOGIN/$SRC_PASSWORD@$SRC_DB: this allow to retrieve the names of all the tables from the DB
  • grep -v OBJECT_NAME | grep -v "-" | grep -v "^$" : this removes empty lines and useless trace from Oracle SQLPlus
  • sed "1,9d" | tac | sed "1,3d" | tac: removes the 9 first lines and the 3 last ones. tac in Unix allow to reverse the content of a file
  • sort | uniq : sort lines and remove duplicates
  • exp $SRC_LOGIN/$SRC_PASSWORD@$SRC_DB file=$table.sql buffer=10485867 tables="$table" direct=y: exports the content of the table $table
  • echo "truncate table $table;" | sqlplus $DEST_LOGIN/$DEST_PASSWORD@$DEST_DB: truncates destination table. This is useful in case the script must be played more than once, for instance.
  • imp $DEST_LOGIN/$DEST_PASSWORD@$DEST_DB file=$table.sql buffer=10485867 tables="$table" fromuser=$SRC_LOGIN touser=$DEST_LOGIN ignore=y: import the content of the table

Some improvement ideas:

  • handle sequences, triggers, stored procedures, etc.
  • create the complete destination DB owing to source DB, ie do not assume anymore that the DBs share the same structure

Leave a Reply