SQL LIBRARY
Release 23-Mar-98, Copyright (c) 1998 Mark Lang


IMPORTANT!

If you are upgraded from an earlier release always re-run
the installation (@install) against all databases you are
going to use the library against.  This will update you
with the latest package(s) and help table(s).

See RELNOTES.TXT for latest changes to scripts.


CONTENTS

0.      LICENSE AGREEMENT

1.      INTRODUCTION
1.1       BENEFITS
1.2       HOW THE LIBRARY WAS DESIGNED
1.3       FEATURES

2.      INSTALLATION
2.1       REQUIREMENTS
2.2       HOW TO INSTALL

3.      USING THE LIBRARY
3.1       SPOOLED FILES
3.2       ALGORITHMS
3.3       DBA vs NON-DBA USERS
3.4       CUSTOMIZATION
3.5       EXAMPLES

4.      SCRIPT DOCUMENTATION
4.1       ONLINE HELP
4.2       SCRIPT HEADERS

5.      NOTES
5.1       RESTRICTIONS
5.2       SUPPORT


APPENDIXES

A.      ALPHABETICAL INDEX OF SCRIPTS
B.      ABOUT THE AUTHOR



0.      LICENSE AGREEMENT

This software is absolutely free!  But please read below...

This software and the accompanying files are distributed "as is"
and without warranties as to performance, accuracy, or any
other warranties whether expressed or implied.  The author is not
responsible for any costs or damages incurred, whether incidental
or consequential, by the use or misuse of this software.

Any code distributed with this library which is in "wrapped",
"byte", "binary", or some other compiled or object format may not
be reverse engineered.  You may however, use any source code
or scripts supplied however you wish (copy, modify, customize, etc.)
as long as it is for your personal use only, and not used to create
a similar or competing product, and is not integrated into any
other product which is distributed or sold without my express
written consent.

You may redistribute this software electronically to others
provided it is kept in its standalone original form, intact
and unmodified, and at no cost of any kind.

By downloading this software, you are agreeing to this license.



1.      INTRODUCTION

This document provides an overview of the SQL script library that
I am distributing to the public domain.  This library is a
collection of the most useful scripts I have developed over the
past 5 years as an Oracle DBA.  This release contains
approximately 270+ scripts in 20 subject areas.  This is an initial
"beta" release--I am still refining and reorganizing things.
My plan is to continue to update and refine this library on a
continuous (at least monthly) basis and make it accessible to the
public domain over the internet.  The latest copy can be downloaded
from

	http://www.geocities.com/SiliconValley/Lakes/1261


Some of the subject areas listed below I have
only begun to explore.  Also, I have only begun to develop scripts
that cover the new functionality of Oracle8, as well as migration
from 7 to 8.  I am also developing in other areas, such as PL/SQL
and Java, which I will would like to eventually include and bundle
with this library.


1.1     BENEFITS

I use this library every single day as a DBA.  It has saved me
literally hundreds of hours of work over the years, and helped me
diagnose the most difficult problems quickly and easily.  These
scripts are designed to complement Oracle's management tools
such as Enterprise Manager.  You quickly will find that there
are many things that you can do in a script that OEM (as nice as
it is) just can't do.

The benefits I get every day has been worth the investment I
have made, and continue to make, in developing these scripts.  Now
you can get the same benefits for free!  Especially if you are a
beginning / intermediate DBA and have not had the time to develop
a script library of your own.  If you are already an experienced
DBA with you own library, you may find something in here that you
don't already have.

I imagine that these scripts aren't anything an experienced
Oracle person couldn't develop themselves given the time--but since
I've already done it you might as well take advantage of it!


1.2     HOW THE LIBRARY WAS DESIGNED

Here are some basic philosophies I've used in organizing the library:

- Based functionality on well known "best-practices" and/or
  documented Oracle methods
- Be able to dissect the data dictionary in as many, and the most
  meaningful, ways possible
- Minimize the number of scripts and parameters while maximizing
  what each script can do
- Fit as much output on the screen as possible
- Be consistent & organized, but not redundant
- Be easy to install and use
- Be compatible (as possible) on all platforms and versions

