Below are Oracle Scripts to administer an Oracle Database... feel free to use the scipts listed, all I ask is that you retain the ownership block at the top of each script.
Script Name
Purpose
all_cltr.sql
show all the clusters for a given database
all_cons.sql
show al the constraints for a given database
all_link.sql
show all the links for a given database
all_ndxs.sql
Show the indexes associated with all tables
all_ndx1.sql
Show the indexes associated with all tables(statistics)
all_quot.sql
Show the tablespace quotas for all users
all_role.sql
Show all the roles for a given database
all_seqs.sql
Show all the sequences for a given database
all_sizing.sql
Show the size of all table/indexes
all_snap.sql
Show all the snapshots for a given database
all_stats.sql
Show database statistics
all_syns.sql
Show all the synonyms for a given database
all_stat.sql
Show the analyze statistics of all tables
all_tbls.sql
Show the table information for all tables
all_tbl1.sql
Show the table information for a table(statistics)
all_trgr.sql
Show all the triggers for a given database
all_tsps1.sql
Show all the tablespaces for a given database
all_tsps.sql
all_users.sql
Show all oracle users
all_view.sql
Dump all user defined views to a file
allvsact.sql
Report: Actual vs. Allocated Storage Report
analyze1.sql
Statistics of a table; empty blocks and leaf blocks
analyze2.sql
Statistics of a table, indexes; empty blocks and leaf blocks
alter_extents.sql
SQL to build SQL - alter extents
alter_index.sql
SQL to build SQL - alter index parameters
alter_table.sql
SQL to build SQL - alter table parameters
alter_tablespace.sql
SQL to build SQL - alter tablespace parameters
alter_trigger_disable
SQL to build SQL - disable triggers
alter_users.sql
SQL to build SQL - alter user profiles
backup.sql
Datafile backup
bld_cnt.sql
SQL to build SQL to count(*) from all tables
bld_compile_pak.sql
SQL to buils SQL to compile all invalid packages
bld_coal.sql
SQL to build SQL to coalesce all tablespaces
bld_con.sql
SQL to build SQL to generate constraints of all tables
bld_dump.sql
SQL to build SQL to select * from all tables
bld_grnt.sql
SQL to build SQL to generate grant of all tables
bld_my_ndxs.sql
Build the list of indexes created by me....
bld_ndx.sql
SQL to build SQL to rebuild indexes of all tables
bld_ndx3a.sql
Script to rebuild the indexes tied to a table
bld_role.sql
SQL to build SQL to rebuild user defined roles
bld_rollback.sql
SQL to rebuild the rollback segments
bld_seqs.sql
SQL to build SQL to rebuild sequences
bld_slct.sql
SQL to build SQL to build select statements
bld_tbl.sql
SQL to build SQL to rebuild a specific table
bld_trun.sql
SQL to build SQL to truncate tables
bld_usrs.sql
SQL to build SQL to rebuild the create user command
cr8_rol.sql
SQL to build SQL to recreate DB roles
db_syn01.sql
Display information about all of your own synonyms
db_usr01.sql
Display information about all the people logged on
dba_tool.sh
Detailed statistical information about database
dbaext.sql
REPORT: DBAEXT - Table and Index Extents(w/o system)
dbaextsy.sql
REPORT: DBAEXTSYS - Table and Index Extents
dbafiles.sql
REPORT: DBAFILES - Data Files Report
dbaind.sql
REPORT: DBAIND - Index Column Report
dbaind2.sql
REPORT: DBAIND2 - Index Column Report
dbaindec.sql
REPORT: DBAINDEC - Table and Index Extents
dbaindmf.sql
REPORT: DBAINDMFG - Index Column Report
dbausers.sql
REPORT: DBAUSERS - DBA User Report
dcstats.sql
REPORT: DCSTATS - Dictionary Cache Stats
describe.sql
Script to describe "all" tables
diskio.sql
active data files
dsc_dba1.sql
Oracle Database Utility Procedure
dyn1alt.sql
Script to backup the tablespaces
ext.sql
Count Of Extents For Database Objects
extent_1.sql
REPORT: Segments that exceed extent cnt
extent_2.sql
Extent and Tablespace Report:
file_spa.sql
check various database files
file_sz.sql
Print the size of the AP (financial) files
fileio_1.sql
detailed data file I/O statistics (BM)
fragmntd.sql
Fragmented TableSpace
get_ndxs.sql
show all indexes associated for a given table
get_stmt.sql
Show what is executing in the shared pool area
get_tbls.sql
show the attributes associated with a table
gr_roles.sql
REPORT: System Privlages Granted to Query Roles
idx_stat.sql
REPORT: Script for ALL_INDEXES from index
imp_vrfy.sql
Show statistics for all dba objects
jobs_2_rollbacks.sql
Show jobs tied to a rollback segment
jobs_2_rollbacks2.sql
Show jobs tied to a rollback segment(2)
kill_user.sql
Script to kill users
length01.sql
Determine the data distribution of an attribute within a table
locking1.sql
Defines who have locks and the type thereof
locking2.sql
Determines who (sessions) have what types of locks
locking3.sql
Defines sessions in a locking contention situation
locking4.sql
Ultimate locking script
map_tsps.sql
Map tablespace segments
map_tsps_free.sql
Map free space in a tablespace
map_tsps_t.sql
Map tablespace objects
max_read.sql
REPORT: Sessions With The Highest I/O
max_uga.sql
session uga memory max
morning.sql
SQL driver to dump stats
oi_daily.sql
REPORT: Orders imported from MOE system
oraenv.sh
UNIX script to see what Oracle tools are installed
oo_daily.sql
REPORT: Show the number of orders compleated
priv_col.sql
show all the object privilege (column level)
priv_dba.sql
show all the object privilege
priv_rol.sql
show all the object privilege(role level)
priv_ses.sql
show all the object privilege(session level)
priv_sys.sql
show all the object privilege(system level)
priv_tab.sql
show all the object privilege(table level)
proc_001.sql
script to show what activity is going on within database
qry_scrp.sql
Produce the report of those users accessing the database
rbsegrpt.sql
REPORT: Rollback Segment Report
recsblks.sql
Average records per block
redo_swt.sql
Redo log file statistics
rlbk_seg.sql
show the rollback segments, status and size for a database
rollback_wrap_time.sql
Rollback wrap time
rows2blk.sql
Show how many rows-per-block are actually being stored
segsize.sql
Segment Sizing
sess.sql
Shows the stats for a given session
sga_size.sql
Should the sga be increased?
shared_p.sql
Mathematical formulae to compute the shared_pool_size
show_backup_file_stat
Show datafile status
show_con.sql
Show the constraints on an object
show_con2.sql
Show external constraints on an object
show_depends.sql
Show external dependencies on an object
show_lnk.sql
Shows the data for a given link
show_ndx.sql
Shows the data for a given index
show_obj.sql
Shows the data for a given object
show_stat_dt.sql
Show the date the statistics were created for a table
show_stat_type.sql
Show the type of statistics for a specific table
show_syn.sql
Shows the data for a given synonym
show_trg.sql
Shows the trigger data for a given table
show_tsp.sql
Shows the data for a given tablespace
show_vw.sql
Shows the data for a given view
shrink_rollbacks.sql
Shrink unused rollback segments
size_ndx.sql
Reports on the size of each index in bytes/blocks
size_obj.sql
Show size of table/indexes
size_tab.sql
Reports on the size of each table in bytes/blocks
Size occupied by an index within the current database
sortscan.sql
Scan for sort types and numbers
space_01.sql
show space problems
stats.sql
Several statistical reports; hit ratio information
swdr.sql
Load session wait information
swl.sql
Load session wait information for terminal
swsr.sql
Session Wait Summary Report
sysalloc.sql
REPORT: SYSALLOC - System Space Allocated
sysfree.sql
REPORT: SYSFREE - TS Free Space Report
sz_ndx_1.sql
