中国开发网: 论坛: 数据库: 贴子 895885
DeepBlue: Cloning an Oracle Schema
Cloning an Oracle Schema
When I started this blog I decided I would only blog about things you could not find reasonably easily with a simple google search. This post violates that rule. Cloning a schema is something that I do fairly frequently but not frequently enough that I remember the exact syntax. First I googled it every time, later I created a little text file with the commands. Having already gone to the trouble to write a text file, I might as well just paste it in here. I use datapump. In the example below I want to export the SCOTT schema which has all of its data in the USERS tablespace. I want to import it into a database (either the same or another Oracle database) as the user SCOTT2 in the tablespace USERS2. That means I need to change both the database schema and tablespace. Here are the commands (replace values as appropriate for your env):

export ORACLE_HOME=/opt/oracle/product/oracle10g
export ORACLE_SID=c2dev1
cd $ORACLE_HOME/bin
./expdp system/ dumpfile=scott.dmp schemas=scott

Note: this will create the file scott.dmp in the location $ORACLE_HOME/admin/dpdump. You can create a different directory for it, but for my purposes this is sufficient.

That is all it takes to do the export.
If you plan to import into the same database it can stay in the same location.

To do the import into a different database you will need to copy the scott.dmp file to the right location for the other database.

cp $ORACLE_HOME/admin/c2dev1/dpdump/scott.dmp /mynas/scott.dmp
Then I ssh to the target machine and copy to the new target location

cp /mynas/scott.dmp $ORACLE_HOME/admin/c2dev2/dpdump/scott.dmp

export ORACLE_HOME=/opt/oracle/product/oracle10g
export ORACLE_SID=c2dev2
cd $ORACLE_HOME/bin
./impdp system/ dumpfile=scott.dmp remap_schema=scott:scott2 remap_tablespace=USERS:USERS2

That should do it. This should make it easy for me to do in the future and hopefully help someone else along the way.

=================================
1. imp/exp tools with the FROMUSER and TOUSER clauses.
2. impdp/expdp tools with the REMAP_SCHEMA clause.

=================================



执行力=流程+计划+组织

把理想变成计划,
把计划变成步骤,
把步骤变成行动,
把行动变成成果。

好語說盡人必易之。規矩行盡人必繁之。福若受盡緣必孤。勢若使盡禍必至。

相关信息:


欢迎光临本社区,您还没有登录,不能发贴子。请在 这里登录