Dba Scripts All in One [PDF]

Zitiervorschau

##################find all the tables which contain emp no as comman column########################## select * from "sys"."user_tab_cols" where column_name = 'emp_no' ----------------------database startup time--------------------------------select substr(instance_number,1,3) ins,/ substr(upper(instance_name),1,10) database, to_char(startup_time,'dd/mm/yyyy hh24:mi:ss') starttime from v$instance / select sys_context('userenv', 'os_user') from dual; select sys_context('userenv', 'ip_address') from dual; the operating system identifier for the client of the current session select sys_context('userenv', 'terminal') from dual; name of the host machine from which the client has connected. select sys_context('userenv', 'host') from dual; ##################################-memory tuning####################################### ---------------comments:-----------------user_tab_comments user_col_comments **************tblspace fragmentation*********************** select tablespace_name, bytes, blocks from dba_free_space where tablespace_name='users'; ---------------------------------------------------------------------select a.tablespace_name, a.allocated, b.freespace, decode(greatest(a.allocated,500), 500, round(b.freespace-200), decode(greatest(a.allocated,20480), a.allocated, round(b.freespace-4096), round(b.freespace -(a.allocated*.2)))) warning from (select tablespace_name ,sum(bytes)/1024/1024 allocated from dba_data_files group by tablespace_name) a , (select tablespace_name,sum(bytes)/1024/1024 freespace from dba_free_space group by tablespace_name) b

where a.tablespace_name = b.tablespace_name -------------------------------------- -----------------------------------------------------------------------------used space for each oracle user-------------------------select substr(owner,1,16) owner, substr(tablespace_name,1,26) tablespace_name, substr(segment_type,1,10) segment_type, sum(bytes) bytes, sum(blocks) blocks from sys.dba_extents where owner in ('abc','steve','mohan','usrx','myref','rept') group by substr(owner,1,16), substr(tablespace_name,1,26), substr(segment_type,1,10) order by 1,2,3 / ----------------------------------------------------------------------------------------------memory usage shared pool memory usage notes: owner - owner of the object object - name/namespace of the object sharable memory - amount of sharable memory in the shared pool consumed by the object --break on owner on report compute sum of "memory" on report select substr(owner,1,12) owner, substr(name||' - '||type,1,50) object, sharable_mem "memory " from v$db_object_cache where owner in ('steve','usrx','myref','rept') and sharable_mem > 10000 and type in ('package','package body','function','procedure') group by owner,name,type,sharable_mem order by sharable_mem desc / ------------------imp---------------------------------------------------loads loads into shared pool notes: owner - owner of the object object - name/namespace of the object loads - number of times the object has been loaded. this count also increases when an object has been invalidated. select substr(owner,1,10) owner, substr(name||' - '||type,1,55) object,

loads from v$db_object_cache where owner in ('steve','usrx','myref','rept') and loads > 3 and type in ('package','package body','function','procedure') order by loads desc / ------------------imp---------------------------------------------------executions shared pool execution notes: owner - owner of the object object - name/namespace of the object executions - total number of times this object has been executed select substr(owner,1,10) owner, substr(name,1,25)||' - '||substr(type,1,25) object, substr(executions,1,12) executions, sharable_mem mem_used from v$db_object_cache where owner in ('abc','steve','rept','usrx') and executions > 100 and type in ('package','package body','function','procedure') order by executions desc / select substr(owner,1,10) owner, substr(type,1,12) type, substr(name,1,25) name, executions, sharable_mem mem_used --substr(kept||' ',1,4) "kept?" from v$db_object_cache where owner in ('abc','steve','rept','usrx') and type in ('trigger','procedure','package body','package') and executions > 100 order by executions desc; -------------------sql area details-----------------------------------------------select substr(sql_text,1,300) sql_txt, substr(executions,1,4) exec, substr(buffer_gets,1,5) bfgets, substr(disk_reads,1,5) bgreads, substr(rows_processed,1,5) rowpros, --substr(sorts,1,5) sort, --substr(address,1,10) addr, --substr(first_load_time,1,15) frst_load, --substr(hash_value,1,5) hashval, substr(module,1,10) modul from v$sqlarea

where executions >20 and module like 'pl/sql dev%' order by executions desc / ----------------------------------------------------------------------------disk intensive sql sql with most disk read notes: username - name of the user disk reads - total number of disk reads for this statement executions - total number of times this statement has been executed reads/execs - number of reads per execution sql text - text of the sql statement requiring the cursor, or the pl/sql anonymous code select substr(a.username,1,10) username, substr(osuser,1,25) osusr, sid "sid", substr(disk_reads,1,7) disk_reads, substr(executions,1,5) executions, substr(round(disk_reads / decode(executions,0,1,executions)),1,5) "reads/execs", --substr(sql_text,1,150) sql_text from dba_users a, v$, v$sqlarea where parsing_user_id=user_id and a.username in ('steve') --and a.username not in ('sys','system') and address=sql_address(+) and disk_reads > 10000 order by disk_reads desc, executions desc / -------------------------------------------------------------------------open cursors by user open cursors by user notes: username - name of user sql text - text of the sql statement requiring the cursor, or the pl/sql anonymous code set line 300 set pagesize 80 select substr(nvl(username,'oracle proc')||'('||s.sid||')',1,12) username, substr(osuser,1,25) osuser, --substr(machine,1,20) machine, substr(sql_text,1,250) sql_text from v$open_cursor oc, v$session s where s.saddr = oc.saddr and osuser not in ('sys','system') order by substr(osuser,1,25) / -------------------------------------- --------------------------------------

running cursors by user running cursors by user notes: username - name of user sql text - text of the sql statement requiring the cursor, or the pl/sql anonymous code set line 300 set pagesize 85 select substr(nvl(username,'oracle proc')||'('||s.sid||')',1,12) username, substr(osuser,1,20) osuser, --substr(machine,1,20) machine, sql_text sql_text from v$open_cursor oc, v$session s where s.sql_address = oc.address and s.sql_hash_value = oc.hash_value order by substr(osuser,1,25) / -------------------------------------- -------------------------------------cursor usage by session cursor usage by session notes: username - name of the user recursive calls - total number of recursive calls opened cursors - total number of opened cursors current cursors - number of cursor currently in use select user_process username, 0 --machine, user_nm, prog, "recursive calls", "opened cursors", "current cursors" from ( select substr(nvl(ss.username,'oracle proc')||'('||se.sid||') ',1,15) user_process, --substr(ss.machine,1,25) machine, substr(ss.osuser,1,25) user_nm, substr(ss.program,1,20) prog, sum(decode(name,'recursive calls',value)) "recursive calls", sum(decode(name,'opened cursors cumulative',value)) "opened cursors", sum(decode(name,'opened cursors current',value)) "current cursors" from v$session ss, v$sesstat se, v$statname sn where se.statistic# = sn.statistic# and (name like '%opened cursors current%' or name like '%recursive calls%' or name like '%opened cursors cumulative%') and se.sid = ss.sid and ss.username is not null

and ss.username not in ('sys','system') and ss.status='inactive' group by nvl(ss.username,'oracle proc')||'('||se.sid||') ',ss.machine,ss.osuser,ss.program ) orasnap_user_cursors order by "recursive calls" desc --order by user_process,"recursive calls" / ##################################-the sessions####################################### ###################finding sessions that have more than 50 cursors open select sid,count(*) from v$open_cursor group by sid having count(*) > 50 order by count(*) desc;

###################to check the long operations##################### set space0; select substr(sid,1,4)sid,username,opname,substr(totalwork,1,7)totwrk,sofar,message from v$session_longops / select sid,username,opname,totalwork,sofar,time_remaining,message from v$session_longops /

