Oracle queries table space usage

Oracle query table space usage -- Query table space usage SELECTUPPER (F. TABLESPACE_NAME) tablespace name, D. TOT_GROOTTE_MB tablespace size (M), D. TOT_GROOTTE_MB

Oracle queries table space usage-queries table space usage select upper (F. TABLESPACE_NAME) tablespace name, D. TOT_GROOTTE_MB tablespace size (M), D. TOT_GROOTTE_MB-F. TOTAL_BYTES space used (M), TO_CHAR (ROUND (D. TOT_GROOTTE_MB-F. TOTAL_BYTES)/D. TOT_GROOTTE_MB * 1

Oracle queries table space usage
-- Query table space usage
Select upper (F. TABLESPACE_NAME) "tablespace name ",
D. TOT_GROOTTE_MB "tablespace 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 * 990), '2014. 99') | '%' "usage ratio ",
F. TOTAL_BYTES "Idle space (M )",
F. MAX_BYTES "maximum block (M )"
FROM (SELECT TABLESPACE_NAME,
ROUND (SUM (BYTES)/(1024*1024), 2) TOTAL_BYTES,
ROUND (MAX (BYTES)/(1024*1024), 2) MAX_BYTES
From sys. DBA_FREE_SPACE
Group by TABLESPACE_NAME) F,
(Select dd. TABLESPACE_NAME,
ROUND (SUM (DD. BYTES)/(1024*1024), 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 a 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;


-- Query the total table space capacity
Select tablespace_name, sum (bytes)/1024/1024 as MB
From dba_data_files
Group by tablespace_name;


-- Query table space usage
Select total. tablespace_name,
Round (total. MB, 2) as Total_MB, examination 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. the SQL statement for searching the current table lock is 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 '12345623 ';


3. Search for locks in the 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 who is running the SQL statement of the current database
Select osuser, username, SQL _text
From v $ session a, v $ sqltext B
Where a. SQL _address = B. address order by address, piece;





5. Find user sessions with more CPU resources
Select a. sid, spid, status, substr (a. program, 60/100) prog, a. terminal, osuser, value/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. view the 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. objects with the highest waiting Value
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
Where
N. statistic # = s. statistic # and
Name = 'session pga memory'
And s. sid = a. sid
Order by s. value;





9. users with the most waiting
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. The most waiting SQL statement
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 statements that consume the most resources
SELECT hash_value, executions, buffer_gets, disk_reads, parse_cils
From v $ SQLAREA
WHERE buffer_gets> 10000000 OR disk_reads> 1000000
Order by buffer_gets + 100 * disk_reads DESC;





12. view the resource consumption of an SQL statement
SELECT hash_value, buffer_gets, disk_reads, executions, parse_cils
From v $ SQLAREA
WHERE hash_Value = 228801498 AND address = hextoraw ('cbd8e4b0 ');




13. query the actual SQL statement executed by the session
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. display all sessions waiting for Lock
SELECT * FROM DBA_WAITERS;