Oracle Sql scripts

User , Role Privileges

select * from dba_tab_privs;
select * from dba_role_privs WHERE granted_ROLE= 'SHARED_USER';
select * from dba_sys_privs;
 
select * from all_tab_privs;
select * from all_role_privs;
select * from all_sys_privs;
 
select * from user_tab_privs;
select * from user_role_privs;
select * from user_sys_privs;
 
select * from role_tab_privs;
select * from role_role_privs;
select * from role_sys_privs;
 
 
select * from role_tab_privs where role = 'SHARED_USER';
select * from role_role_privs  where role = 'SHARED_USER';
select * from role_sys_privs  where role =  'SHARED_USER'
 
select count(*) from dba_users;
select * from user_role_privs;
select * from dba_roles where role = 'ADMIN'

Stats Details

-To verify stats
 
select * from user_tab_statistics;
select * from user_tab_col_statistics;

Force Index to use

To use a full scan or a range scan.
http://www.dba-oracle.com/t_v_sql_hint.htm
 
 
 
 
SELECT /*+ FULL(m)         */ b FROM mytable m WHERE b=10; -- full table scan
SELECT /*+ INDEX_RS_ASC(m) */ b FROM mytable m WHERE b=10; -- index range scan
SELECT /*+ INDEX_FFS(m)    */ b FROM mytable m WHERE b=10; -- index fast full scan

Get the Explain plan for sql query

select sql_id, child_number , s.*  
from gv$sql s 
WHERE upper(SQL_FULLTEXT) like '%TABLE_NAME%' 
order by last_active_time desc;

SELECT * 
FROM TABLE(dbms_xplan.display_cursor('frmxr4w3tb9wv', 0));

Check time consuming SQL queries in Oracle

SELECT * FROM
(SELECT
    sql_fulltext,
    sql_id,
    child_number,
    disk_reads,
    executions,
    first_load_time,
    last_load_time
FROM    v$sql
ORDER BY elapsed_time DESC)
WHERE ROWNUM < 20
 
 
 
SELECT Disk_Reads DiskReads, Executions, SQL_ID, SQL_Text SQLText,
   SQL_FullText SQLFullText
FROM
(
   SELECT Disk_Reads, Executions, SQL_ID, LTRIM(SQL_Text) SQL_Text,
      SQL_FullText, Operation, Options,
      Row_Number() OVER
         (Partition By sql_text ORDER BY Disk_Reads * Executions DESC)
         KeepHighSQL
   FROM
   (
       SELECT Avg(Disk_Reads) OVER (Partition By sql_text) Disk_Reads,
          Max(Executions) OVER (Partition By sql_text) Executions,
          t.SQL_ID, sql_text, sql_fulltext, p.operation,p.options
       FROM v$sql t, v$sql_plan p
       WHERE t.hash_value=p.hash_value AND p.operation='TABLE ACCESS'
       AND p.options='FULL' AND p.object_owner NOT IN ('SYS','SYSTEM')
       AND t.Executions > 1
   )
   ORDER BY DISK_READS * EXECUTIONS DESC
)
WHERE KeepHighSQL = 1
AND rownum <=5;
 
 
SELECT username,
       buffer_gets,
       disk_reads,
       executions,
       buffer_get_per_exec,
       parse_calls,
       sorts,
       rows_processed,
       hit_ratio,
       module,
       sql_text
       -- elapsed_time, cpu_time, user_io_wait_time, ,
  FROM (SELECT sql_text,
               b.username,
               a.disk_reads,
               a.buffer_gets,
               trunc(a.buffer_gets / a.executions) buffer_get_per_exec,
               a.parse_calls,
               a.sorts,
               a.executions,
               a.rows_processed,
               100 - ROUND (100 * a.disk_reads / a.buffer_gets, 2) hit_ratio,
               module
               -- cpu_time, elapsed_time, user_io_wait_time
          FROM v$sqlarea a, dba_users b
         WHERE a.parsing_user_id = b.user_id
           AND b.username NOT IN ('SYS', 'SYSTEM', 'RMAN','SYSMAN')
           AND a.buffer_gets > 10000
         ORDER BY buffer_get_per_exec DESC)
 WHERE ROWNUM <= 20

Dynamic Oracle Views

SELECT * FROM V$FIXED_VIEW_DEFINITION;
SELECT * FROM V$FIXED_TABLE;
SELECT NAME, TYPE FROM V$FIXED_TABLE WHERE NAME LIKE 'V$%';

LOCKED DATABASE OBJECTS

SELECT l.session_id || ',' || v.serial# sid_serial,      
       l.ORACLE_USERNAME ora_user,      
       o.object_name,      
       o.object_type,      
       DECODE(l.locked_mode,             
              0,
              'None',             
              1,
              'Null',             
              2,
              'Row-S (SS)',             
              3,
              'Row-X (SX)',             
              4,
              'Share',             
              5,
              'S/Row-X (SSX)',             
              6,
              'Exclusive',             
              TO_CHAR(l.locked_mode)             
              ) lock_mode,      
       o.status,      
       to_char(o.last_ddl_time, 'dd.mm.yy') last_ddl
  FROM dba_objects o, gv$locked_object l, v$session v
 WHERE o.object_id = l.object_id     
   and l.SESSION_ID = v.sid;


select a.sid, a.serial#, OBJECT_NAME , a.*
  from v$session a, v$locked_object b, dba_objects c
 where b.object_id = c.object_id
   and a.sid = b.session_id;
 
 
ALTER SYSTEM KILL SESSION 'sid,serial#';
 
 
--Dynamic spool name
column filename new_val filename
select 'alarm_query_'||  sys_context('userenv','db_name') || '_' || to_char(sysdate, 'yyyymmdd' ) filename from dual;
spool '&filename'
  
set heading OFF;
--set serveroutput on;
select 'User : ' || user from dual;
select 'Database : ' || ora_database_name from dual;

SQL