Acme Sprockets

Here are a few simple but useful SQL scripts that I've developed and accumulated over the years of working on and around Oracle databases. More to come...

drop_all_objects.sql

Use with caution; this one drops all objects from the connected user's schema. This comes in handy to clean out a schema (perhaps during development) versus dropping the user and loosing privileges and roles that have been assigned.

snap7.sql

Takes a level 7 Statspack snapshot (Oracle 9i) and prints out the snapshot id. Must be run as a user with perfstat permissions (and Statspack must be installed, of course).

ts_usage.sql

Reports total size (MB) and space available (MB) by tablespace. Must be run as a DBA user.

wr_snap.sql

10g script to take a AWR snapshot and print out the snapshot id.