What's the fuel that keeps your Oracle engines running? Code, of course. This is the place to find scripts that can keep your system performing its best! To use these scripts, please select (highlight) the script in the article and copy it to your own file or view the document source of the article and then copy the script.
 
 
 
1996 Code Index 
12/20/96 Finding Module Dependencies in Designer/2000 by Bibhu Varma
12/13/96 Viewing Table Indexes by Chip Dawes
12/06/96 Reporting Table Constraints by Trevor McCloy
11/27/96 Using Decode to Suppress Unwanted Text by Tony Fogg
11/22/96 Crypting Data with Masks by Andre Caldas Oliveira
11/15/96 Converting A 7-Bit Charset Database to an 8-Bit Charset Database by Venkat S. Devraj
11/08/96 Providing Multi-Language Support For Forms And Reports In Financials R10 by Simon Ambridge
10/25/96 Collection of Scripts for New/Junior DBAs by Michael McClure
10/18/96 Providing Users with Session Information by Greg Scholey
10/11/96 Creating Scripts to Recreate a Table Structure by Ottar Sorland
10/04/96 Printing a Hierarchical List of Referring and Referenced by R. Wayne Linton, I.S.P.
09/27/96 Finding Columns that are Not Part of Unique Keys by Ta Murari
09/20/96 Dropping Database Objects for a User by Mike O'Reilly
09/13/96 Printing Dependencies for Database Objects by Lasse Ahvenainen
09/09/96 Forecasting on Next Extents by Xueli Zou
09/06/96 Foreign Key Creation Script by Govind Padmavijayam
08/19/96 Powering DBMS_OUTPUT.PUT_LINE by Xueli Zou
08/10/96 Download Data from Database by Govind Padmavijayam
08/09/96 Foreign Key Creation Script by Govind Padmavijayam 
08/02/96 Drop All Database Objects for a User by Henri M. B. van den Bulk 
07/30/96 Using C to Generate SQL*Loader Script by Argyris Derlopas
07/29/96 Creating a Backup of Views by Argyris Derlopas
07/26/96 Creating a Database Layout by Jeff Hancock
07/15/96 Using Dynamic SQL in PL/SQL: Resolving Problems with Parse Procedure by Debabrata Panda 
07/08/96 Generating a Hierarchical List of Tables by Sambasiva Chebrolu
07/08/96 Replacing SQL Scripts When Recreating a Database by Wes Baird
06/28/96 Available Space, Fragmentation, and Extents by Ottar Sorland
06/27/96 Updating or Deleting a Record in Developer/2000 Forms
06/26/96 Out of Shared Memory
06/21/96 Exporting With Compress Under UNIX
06/10/96 Sorting and Ordering Hierarchical Data by Janco Tanis
06/07/96 Creating a Stored Procedure to Analyze Tables by Christopher Soza
05/17/96 Changing Passwords for Oracle Users by Shankaran Iyer
05/09/96 Make a Map of Your Oracle Files by Dennis Cheung
05/09/96 Change the Initial Extent to Create a Test Database by James Koopmann
05/06/96 Creating Statistics for the Cost-Based Optimizer by Rick Godfrey
04/15/96 How To Recreate the Control File
 
 
 