You have to keep in mind also that these scripts are geared around
the way that "I" manage databases.  Since every Oracle person has
their own style, you may find my scripts different than what
you are used too.  I encourage you to use the library as it is, but
feel free to change things you don't like (for your own personal
use, of course).  I am sure there is lots of room for improvement.


1.3     FEATURES

Here are some of the main features of the SQL library:

- Contains 270+ scripts covering ~ 20+ subject areas
- Includes documentation and table-driven, subject-based help
- Portable script names (8.3) run on any platform; consistent,
  UNIX flavored script names
- Consistent argument types & handling, smart wildcard support
- Automatically determines whether to use DBA, ALL, or USER
  data dictionary views, based on user type (DBA/non-DBA)
- All formatted to run in 80-char screen format
- Always displays generated SQL and prompts before executing
- Supports 7.x and 8.x databases
- Contains 8.x specific scripts

You will find that many of the scripts are just convenience scripts
which will save you a lot of typing.  However, there are just are
many that involve complex SQL, PL/SQL, and dynamic SQL.  A lot of 
the value of this library is not in the coding itself but in the
knowledge of the Oracle data dictionary embedded within.



2.      INSTALLATION

Installation of the library is simple and straightforward.
The library requires a package and optional help table(s)
to support on-line help.  You can opt for local
(current user only) or global (public) installs--the latter
requires creating the objects in the SYSTEM, or some other
privileged account.
(Note: the package performs read / formatting operations only
and does not represent any major security hazards.)


2.1     REQUIREMENTS

This library will run best on Oracle 7.3/8.0 or better.  Previous
versions of the database (at least 7.1) SHOULD work ok but I
no longer have access to these versions of the database to test.
If you are running on a 7.1 or 7.2 database and experience problems
you should contact me.  Later versions will (eventually) only support
7.3/8.x version of the database.


2.2     HOW TO INSTALL

The installation process requires three simple steps:

1. Install scripts into a common (shared) directory
2. Set appropriate search path to find the scripts in user's environment
3. Run "@install.sql" script to install support package(s)
   and help table(s)

   IMPORTANT!  @install must be run against every database
   you want to use the library with (Steps 1+2 need only to
   be done once).  You should also re-run @install against
   each database whenever you download a later version of 
   the software.  Many of the scripts (but not all) rely on 
   a package (LANG_UTIL) which is created by @install.  You
   will nt be able to use these scripts without the package.


STEP 1.

Below demonstrates sample installations for this step
on UNIX, WIN95/NT, and WIN31/DOS platforms.


UNIX
----------

1. mkdir $ORACLE_BASE/sql
   Move all scripts into the "sql" directory; on case-sensitive systems
   like UNIX, the script names must be kept in lower-case
2. Edit appropriate profile file (.profile for sh/ksh, .login for csh)
   in home directory.  Define the following environment variables
   (example shown is bourne/korn shell):

	SQL=$ORACLE_BASE/sql; export SQL
	SQPATH=$SQLPATH:$SQL; export SQLPATH

3. Before using the library a package must be created and made available
   the script users.  Online help requires the creation of a table in
   the same schema.  You can opt for a global (all users) or local (one
   user) install.  A global install should be done in SYSTEM or some
   other privileged account (but not SYS).  The global install is
   recommended, but if you are not a DBA you may have to use the
   local install.

	sqlplus system@<db>

	SQL> @install

	SQL LIBRARY INSTALLATION

	Local or Global install (l/G)?

	Install HELP table(s) (n/y)?

	Press ENTER to continue or CTRL+C to cancel...

	...


WIN95/NT        (32-bit client software)
----------

1. mkdir C:\SQL
   Move all script into C:\SQL directory
2. At command prompt or "Run" dialog execute: "regedit"
   Goto [HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE]
   Append to String key "SQLPATH" (create if not exists) the
   value "C:\SQL"
3. sqlplus system@<db>
   SQL> @install
   ...


WIN31/DOS       (16-bit Oracle client software)
----------

1. mkdir C:\SQL
   Move all script into C:\SQL directory
2. Edit ORACLE.INI (in C:\WINDOWS or C:\ORAWIN)
   Append to key "SQLPATH=" (add to file under [ORACLE] key
   if not exists) the value "C:\SQL"