#######################--sessions--############################## -----------user sessions---------------select username, sid, serial#, process, status from v$session where username is not null and osuser in('k?kannaiyan') / ############################################################ column column column column

username format a15 word_wrapped module format a15 word_wrapped action format a15 word_wrapped client_info format a30 word_wrapped

select username||'('||sid||','||serial#||')' username, substr(module,1,20), substr(action,1,20) client_info

from v$session where module||action||client_info is not null / --------------joining v$session and v$sqlarea to obtain current session sql -------col username format a10 col machine format a10 col terminal format a10 col program format a20 select username, audsid, machine, terminal, program, sql_address from v$session where audsid=(select userenv('sessionid') from dual); ######################tracking the progress of long-running query########################## select * from (select username,opname,sid,serial#,context,sofar,totalwork, round(sofar/totalwork*100,2) "% complete" from v$session_longops) where "% complete" != 100 ######################user loged (idle) in from more than one hour############################## set line 150 set pagesize 100 select substr(sid,1,3) sid, substr(serial#,1,5) slno, substr(username,1,10) schema_nm, substr(osuser,1,22) user_nm, substr(machine,1,22) machine_nm, substr(program,1,17) prog, to_char(logon_time,'dd/mm/yyyy hh24:mi:ss am') logon, substr(trunc(last_call_et/3600,2)||' hrs ',1,10) idle_time --substr(last_call_et,1,10) last_call from v$session where last_call_et>3600 and username is not null and status='inactive' and status 'killed' and program like 'plsql%' --and program like 'dllhost.exe%' --and program like 'aspnet_wp.exe%' order by last_call_et desc; ################################################################################## ######## --------------pertcular user------------------select 'alter system kill session ''' || sid || ',' || serial# || ''';' from v$session where

username is not null and osuser in('p?deshpande') and status='inactive' and program like 'plsql%' / ####################################-killing the sessions################################# select 'alter system kill session ''' || sid || ',' || serial# || ''';' from v$session where username is not null username in ('pradeep') and status 'killed' and program like 'plsql%' order by last_call_et desc / ################################################################################## ########## select 'alter system kill session ''' || sid || ',' || serial# || ''';' from v$session where machine like 'pj\snakka%' and status='inactive' and status 'killed' order by username / ################################################################################## ######## select substr(sid,1,3) sid, substr(serial#,1,5) slno, substr(username,1,10) schema_nm, substr(osuser,1,22) user_nm, substr(machine,1,22) machine_nm, substr(program,1,17) prog, to_char(logon_time,'dd/mm/yyyy hh24:mi:ss am') logon --substr(trunc(last_call_et/3600,2)||' hrs ',1,10) idle_time --substr(last_call_et,1,10) last_call from v$session where --last_call_et > 3600 --username is not null username in ('rept') and machine like 'pj\%' and status='inactive' and status 'killed' --and program like 'plsql%' --and program like 'dllhost.exe%' --and program like 'aspnet_wp.exe%' order by username /

break on c.table_name on report break on c.table_name on report break on c.constraint_name,1,25 on report break on c.table_name,c.constraint_name,substr(c.column_name,1,25) on report compute sum of con on report ############################max sessions(score) details for each user########################### break on user_nm on report compute sum of con on report select substr(machine,1,30) machine_nm, substr(osuser,1,30) user_nm, count(*) con from v$session where username is not null and status='inactive' group by substr(machine,1,30),substr(osuser,1,30) order by con desc / ############################max sessions(score) details for specified user########################### select substr(machine,1,30) machine_nm, substr(osuser,1,30) user_nm, count(*) con from v$session where username is not null and osuser in('s?tadikonda') and status='inactive' group by substr(machine,1,30),substr(osuser,1,30) order by con desc / #######################it will gives you the username,systemid,rating############################## select nvl(s.username, 'oracle process')username, s.sid, s.fixed_table_sequence from v$session s where s.type = 'user' and rownum 1) and logon_time=(select max(logon_time) from v$session); ###############sql statement of perticular user####################### select sid, schemaname, osuser, substr(machine,1,20) machine from v$session order by schemaname select sql_text from v$sqlarea where (address, hash_value) in(select sql_address,sql_hash_value from v$session where sid = &sid_number); #########################################################################

select file_name,phyrds,phywrts,decode(phyrds,0,0,phyblkrd/phyrds) �blocks/read� decode(phywrts,0,0,phyblkwrt/phywrts) �blocks/write� from dba_data_files, v$filestat where dba_data_files.file_id=v$filestat.file#;

select substr(sn.name,1,30) parameter,ss.username ||� (�|| se_sid ||�) � user_process, se.value from v$session ss, v$sesstat se, v$statname sn#where se. statistic# = sn.statistic# and sn.name like �%cpu used by this session%� and se.sid=ss.sid order by sn.name, se_value desc; select 'alter ||'object_type'||'owner'||'.'||'object_name'||'move tablespace steveusrx ;' from user_objects wher owner in('usrx') and object_type in('index'); alter user usrx quota 0 on users;

################################################################################# --------------username and sql text--------------------------------------------select osuser, username, sql_text from v$session a, v$sqltext b where a.sql_address =b.address order by address, piece -------------------sessions order by highest physical reads----------------------------set line 300 set pagesize 80 select --substr(p.spid,1,4) spid , ----substr(p.pid,1,3) pid, substr(si.sid,1,3) sid , substr(s.audsid,1,4) usid, substr(s.serial#,1,5) "serial#", --substr(s.type,1,5) "type", substr(s.username,1,7) "db user", substr(s.osuser,1,22) "client user", --substr(s.server,1,15) "server", --substr(s.machine,1,22) "machine", --substr(s.module,1,20) "module", --substr(s.client_info,1,15) "client info", --substr(s.terminal,1,15) "terminal", substr(s.program,1,18) "program", --substr(p.program,1,15) "o.s. program", to_char(s.logon_time,'dd/mm/yyyy hh24:mi:ss') "connect time", --substr(lockwait,1,4) "l w", substr(si.physical_reads,1,8) "p r", substr(si.block_gets,1,4) "b g",

substr(si.consistent_gets,1,8) "c g", substr(si.block_changes,1,4) "b c" --si.consistent_changes "consistent changes", --s.process "process", --s.sql_address "address", --s.sql_hash_value "sql hash", --s.action, --to_char(sysdate - (s.last_call_et / 86400),'dd/mm/yyyy') "last call" from v$session s, v$process p, sys.v_$sess_io si where s.paddr = p.addr(+) and si.sid(+) = s.sid and s.status in ('inactive') and (s.username is not null) and (nvl (s.osuser, 'x') 'system') and (nvl (s.username, 'x') 'system') and (s.type 'background') and si.physical_reads >=1000 order by si.physical_reads desc / ----------------------------------------------------------------------------------who uses what details---------------------------------------break on "owner" on report select sid "sid", substr(owner,1,15) "owner", substr(object,1,25) "object" from v$access where owner in ('usrx') order by owner / -----------------lists all tables with their pk columns that are referred as fk in other tables---------------select ucc.table_name, ucc.column_name from user_constraints uc, user_cons_columns ucc where uc.constraint_name in (select r_constraint_name from user_constraints where constraint_type='r') and uc.table_name=ucc.table_name and uc.constraint_name=ucc.constraint_name -------following query may be used to check parent and child --------------------------