1997 Tip Index
12/15/97 Viewing Table Sizes (Foxpro Style) by Shalabh Bhatnagar
12/02/97 Image Format Conversion using Forms 4.5 by Barinder Singh
11/24/97 Automatically Deleting Old Archivelogs by Venkata Balachandran
11/14/97 Generating a Table Usage Report by Tim Bateson
11/07/97 Changing Instance Name and Starting Multiple Instances by Barinder Singh
10/31/97 Displaying Connected User Information by R. Wayne Linton
10/24/97 Viewing the Dependency Tree by Marcus Bulach
10/24/97 Viewing Free Space for Tablespaces and Datafiles by Milind Kshirsagar
10/17/97 Listing Any Users System Privileges by Hari Vetsa
10/10/97 Monitoring Session Statistics by Matt Reeves
08/01/97 Show List of Locks by Users byHerv Debarre 
07/18/97 Computing Exact Table Size by Lakshmi Munugoor
07/11/97 Finding Product of a Column & Finding the nth Maximum Value of the Field by Srivatsa S.
07/07/97 Viewing Extent Usage and Availability by Ahbaid Gaffoor
06/24/97 Recompiling Invalidated Objects by Robert Cordingley
06/18/97 Another Way to Check the Percentage of Free Space by Karim Shaharidan
06/06/97 Dropping Rollback Segments with "needs recovery" Status by Muriel Thijssen
05/23/97 Show List of Locks by Users by Herve Delbarre
05/16/97 Listing SQL*NET Aliases from Your Name Server by Bob Goetz
05/09/97 Finding Non-Index lookups by Sridevi Potu
05/02/97 Migrating a Database to a New Mount Point: version 2 by Ravi Swaminathan
04/25/97 Displaying User Constraints and Index Reports by Shanmukha Immadi
04/18/97 Viewing Oracle Processes by Andreas Kokkinos
04/17/97 Finding the Non-Indexed Foreign Keys by Amanpreet Singh
04/11/97 Migrating a Database to New Mount Points by Sanjay Garg
04/04/97 Amazing Power of Decode by Murali O
03/28/97 Making Export Backups to Tape by C.S. Rajasekaran
03/21/97 Optimized Table Comparisons Using In-Line Views by Diby B. Malakar
03/14/97 Another Way to Delete Duplicate Rows from a Table by Nick Butcher
03/07/97 Expected Table Size by Ram Kumar Prasad
02/28/97 Determining Database Startup Time by Jay Mehta
02/21/97 Preloading Commonly Used Libraries by Venkat S. Devraj
02/14/97 Deleting Duplicate Rows from a Table by Devendra Garg
02/07/97 Viewing Occupied Tablespace by Josep Arques
01/27/97 Dropping Objects in a Schema According to Dependencies by Christian Menaldo
01/27/97 Checking SQL*Plus without Affecting the Contents of the SQL Buffer by Brian Ewins
01/10/97 Describing a Table's Primary Keys With SQL*Plus (version 2) by Zhang Xiu Yan
01/03/97 Describing a Table's Primary Keys With SQL*Plus by Arul Ramachandran and Andy Harmon
 