3. sqlplus system@<db>
   SQL> @install
   ...


A similar installation process can be done on other operating systems.



3.      USING THE LIBRARY

I think you will find the library very comprehensive but easy
to use and understand.  My style of scripts are (almost) always
parameter-driven, never prompted, and very compact--they try to
cover as much ground as possible without becoming too complex.
They are also (fairly) consistent in design and usage.  Once you've
learned how a few scripts work, the rest of the library will
become very natural to use.


3.1     SPOOLED FILES

All scripts which generate SQL always spool the SQL to a file with
the name "<script_name>.tmp".  If the spooled commands are DDL or
otherwise sensitive, the output will also be show on the screen
and you will be prompted to cancel before the commands are executed.


3.2     ALGORITHMS

The tuning and sizing algorithms are all based on "well-known"/
"best-practice" techniques and/or formulae described in Oracle
documentation.


3.3     DBA vs. NON-DBA USERS

The library automatically tries to use the appropriate views
(DBA / ALL / USER) based on user type (DBA or NON-DBA).  If you
have the DBA role, the scripts will always use DBA_ views,
otherwise it will use the ALL_ views (or USER_ views if there is
no corresponding ALL_ view).  There is some difficulty in doing
this because two problems:

	1. The aren't ALL views for every DBA/USER view
	2. Annoyingly, many of the ALL views have slight differences
	   in column names that DBA and/or USER views

I am also finding some existing views have changed in version 8 
(although Oracle does a reasonable job of keeping things
as compatible as possible while supporting the new features of
version 8).  This is why you may see some "<name>8.sql" scripts out
there.  This is how I am handling some version 8 compatibility
problems until I come up with a better solution.


3.4     CUSTOMIZATION

Every script has calls two scripts to initialize and restore the
user's environment to run the script library.

	@setup[1-4]     -- prepare environment for script
	...
	<main script body>
	...
	@setdefs        -- restores environment

You should look and @setup and @setdefs if you want to customize the
behavior of the library, such as always forcing it to use DBA views
for example.  @setdefs undefs any DEFINEs created SETs the SQL*Plus
environment variables which were changed back to factory defaults
(assuming login.sql and glogin.sql are empty).


3.5     SCRIPT EXAMPLES

This sections shows how to use some of the most common scripts
in the library.  Note that the output for each example contains
contrived data only, but it its similar to what you should expect.

	@help           Display / search on-line help
	@whoami         Show current user and database
	@tabs           List tables
	@idxs           List indexes
	@whatis         Resolve object name
	@desc           Enhanced Sql*Plus DESCRIBE

	@who            List connected users
	@ping           Test remote connection
	@hits           Show basic database hit ratios
	@frag           Show database space utilization
	@pubsyn         Create public synonyms
	@privs          Show privileges


@help
Display / search on-line help for SQL script library

SQL> @help tabs %               -- show help on @tabs script
SQL> @help t% pu                -- show help for all scripts like 'T%';
				-- show only Purpose: and Usage:
SQL  @help s:user pu            -- show help for scripts with Subject:
				-- "user"
SQL> @help help %               -- show help on @help
NAME       TEXT
---------- ---------------------------------------------------------------
help       Purpose: display on-line help about sql scripts
	   Usage:   @help [<srch_opt>:]<%search%> <print_opts>
	   Attrib:  sql
	   Descr:
	   Notes:   <srch_opt> and <print_opts> made up of <PSUADNL> below:
	   P=purpose S=subject U=usage A=Attrib D=descr N=notes L=seealso
	   <srch_opt> can only be one letter, if not specified then search
	   by name <print_opt> can be one or all above, "Purpose:" is always
	   displayed, "%" specifies all
	   SeeAlso:

SQL> @help t% p
NAME       TEXT
---------- ---------------------------------------------------------------
tabs       Purpose: list tables
tabstat    Purpose: list table statistics
trace      Purpose: set sql_trace on/off in selected user sessions
trigs      Purpose: list triggers
trigs2     Purpose: report summary of triggers by type and event
trim       Purpose: trim values in columns
trimu      Purpose: trim values in columns and convert to uppercase
trunc      Purpose: drop database objects
ts         Purpose: list tablespaces

