Generate user DDL with dbms_metadata.get_ddl user

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

How to cleanup orphaned datapump jobs in Oracle

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]

Sql Server loop through databases

How can I run the same command on all SQL Server databases without cursors

The stored procedure sp_MSforeachdb allows us to iterate through each database in a SQL Server instance without a loop statement.
It’s usage is similar than sp_MSforeachtable; simply execute the stored procedure, passing it the command that you’d like to execute as a string.

loop all databases sql server

For this example, we will truncate the transaction log file on all databases (but exclude system DBs as master, msdb, model and tempdb).

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

  1. Call stored procedure sp_MSForeachdb
  2. Display current db for information
  3. Set variable to transaction log name
  4. Change the database recovery model to SIMPLE for the database
  5. Shrink the truncated log file to 1 MB
EXEC master..sp_MSForeachdb '
USE [?]
IF ''?'' <> ''master'' AND ''?'' <> ''model'' AND ''?'' <> ''msdb'' AND ''?'' <> ''tempdb''
BEGIN
-- display current db
SELECT ''?'';

-- set transaction log name
DECLARE @FileName varchar(300)
SELECT @FileName = name from sys.database_files where type=1

-- Change the database recovery model to SIMPLE.
ALTER DATABASE [?] SET RECOVERY SIMPLE;

-- Shrink the truncated log file to 1 MB.
DBCC SHRINKFILE (@FileName, 1);

END
'

Thank you!

Author: Vincent Fenoll – SQL Server DBA Montreal

Dataguard management without dgbroker

Standby stop start apply log

I want to start or stop redo apply without dgbroker. I also want to know other queries to check Dataguard and standby status.
These commands are compatible with Oracle 18c, 12c, 11g, 10g, 9i.

Starting Redo Apply on standby database

 

To start Redo Apply in the foreground, issue the following SQL statement.

Without Real Time Apply (RTA) on standby database

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

 

With Real Time Apply (RTA)

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;

 

Stopping Redo Apply on standby database

To stop Redo Apply in the foreground, issue the following SQL statement.

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

 

Monitoring Redo Apply on Physical Standby Databases

 

Last sequence received and applied

SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference"
FROM
(SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,
(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL
WHERE
ARCH.THREAD# = APPL.THREAD#;

 

Standby database process status

select distinct process, status, thread#, sequence#, block#, blocks from v$managed_standby ;

 

If using real time apply

select TYPE, ITEM, to_char(TIMESTAMP, 'DD-MON-YYYY HH24:MI:SS') from v$recovery_progress where ITEM='Last Applied Redo';

or,

select recovery_mode from v$archive_dest_status where dest_id=1;

 

Author: Vincent Fenoll (Oracle DBA in Montreal)

Flash Recovery Area (FRA)

Sometimes the Flash Recovery Area (FRA) is full and the Oracle DBA wants to know what is it use, size and the list of occupants (archives, RMAN backups pieces or image copies, flashback logs).

To Enable /Disable the FRA, it’s here.

-- Use (MB) of FRA
set lines 100
col name format a60

select 
   name,
  floor(space_limit / 1024 / 1024) "Size MB",
  ceil(space_used / 1024 / 1024) "Used MB"
from v$recovery_file_dest;

-- FRA Occupants
SELECT * FROM V$FLASH_RECOVERY_AREA_USAGE;

-- Location and size of the FRA
show parameter db_recovery_file_dest

-- Size, used, Reclaimable 
SELECT 
  ROUND((A.SPACE_LIMIT / 1024 / 1024 / 1024), 2) AS FLASH_IN_GB, 
  ROUND((A.SPACE_USED / 1024 / 1024 / 1024), 2) AS FLASH_USED_IN_GB, 
  ROUND((A.SPACE_RECLAIMABLE / 1024 / 1024 / 1024), 2) AS FLASH_RECLAIMABLE_GB,
  SUM(B.PERCENT_SPACE_USED)  AS PERCENT_OF_SPACE_USED
FROM 
  V$RECOVERY_FILE_DEST A,
  V$FLASH_RECOVERY_AREA_USAGE B
GROUP BY
  SPACE_LIMIT, 
  SPACE_USED , 
  SPACE_RECLAIMABLE ;

-- After that you can resize the FRA with:
-- ALTER SYSTEM SET db_recovery_file_dest_size=xxG;

-- Or change the FRA to a new location (new archives will be created to this new location):
-- ALTER SYSTEM SET DB_RECOVERY_FILE_DEST='/u....';

FLASH RECOVERY AREA architecture

It is important to monitor regularly space usage in the fast recovery area. The Oracle DBA must make sure that the FRA is large enough to contain backups and other recovery-related files.

If the PERCENT_FULL value (V$RECOVERY_FILE_DEST) is frequently close to 100% after several recent backups; consider allocating more space for your flash recovery area or decrease the retention time of your backups.

Oracle Database provides two views to monitor fast recovery area space usage, V$RECOVERY_FILE_DEST and V$RECOVERY_AREA_USAGE.

A best practice is to be generous on FRA size!

Compatibility: Oracle 18c, 12c, 11g, 10g
Author: Vincent FENOLL – Oracle DBA Montreal

example DBA 18c 12c 11g Unix