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 DBgrep -v OBJECT_NAME | grep -v "-" | grep -v "^$"
: this removes empty lines and useless trace from Oracle SQLPlussed "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 filesort | uniq
: sort lines and remove duplicatesexp $SRC_LOGIN/$SRC_PASSWORD@$SRC_DB file=$table.sql buffer=10485867 tables="$table" direct=y
: exports the content of the table $tableecho "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