SQL> @help s:space p
NAME       TEXT
---------- ---------------------------------------------------------------------
cantext    Purpose: list segments which cant extend because of space or extents
coalesce   Purpose: perform alter tablespace ... coalesce on selected
	   tablespaces
dealloc    Purpose: deallocate unused space from table segments
dumpts     Purpose: create script which recreates tablespaces
exts       Purpose: list segment extents
frag       Purpose: displays database fragmentation
segs       Purpose: list segments
segs2      Purpose: list segments grouped by segment type
segs3      Purpose: list segments
space      Purpose: display unused space within segment extents
space8     Purpose: display unused space within segment extents
spacemap   Purpose: display map of tablespace fragmentation


@whoami
Show current user and database.

SQL> @whoami
Connected as SYSTEM@DEV.


@tabs <%owner.name%> <%tablespace%>
List tables by name and/or tablespace.

SQL> @tabs %.% %                -- all tables
SQL> @tabs %.%emp% %            -- all tables like '%EMP%' in any schema
SQL> @tabs scott.% data         -- all tables owned by SCOTT in DATA
				-- tablespace
SQL> @tabs scott.% %            -- all table owned by SCOTT


TABLE_NAME                TABLESPACE      PCTFU  TRANS  B DEGRE INSTA C
------------------------- --------------- ------ ------ - ----- ----- -
SCOTT.ACCTS               USERS           10/40  1/255  N     1     1 N
SCOTT.ACCT_ADDRS          USERS           10/40  1/255  N     1     1 N
SCOTT.CITIES              USERS           10/40  1/255  N     1     1 N
SCOTT.CUSTOMER            USERS           10/40  1/255  N     1     1 N
SCOTT.DEPT                USERS           10/40  1/255  N     1     1 N
SCOTT.DUMMY               USERS           10/40  1/255  N     1     1 N
SCOTT.EMP                 USERS           10/40  1/255  N     1     1 N
SCOTT.ITEM                USERS           10/40  1/255  N     1     1 N
SCOTT.MODE_BUTTON         USERS           10/40  1/255  N     1     1 N
SCOTT.ORD                 USERS           10/40  1/255  N     1     1 N
SCOTT.ORDER_HISTORY       USERS           10/40  1/255  N     1     1 N
SCOTT.ORDPICT             USERS           10/40  1/255  N     1     1 N
SCOTT.PORTDESC            USERS           10/40  1/255  N     1     1 N
SCOTT.PORTFOLIO           USERS           10/40  1/255  N     1     1 N
SCOTT.PRICE               USERS           10/40  1/255  N     1     1 N
SCOTT.PRODUCT             USERS           10/40  1/255  N     1     1 N
SCOTT.SALES_REVENUE       USERS           10/40  1/255  N     1     1 N
SCOTT.SALGRADE            USERS           10/40  1/255  N     1     1 N


@idxs <%owner.name%> <%index%>
List indexes by table and/or index.

SQL> @idxs mY_emp% %            -- all indexes on table(s) like 'MY_EMP%'
SQL> @idxs %.%emp% %            -- all indexes on table(s) like '%EMP%'
				-- in any schema
SQL> @idxs % %pk                -- all indexes in current schema that
SQL> @idxs my_employees %       -- all indexes on MY_EMPLOYEES table
				-- end in 'PK'

TABLE_NAME                INDEX
------------------------- ------------------------------------------------
MLANG.MY_EMPLOYEES        MYEMP_MYCTY_FK_I(COUNTRY_ID)
MLANG.MY_EMPLOYEES        MYEMP_MYDPT_FK_I(DEPT_ID)
MLANG.MY_EMPLOYEES        UNIQUE MYEMP_PK(EMP_ID)
MLANG.MY_EMPLOYEES        UNIQUE MYEMP_USER_NAME_UK(USER_NAME)
MLANG.MY_EMPLOYEES        MYEMP_FULL_NAME_I(LAST_NAME, FIRST_NAME)


@cons
List table constraints by table and/or type.

