Oracle exedp&impdp备份

#创建逻辑目录，此步不会在OS上创建真正的目录
create directory dump_dir as '/data/oracle/dumpdir';

#检查刚才创建的管理员目录是否存在
select * from dba_directories where directory_name = 'DUMP_DIR';

#切换PDB
alter session set container=SZDB;
#用system管理员给你的指定用户（要导出的表所属用户）赋予在该目录的操作权限。
grant read,write on directory dump_dir to ZJTS;

用户expdp导出数据

#查询数据表行数
select t.table_name,t.num_rows from user_tables t;

#全量导出
expdp zjts/123456@127.0.0.1/SZDB file=expdp.dmp directory=dump_dir full=y log=expdp.log;

#schemas按用户导出
expdp zjts/123456@127.0.0.1/SZDB schemas=zjts file=expdp.dmp directory=dump_dir log=expdp.log;

#导出表空间
expdp zjts/123456@127.0.0.1/SZDB tablespaces=ZJTS file=tbs.dmp directory=dump_dir log=tbs.log;

删除索引

drop index historysettlement.PK_TEST
报错ora-02429: 无法用于删除强制唯一/主键的索引错误

alter table IAM_ROLE_TL  drop constraint IAM_ROLE_TL_PK;

提前准备导入账户

sqlplus system/123456 as sysdba

alter session set container=SZDB;

create user exedpuser_test identified by 123456;

CREATE BIGFILE TABLESPACE "EXEDPSP_TEST" DATAFILE '/opt/oracle/oradata/ORCLCDB/ORCLPDB1/exedpsp_test.dbf' SIZE 100M AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;

alter user exedpuser_test default tablespace "EXEDPSP_TEST";

grant connect,resource,dba to exedpuser_test;

drop user exedpuser_test CASCADE;

DROP TABLESPACE "EXEDPSP_TEST" INCLUDING CONTENTS AND DATAFILES;
COMMIT;

导入数据

#将表空间ZJTS导入到表空间EXEDPSP_TEST，将用户zjts的数据导入到exedpuser_test
impdp exedpuser_test/123456@127.0.0.1/SZDB directory=dump_dir dumpfile=expdp.dmp REMAP_SCHEMA=zjts:exedpuser_test REMAP_TABLESPACE=ZJTS:EXEDPSP_TEST EXCLUDE=USER;

#表空间导入
impdp exedpuser_test/123456@127.0.0.1/SZDB directory=dump_dir dumpfile=tbs.dmp tablespaces=EXEDPSP_TEST;