--Query Tablespace usage SELECT UPPER (f.tablespace_name) "tablespace name", D.TOT_GROOTTE_MB "tablespace size (M)", d.tot_grootte_mb-f.total_bytes "empty Room (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 "maximum block (m)" from (select Tablespace_name, ROUND (SUM (BYTES)/(1024 * 1024 ), 2) total_bytes, ROUND (MAX (BYTES)/(1024x768 * 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 tablespace for free 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 capacity of the tablespace 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, 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_na Me) 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 Lock-out table process: Alter system kill session ' 436,35123 '; 3.RAC environment Lock Lookup: SELECT inst_id,decode (request,0, ' Holder: ', ' Waiter: ') | | Sid Sess, Id1, ID2, Lmode, request, Type,block,ctimefrom Gv$lockwhere (ID1, ID2, type) in (SELECT id1, Id2, type from gv$l Ock 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 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 SE Lect (select username from v$session WHERE SID = a.sid) blocker, a.sid, ' was blocking ', (select username from v$session WHE RE 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. 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.curr ent_obj# = O.object_idgroup by O.owner,o.object_name, O.object_type, a.eventorder 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$sess Ion S WHERE a.sample_time between sysdate-30/2880 and sysdate and a.current_obj# = o.object_id and a.session_id = S.SI DGROUP by O.owner, O.object_name, O.object_type, A.event, a.session_id, S.program, S.machine, S.osuserorder by total_wait _time DESC; 8. Query the number of currently connected sessions Select S.value,s.sid,a.usernamefrom v$sesstat s,v$statname n,v$session awhere n.statistic#=s.statistic# Andname= ' session PGA Memory ' and S.sid=a.sidorder 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$s ession s WHERE a.sample_time between sysdate-30/2880 and Sysdategroup by S.sid, S.usernameorder by Total_wait_time DES C 10. Waiting for the most sqlselect 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_idgroup by A.program, A.sessio n_id, a.user_id, S.sql_text, D.username; 11. View the most consumed sqlselect hash_value, executions, buffer_gets, Disk_reads, Parse_callsfrom v$sqlareawhere buffer_gets > 10000000 OR disk_reads > 1000000ORDER by buffer_gets + + * Disk_reads DESC; 12. View resource consumption for an SQL statement select Hash_value, buffer_gets, disk_reads, executions, parse_callsfrom v$sqlareawhere hash_value = 228801498 and address = Hextoraw (' cbd8e4b0 '); 13. The actual sqlselect of the query session execution A.sid, A.username, S.sql_text from V$session A, v$sqlt ext s WHERE a.sql_address = s.address and A.sql_hash_value = s.hash_value and a.status = ' ACTIVE ' ORDER by A.username, a.si D, s.piece;14. Show all sessions waiting for a lock select * from Dba_waiters;
Oracle Common SQL
