--查询数据库都有哪些表空间
select tablespace_name from dba_tablespaces;
--查询用户拥有的表空间
select DISTINCT TABLESPACE_NAME from dba_tables where owner='SAP2PDM'
UNION
select DISTINCT TABLESPACE_NAME from dba_INDEXES where owner='SAP2PDM';
先查看数据库是否有逻辑备份目录:
col owner format a5;
col directory_name format a25;
col directory_path format a40;
select * from dba_directories;
OWNER DIRECTORY_NAME DIRECTORY_PATH
---------- ------------------------- ----------------------------------------
SYS DUMP_DIR /data/dump
如无,创建逻辑目录,该命令不会在操作系统创建真正的目录(请先创建真正的目录,oracle权限)
create directory dump_dir as '/data/dump';
#查询回收站
SQL> show recyclebin
在执行数据泵导出前,建议清空回收站
SQL> purge recyclebin;
查看数据库编码:
select userenv('language') from dual;
导出前设置操作系统字符编码:
select userenv('language') from dual;
导出SAP2PDM和PDMTEST两个用户数据(如果磁盘空间不够,可以加上COMPRESSION=ALL 进行压缩):
<Oracle>$expdp system/password DIRECTORY=DUMP_DIR schemas=SAP2PDM,PDMTEST DUMPFILE=all_expdp.dmp logfile=all_expdp.log exclude=statistics consistent=y
Export: Release 10.2.0.5.0 - 64bit Production on Monday, 11 March, 2019 12:47:13
Copyright (c) 2003, 2007, Oracle. All rights reserved.
Username: sys as sysdba
Password:
**************************************************************************************************************************
检查源数据库字符编码目标数据库编码是否一致(最好建库前,字符编码选择和源目标一致):
select userenv('language') from dual;
目标系统字符编码:
export nls_lang=AMERICAN_AMERICA.AL16UTF16
检查是否有逻辑备份目录:
select * from dba_directories;
创建逻辑目录:
create directory dump_dir as '/data/dump';
创建表空间:
SQL> create tablespace pdmtest datafile '/data/pdmtestdata/pdmtest.dbf' size 20000M autoextend on next 100M maxsize unlimited;
导入,如果第一遍导入不成功,需将创建的用户drop
drop user SAP2PDM cascade;
drop user PDMTEST cascade;
然后重新导入(tablespace='(SAP2PDM:pdmtest,SMTMDB:pdmtest)'原表空间:新表空间,本次是两个源表空间)
impdp system/password DIRECTORY=dump_dir DUMPFILE=all_expdp.dmp remap_tablespace='(SAP2PDM:pdmtest,SMTMDB:pdmtest)' logfile=impdp.log