SQL> @cons my_employees PUR     -- list PK/UK/FK constraints on table
SQL> @cons %.%emp% P            -- list all PK constraints on table(s)
				-- like %EMP% in any schema
SQL> @cons % P                  -- list all PK constraints in schema
SQL> @cons my_employees %       -- list all constraint on table


SD NAME                           CONSTRAINT
-- ------------------------------ ----------------------------------------
E  MLANG.MY_EMPLOYEES.MYEMP_PK    PRIMARY KEY (EMP_ID)
E  MLANG.MY_EMPLOYEES.MYEMP_USER_ UNIQUE (USER_NAME)
   NAME_UK
E  MLANG.MY_EMPLOYEES.MYEMP_SOC_S UNIQUE (SOC_SEC_NUM)
   EC_NUM_UK
E  MLANG.MY_EMPLOYEES.SYS_C001569 CHECK (EMP_ID IS NOT NULL)
E  MLANG.MY_EMPLOYEES.SYS_C001570 CHECK (ADDED_DATE IS NOT NULL)
E  MLANG.MY_EMPLOYEES.SYS_C001572 CHECK (UPDATED_DATE IS NOT NULL)
E  MLANG.MY_EMPLOYEES.SYS_C003245 CHECK (SALARY_FLAG IN ('N', 'Y'))
E  MLANG.MY_EMPLOYEES.SYS_C003247 CHECK (FULL_TIME_FLAG IN ('N', 'Y'))
E  MLANG.MY_EMPLOYEES.SYS_C005965 CHECK (ACTIVE_FLAG IS NOT NULL)
EN MLANG.MY_EMPLOYEES.MYEMP_MYDP  FOREIGN KEY (DEPT_ID) REFERENCES
   T_FK                           MY_DEPARTMENTS (DEPT_ID)


@whatis <name>
Resolve specified name (what does <name> refer too?)

SQL> @whatis MY_EMPLOYEES
MLANG.MY_EMPLOYEES (TABLE)

SQL> @whatis MY_EMPLOYEES       -- not from MLANG schema
PUBLIC.MY_EMPLOYEES => MLANG.MY_EMPLOYEES (TABLE)


@desc
Enhanced DESCRIBE command (for tables + views only); has
DESCRIBE-like output but also includes the KEYS column which
shows key and index information for each column.  Can also
describe multiple tables at once (i.e. @desc %emp% %).

SQL> @desc my_employees %

(MLANG.MY_EMPLOYEES TABLE)
COLUMN_NAME                 NULL?    TYPE            KEYS
--------------------------- -------- --------------- ---------------------
EMP_ID                      NOT NULL NUMBER          P1(1)
ADDED_DATE                  NOT NULL DATE
ADDED_USER                  NOT NULL VARCHAR2(30)
UPDATED_DATE                NOT NULL DATE
UPDATED_USER                NOT NULL VARCHAR2(30)
USER_NAME                            VARCHAR2(30)    U1(1)
SALARY_FLAG                          CHAR(1)
OVERTIME_FLAG                        CHAR(1)
FULL_TIME_FLAG                       CHAR(1)
LAST_NAME                            VARCHAR2(30)    I1(1)
FIRST_NAME                           VARCHAR2(15)    I1(2)
MIDDLE_NAME                          VARCHAR2(10)
ADDRESS1                             VARCHAR2(35)
ADDRESS2                             VARCHAR2(35)
ZIP_CODE                             VARCHAR2(6)
CITY                                 VARCHAR2(30)
STATE                                VARCHAR2(2)
ZIP_CODE                             VARCHAR2(6)
ZIP_PLUS_4                           VARCHAR2(4)
PHONE_NUM                            VARCHAR2(10)
SOC_SEC_NUM                          VARCHAR2(9)     U2(1)
DEPT_ID                              NUMBER          R1(1) I2(1)
EMAIL_ADDR                           VARCHAR2(40)
START_DATE                           DATE
TERM_DATE                            DATE


@who <%user%>
List currently connected sessions.

SQL> @who j%            -- list all connected users like 'J%'
SQL> @who %             -- list all connected users

