DAILY NEEDED SCRIPTS FOR DBA

---------------------- विवेक गुप्ता ------------------------------ To Check Locks select s1.username || '@' || s1.machine || ' ( SID=' || s1.sid || ' ) is blocking ' || s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status from v$lock l1, v$session s1, v$lock l2, v$session s2 where s1.sid=l1.sid and s2.sid=l2.sid and l1.BLOCK=1 and l2.request > 0 and l1.id1 = l2.id1 and l2.id2 = l2.id2 ; ----------------------------------- Object Lock SELECT c.owner, c.object_name, c.object_type, b.sid, b.serial#, b.status, b.osuser, b.machine FROM v$locked_object a , v$session b, dba_objects c WHERE b.sid = a.session_id AND a.object_id = c.object_id; --------------------------------------- REM: Script to find Database locks REM: REM:***************************************** REM: Author: Babu REM: Date Submitted: 27-Dec-2008 REM: REM: REM: NOTE: PLEASE TEST THIS SCRIPT BEFORE USE. REM: Author will not be responsible for any damage that may be cause by this script. REM:***************************************** column blocker format a25 column blockee format a25 column sid_serial format a10 select (select username || ' - ' || osuser from v$session where sid=a.sid) blocker, a.sid || ', ' || (select serial# from v$session where sid=a.sid) sid_serial, ' is blocking ', (select username || ' - ' || osuser from v$session where sid=b.sid) blockee, b.sid || ', ' || (select serial# from v$session where sid=b.sid) sid_serial 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; ---------------------------------------------------------- ------------------------ col username form A10 col sid form 9990 col type form A4k col lmode form 990 col request form 990 col objname form A15 Heading "Object Name" SELECT sn.username, m.sid, m.type, DECODE(m.lmode, 0, 'None' , 1, 'Null' , 2, 'Row Share' , 3, 'Row Excl.' , 4, 'Share' , 5, 'S/Row Excl.' , 6, 'Exclusive' , lmode, ltrim(to_char(lmode,'990'))) lmode, DECODE(m.request, 0, 'None' , 1, 'Null' , 2, 'Row Share' , 3, 'Row Excl.' , 4, 'Share' , 5, 'S/Row Excl.' , 6, 'Exclusive' , request, ltrim(to_char(request,'990'))) request, obj1.object_name objname, obj2.object_name objname FROM v$session sn, V$lock m, dba_objects obj1, dba_objects obj2 WHERE sn.sid = m.sid AND m.id1 = obj1.object_id (+) AND m.id2 = obj2.object_id (+) AND lmode != 4 ORDER BY id1,id2, m.request -------------------------------------- --SPID ---sid---- SELECT s.sid, p.spid, s.osuser, s.program FROM v$process p, v$session s WHERE p.addr = s.paddr and s.sid=496; ; To check SQL text for particular sid select s.sid, q.sql_text from v$sqltext q, v$session s where q.address = and s.sid = 205 order by piece select do.object_name, row_wait_obj#, row_wait_file#, row_wait_block#, row_wait_row#, dbms_rowid.rowid_create ( 1, ROW_WAIT_OBJ#, ROW_WAIT_FILE#, ROW_WAIT_BLOCK#, ROW_WAIT_ROW# ) from v$session s, dba_objects do where sid=479 and s.ROW_WAIT_OBJ# = do.OBJECT_ID SELECT s.sid, s.serial#, st.sql_text sql_text FROM v$session s, v$sql st, v$process p WHERE s.sql_hash_value = st.hash_value AND s.sql_address = st.address AND s.paddr = p.addr and ( s.sid=392) Full text select x.sql_text from v$session s,v$sqltext x where s.sql_address=x.address and s.sid =205 order by sid, piece asc ---------------------------------- to get sid base on unix process id : SELECT s.sid,s.serial# from v$process p, v$session s where s.paddr=p.addr and p.spid = '&spid'; to get unix process id from sid : SELECT s.sid,s.serial#, p.spid from v$process p, v$session s where s.paddr=p.addr and s.sid=&1 GRANT PERMISSION ON WHOLE SCHEMA set verify off set feedback off set pagesize 10000 set linesize 120ACCEPT main_owner CHAR PROMPT 'MAIN OWNER: ' ACCEPT grant_to CHAR PROMPT 'GRANT TO: ' ACCEPT permissions CHAR PROMPT 'PERMISSIONS (i.e. SELECT,UPDATE): ' spool grant_from_&&main_owner._to_&&grant_to..sql select 'grant &&permissions on "&&main_owner"."'||table_name||'" to "&&grant_to";' "---" from dba_tables where owner=upper('&&main_owner'); Tablespace Usage select a.TABLESPACE_NAME, a.BYTES/1024/1024 mb_used, b.BYTES/1024/1024 mb_free, b.largest/1024/1024 mb_largest, round(((a.BYTES-b.BYTES)/a.BYTES)*100,2) percent_used from ( select TABLESPACE_NAME, sum(BYTES) BYTES from dba_data_files group by TABLESPACE_NAME ) a, ( select TABLESPACE_NAME, sum(BYTES) BYTES , max(BYTES) largest from dba_free_space group by TABLESPACE_NAME ) b where a.TABLESPACE_NAME=b.TABLESPACE_NAME order by ((a.BYTES-b.BYTES)/a.BYTES) desc --Total cursors open, by session select a.value, s.username, s.sid, s.serial# from v$sesstat a, v$statname b, v$session s where a.statistic# = b.statistic# and s.sid=a.sid and b.name = 'opened cursors current'; --Total cursors open, by username & machine select sum(a.value) total_cur, avg(a.value) avg_cur, max(a.value) max_cur, s.username, s.machine from v$sesstat a, v$statname b, v$session s where a.statistic# = b.statistic# and s.sid=a.sid and b.name = 'opened cursors current' group by s.username, s.machine order by 1 desc;