Skip to content

Home

Jump to bottom
Tomas Rohr edited this page Oct 31, 2017 · 6 revisions

Documentation

Use the scripts to display information from data dictionary and navigate around in a similar way like in linux shell. You must place all the scripts in the current working directory when starting sqlplus. Alternatively, you may run login.sql first before calling any other script.

Script invocation

system@XE SYSTEM> @lstab <schema> <table>

Look at the code snippet above. You are logger on to Oracle instance "XE" as a user "system" and you current schema is "SYSTEM". You don't need to include .sql suffix. Most of the scripts accept one or two parameters.

Invocation with two parameters:

  • <schema> is a schema name
  • <table> is a table name You can use SQL wildcards in name - characters % and _

Invocation with one parameter:

system@XE SYSTEM> @lstab <table>

In this case the script lists tables (objects) in the current schema (here it is SYSTEM).

login.sql

When SQL*Plus starts or connects to a database it looks for login.sql script in its current working directory (CWD). SQL*Plus then runs login.sql which sets up your Oracle session.

If you usually start SQL*Plus using a windows shortcut, the CWD is the "Start in" entry in shortcut properties.

login.sql must be executed before any other scripts.

List of scripts

  • login.sql - login script
  • lsmv.sql - list materialized views
  • lsobj.sql - list all schema objects
  • lspkg.sql - list PL/SQL packages
  • lssyn.sql - list synonyms
  • lstab.sql - list tables
  • lstrg.sql - list triggers
  • lsu.sql - list users/schemas
  • lsv.sql - list views
  • mysysprivs.sql - display system privileges of the current user
  • mytabprivs.sql - display table privileges of the current user
  • owner.sql - changes current schema
  • tcc.sql - display constraints and constraint columns of a given table
  • tfk.sql - display foreign keys of a given table
  • tic.sql - display indices and index columns of a given table
  • tpt.sql - display details about table patritioning
  • tptl.sql - display all partitions of a given table
  • tsyn.sql - display synonyms of a given table
  • ttrg.sql - list triggers

login.sql

This login script is called automatically when you log on to the Oracle instance. The scripts configure you database session.

lstab.sql

Lists tables with a specified name pattern and located in a specific schema or in the current schema. Invocation:

system@XE SYSTEM> @lstab <schema> <table>       -- lists specified tables in specified schemas
system@XE SYSTEM> @lstab <table>                -- lists specified tables in the current schema
system@XE SYSTEM> @lstab                        -- lists all tables in the current schema

owner.sql

Changes the current schema and also updates the the sqlplus prompt. Invocation:

system@XE SYSTEM> @owner <schema> 
system@XE SYSTEM> @owner              -- only display the current schema
Clone this wiki locally