Querying table space usage

Reprinted from Http://www.cnblogs.com/askjacklin/archive/2012/06/04/2534571.html

--Querying table space usage
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;
--Query The free space of the tablespace
Select Tablespace_name,
count (*) as EXTENDS,
round (sum (bytes)/1024/ 1024x768, 2) as MB,
sum (blocks) as BLOCKS
from DBA_FREE_SPACE
GROUP by tablespace_name;

--the total capacity of the query tablespace
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, L O.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, ID 2, type from Gv$lock WHERE request>0) ORDER by ID1, request;

4. Monitor 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 ord Er by address, piece;

5. Find a user with more CPU session 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 ',     & nbsp;  (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. Objects 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.CU rrent_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  & nbsp WHERE a.sample_time between sysdate-30/2880 and sysdate      a.current_obj# = o.object_id &nbs p;    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 number of currently 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_wa It_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-3 0/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 most consumed SQL 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 = 22 8801498 and address = Hextoraw (' cbd8e4b0 ');

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

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

Querying table space usage