select parent_qry.table_name pk_table, parent_qry.column_name pk_column, child_qry.table_name fk_table, child_qry.column_name fk_column from (select uc.constraint_name,ucc.table_name, ucc.column_name from user_constraints uc, user_cons_columns ucc where uc.constraint_name in (select r_constraint_name

from user_constraints where constraint_type='r') and uc.table_name=ucc.table_name and uc.constraint_name=ucc.constraint_name) parent_qry, (select uc.r_constraint_name,ucc.table_name, ucc.column_name from user_constraints uc, user_cons_columns ucc where uc.constraint_name in (select constraint_name from user_constraints where constraint_type='r') and uc.table_name=ucc.table_name and uc.constraint_name=ucc.constraint_name) child_qry where parent_qry.constraint_name=child_qry.r_constraint_name

-------------------row locks details----------------------------------------select s.username, s.sid, o.owner, o.object_name, a.sql_text, s.row_wait_file#, s.row_wait_block#, s.row_wait_row# from v$session s, v$sqlarea a, dba_objects o where o.object_id = s.row_wait_obj# and s.sql_address = a.address and s.row_wait_obj# > 0 / ----------------------high cpu usage in this sessions--------------------select substr(ss.username||'('||se.sid||')',1,15) user_process, substr(ss.osuser,1,23) user_nm, substr(ss.machine,1,23) machine_nm, substr(ss.program,1,17) program, value from v$session ss, v$sesstat se, v$statname sn where se.statistic# = sn.statistic# and name like '%cpu used by this session%' and se.sid = ss.sid and ss.username is not null and ss.status in ('inactive') and ss.username not in ('sys','system') and value >=10 order by substr(name,1,25), value desc /

---------------------to analyze the table ------------------------------select 'analyze table '||table_name||' estimate statistics;' from cat where table_type='table' --and table_name like 'ptadv001gt%' /

analyze table ptadv001gt estimate statistics;

---------------- to analyze all the tables and indexes of a schema ----------------select ' exec dbms_stats.gather_table_stats (ownname=>'||chr(39)||owner|| chr(39)||',tabname=>'||chr(39)||table_name||chr(39)||', degree=>8,estimate_percent=>40,cascade=>true);' from dba_tables where owner='steve'

--------------------if the table size is greater than 10 mb in size, statistics are estimated. select 'analyze table '||owner||'.'||table_name||' '|| decode(sign(10485760 - initial_extent),1,'compute statistics;','estimate statistics;') from sys.dba_tables where owner in ('usrx') / ------------------analyzed talbes details--------------------select substr(owner,1,10) owner, sum(decode(nvl(num_rows,9999), 9999,0,1)) "tables analyzed", sum(decode(nvl(num_rows,9999), 9999,1,0)) "tables not analyzed", count(table_name) total --sum(decode(nvl(num_rows,9999), 9999,0,1)) + sum(decode(nvl(num_rows,9999), 9999,1,0)) "total tables" from all_tables where owner in ('steve') group by owner / ------------------or--------------------------------------------------select substr(owner,1,10) owner, sum(decode(nvl(num_rows,9999), 9999,0,1)) analyzed, sum(decode(nvl(num_rows,9999), 9999,1,0)) not_analyzed, count(table_name) total from dba_tables where owner in ('steve') group by owner; --------------------------------------------------------------------------------------query for recently analyzed tables select from where and and --and and

substr(owner,1,10) owner, substr(table_name,1,25) table_name, to_char(last_analyzed,'mm/dd/yyyy hh24:mi:ss') lastanalyzed dba_tab_columns owner in ('steve') last_analyzed is not null column_id=1 (sysdate-last_analyzed) < 2 to_char(last_analyzed,'mm/dd/yyyy') >= '10/17/2005'

order by (sysdate-last_analyzed); -----------------------------------------------------------------------------query for tables not analyzed select --substr(owner,1,15) owner, substr(table_name,1,25) table_name, last_analyzed last_analyzed from dba_tables where owner in ('rept') and last_analyzed is null --and last_analyzed < ('15-oct-05') --and (sysdate-last_analyzed) < 30 order by table_name --order by (sysdate-last_analyzed) / ----------------table created

dates details-------------------------------

select substr(object_name,1,25) table_name, substr(to_char(created,'mm/dd/yyyy hh24:mi:ss'),1,25) created_datetime, substr(to_char(last_ddl_time,'mm/dd/yyyy hh24:mi:ss'),1,25) modified_datetime , status from dba_objects where owner='rept' and object_type in ('table') --and object_name like 'cltst%' and to_char(created,'mm/dd/yyyy')>='10/20/2005' --and to_char(created,'mm/dd/yyyy') to_char(last_ddl_time,'mm/dd/yyyy') order by created desc / ----------------------table modified dates details------------------------select substr(object_name,1,25) table_name, substr(to_char(created,'mm/dd/yyyy hh24:mi:ss'),1,25) created_datetime, substr(to_char(last_ddl_time,'mm/dd/yyyy hh24:mi:ss'),1,25) modified_datetime , status from dba_objects where owner='steve' and object_type in ('table') --and object_name like 'adfcd001lt' and to_char(last_ddl_time,'mm/dd/yyyy')='11/28/2005' order by last_ddl_time desc / ----------------------find user who modified table------------------------select s.sid,substr(d.object_name,1,25) table_name, substr(to_char(d.created,'mm/dd/yyyy hh24:mi:ss'),1,25) created_datetime, substr(to_char(d.last_ddl_time,'mm/dd/yyyy hh24:mi:ss'),1,25) modified_datetime , d.status from dba_objects d,v$session s

where d.owner='steve' and object_type in ('table') --and object_name like 'adfcd001lt' and to_char(d.last_ddl_time,'mm/dd/yyyy') between '02/27/2006' and '02/28/2006' and d.object_name!='ptpmt002gt' and d.owner=s.username order by last_ddl_time desc ------------------------tables with no data-------------------------------sql> select 2 substr(owner,1,10) owner, 3 substr(table_name,1,35) tab, 4 num_rows, 5 empty_blocks 6 from dba_tables 7 where owner in ('steve_prod') 8 and num_rows 50000 and executions > 0 and v$sqlarea.parsing_user_id = dba_users.user_id order by 4 desc; ------------------------------monitoring rollback progress------------------select t.used_ublk, t.used_urec from v$session s, v$transaction t where s.taddr=t.addr and s.sid =:sid; ================================================================================== ============= rem script description: this script produces a report which describes the indexed columns rem for each index of each table of a specified schema. the user is rem prompted from the schema name when the script is run. the indexes rem for each table are listed on a separate page. break on table_name skip page on index_name skip 1; select substr(i.index_owner ||'.'|| i.index_name,1,35) index_name, --substr(i.table_owner,1,10) tab_owner, substr(i.table_name,1,25) tab_name, substr(i.column_name,1,25) col_nm, substr(i.column_position,1,3) pos, substr(i.column_length,1,5) length --substr(t.data_type,10) data_type from all_ind_columns i, all_tab_columns t where table_owner = upper('steve')

and t.owner = i.table_owner and t.table_name = i.table_name and t.column_name = i.column_name and i.table_name like upper('adr')||'%' order by i.table_name, i.index_name;

