Welcome to my GeoCities Page. I hope you find something useful.
go to SQL scripts
go to UNIX scripts
July 26, 2005
Here is another script for extracting source code. This one reads trigger source.
July 26, 2005
I appear to be updating every second month. OK, I will update today and this Thursday.
In the days before version control (which, around here, probably includes today), developers were occasionally at a loss to find their source code. Oh, there were some local files people had been using, some stuff was in a repository somewhere, and there were always the export files. But I was occasionally beleaguered with a request like "I need you to replace this development database. Can you save all the code in it?"
So how was I supposed to do that? Well, here was my answer.
May 24, 2005
Has it been that long since I last updated? Oh well, ...
Today I have a script set that nicely complements df_use.sql: a tablespace map. It lets you see how your segments and free space are distributed in your datafiles.
April 13, 2005
Have you ever been bothered by poorly performing SQL? OK, so that question was probably rhetorical. I don't know one DBA that has not griped about this. But the real question is "How do I find it?"
As you have already guessed, that is today's script.
Oh, and I changed the background colour. Why not, it is 35 years ago today that Apollo 13 had its accident (a completely pointless reference, but then is there really such a thing as "cause and effect"?).
April 11, 2005
Another short update.
I am adding a tiny UNIX shell script today. A friend down in Texas and I were comparing scripts. We both had one that would list the active instances on a UNIX server. So I am adding my version today.
df_use.html
A older script to help you determine how much space you are using in your Oracle datafiles.
Warning: If you have your files set to autoextend to incredibly large sizes, the ascii graph will look blank.
TsMap.sql
TsMap.1.sql
TsMap.2.sql
TsFSMap.sql
TsFSMap.1.sql
This script set displays the contents of your datafiles in the order that they are stored. It produces a tabular map of individual segment and free space extents. It also displays some general information about the tablespace and its datafiles.
Because I am sometimes interested only in the free space, I have included a variation that maps only the free space.
I find these scripts most useful for detecting tablespace fragmentation or removing excess free space from a datafile.
Be sure to store the scripts in a single directory. The scripts to call are the wrapper scripts TsMap.sql and TsFSMap.sql.
scripts
top
rollback_stat.sql.html
This incredibly useful script by Tim Onions gives you a wonderful overview of how your rollback segments are working.
Of course, if you are now using UNDO tablespaces, it is not so useful ...
Here is a sample report.
scripts
top
mkCnst.zip
mkCnst.sql.txt
flipTrigs.sql.txt
flipCnst.sql.txt
isql_prefs.sql.txt
spoolPrep.sql.txt
This script set will enable or disable table triggers and/or referential integrity constraints for the entire database (SYS and SYSTEM excluded). The main script is "mkCnst.sql"
I promise to clean this up later. I have to get home now to get ready for my son's birthday party tomorrow.
scripts
top
This interactive script will find resource expensive SQL currently in your instance. You get to decide what criterion to use and what level is considered unacceptable. Then the script presents you with a formatted report. Each statement gets it own page. It is designed for SQL*Plus. The report is formatted for a linesize of 120 characters.
This script will create source code files for all stored procedures, functions, and packages in the database.
It was originally written for Oracle7. I don't know how it reacts with types or java source. You have the source and the idea, anyway. I am sure that the industrious find and fix any bugs.
getActiveSid.ksh
This one-liner will return the SID's of all active Oracle instances on your UNIX server (so long as nobody is running a program called "ora_lgwr_*").
Because it writes to STDOUT, you can insert into other scripts, e.g.:
for Sid in $(getActiveSid.ksh) do # your loop instructions go here done ;