要求: 把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