Miscellaneous SQL Scripts


  1. sqlplussettings.sql
    Set SQLPLUS defaults

  2. abl_idx_size.sql
    Generate ordered extents report

  3. active.sql
    Get user processes currently rolling back

  4. acttran.sql
    Get active transactions with rollback blocks

  5. all_extents.sql
    Find segments which have more than a specified number of extents.

  6. allfiles.sql
    Get log and config file information

  7. all_tab.sql
    Script to identify everything to do with a table.

  8. alt_temp.sql
    Get users who are using system for their temporary tablespace and convert them to use tablespace temp

  9. analyze_schema.sql
    Analyze all tables in schema

  10. call_shell_java.sql
    Example of how to run java code within Oracle

  11. chained.sql
    List the number of chained rows per table

  12. chainning.sql
    List the number of chained or migrated rows read via an index

  13. check_grant.sql
    Report what object privileges are related to a certain user

  14. check_seq.sql
    Check user sequences

  15. check.sql
    Check segments and tablespaces

  16. check_sys_prv.sql
    Show the SYSTEM privileges a certain user has

  17. ckcol.sql
    Display all tables where a column is used.

  18. ckcur.sql
    Check open cursors

  19. ckerrors.sql
    Check for errors in sys.dba_errors

  20. ck_exist.sql

  21. ckfile.sql
    Data files by tablespace

  22. ckfreesp.sql
    Check contiguous free space

  23. ckgrant.sql
    Grant select, update, insert, delete on a table

  24. ckind_analyze.sql
    Check for un-analyzed indexes

  25. ckkeep.sql     (Sample)
    Generate listing of stored packages and procedures

  26. ckkept.sql     (Sample)
    Stored packages and procedures

  27. cklinks.sql
    Obtain DBA_DB_LINKS info

  28. ckloghist.sql     (Sample)
    Check log history

  29. coalesce2.sql     (Sample)

  30. column_diff.sql
    List columns that have the same name but different characteristics

  31. columns.sql
    Get column names and data types

  32. comp.sql
    Compile all invalid database objects

  33. constraint.sql
    List all constraints for the user specified table

  34. constraints.sql
    List all constraints for the user specified table

  35. count.sql
    List all tables in database

  36. cpmpare.sql
    Iterate through all the tables existing in two schemas, identifying all the records in one, but not the other.

  37. cpu.sql         (Sample)
    CPU used per session in descending order

  38. crea_index.sql
    Script for creating indexes

  39. creaplan.sql
    Create table required by EXPLAIN PLAN statement

  40. creatablespace.sql
    Generate script to create tablespaces

  41. create_tspace.sql
    Simple create tablespace statement

  42. create_user1.sql
    Create user script

  43. create_user.sql
    Create user script

  44. current_sid.sql
    Get current SID

  45. cursor_function.sql
    Example of how to return a cursor from a function

  46. cursor.sql
    Example of cursor creation.

  47. cur_variable_passing2.sql
    Example of passing a cursor variable between PL/SQL functions

  48. dailymaint.sql
    Daily Oracle instance maintenance scripts

  49. datafile3.sql         (Sample)
    Data files by name

  50. datafile4.sql         (Sample)
    Data files by tablespace

  51. datafile.sql         (Sample)
    Data files by freespace

  52. data_io.sql         (Sample)
    Datafile I/O statistics

  53. data_tran.sql
    Propagate new records to other Oracle sites where there is no networking

  54. date.sql
    Sample data-based queries

  55. dba_report.sql
    Describe all the features of a table

  56. db_info.sql
    Check the status of a database

  57. db_link.sql     (Sample)
    Database links

  58. depend_all.sql
    Show the dependency tree for a given object

  59. depend.sql     (Sample)
    Table dependencies

  60. df1.sql
    Show fragmented indexes

  61. dfile.sql
    'Data files by tablespace

  62. dfrag.sql
    Segment Fragmentation - 3 or greater

  63. dfree.sql
    Free Space by Tablespace

  64. diffobj.sql
    List objects in a schema that are not in both of two instances.

  65. difftab.sql
    List differences in table definitions in the tables for a schema in two different instances.

  66. dindex.sql
    Index storage parameters

  67. dup_check.sql
    PL/SQL duplicate session check

  68. dup_rows.sql
    Check for duplicate rows in a table

  69. email.sql
    Send email (PL/SQL)

  70. executions.sql     (Sample)
    Show executions in the shared pool

  71. explain.sql
    EXPLAIN PLAN example

  72. export.sql
    PL/SQL schema exporter

  73. extents2.sql
    List extents

  74. feespace.sql
    Show the allocated, used, and free space for every tablespace

  75. files.sql
    Show control files

  76. findpkg.sql
    Find package

  77. foreign_key.sql
    List all foreign keys that currently exist in the database without the foreign key columns indexed in the child table

  78. frag.sql
    Check for fragmented database objects

  79. free2.sql     (Sample)
    Tablespace free space

  80. free_space2.sql
    Tablespace free space

  81. freespace2.sql
    Tablespace fragments

  82. freespace.sql     (Sample)
    Tablespace free space

  83. free.sql     (Sample)
    Tablespace free space

  84. fullscan.sql
    Provide information on full table scan activity

  85. get_time.sql
    Get time (PL/SQL)

  86. gl.sql
    Get Lawson GL account info

  87. grantee.sql
    Report what OBJECT privileges are related to a certain user

  88. ibrary.sql
    Determine if effectively using shared sql area of sga

  89. index2.sql
    List all indexes

  90. index.sql
    List indexes for a selected table

  91. init_sel.sql
    Get user privilege information

  92. invalid_java.sql
    Find invalid Java

  93. invalid.sql     (Sample)
    Find invalid SQL

  94. io.sql     (Sample)
    I/O report

  95. jq.sql
    Display info about jobs currently running

  96. keep_gc.sql
    PIN all procedures/packages in SGA (PL/SQL)

  97. keep.sql
    PIN all procedures/packages in SGA (PL/SQL)

  98. kill_session.sql
    Kill a session (PL/SQL)

  99. kill.sql
    Kill a session based on SID and serial number (PL/SQL)

  100. last_seq.sql
    Get sequence numbers

  101. ldctrl.sql
    Generate load control script for a table

  102. lib.sql     (Sample)
    Examine cache activity by monitoring the sums of the GETS and GETMISSES columns

  103. lock.sql
    Get locked objects

  104. log.sql     (Sample)
    Get log information

  105. long.sql
    Convert LONG columns (PL/SQL)

  106. mem_usage2.sql     (Sample)
    Memory usage

  107. mem_usage.sql     (Sample)
    Memory usage

  108. next.sql
    Tables with next extent greater than max free space in tablespace

  109. noindex.sql
    Lists all tables that do not have any indexes

  110. non_index.sql
    List all indexes that have the same leading column on a table and may be superfluous.

  111. obj_access.sql
    List objects and their owners

  112. objcount.sql
    Count object types

  113. objpinned.sql
    Objects pinned in shared pool

  114. obj_size.sql
    Get size of an object

  115. objstor.sql
    Object storage information

  116. oerr.sql
    Get Oracle error messages

  117. onlineredo1.sql
    Get online redo information

  118. onlineredo.sql
    Get online redo information

  119. outer.sql
    Example of an outer join

  120. package_memory.sql     (Sample)
    Display used SGA memory for triggers, packages, and procedures.

  121. password.sql
    Example of altering a password

  122. pending.sql
    Report on any pending distributed transations.

  123. pk.sql
    Get package info

  124. plan.sql
    Example of create rows in PLAN_TABLE

  125. privileges.sql
    Determine the object privileges given to a user

  126. process.sql     (Sample)
    Get user processes

  127. prod_db_diag.sql
    Examine various V$ parameters

  128. random.sql
    Create package random (PL/SQL)

  129. rback.sql
    Display info about rollback segments

  130. rbk_lcks.sql     (Sample)
    Rollback lock info

  131. rbk.sql
    Produce a report of the RBS occupying more than a given threshold

  132. rb.sql
    Rollback segments

  133. redo.sql
    Online redo logs

  134. re_index2.sql
    Rebuild an index

  135. reseq.sql
    Get sequence names and numbers

  136. resource.sql     (Sample)
    Get resource info for a user

  137. roleinfo.sql
    Information about roles - Roles, Privileges, assigned users, etc. (PL/SQL)

  138. rollback.sql     (Sample)
    Rollback info

  139. rollstat.sql
    Rollback status

  140. run_this.sql
    Examine various V$ parameters

  141. schema.sql
    Generete schema from database

  142. see_active_sql.sql     (Sample)
    View active SQL queries

  143. see_rollback.sql     (Sample)
    Tie Oracle rollbacks to UNIX processes

  144. see_sql2.sql     (Sample)
    Find your active processes

  145. seesql2.sql
    Find active processes by user

  146. seesql3.sql
    Monitor a user's cursor statement

  147. see.sql
    Determine if effectively using the data dictionary cache of the sga

  148. session1.sql
    Display all connected sessions

  149. session3.sql
    Find session using PID

  150. session4.sql     (Sample)
    Display session info

  151. session.sql     (Sample)
    Display session info

  152. session_time.sql     (Sample)
    Display session info with login time

  153. sga.sql
    Get SGA info

  154. show_active_trans.sql
    Show active (in-progress) transactions

  155. space_usage.sql
    Prints the amount of tablespace, grouped by segment type consumed by each user

  156. sql_hint.sql
    Examples of SQL HINT syntax

  157. sys_priv.sql
    Show the SYSTEM privileges a certain user has

  158. sysseg.sql
    List all segments that reside in the system tablespace

  159. sysuser.sql
    Users with default tablespaces's in system tablespace

  160. table_size.sql
    Get table sizes

  161. table.sql
    Generate CREATE TABLE statements using Catalog tables

  162. table_usage2.sql
    Tells you how often specific tables are loaded into memory and the number of times those tables have been executed.

  163. tabview.sql
    Produce a report of tables for a given database owner and table search string

  164. topsql.sql     (Sample)
    List all statements that are taking longer than 2 seconds to scan through the buffer cache

  165. trace.sql
    Example of doing a trace

  166. tune_sga.sql
    Query the SGA of an instance and advises if modifications are needed in the (PL/SQL)

  167. tune.sql
    Monitor a database (PL/SQL)

  168. users.sql
    List of all database users

  169. user_system_tab.sql
    list of users who own objects in the SYSTEM tablespace

  170. view_tran.sql
    Display active transactions

  171. vsqltop.sql
    Shared SQL Area - Top SQL Statements

  172. waiter.sql
    Waiting transactions

  173. wait.sql
    Waiting transactions

  174. whologin.sql
    Who is logged in