欧美vvv,亚洲第一成人在线,亚洲成人欧美日韩在线观看,日本猛少妇猛色XXXXX猛叫

概述

寫這篇的初衷是因?yàn)橹岸疾辉趺慈ビ沞xpdp的一些參數(shù)，用到的時候卻總發(fā)現(xiàn)一些命令給忘了，然后網(wǎng)上查卻怕不小心踏坑，所以花了點(diǎn)時間總結(jié)了下，以后就直接看這篇來找expdp方面的內(nèi)容就行了。

一、基礎(chǔ)準(zhǔn)備

1、創(chuàng)建導(dǎo)出目錄

create directory dpdata as '/home/oracle/dump'; 
select * from dba_directories where directory_name='DPDATA'; 
grant read,write on directory dpdata to hwb; 

2、確保監(jiān)聽沒問題

服務(wù)器監(jiān)聽沒問題，可以通過 xx/xx來登錄數(shù)據(jù)庫

3、檢查導(dǎo)出前數(shù)據(jù)情況

SUM(s.BYTES)/1024/1024 "sizes(MB)" from s where s.owner='hwb';

count(*) from t where t.owner='hwb';

4、確定需要導(dǎo)出的用戶在哪些表空間，及其表初始化時占用的表空間大小

SELECT D.owner,D.tablespace_name, SUM(D.initial_extent)/1024/1024 initial_extent
FROM DBA_SEGMENTS D
WHERE D.owner IN
(SELECT a.username
FROM DBA_USERS A
WHERE A.account_status = 'OPEN'
AND A.username NOT IN
('SYS', 'SYSTEM', 'MGMT_VIEW', 'SYSMAN', 'DBSNMP'))
GROUP BY D.tablespace_name,D.owner
ORDER BY initial_extent desc;

SELECT SUM(D.initial_extent)/1024/1024 initial_extent FROM DBA_SEGMENTS D WHERE D.owner IN
(SELECT a.username FROM DBA_USERS A WHERE A.account_status = 'OPEN' AND A.username NOT IN ('SYS', 'SYSTEM', 'MGMT_VIEW', 'SYSMAN', 'DBSNMP'));

由此可以知道，創(chuàng)建這些元數(shù)據(jù)大約需要6.8G的空間，如果涉及到數(shù)據(jù)的話，還需要判斷數(shù)據(jù)占用空間，不然執(zhí)行導(dǎo)入的時候會因?yàn)楸砜臻g不足而不能導(dǎo)入

5、確定需要導(dǎo)出的用戶中有哪些無效的對象、及總共需要導(dǎo)出的對象數(shù)量

SELECT d.OWNER,d.status,count(1) FROM dba_objects d WHERE d.OWNER in (SELECT a.username FROM DBA_USERS A WHERE A.account_status = 'OPEN' AND A.username NOT IN ('SYS', 'SYSTEM', 'MGMT_VIEW', 'SYSMAN', 'DBSNMP')) group by d.OWNER, d.status;

SELECT d.OWNER,d.OBJECT_NAME,d.OBJECT_TYPE,d.status FROM dba_objects d WHERE d.status = 'INVALID' and d.owner in (SELECT a.username FROM DBA_USERS A WHERE A.account_status = 'OPEN' AND A.username NOT IN ('SYS', 'SYSTEM', 'MGMT_VIEW', 'SYSMAN', 'DBSNMP'));

二、expdp命令

1、導(dǎo)出用戶

expdp scott/tiger@orcl =scott =expdp.dmp =;

2、導(dǎo)出表

expdp scott/tiger@orcl =emp,dept =expdp.dmp =;

3、按查詢條件導(dǎo)

expdp scott/tiger@orcl = =expdp.dmp =emp query='where =20';

4、按表空間導(dǎo)

expdp /@orcl = =.dmp =temp,;

5、導(dǎo)整個數(shù)據(jù)庫

expdp /@orcl = =full.dmp full=y;

6、利用=導(dǎo)出元數(shù)據(jù)

當(dāng)設(shè)置為ALL 時,將導(dǎo)出對象定義及其所有數(shù)據(jù).為時,只導(dǎo)出對象數(shù)據(jù),為時,只導(dǎo)出對象定義