--------------------------primary keys details---------------------------------======================================================================= /* script description: this script will identify large anonymous blocks of pl/sql that should be replaced with packaged procedures and pinned set lines 132 pages 1000; select loads,version_count,executions,sql_text from v$sqlarea where command_type=47 and length(sql_text) > 250; ========================================================================= rem script description: this script identifies pertinent information for jobs scheduled through oracle. select substr(job,1,10) job, --substr(log_user,1,10) log_usr, --substr(priv_user,1,10) prv_usr, substr(schema_user,1,10) sch_usr, to_char(last_date, 'dd/mm/yy hh24:mi') last, to_char(next_date, 'dd/mm/yy hh24:mi:ss') next, substr(interval,1,20) interval, substr(broken,1,20) broken, substr(failures,1,20) failures, substr(what,1,30) what from dba_jobs order by job; spool off; =================================================================== ----------------this simple script lists all tables that do not have any indexes. select owner, table_name from all_tables where owner in ('steve') minus select owner, table_name from all_indexes; ====================================================================== ---------------------database size in gb--------------------------select trunc(a.data_size+b.temp_size+c.redo_size,2) "total_size in gb" from ( select sum(bytes)/1024/1024/1024 data_size

from ( select from ( select from

dba_data_files ) a, nvl(sum(bytes),0)/1024/1024/1024 temp_size dba_temp_files ) b, sum(bytes)/1024/1024/1024 redo_size sys.v_$log ) c

/ ----------to know the table space details and free space in mb --------compute sum of total on report compute sum of free on report compute sum of used on report break

on report

select substr(a.tablespace_name,1,13) tablespace, round(sum(a.total1)/1024/1024, 1) total, round(sum(a.total1)/1024/1024, 1)-round(sum(a.sum1)/1024/1024, 1) used, round(sum(a.sum1)/1024/1024, 1) free, round(sum(a.sum1)/1024/1024, 1)*100/round(sum(a.total1)/1024/1024, 1) pct_free, round(sum(a.maxb)/1024/1024, 1) largest, max(a.cnt) fragment from (select tablespace_name, 0 total1, sum(bytes) sum1, max(bytes) maxb, count(bytes) cnt from dba_free_space group by tablespace_name union select tablespace_name, sum(bytes) total1, 0, 0, 0 from dba_data_files group by tablespace_name) a group by a.tablespace_name / ----------------schema size-------------------------select substr(tablespace_name,1,10) table_space, sum(bytes)/1024/1024/1024 as total_size_gb from dba_segments where owner in ('myref') group by owner , rollup(tablespace_name) / -----------------to know the table space details and free space in gb---------------------compute sum of total on report compute sum of free on report compute sum of used on report break

on report

select substr(a.tablespace_name,1,13) tablespace, round(sum(a.total1)/1024/1024/1024, 1) total, round(sum(a.total1)/1024/1024/1024, 1)-round(sum(a.sum1)/1024/1024/1024, 1) used, round(sum(a.sum1)/1024/1024/1024, 1) free, round(sum(a.sum1)/1024/1024, 1)*100/round(sum(a.total1)/1024/1024, 1) pct_free, round(sum(a.maxb)/1024/1024, 1) largest, max(a.cnt) fragment

from (select tablespace_name, 0 total1, sum(bytes) sum1, max(bytes) maxb, count(bytes) cnt from dba_free_space group by tablespace_name union select tablespace_name, sum(bytes) total1, 0, 0, 0 from dba_data_files group by tablespace_name) a group by a.tablespace_name / --------------------or----------------------------------------------break on report compute sum of kbytes on report compute sum of free on report compute sum of used on report select nvl(b.tablespace_name,nvl(a.tablespace_name,'unkown')) name , kbytes_alloc kbytes , kbytes_alloc-nvl(kbytes_free,0) used , nvl(kbytes_free,0) free , ((kbytes_alloc-nvl(kbytes_free,0))/kbytes_alloc)*100 pct_used , nvl(largest,0) largest from ( select sum(bytes)/1024 kbytes_free , max(bytes)/1024 largest , tablespace_name from sys.dba_free_space group by tablespace_name ) a , ( select sum(bytes)/1024 kbytes_alloc , tablespace_name from sys.dba_data_files group by tablespace_name ) b where a.tablespace_name (+) = b.tablespace_name / ----------------------database growth ------------------------------select to_char(creation_time, 'rrrr month') "month" , sum(bytes)/1024/1024 "growth in meg" from sys.v_$datafile where creation_time > sysdate-365 group by to_char(creation_time, 'rrrr month'); ==================================================================== --------------to display all objects count of schema-----------------------select substr(u.name,1,10) "user", substr(sum(decode(o.type#, 2, 1, 0 )),1,6) tables, substr(sum(decode(o.type#, 3, 1, 0 )),1,6) clust, substr(sum(decode(o.type#, 1, 1, 0 )),1,8) indexes, substr(sum(decode(o.type#, 4, 1, 0 )),1,6) views, substr(sum(decode(o.type#, 5, 1, 0 )),1,6) syn, substr(sum(decode(o.type#, 6, 1, 0 )),1,6) seq, substr(sum(decode(o.type#, 7, 1, 0 )),1,6) proc, substr(sum(decode(o.type#, 8, 1, 0 )),1,6) func, substr(sum(decode(o.type#, 9, 1, 0 )),1,6) pkg, substr(sum(decode(o.type#, 12, 1, 0 )),1,6) trig

from sys.obj$ o, sys.user$ u where owner# = u.user# and u.name in ('steve') group by u.name ; ================================================================================== ======= ---------to know the table details like datatype ---------------------break on tnam skip 1 select substr(tc.table_name,1,30) tnam, substr(tc.column_id,1,3) cid, substr(tc.column_name,1,20) tcol, substr(tc.data_type,1,10) typ, substr(tc.data_length,1,5) len, substr(tc.data_precision,1,5) prc, substr(decode(tc.nullable,'n','no','y','yes','no'),1,5) nul, --substr(tc.num_distinct,1,5) dis, --substr(tc.low_value,1,20) lv, --substr(tc.high_value,1,20) hv, -- substr(tc.density,1,20) dens, -tc.num_nulls nnul, to_char(tc.last_analyzed,'mm/dd/yy hh24:mi:ss') analyzed_date from dba_tab_columns tc where tc.owner = upper('steve') and tc.table_name like upper('adrpt')||'%' order by 1 ; ================================================================================== ========= -----------------tables with out primary key-----------------------select owner, table_name from dba_tables a where table_name not in (select table_name from dba_constraints where owner=a.owner and constraint_type='p') and owner in ('steve') order by owner; ---------data files information---------------------------------------select substr(tb.name,1,10) tsname, substr(v.name,1,40) dfname, substr(decode(f.status$, 1, 'invalid', 2, 'available', 'undefined'),1,12) status, f.blocks from file$ f, v$datafile v, v$tablespace tb where f.blocks > 0 and f.file#=v.file# and f.ts# = tb.ts# order by 1,2 ; ================================================================================== ============== -------to dispaly the jobs------------------------select substr(job,1,4) "job",

substr(log_user,1,5) "user", substr(schema_user,1,5) "schema", substr(to_char(last_date,'dd.mm.yyyy hh24:mi'),1,16) "last date", substr(to_char(next_date,'dd.mm.yyyy hh24:mi'),1,16) "next date", substr(broken,1,2) "b", substr(failures,1,6) "failed", substr(what,1,20) "command" from dba_jobs;

----------data files inforamtion(imp)-------------------------------------------break on table_space on report skip 1 compute sum of total on table_space compute sum of free on table_space compute sum of used on table_space compute sum of used on report compute sum of total on report compute sum of free on report select substr(a.tablespace_name,1,15) table_space, substr(a.file_name,1,45) data_file, a.avail total, nvl(a.avail,0)-nvl(b.free,0) used, nvl(b.free,0) free -- nvl(round(((free/avail)*100),2),0) a5 from (select tablespace_name, substr(file_name,1,45) file_name, file_id, round(sum(bytes/(1024*1024)),3) avail from sys.dba_data_files group by tablespace_name, substr(file_name,1,45), file_id) a, (select tablespace_name, file_id, round(sum(bytes/(1024*1024)),3) free from sys.dba_free_space group by tablespace_name, file_id) b where a.file_id = b.file_id (+) order by 1, 2 / -------------------------------------------------------------------------------------select substr(df.tablespace_name,1,12) tblsp_name, substr(df.file_name,1,40) data_file, substr(df. status,1,10) sta, (df.bytes/1024000) tot, (fs.s/df.bytes*100) fre, substr(decode (ae.y,1,'yes','no'),1,3) a from dba_data_files df, (select file_id,sum(bytes) s from dba_free_space group by file_id) fs, (select file#, 1 y from sys.filext$ group by file#) ae where df.file_id = fs.file_id

and ae.file#(+) = df.file_id order by df.tablespace_name, df.file_id; ------------objecsts and table space details------------------------------select substr(owner,1,10) owner, substr(tablespace_name,1,10) tablespace_name, substr(segment_type,1,15) segment_type, sum(bytes) bytes, sum(blocks) blocks from sys.dba_extents where owner in ('abc','steve','mohan','usrx','myref') group by substr(owner,1,10), substr(tablespace_name,1,10), substr(segment_type,1,15) order by 1,2,3; ----------------------tables and indexes in system table space------------------------------select substr(owner,1,10) owner, substr(segment_name,1,20) segment_name, substr(segment_type,1,15) segment_type, substr(tablespace_name,1,10) table_space from dba_segments where owner in ('rept') and tablespace_name in ('users') and segment_type in('table') order by 2 / -------------------------------------------------------------------------------------------to find out which object is corrupted------------select segment_type, segment_name from dba_extents where file_id = 1 and block_id < 3840 and block_id + blocks >= 3840; ---------or-------------------select segment_type, segment_name from dba_extents where file_id = 1 and 3840 between block_id and block_id + blocks -1; ----------------parameter file details-------------------------------------set pagesize 9000 set head off set term off spool initparameters.ora select decode(isdefault, 'true', '# ')||decode(isdefault, 'true', rpad(name,43), rpad(name,45))||' = '|| value "parameter and value" from v$parameter --where upper(name) like '%shared%' order by name;

spool off ================================================================================== =============== ------------------errors while creating proc,pkg and trig-----------------------select substr(type,1,25) typ, substr(owner,1,10) own, substr(name,1,30) nm, substr(sequence,1,3) seq, substr(line,1,3) len, substr(position,1,3) pos, substr(text,1,150) || chr(10) || chr(10) text from dba_errors where owner in ('steve') order by 1, 2, 3 / ================================================================================== =============== ------------------------------tables,columns,datatyes and created date-------------------------set line 200 set pagesize 100 break on table_name skip 1 select substr(tc.table_name,1,30) table_name, substr(tc.column_id,1,3) cid, substr(tc.column_name,1,20) column_name, substr(tc.data_type,1,10) data_type, substr(tc.data_length,1,5) len, substr(tc.data_precision,1,5) prc, substr(decode(tc.nullable,'n','no','y','yes','no'),1,5) nul, --substr(tc.num_distinct,1,5) dis, --substr(tc.low_value,1,20) lv, --substr(tc.high_value,1,20) hv, -- substr(tc.density,1,20) dens, -tc.num_nulls nnul, to_char(do.created,'mm/dd/yyyy hh24:mi:ss') created_date --to_char(last_ddl_time,'mm/dd/yyyy hh24:mi:ss') modified_date, --to_char(tc.last_analyzed,'mm/dd/yy hh24:mi:ss') analyzed_date from dba_tab_columns tc,dba_objects do where tc.owner = upper('steve') and tc.table_name like upper('adfcd')||'%' and tc.owner = do.owner and do.object_type in ('table') and tc.table_name=do.object_name order by 1,2 ;

select count(*) from dba_tab_columns where owner in ('abc') and table_name in ('adusr001mt');

----------------------------long running sql statements---------------------alter session set sort_area_size=1048576; col col col col

rows_processed buffer_gets executions disk_reads

form form form form

9,999,990 999,999,990 999,999,990 99,999,990

head rows

set pages 60 set lines 132 set newpage 0 break on rows_processed on loads on executions on buffer_gets on disk_reads ttitle 'long running sql statements' select s.rows_processed, s.loads, s.executions, s.buffer_gets, s.disk_reads, t.sql_text from v$sql/*area*/ s, v$sqltext t where s.address = t.address and ((buffer_gets > 10000000) or (disk_reads > 1000000) or (executions > 1000000)) order by ((s.disk_reads * 100) + s.buffer_gets) desc, t.address, t.piece / select c.executions,trunc(a.last_call_et/60) min,b.sql_text from v$session a ,v$sqltext b ,v$sqlarea c where --a.sid=167 a.sql_address = b.address and a.sql_address = c.address order by b.piece; -------tables with no rows---------------select owner,table_name from dba_tables where num_rows = 0 and owner in ('abc')order by 1,2; -------columns with no value----------------break on table_name skip 1 select --substr(owner,1,10) owner, substr(table_name,1,25) table_name, substr(column_name,1,20) column_name from dba_tab_columns where num_distinct = 0 and owner in ('abc') order by table_name / ------------------temporary segments---------------------select tablespace_name, segment_name, segment_type, sum(bytes), count(extent_id) from dba_extents where segment_type = 'temporary' group by tablespace_name, segment_name, segment_type /

