Akadia Information Technology


Code Listing or Textfile
download.gif (899 bytes) Code for Download.



ANALYZE table with estimate or compute, depending on table size, see SIGN(n)

Buffer Cache Analysis - Objects (Analysis of V$CACHE)

Buffer Cache Analysis - Slot Status (Analysis of V$CACHE)

Calculate 'Average Length of the Dirty Buffer Write Queue' for Performance Tuning

Circuits trough Dispatcher to Shared Serverprozess, MTS und Shared-Server

Compare HW-Mark which is say 20% larger than the actual data in the tables (Ora7)

Compare HW-Mark which is say 20% larger than the actual data in the tables (Ora8)

Creates a script that grants a user access to the SYS tables (V$%)

DECODE as a very effizient use of IF-THEN-ELSE

Database Trigger to implement an UPDATE CASCADE with Oracle8i

Date arithmetic with Oracle (e.g. How to add 1 [sec] to a date ?)

Disable all relational constraints on tables owned by the user that executes this script

Displays an ordered list of all non-index segments > 10 MB

Displays an ordered list of the indexes on a given table

Displays database resource usage statistics (Whole instance or Session)

Displays the execution plan for a SQL DML statement

Drop all objects of the user that executes this script.

Enable all relational constraints on tables owned by the user that executes this script

Extensive Partitioning Examples for Oracle8 Partition Option

Flush Shared Pool when it reaches 60-70% of it's capacity

Formatted SELECT * FROM 'table' Statement Results

Generate 'CREATE TABLE' Script for an existing Table in the database

Generate INSERT statements for existing data in a table

Generate Script to coalesce free Space in cluttered Tablespaces

Guide for Tuning the Rollback Segments

Guide for Tuning with UTLBSTAT und UTLESTAT

How to implement "Sleep Function" with PL/SQL ?

How to query a n X m relation using the UNION construct to avoid the ORA-1417 error

How to reclaim UNUSED_SPACE from Indexes and Tables using DBMS_SPACE.UNUSED_SPACE

Install SQL*PLUS and PL/SQL Help Tables in Data Dictionary for: SQL>help command

Monitor Data Access Activities (Full Table and Index Scans, Chained Rows)

Monitor Private SQL Areas and PL/SQL space in the UGA and SGA

Monitor SQL*Net communication activities

Monitor Sort Activities (Sorts in Memory, Sorts on Disk)

NLS: Show current NLS database settings from sys.props$

NLS: Show valid NLS parameters (TERRITORY, CHARACTERSET) from v$nls_valid_values


Performance Enhancements with PL/SQL DBMS_SQL Bulk-Operations instead of Looping

Recompile all INVALID DB-Objetcs with Dependency Tracking (Very handy Script)

SQL Statement to create the PLAN_TABLE used by EXPLAIN PLAN

Script to increase a sequence above the value the related attribute has

Send E-Mail Messages from PL/SQL with Oracle 8.1.6 using UTL_TCP or UTL_SMTP

Set PRIVATE SYNONYMs to Schema of a connected Oracle User

Show 'Who owns what where' in the Database

Show 'Who uses what objects' in the Database

Show Buffer Cache Hit Ratio in % for active Instance since last Startup

Show Characteristics for SYSTEM and other Rollback Segments

Show Constraints of Tabelles for a Schema Owner which be choosen

Show Contents of the Controlfile and Oracle Data Dictionary

Show DB-Events which causing Sessions to wait

Show DB-Files with Heavy I/O (where are Hotspots of Reads und Writes) ?

Show DBMS_JOBS for all Oracle Users

Show Data Dictionary Cache Hit % for active Instance since last Startup

Show Database Space used for all Schema-Owners

Show Database Triggers for Schema Owner

Show File-I/O Rate, System-I/O Rate and Throughput on all DB-Files

Show Foreign-Key Refrences from / to Oracle Tables

Show Foreign-Primarykey Relations with Foreign Keys without an Index

Show Free List Hit Ratio in % to verify Database Block Contention

Show Highwater Mark of a table (Choose Table and Schema Owner)

Show Hit-Ratios, Consistent-Gets, DB-Block-Gets, Physical-Reads for the Sessions

Show I/O between DB-Server and Clients over SQL*Net in Bytes/s

Show INITIAL, NEXT, Total Extents, Total Blocks of DB-Objects

Show Indexes for a Schema Owner

Show Library Cache Hit % for the Shared Pool of the Instance

