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;
No comments:
Post a Comment