Most of our senior consultants have been working with Oracle technology for over ten years. As you can imagine, they've developed quite a few tools and scripts that they carry with them from project to project. Below, you'll find a sampling of our handy database administration scripts that are yours for the taking. Feel free to download and use them. But first, here's the part our lawyer wants you to read:

* These scripts are provided by Database Specialists, Inc. for individual use and not for sale. Database Specialists, Inc. does not warrant the scripts in any way and will not be responsible for any loss arising out of their use. By downloading or using these scripts you are agreeing to these terms and conditions.

Have comments or suggestions? Send us your feedback.

• Take a fast hot backup of your database using your filer's snapshot facility
  This script puts all tablespaces of the database into backup mode and calls upon your filer's snapshot facility to take a snapshot of the database. This allows you to take an "instantaneous" hot backup—you can then copy the snapshot to tape for archival purposes if desired. This script makes sure the snapshot backup will include a copy of the server parameter file, the control file, and a trace listing of the control file. A detailed log report indicates exactly what operations were performed and when, and which archived redo logs would be required to recover the database from the backup set.

• Compare data in two Oracle schemas, table by table and row by row
  This script reads all the rows in a specified list of tables in one Oracle schema and compares the data row for row against tables with the same names in another schema. You'll get a report with summary counts of differing rows, and SQL queries you can run to view all of the data discrepancies. This script is invaluable in situations such as comparing seed data in separate databases, validating regression test data in a QA environment, or testing data load scripts.

• Compare two Oracle schemas and get a report of all the discrepancies
  This script is a godsend when you've got several schemas that are supposed to be identical, but you just aren't sure if a little schema change might have crept into one without being replicated to all of the others. It is also useful for validating your schema creation scripts --run your creation scripts in a test environment and then use this tool to compare what you've just created against the reference schema already in use.

• Check your data for bad dates caused by the Y2K bug
  This script reads every row from every table in a database schema and reports how many rows include dates prior to 1950. If now or at some point in the past your application had Y2K bugs, you might have dates in your database with the wrong century--1900 instead of 2000, for example. This script will help you find bad dates so that you may correct them.

• Drop all schema objects, roles, synonyms, and such
  This is really handy when you want to wipe a test or replicated database clean and reload it from scratch, without having to rerun the catalog scripts and recreate the tablespaces.

• Check index fragmentation and bloat before your next tablespace reorg
  This script analyzes every index in the tablespace you specify, telling you the index's current size, number of extents, largest single extent, and (of most interest) the estimated size of the index if you were to rebuild it.

• Take a quick and easy hot backup
  This shell script reads the data dictionary in your database to make sure it is including all data files in the backup. The control file and parameter files are backed up, too. This script works on Solaris and Linux, and probably other variations of Unix as well. It's a quick, effective way to protect your data if you don't already have a comprehensive backup plan in place. Read the comments at the top of the script for important information. You'll also need two supporting scripts -- get them here and here.

• Export the database, or just the schema definitions, to a compressed file
  This is a Korn shell script (written for Solaris, but reasonably portable to other platforms) that is intended to be run by cron on a scheduled basis. The script is pretty robust; it checks for errors every step of the way and sends email if something goes wrong.

• Check the database for fragmentation, lack of free space, and other unpleasant things
  This is a Korn shell script (written for Solaris, but reasonably portable to other platforms) that is intended to be run by cron on a scheduled basis. The script checks for a few common problems that could be brewing in your database, and sends email if it spots something that requires DBA attention.