1997 Code Index
12/15/97 Deleting Old Archive Logs in Unix by Suvas L.
12/02/97 What Are Your Users Doing? by Jayshree Ravi
11/24/97 Mapping Physical UNIX Devices and Oracle Datafiles by Darlynna Swarts
11/14/97 User Activity at a Glance by Jayshree Ravi
11/07/97 Automating a Cold Backup When Running Peoplesoft HRMS by Jyothi Eppalapalli
10/31/97 Getting Entity and Attribute Relationships Without ER Software by Nagesh Anupindi
10/24/97 Amazing Power of Decode v2 by Anoop Shekhar
10/17/97 Producing Owner's Data Dictionary by Jim Lopatosky
10/10/97 Generating Index Re-creation Schema by Shanmukha Immadi
08/01/97 Full User Details for Oracle Applications by Raghu Eppalapalli
07/18/97 Eliminating Duplicate Rows From Large Tables i> y Joseph Lutz
07/11/97 An Implementation of the strtok Function by Radu Caulea
07/07/97 Compare Two Schema Definitions v.2 by Ottar Sorland
06/24/97 Adjust Sequence Value by Henry Ballen
06/18/97 Determining Instance Duration Time by Eric Chorng-En Chang
06/06/97 Developer/2000 Modules Stored in the Database by Hiroshi Komatsu
05/23/97 Create Table Script Generator by Wai Man Law
05/16/97 Automating Hot Backups by rios Derlopas
05/09/97 Tables with Chained Rows by Rex Ng
05/02/97 Identifying Phantom Synonyms by Srinivasan Sadasivam
04/25/97 Compare Two Schema Definitions by Ottar Sorland
04/11/97 Easier Way to Compile the Whole Schema Regardless of Dependencies by Sunil G. Mehta
04/04/97 Tighter Security for ctxct by Peter Mage
03/21/97 Sequencing Object Dependencie by Ram Kuma
03/14/97 Checking the Status of SQL*Net by Pinckney K. Ma
03/07/97 Performance Tuning by Ms. B. Hemasubalakshmi
02/28/97 Reverse Engineering a Table Creation Script from the Data Dictionary by Ranjit Panigrahi
02/21/97 Validating in Designer 2000 by Wim Blanken and Piet de Groot
02/07/97 Displaying Statistics Against Tables by Trevor McCloy
01/10/97 Ordering by a Non-Base Table Column by Thirumalai Murari
01/03/97 Viewing Occupied and Free Space of Tablespaces by Ari Arantes Filho
 
 
 
 
 
 
1998 Tip Index 

 
12/23/98 Analyze Tables Based on Table Size by Jeffrey Beckstrom
12/15/98 Avoiding Order By (Sort) on Full Table Scans by Mehul Zaveri
12/07/98 Generating Remote or Distributed Database Reports by Ratan Makker
11/30/98 Create a Trigger to Save User System Date on Record by Prapon Laobooncharoen
11/21/98 Determining the Right Arguments for a Function by Sobhan Khan
11/11/98 Dynamically Setting the Transaction to Use the Largest Rollback Segment in the Oracle Database by Neminath Gadgade
11/04/98 View Disk I/O For Each Datafile by Jayant Kulkarni
10/28/98 Truncate a Table from a PL/SQL Program by Hector Gonzalez
10/17/98 Convert Numbers into Thai by Jagdeep Singh Anand
10/06/98 Faster Database Export on UNIX by Kimmy Chan
09/30/98 Viewing Free Space (Revisited) by Marcel-Jan Krijgsman
09/23/98 Locating Gaps Between Numbers by Devendra Vaidya
09/16/98 Optimally Tuning the SGA by Biyi Iredele
09/09/98 Adding New Columns to Existing (Empty) Tables Without Destroying User Privileges by Craig Coleman
08/27/98 Coalescing Free Space by Marci Greenberg
08/20/98 Viewing Session Information by Ensie McGathey
08/12/98 Suppressing the Parameter Form in Reports 2.5 by Anunaya Shrivastava
08/05/98 Creating Public Synonyms by Jeremy Bettis
07/24/98 Enable/Disable Schema Triggers by Balamurugan Palani
07/07/98 Analyzing Free Space in Tablespaces Over Three Months by Kaushik Mukhopadhyay
06/23/98 Viewing Active Transactions in a Rollback Segment by Kathy Shah
06/11/98 Getting Information About Your Target Database for Backup & Tuning by Anil Koranath
05/27/98 Checking Oracle Background Processes by Suvas L.
05/19/98 Re-Start Your Sequences by Andy Asensio
05/05/98 Finding Missing References by Srinivasa Rao Sarvasiddhi
04/27/98 Hiding your PL/SQL by Somu Chockalingam
04/09/98 Sending Messages to the DBA About Export Backup Status by Pankaj Mandalia
04/02/98 Making Daily Exports to Disk by Christine Campo
03/09/98 A Lockpick for DBAs by Andy Barker
02/27/98 Describing All Enabled Constraints for a Table (With Pause) by Pramod Dhulked
02/13/98 A Generic Script for Creating Indexes by Shamik Kacker
02/13/98 Making a Table Read Only by Wilson Ng
01/30/98 Loading Image and Text Files Using SQL Loader by Pankaj Garg
01/23/98 Compressing an Export File While Export is Running, Part II by Inderpal S. Johal
01/23/98 Reporting Percentage of Rows Chained by Stan Tomasiak
01/16/98 Finding the Nth Largest Value by Pankaj Malik
01/16/98 Exporting a Database That's More Than 2GB When Compressed by Devarajan Sundaravaradan
01/09/98 Determining a User's Object and System Privileges by Krishnakumar Govindan
01/05/98 Compressing an Export File While Export is Running by Balaji Bollu
 