Show Low-level Locks (Latches) on internal shared Memory Structures

Show Memory Sort Hit % (Memory and Disc)

Show Memory allocated in [Bytes] for the whole Instance

Show Number of Logswitches per Hour and Day as a Histogram

Show Number of Objects (Tab,Ind,Syn,Vew,Seq,Prc,Fun,Pck,Trg) for each Oracle User

Show Number of Rows per Block for a Table (Only for Oracle7 ROWID)

Show Number of Transactions and other Cursor Statistics (Commits, Rollbacks, etc)

Show Number of physical Reads and Writes per Sec for each DB-File (I/O Details)

Show Object Privileges for Schema Owner which can be choosen

Show Objects and Comments from the Oracle Data Dictionary (View DICTIONARY)

Show Objects which cannot allocate NEXT Extent (ORA-01653)

Show Partition Indexes (DBA_IND_COLUMNS, DBA_INDEXES)

Show Partition Tables and Indexes (DBA_TAB_PARTITIONS)

Show Primary and Foreign Key Relationsships

Show Procedures of a Schema Owner

Show Progress Control for long running PL/SQL Procedures with DBMS_APPLICATION_INFO

Show Redo Allocation Hits in % (Redolog Tuning)

Show Redo Waits ('redo log space wait time', 'redo log space requests')

Show Roles granted to Users and Roles

Show Rollback Segment Report Usage (Nowait Hit %, Waits, Shrinks)

Show SQL-Code of CPU-Intensive Oracle Prozesses in the Memory

Show SQL-Statements in Memory for the connected Sessions (Shared Cursors)

Show SQL-Statements in Memory with I/O-intensiv SQL-Statements (V$SQLAREA)

Calculate Shared Pool Minimium Size

Show SYSTEM Privileges of Oracle-Roles and DB-User

Show Segments with critical Number of Extents, soon reaching MAX_EXTENTS

Show Sequences for Schema Owner

Show Session Statistic (Users Logged-On, Users Waiting, Users Waiting-for-Locks)

Show Sessions with bad Buffer Cache Hit Ratio in %

Show Size of each Object itself (without content) in the Database

Show Startup Time of the Oracle Instance (Different for Ora7 and Ora8: V$INSTANCE)

Show Statistics of connected Sessions (PID, Connection-Type, Username, Logon-Time)

Show Status for all Objects (VALID, INVALID) of a Schema Owner

Show Synonyms for all Schmea Owners

Show Table Grants for all Schema Owners

Show Table Structure (Column-Name, Datentyp, etc) for all Schema-Owners

Show Table and Column Comments

Show Tablespace Status Information

Show Users with High CPU Processing since Instance Startup

Show Views for Schmea-Owner

Show all INVALD Objects in the Database for all Users except SYS and SYSTEM

Show all Privileges for a connected User through Roles and direct

Show all Schmea Objects (Tables,Synonyms,Views,Sequences,Indexes)

Show block chaining (chained rows) with ANALYZE TABLE LIST CHAINED ROWS

Show columns that have the same name but different characteristics

Show complete System Statistic, e.g. Full Table Scans, Redolog Infos from V$SYSSTAT

Show detailled Report of Library Cache Usage in the Shared Pool of the Instance

Show fragmented Objects (more than 3 Extents)

Show free Space in all Datafiles and if AUTOEXTENT is ON

Show information about your current database account (who am I)

Show installed Database Version and Options with Port specific infos

Show last Checkpoints in the File Headers

Show next sequence number from sequence (without to increment it with NEXTVAL)

Shows actual DML-Locks (incl. Table-Name), WAIT = YES means that users are waiting for a lock.

Show users waiting for a lock, the locker and the SQL-Command they are waiting for a lock
Print out the lock wait-for graph in a tree structured fashion (Original UTLLOCKT.SQL)

Show the most resource intensive SQL statements that have been recently executed

Show total, free and used space in all tablespaces / database files

Show waiting Sessions blocked through other Sessions

Show which Users are accessing which Rollback Segments.


Shows SQL-Statement for connected SID/SRL# from V$SQLTEXT

Shows the User that has performed the most physical disk reads

Solutions for the "Mutation Table Problem" with DELETE CASCADE and Cascade Update

Summary of INVALID Objects ordered by Object Type

Try to set SQL_TRACE ON for another Session / Program

Tuning Redologs und Checkpoints (Contention, Waits, Number/Duration of Checkpoints)

Which ROLES are currently enabled for my Session ?