导入步骤:
1、查询需要导入库的server_id 和表空间
查询server_id
/home/oracle/app/oracle/product/12.1.0/dbhome_1/network/admin/listener.ora
查询表空间
SELECT username,default_tablespace FROM dba_users; 要用sys用户登录且server_id必须匹配
两种方式:
注意oracle12以后不同的用户server_id不同,登录的时候一定要带上server_id
2:导出命令
exp sys@orclpdb owner='cfqs41' file=/tmp/0520/cfqs4102.dmp log=/tmp/0520/cfqs4102.log buffer=4096;
3:导入主机创建用户,表空间,授权用户。
查看表空间的文件:
select name from v$datafile;
创建永久表空间:
CREATE TABLESPACE customerchat LOGGING DATAFILE '/home/oracle/app/oracle/oradata/orcl/pdborcl/customerchat.dbf' SIZE 5G AUTOEXTEND ON NEXT 500M MAXSIZE 6G EXTENT MANAGEMENT LOCAL;
创建临时表空间:
create temporary tablespace customerchat_temp tempfile '/home/oracle/app/oracle/oradata/orcl/pdborcl/customerchat_temp.dbf' size 5G autoextend on next 500M maxsize 6G extent management local;
注意这里创建的表空间要与导出库的表空间名称和用户名称一一致,免于报错
创建用户:
create user cfqs41 identified by cfqs41 default tablespace customerchat temporary tablespace customerchat_temp;
为用户授权:
grant connect,resource,exp_full_database,imp_full_database to cfqs41;
grant exp_full_database,imp_full_database to cfqs41;
grant create any sequence to CFQS41;
grant debug any procedure to CFQS41;
grant debug connect session to CFQS41;
grant unlimited tablespace to CFQS41;
为用户授权命令可以在sqlplus复制;
4.事先将导出的文件拷贝到导入服务器,进行导入
命令:
imp cfqs41@pdborcl fromuser=cfqs41 touser=cfqs41 file=/tmp/0520/cfqs4102.dmp log=/tmp/0520/cfqs4102.log ignore=y;
5.查看日志可以看到两边导入导出的字符集是否一致,导入的日志和表是否和导出的日志和表的行数一一致。