登录数据库
sqlplus / as sysdba
新建表空间
CREATE TABLESPACE JX_ZG
DATAFILE '/home/u01/app/oracle/JX_ZG.DBF'
SIZE 1024M
AUTOEXTEND ON
NEXT 32M MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL;
CREATE TEMPORARY TABLESPACE JX_ZG_TEMP
TEMPFILE '/home/u01/app/oracle/JX_ZG_TEMP.DBF'
SIZE 1024M
AUTOEXTEND ON
NEXT 32M MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL;
创建用户
CREATE USER JX_ZG IDENTIFIED BY JX_ZG
ACCOUNT UNLOCK
DEFAULT TABLESPACE JX_ZG
TEMPORARY TABLESPACE JX_ZG_TEMP;
GRANT CONNECT,RESOURCE,DBA TO JX_ZG ;
新建导入路径
create directory imp_dir as '/home/oracle/dmp';
赋予读写权限
grant read,write on directory imp_dir to system;
导入命令
quit退出sqlplus
impdp JX_ZG/JX_ZG directory=imp_dir dumpfile=zgtams215_v1231.dmpd remap_schema=zgtams215_v1231:JX_ZG remap_tablespace=zgtams215_v1231_DATA:JX_ZG LOGFILE=20210111.LOG transform=segment_attributes:n transform=oid:n
注释:
- impdp
- 用户名/密码
- remap_schema=导出用户名:导入用户名
- directory=DATA_PUMP_DIR(即导入dmp文件的存放目录)
- remap_tablespace=导出表空间:导入表空间
- dumpfile=导入的文件名
- logfile=导入日志
- transform=OID:N(非必填,解决导入过程中创建对象报错等)
导入结束。
附录
//导入报错了,需要重新导入,先删除表空间释放内存
drop tablespace JXZG including contents and datafiles cascade constraint;
//dbf文件被手动删除导致删除不了表空间。
alter database datafile ‘/home/u01/app/oracle/JXZG .DBF’ offline drop;(/home/u01/app/oracle/JXZG .DBF为表空间文件的物理路径)
drop tablespace JXZG including contents and datafiles cascade constraint;
// 查看有多少张表
select count(*) from user_tables;