Oracle in a Nutshell

SQL

The following views provide information about the processing of all SQL statements in the instance:

*$OBJECT_DEPENDENCY

Lists all objects that a package, procedure, or cursor in the SGA is depending upon. This view can be joined to the *$SQL and *$SESSION views to obtain a list of all objects being referenced by a user.

*$OPEN_CURSOR

Lists all open cursors in the system.

*$SORT_SEGMENT

Provides information about all sort segments in tablespaces specified as TEMPORARY.

*$SORT_USAGE

Provides information about sort segments in all tablespaces.

*$SQL

Provides information about all SQL statements in the shared SQL area.

*$SQL_BIND_DATA

Provides information about bind variables in each SQL statement.

*$SQL_BIND_METADATA

Provides metadata about all bind variables used in SQL statements.

*$SQL_CURSOR

Provides debugging information about every cursor in the shared SQL area.

*$SQL_PLAN

Shows the plan used to execute recent SQL statements. Similar to the information you would get from using the EXPLAIN PLAN command, except that this information is a log of the plan actually used, rather than a description of the plan that will probably be used. New with Oracle9i.

*SQL_REDIRECTION

Lists the SQL statements that have been redirected, along with the reason for the redirection, such as a query rewrite, with materialized views, or an invalid object that was referenced. New with Oracle9i.

*$SQL_SHARED_MEMORY

Provides information about how memory is allocated for every cursor in the shared SQL area.

*$SQL_WORKAREA ...