ORA600 Ltd., Oracle Consultancy

My Useful scripts

These scripts are ones I have written over many years and use in my day-to-day work, generally database administration and tuning. There are a few I have inherited along the way too. They are mostly for listing out aspects of the database {such as table, column and index details} and DBA-type things {like listing initialisation parameters and the contents of the SGA}. Most of them have been run against Oracle 10.2 and many have been updated to match this version.

Jump to the scripts

Most of these scripts are written to run against DBA or internal dictionary tables, which need privileges to see {DBA role or select any dictionary}. However many of them can be easily converted to run for 'normal' users. Many tables referenced {actually they are usually views} are called DBA_aaaaaaaa:-

  • DBA_TABLES
  • DBA_INDEXES
  • DBA_OBJECTS

Simply change to one of the two other versions of most of these tables:-

  • ALL_aaaaaaa eg ALL_TABLES - these will show all objects that you have privilege to see
  • USER_aaaaaa eg USER_TABLES - these will show the objects owned by the user you are logged in as

Most of the scripts prompt for an input or inputs, eg tab_lst.sql prompts for a table name. The value(s) entered limit the information reported on. Most scripts add '%' to the value and use a LIKE comparison, so if you enter 'ORd' the tab_lst script will show all tables staring ORD. The value is often forced to upper case as object names in the dictionary are upper case.

Some other points are:-

  • Many of the scripts default a null input to a string of 'WHOOPS' or 'ERIC' {why 'WHOOPS' or 'ERIC'? Well, why not. Last time I checked, no internal objects started with these strings} so that running it with no input does not report back very large amounts of data. Enter '%' to see everything {WARNING - this might be a very, very long list}.
  • All statistics on tables, columns, indexes depend on the objects having been analyzed {dbms_stats ideally} at a level and time to give accurate results.
  • Nearly all scripts spool out to a file called the same thing with a .lst extension.

Scripts tend to have shortened names; 'tab' for table, 'ind' for index, 'ts' for tablespaces, 'lst' if it just lists basic information, 'chk' if the script was intended for checking the state of play. Like Oracle Corp I have sometimes been poor in keeping to my own standards :-). Most scripts use sql*plus commands to format the output and older scripts keep their output to 80 characters wide and 24 lines, which shows how old I am {back in the late 80's/early 90's many terminals or terminal emulators were still in use and had this resolution}. Later scripts tend towards 120 characters.

Finally, I do not mind who has or uses these scripts, give them away as you like, but I do ask that if my name or, more importantly, someone else's name is in the comments at the top of the script, you preserve that name. If you improve the script or want to share any others, please let me know.

These are the scripts, I've seperated some out into groups and then finish with a list of all of them.

Looking at tables

  • tab_lst - simple list of tables with number of rows (as last analyzed)
  • tab_desc - slightly more informative replacement for DESC
  • tab_det - table and storage info, a little out of date.
  • tab_detf - table storage and stats

Looking at indexes

  • ind_lst - list of indexes by TABLE(s)
  • ind_cols - indexes and their columns by TABLE(s)

Looking at users

Finding things

  • obj_lst - list of objects by name and object type, defaults to table
  • syn_lst - synonyms
  • col_lst - occurances of a column with that name. Useful for spotting links

Looking at server-side "code"

  • pkg_lst - stored PL/SQL by owner
  • pkg_txt - the sql text by stored PL/SQL name. Can be quite long. Can see some system code with this.
  • vw_lst - list of views
  • vw_txt - Can look at data dictionary views

Peformance

  • chk_sga - show most demanding SQL code in SGA
  • chk_sga_disk - show most demanding SQL in SGA by disc access
  • chk_sga_txt - SQL from SGA that contains the given text (case sensitive)
  • chk_sga_today - SQL from SGA in order of parse time descending, for today
  • chk_hist - Pull out column histograms for a table and optionally limit by owner and column
  • chk_sci - nearly all stast info you need to tune access to a table
  • chk_col_usage - what columns have been referenced in sql predicates and joins since startup
  • chk_tsh_full - when stats were gathered on tables
  • chk_ish_full - when stats were gathered in indexes
  • exp1 - Explain. NB it has the select from dbms_xplan in there but commented out

