Summary of Common Oracle queries

--Query the usage of table space
Select UPPER (f.tablespace_name) "Table space name",
D.TOT_GROOTTE_MB "Table space size (M)",
d.tot_grootte_mb-f.total_bytes "used Space (M)",
To_char (ROUND ((d.tot_grootte_mb-f.total_bytes)/d.tot_grootte_mb * 100,2), ' 990.99 ') | | '% ' "use ratio",
f.total_bytes "free Space (M)",
f.max_bytes "Max Block (M)"
From (SELECT tablespace_name,
ROUND (SUM (BYTES)/(1024x768 * 1024x768), 2) total_bytes,
ROUND (MAX (BYTES)/(1024x768), 2) max_bytes
From SYS. Dba_free_space
GROUP by Tablespace_name) F,
(SELECT DD.) Tablespace_name,
ROUND (SUM (DD). BYTES)/(1024x768 * 1024x768), 2) TOT_GROOTTE_MB
From SYS. Dba_data_files DD
GROUP by DD. Tablespace_name) D
WHERE d.tablespace_name = F.tablespace_name
ORDER by 1;
--free space for querying table space
Select Tablespace_name,
Count (*) as extends,
round (sum (bytes)/1024/1024, 2) as MB,
sum (blocks) as blocks
From dba_free_space
GROUP by Tablespace_name;
--The total capacity of the query table space　　
Select Tablespace_name, sum (bytes)/1024/1024 as MB
From dba_data_files
GROUP by Tablespace_name;
--Query table space utilization　　
Select Total.tablespace_name,
round (total. MB, 2) as TOTAL_MB,The exam Big forum
round (total. Mb-free. MB, 2) as USED_MB,
round (1-free. Mb/total. MB) * 100, 2) | | '% ' as used_pct
From (select Tablespace_name, sum (bytes)/1024/1024 as MB
From dba_free_space
GROUP by Tablespace_name) free,
(select Tablespace_name, sum (bytes)/1024/1024 as MB
From dba_data_files
GROUP by Tablespace_name) Total
where free.tablespace_name = Total.tablespace_name;

1. Find the SQL for the current table-level lock as follows:
Select Sess.sid,
sess.serial#,
Lo.oracle_username,
Lo.os_user_name,
Ao.object_name,
Lo.locked_mode
From V$locked_object Lo,
Dba_objects AO,
V$session Sess
where ao.object_id = lo.object_id and lo.session_id = Sess.sid;

2. Kill the Lock table process:
Alter system kill session ' 436,35123 ';

Lock Lookup in 3.RAC environment:
SELECT Inst_id,decode (request,0, ' Holder: ', ' Waiter: ') | | Sid Sess,
ID1, ID2, Lmode, request, Type,block,ctime
From Gv$lock
WHERE (ID1, ID2, type) in
(SELECT id1, Id2, type from Gv$lock WHERE request>0)
ORDER by ID1, request;

4. Monitor the current database who is running what SQL statement
Select Osuser, username, Sql_text
From V$session A, V$sqltext b
where a.sql_address =b.address order by address, piece;

5. Find a user session with more CPU
Select A.sid,spid,status,substr (a.program,1,40) prog, a.terminal,osuser,value/60/100 value
From V$session a,v$process B,v$sesstat C
where C.statistic#=12 and
C.sid=a.sid and
A.paddr=b.addr
Order BY value Desc;

6. Viewing deadlock information
Select (select Username
          from v$ Session
         where SID = a.sid) blocker, a.sid, ' is blocking ',
       (SELECT username
          From V$session
         WHERE SID = b.sid) blockee, B.sid
  from v$ Lock A, V$lock b
 where a.block = 1 and b.request > 0 and a.id1 = b.id1 and A.id2 = B.id2;

7. The object with the highest waiting
SELECT O.owner,o.object_name, O.object_type, A.event,
SUM (A.wait_time + a.time_waited) total_wait_time
From V$active_session_history A, dba_objects o
WHERE a.sample_time between sysdate-30/2880 and Sysdate
and a.current_obj# = o.object_id
GROUP by O.owner,o.object_name, O.object_type, a.event
ORDER by Total_wait_time DESC;

SELECT a.session_id, S.osuser, S.machine, S.program, O.owner, O.object_name,
O.object_type, A.event,
SUM (A.wait_time + a.time_waited) total_wait_time
From V$active_session_history A, dba_objects O, v$session s
WHERE a.sample_time between sysdate-30/2880 and Sysdate
and a.current_obj# = o.object_id
and a.session_id = S.sid
GROUP by O.owner,
O.object_name,
O.object_type,
A.event,
A.SESSION_ID,
S.program,
S.machine,
S.osuser
ORDER by Total_wait_time DESC;

8. Query the current number of connected sessions
Select S.value,s.sid,a.username
From
V$sesstat s,v$statname n,v$session A
where
N.statistic#=s.statistic# and
Name= ' Session PGA Memory '
and S.sid=a.sid
Order BY S.value;

9. Waiting for the most users
SELECT S.sid, S.username, SUM (A.wait_time + a.time_waited) total_wait_time
From V$active_session_history A, v$session s
WHERE a.sample_time between sysdate-30/2880 and Sysdate
GROUP by S.sid, S.username
ORDER by Total_wait_time DESC;

10. Waiting for the most SQL
SELECT A.program, a.session_id, a.user_id, D.username, S.sql_text,
SUM (A.wait_time + a.time_waited) total_wait_time
From V$active_session_history A, V$sqlarea s, dba_users D
WHERE a.sample_time between sysdate-30/2880 and Sysdate
and a.sql_id = s.sql_id
and a.user_id = d.user_id
GROUP by A.program, a.session_id, a.user_id, S.sql_text, D.username;

11. View the SQL that consumes the most resources
SELECT hash_value, executions, buffer_gets, Disk_reads, Parse_calls
From V$sqlarea
WHERE buffer_gets > 10000000 OR disk_reads > 1000000
ORDER by Buffer_gets + * Disk_reads DESC;

12. View resource consumption for an SQL statement
SELECT Hash_value, Buffer_gets, disk_reads, executions, parse_calls
From V$sqlarea
WHERE Hash_value = 228801498 and address = Hextoraw (' cbd8e4b0 ');

13. Query the actual SQL that the session executes
SELECT A.sid, A.username, S.sql_text
From V$session A, v$sqltext s
WHERE a.sql_address = s.address
and A.sql_hash_value = S.hash_value
and a.status = ' ACTIVE '
ORDER by A.username, A.sid, s.piece;

14. Show all sessions that are waiting for a lock
SELECT * from Dba_waiters;