set pages 999; column pga_size format 999,999,999 select a.value,b.value,2048576+a.value+b.value pga_size from v$parameter a, v$parameter b where a.name = 'sort_area_size' and b.name = 'hash_area_size' / --------------temp segments--------------------break on tablespace_name skip 1 on file_id skip 1 select tablespace_name, file_id, segment_name,segment_type, block_id, blocks from dba_extents where tablespace_name = 'temp' order by file_id, block_id / break on tablespace on segfile# select b.tablespace,b.segfile#,b.segblk#,b.blocks from v$sort_usage b / select a.tablespace,a.segfile#,a.segblk#,a.blocks,b.sid,b.serial#,b.username,b.osuser,b.s tatus from v$sort_usage a,v$session b where a.session_addr = b.saddr order by a.tablespace,a.segfile#,a.segblk# / select a.tablespace, b.sid, b.serial#, a.blocks,c.sql_text from v$sort_usage a,v$session b,v$sqltext c where a.session_addr = b.saddr and b.sql_address = c.address order by a.tablespace,b.sid,b.serial#,c.address, c.piece / select * from (select username,opname,sid,serial#,context,sofar,totalwork ,round(sofar/totalwork*100,2) "% complete" from v$session_longops) where "% complete" != 100 / select a.executions, a.disk_reads, a.disk_reads/a.executions dre, a. _gets, a.buffer_gets/a.executions bge, b.username, a.first_load_time,

a.sql_text from v$sql a, all_users b where a.executions > 0 and a.parsing_user_id = b.user_id and username not in ('sys','system') order by 1 desc / select t.used_ublk, t.used_urec from v$session s, v$transaction t where s.taddr=t.addr --and s.sid =:sid; ------------------to dispaly sequence and current value--------------------------------------imp first analyze the tables before running this script---------------select substr(b.table_name,1,30) tab, --c.num_rows norows, substr(a.sequence_name,1,30) seq, substr(b.column_name,1,25) col, substr(a.last_number,1,5) cur_val from user_sequences a, user_ind_columns b --,user_tables c where substr(a.sequence_name ,1,length(a.sequence_name)-4)=b.table_name and b.index_name like '%pk%' --and c.table_name = b.table_name -- and a.sequence_name like 'cltst003dt%' --and b.table_name like 'adalm001lt%' order by b.table_name / ----------------tables with out sequences details---------------------------------select distinct * from ( select substr(b.table_name,1,30) tab --substr(b.column_name,1,25) col from user_ind_columns b,user_objects c where b.table_name = c.object_name and object_type in ('table') and substr(c.object_name,1,3) not in ('smp','vdk','vbz') and b.table_name not in (select substr(a.sequence_name,1,length(a.sequence_name)-4) from user_sequences a where substr(a.sequence_name,1,length(a.sequence_name)-4)=b.table_name and b.index_name is not null) --and b.table_name like 'ad%' order by b.table_name) / --------------temporary tables details----------------------------------select owner,table_name,last_analyzed from dba_tables where owner in ('steve') and temporary ='y' ----------------------------------------------------------------------------------------total memory of all running sessions-------------------select sum(se.value)||' bytes' "total memory for all sessions" from v$sesstat se, v$statname n

