How to cleanup orphaned datapump jobs

How can I cleanup old (orphaned) datapump jobs?

Identify these jobs:

SET lines 150
COL owner_name FORMAT a10
COL job_name FORMAT a20
COL operation FORMAT a10

SELECT owner_name, job_name, operation
FROM dba_datapump_jobs where state='NOT RUNNING' and attached_sessions=0;

 

Drop the master tables:

set head off
SELECT 'drop table ' || owner_name || '.' || job_name || ';'
FROM dba_datapump_jobs WHERE state='NOT RUNNING' and attached_sessions=0;

Execute the generated script.

 
If using recycling bin:
SELECT ‘purge table ‘ || owner_name || ‘.’ || ‘”‘ || job_name || ‘”;’
FROM dba_datapump_jobs WHERE state=’NOT RUNNING’ and attached_sessions=0;

 

Author: Vincent Fenoll – Oracle DBA from Montreal

Trace a specific ORA- error

How to trace a specific ORA- error that is raised and can be reproduced?
We want to find the complete Oracle stack to understand the origin of this error.

For example, I want to understand why datapump expdp/impdp raises:
ORA-01422: exact fetch returns more than requested number of rows

This time, the alert file is not very helpful.

To determine the root cause, event 1422 can be set as follows:

connect /as sysdba 
alter system set events '1422 trace name ERRORSTACK level 3'; 

[…reproduce the issue…]

The event can be turned off again using:

connect /as sysdba

alter system set events '1422 trace name ERRORSTACK off';

In the event 1422 trace file, below sql query was causing error.

*** ACTION NAME:(Select sys_context into variable) 2017-01-27 14:15:24.257
*** MODULE NAME:(Trigger DDL_AUDIT) 2017-01-27 14:15:24.257
*** SERVICE NAME:(SYS$USERS) 2017-01-27 14:15:24.257
*** SESSION ID:(3265.22843) 2017-01-27 14:15:24.257
*** 2017-01-27 14:15:24.257
ksedmp: internal or fatal error
ORA-01422: exact fetch returns more than requested number of rows
Current SQL statement for this session:
SELECT UPPER(OSUSER), PROGRAM, MODULE, CLIENT_INFO FROM V$SESSION WHERE AUDSID = SYS_CONTEXT(‘userenv’, ‘SESSIONID’)

Of course, it’s easier to locate the target module if you have already instrumented your code with DBMS_APPLICATION_INFO.SET_MODULE and DBMS_APPLICATION_INFO.SET_ACTION;

In this case, the problem was due to SYS_CONTEXT(‘USERENV’,’SESSIONID’) returning 2 rows causing the ORA-01422 and subsequent datapump failure.

The workaround of this specific problem is to disable the Trigger DDL_AUDIT.
A solution is to modify the cursor or the statement in the trigger to retreive just one row, for example:
SELECT UPPER(OSUSER), PROGRAM, MODULE, CLIENT_INFO FROM V$SESSION
WHERE AUDSID = SYS_CONTEXT(‘userenv’, ‘SESSIONID’) and rownum=1;
Another solution is to trap error with an excveption clause.

 
Author: Vincent Fenoll, Oracle DBA Montreal

Standard datapump use

How the Oracle DBA can use datapump in consistency mode with “flashback_time” :
– Create the export directory
– Verify the grantees on the export directory
– Execute the datapupmp export with expdp in consistency mode
– Execute an example of datapump import

 
-- Create the datapump directory
SQL> create directory export as '/oracle/export/SID/’;


-- Verify the grantees on the datapump directory
SQL> SELECT dbms_metadata.get_dependent_ddl('OBJECT_GRANT', 'EXPORT', 'SYS') from dual;

-- You should have a result like this as :
--
-- DBMS_METADATA.GET_DEPENDENT_DDL('OBJECT_GRANT','EXPORT','SYS')
-- --------------------------------------------------------------------------------
--
--  GRANT READ, WRITE ON DIRECTORY "EXPORT" TO "EXP_FULL_DATABASE"


-- Execute the export datapump with expdp in consistency mode:
$ expdp \'/ as sysdba\' dumpfile=exp_nomBase_02052011_0956.dmp logfile=exp_nomBase_02052011_0956.log full=y flashback_time=\"TO_TIMESTAMP\(TO_CHAR\(SYSDATE, \'YYYY-MM-DD HH24:MI:SS\'\),\'YYYY-MM-DD HH24:MI:SS\'\)\" directory=export 

