Click Here for online Oracle script downloads
The following scripts are provided in native SQL*Plus format. Rather than
having to install a complex GUI tool. these scripts will provide useful Oracle
information from any SQL*Plus prompt. That makes these scripts ideal for
Oracle off-hours support.
Free Scripts
The following Oracle scripts are available for your use at no charge. They
are certified for use in Oracle7, Oracle8 and Oracle8i.
Tablespace and file scripts
Table and Index scripts
User & Security scripts
| adduser.sql |
This is a script that will accept the userid and
password and generate the appropriate create user syntax. This script will
add an entry to the DBA_TABLES view, and includes setting the default
tablespace and the temporary tablespace.
--******************************************
--
-- adduser.sql
-- Script to add a user – accepts userid & password
--
-- Copyright © 2001 by Donald K. Burleson
--
--******************************************
create user &&1 identified by &&2;
alter user &&1 default tablespace users;
alter user &&1 temporary tablespace temp;
grant connect to &&1; |
| audit.sql |
This remarkable script twill quickly check any Oracle9i
database for possible security loopholes.
|
| allprivs.sql |
|
| |
|
| |
|
| |
|
Administration Scripts
| analyze.ksh |
This is a korn shell script that will accept the $ORACLE_SID
as an argument. The script will generate the "alter table xxx estimate
statistics" and the "alter index compute statistics" commands for all tables
and indexes are that not system-owned. The script estimates table
statistics for the cost-based SQL optimizer abd uses a sample size of 5000
rows. |
| bhr_keep.sql |
This script will interrogate the v$buffer_pool_statistics
view and display the buffer hit ration for the KEEP pool. Since the
number of data blocks in the KEEP pool should equal sum(blocks) for all
tables assigned to the KEEP pool, the ration should always be 100%. |
|
alllocks.sql |
This script will add the dba_blockers and dba_waiters
views to the Oracle data dictionary (as the SYS user), and then run queries
to show all locks in the database. |
| chained_rows.sql |
This script will check DBA_TABLES and inspect the chain_cnt
column as a percentage of the number of rows in the table. This script
requires that the table be analyzed to get current statistics. There are two
reports, one for tables with RAW datatypes and another for tables without
RAW datatypes. This is because table with RAW columns may chain because of
their row length being longer than the db_block_size. |
| check_env.sql |
This script is useful for quickly inspecting a foreign
database. It checks for LONG columns that can be converted to BLOB or
CLOB datatypes. It also counts tables, snapshots, database links, the
multithreaded server (MTS), multiple freelists in tables and indexes,
multiple freelist groups in tables and indexes, tables with chained rows,
tables with a default degree of parallelism > 1, important SGA parameters,
high-use SQL statements in the library cache, redo log space requests and
redo log switch frequency. |
| cr_rbs.sql |
|
| |
|
| fnd_dups.sql |
A script to locate duplicate rows within a table. |
| clust_factor.sql |
Show the clustering factor for an index against it's target
table. Clock here for details on using clustering_factor to reduce
table I/O for index range scans. |
| get_init.sql |
Display all Oracle init.ora parameters. |
| |
|
| get_vsd.sql |
|
| hash_area.sql |
This script will generate the ALTER SESSION syntax to
appropriately set the hash_area_size for SQL that performs a hash join. |
| |
|
| |
|
| |
|
| |
|
| |
|
Tuning scripts
| bif.sql |
This script will check the v$sqlarea and display all SQL
that uses built-in functions. This is especially useful for finding
opportunities for adding function-based indexes to remove unnecessary
full-table scans. |
| coe_xplain.sql |
This is the detailed SQL analysis script from Oracle
corporation. |
| display_sql.sql |
|
| find_literal_sql.sql |
|
| get_sql.sql |
Display SQL from the library cache for all SQL statements
that contain a specific string. |
| high_ver_sql.sql |
|
| |
|
| |
|
| |
|
| |
|
| |
|
| |
|
| |
|
| |
|
| |
|
Unlike expensive tools that require a graphical interface,
these flexible scripts are ready to execute from any SAQL*Plus prompt. They can
be easily used via telephone dial-up allowing remote DBA support from anywhere
in the world.
File and tablespace utilities
Tsfree – This script joins the DBA_TABLEPOSCAES view with
DBA_?? To produce a wonderful report that shows the total free space within a
tablespace as well as the largest free segment. This script is indispensable
for the on-call DBA will needs to know tablespace details via dial-up
connections.
Table and object Utilities
Tblexts.sql – This extremely useful script will show
critical details about any table and inc;ludes the following: table_owner,
table_name, num_rows, chain_cnt, pct_free, pct_used, .
SQL Tuning Suite – This revolutionary code explains all of
the SQL in your library cache and creates several critical reports:
Full-table scan report – This report will tell
you those tables that experience full-table scans, and how often. The
information from this report is critical for placing table in the KEEP pool or
for implementing Oracle parallel query.
Index range scan report – This report tells you which
indexes are the most popular, and provides a breakdown of total index usage.
This report can be used to predict the performance benefit of adding a new
index, and co locate un-used indexes for deletion.
Chained row alert report – This script will quickly alert
you to those tables that contain chained rows, and those that would benefit from
reorganization. This report interrogates the DBA_TABLES view and relies on the
cost-based optimizer statistics.
Locate low cardinality columns for conversion to bitmap
indexes – This set of scripts uses the DBA_TAB-COLUMNS view and report on the
number of distinct values within any table.
Security auditing
Audit.sql – This script interrogates DBA_ROLES, DBA_ROLE_PRIVES
and find inappropriate system and object privileges. These include:
Users granted DBA with ADMIN privilege:
|
