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. The only problem with modifying the library is that you may have difficulty upgrading to new releases, but it is always your option to do so if you want too. 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@ 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@ 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@ 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 ".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 "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 ...
... @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 [:]<%search%> Attrib: sql Descr: Notes: and made up of below: P=purpose S=subject U=usage A=Attrib D=descr N=notes L=seealso can only be one letter, if not specified then search by name 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 Resolve specified name (what does 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%> 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%> <%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 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 -- create PUBLIC SYNONYMS for all -- objects owned by SCOTT SQL> @pubsyn -- 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) 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%' (non-wildcard parameter) (parameter; % is all or default value) <0|1|%> (choices w/ default; must be one of listed vals) [*] (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