在将每天DMP逻辑备份导入到个数据库过程中在导数据的前不能删除用户操作出现下面:

OS:linux

Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production



SQL> drop user test cascade;

drop user test cascade

*

第 1 行出现:

ORA-01940: cannot drop a user that is currently connected



解决:

查找原因是由于在原来DMP逻辑备份中test用户下数据中存在job由于在导入数据时这些job已经开始执行所以造成在进程中存在

该test用户执行JOB任务会话介绍说明该用户正在被使用所以该用户是不能被drop掉





下面ORA-01940复现过程:

创建测试表

drop table test1　

select * from test1



create table test1 (datetime date);　

　　

创建个自定义过程

create or replace procedure MYPROC as

begin

for i in 1..100000 loop

insert o TEST1(datetime) values(sysdate);

end loop;

end;

PL/SQL procedure successfully completed





创建JOB,每天1440分钟即每5分钟运行test过程次

declare

job number;

begin

sys.dbms_job.submit

(job => job,

what =>'MYPROC;',

next_date => sysdate,

erval => 'sysdate+(1/1440)*5');

commit;

end;

PL/SQL procedure successfully completed

　　

运行JOB

SQL> begin

2 sys.dbms_job.run(241);

3 end;

4 /

PL/SQL procedure successfully completed





此时需要删除用户daimin时就会报下面

以sys登陆系统删除daimin用户

--查看daimin用户下job

SQL> select job,what,erval,to_char(next_date, 'yyyy-mm-dd hh24:mi:ss ')

2 from dba_jobs where schema_user='DAIMIN';

JOB WHAT INTERVAL TO_CHAR(NEXT_DATE,'YYYY-MM-DDH

---------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ------------------------------

241 MYPROC; sysdate+(1/1440)*5 2008-12-16 10:10:03



查看正在运行JOB

SQL> select * from dba_jobs_running;



SID JOB FAILURES LAST_DATE LAST_SEC THIS_DATE THIS_SEC INSTANCE

---------- ---------- ---------- ----------- ---------------- ----------- ---------------- ----------

1619 241 2008-12-16 10:25:51 0



查看daimin用户活动会话

SQL> select sid,serial#,username,program,machine,status

2 from v$session

3 where username='DAIMIN'

4 AND STATUS='ACTIVE';



SID SERIAL# USERNAME PROGRAM MACHINE STATUS

---------- ---------- ------------------------------ ------------------------------------------------ ---------------------------------------------------------------- --------

1619 2360 DAIMIN plsqldev.exe STAPLES\DELLF98P-10QC ACTIVE



--以sys用户下来删除daimin用户会报下面

SQL> drop user daimin cascade;

drop user daimin cascade

ORA-01940: cannot drop a user that is currently connected





试图删除job

SQL> begin

2 FOR r_job IN (select job,what,erval,to_char(next_date, 'yyyy-mm-dd hh24:mi:ss ')

3 from dba_jobs where schema_user='DAIMIN'

4 ) LOOP

5 dbms_job.REMOVE(r_job.job);

6 end loop;

7 commit;

8 end;

9 /

begin

FOR r_job IN (select job,what,erval,to_char(next_date, 'yyyy-mm-dd hh24:mi:ss ')

from dba_jobs where schema_user='DAIMIN'

) LOOP

dbms_job.REMOVE(r_job.job);

end loop;

commit;

end;

ORA-23421: job number 241 is not a job in the job queue

ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86

ORA-06512: at "SYS.DBMS_IJOB", line 529

ORA-06512: at "SYS.DBMS_JOB", line 171

ORA-06512: at line 5

分析:以sys用户来删除daimin用户下JOB所以会报错



解决思路方法:

1、杀掉执行JOB任务会话进程；

alter system disconnect session 'sid,serial#' immediate;

或者Alter system kill session 'sid,serial#';



2、通过设置在文件中设置参数JOB_QUEUE_PROCESSES=0来使Oracle会杀掉CJQ0及相应job进程；

修改init.ora文件然后重新启动数据库,或者执行ALTER SYSTEM SET JOB_QUEUE_PROCESSES = 0；

修改过参数 设置JOB_QUEUE_PROCESSES=0的后重新启动数据库的后进行逻辑备份导入操作先删除用户此时删除成功！！



参考网页: