Administration

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

Show all the tablespaces for a given database

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_ndx.sql

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

Estimates the size requirements for a given index

size_tbl.sql

Estimates the size requirements for a given table

sz_tbl_1.sql

Estimates the size requirements for a given table

tab_ind.sql

display information about tablespace contents (indexes)

tabs_tab.sql

display information about tablespace contents (tables)

tbl_blks.sql

Table Usage Parameters

tbl_util.sql

Table Utilization

tblsuse.sql

Summarize tablespace usage by segment type and user

tf2.sql

Database Freespace Summary

tot_fs01.sql

Total Free Space Available

touch_invalid_procedu

Touch all (recompile) all invalid procedures

touch_invalid_view

Touch all (recompile) all invalid views

tsquota.sql

REPORT: TSQUOTA - DBA TS Quotas Report

tssusage.sql

Tablespace Per Segment Type Per User Summary

tssuser.sql

REPORT:  Database Tablespace per User Summary

wake_up_smon.sql

Utility to wake-up SMON and coalesce temp

Disclaimer: User assumes all responsibility; please understand the objective of the script and use it accordingly.  If you do not see a script for a specific function please let me know via eMail and I can probably 'whip' one up in a matter of minutes...

 

[Home] [Professional] [Vacations] [All About  Rick]

Have a question or comment... please email me at Rick@Kupcunas.com