Category Archives: Datapump

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

For Oracle 18c / 12c / 11g / 10g:

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 the package dbms_metadata, function get_ddl user in the official Oracle 18c/12c documentation :
https://docs.oracle.com/en/database/oracle/oracle-database/18/arpls/DBMS_METADATA.html

For Oracle <10 (runs well too with 10g, 11g, 12.2 and 18c):

clear screen

accept uname prompt 'Display the DDL for this specific user: '
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

Another use of this procedure is to copy a user account from one Oracle instance to another. With the same password, grants and roles without using the expdp/impdp tools.

Another method to retreive the Data Description Language for an Oracle user with all roles and Privileges:

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/

Author: Vincent Fenoll
Compatibility: Oracle 18c, 12c, 11g

Datapump

How to cleanup orphaned datapump jobs in Oracle

2018/09/26 Vincent Leave a comment

Download Brave : Secure, Fast & Private Browser with Adblocker

How can I cleanup old (orphaned) datapump jobs in DBA_DATAPUMP_JOBS ?

Cause: In many cases you have stop Oracle data pump jobs, shutdown database during export/import or use undocumented parameter KEEP_MASTER=Y. In these cases the master table remains in the database and it’s better to delete them.

Below is a step-by-step instruction on how to do this.

Step 1. Determine in SQL*Plus if Data Pump jobs exist in the dictionary

Identify these jobs and ensure that the listed jobs in dba_datapump_jobs are not export/import Data Pump jobs that are active: status should be ‘NOT RUNNING’ and not attached to a session:

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;

Step 2: 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.

Step 3: Identify orphan DataPump external tables

Check and drop external tables created for datapump jobs with select object_name, created from dba_objects where object_name like ‘ET$%’

Step 4: Purge recycle bin

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

Step 8: Confirm that the job has been removed

Run sql statement from step 1.

Now you should be able to run your script with the same job name without any issues.

Hope this post will help!

Author: Vincent Fenoll – Oracle DBA

Compatibility: Oracle Database – Standard/Enterprise Edition – Version 10g to 18c [Release 10.1 to 12.2/18]

Datapump, Triggers, Troubleshooting

Trace a specific ORA- error

2017/01/27 Vincent Leave a comment

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

Datapump

Standard datapump use

2011/05/02 Vincent Leave a comment

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

Sometimes, we may get a requirement to delete datapump jobs which are stopped abruptly due to some reason, you can purge data pump jobs.

Author: Vincent Fenoll – Oracle DBA

Compatibility: Oracle 10 to Oracle 18.

Oracle Database SQL scripts