Miscellaneous SQL Scripts
- sqlplussettings.sql
Set SQLPLUS defaults
- abl_idx_size.sql
Generate ordered extents report
- active.sql
Get user processes currently rolling back
- acttran.sql
Get active transactions with rollback blocks
- all_extents.sql
Find segments which have more than a specified number of extents.
- allfiles.sql
Get log and config file information
- all_tab.sql
Script to identify everything to do with a table.
- alt_temp.sql
Get users who are using system for their temporary tablespace and convert them to use tablespace temp
- analyze_schema.sql
Analyze all tables in schema
- call_shell_java.sql
Example of how to run java code within Oracle
- chained.sql
List the number of chained rows per table
- chainning.sql
List the number of chained or migrated rows read via an index
- check_grant.sql
Report what object privileges are related to a certain user
- check_seq.sql
Check user sequences
- check.sql
Check segments and tablespaces
- check_sys_prv.sql
Show the SYSTEM privileges a certain user has
- ckcol.sql
Display all tables where a column is used.
- ckcur.sql
Check open cursors
- ckerrors.sql
Check for errors in sys.dba_errors
- ck_exist.sql
- ckfile.sql
Data files by tablespace
- ckfreesp.sql
Check contiguous free space
- ckgrant.sql
Grant select, update, insert, delete on a table
- ckind_analyze.sql
Check for un-analyzed indexes
- ckkeep.sql (Sample)
Generate listing of stored packages and procedures
- ckkept.sql (Sample)
Stored packages and procedures
- cklinks.sql
Obtain DBA_DB_LINKS info
- ckloghist.sql (Sample)
Check log history
- coalesce2.sql (Sample)
- column_diff.sql
List columns that have the same name but different characteristics
- columns.sql
Get column names and data types
- comp.sql
Compile all invalid database objects
- constraint.sql
List all constraints for the user specified table
- constraints.sql
List all constraints for the user specified table
- count.sql
List all tables in database
- cpmpare.sql
Iterate through all the tables existing in two schemas, identifying all the records in one, but not the other.
- cpu.sql (Sample)
CPU used per session in descending order
- crea_index.sql
Script for creating indexes
- creaplan.sql
Create table required by EXPLAIN PLAN statement
- creatablespace.sql
Generate script to create tablespaces
- create_tspace.sql
Simple create tablespace statement
- create_user1.sql
Create user script
- create_user.sql
Create user script
- current_sid.sql
Get current SID
- cursor_function.sql
Example of how to return a cursor from a function
- cursor.sql
Example of cursor creation.
- cur_variable_passing2.sql
Example of passing a cursor variable between PL/SQL functions
- dailymaint.sql
Daily Oracle instance maintenance scripts
- datafile3.sql (Sample)
Data files by name
- datafile4.sql (Sample)
Data files by tablespace
- datafile.sql (Sample)
Data files by freespace
- data_io.sql (Sample)
Datafile I/O statistics
- data_tran.sql
Propagate new records to other Oracle sites where there is no networking
- date.sql
Sample data-based queries
- dba_report.sql
Describe all the features of a table
- db_info.sql
Check the status of a database
- db_link.sql (Sample)
Database links
- depend_all.sql
Show the dependency tree for a given object
- depend.sql (Sample)
Table dependencies
- df1.sql
Show fragmented indexes
- dfile.sql
'Data files by tablespace
- dfrag.sql
Segment Fragmentation - 3 or greater
- dfree.sql
Free Space by Tablespace
- diffobj.sql
List objects in a schema that are not in both of two instances.
- difftab.sql
List differences in table definitions in the tables for a schema in two different instances.
- dindex.sql
Index storage parameters
- dup_check.sql
PL/SQL duplicate session check
- dup_rows.sql
Check for duplicate rows in a table
- email.sql
Send email (PL/SQL)
- executions.sql (Sample)
Show executions in the shared pool
- explain.sql
EXPLAIN PLAN example
- export.sql
PL/SQL schema exporter
- extents2.sql
List extents
- feespace.sql
Show the allocated, used, and free space for every tablespace
- files.sql
Show control files
- findpkg.sql
Find package
- foreign_key.sql
List all foreign keys that currently exist in the database without the foreign key columns indexed in the child table
- frag.sql
Check for fragmented database objects
- free2.sql (Sample)
Tablespace free space
- free_space2.sql
Tablespace free space
- freespace2.sql
Tablespace fragments
- freespace.sql (Sample)
Tablespace free space
- free.sql (Sample)
Tablespace free space
- fullscan.sql
Provide information on full table scan activity
- get_time.sql
Get time (PL/SQL)
- gl.sql
Get Lawson GL account info
- grantee.sql
Report what OBJECT privileges are related to a certain user
- ibrary.sql
Determine if effectively using shared sql area of sga
- index2.sql
List all indexes
- index.sql
List indexes for a selected table
- init_sel.sql
Get user privilege information
- invalid_java.sql
Find invalid Java
- invalid.sql (Sample)
Find invalid SQL
- io.sql (Sample)
I/O report
- jq.sql
Display info about jobs currently running
- keep_gc.sql
PIN all procedures/packages in SGA (PL/SQL)
- keep.sql
PIN all procedures/packages in SGA (PL/SQL)
- kill_session.sql
Kill a session (PL/SQL)
- kill.sql
Kill a session based on SID and serial number (PL/SQL)
- last_seq.sql
Get sequence numbers
- ldctrl.sql
Generate load control script for a table
- lib.sql (Sample)
Examine cache activity by monitoring the sums of the GETS and GETMISSES columns
- lock.sql
Get locked objects
- log.sql (Sample)
Get log information
- long.sql
Convert LONG columns (PL/SQL)
- mem_usage2.sql (Sample)
Memory usage
- mem_usage.sql (Sample)
Memory usage
- next.sql
Tables with next extent greater than max free space in tablespace
- noindex.sql
Lists all tables that do not have any indexes
- non_index.sql
List all indexes that have the same leading column on a table and may be superfluous.
- obj_access.sql
List objects and their owners
- objcount.sql
Count object types
- objpinned.sql
Objects pinned in shared pool
- obj_size.sql
Get size of an object
- objstor.sql
Object storage information
- oerr.sql
Get Oracle error messages
- onlineredo1.sql
Get online redo information
- onlineredo.sql
Get online redo information
- outer.sql
Example of an outer join
- package_memory.sql (Sample)
Display used SGA memory for triggers, packages, and procedures.
- password.sql
Example of altering a password
- pending.sql
Report on any pending distributed transations.
- pk.sql
Get package info
- plan.sql
Example of create rows in PLAN_TABLE
- privileges.sql
Determine the object privileges given to a user
- process.sql (Sample)
Get user processes
- prod_db_diag.sql
Examine various V$ parameters
- random.sql
Create package random (PL/SQL)
- rback.sql
Display info about rollback segments
- rbk_lcks.sql (Sample)
Rollback lock info
- rbk.sql
Produce a report of the RBS occupying more than a given threshold
- rb.sql
Rollback segments
- redo.sql
Online redo logs
- re_index2.sql
Rebuild an index
- reseq.sql
Get sequence names and numbers
- resource.sql (Sample)
Get resource info for a user
- roleinfo.sql
Information about roles - Roles, Privileges, assigned users, etc. (PL/SQL)
- rollback.sql (Sample)
Rollback info
- rollstat.sql
Rollback status
- run_this.sql
Examine various V$ parameters
- schema.sql
Generete schema from database
- see_active_sql.sql (Sample)
View active SQL queries
- see_rollback.sql (Sample)
Tie Oracle rollbacks to UNIX processes
- see_sql2.sql (Sample)
Find your active processes
- seesql2.sql
Find active processes by user
- seesql3.sql
Monitor a user's cursor statement
- see.sql
Determine if effectively using the data dictionary cache of the sga
- session1.sql
Display all connected sessions
- session3.sql
Find session using PID
- session4.sql (Sample)
Display session info
- session.sql (Sample)
Display session info
- session_time.sql (Sample)
Display session info with login time
- sga.sql
Get SGA info
- show_active_trans.sql
Show active (in-progress) transactions
- space_usage.sql
Prints the amount of tablespace, grouped by segment type consumed by each user
- sql_hint.sql
Examples of SQL HINT syntax
- sys_priv.sql
Show the SYSTEM privileges a certain user has
- sysseg.sql
List all segments that reside in the system tablespace
- sysuser.sql
Users with default tablespaces's in system tablespace
- table_size.sql
Get table sizes
- table.sql
Generate CREATE TABLE statements using Catalog tables
- table_usage2.sql
Tells you how often specific tables are loaded into memory and the number of times those tables have been executed.
- tabview.sql
Produce a report of tables for a given database owner and table search string
- topsql.sql (Sample)
List all statements that are taking longer than 2 seconds to scan through the buffer cache
- trace.sql
Example of doing a trace
- tune_sga.sql
Query the SGA of an instance and advises if modifications are needed in the (PL/SQL)
- tune.sql
Monitor a database (PL/SQL)
- users.sql
List of all database users
- user_system_tab.sql
list of users who own objects in the SYSTEM tablespace
- view_tran.sql
Display active transactions
- vsqltop.sql
Shared SQL Area - Top SQL Statements
- waiter.sql
Waiting transactions
- wait.sql
Waiting transactions
- whologin.sql
Who is logged in