UNAME           PROCESS   TERMINAL   LOGON_TIME   STATUS   MODULE
--------------- --------- ---------- ------------ -------- ---------------
ASTCLAR,35      429480708 1429       Tue 09:08    INACTIVE screen01.fmx
BJOHNSON,60     429479964 1901       Tue 08:46    INACTIVE screen12.fmx
CANDTE,59       429487436 1150       Tue 08:31    INACTIVE screen04.fmx
CFLIGHT,50      429484151 1266       Tue 08:33    INACTIVE SQL*Plus
CSMITH,30       429485687 1143       Tue 09:07    INACTIVE screen10.fmx
DSWATCH,44      429484056 1425       Tue 08:30    INACTIVE SQL*Plus
DVANDERP,29     429488424 1422       Tue 08:49    INACTIVE jagipf21
GPIPA,19        429485963 1133       Tue 08:29    INACTIVE screen01.fmx
JFERRARI,28     429485979 TBD        Tue 08:32    INACTIVE
JSHAMME,43      429483014 1134       Tue 08:17    ACTIVE   screen03.fmx
JSKIPP,42       429477758 1418       Tue 08:40    INACTIVE
JTASSMAR,49     429484128 1433       Tue 08:31    INACTIVE SQL*Plus
...


@ping
Test connection to remote database; database "ping"; requires
necessary database link(s) in place to function.

SQL> @ping dev
Ping successful to DEV at Tue Feb 10, 1998 12:19:54.


@hits
Displays primary system hit ratios (buffer cache, library cache,
dictionary cache, and sorts to disk/memory).  The first column
shows actual ratio, the second show the typical recommended ratio,
the third shows the memory allocated to each structure.

SQL> @hits
PERFORMANCE RATIOS - PROD
LC     0.999  0.990 38M
DC     0.982  0.850 38M
BC     0.907  0.850 117M (30,000 4k blocks)
SORTS  0.998  0.950 128k/64k


@frag <%tablespace%> <pctfree|%>
List space utilization / fragmentation by tablespace and/or min pct
free space;  includes many useful statistics.  This is one of the
most common scripts for DBAs.

SQL> @frag % %

FREE SPACE - PROD     +------------- FREE ------------+ +------  USED ------+
Tspace       Fl SizeM FreeM %Fre Piecs BigsM <100k Dead NmSegs >10e >100 Most
------------ -- ----- ----- ---- ----- ----- ----- ---- ------ ---- ---- ----
APP_DATA      1   100    49  49%     1    49     0    0      4    0    0    0
APP_INDEX     1   100    53  53%     1    53     0    0     12    0    0    1
COM_DATA      1   150   116  77%     1   116     0    0     56    0    0    0
COM_INDEX     1   150   131  88%     2   131     0    0     87    0    0    0
COM_STAGE     1    20    10  49%     1    10     0    0      2    0    0    0
...
RBS           1    50     2   4%    22     0    22    0    217    8    0   48
RBSBIG        1   200     5   2%     1     5     0    0     39    1    0   38
SYSTEM        1    70     3   4%     1     3     0    0    670    9    1  148
TOOLS         1    10     7  66%     2     5     0    0     19    0    0    9
USERS         1    10     3  29%     1     3     0    0     28    0    0    4


@privs <%user|role%> <srtc|%> <%privilege%> <%owner.name%>
List user/role privileges by type (system, role, table, column),
privilige, and/or object.

SQL> @privs scott % % %.%       -- list all privs for SCOTT
SQL> @privs scott sr % %        -- list all sysprivs/roles for SCOTT
SQL> @privs % r dba %           -- list all users with DBA role
SQL> @privs % tc insert %.%emp% -- list all users with INSERT on
				-- tables like '%EMP%' in any schema
SQL> @privs connect % % %.%     -- privileges for CONNECT role

GRANTEE              P PRIVILEGE            OP ONAME
-------------------- - -------------------- -- ------------------------------
CONNECT              S ALTER SESSION
CONNECT              S CREATE CLUSTER
CONNECT              S CREATE DATABASE LINK
CONNECT              S CREATE SEQUENCE
CONNECT              S CREATE SESSION
CONNECT              S CREATE SYNONYM
CONNECT              S CREATE TABLE
CONNECT              S CREATE VIEW