where n.statistic# = se.statistic# and n.name = 'session pga memory'

/ -------------session total waits and time waited---------------------select substr(a.sid,1,5) "sid", substr(a.process,1,7) "process", substr(a.username,1,10) "user", substr(a.osuser,1,23) user_nm, substr(a.machine,1,23) machine_nm, substr(a.program,1,17) program, total_waits, time_waited from v$session_event v, v$session a where v.event='db file scattered read' and v.total_waits > 0 and a.sid = v.sid and a.username not in ('sys','system') order by v.total_waits desc / -------------un used columns in the tables--------------------------------create or replace view dba_unused_col_tabs ( owner ,table_name ,count ) as select u.name, o.name, count(*) from sys.user$ u, sys.obj$ o, sys.col$ c where c.obj# = o.obj# and bitand(c.property,32768) = 32768 -- is unused column and bitand(c.property, 1) != 1 -- not adt attribute col and bitand(c.property, 1024) != 1024 -- not ntab's setid col and u.user# = o.owner# and u.user# in ('abc') group by u.name, o.name / select u.name, o.name, decode(c.type#, 1, decode(c.charsetform, 2, 'nvarchar2', 'varchar2'), 2, decode(c.scale, null, decode(c.precision#, null, 'number', 'float'),'number'), 8, 'long', 9, decode(c.charsetform, 2, 'nchar varying', 'varchar'), 12, 'date', 23, 'raw', 24, 'long raw', 69, 'rowid', 96, decode(c.charsetform, 2, 'nchar', 'char'), 105, 'mlslabel', 106, 'mlslabel', 111, 'unknown', 112, decode(c.charsetform, 2, 'nclob', 'clob'), 113, 'blob', 114, 'bfile', 115, 'cfile', 121, 'unknown', 122, 'unknown', 123, 'unknown', 178, 'time(' ||c.scale|| ')', 179, 'time(' ||c.scale|| ')' || ' with time zone', 180, 'timestamp(' ||c.scale|| ')', 181, 'timestamp(' ||c.scale|| ')' || ' with time zone',

231, 'timestamp(' ||c.scale|| ')' || ' with local time zone', c.scale || ')',

182, 'interval year(' ||c.precision#||') to month', 183, 'interval day(' ||c.precision#||') to second(' ||

208, 'urowid','undefined'), c.length, c.precision#, c.scale from sys.user$ u, sys.obj$ o, sys.col$ c where c.obj# = o.obj# and bitand(c.property,32768) = 32768 -- is unused column and bitand(c.property, 1) != 1 -- not adt attribute col and bitand(c.property, 1024) != 1024 -- not ntab's setid col and u.user# = o.owner# / --------report on all objects that do not have statistics collected on them----------------------------------select owner owner , 'table' object_type , table_name object_name , null partition_name from sys.dba_tables where last_analyzed is null and owner in ('steve') and partitioned = 'no' union select owner owner , 'index' object_type , index_name object_name , null partition_name from sys.dba_indexes where last_analyzed is null and owner in ('steve') and partitioned = 'no' union select table_owner owner , 'table partition' object_type , table_name object_name , partition_name partition_name from sys.dba_tab_partitions where last_analyzed is null and table_owner in ('steve') union select index_owner owner , 'index partition' object_type , index_name object_name , partition_name partition_name from sys.dba_ind_partitions where

last_analyzed is null and index_owner in ('steve') order by 1 , 2 , 3 / ----------find the tables more than 100 rows------------------create or replace function rowcount(tname varchar2) return number is x number; stmt varchar2(200); begin stmt := 'select count(*) from '||tname; execute immediate stmt into x; return x; exception when no_data_found then return 0; end; / select table_name, rowcount(table_name) from cat where rowcount(table_name) >= 100; / --------the session id,event name,time waited on the database. useful in identifying the resource intensive queries. ----------------------------select substr(se.sid,1,3) sid, substr(s.username,1,15) user, substr(se.event,1,20) event, substr(se.time_waited,1,20) tim from v$session s, v$session_event se where s.username is not null and s.username not in ('sys','system') and se.sid=s.sid and s.status='active' and se.event not like '%sql*net%' /

----------------------------system schema--------------------------------select owner,substr(segment_name,1,25) segname, substr(segment_type,1,12) segtype, blocks, extents from dba_segments where segment_name='sycdm001lt'; -----------------------------steve schema---------------------------------select

substr(table_name,1,25) tablename, num_rows,blocks,empty_blocks from user_tables where table_name='adrptc003dt'; select count (distinct dbms_rowid.rowid_block_number(rowid)|| dbms_rowid.rowid_relative_fno(rowid)) "used" from adrpt003dt; ---------------------------analyze table------------------------------------------------truncate table adrcp001gt reuse storage truncate table adrpt003dt; analyze table adrpt003dt estimate statistics; select 'analyze table '||owner||'.'||segment_name||' from dba_segments where segment_type in('table') and owner in('steve');

estimate statistics;'

----------------------------------------------------------------------select substr(owner,1,10) owner, substr(table_name,1,35) tab, num_rows, substr(blocks,1,5) block, empty_blocks, substr(avg_space,1,5) avg_space, substr(chain_cnt,1,5) chain, substr(avg_row_len,1,5) avg_row, last_analyzed from dba_tables where owner in ('steve') and last_analyzed is not null and table_name like 'adrpt%' and num_rows = 1000) then commit; cnt := 0; end if; end loop; commit; dbms_output.put_line(' records deleted: '||tot); end; / # *******************tables without primary key constraint *************** select from

substr(owner,1,10) owner, substr(table_name,1,35) table_name (select owner, table_name from dba_tables where owner in ('steve') minus select owner, table_name from dba_constraints where owner in ('steve') and constraint_type = 'p')

----------trace file details---------------------------------select s.sid, s.serial#, pa.value || '/' || lower(sys_context('userenv','instance_name')) || '_ora_' || p.spid || '.trc' as trace_file from v$session s, v$process p, v$parameter pa where pa.name = 'user_dump_dest' and s.paddr = p.addr and s.audsid = sys_context('userenv', 'sessionid');

