#### 檢查相關SQL
``` sql
--檢視字符集
select userenv('language') from dual;
export NLS_LANG="以上查詢結果"
--查詢當前空表11g+
set heading off; 
set echo off; 
set feedback off; 
set termout on; 
spool C:\allocate.sql; 
Select 'alter table '||table_name||' allocate extent;' from user_tables where num_rows=0; 
spool off;

--檢視需要遷移使用者
select d.username,u.password,d.default_tablespace,d.TEMPORARY_TABLESPACE
from dba_users d,user$ u
where d.username=u.name and account_status='OPEN'
and username not in('SYS','SYSTEM','DBSNMP','SYSMAN','MGMT_VIEW');

--檢視同義詞
SELECT 'CREATE  or replace PUBLIC SYNONYM '||synonym_name||' FOR '||table_owner||'.'||table_name||';' 
FROM dba_SYNONYMS WHERE table_owner in ('scott','hr','system' )  AND owner='PUBLIC';

```

#### 生成建立使用者的語句
``` sql
--生成語句(使用者建立語句、許可權等可自行匯出)，手動查詢備用
set lines 2000
col user_ddl for a140
select 'create user '||d.username||' identified by values '''||u.password||''' default tablespace '||d.default_tablespace||' temporary tablespace '||d.TEMPORARY_TABLESPACE||';'  as user_ddl
from dba_users d,user$ u
where d.username=u.name and account_status='OPEN'
and username not in('SYS','SYSTEM','DBSNMP','SYSMAN','MGMT_VIEW');

--許可權（匯出時含有許可權，可不統計）
select 'grant '||granted_role||' to '||grantee||';'  as user_role
from dba_role_privs
where grantee in (select username from dba_users where account_status='OPEN'
and username not in('SYS','SYSTEM'));

select 'grant '||PRIVILEGE||' to '||grantee||';' as user_priv
from DBA_SYS_PRIVS
where grantee in (select username from dba_users where account_status='OPEN'
and username not in('SYS','SYSTEM'));

--表空間  注意是否自動擴充套件,注意系統表空間大小
--全部表空間
select dbms_metadata.get_ddl('TABLESPACE',tablespace_name) from dba_tablespaces;
--某一個表空間
select dbms_metadata.get_ddl('TABLESPACE','tablespace_name') from dual;
```

#### 匯出匯入語句參考
``` shell
expdp '/ as sysdba' directory=my_exp dumpfile=myexpdb%U.dmp logfile=myexpdb.log parallel=2 EXCLUDE=STATISTICS parfile=mypar.par cluster=no

#mypar.par m注意可以排除系統使用者及非OPEN使用者  全庫匯出無需建立使用者
FULL=Y
EXCLUDE=SCHEMA:"in (select username from dba_users where account_status<>'OPEN' or username in('SYS','SYSTEM'))"

#排除部分使用者表
expdp sys/a directory=exp_dmp dumpfile=scott.dmp logfile=scott1.log schemas=scott 
EXCLUDE=table:\"in $select table_name from dba_tables where owner\=\'SCOTT\' and table_name\=\'EMP\'$\"

#匯出全庫，壓縮
expdp \'/ as sysdba\' directory=dump_dir dumpfile=full_sys_all.dmp logfile=expdp_full_sys_all.log parallel=2 full=y COMPRESSION=all

#匯入
impdp \'/ as sysdba\' directory=full_dmp dumpfile=dumpfull_2019%U.dmp logfile=dumpfull_2019_imp.log parallel=4 TABLE_EXISTS_ACTION=SKIP exclude=TABLESPACE cluster=N

#收集統計資訊
begin
dbms_stats.gather_database_stats(
ESTIMATE_PERCENT=>DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'for all indexed columns size auto', cascade=>true, degree=>8);
end;
/
```
#### 作業情況檢視
``` sql
-- 查詢資料泵作業:
set lines 150
set pages 999
col owner_name for a15
col job_name for a30
col operation for a40
col job_mode for a30
col state for a15
col attached_sessions for 20
SELECT owner_name, job_name, operation, job_mode, 
state
--, attached_sessions 
FROM dba_datapump_jobs 
WHERE job_name NOT LIKE 'BIN$%' 
ORDER BY 1,2;

--啟停作業
expdp sys/firsoul ATTACH=SYS_EXPORT_SCHEMA_01
stop_job/start_job

-- 查詢資料泵的 master 表,停止無法刪除
set lines 150
col OWNER.OBJECT for a40
SELECT o.status, o.object_id, o.object_type, 
       o.owner||'.'||object_name "OWNER.OBJECT" 
  FROM dba_objects o, dba_datapump_jobs j 
WHERE o.owner=j.owner_name AND o.object_name=j.job_name 
   AND j.job_name NOT LIKE 'BIN$%' ORDER BY 4,2;

--檢視作業情況
SET LINE 9999
COL OWNER_NAME FOR A10
COL JOB_NAME FOR A25
COL OPERATION FOR A10
COL JOB_MODE FOR A10
COL STATE FOR A15
COL OSUSER FOR A10
COL "DEGREE|ATTACHED|DATAPUMP" FOR A25
COL SESSION_INFO FOR A20  
SELECT DS.INST_ID,
       DJ.OWNER_NAME,
       DJ.JOB_NAME,
       TRIM(DJ.OPERATION) OPERATION,
       TRIM(DJ.JOB_MODE) JOB_MODE,
       DJ.STATE,
       DJ.DEGREE || ',' || DJ.ATTACHED_SESSIONS || ',' ||DJ.DATAPUMP_SESSIONS "DEGREE|ATTACHED|DATAPUMP",
       DS.SESSION_TYPE,
       S.OSUSER ,
       (SELECT S.SID || ',' || S.SERIAL# || ',' || P.SPID
          FROM GV$PROCESS P
         WHERE S.PADDR = P.ADDR
           AND S.INST_ID = P.INST_ID) SESSION_INFO
  FROM DBA_DATAPUMP_JOBS DJ --GV$DATAPUMP_JOB  
  FULL OUTER JOIN DBA_DATAPUMP_SESSIONS DS --GV$DATAPUMP_SESSION
    ON (DJ.JOB_NAME = DS.JOB_NAME AND DJ.OWNER_NAME = DS.OWNER_NAME)
  LEFT OUTER JOIN GV$SESSION S
    ON (S.SADDR = DS.SADDR AND DS.INST_ID = S.INST_ID)
 ORDER BY DJ.OWNER_NAME, DJ.JOB_NAME;
```

#### DBLINK
``` sql
create public database link dblink1 connect to mytest identified by test
  using '(DESCRIPTION =(ADDRESS_LIST =(ADDRESS =(PROTOCOL = TCP)(HOST = 192.168.8.24)(PORT = 1521)))(CONNECT_DATA =(SERVICE_NAME=mydb)))';

DROP PUBLIC DATABASE LINK dblink2;
select sysdate from dual@dblink1;

--匯入
impdp system/oracle directory=sh_dmp network_link=dblink1 remap_schema=mytest:test remap_tablespace=users:test
```

【Datapump】Oracle資料泵遷移資料命令參考(expdp/impdp說明）

相關文章