Oracle 数据泵expdp、impdp 导入导出 保姆及别

你想要的都在这里

检查cpu核数

cat /proc/cpuinfo| grep “processor”| wc -l

设置归档
alter system set log_archive_dest_1=‘LOCATION=/data/tb_oracle/arch’;

删除用户
drop user username cascade;

创建文件
create directory expdp_dir as ‘/home/oracle’;

授权
grant read,write on directory expdp_lc to scjy;
grant DATAPUMP_EXP_FULL_DATABASE,IMPORT FULL DATABASE to scjy;

1、只导出数据，目标数据库进行数据覆盖，保留视图等内容

expdp ‘/ as sysdba’ directory=expdp dumpfile=expdp_dataonly_%U.dump logfile=expdp_dataonly.log content=data_only parallel=4;

impdp ‘/ as sysdba’ directory=expdp dumpfile=expdp_dataonly_%U.dump logfile=impdp_dataonly.log table_exists_action=replace ;

或者
impdp ‘/ as sysdba’ directory=expdp dumpfile=expdp_dataonly_%U.dump logfile=impdp_dataonly.log table_exists_action=truncate ;

2、正常导出

expdp ‘/ as sysdba’ directory=expdp dumpfile=expdp_data_%U.dump logfile=expdp_data.log parallel=4;
impdp ‘/ as sysdba’ directory=expdp dumpfile=expdp_data_%U.dump logfile=impdp_data.log parallel=4;

3、按照用户导出

expdp ‘/ as sysdba’ directory=expdp dumpfile=expdp_data_%U.dump logfile=expdp_data.log parallel=4 schemas=username1,username2;
impdp ‘/ as sysdba’ directory=expdp dumpfile=expdp_data_%U.dump logfile=impdp_data.log parallel=4 schemas=username1,username2;

4、按照集群方式导出

expdp ‘/ as sysdba’ directory=expdp dumpfile=expdp_data_%U.dump logfile=expdp_data.log parallel=4 cluster=no;

impdp ‘/ as sysdba’ directory=expdp dumpfile=expdp_data_%U.dump logfile=expdp_data.log parallel=4 cluster=no;

5、使用具体用户导出

expdp scjy/scjy@10.10.10.1:1521/scjy directory=expdp dumpfile=expdp_data_%U.dump logfile=expdp_data.log parallel=4;
impdp ‘/ as sysdba’ directory=expdp dumpfile=expdp_data_%U.dump logfile=impdp_data.log parallel=4 ;

6、19c可拔插数据库

expdp scjy/scjy@10.10.10.1:1521/scjy directory=expdp dumpfile=expdp_data_%U.dump logfile=expdp_data.log parallel=4;
impdp ‘/ as sysdba’ directory=expdp dumpfile=expdp_data_%U.dump logfile=impdp_data.log parallel=4 ;

7、导出用户，在导入的时候 导入默认表空间修改了的

expdp ‘/ as sysdba’ directory=expdp dumpfile=expdp_data_%U.dump logfile=expdp_data.log parallel=4 schemas=scjy;

impdp ‘/ as sysdba’ directory=expdp dumpfile=expdp_data_%U.dump logfile=impdp_data.log parallel=4 schemas=scjy remap_tablespace=source_tablespace:目标表空间;

8、只导出数据表结构

expdp ‘/ as sysdba’ directory=EXPDP_2020 dumpfile=expdp_asm_20230323.dump logfile=expdp_asm_20230323.log schemas=AMS content=METADATA_ONLY

9、数据库只导入索引 存在的表就进行跳过

impdp ‘/ as sysdba’ directory=impdp dumpfile=expdp_LC00019999_%U.dump logfile=impdp_LC00019999.log cluster=n schemas=LC00019999 parallel=2 remap_tablespace=USERS:CWBASE0001 TABLE_EXISTS_ACTION=SKIP;

10、数据库不相同的字符集之间进行导出导入

导出和原来一致
expdp ‘/ as sysdba’ directory=expdp dumpfile=expdp_ams_%U.dump logfile=expdp_ams_20230421.log cluster=n schemas=AMS parallel=4;

1)导入表结构

impdp ‘/ as sysdba’ directory=impdp dumpfile=expdp_ams_%U.dump logfile=impdp_ams_20230421.log cluster=n schemas=AMS parallel=4 content=METADATA_ONLY remap_tablespace=ETL:AMS,ARCHIVES:AMS;

2）改字段大小
select ‘alter table AMS.’||t.TABLE_NAME||’ modify ‘||t.COLUMN_NAME||’ ‘||t.data_type||’(‘||ceil(t.DATA_LENGTH*1.5)||’);’
from DBA_tab_columns t
where t.DATA_TYPE=‘VARCHAR2’
AND t.TABLE_NAME =‘table1’ and t.owner=‘AMS’;

3）到数据
impdp ‘/ as sysdba’ directory=expdp dumpfile=expdp_ams_%U.dump logfile=impdp_ams_20230421.log cluster=n schemas=AMS parallel=4 content=DATA_ONLY;

执行无效对象编译
@？/rdbms/admin/utlrp.sql

查看数据导入 具体线程那个表

impdp ‘/ as sysdba’ attach=SYS_IMPORT_SCHEMA_02

列如
Worker 8 Status:
Instance ID: 1
Instance name: wbdb
Host name: localhost.localdomain
Object start time: Wednesday, 22 March, 2023 11:12:58
Object status at: Wednesday, 22 March, 2023 11:12:58
Process Name: DW07
State: EXECUTING
Object Schema: LC00019999
Object Name: GSPAURESULT
Object Type: SCHEMA_EXPORT/TABLE/TABLE_DATA
Completed Objects: 1
Completed Rows: 18,225,733
Completed Bytes: 15,853,546,928
Percent Done: 19
Worker Parallelism: 1