1998 Code Index 

 
12/23/98 Tablespace Growth Analysis by Richard Larocque
12/15/98 Schema Cleaner by Alexandre Junqueira
12/07/98 Generating CREATE USER DDL Statements by Ted Martin
11/30/98 Avoiding ORA-1555 Error When Deleting Large Tables by Ujwal Shrivatsa
11/21/98 Rollback Segment User/Transaction by Shaleen Garg
11/11/98 Recreating Indexes for Schema in Another Tablespace by Sergey Alentyev
11/04/98 Determining Which Objects Are Low on Extents by Michael Jenkins
10/28/98 Rebuilding Rather Than Recreating An Index by Val Soreno
10/17/98 Determining Transaction Processing Rate by Manish Varma
10/06/98 Mapping Roles by Simon Clifford
09/30/98 Generating Matrix & Nested Matrix Reports Using SQL Script by Dhananjay Papde
09/23/98 Recompiling Objects by Greg Kainz
09/16/98 Checking Parent Columns for Child Table, R_Constraints by Quan Yang
09/09/98 Breaking the 255 Byte Put_Line Barrier by Daniel Clamage
08/27/98 Viewing Free Space, Largest Extent, and Fragment Information in One Report by Fan Zhang
08/20/98 Filling Your Small Free Extents by Laszlo Jagusztin
08/12/98 Viewing the Details of Plan Tables by K. Srinivasan
08/05/98 Available Object Space Plus by Angelo Marcotullio
07/24/98 Dropping Database Objects for a User by Heiko Bacher
07/07/98 Listing the Most CPU-Intensive Oracle Processes by Renzo Carta
07/07/98 Maintaining the Product_User_Profile Table by Wayne Linton
06/23/98 An Automated Data-Copying Utility for Complex RI-Driven Environment by Venkat S. Devraj
06/23/98 Calculating the Average Row Size by Venkat Kambalapally
06/11/98 Monitoring Your Database Performance Duncan Berriman
06/11/98 Dynamically Populate Records by Changing One or Many List Items in Oracle Forms 4.5 by Mark Falvo
05/27/98 Data Transfer Between Oracle Schemas by Pradeep Pujari
05/05/98 Automatically Adjusting the Sequences in User Schema by Jason Jia
04/09/98 Catching Run-Time or User-Defined Errors by Manish Shah
04/09/98 Recreating Referential Constraints that Refer to a Table by Duncan Berriman
04/02/98 Determining Free Space for a Next Extent by Subhamoy Mukherjee
04/02/98 Creating A Tablespace Summary Report, Part II by Renauld Chapellier
03/09/98 Showing Table Privileges For Users and Roles by Simon Clifford
03/09/98 Tracing User Sessions by Inderpal Singh Johal
02/27/98 Using the Amazing Power of Decode to Compare Addresses, Version 2 by David Hicken
02/27/98 Creating a Tablespace Summary Report for Any Oracle Database by Arun Kumar R.
02/12/98 Creating a Database Link by Martin Sweeney
01/30/98 Finding the Nth Largest Value, Part II by Atman Brahmachari
01/30/98 An API for Dynamic User-Defined Queries and Updates in Oracle by Saurabh Banerjee
01/23/98 Two Ways to Convert Numbers to Words by Goyal Manish and Philip Raj
01/16/98 Managing Oracle Logfiles by Gerhard Möller
01/09/98 Using the Amazing Power of Decode to Compare Addresses by Murthy S.R. Ravikanti
01/05/98 Creating Tablespace Summary Reports by Dave Sheppard
 
 
 
 
 