-----------grants details----------------------------select grantee, privilege, admin_option from sys.dba_sys_privs where (privilege like '% any %' or privilege in ('become user', 'unlimited tablespace') or admin_option = 'yes') and grantee not in ('sys', 'system', 'outln', 'aq_administrator_role', 'dba', 'exp_full_database', 'imp_full_database', 'oem_monitor', 'ctxsys', 'dbsnmp', 'ifssys', 'ifssys$cm', 'mdsys', 'ordplugins', 'ordsys', 'timeseries_dba') / ----------deadly roles to assigned to users------------------------------select grantee, granted_role, admin_option from sys.dba_role_privs where granted_role in ('dba', 'aq_administrator_role', 'exp_full_database', 'imp_full_database', 'oem_monitor') and grantee not in ('sys', 'system', 'outln', 'aq_administrator_role', 'dba', 'exp_full_database', 'imp_full_database', 'oem_monitor', 'ctxsys', 'dbsnmp', 'ifssys', 'ifssys$cm', 'mdsys', 'ordplugins', 'ordsys', 'timeseries_dba') / **************************************************************************** database performance tuning scripts **************************************************************************** measure the buffer cache hit ratio increase db_block_buffer if cache hit ratio < 90% -------------------------------------------------------------------------------select 1-(phy.value / (cur.value + con.value)) "cache hit ratio", round((1-(phy.value / (cur.value + con.value)))*100,2) "% ratio" from v$sysstat cur, v$sysstat con, v$sysstat phy where cur.name = 'db block gets' and con.name = 'consistent gets' and phy.name = 'physical reads' / select (d.value+c.value-p.value)/(d.value+c.value)*100 "hit ratio" from v$sysstat d,v$sysstat c,v$sysstat p where d.name='db block gets' and c.name='consistent gets' and p.name ='physical reads' / col name format a20 heading "buffer pool name select name,free_buffer_wait,write_complete_wait, buffer_busy_wait,db_block_gets, consistent_gets,physical_reads, physical_writes from v$buffer_pool_statistics; ----------------sessions with bad buffer cache hit ratio in %----------------

select substr(a.sid,1,5) "sid", substr(a.username,1,10) "user", substr(a.osuser,1,23) user_nm, --substr(a.machine,1,23) machine_nm, substr(a.program,1,17) program, b.consistent_gets "consgets", b.block_gets "blockgets", b.physical_reads "physreads", 100 * round((b.consistent_gets + b.block_gets - b.physical_reads) / (b.consistent_gets + b.block_gets),3) hitratio from v$session a, v$sess_io b where a.sid = b.sid and (b.consistent_gets + b.block_gets) > 0 and a.username is not null and a.username not in ('sys','system') order by hitratio asc / -------------response time per transaction---------------------------select ((wait + val) / trxn_cnt) as trxn_speed from (select sum(total_waits) as wait from v$system_event) , (select value as val from v$sysstat where name='cpu used by this session'), (select sum(value) as trxn_cnt from v$sysstat where name in ('user rollbacks','user commits')) / ------------sql text executing morethan 25 times(sql hard parsing )--------------select substr(sql_text,1,50), count(*) from v$sql group by substr(sql_text,1,50) having count(*) > 25 / rem**---------------------latch contention----------------** col name format a40 heading "latch name" select name, gets, misses, round((gets-misses)/decode(gets,0,1,gets),3) hit_ratio, sleeps, round(sleeps/decode(misses,0,1,misses),3) "sleeps/misses" from v$latch where gets != 0 order by name; select name,immediate_gets,immediate_misses,(immediate_gets)/(immediate_gets+immediate_mi sses) hit_ratio from v$latch where immediate_gets != 0; rem**---------------shared pool statistics-------------------------------** col namespace format a22 select namespace,gets,gethits,round(gethitratio,2) gethitratio,pins,pinhits,round(pinhitratio,2) pinhitratio,reloads,invalidations from v$librarycache; -----------------------------------------------------------------------

display database sga statistics ----------------------------------------------------------------------declare libcac number(10,2); rowcac number(10,2); bufcac number(10,2); redlog number(10,2); spsize number; blkbuf number; logbuf number; begin select value into redlog from v$sysstat where name = 'redo log space requests'; select 100*(sum(pins)-sum(reloads))/sum(pins) into libcac from v$librarycache; select 100*(sum(gets)-sum(getmisses))/sum(gets) into rowcac from v$rowcache; select 100*(cur.value + con.value - phys.value)/(cur.value + con.value) into bufcac from v$sysstat cur,v$sysstat con,v$sysstat phys,v$statname ncu,v$statname nco,v$statname nph where cur.statistic# = ncu.statistic# and ncu.name = 'db block gets' and con.statistic# = nco.statistic# and nco.name = 'consistent gets' and phys.statistic# = nph.statistic# and nph.name = 'physical reads'; select value into spsize from v$parameter where name = 'shared_pool_size'; select value into blkbuf from v$parameter where name = 'db_block_buffers'; select value into logbuf from v$parameter where name = 'log_buffer'; dbms_output.put_line('> sga cache statistics'); dbms_output.put_line('> ********************'); dbms_output.put_line('> sql cache hit rate = '||libcac); dbms_output.put_line('> dict cache hit rate = '||rowcac); dbms_output.put_line('> buffer cache hit rate = '||bufcac); dbms_output.put_line('> redo log space requests = '||redlog); dbms_output.put_line('> '); dbms_output.put_line('> init.ora setting'); dbms_output.put_line('> ****************'); dbms_output.put_line('> shared pool size = '||spsize||' bytes'); dbms_output.put_line('> db block buffer = '||blkbuf||' blocks'); dbms_output.put_line('> log buffer = '||logbuf||' bytes'); dbms_output.put_line('> '); if libcac < 99 then dbms_output.put_line('*** hint: library cache too low! increase the shared pool size.'); end if; if rowcac < 85 then dbms_output.put_line('*** hint: row cache too low! increase the shared pool size.'); end if; if bufcac < 90 then dbms_output.put_line('*** hint: buffer cache too low! increase the db block buffer value.'); end if; if redlog > 100 then dbms_output.put_line('*** hint: log buffer value is rather low!'); end if;

end; /

---------------list all supported init.ora parameters----------------------------select a.ksppinm name, b.ksppstvl value, b.ksppstdf isdefault, decode(a.ksppity, 1, 'boolean', 2, 'string', 3, 'number', 4, 'file', a.ksppity) type, a.ksppdesc description from sys.x$ksppi a, sys.x$ksppcv b where a.indx = b.indx and a.ksppinm not like '\_%' escape '\' order by name / --------------list all un-supported init.ora parameters-------------------------select a.ksppinm name, b.ksppstvl value, b.ksppstdf isdefault, decode(a.ksppity, 1, 'boolean', 2, 'string', 3, 'number', 4, 'file', a.ksppity) type, a.ksppdesc description from sys.x$ksppi a, sys.x$ksppcv b where a.indx = b.indx and a.ksppinm like '\_%' escape '\' order by name / ---------------active (in progress) transactionsrollback seg-----------------col col col col col tti

name format a8 username format a8 osuser format a8 start_time format a17 status format a12 'active transactions'

select username, terminal, osuser, t.start_time, r.name, t.used_ublk "rollb blks", decode(t.space, 'yes', 'space tx', decode(t.recursive, 'yes', 'recursive tx', decode(t.noundo, 'yes', 'no undo tx', t.status) )) status from sys.v_$transaction t, sys.v_$rollname r, sys.v_$session s where t.xidusn = r.usn and t.ses_addr = s.saddr / ----------------------------------------------------------------------rem purpose: display rollback segment statistics rem ----------------------------------------------------------------------column column column column

"rollback segment" "size (kb)" "gets" "waits"

format format format format

a16 9,999,999 999,999,990 9,999,990

column "% waits" column "# shrinks" column "# extends"

format 90.00 format 999,990 format 999,990

prompt prompt rollback segment statistics... select rn.name "rollback segment", rs.rssize/1024 "size (kb)", rs.gets "gets", rs.waits "waits", (rs.waits/rs.gets)*100 "% waits", rs.shrinks "# shrinks", rs.extends "# extends" from sys.v_$rollname rn, sys.v_$rollstat rs where rn.usn = rs.usn / show sga show parameters area_size set pages 999; column pga_size format 999,999,999 select 2048576+a.value+b.value pga_size from v$parameter a,v$parameter b where a.name = 'sort_area_size' and b.name = 'hash_area_size';

