Frequently Used Data Dictionary Queries

1.Index Table :

Select INDEX_NAME ,INDEX_TYPE,TABLE_OWNER,TABLE_NAME,NUM_ROWS,

LAST_ANALYZED,PARTITIONED From All_Indexes;

2. Index Columns :

select INDEX_NAME,TABLE_OWNER,TABLE_NAME,COLUMN_NAME,

COLUMN_POSITION, COLUMN_LENGTH,CHAR_LENGTH from ALL_IND_COLUMNS;

3. Table Column Level Stats :

Select TABLE_NAME,COLUMN_NAME,NUM_DISTINCT,LAST_ANALYZED,AVG_COL_LEN from All_Tab_Col_Statistics;

4. Constraints on a table:

select CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME,SEARCH_CONDITION,

LAST_CHANGE,INDEX_OWNER,INDEX_NAME from All_Constraints;

5. Text of a View:

select view_name,text,owner from All_Views;

6. Text of a Trigger :

select   trigger_name,trigger_type,triggering_event,table_owner,table_name,

description,trigger_body from all_triggers;

7. Locks on Table:

select c.owner,c.object_name,c.object_type,c.object_id,b.sid,b.serial#,

b.status,b.osuser,b.machine,a.LOCKED_MODE from v$locked_object

a ,v$session b,dba_objects c where b.sid = a.session_id and  a.object_id =

c.object_id;

Locking Modes Description:

0- none

1 - null (NULL)

2 - row-S (SS)

3 - row-X (SX)

4 - share (S)

5 - S/Row-X (SSX)

6 - exclusive (X)

8. Query to Kill a Session :

alter system kill session(sid,serial no)

9.Find the Actual size of a Database

SELECT SUM (bytes) / 1024 / 1024 / 1024 AS GB FROM dba_data_files;

10.Find the size occupied by Data in a Database or Database usage details

SELECT SUM (bytes) / 1024 / 1024 / 1024 AS GB FROM dba_segments;

11. Last SQL fired by the User on Database

SELECT S.USERNAME || '(' || s.sid || ')-' || s.osuser UNAME, s.program || '-' ||

s.terminal || '(' || s.machine || ')' PROG,s.sid || '/' || s.serial# sid, s.status

"Status",p.spid,sql_text sqltext FROM v$sqltext_with_newlines t,

V$SESSION s, v$process p   WHERE t.address = s.sql_address   AND

p.addr = s.paddr(+) AND t.hash_value = s.sql_hash_value  ORDER BY s.sid,

t.piece;

12. CPU usage of the USER

SELECT ss.username, se.SID, VALUE / 100 cpu_usage_seconds FROM

v$session ss, v$sesstat se, v$statname sn WHERE  se.STATISTIC# =

sn.STATISTIC#  AND NAME LIKE '%CPU used by this session%' AND se.SID

= ss.SID AND ss.status = 'ACTIVE' AND ss.username IS NOT NULL ORDER

BY VALUE DESC;

13. Long Query progress in database

SELECT a.sid,a.serial#,b.username,opname OPERATION,target

OBJECT,TRUNC (elapsed_seconds, 5) "ET (s)",TO_CHAR (start_time,

'HH24:MI:SS') start_time,ROUND ( (sofar / totalwork) * 100, 2) "COMPLETE

(%)" FROM v$session_longops a, v$session b  WHERE     a.sid = b.sid  AND

b.username NOT IN ('SYS', 'SYSTEM') AND totalwork > 0 ORDER BY

elapsed_seconds;

14.Last DDL SQL Fired from particular Schema or Table:

SELECT CREATED, TIMESTAMP, last_ddl_time  FROM all_objects  WHERE     OWNER = 'MYSCHEMA'  AND OBJECT_TYPE = 'TABLE' AND OBJECT_NAME = 'EMPLOYEE_TABLE';

15.Find Top 10 SQL by reads per execution

SELECT * FROM (  SELECT ROWNUM,SUBSTR (a.sql_text, 1, 200)

sql_text,TRUNC (a.disk_reads / DECODE (a.executions, 0, 1, a.executions))

reads_per_execution,a.buffer_gets,a.disk_reads,a.executions,a.sorts,a.address

FROM v$sqlarea a ORDER BY 3 DESC) WHERE ROWNUM < 10;

16.Oracle SQL query that show the opened connections group by the program that opens the connection.

SELECT program application, COUNT (program) Numero_Sesiones FROM

v$session GROUP BY program ORDER BY Numero_Sesiones DESC;

17.Get number of objects per owner

SELECT owner, COUNT (owner) number_of_objects FROM dba_objects

Group By Owner ORDER BY number_of_objects DESC;

18.Oracle SQL query that shows Oracle users connected and the sessions number for user

SELECT username Usuario_Oracle, COUNT (username) Numero_Sesiones

FROM v$session Group By Username ORDER BY Numero_Sesiones DESC;

19. DML Operation Audit

select username ,obj_name ,to_char(timestamp,'dd-mon-yy hh24:mi')

event_time  ,substr(ses_actions,4,1) del  ,substr(ses_actions,7,1)

ins ,substr(ses_actions,10,1) sel  ,substr(ses_actions,11,1) upd  from dba_audit_object;

20.SQL Statements with Maximum Wait

select ash.user_id, u.username, s.sql_text, sum(ash.wait_time +

ash.time_waited) ttl_wait_time from v$active_session_history ash,

v$sqlarea s, dba_users u where ash.sample_time between sysdate -

60/2880 and sysdate and ash.sql_id = s.sql_id and ash.user_id = u.user_id

group by ash.user_id,s.sql_text, u.username order by ttl_wait_time ;

21. SQL Text:

select sid, sql_text from v$session s, v$sql q where sid in (* ,*) and

(q.sql_id = s.sql_id or q.sql_id = s.prev_sql_id);

Note:

Provide the Sid for which you wish to see the SQL Text else list will be exhaustive.

22.query to find out which session is currently using the most undo

select s.sid, t.name, s.value from v$sesstat s, v$statname t where

s.statistic# = t.statistic# and t.name = 'undo change vector size' order by

s.value desc;

23. Monitoring Temporary Tablespace Usage

select * from (select a.tablespace_name,sum(a.bytes/1024/1024)

allocated_mb  from dba_temp_files a where a.tablespace_name = upper

('&&temp_tsname') group by a.tablespace_name) x, (select sum

(b.bytes_used/1024/1024) used_mb, sum(b.bytes_free/1024/1024)

free_mb  from v$temp_space_header b where b.tablespace_name=upper

('&&temp_tsname') group by b.tablespace_name);

24. query to find out which sessions are using space in the temporary tablespace.

select s.sid || ',' || s.serial# sid_serial, s.username, s.osuser,

p.spid,s.module,s.program,sum (o.blocks) * t.block_size / 1024 / 1024

mb_used, o.tablespace,count(*) sorts from v$sort_usage o, v$session s,

dba_tablespaces t, v$process p where o.session_addr = s.saddr and

s.paddr = p.addr and o.tablespace = t.tablespace_name group by s.sid,

s.serial#, s.username, s.osuser, p.spid, s.module, s.program, t.block_size,

o.tablespace order by sid_serial;