Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles
New Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 


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:

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright © 1996 -  2016

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.