Sql Toolkit Readme
##############################################################################################################
# Runner Technologies, Inc.
#
# SQL Toolkit 2.3 - Command line utilities for Oracle SQL*Plus
#
# November 1, 2002
#
# SQL Toolkit 2.3 is a freeware collection of over 150 SQL*Plus queries and utilities which run against an
# Oracle database. They are designed to be run from the SQL*Plus command prompt, and can query either
# USER_ or DBA_ tables. They are a time saving collection of SQL scripts for DBAs and developers which
# reduce typing and automate common and complex database tasks.
# Wildcards can be specified in most parameters to reference multiple objects or when you do not know the
# exact spelling of a database object (ie. @qobject table% @grantselectto table% myuser).
#
# SETUP.TXT - Please see the SETUP.TXT file for important installation notes.
#
# LICENSE.TXT - Please read the License Agreement (LICENSE.TXT) prior to using SQL Toolkit 2.3.
#
# SUPPORT - Please email any requested enhancements or issues you find to: support@runnertechnologies.com
#
# AUTHOR - Runner Technologies, Inc.
#
# We at Runner Technologies thank you for using SQL Toolkit 2.3 and hope you find it as useful as we have.
#
##############################################################################################################
# DISCLAIMER:
#
# Users of SQL Toolkit 2.3 (herein referred to as SQL Toolkit) must accept this disclaimer of warranty:
# SQL Toolkit is supplied as is. The author disclaims all warranties, expressed or implied, including,
# without limitation, the warranties of merchantability and of fitness for any purpose. The author assumes
# no liability for damages, direct or consequential, which may result from the use of SQL Toolkit.
##############################################################################################################
# Other products offered by Runner Technologies:
#
# ---------------------------------------------------------------------------------------------------------
# ORBIT - Web Development Suite for Oracle
# ---------------------------------------------------------------------------------------------------------
# ORBIT is a web development suite for building dynamic thin client web applications in Oracle.
# ORBIT's enterprise class application framework is the easiest and most flexible way to rapidly build
# new applications or migrate from client-server to the web
#
# ---------------------------------------------------------------------------------------------------------
# CLEAN_Address() - Address Verification for Oracle
# ---------------------------------------------------------------------------------------------------------
# CLEAN_Address() is an address hygiene component built specifically for Oracle databases. It will
# standardize and verify any Global address, name, telephone, and email address through a simple PL/SQL
# procedure. CLEAN_Address() supports both a real-time and batch mode.
#
# For more information, please call us at the number below or check out http://www.RunnerTechnologies.com
##############################################################################################################
# Copyright (c) 1997-2002 Runner Technologies, Inc. All Rights Reserved.
# www.RunnerTechnologies.com sales@runnertechnologies.com 877-784-0003 / 561-395-9322 11/02
##############################################################################################################
##############################################################################################################
# User / Role / Session Management / Security Scripts
##############################################################################################################
crtuser <user> <password> - Create a user with default tablespace USERS, temp tablespace TEMP
ALIAS: adduser, mkuser
chgpass <user> <password> - Change a user password
ALIAS: passwd
qsession <user | SID | OSuser | Machine>
- Show all active sessions in the system
ALIAS: ps, qsess
qsession_active <user | SID | OSuser | Machine>
- Show all active users currently connected
qjob <what | user> - Show all Database jobs that match the input parameter
DBA: qdbajob
qpid <system_pid | user | SID | OSUser | Machine>
- Shows the user and system process id of all connected users
qusersql <user | SID | OSuser | Machine>
- Show the currently running SQL text for a user
qwaiters - Shows all waiting / blocking users and objects
qlocks <table> - Show all database locks matching the parameter
ALIAS: qlock
kill <SID,Serial#> - Kills a session based on the SID and Serial#
killuser <user> - Kills all sessions for the user(s) specified
quser <user> - Shows user information matching the parameter
qaccess <user> - Shows access for the specified user(s)
DBA: qdbaaccess
qaccessto <object | user> - Show all users with access to the specified object
DBA: qdbaaccessto
qaccesstoby <object> <user> - Shows the access by a user to a table
DBA: qdbaaccesstoby
qaccesssum <object | user | role> - Shows the summary of access for users matching the parameter
DBA: qdbaaccesssum
qrole <role> - Shows database Roles matching the parameter
qrolemap <role> - Shows the Role hierarchy (up and down) for the specified role
qroleusers <role | user> - Shows the users with access to a role
##############################################################################################################
# Grant / Revoke Utility Scripts
##############################################################################################################
grantselectto <object> <user> - Grants select access on objects to a user
grantexecto <package | function | procedure> <user>
- Grants execute access on packages to a user
grantallto <object> <user> - Grants ALL privileges on objects to a user
grantsiudto <object> <user> - Grants Sel,Ins,Upd,Del (siud) privileges on objects to a user
grantroleto <role> <user> - Grants roles to a user
revoke <S|I|U|D|ALL> <object> <user>
- Creates a script which revokes privileges on objects from users.
<privilege> can be abbreviated as s,i,u,d,all
revokeallon <object> - Creates a script which revokes all user access for objects
matching the parameter
revokeallfrom <user> - Creates a script which revokes all access from the specified user
##############################################################################################################
# Table / Column Statistic Scripts
##############################################################################################################
qcount <table> - Count the number of records in a table or view
ALIAS: cnt, count
qcountw <table> - Count the number of records in a table specifying a where clause
ALIAS: cntw, countw
qtabcnt <table> - Show the record count for all tables matching the parameter
ALIAS: cnttab, tabcnt
DBA: qdbatabcnt, dbacnttab, dbatabcnt
qobjcnt <table> - Show the record count for all objects matching the parameter
ALIAS: cntobj, objcnt
DBA: qdbaobjcnt, dbacntobj, dbaobjcnt
qgrpcnt <col,col,...> <table> - Show the distribution of column values for a table
ALIAS: grpcnt
qgrpcnt2 <col,col,...> <table> - Same as qgrpcnt, but only for values with 2 or more occurrences
ALIAS: grpcnt2
qgrpcntw <col,col,...> <table> - Show the distribution of column values for a table, specifying
an optional where clause when prompted
ALIAS: grpcntw
qgrpcntn <col,col,...> <table> <limit>
- Show the top values for this column with an occurance >= limit
ALIAS: grpcntn
qgrplencnt <column> <table> - Show the distribution of column values and lengths for a table
ALIAS: grplencnt
qlencnt <column> <table> - Show the distribution of column value lengths for a table
ALIAS: lencnt
qcolcnt <table> - Show the number of columns in tables
ALIAS: colcnt, cntcol
DBA: qdbacolcnt, dbacolcnt, dbacntcol
qcntnull <column> <table> - Count the number of NULL/NOT NULL values in a column
ALIAS: qnullcnt, nullcnt, cntnull
qminmax <column> <table> - Show the minimum and maximum values in a column
ALIAS: minmax
qunique <col,col,...> <table> - Counts the number of unique values in the table.column
qunique2 <col,col,...> <table> - Counts the number of unique values in the table.column with 2 or
more occurrences
##############################################################################################################
# Object (Table / View / Snapshot / Column / Index / Sequence) Search Scripts
##############################################################################################################
qobject <object> - Show all objects matching the parameter
ALIAS: qobj, dir, ls
DBA: qdbaobject, qdbaobj, dbadir, dbals
qobject_last <object> <number_days> - Show all objects matching the parameter that have been changed
in the last number_days
ALIAS: qobj_last
DBA: qdbaobject_last, dbaqobj_last
qdesc <table> - Describe the specified object(s)
ALIAS: desc
DBA: qdbadesc, dbadesc
qcat <table> - Show all tables/views/sequences/synonyms from the user catalog
matching the parameter
DBA: qdbacat
qtable <table | tablespace> - Show table information including tablespace and storage
ALIAS: qtab
DBA: qdbatable, qdbatab
qview <view> - Show view information
DBA: qdbaview
qviewtext <view> - Show the view Select text
ALIAS: viewtext
DBA: qdbaviewtext, dbaviewtext
getview <view> - Extracts view text to a file - one per view (wildcards allowed)
ALIAS: getviewtext
getview_noaudit <view> - Extracts view text to a file without audit information
qsnapshot <snapshot> - Show snapshot information
ALIAS: qsnap, qss
DBA: qdbasnapshot, qdbasnap, qdbass
qcolumn <column | table> - Show all columns matching the parameter
ALIAS: qcol
DBA: qdbacolumn. qdbacol
qcolcomment <column | table> - Shows all columns (with comments) matching the parameter
ALIAS: qcomment
DBA: qdbacolcomment, qdbacomment
qcoltab <column> <table> - Shows all matching tables/columns in matching the input column
within the input table
ALIAS: qtabcol
DBA: qdbacoltab. qdbatabcol
qindex <table|index|tablespace> - Show index information including tablespace and storage
ALIAS: qind
DBA: qdbaindex, qdbaind
qindexsize <column> <table> - Show the estimated size of a single column index on the specified
table/column (no wildcards)
qseq <sequence> - Show all sequences matching the parameter
ALIAS: qsequence
DBA: qdbaseq, qdbasequence
qdblink - Shows all database links
ALIAS: dblink
DBA: qdbadblink, dbadblink
qrec <[user.]table> "<where_clause>"
- Show a table record in vertical format based on the "where_clause"
NOTE: Enclose the "where_clause" in quotes (no wildcards)
ie) @qrec mytable "where mycol='abc' order by xyz"
DBA: qdbarec
qall <table> - Shows the entire contents of a table, sorted by the first column
ALIAS: all
##############################################################################################################
# Key / Constraint Scripts
##############################################################################################################
qpkey <table | pkey | ukey> - Show all Primary/Unique key constraints matching the parameter
DBA: qdbapkey
qfkey <table | fkey | pkey> - Show all foreign key constraints matching the parameter
DBA: qdbafkey
qconstraint <table | constraint> - Show all constraints matching the parameter
ALIAS: qcon
DBA: qdbaconstraint, qdbacon
qkey <table | fkey | pkey | ukey>
- Show all key constraints matching the parameter
DBA: qdbakey
qkeynotin <col1> <tab1> <col2> <tab2>
- Show all column1 values that are not in table2 (column2)
Note: Use a. and b. notation before column1/column2 if either of
the tables have common column names
qcondisabled <table | constraint> - Show all disabled constraints matching the parameter
ALIAS: qdisabled, qcondis, qdiscon
DBA: qdbadisabled, qdbacondisabled, qdbacondis, qdbadiscon
enablefkey <table | fkey | pkey> - Enable Foreign key constraints on a table
ALIAS: enable_fkey
disablefkey <table | fkey | pkey> - Disable Foreign key constraints on a table
ALIAS: disable_fkey
dropfkey <table | fkey> - Creates a script which drops Foreign key constraints on a table
ALIAS: drop_fkey
##############################################################################################################
# Synonym Scripts
##############################################################################################################
qsyn <synonym | table | owner>- Show all private synonyms matching the parameter
ALIAS: qsynonym
DBA: qdbasyn, qdbasynonym
qpubsyn <synonym | table | owner>- Show all public synonyms matching the parameter
qdiffsyn <synonym | table | owner>- Show all public and private synonyms matching the parameter where
the synonym name != table name
ALIAS: qsyndiff
DBA: qdbadiffsyn, qdbasyndiff
crtpubsyn <object> - Create public synonyms for the objects specified.
ONLY missing or different synonyms are created.
ALIAS: mkpubsyn
dropprivsyn <object> <owner> - Creates a script which drops all private synonyms for the
owner and object specified
droppubsyn <object | owner> - Creates a script which drops all public synonyms for the object
or owner specified
##############################################################################################################
# Package / Procedure / Function Scripts
##############################################################################################################
qpkg <package> - Show package information matching the parameter
ALIAS: qpackage
DBA: qdbapkg, qdbapackage
qpkg_last <package> <number_days> - Show packages matching the parameter that have been modified in
the last number_days
ALIAS: qpackage_last
DBA: qdbapkg_last, qdbapackage_last
qinvalid <object> - Show all INVALID objects matching the parameter
ALIAS: invalid
DBA: qdbainvalid
refresh <object> - Recompile all invalid objects for a user
ALIAS: ref, compile, comp
DBA: dbarefresh, dbaref, dbacompile, dbacomp
qerrors <object> - Shows the errors and lines of invalid packages, procedures, etc.
ALIAS: showerrors, show_errors, qerror, qerr
DBA: qdbaerrors, dbashowerrors, dbashow_errors, qdbaerror, qdbaerr
getpkg <package> - Extract a procedure or package header and body (wildcards allowed)
ALIAS: getpackage
getpkg_unix <package> - Extract a procedure or package header and body.
Replaces $ with # in filenames for unix compatibility
getpkg_noaudit <package> - Extract a procedure or package header and body without audit
information in the header (wildcards allowed)
droppackage <package | function | procedure>
- Creates a script named "droppackage_<USER>.sql" for dropping
packages in a users schema
ALIAS: droppkg
##############################################################################################################
# Trigger Scripts
##############################################################################################################
qtrigger <table | trigger> - Show trigger information matching the parameter
ALIAS: qtrig, qtrg
DBA: qdbatrigger, qdbatrig, qdbatrg
enabletrigger <table | trigger> - Enable triggers matching the parameter
ALIAS: enabletrg, enabletrig, enable_trigger
disabletrigger <table | trigger> - Disable triggers matching the parameter
ALIAS: disabletrg, disabletrig, disable_trigger
gettrigger <table | trigger> - Extracts trigger text to a file (wildcards allowed)
ALIAS: gettrig, gettrg
gettrigger_noaudit <trigger> - Extracts trigger text to a file without audit information
##############################################################################################################
# Tablespace / Table / Index Size Scripts
##############################################################################################################
qtablespace <tablespace> - Shows the total sizes of all tablespaces matching the parameter
ALIAS: qts, ts, tablespace, tablespaces
qdatafiles <tablespace> - Show all database files matching the parameter
ALIAS: datafiles, qdf, df
qextents <user | table | index > - Show all table/index extents matching the parameter
ALIAS: qext, extents, ext
DBA: qdbaextents, qdbaext, dbaextents, dbaext
qtable <table | tablespace> - Show table information including tablespace and storage
ALIAS: qtab
qindex <table | index> - Show index information including tablespace and storage
ALIAS: qind
DBA: qdbaindex, qdbaind
qindexsize <column> <table> - Show the estimated size of a single column index on the specified
table/column (no wildcards)
##############################################################################################################
# Table / Column / Index / Sequence Utility Scripts
##############################################################################################################
resizetable <table> <next_extent> <max_extents>
- Resize a table by resetting the next extent and max extents
ALIAS: resizetab
resizeindex <index | table> <next_extent> <max_extents>
- Resize an index by resetting the next extent and max extents
ALIAS: resizeind
synctables <source_table> <destination_table>
- Create insert / update / delete commands for syncing a table
(destination_table) relative to an existing table (source_table)
ALIAS: synctable
crtindex <col,col,...> <table> <tablespace>
- Size and create an index on the table/column(s)
Index name will be: <table>_<column>_I
NOTE: Multiple column indexes will default to initial extent 16K
ALIAS: crtind
crtindex_unique <col,col,...> <table> <tablespace>
- Size and create a unique index on the table/column(s)
Index name will be: <table>_<column>_I
NOTE: Multiple column indexes will default to initial extent 16K
crtindex_prefix <col,col,...> <table> <tablespace> <index_prefix>
- Size and create an index on the table/column(s) with the
specified prefix. Index name will be: <index_prefix>_<column>_I
NOTE: Multiple column indexes will default to initial extent 16K
dropindex <index | table> - Creates a script which drops indexes matching the parameter
ALIAS: dropind
add_notnull_col <table> <column> <column_type> <default_value>
- Add a NOT NULL column to a table with the data type and
default value specified
add_notnull_flag <table> <column> - Add a NOT NULL flag (NUMBER(1) default 0) to a table with
check constraint of (1,0)
resetseq <sequence> <next_number> - Reset sequences to a new start value for sequences matching
the parameter
DOES NOT TOUCH sequences that CYCLE or do not have an
increment by of 1
ALIAS: resetsequence
buildresetseq <sequence> - Creates a reset sequence script to set the sequence to the
current value (for synchronizing sequences between 2 instances)
##############################################################################################################
# Data Dictionary Drop Scripts
#
# NOTE: The drop<xxx> scripts ONLY generate a script file based on the parameters (drop<xxx>_<USER>.sql).
# They do NOT run this file automatically. You need to run this file manually after generation.
##############################################################################################################
dropcat <table | view | sequence | synonym | snapshot>
- Creates a script named "dropcat_<USER>.sql" for deleting objects
in a schema. Includes: Tables, Views, Sequences, Snapshots.
Does NOT drop packages.
droppackage <package | function | procedure>
- Creates a script named "droppackage_<USER>.sql" for dropping
packages in a users schema
ALIAS: droppkg
dropindex <index | table> - Creates a script which drops indexes based on the parameter
ALIAS: dropind
dropfkey <table | fkey> - Creates a script which drops Foreign key constraints on a table
ALIAS: drop_fkey
dropprivsyn <object> <owner> - Creates a script which drops all private synonyms for the
owner and object specified
droppubsyn <object | owner> - Creates a script which drops all public synonyms for the object
or owner specified
 |
