Backup of Privs as Pre Refresh script

Make Pre-Refresh script.sql as below

ORAENV_ASK=NO
export ORACLE_SID=$1
. oraenv -s
ORAENV_ASK=YES
sqlplus -s "/as sysdba" <<EOF
set echo off
set head off
set verify off
set feedback off
set pages 0
set lines 200
spool user_create.sql
select 'create user '||USERNAME||' identified by Welcome#123 default tablespace '||DEFAULT_TABLESPACE||' temporary tablespace '||TEMPORARY_TABLESPACE||' profile REFRESH
_TEMP ;' from dba_users ;
select 'alter user '||USERNAME||'  profile REFRESH_TEMP  ;' from dba_users ;
spool off
spool pass_profile.sql
select 'alter user '||name||' identified by values '''||password||''' ;' from sys.user$ where password is not null;
select 'alter user '||USERNAME||'  profile '||profile||' ;' from dba_users ;
spool off
spool privs.sql
select 'create role '||role||' ;' from dba_roles ;
select 'grant '||PRIVILEGE||' to '||GRANTEE||''||ADMIN||'  ;' from (select grantee,PRIVILEGE,(case ADMIN_OPTION when 'YES' then ' WITH ADMIN OPTION' end ) ADMIN from db
a_sys_privs ) ;
select 'grant '||GRANTED_ROLE||' to '||GRANTEE||''||ADMIN||'  ;' from (select grantee,GRANTED_ROLE,(case ADMIN_OPTION when 'YES' then ' WITH ADMIN OPTION' end ) ADMIN f
rom dba_role_privs ) ;
select 'grant '||PRIVILEGE||' on '||OWNER||'.'||TABLE_NAME||' to '||GRANTEE||''||ADMIN||'  ;'  from (select grantee,PRIVILEGE,owner,table_name,(case GRANTABLE when 'YES
' then ' WITH GRANT OPTION' end ) ADMIN from dba_tab_privs ) where GRANTEE not in ('SYS') ;
spool off
spool synonyms.sql
select 'create or replace synonym '||owner||'.'||synonym_name||' for '||table_owner||'.'||table_name||' ;' from dba_synonyms where owner not in ('PUBLIC','SYS','SYSTEM'
,'DBSNMP','APPQOSSYS') and table_owner not in ('SYSTEM','DBAMONITOR','APPQOSSYS','SYS') and DB_LINK is null ;
select 'create or replace synonym '||owner||'.'||synonym_name||' for '||table_owner||'.'||table_name||'@'||DB_LINK||' ;' from dba_synonyms where owner not in ('PUBLIC',
'SYS','SYSTEM','DBSNMP','APPQOSSYS') and table_owner not in ('SYSTEM','DBAMONITOR','APPQOSSYS','SYS') and DB_LINK is not null ;
select 'create or replace public synonym '||synonym_name||' for '||table_owner||'.'||table_name||' ;' from dba_synonyms where owner in ('PUBLIC') and table_owner not in
 ('SYSTEM','DBAMONITOR','APPQOSSYS','SYS') and DB_LINK is null ;
select 'create or replace public synonym '||synonym_name||' for '||table_owner||'.'||table_name||'@'||DB_LINK||' ;' from dba_synonyms where owner in ('PUBLIC') and tabl
e_owner not in ('SYSTEM','DBAMONITOR','APPQOSSYS','SYS') and DB_LINK is not null ;
spool off
exit
EOF
echo " COMPLETED "

how to find DDL of privilege of user

DDL of privilege of  user


select 'grant ' || privilege ||' on ' || owner || '.' || table_name || ' to "' || grantee ||'"'|| decode(grantable,'YES',' WITH GRANT OPTION',null) || ';' "TXT" 
from dba_tab_privs where grantee in (select username from dba_users where username  in ('xxxxx'));

Lists all locked objects.

SET LINESIZE 500
SET PAGESIZE 1000
SET VERIFY OFF
COLUMN owner FORMAT A20
COLUMN username FORMAT A20
COLUMN object_owner FORMAT A20
COLUMN object_name FORMAT A30
COLUMN locked_mode FORMAT A15

SELECT lo.session_id AS sid,
       s.serial#,
       NVL(lo.oracle_username, '(oracle)') AS username,
       o.owner AS object_owner,
       o.object_name,
       Decode(lo.locked_mode, 0, 'None',
                             1, 'Null (NULL)',
                             2, 'Row-S (SS)',
                             3, 'Row-X (SX)',
                             4, 'Share (S)',
                             5, 'S/Row-X (SSX)',
                             6, 'Exclusive (X)',
                             lo.locked_mode) locked_mode,
       lo.os_user_name
FROM   v$locked_object lo
       JOIN dba_objects o ON o.object_id = lo.object_id
       JOIN v$session s ON lo.session_id = s.sid
ORDER BY 1, 2, 3, 4;

Lists all objects being accessed in the schema

SET LINESIZE 255
SET VERIFY OFF
COLUMN object FORMAT A30
SELECT a.object,
       a.type,
       a.sid,
       b.serial#,
       b.username,
       b.osuser,
       b.program
FROM   v$access a,
       v$session b
WHERE  a.sid    = b.sid
AND    a.owner  = DECODE(UPPER('&1'), 'ALL', a.object, UPPER('&1'))
AND    a.object = DECODE(UPPER('&2'), 'ALL', a.object, UPPER('&2'))
ORDER BY a.object;

11gR2 Clusterware Startup Sequence (RAC)

This description is also from the MOS note stated above

Short summary of the startup sequence: INIT spawns init.ohasd (with respawn) which in turn starts the OHASD process (Oracle High Availability Services Daemon).  This daemon spawns 4 processes.

Level 1: OHASD Spawns:

    cssdagent         - Agent responsible for spawning CSSD.
    orarootagent     - Agent responsible for managing all root owned ohasd resources.
    oraagent         - Agent responsible for managing all oracle owned ohasd resources.
    cssdmonitor        - Monitors CSSD and node health (along wth the cssdagent).
   

Level 2: OHASD rootagent spawns:
    CSDD (ora.cssd)     - Cluster Synchronization Services
    CRSD(ora.crsd)     - Primary daemon responsible for managing cluster resources.
    CTSSD(ora.ctssd)     - Cluster Time Synchronization Services Daemon
    Diskmon(ora.diskmon)
    ACFS (ASM Cluster File System) Drivers

Level 2: OHASD oraagent spawns:

    MDNSD(ora.mdnsd)     - Used for DNS lookup
    GIPCD(ora.gipcd)     - Used for inter-process and inter-node communication
    GPNPD(ora.gpnpd)     - Grid Plug & Play Profile Daemon
    EVMD(ora.evmd)     - Event Monitor Daemon
    ASM(ora.asm)     - Resource for monitoring ASM instances

Level 3: CRSD spawns:

    orarootagent     - Agent responsible for managing all root owned crsd resources.
    oraagent         - Agent responsible for managing all oracle owned crsd resources.

Level 4: CRSD rootagent spawns:

    Network resource     - To monitor the public network
    SCAN VIP(s)     - Single Client Access Name Virtual IPs
    Node VIPs         - One per node
    ACFS Registery     - For mounting ASM Cluster File System
    GNS VIP (optional)     - VIP for GNS

Level 4: CRSD oraagent spawns:

    ASM Resouce     - ASM Instance(s) resource
    Diskgroup         - Used for managing/monitoring ASM diskgroups.
    DB Resource     - Used for monitoring and managing the DB and instances
    SCAN Listener     - Listener for single client access name, listening on SCAN VIP
    Listener         - Node listener listening on the Node VIP
    Services         - Used for monitoring and managing services
    ONS         - Oracle Notification Service
    eONS         - Enhanced Oracle Notification Service
    GSD         - For 9i backward compatibility
    GNS (optional)     - Grid Naming Service - Performs name resolution

How to create BITMAP index in Oracle 11G

How to create BITMAP index in Oracle 11G

Example - Create bitmap index on the historyABC column of the PO table

SYNTEX -  

CREATE BITMAP INDEX 
emp_bitmap_idx
ON index_demo (gender);


SQL>CREATE BITMAP INDEX  PO_HISTORYFLAG_IDX  ON MXPRE.PO(historyABC);

SQL>execdbms_stats.gather_index_stats(OWNNAME=>'SYS',INDNAME=>'PO_HISTORYFLAG_IDX'); 

How to Check the Locked Object details

Lock related queries :

SELECT XIDUSN,OBJECT_ID,SESSION_ID,ORACLE_USERNAME,OS_USER_NAME,PROCESS from gv$locked_object;

SELECT d.OBJECT_ID, substr(OBJECT_NAME,1,20), l.SESSION_ID, l.ORACLE_USERNAME, l.LOCKED_MODE

from   v$locked_object l, dba_objects d

where  d.OBJECT_ID=l.OBJECT_ID;

SELECT ADDR, KADDR, SID, TYPE, ID1, ID2, LMODE, BLOCK from gv$lock;

SELECT a.sid, a.saddr, b.ses_addr, a.username, b.xidusn, b.used_urec, b.used_ublk

FROM   v$session a, gv$transaction b

WHERE  a.saddr = b.ses_addr;

SELECT s.sid, l.lmode, l.block, substr(s.username, 1, 10), substr(s.schemaname, 1, 10), 

       substr(s.osuser, 1, 10), substr(s.program, 1, 30), s.command

FROM   v$session s, v$lock l

WHERE  s.sid=l.sid;

SELECT  p.spid, s.sid, p.addr,s.paddr,substr(s.username, 1, 10), substr(s.schemaname, 1, 10), 

        s.command,substr(s.osuser, 1, 10), substr(s.machine, 1, 10) 

FROM    v$session s, v$process p

WHERE   s.paddr=p.addr

SELECT sid, serial#, command,substr(username, 1, 10), osuser, sql_address,LOCKWAIT, 

       to_char(logon_time, 'DD-MM-YYYY;HH24:MI'), substr(program, 1, 30)

FROM   v$session;

SELECT sid, serial#,  username, LOCKWAIT from v$session;

SELECT v.SID, v.BLOCK_GETS, v.BLOCK_CHANGES, w.USERNAME, w.OSUSER, w.TERMINAL

FROM   v$sess_io v, V$session w

WHERE  v.SID=w.SID ORDER BY v.SID;

SELECT * from dba_waiters;

SELECT waiting_session, holding_session, lock_type, mode_held

FROM   dba_waiters;

SELECT 

  p.spid                      unix_spid,

  s.sid                       sid, 

  p.addr,

  s.paddr,

  substr(s.username, 1, 10)   username, 

  substr(s.schemaname, 1, 10) schemaname, 

  s.command                   command,

  substr(s.osuser, 1, 10)     osuser, 

  substr(s.machine, 1, 25)    machine

FROM   v$session s, v$process p

WHERE  s.paddr=p.addr

ORDER BY p.spid;

ref -http://www.dbatodba.com/oracle/how-to-check-the-locked-object-details/