-- Execute the import datapump of one schema. Remap User Scott to Vinc and 2 tablespaces to DATA:
$ impdp \'/ as sysdba\' dumpfile=exp_nomBase_02052011_0956.dmp logfile=imp_nomBase_02052011_0956.log REMAP_SCHEMA=Scott:Vinc SCHEMAS=scott REMAP_TABLESPACE=USER1:DATA  REMAP_TABLESPACE=USER2:DATA directory=export 

-- export a single schema
expdp \'/ as sysdba\' SCHEMAS=myschema directory=DATA_PUMP_DIR dumpfile=myschema.dmp logfile=myschema.log VERSION=LATEST

-- export a single table of a schema
expdp \'/ as sysdba\' tables=myschema.mytable directory=DATA_PUMP_DIR dumpfile=myschemamytable.dmp logfile=myschemamytable.log VERSION=LATEST

-- Import single table
impdp \'/ as sysdba\' tables=myschema.mytable directory=DATA_PUMP_DIR dumpfile=myschemamytable.dmp logfile=impmyschemamytable.log
-- If the table to import exists, use the option: TABLE_EXISTS_ACTION=[SKIP | APPEND | TRUNCATE | REPLACE]

— Optionally you can easily get DDL from dumpfile using the parameter sqlfile=My_file.sql

Generate user DDL

The oracle DBA can use these 2 scripts to generate DDL statements for a user with their roles, system and object privileges.

On the other side, with datapump (impdp) you can use the parameter sqlfile=My_file.sql you can easily get DDL from dumpfile:
http://www.oracle-scripts.net/standard-datapump-use/

For Oracle >=10:

clear screen
accept uname prompt 'Enter User Name : '
accept outfile prompt  ' Output filename : '

spool &&outfile..gen

SET LONG 20000 LONGCHUNKSIZE 20000 PAGESIZE 0 LINESIZE 1000 FEEDBACK OFF VERIFY OFF TRIMSPOOL ON

BEGIN
   DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'SQLTERMINATOR', true);
   DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'PRETTY', true);
END;
/

SELECT dbms_metadata.get_ddl('USER','&&uname') FROM dual;
SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','&&uname') from dual;
SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT','&&uname') from dual;
SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT','&&uname') from dual;

spool off

More information for this package in the official Oracle 12c documentation :
http://docs.oracle.com/cd/E16655_01/appdev.121/e17602/d_metada.htm#ARPLS026

For Oracle <10 (runs well too with 10g, 11g):

clear screen

accept uname prompt 'Enter User Name : '
accept outfile prompt  ' Output filename : '

col username noprint
col lne newline

SET LONG 20000 LONGCHUNKSIZE 20000 PAGESIZE 0 LINESIZE 1000 FEEDBACK OFF VERIFY OFF TRIMSPOOL ON

spool &&outfile..gen

prompt  -- generate user ddl
SELECT username, 'CREATE USER '||username||' '||
       DECODE(password, 'EXTERNAL', 'IDENTIFIED EXTERNALLY',
              'IDENTIFIED BY VALUES '''||password||''' ') lne,
       'DEFAULT TABLESPACE '||default_tablespace lne,
       'TEMPORARY TABLESPACE '||temporary_tablespace||';' lne
  FROM DBA_USERS
 WHERE USERNAME LIKE UPPER('%&&uname%')
    OR UPPER('&&uname') IS NULL
 ORDER BY USERNAME;

SELECT username, 'ALTER USER '||username||' QUOTA '||
       DECODE(MAX_BYTES, -1, 'UNLIMITED', TO_CHAR(ROUND(MAX_BYTES/1024))||'K')
       ||' ON '||tablespace_name||';' lne
  FROM DBA_TS_QUOTAS
 WHERE USERNAME LIKE UPPER('%&&uname%')
    OR UPPER('&&uname') IS NULL
 ORDER BY USERNAME;

col grantee noprint

select grantee, granted_role granted_priv,
       'GRANT '||granted_role||' to '||grantee||
       DECODE(ADMIN_OPTION, 'Y', ' WITH ADMIN OPTION;', ';')
  from dba_role_privs
 where grantee like upper('%&&uname%')
         UNION
select grantee, privilege granted_priv,
       'GRANT '||privilege||' to '||grantee||
       DECODE(ADMIN_OPTION, 'Y', ' WITH ADMIN OPTION;', ';')
  from dba_sys_privs
 where grantee like upper('%&&uname%')
 order by 1, 2;

spool off