====建立用户及表空间====
--创建临时表空间
create temporary tablespace orcl_temp
tempfile 'D:/oracle_date/orcl/temp01.dbf'
size 32m
autoextend on
next 32m maxsize 1048m
extent management local;
--创建数据表空间
create tablespace orcl_data
logging
datafile 'D:/oracle_date/orcl/data01.dbf'
size 32m
autoextend on
next 32m maxsize 1048m
extent management local;
--创建用户并指定表空间
create user uid identified by pwd
default tablespace orcl_data
temporary tablespace orcl_temp;
--给用户授予权限
grant connect,resource to uid;
====数据泵导出导入====
--创建备份目录
create directory orabackup as 'e:/orabackup';
--查看备份目录
select * from dba_directories
--删除备份目录
drop directory orabackup;
--导出与导入
expdp uid/pwd@orcl directory=orabackup dumpfile=orcl_110319.dump
impdp uid/pwd@orcl directory=orabackup dumpfile=orcl_110319.dump remap_schema=test:test2 remap_tablespace=users:test2
--注意低版本是不能导入高版本 比如我的数据库是11gR2 对方是11gr1 需要指定版本号11.1.0.6.0
- expdp username/password directory=dump_test dumpfile=data.dmp version=11.1.0.6.0
解释:
remap_schema=test:test2 , 将数据的schema从test 转换为test2
remap_tablespace=users:test2 将数据的tablespace 从test 转换为test2
可以排除指定表和对象进行导出操作,使用INCLUDE是包含,EXCLUDE是排除,也可以使用通配符INCLUDE=TABLE:"LIKE '%P%'",也可以排除索引exclude=STATISTICS,INDEX
expdp user/password@tsid DIRECTORY=orabackup DUMPFILE=backup.dup EXCLUDE=TABLE:\"IN\(\'F_ENTSEARCH_0819T\',\'F_ENTSEARCH\'\)\",INDE X version=10.2.0.1.0
如果在导入时提示如下错误:
ORA-39006: internal error
ORA-39213: Metadata processing is not available可以尝试执行如下步骤解决:
connect / as sysdba
execute sys.dbms_metadata_util.load_stylesheets;