All Scripts

  • all_ind_cols - all indexes and their columns for a table
  • backup_summary - List backups as run under control of oem, must be run on enterprise manager DB with high privs. (amw)
  • blockers - fast, efficient script to show blocking locks
  • blocks_by_usr - count of table blocks by user in the DB
  • buff_conts - List out the contents of the SGA, largest object first. Only those with >100 blocks
  • chk_all_pgamax - Quick check of PGA memory. very specific but good eg of getting sessions stats
  • chk_bbc_conts - tells you what is in bbc1 tonight. OK, it list the number of blocks and the segments taking up most space (over 200 blocks) in the SGA. Can watch the SGA fill after restarting db or flushing the cache.
  • chk_bck - real simple check on backup status of tablespaces
  • chk_ts_bck - very simple check to see if the tablespace has ever been backed up
  • chk_col_counts - number of distinct values for columns, by table and owner. Useful for checking basic selectivity. Needs stats to be up to date and collected correctly
  • chk_col_usage - what columns have been referenced in sql predicates and joins since startup. Incredibly useful to see what columns may benefit from indexing. dbms_stats uses this info
  • chk_dbst_prm - see what the default parameters for the auto stats job are set to
  • chk_grow - check tablespaces for segments that cannot grow. Old and possibly redundant due to auto extend TS's
  • chk_gv_res - show global resource enries (for RAC). Originally written for OPS!
  • chk_hidden_prms - show all the underscore (hidden) initialisation parameters
  • chk_hist - Pul out column histograms for a table and optionally limit by owner and column
  • chk_invalid - count and details of invalid objects. Run before and after releases to see if you broke anything!
  • chk_jobs - show the jobs set to run on that instance
  • chk_longops - list long running operations. Only works where a step in the operation runs for a long time
  • chk_obj_frag - Lists all non-sys objects with more extents than the supplied value. This should be pretty redundant now. But it ain't
  • chk_pga - display information about PGA statistics
  • chk_ref_by - display foreign keys pointing to the uniqe/pk indexes on stated tables
  • chk_sess_count - current number of sessions and max so far
  • chk_sga - List out contents of SGA ordered by whichever order clause you comment back in. I usually check for most Buffer gets per execution or row returned
  • chk_sga2 - As chk_sga but displays average buffer gets and disc gets per execution. Useful to see if an alteration to indexes/init params is causing the sql to perform better
  • chk_sga_disk - As chk_sga but ordered by most disc io for each row returned
  • chk_sga_today - list contents of the SGA ordered by load time (when it was first parsed) most recent first
  • chk_sga_txt - SQL out of the SGA that containts the provided sql. Ignores case. Can take a few seconds to run. V useful for getting sql you have changed and are testing
  • chk_sga_usage - size of the components of the SGA
  • chk_snaps - snapshots (as in statspack) snapshots you have
  • chk_stats - tables and paritioned which had stats gathered in the last 10 days, most recent first. Use to see if some sql gone rogue is using some recently analysed table
  • chk_usr_pga - Get the PGA data for a given sid
  • chk_waits - systems event waits (total since startup). Run twice to get info for a delta
  • col_lst - columns and that tables they appear in for a stated column name
  • col_lst_sys - columns and tables they appear in ONLY for sysuse to interogate the data dictionary
  • col_stats - column stats information by tables and owner
  • cons_lst - constraints on a table, ignoring ones like 'SYS%'usually notnull constraints but could be unnamed constraints (shoot the dba or developer responsible)
  • cpu_usage - Get users and the cpu they are usingnot mine, do I ever actually use?
  • cre_alt_usr_ts - Create a script to alter the temp tablespace for the provided tablespace to the other tablespace. Used when we had to flick people between locked tablespaces
  • cre_dba_users - create a set of standard database priveledged users
  • cre_dbav_usr - create a standard see anything database user. Needs updating really. Pass in pwd, assumes temp_01 and data_01 ts's exist
  • cre_drop_asyn - create a script to drop all public and private synonyms. (why?)
  • cre_drop_lsyn - create a script to drop all synonyms for the current user
  • cre_drop_psyn - create a script to drop all public synonyms
  • cre_obj_maint_pkgcreates - a package that was developed to provide useful functions or help in admin a db, eg disable and re-enable constraints on a table. Partly redundant as dbms_metadata finally arrived
  • cre_re_bld_objs - create a script to attempt to recompile invalid objects. It has no concept of dependencies. Running it several times usually fixes all that can be fixed but not always. I had a better version of this but lost it somewhere. ADB added the pause and autorun
  • cre_re_cre_sysn - script to create a script to recreate public syns
  • cre_stats_tab - harness for creating a stats holding table. Would probably need to tweak it
  • cre_test - create a set of test tables. Replace
  • cre_usr - prompts for username, pwd and default TS, creates a user with those and grants connect, resource and unlimited TS. Assumes temp_01 TS exists
  • cre_usrs - current users (actually, sessions)
  • db_size_sum - summary of database size, as allocated dbfs and actual segments. I tend to use total segments as the "real" size
  • dbf_lst - datafiles for a tablespace. Doesn't handle TB datafiles :-)
  • dbf_lst2 - attempt to update above to remove centre of file name (as usually repeats) and show if autinc on and how much by
  • dbl_lst - show database links. Under 9 it also shows pwds!
  • desc_x_tabs - a rough and ready desc for x$ tables
  • explain - harness for running explain plan. Written for V7 and updated for 8 and 9 but really getting a bit ratty
  • exp1 - I got sick of typing !explain'. slightly updated
  • find_plsql_txt - crude search of stored pl/sql for provided text. Case insensitive and can take a while to run
  • find_vw_txt - scan views for provided text. Can't do in sql as the text is stored as a long, so need to use plsql
  • find_x_cols - look for columns in the x$ tables. Only way to try and navigate the damn things
  • free_spc - shows free space in a tablespace, above a certain number of blocks (defaults to 0)
  • gather_sys-dd_fd_stats - gather system, data dictionary and fixed object stats
  • gather_sys_stats - simple system (physical) stats gathering script. No parameters, must edit before run
  • get_binds - supposed to get bind variables for cursors. But it does not. Need statistics_leve=all for it to work but it gives impression it works at lower levels
  • ind_cols - columns (in order) for an index
  • ind_lst - Simple list of indexes on a table
  • locks - supposed to be the locks on a database but it often runs slow and shows all locks which is not what you want
  • login - my default login.sql script. If it is in your sqlpath or current dir when go into sqlplus it fires (and from 10 onwards if you reconnect). NB it alters nls_date_format which can play merry hell with implicit date conversions
  • obj_dep - simple list of things dependent on the object name supplied. Should be replaced with something that looks itteritively
  • obj_lst - objects by name, inc last altered. Good for looking up "tables"
  • obj_privs - Privs granted on/to an object, by name and owner
  • obj_size - Size of objects, by name, object type and owner (defaults to user)
  • os_chk - little pl/sql script to get full OS name. More than is in v$instance. An eg of what pl/sql built-ins can now provide
  • par_dets - summary info of partitions on a stated table name, with partition name you provide (defaults to all)
  • par_dets_stats - as par_dets but gives stats info instead of size (should merge the two?)
  • par_ind_dets - summary of index paritions by index name. Currently lacks partition name filter
  • par_size - size of partitions by object name, owner and type (defaults to tables)
  • part_col_stats - cloumn stats for partitions, by owner, table name, partition name and column name
  • pkg_lst - List of pl/sql stored packages, procedures and functions by name, ignoring sys and system ones
  • pkg_txt - simple extraction of pl/sql source text by object name. NB it will list sys and system owned objects and, if not wrapped, are very interesting!
  • prm_lst - initialisation parameters by name. NB session ones or system ones?
  • rbs_dets - rollback segements. Probably almost redundant
  • runsnap - take a snapshot (statspack needs tweaking to run on 10)
  • schema_size_sum - database size information split into schemas
  • scn_chk - selects the scn in various ways and then demos the use of ora_rowscn
  • seg_dets - name, size and storage info for segments. Shows partitions but can't filter on them
  • seg_activity_info - look at the various activity stats from v$segment_statistics. Good eg of what you can do with the v$ info
  • seg_par_dets - as seg_dets but limited to and filtered by partition name
  • seq_lst - display sequences by name, ignoring sys ones
  • sga_pga_info - Pulls out various sets of information on sga and pga/uga data for oracle support should you have memory leak issues
  • show_access - List who is currently accessing what on the database. I pinched this one
  • show_auto_stat_runs - show details of schema and database stat runs. If the run took up the whole run window, it chocked on one or more segments
  • show_awr_metrics - display the names of all the metrics AWR collects. Can be used to dig into the AWR data directly
  • show_cons - Show constraints to and from the stated table on the current schema
  • show_grants - what privs have been granted on a table to whom
  • show_ish_full - Index stats history. No dba_ind_stats_hist so I created this by modifying and extending the view for dba_tab_stats_hist. By index and owner
  • show_idx_pct_change - similar to show_ish__full but shows the percentage changed and ordered by it
  • show_jobs - jobs set up on the database. NB superceeded?
  • show_locks - supposed to show locks, Not mine, seems to be slow and it is flooded with transient locks
  • show_logs - online redo log details
  • show_machine - the servers and count of connected sessions. See what app serverers are using the instance
  • show_sess - sessions currently on the system, have option to ignore background jobs
  • show_sorts - size and type of sorts being done on the db
  • show_tab_mods - table modifications since table last analyzed, only shows tables modified in last 31 days and ignored sys tables
  • show_tab_stats_hist - the times a table has been analyzed (in last 31 days) by table and owner
  • showtime - shows current time. non-standard name but kept for nostalgia
  • show_tsh_full - table stats history. Shows all dbms_stats gathers on table for last 31 days, by table and owner. NB the values are the ones REPLACED
  • show_x_tabs - show what x$ tables exist. You can't see them in dba_tables, dba_objects or even sys.obj$ as they are just exposures of memory objects
  • sid_from_osidf - show the oracle sid for a provided os user
  • sid_sga - the sql statements associated with a given sidoem screens do this better
  • snaprep - little hook for running the statspack report
  • snap_redo_ph - attempt to show the redo rate from the statspack reports by the hour. A bit dody
  • snap_stats_redo - show the redo rate between snaps. Works best if the snaps are all the same time apart. Redo is a good indicator of overall database DML workload
  • spc_sum - space allocated and free in tablespaces, by tablespace name. Useful for checking you can put a new segment in it
  • sql_for_hash - get the sql text for a sql hash. use in conjunction with the chk_sga scripts that only show the first 1000 chrs of a sql statement
  • store_save - little demo of storing and retrieving you sql*plus "set" values
  • sts_lst - pulls off all entried in v$sysstat so can compare to the values later
  • syn_lst - synonyms by name
  • tab_all_part_stats - stats, indexes and column info for table and it's partitions, by table and partition name
  • tab_all_stats - stats, index and column info for tables. Basic info needed for tuning
  • tab_chain - check for chaining in tables. Relies on the table having been analyzed, which is not likely now most sites have moved to dbms_stats
  • tab_count - table name and rowcount without doing a count(*). Pretty damned accurate uses num_rows and dba_tab_modifications. ** NEEDS THE FLUSH PUT IN IT **
  • tab_d - table description that does not spool on and off, so can do own spool and desc all the tables you are interested in before spooling off. Not been maintained
  • tab_desc_usr - describe all tables by name and user
  • tab_desc - My own replacement for the desc command, which I don't like
  • tab_det - brief details of tables
  • tab_detf - more in-depth deatils of tables. Spills over several lines. Should alter to use wider lines seeing as the days of 80chr terminals are just a little behind us
  • tab_info - info, stats and indexes for a table. Replaced with tab_all_stats
  • tab_lst - list of table names as oracle still don't provide a nice, simple command in sqlplus
  • tab_mods - the recent insert/updates/deletes to a table. Since V10.2 this data is not updated automatically, have to call the dbms_stats flush command
  • tab_sci - My core script to help me tune. Gets info on table, indexes and columns. Does not get histogram info as it can be too long and usually do not need it. NB uses utl_raw
  • template - the stub I use (if I remember) to create new sql scripts
  • trc_off - one-liners to turn tracing for the session off
  • trx_on - and on
  • trg_txt - text of triggers, by table name and owner. NB often they call packages
  • ts_conts - What segments are in a tablespace
  • ts_lst - list of tablesapaces and their storage details (needs updating)
  • ts_objs_by_size - contents of a tablespace ordered by size of object not name
  • ts_usage - How each none-sys/rbs/temp tablespace (as identified by name) is being used, eg count and type of objects in them
  • usr_lst - users and their default tablespaces
  • usr_privs - privs a user has (sys and object). Also works on roles
  • utlsampl - Oracle's emp/dept script
  • vw_lst - list of views
  • vw_txt - Text of a view. Can be extremely interesting running this on dictionary views!
  • waiters - supposed to list people waiting on locks. Not sure it works so well
  • what - List of users by os user, oracle user or sid on the instance
  • who - List of users by os user or oracle usr. Gives you the format of the kill statement