DBAToolZ - Scripts ( Favorite Scripts ) DBAToolZ Home
 (Last updated 05/21/2003)
Home     Download     Forums     SQL Scripts     Book Review     Links     Feedback


Script Name Description

a_cm_long_reqs.sql

	Prints concurrent requests that ran for over "X" minutes.
	Calls analyzereq.sql that can analyze conc request of your choice.

a_ses_by_pid.sql

	** SHOULD BE RUN AS APPS **
	Reports the following:
	----------------------
	- session detail from v$session, v$process
	   by PID (from backend).
	- any running requests from apps.FND_CONCURRENT_REQUESTS
	   output includes REQ_ID, Phase, Status, O-PID, A-PID, OUT File
	- calls analyzereq.sql to print details of a REQ_ID
	NOTE:
	   to link request id to a session use one of the following
	      o fnd_concurrent_requests.oracle_session_id = v$session.audsid
	      o fnd_concurrent_requests.oracle_process_id = v$process.spid
	        (v$process.addr = v$session.paddr)
	      o fnd_concurrent_requests.os_process_id     = v$session.process
    

a_user_ses_R11i.sql

	Displays session information
	for Oracle Applications R11i
	!! Will only work if AUDIT is set for USER Level !!
	To do this set:
	navigator(Sys Admin Resp)
	 -Profile
	  -System
	   -Find System Profile Values (FORM)
	    search>
	    -System Profile Values (FORM)
	     save>

	WARNING:
	-------
	This script ** MUST BE RAN as SYSTEM **

	If you dont set AUDIT level to USER
	then PACKAGE APPLSYS.FND_SIGNON will not
	populate table FND_LOGINS with information
	about user sessions.
	HERE is that package body:
	   begin
	       SELECT P.PID, S.PROCESS, P.SERIAL#, P.SPID
	       INTO local_pid, local_spid, local_serial#
	       ,    local_process_spid
	       FROM V$PROCESS P, V$SESSION S
	       WHERE S.AUDSID = USERENV(`SESSIONID`)
	       AND S.PADDR = P.ADDR,
	   exception
	   when no_data_found then
	       local_pid := null,
	       local_spid := null,
	   end,

	   -- Insert record
           INSERT INTO FND_LOGINS
           (LOGIN_ID, USER_ID, START_TIME, TERMINAL_ID,
               LOGIN_NAME, PID, SPID, SESSION_NUMBER, SERIAL#,
               PROCESS_SPID, LOGIN_TYPE)
           VALUES(audit_user.login_id, audit_user.user_id, SYSDATE,
                  audit_user.terminal_id, audit_user.login_name,
                  local_pid, local_spid, audit_user.session_number,
                  local_serial#, local_process_spid, `FORM`),

analyzereq.sql

--
--      analyzereq.sql
--      Analyze a concurrent request
--
--      USAGE: sqlplus apps_user/apps_passwd @analyzereq request_id
--      EX:    sqlplus apps/apps @analyzereq 304504
--

c_clone_grants.sql

	This script will "clone" SYS, OBJECT and ROLE level privs for any schema
	by creating a SQL script that can be used to copy these privs to another
	schema within the same database or a different database.
    

c_ind_rebuild.sql

	This script will rebuild indexes of your choice

c_kill.sql

	KILLS user session (prompts for SID and SERIAL#)

c_recomp_v7-8.sql

	Recompile all invalid objects.

c_recomp_v8i.sql

	Recompile all invalid objects including JAVA and TYPE.
	Allows compile of a single schema or all schemas.

s_db_ext2.sql

	Reports segments close to their maxextents parameter

s_db_segs1.sql

	Reports largest segment types by owner

s_db_segs2.sql

	USE s_db_segs3.sql for a detailed report of segments

	Reports SUM(bytes) for all segments by owner groupping them by
	the current size from dba_segments.

	There is a theory by which you only need 3 types
	of tablespaces:
	  160k (SMALL)
	  5MBm (MEDIUM)
	  160MB (LARGE)
	If you follow this theory you can use this script
	to "place" your segments in the "right" tablespace:
	  1. 160K ts (segs < 5MB)
	  2. 5MB ts (segs < 160MB)
	  3. 160MB ts (segs > 160MB)

s_db_segs3.sql

	Reports segments by owner groupping them by
	the current size from dba_segments.

	There is a theory by which you only need 3 types
	of tablespaces:
	  160k (SMALL)
	  5MBm (MEDIUM)
	  160MB (LARGE)
	If you follow this theory you can use this script
	to "place" your segments in the "right" tablespace:
	  1. 160K ts (segs < 5MB)
	  2. 5MB ts (segs < 160MB)
	  3. 160MB ts (segs > 160MB)

s_db_segs_alter.sql

	Alters segment`s storage parameters (NEXT  and PCTINCREASE 0)
	where  is derived based on the current size from dba_segments:

	There is a theory by which you only need 3 types
	of tablespaces:
	  160k (SMALL)
	  5MBm (MEDIUM)
	  160MB (LARGE)
	If you follow this theory you can use this script
	to "size" your segment`s future extents for the "right" tablespace:
	  1. 160K ts (segs < 5MB)
	  2. 5MB ts (segs < 160MB)
	  3. 160MB ts (segs > 160MB)

s_db_segs_outbound.sql

	Reports segments that fit the following criteria:
	-------------------------------------------------
	   a)	(160K =< SEG < 5M) NEXT < 160K
	   b)	(5M =< SEG < 160M) NEXT < 5M
	   c)	(160M =< SEG ) NEXT < 160M
	The report should be analyzed and segments can be altered
	by using the following script "s_db_segs_alter.sql"
    

