removing oracle datapump job

you may run into situations where you need to stop an expdp or impdp job from running. killing the datapump process from the Operating System level won’t stop the job , infact if you use the below query you may still see it in “RUNNING” state.

SQL> select * from dba_datapump_jobs;

SOOOOOO you need to kill the datapump job from database-level as follows:

$ORACLE_HOME/bin/expdp “‘/as sysdba'” attach=SYS.”‘EXPORT_TABLE_NAME‘”

Export> KILL_JOB

Are you sure you wish to stop this job ([yes]/no): yes

Check it again :

select job_name,owner_name,state from dba_datapump_jobs;

Remark: EXPORT_TABLE_NAME is the table name resulted in the query unser SYS account.


As part of new Oracle 12c Database security implementation, they have stripped the system privilege ” SELECT ANY DICTIONARY ” from accessing the following SYS tables: USER$, ENC$,DEFAULT_PWD$, LINK$, USER_HISTORY$, CDB_LOCAL_ADMINAUTH$, XS$VERIFIERS.

The reason they removed access from these tables, is just in case you grant “select any dictionary” to a non-dba account, this account wont be able to see the “hashed passwords”. Only the SYS account will be able to query these tables.

in 12c if you simulate that:


SQL> grant select any dictionary to account1;


sqlplus account1/password@DB1

SQL> select * from SYS.USER$;

ORA-01031: insufficient privileges

Bingo…… can’t query USER$ table although you are granted the “select any dictionary” privilege.

TOAD ORA-01031: insufficient privileges when connecting to Oracle 12c

An issue  faced is that when we try to connect from TOAD (version: 11.6 ) to a newly upgraded Oracle database 12c we faced the error:

ORA-01031: insufficient privileges

TOAD Error Message ORA-01031

The reason we are facing this problem, is the newly security implementation by Oracle in 12c for the system privilege SELECT ANY DICTIONARY. They have stripped this system privilege from accessing the following tables:USER$, ENC$,DEFAULT_PWD$, LINK$, USER_HISTORY$, CDB_LOCAL_ADMINAUTH$, XS$VERIFIERS

The current TOAD version checks the old (SELECT ANY DICTIONARY) which already has this table SYS.USER$ part of its permission when it can’t access this table, it throws the error message:  ORA-01031

The reason in 12c they stripped those tables from “SELECT ANY DICTIONARY” is to protect the passwords in case this privilege is granted to non-DBA account , so the hashed passwords are not exposed (ONLY SYS account can query those tables).

So, to let the current version of TOAD works for 12c execute the following sql query:

SQL> grant select on sys.user$ to database_account;

Hope This Helps 😉