column pga_size format 999,999,999 accept hwm number prompt 'enter the high-water mark of connected users: ' select 150*(2048576+a.value+b.value) pga_size from v$parameter a, v$parameter b where a.name = 'sort_area_size' and b.name = 'hash_area_size';

================================================================================== =============== rem database health monitoring script. rem rem segments max extents & current extent comparison rem set line 180 set pagesize 10000 set feedback off prompt col time format a50 heading "system time" select to_char(sysdate,'dd-mon-yyyy:hh24:mi:ss') time from dual; prompt prompt prompt

rem**-------------objects reaching max extents-----------** col segment_name format a40 heading "object name" col max_extents format 9999999999 heading "max extents" col curr_extents format 99999999999 heading "curent extents" select a.segment_name,a.max_extents,b.curr_extents from dba_segments a,(select segment_name,max(extent_id) curr_extents from dba_extents group by segment_name) b where a.segment_name = b.segment_name and (a.max_extents - b.curr_extents) create empcopy > using select * from emp ---------------- to convert a tablespace to locallymanaged------------------------------sys.dbms_space_admin .tablespace_migrate_to_local('users') ------------ take a tablespace locally-managed back to dictionary management--------------sys.dbms_space_admin .tablespace_migrate_from_local('users')

##################duplicate rows###################################################### delete from personnelwhere rowid in (select p2.rowid from personnel p2, (select p3.id, p3.name, max(p3.rowid) max_rowid from personnel p3 group by p3.id, p3.name) p4 where p2.rowid p4.max_rowid and p2.id=p4.id and p2.name=p4.name ); ######################who locked whom################################################################# 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

blocker sid 'isblocking' blockee sid ------------------------------ ---------- ---------------------------------------------------------system 17 is blocking perfstat 11

2. look for the object id, row wait file#, row wait block# and row wait row# these info will be using in dbms_rowid select sid,row_wait_file#,row_wait_block#,row_wait_row# from v$session where sid=11; --- use blockee sid; sid row_wait_obj# row_wait_file# row_wait_block# row_wait_row# ---------- ----------------------------- ------------------------------------------------------------ ----------------------------11 26435 11923 5

3

3. look for object name - join dba_objects (object_id) in step 2 select object_type, owner, object_namefrom dba_objectswhere object_id=26435;object_type owner -------------------------------------------------------------------------------------table perfstat t

object_name

4. use dbms_rowid to find out the row. select * from t where rowid = dbms_rowid.rowid_create(1,26435,3,11923,5); c1 c2 ---------- ------------

6

f

this is the row which is locked by system where perfstat user trying to update ########################################################################### alter session set sql_trace = true; select count(*) from dual; alter session set sql_trace = false; the resulting trace file will be located in the user_dump_dest directory. this can then be interpreted using tkprof at the commmand prompt as follows: tkprof explain=user/password@service table=sys.plan_table ################################################################### convert a long raw column into a clob or blob or bfile. alter table old_table modify ( c clob ); create table new_table as select c1, c2, to_lob(c3) from old_table;

rename table current_db.tbl_name to other_db.tbl_name; alter table test rename column col1 to id; alter table x alter column c1 varchar(80) alter table table_name modify ( column_name );

varchar2(30)

export rows redefine table and import. alter table ntiac_table add (publication_date_copy date) ; update ntiac_table set publication_date_copy=publication_date; alter table ntiac_table set unused column publication_date;

alter table ntiac_table drop unused columns; alter table ntiac_table rename column publication_date_copy to publication_date;

..........generate 1 to 64 in words...................... select upper(to_char(to_date(rownum,'j'),'jsp')) text from (select 1 from dual group by cube (1,2,3,4,5,6)) my [email protected]: ramu11 srinivasrao pilli: hi..find here video and photo coverage of johndoerr's visit........ \\pjindia\backupofdevelopement\copy of johndoerrvisit

http://economictimes.indiatimes.com/articleshow/msid-1384411,curpg-1.cms swami vivekananda's words.." a country's fate is in the hands of youth"...hatsoff 2 the guys..'n gudluks 2 them..

delete from personnelwhere rowid in (select p2.rowid from personnel p2, (select p3.id, p3.name, max(p3.rowid) max_rowid from personnel p3 group by p3.id, p3.name) p4 where p2.rowid p4.max_rowid and p2.id=p4.id and p2.name=p4.name ); select * from a where a.name in (select * from b where b.id=a.id); select emp_id from emp where emp_id in ( select emp_id from dep ) ;

select table_name,constraint_name,constraint_type from user_constraints c where constraint_type='p' and c.table_name in(select c1.table_name from user_constraints c1 where c1.constraint_type='p') / ################################################### buffer hit ratio of sql statement### select substr(a.sid,1,5) "sid", substr(a.username,1,10) "user", substr(a.osuser,1,23) user_nm, --substr(a.machine,1,23) machine_nm, substr(a.program,1,17) program, b.consistent_gets "consgets", b.block_gets "blockgets",

b.physical_reads "physreads", 100 * round((b.consistent_gets + b.block_gets - b.physical_reads) / (b.consistent_gets + b.block_gets),3) hitratio from v$session a, v$sess_io b where a.sid = b.sid and (b.consistent_gets + b.block_gets) > 0 and a.username is not null and a.username not in ('sys','system') order by hitratio asc / ##########################################################drop schema################################## select 'alter table '||table_name||' disable constraint '||constraint_name||';' from user_constraints where constraint_type = 'r' where table_name in('first table','sec table') / select 'prompt truncate cluster '||user||'.'||object_name||' in progress...'|| chr(10)|| 'truncate '||object_type||' '||object_name||';' from user_objects where object_type = 'cluster' order by 1 / select 'prompt truncate table '||user||'.'||table_name||' in progress...'|| chr(10)|| 'truncate table '||table_name||';' from user_tables where cluster_name is null order by 1 / select 'prompt drop '||object_type||' '||user||'.'||object_name||' in progress...'||chr(10)|| 'drop '||object_type||' '||object_name|| decode(object_type,'table',' cascade constraint;',';') from user_objects where object_type not in ('package body','index','cluster','trigger','lob') and subobject_name is null order by 1 / select 'prompt drop '||object_type||' '||user||'.'||object_name||' in progress...'||chr(10)|| 'drop '||object_type||' '||object_name||';' from user_objects where object_type = 'cluster' order by 1 / steve @pcjmso> select 'create public synonym '||tname||' for steve.'||tname||';' from tab where tabtype='table' and tname not like 'satya%' and tname not like 'vdk%' and tname not like 'smp%' and tname not like 'vbz%' and tname not like 'vmq%'and tname not like 'epc%'

steve @pcjmso> select 'grant select on '||tname||' to public;' from tab where tabtype='table' and tname not like 'satya%' and tname not like 'vdk%' and tname not like 'smp%' and tname not like 'vbz%' and tname not like 'vmq%'and tname not like 'epc%' --------------------------------------------------------------------------------------------------create or replace procedure fast_insert as vcount number; vsysdate date; type books_aat is table of old_cardio_set%rowtype index by pls_integer; books books_aat; begin select /*+ rule */ d.* bulk collect into books from cardio_workout a, cardio_activity b, cardio_activity_changed c, old_cardio_set d where a.workout_nr = b.workout_nr and b.activity_nr = c.new_activity_nr and c.change_nr = d.change_nr and a.person_nr =123456; forall book_index in books.first .. books.last insert into old_cardio_set values books(book_index); vcount := sql%rowcount; end; / __________________________________________________________________________________ ___________________