要求: 把schema = vsop 的資料庫到 vsop2
操作:
一、建立邏輯目錄,該指令不會在作業系統建立真正的目錄(請先建立真正的目錄),最好以system等管理者建立邏輯目錄
sqlplus '/ as sysdba'
SQL>conn system/[email protected] as sysdba
SQL>create directory dump_dir as '/test/dump';
二、檢視管理者目錄(同時檢視作業系統是否存在,因為oracle并不關心該目錄是否存在,假如不存在,則出錯)
SQL>select * from dba_directories;
三、給scott使用者賦予在指定目錄的操作權限,最好以system等管理者賦予。
SQL>grant read,write on directory dump_dir to scott;
四、用expdp導出資料
expdp vsop/[email protected] directory=dump_dir dumpfile=full.dmp full=y;
五、用impdp導入資料
impdb vsop2/[email protected] directory=dump_dir dumpfile=full.dmp full=y;
-------
最後看一下 兩邊的表結構:
select a.table_name user_table, a.column_name user_column,
b.table_name user2_table, b.column_name user2_column
from (select table_name, column_name from dba_tab_columns
where owner= 'vsop' ) a,
(select table_name, column_name from dba_tab_columns
where owner= 'vsop2' ) b
where a.table_name(+) = b.table_name
and a.column_name(+) = b.column_name
and a.column_name is null
union all
select a.table_name user_table, a.column_name user_column,
b.table_name user2_table, b.column_name user2_column
from (select table_name, column_name from dba_tab_columns
where owner= 'vsop' ) a,
(select table_name, column_name from dba_tab_columns
where owner= 'vsop2' ) b
where a.table_name = b.table_name(+)
and a.column_name = b.column_name(+)
and b.column_name is null;
更多内容可看:http://blog.sina.com.cn/s/blog_67d41beb0100ixnb.html