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;