expdp hwb/hwb directory=DPDATA dumpfile=META.dmp logfile=META.log content=metadata_only schemas=TEST,SCOTT

7、并行導(dǎo)出scott用戶下所有的內(nèi)容

expdp scott/tiger@orcl directory=DPDATA schemas=scott dumpfile=scott_all%U.dmp logfile=scott_all.log parallel=2

8、expdp腳本導(dǎo)出

expdp \'/ as sysdba\' parfile=parfile.ora
# vi parfile.ora
directory=xxxx
dumpfile=xxxx%U.dmp
filesize=20G
logfile=xxxx.log
tables=xxxx.xxxx
#schemas=xxxx
#tablespaces=xxxx
#full=y
content=ALL | DATA_ONLY | METADATA_ONLY
#EXCLUDE=object_type[:name_clause] [,….]
#estimate={BLOCKS | STATISTICS}
#ESTIMATE_ONLY=y
parallel=3
EXCLUDE=table:"IN('REPORT_DATA','FORMATDOC_DATA','CUSTOMER_FSRECORD','REPORT_RECORD')"
Exclude specific object types.
EXCLUDE=table:"IN('REPORT_DATA','FORMATDOC_DATA','CUSTOMER_FSRECORD','REPORT_RECORD')"

三、impdp命令

1、導(dǎo)入用戶（從用戶scott導(dǎo)入到用戶scott）

impdp scott/tiger@orcl = =expdp.dmp =scott;

2、導(dǎo)入表（從scott用戶中把表dept和emp導(dǎo)入到用戶中）

impdp /@orcl = =expdp.=scott.dept,scott.emp =scott:;

3、導(dǎo)入表空間

impdp /@orcl = =.dmp =;

4、導(dǎo)入數(shù)據(jù)庫

impdb /@orcl = =full.dmp full=y;

5、追加數(shù)據(jù)

impdp system/manager@orcl directory=dump_dir dumpfile=expdp.dmp schemas=systemtable_exists_action

四、測試驗(yàn)證

1、刪除錯誤用戶下的包、存過和函數(shù)

執(zhí)行如下的腳本刪除相應(yīng)的錯誤對象：

BEGIN
FOR CUR IN (SELECT 'DROP ' || D.OBJECT_TYPE || ' ' || D.OBJECT_NAME SQLT FROM dba_objects d WHERE d.OWNER = 'NWPP_TEST'
AND d.CREATED >=to_date('2019-04-15 00:00:00','YYYY-MM-DD HH24:MI:SS')
AND D.OBJECT_TYPE IN ('FUNCTION', 'PROCEDURE', 'PACKAGE')) LOOP
EXECUTE IMMEDIATE CUR.SQLT;
END LOOP;
END;

2、數(shù)據(jù)校驗(yàn)

執(zhí)行如下腳本和源庫作比較oracle 導(dǎo)出數(shù)據(jù)庫表結(jié)構(gòu)oracle 導(dǎo)出數(shù)據(jù)庫表結(jié)構(gòu)，查看數(shù)據(jù)是否完整。

SELECT d.OWNER,d.status,count(1) FROM dba_objects d WHERE d.OWNER in (SELECT a.username FROM DBA_USERS A WHERE A.account_status = 'OPEN' AND A.username NOT IN ('SYS', 'SYSTEM', 'MGMT_VIEW', 'SYSMAN', 'DBSNMP')) group by d.OWNER, d.status;
SELECT d.OWNER,d.OBJECT_NAME,d.OBJECT_TYPE,d.status FROM dba_objects d WHERE d.status = 'INVALID' and d.owner in (SELECT a.username FROM DBA_USERS A WHERE A.account_status = 'OPEN' AND A.username NOT IN ('SYS', 'SYSTEM', 'MGMT_VIEW', 'SYSMAN', 'DBSNMP'));

這里主要做記錄用，所以會放命令比較多，部分有待驗(yàn)證，大家看下有什么可以補(bǔ)充的地方，歡迎在下面留言探討。后面會分享更多DBA方面內(nèi)容，感興趣的朋友可以關(guān)注下！