@pubsyn <owner.%name%>
This useful script maintains PUBLIC SYNONYMS for objects in a schema.
It basically does 3 things:
1. Create public synonyms of same name for objects in SCOTT that
   match <%name%> and have no public synonym
2. Rename public synonyms of same name that match <%name%> but point
   to same object(s) in another schema
3. Drop public synonyms which match <%name%> that point to object(s)
   in specified schema which no longer exist
Note that the schema (owner) name cannot be wildcarded.

SQL> @pubsyn <scott.%>          -- create PUBLIC SYNONYMS for all
				-- objects owned by SCOTT
SQL> @pubsyn <scott.%emp%>      -- like above but only if like '%EMP%'

create public synonym for SCOTT.EMP;
Press ENTER to continue or CTRL+C to cancel...


4.      SCRIPT DOCUMENTAION

Each script contains its own documentation in a 10+ line header
which describes the usage and functionality of the script in detail.
The information in the script headers is used to generate the
on-line help system, so what you see in the help system
is the same information found in the script header itself. The
format of the header is the same for each script, although some
scripts may contain more information than others:

rem Author:  Mark Lang, 1998
rem Name:    tabs.sql
rem Purpose: List tables
rem Usage:   @tabs <%owner.table%> <%ts%>
rem Subject: object:table
rem Attrib:  sql
rem Descr:
rem Notes:
rem SeeAlso: @cons @idxs @objs @tabstat
rem History:
rem          01-feb-98  mlang   Initial release


Name:    File name of the script

Purpose: One line description of purpose / function of script

Usage:   Describes script syntax and parameters

	< > mandetory
	[ ] optional

	Argument Types:

	<%owner.name%>  (object name; accepts wildcards; not case-sens)
	or
	<%[owner.]name%>

	Examples:

	emp             object in current schema named 'EMP'
			(not case-sensitive)
	emp%            all objects in current schema like 'EMP%'
	%               all objects in current schema
	scott.emp       object in 'SCOTT' schema named 'EMP'
	scott.emp%      all objects in 'SCOTT' schema like 'EMP%'
	scott.%         all objects owned by 'SCOTT'
	scott%.emp      all objects in schemas like 'SCOTT%' named 'EMP'
	scott%.emp%     all objects in schemas like 'SCOTT%' named
			like 'EMP%'
	%.emp           all objects in all schemas named 'EMP'
	%.emp%          all objects in all schemas like 'EMP%'
	%.%             all objects in all schemas
			(which you have access to)


	<owner.name>
	or
	<[owner.]name>  (object name; wildcards not accepted; not case-sens)

	emp             object in current schema named 'EMP'
	scott.emp       object in 'SCOTT' schema named 'EMP'

	<%name%>        (object name; wildcards accepted; not case-sens)

			current schema only

	<%name%>        (object name; wildcards not accepted; not case-sens)

			current schema only

	<%param%>       (argument; wildcards accepted; may be case-sens)

	table           = 'table'
	table%          like 'table%'

	<param>         (non-wildcard parameter)

	<param|%>       (parameter; % is all or default value)

	<0|1|%>         (choices w/ default; must be one of listed vals)

	<arg>[*]        (optional * at end changes functionality)


Subject: Subject key used for cross-reference in help

	Keyword         Subject Description
	------------    -----------------------------------------------
	backup          Backup & recovery
	data            Data management
	database        Database management
	dba             DBA control scripts
	instance        Instance management
	job             Job management
	misc            Miscelleneous
	mts             MTS management
	nls             NLS management
	object          Object (Table,Index,Constraint,etc.) management
	plsql           PLSQL (Stored object) management
	quality         Quality
	replication     Replication support
	security        Security management
	session         Session management
	space           Space management
	sqlplus         SQLPLUS nested scripts
	svrmgr          SVRMGR specific scripts
	tuning          Tuning
	user            User management


	You will find some subject areas more mature than others.
	I am constantly developing scripts to enhance all areas of
	Oracle administration and development.


