expdp/impdp

expdp nc65/nc65_xgl_xxh directory=bakdir dumpfile=nc_%u.dmp logfile=0326.log parallel=8 compression=all cluster=no

stop_job=immediate

impdp nc65/nc65_xgl_xxh directory=mydir dumpfile=nc_%u.dmp logfile=0326.log parallel=8

---

content=metadata_only
然后content=data_only

---

---

查看是否有中断的expdp job:
select * from dba_datapump_jobs where state = 'NOT RUNNING' and owner_name=’NC65’;
select a.sid,a.event,a.blocking_session,a.wait_class from v$session a where a.module like 'Data Pump%';

pdb需要进入容器里面创建目录：
show pdbs
alter session set container=orclpdb;

expdp NCC10/NCC10@172.20.17.29:1521/orclpdb schemas=NCC10 DIRECTORY=ncdb DUMPFILE=NCC1020240318.dmp LOGFILE=NCC1020240318.log

D:>expdp nc633/nc633 schemas=nc633 directory=CTSW dumpfile=nc6330408.dmp logfil
e=nc6330408.log exclude=table:"in('SM_FILESTOREVIEW')"

impdp导入：
create directory qyldir as '/oracle/expdata1';
grant read on directory qyldir to system;
grant write on directory qyldir to system;
impdp nnc/nnc directory=mydir dumpfile=biphnst2022_20231109021001.dmp logfile=20231116_impdp.log remap_schema=biphnst2022:nnc

create tablespace nnc_data01 datafile 'E:\data\nnc_data01.dbf' size 30G autoextend on next 500m extent management local uniform size 1m;
alter tablespace nnc_data01 add datafile 'E:\data\nnc_data02.dbf' size 10g autoextend on next 500m maxsize unlimited;
alter tablespace nnc_data01 add datafile 'E:\data\nnc_data03.dbf' size 10g autoextend on next 500m maxsize unlimited;
create tablespace nnc_index01 datafile 'E:\data\nnc_index01.dbf' size 5g autoextend on next 500m extent management local uniform size 128k;
create user nnc identified by nnc default tablespace nnc_data01 temporary tablespace temp;
grant dba,connect,resource to nnc;

alter tablespace SYSTEM add datafile '+DATA/ORCL/DATAFILE/system001.dbf' size 1G AUTOEXTEND ON;

oracle怎么删除directory 语法如下：drop directory dirname;
创建或者修改directory目录：create or replace directory dum_date_dir as '/home/oracle/datatmp';

CREATE TABLESPACE NNC_DATA01 DATAFILE 'D:\ORACLE\ORADATA\ORA11G\nnc_data01.dbf' SIZE 500M
AUTOEXTEND ON NEXT 50M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 256K ;
CREATE TABLESPACE NNC_INDEX01 DATAFILE 'D:\ORACLE\ORADATA\ORA11G\nnc_index01.dbf' SIZE 500M
AUTOEXTEND ON NEXT 50M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K ;
CREATE USER NC65 IDENTIFIED BY NC65 DEFAULT TABLESPACE NNC_DATA01 TEMPORARY TABLESPACE temp;
GRANT connect,dba to NC65;

公共参数
alter system set "_optimizer_cartesian_enabled"=false;
alter system set "_b_tree_bitmap_plans"=false;
alter system set "_optimizer_skip_scan_enabled"=false;
alter system set deferred_segment_creation=false scope=both;
alter system set event= '10949 trace name context forever, level 1' scope=spfile;
alter system set audit_trail=none scope=spfile;
alter system set "_optimizer_group_by_placement"=false scope=both;
⚫ Oracle 12.1 以上版本还需要设置如下参数

1. 建议必须设置上，对性能影响很大，会大量减少 redo 的量
   ALTER SYSTEM SET TEMP_UNDO_ENABLED = TRUE;
   2.将 ddl 操作记录到 alert.log
   ALTER SYSTEM SET enable_ddl_logging=TRUE SCOPE=BOTH;
   3.动态采样调整成 2
   ALTER SYSTEM SET optimizer_dynamic_sampling=2;
   4.创建对象(如表)，初始没有数据，立即创建 segment
   ALTER SYSTEM SET deferred_segment_creation=false;
   ⚫ 禁止 sql tuning advisor
   BEGIN
   DBMS_AUTO_TASK_ADMIN.disable(client_name => 'sql tuning advisor',
   operation => NULL,
   window_name => NULL);
   END;

alter system set deferred_segment_creation=false scope=both;