1999 Tip Index 

 
04/27/99 Creating Virtual Views with DEFINE by Kam Onn Siew
04/20/99 Schema Builder, Reverse Engineering by Razi Chaudhry
04/13/99 Global Update of Numeric/Character Fields Which Are Part of Primary/Foreign Keys by Jayanta Sengupta
04/08/99 Viewing Critical Object Extents by Marco Pagan
BONUS Generating DDL to Recompile Invalid Database Objects, Part IV by Paul Couchman
BONUS An Enhancement to Recompiling Invalid Database Objects II by David Scerba
04/02/99 Adding One Second to a Date Field by Richard Alercia
03/30/99 Releasing Exclusive Locks and Killing Sessions From Oracle by Sivasubramaniyan Sundar
03/12/99 Oracle Connectivity Using JDBC OCI Driver by Krishna Kumar Govindan
03/04/99 Identifying Locks Based on the Session's SQL Addresses by Bobby Akbari
02/25/99 Viewing The SQL Statement Of The CPU-Intensive Oracle Processes by Jayant Kulkarni
BONUS Note on Speeding up Triggers by Tony Carter
BONUS Generating DDL to Recompile Invalid Database Objects, Part III by Frank Puechl
02/18/99 Generating DDL to Recompile Invalid Database Objects, Part II by Bruce Mulder
02/04/99 Creating Server-Side JavaScript Libraries by Jason Bennett
01/28/99 Generating DDL to Recompile Invalid Database Objects by Ron Warshawsky
01/18/99 Enable SQL Trace for a Session Other Than Your Own by Joseph Prabhakar
01/08/99 Speeding Up Triggers by Shravan Miriyala
 
1999 Code Index 
04/27/99 Dynamically Executing "Create Tables" by Sripad Kamat
04/20/99 Changing Database Password in a Web Server Application by Debu Panda
04/13/99 Reporting Index Storage Usage by Steven Orr
04/08/99 Viewing Datafile Usage With an ASCII Graph by Jon Hickam
04/02/99 Creating DDL Script for Foreign Key Constraints in a Schema by Manmohan Jalsingh
03/30/99 Comparing Data in Two Schemas by Vadim Tropashko
03/12/99 Using Views, PL/SQL Functions and Forms Field Property to Reduce Network Traffic by Ashay Salunke
03/04/99 Listing Memory Used by All Sessions by Frank Bommarito, submitted by Jan Caviness
02/25/99 Estimating Table Size (Procedure) by Kari Pannila
02/18/99 Converting Multiple Formats String Dates to Y2K-Compliant Dates by Ramakrishna Kolluru
02/04/99 Monitoring Growing Rollback Segments by Bruno Vroman
01/28/99 Resetting a Sequence to One by Craig Coleman
01/18/99 Tablespace Saturation Alert by Jeffrey Tarnok
01/08/99 Testing Compressed Export Backups by Rajeev Srivastava
 
 
 
 
10 Scripts Every DBA Should Have  

by Kevin Loney 

  1. Display the Current Archivelog Status 
  2. Creating a Control File Trace File 
  3. Tablespace Free Extents and Free Space 
  4. Display Allocated Space and Quota by User 
  5. Show Allocated Storage for All Objects 
  6. Map a Tablespace's Used and Free Space 
  7. Blocks Used by Data in a Table 
  8. Reset a User's Password 
  9. Query V$PARAMETER 
  10. Show Statement Execution Path 
All specific technical questions from users of Oracle supported products should be directed to Oracle's Technical Support Department. Oracle does not provide any warranty as to the accuracy of any information provided through Oracle Magazine Interactive. Oracle shall not be liable for any damages incurred as a result of reliance on any information provided herein.
Copyright © 1999 Oracle Corporation. All Rights Reserved.