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