s_db_sorts.sql

	Reports ALL sorts in the database
	Works with 8i TEMP tablespaces

s_dbf_ai.sql

     VERY HELPFULL TO BALANCE DBF`s on FILE SYSTEMS
     This script displays just about all you need to know
     about datafiles, storage, tablespaces and autoextend

s_dbf_io.sql

	This script will show IO by mount point

s_locked_obj.sql

	            *** QUICK ***
	Reports the following:
	-  object locks from V$LOCKED_OBJECT
	   using PL/SQL loops since join of DBA_OBJECTS
	   and V$LOCKED_OBJECT is extremly slow
	- blocked objects from V$LOCK and SYS.OBJ$
	- blocked sessions from V$LOCK

s_sga_db_buffer.sql

	Displays Hit Ratio for DB_BUFFER
	HIT RATIO should be > 90%
     Refer to Metalink Doc ID: 33883.1
          STATISTIC "cache hit ratio" - Reference Note

s_sga_lib_cache_lock.sql

	Reports library cache locks.  Many times in heavy development
	environment PL/SQL code get`s compiled while someone else is
	using it.  This creates locks in library cache which can be
	very hard to trace.   I would typically check v$session_wait
	to see if there	are any waits for "enque", most of the time it`s
	library	cache lock especially when other locks are not present.
	--
	The next step would be to find out which package waiting
	session was trying to compile or execute and run this script
	supplying this package name.  When you get the output of this
	script you can kill sessions that are causing library cache lock.

s_sga_pga_used.sql

	Reports SID`s PGA usage
	some platforms have a limitation on maximum
	PGA you can have (32bit AIX - 256m)
	If SID`s PGA usage goes above this maximum
	session can get disconnected with the following error:
	  "ORA-3113: end-of-file on communication channel"

s_tab_ext.sql

	Lists tables/Indexes with > 50 extents

s_tabsp.sql

	Lists freespace by tablespace

s_tabsp_detail.sql

	This script is from Kevin Loney "ORACLE8 DBA Handbook"
	----------------------------------------------------------------------
	Generates a mapping of the space usage
	(free space vs used) in a tablespace. It graphically
	shows segment and free space fragmentation.

	After running this script:
	if a tablespace has too many "free space gaps" you should:
	a) let SMON process coalesce extents by setting default PCTINCREASE to
	   nonzero value
	OR
	b) manually coalesce the free extents of the tablespace:
	   SQL> alter tablespace  coalesce,

s_tabsp_dif.sql

	---------------------------------------------------------
	Compares tablespace differences between current database
	and any other database.  Reports segments with different
	number of extents from these tablespaces.
	---------------------------------------------------------
	(Creates DB LINK and temp table)

s_tabsp_frag2.sql

	Displays count of empty "holes" in every
	tablespace.  When you see this count reaching
	thousands I whould say it`s time to reorg TS
	or at least try to COALESCE ...

s_tabsp_top_10_segs.sql

	Displays top 10 biggest segments by tablespace
	WARNING:
	Uses ORDER BY with INLINE VIEWS not supported for <8i

s_user_long_ops.sql

	Reports long running processes

s_user_sql.sql

	This script reports SQL TEXT from v$sqltext
    

s_waiters.sql

	This script reports sessions that are currently waiting
	output DECODED locking information from v$lock and v$sessions
    

s_waitt.sql

	This script reports details from v$session_wait
	where wait_time = 0
	and seconds_in_wait > 
    

u_autotrace.sql

	Creates PLAN table by running $ORACLE_HOME/rdbms/admin/utlxplan.sql
	sets AUTOTRACE ON (traceonly explain)

u_tab_data.sql

	Display table row in "vertical" format

DBAToolZ Home | Contact Us
Copyright© 2001 DBAToolZ.com All Rights Reserved.