Attrib:  Indicates some characteristics about the behavior of the script;
	 For example, scripts which are DBA only will have the "dba"
	 or "sys" attribute.
	 (most are correct but still updating)


	Attr    Description
	----    ------------------------------------------------
	sql     sql*plus / sql
	pls     sql*plus / pl/sql
	mgr     svrmgr

	gen     generates code
	dyn     execute dynamic sql
	out     outputs a file
	nst     nested script

	dml     does insert,update,delete
	ddl     does DDL

	own     must run as object owner
	dba     must be run by DBA (accesses SYS owned objects)
	sys     must be run by SYS

	v7x     must run version 7.X or higher (default is 7.0)
	v8x     must run version 8.X or higher
	n7x     will not run on version 7.X or higher
	n8x     will not run on version 8.X or higher


Descr:   Full description of the script
	 (still updating)

Notes:   Miscellaneous usage notes

SeeAlso: Lists other related scripts

History: Modification history



5.      NOTES


5.1     RESTRICTIONS

DO NOT USE the LANG_UTIL package directly.  It may be changing
in future releases.


5.2     SUPPORT

There is no official support for this software.  It is provided 
"as is", but its also free.  Plus, you have the source for the SQL
at your fingertips.  This doesn't mean you can't email me
with questions or comments; on the contrary, I look forward to
hearing from you.  Unfortunately, I cannot guarentee that I will
be able to respond to every single message.



APPENDIXES


A.      SCRIPT LIBRARY - ALPHABETICALLY

Current listing of all scripts in the library.


about   access  access2 alttab  altusr analyze  appdev  appown  
args    appusr  ascii   aud

become  bg      bh      bufpool

cache   cantext cinfo   clrpwd  clus    clustat cmts    coalesce
code    colcmp  coldefs coldict coldist cols    cols2   colstat 
compat  compile cons    cons2   conn  count   crrbs     crseq   
crud    crud2   ctas    cursors

daily   date    db      dbabort dbbegin dbend   dbpin   dbshut  
dbshutf dbsize  dbstart dbstartfdealloc del     deps    desc
disablecdisableddisablekdisabletdoit    drop    dropc   dropexcl
drop    droprefsdroproledropsegsdropt   dropu   dump    dumpp
dumpr   dumpts  dumpu   dups    dupscnt dview

enablec enablet enforce errors  estimateestown  events  except
explain explcnt explsel explstatexplstmtexpltab exts

files   filext  finger  forcols forobjs frag

grobj   grsys

help    helpbildhelpdrophier    hits

idxs    idxstat initora ins     install invalid io

jobs

keeps   keys    kill

lastsql lic     like    links   list    lockdef locks   logf
logs    longops lowcard lrustat ls

max     mcount  min     miscols miscons misobjs movefilemkexp
mts     mtsmon

nls     noaccessnofk    nopk    notfoundnullcolsnvl

objs    objs2

paramd  params  parts   partstatpartvalspasswd  pga     pin
ping    plans   pq      privs   profs   protect pubs    pubsyn

qacons  qaidxs  quotas

rbs     rbsoff  rbson   rbsstat rebuild refs    reftree rels    
restidxsrestrefsrename  roles   rvsys


saveidxssaverefs
schema  search  segs    segs2   segs3   segsi   sel     seqs
sess    sessio  setbuf  setbufi setdefs setpq   setts   settx
setup   setup1  setup2  setup3  setup4  setupdb setusr  setver
sga     sgastat sh      short   sizes   space   space8  spacemap
sqlarea sroles  stats   strig   submit  sview   sy      syncgrnt
syncpwd syns

tabs    tabstat trace   trigs2  trim    trimu   trunc   ts

uga     uninst  unpin   up      updcols upper   usage
userenv users

ver     vsize

waiters waits   whatis  who     whoami  whoamip

y2k



B.      ABOUT THE AUTHOR

I currently work as an Oracle DBA in Detroit, MI.  You can currently
reach me by e-mail at

	mlang@phoenixgroup.com

If you have any comments about my software, or any suggestions,
hints (or bugs), etc.  please let me know.  I would love to hear
from you.  Also, you should check my website

	http://www.geocities.com/SiliconValley/Lakes/1261

for the latest updates.  Enjoy!

If you like my work, please tell others about it!!!


Mark Lang
