ORA-01940错误的解决过程

在将每天的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 into 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,

　　interval => ’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,interval,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,interval,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,interval,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#’;

或者

SQL> drop user aa;
drop user aa
*
ERROR at line 1:
ORA-01940: cannot drop a user that is currently connected


SQL> select sid,serial# from v$session
  2  where username='AA';

       SID    SERIAL#
---------- ----------
       136         33

SQL> alter system kill session '136,33';

System altered.

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

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

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