Difference between revisions of "Handy scripts"

Revision as of 16:45, 3 November 2012

SQL

• Database Overview - from idevelopment online version
• Connecting as another user via proxy in SQL*Plus
• Password cracker and role and priv tools from Pete Finnigan

DBA privs tables

DBA_AQ_AGENT_PRIVS
DBA_COL_PRIVS
DBA_PRIV_AUDIT_OPTS
DBA_REPGROUP_PRIVILEGES
DBA_ROLE_PRIVS
DBA_RSRC_CONSUMER_GROUP_PRIVS
DBA_RSRC_MANAGER_SYSTEM_PRIVS
DBA_SYS_PRIVS
DBA_TAB_PRIVS
DBA_WM_SYS_PRIVS
DBA_WORKSPACE_PRIVS

Kill an Oracle session

SELECT s.inst_id,
       s.sid,
       s.serial#,
       p.spid,
       s.username,
       s.program
FROM   gv$session s
,      gv$process p
WHERE  1=1
and    p.addr    = s.paddr
AND    p.inst_id = s.inst_id
and    s.type   != 'BACKGROUND'
AND    s.sid     = '235'

SYS@PIBPROD> /

  INST_ID        SID    SERIAL# SPID       USERNAME   PROGRAM
---------- ---------- ---------- ---------- ---------- ---------------------------------------------
        1        235       5609 4718756    OPS$PIBCTM sqlplus@pibprod (TNS V1-V3)

SYS@PIBPROD> alter system kill session '235,5609';

badprivs.sql

select grantee, privilege, admin_option
from   sys.dba_sys_privs
where  (privilege like '% ANY %'
or     privilege in ('BECOME USER', 'UNLIMITED TABLESPACE')
or     admin_option = 'YES')
and    grantee not in ('SYS', 'SYSTEM', 'OUTLN', 'AQ_ADMINISTRATOR_ROLE',
                      'DBA', 'EXP_FULL_DATABASE', 'IMP_FULL_DATABASE',
                      'OEM_MONITOR', 'CTXSYS', 'DBSNMP', 'IFSSYS',
                      'IFSSYS$CM', 'MDSYS', 'ORDPLUGINS', 'ORDSYS',
                      'TIMESERIES_DBA')

This query will list the system privileges assigned to a user

SELECT LPAD(' ', 2*level) || granted_role "USER PRIVS"
FROM (
  SELECT NULL grantee,  username granted_role
  FROM dba_users
  WHERE username LIKE UPPER('%&uname%')
  UNION
  SELECT grantee, granted_role
  FROM dba_role_privs
  UNION
  SELECT grantee, privilege
  FROM dba_sys_privs)
START WITH grantee IS NULL
CONNECT BY grantee = prior granted_role;

or

SELECT path
FROM (
  SELECT grantee,
         sys_connect_by_path(privilege, ':')||':'||grantee path
  FROM (
    SELECT grantee, privilege, 0 role
    FROM dba_sys_privs
    UNION ALL
    SELECT grantee, granted_role, 1 role
    FROM dba_role_privs)
  CONNECT BY privilege=prior grantee
  START WITH role = 0)
WHERE grantee IN (
   SELECT username
   FROM dba_users
   WHERE lock_date IS NULL
   AND password != 'EXTERNAL'
   AND username != 'SYS')
OR grantee='PUBLIC'

A database overview using the sys.plato package

exec plato.help;

or

exec plato.complete('HTM');

Info on blocking processes

select s1.username || '@' || s1.machine
 || ' ( SID=' || s1.sid || ' )  is blocking '
 || s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status
 from v$lock l1, v$session s1, v$lock l2, v$session s2
 where s1.sid=l1.sid and s2.sid=l2.sid
 and l1.BLOCK=1 and l2.request > 0
 and l1.id1 = l2.id1
 and l2.id2 = l2.id2 ;

-- session doing the blocking
 select *
 from  v$lock l1
 where 1=1
 and   block = 1 ;

-- sessions being blocked
select *
from   v$lock l1
where  1=1
and    id2 = 85203

-- info on session doing the blocking
select row_wait_obj#, row_wait_file#, row_wait_block#, row_wait_row#
from   v$session
where  sid = 234

select do.object_name,
row_wait_obj#, row_wait_file#, row_wait_block#, row_wait_row#,
dbms_rowid.rowid_create ( 1, ROW_WAIT_OBJ#, ROW_WAIT_FILE#, ROW_WAIT_BLOCK#, ROW_WAIT_ROW# ) row_id
from v$session s, dba_objects do
where sid=234
and s.ROW_WAIT_OBJ# = do.OBJECT_ID ;

select *
from   CLIENTCACHESTATE
where  rowid = 'AAAH+JAAWAABAHuAAA'

shell

Alert Log scraping with ADRCI

cmd

• Database Export - from DBA Oracle (Burleson)