Oracle Advanced Performance Tuning Scripts

Miscellaneous Scripts

su.sql

This script allows a user with appropriate privileges to connect as another user without knowing their password.
Download script for: 8.0 or 8.1

set_prompt.sql

This script can be called from your login.sql or glogin.sql files to set the prompt in SQL*Plus so that it is preceded by a line showing the current user name and instance, as follows:
STEVE @ PROD:
SQL> 
Download script for: 8.0 or 8.1

big_sort_area.sql

This script can be used to set the sort_area_size for the current session to a suitably large value prior performing large sorts. The sort_area_size is set to a percentage of the size of the SGA (10% by default), unless it was already larger than that.
Download script for: 7.3, 8.0 or 8.1

primes.sql

It is often necessary to choose a prime number for the size of a parameter, or hash cluster. This simple script lists the prime numbers in the range of interest.
Download script for: 7.3, 8.0 or 8.1

lock_element_lwm.sql

In Oracle parallel server, if fine-grained locking is being used, it is normally desirable to have free lock elements at all times (except in VLM environments). This script shows the low water mark of the lock element free list in release 8.0 and beyond.
Download script for: 8.0 or 8.1

post_smon.sql

This script posts the SMON process causing it to cleanup stray temporary segments. The 7.3/8.0 version is Unix specific, and the user must be able to connect internal without giving a password. The 8.1 version is generic, and requires that the user be connected as SYSDBA.
Download script for: 7.3 or 8.0, 8.1

system_space_invaders.sql

This script identifies users who have SYSTEM as their DEFAULT or TEMPORARY tablespace. No user, other than the SYS user, should have SYSTEM as their DEFAULT tablespace; and no user, not even SYS, should have SYSTEM as their TEMPORARY tablespace.
Download script for: 7.3, 8.0 or 8.1

schema_objects.sql

This script counts the objects of each type owned by each schema. It can be useful for getting a quick overview of a database.
Download script for: 7.3, 8.0 or 8.1

controlfile_size.sql

This script returns the size of the controlfile in bytes.
Download script for: 8.0 or 8.1

uptime.sql

This is a very simple script to check how long the instance has been up.
Download script for: 8.0 or 8.1

executing_packages.sql

This script lists the packages (and other stored code objects) that are currently being executed, and the SIDs of the executing sessions.
Download script for: 8.0 or 8.1

db_check.sh

This script may be used interactively or programmatically to check that

a listener is running
all instances are available
instances are not in restricted session mode
auto-archiving is enabled in archivelog mode
archiver processes are not stuck
tablespaces are not in hot backup mode

Download script for: 7.3 to 8.1