全部課程
發(fā)布時(shí)間: 2017-06-16 15:22:52
數(shù)據(jù)泵導(dǎo)出過程:
SQL> create user aaa identified by aaa_1234 account unlock;
User created.
SQL> grant dba to aaa;
Grant succeeded.
create or replace directory test as '/oraarch1/datadump';
SQL> grant read,write on directory test to aaa;
Grant succeeded.
expdp aaa/aaa_1234 full=y directory=test dumpfile=expdp_full_%U.dmp logfile=expdp.log filesize=2G parallel=6
ORA-39171: Job is experiencing a resumable wait.
ORA-01691: unable to extend lob segment AAA.SYS_LOB0004117998C00039$$ by 1024 in tablespace USERS
. . exported "ZJYD"."MLOG$_SF_ARREARAGE" 7.338 GB 246625643 rows
ORA-39171: Job is experiencing a resumable wait.
ORA-01691: unable to extend lob segment AAA.SYS_LOB0004117998C00039$$ by 1024 in tablespace USERS
. . exported "ZJYD"."SF_YDLW_BATCH_BALANCE_LOG" 16.92 GB 96330619 rows
ORA-39171: Job is experiencing a resumable wait.
ORA-01691: unable to extend lob segment AAA.SYS_LOB0004117998C00039$$ by 1024 in tablespace USERS
錯(cuò)誤原因:aaa用戶所在的user表空間不足
導(dǎo)數(shù)前:
導(dǎo)數(shù)后:
USERS表空間USED_RATE(%)為94%,其他不變
解決:
刪除現(xiàn)有的aaa用戶,重建aaa到Y(jié)D_DATA表空間
SQL> create user aaa identified by aaa_1234 default tablespace YD_DATA account unlock;
User created.
SQL> grant dba to aaa;
Grant succeeded.
—————————————————————————————————————————
drop user ts cascade;
ORA-01940: cannot drop a user that is currently connected --------------------無法刪除當(dāng)前已連接的用戶
查找?guī)ぬ?hào)下哪些連接在運(yùn)行:
select username,sid,serial# from v$session where username = 'USERNAME'
alter system kill session'532,4562'
然后就可以刪除該用戶了
————————————————————————————————————————————————
刪除數(shù)據(jù)泵導(dǎo)出進(jìn)程:
一、停止EXP/IMP優(yōu)化速度
可以直接KILL 進(jìn)程,但先要KILL 父進(jìn)程,然后KILL子進(jìn)程,只KILL子進(jìn)程,EXP/IMP還會(huì)在后臺(tái)執(zhí)行的
樣例:ps -ef |grep imp 查詢到pid,kill -9 pid 、kill -9 ppid,就可以了
二、停止EXPDP/IMPDP
這里就不能簡單的用KILL進(jìn)程來處理了,因?yàn)檫@里是一個(gè)JOB
select job_name,state from dba_datapump_jobs;
如果STATE是executing則需要通過expdp參數(shù)刪除
方法:
樣例1:
C:\Documents and Settings\Administrator>expdp system/sys attach=SYS_EXPORT_FULL_01
Export: Release 11.2.0.1.0 - Production on 星期六 7月 13 23:53:55 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
連接到: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
作業(yè): SYS_EXPORT_FULL_01
所有者: SYSTEM
操作: EXPORT
創(chuàng)建者權(quán)限: TRUE
GUID: 7374DCC3E7D547B3B755865E4DB59F10
開始時(shí)間: 星期六, 13 7月, 2013 23:53:57
模式: FULL
實(shí)例: tony
較大并行度: 1
EXPORT 個(gè)作業(yè)參數(shù):
參數(shù)名 參數(shù)值:
CLIENT_COMMAND system/******** directory=dir_dump dumpfile=dir_dump:bigtable.dmp logfile=dir_dump:bigtable.log full=y
狀態(tài): IDLING
處理的字節(jié): 0
當(dāng)前并行度: 1
作業(yè)錯(cuò)誤計(jì)數(shù): 0
轉(zhuǎn)儲(chǔ)文件: d:\impdp\bigtable.dmp
寫入的字節(jié): 5,177,344
Worker 1 狀態(tài):
進(jìn)程名: DW00
狀態(tài): UNDEFINED
Export> stop_job
是否確實(shí)要停止此作業(yè) ([Y]/N): y
是否確實(shí)要停止此作業(yè) ([Y]/N): Y
是否確實(shí)要停止此作業(yè) ([Y]/N): Y
是否確實(shí)要停止此作業(yè) ([Y]/N): Y
是否確實(shí)要停止此作業(yè) ([Y]/N): Y ---------------就是停止不了,在dba_datapump_jobs還是能查到記錄,只是JOB已經(jīng)停止了。
-----已經(jīng)解決了,需要輸入YES
Export> KILL_JOB
是否確實(shí)要停止此作業(yè) ([Y]/N): YES
在執(zhí)行的時(shí)候CTL+C就可以進(jìn)入交互模式,windows和LINUX下都是一樣的。
重新進(jìn)入EXPDP/IMPDP,就可以執(zhí)行下面的交互命令:
$expdp username/password,
交互模式常用命令:
CONTINUE_CLIENT返回到記錄模式。假如處于空閑狀態(tài), 將重新啟動(dòng)作業(yè)。
START_JOB 啟動(dòng)恢復(fù)當(dāng)前作業(yè)。
STATUS在默認(rèn)值 (0) 將顯示可用時(shí)的新狀態(tài)的情況下,要監(jiān)視的頻率 (以秒計(jì)) 作業(yè)狀態(tài)。
STATUS=[interval]
STOP_JOB順序關(guān)閉執(zhí)行的作業(yè)并退出客戶機(jī)。
STOP_JOB=IMMEDIATE 將立即關(guān)閉數(shù)據(jù)泵作業(yè)。
上一篇: zabbix理論知識(shí)