1 – ALIASES
Displays information about aliases for all attached databases.
For each alias, SQL displays the path name or file name of the
current default database, and the file specification for the
database file.
If you specify aliases by name, SQL displays information
about whether or not multischema mode, snapshots, carry-over
locks, adjustable lock granularity, global buffers, commit to
journal optimization, and journal fast commit are enabled. SQL
displays the character sets of the alias if the database default,
national, or identifier character set differs from the session's
default, national, or identifier character set. SQL also displays
the journal fast commit checkpoint and transaction intervals, the
lock timeout interval, the number of users, number of nodes,
buffer size, number of buffers, number of recovery buffers,
ACL-based protections, storage areas, and whether or not the
repository is required.
2 – ALL
Controls whether SQL displays system-defined domains, indexes,
storage maps, tables, or views in the SHOW DOMAINS, SHOW
FUNCTIONS, SHOW INDEXES, SHOW MODULES, SHOW STORAGE MAPS, SHOW
TABLES, SHOW TRIGGERS, and SHOW VIEWS statements.
o If you do not specify either SYSTEM or ALL, the display
includes only user-defined elements.
o If you specify SYSTEM, the display includes only system-
defined elements.
o If you specify ALL, the display includes both user-defined and
system-defined elements.
3 – ANSI_DATE_MODE
Displays the default interpretation for columns with the DATE or
CURRENT_TIMESTAMP data type.
The DATE and CURRENT_TIMESTAMP data types, can be either OpenVMS
or ANSI/ISO. By default, both data types are interpreted as
OpenVMS format.
Use the SET DEFAULT DATE FORMAT statement to change the default
date.
4 – ANSI_IDENTIFIERS_MODE
Displays whether or not identifier checking is enabled. You must
enclose reserved words from the ANSI/ISO SQL standard within
double quotation marks to supply them as identifiers in SQL
statements. When you enable identifier checking, SQL issues
an informational message after statements that misuse ANSI/ISO
reserved words. For a list of the reserved words, see Oracle Rdb
SQL Reference Manual.
By default, identifier checking is disabled. To enable it, use
the SET KEYWORD RULES statement.
5 – ANSI_QUOTING_MODE
Displays whether or not you must use double quotation marks to
delimit the alias and catalog name pair in subsequent statements.
By default, SQL syntax allows only single quotation marks.
Use the SET QUOTING RULES statement to change the quoting rules.
6 – AUTOMATIC_TRANSLATION
Displays the current setting as established using SET AUTOMATIC
TRANSLATION.
7 – CACHE
Displays information about the specified cache. For example:
SQL> SHOW CACHE
Cache Objects in database with filename sample
CACHE1
CACHE2
SQL> SHOW CACHE cache1
CACHE1
Cache Size: 1000 rows
Row Length: 256 bytes
Row Replacement: Enabled
Shared Memory: Process
Large Memory: Disabled
Window Count: 100
Reserved Rows: 20
Sweep Rows: 3000
No Sweep Thresholds
Allocation: 100 blocks
Extent: 100 blocks
8 – CATALOGS
Displays information about the specified catalogs. If you do not
specify any aliases in the catalog names that you specify, SQL
displays this information about all attached databases.
9 – CHARACTER_SETS
Displays information about the specified character sets for the
session and all attached databases.
10 – COLLATING_SEQUENCE
Displays the collating sequences for schemas and domains.
11 – CONNECTIONS
Syntax options:
CONNECTIONS DEFAULT
CONNECTIONS CURRENT
CONNECTIONS connection-name
Displays database information for the specified connection.
12 – CONSTRAINT_MODE
Displays the default setting for constraint evaluation for any
transactions starting after the current transaction. If there is
a current transaction, displays the constraint evaluation mode
for the current transaction.
When the constraint mode is IMMEDIATE, SQL evaluates all commit-
time constraints at the end of each statement and at commit time,
until the transaction completes or until you set the constraint
mode to OFF. When the constraint mode is DEFERRED (the default
setting), constraint evaluation is deferred until commit time.
13 – CONTINUE_CHARACTER
Displays the value for the continuation character, as established
using SET CONTINUE CHARACTER.
14 – CURRENCY_SIGN
Displays the currency indicator, such as the dollar sign ($),
that will be used in output displays.
15 – CURSORS
Displays current cursors.
16 – DATABASES
Displays information about the specified databases. For each
database, SQL displays the alias, the type of database, any
defined collating sequence, and the file specification for the
database file.
If the database was declared using a repository path name, SQL
also displays that path name. If you do not specify any aliases
with the SHOW DATABASES statement, SQL displays this information
about all declared databases.
SQL displays the character sets of the database if the default,
national, or identifier character set differs from the session's
default, national, or identifier character set.
If you do specify an alias, SQL also displays information
about whether or not multischema mode, snapshots, carry-over
locks, adjustable lock granularity, global buffers, commit to
journal optimization, journaling, and journal fast commit are
enabled. SQL also displays the journal fast commit checkpoint
and transaction intervals, the lock timeout interval, the number
of unused storage areas, the number of unused journal files, the
number of users, number of nodes, buffer size, number of buffers,
number of recovery buffers, ACL-based protections, storage areas,
and whether or not the repository is required.
17 – DATE_FORMAT
Displays the values for the date-number and time-number arguments
of the SET DATE FORMAT DATE date-number and SET DATE FORMAT TIME
time-number statements.
18 – DICTIONARY
Displays the current default dictionary directory in the data
dictionary.
19 – DIGIT_SEPARATOR
Displays the character that will be used as the digit separator
in output displays. (The digit separator is the symbol that
separates groups of three digits in values greater than 999. For
example, the comma is the digit separator in the number 1,000.)
20 – DISPLAY
Displays the current settings as established using SET DISPLAY,
SET FEEDBACK, SET HEADING, SET LINE LENGTH (or SET LINESIZE),
SET PAGE LENGTH (or SET PAGESIZE), SET TIMING and SET NULL. Some
values (such as line and page length) are determined from the
OpenVMS terminal characteristics when starting interactive SQL.
21 – DOMAINS
Displays the names, data types, and character sets of specified
domains. If you specify the SHOW DOMAINS statement without any
arguments, SQL displays names, data types, and character sets of
all domains in all attached databases.
22 – EXECUTION_MODE
Shows whether or not SQL executes the statements that you issue
in your interactive SQL session. The default is to execute the
statements as you issue them. However, if you have issued a
SET NOEXECUTE statement in your session, SQL will not execute
subsequent statements.
You can use the SET NOEXECUTE statement to display access
strategies and check for syntax errors. For more information,
see the SET statement.
23 – FLAGGER_MODE
Shows whether or not SQL flags statements containing nonstandard
syntax for all set flaggers. If you specify SET FLAGGER ON, which
is equivalent to SET FLAGGER SQL92_ENTRY ON, the SHOW FLAGGER
statement informs you that flagging for the ANSI/ISO standard
is set. If you specified SET FLAGGER MIA ON, the SHOW FLAGGER
statement informs you that flagging for the MIA standard is set.
24 – FLAGS
Displays the database system debug flags that are enabled for the
current session.
25 – FROM
Syntax options:
{identifier ansi style | PUBLIC}
Specifies the identifiers for the new or modified access
privilege set entry. Specifying PUBLIC is equivalent to a
wildcard specification of all user identifiers.
26 – FUNCTIONS
Displays information about a specified function; either external
or stored. When you enter the SHOW FUNCTIONS statement without
any arguments, SQL displays the name of the function only. The
following table lists the information that you can display using
a set of keywords with the SHOW FUNCTIONS statement:
You
Specify
This: SQL Displays Information About:
COMMENT The description of the function. If none exists,
nothing displays.
ID The unique identification assigned to the function.
LANGUAGE The host language in which the function is coded.
MODULE The name of the module in which the function is
defined.
OWNER The owner of the function.
PARAMETER Information about the parameters, including the
number of arguments, the data type, return type,
and how the parameter is passed.
SOURCE Displays the source definitions for the specified
functions.
27 – HOLD_CURSORS_MODE
Displays the default mode for hold cursors. For example:
SQL> SHOW HOLD CURSORS MODE
Hold Cursors default: WITH HOLD PRESERVE NONE
28 – INDEXES
Displays information about specified indexes. SQL displays the
name of the index, the associated column and table, the size
of the index key, if the definition allows duplicate values for
the column, the type of index (sorted or hashed), and whether
index compression is enabled or disabled. If you specify the SHOW
INDEXES statement without any arguments, SQL displays definitions
of all indexes in all declared databases.
You
Specify
This: SQL Performs This Action:
CARDINALITY Adds the index and column prefix cardinality values
to the SHOW output.
PARTITIONS Displays the index partitions showing the partition
name and number the name of the storage area used for
the partition.
29 – JOURNALS
Displays information about specified journal files. SQL displays
the name of the file specification and, if created, the backup
file specification.
30 – LANGUAGE
Displays the language to be used for translation of month
names and abbreviations in date and time input and display. The
language name also determines the translation of other language-
dependent text, such as the translation for the date literals
YESTERDAY, TODAY, and TOMORROW.
31 – MODULES
Displays information about specified modules.
If you do not specify any of the SHOW MODULES options listed in
the following table, SQL displays information about all these
options:
You
Specify
This: SQL Displays Information About:
COMMENT The description of the module. If none exists,
nothing displays.
FUNCTIONS The stored functions contained in the module.
ID The unique identification assigned to the module.
NAME The name of the module.
OWNER The owner of the module. If the module is a definer's
rights module, the definer's user name displays,
otherwise for an invoker's rights module the output
will be blank.
PROCEDURES The stored procedures contained in the module.
VARIABLES Displays module global variables.
32 – name-list
Most SHOW statements accept an optional name-list which can
specify the name of the object, or a wildcard (*) to indicate a
summary of all such objects. The wildcard or name can be prefixed
by an alias name, or for multischema databases a catalog and
schema.
Names are by default in uppercase. If the object was defined
in mixed or lower case, or with other special characters then
use the SET DIALECT, or SET QUOTING RULES statements to enable
delimited identifers. Then use quotes ("") around the name in the
SHOW statement.
33 – object-name
Specifies the name of an object whose definition you want to
display.
34 – ON DATABASE alias
Specifies the databases for which you want to display access
privilege set information with the SHOW PRIVILEGES or SHOW
PROTECTION statement. You can specify a list of aliases, but you
must specify at least one. To display privileges for the default
database, use the alias RDB$DBHANDLE.
35 – ON syntax options
List of syntax options:
ON TABLES
ON VIEWS
ON COLUMNS
ON FUNCTIONS
ON PROCEDURES
ON MODULES
ON SEQUENCES sequence-name
Specifies the object for which you want to display access
privilege set information with the SHOW PRIVILEGES or SHOW
PROTECTION statement. You can specify a list of names, but
you must specify at least one item to display a list. You must
qualify a column name with at least the associated table name.
In an ANSI/ISO-style database, the SHOW PROTECTION statement
displays which privileges have the option of being granted to
other users and which privileges are without the grant option.
See the SHOW USERS WITH and SHOW USERS GRANTING statements in
this section for more information about displaying privileges
granted directly or indirectly to other users.
36 – ON table name
Specifies the table or tables for which you want to see
associated index definitions.
37 – OUTLINES
Displays the definition of the specified outline. SQL displays
the outline name, ID number, mode, query, compliance, and comment
if one exists.
If you issue the SHOW OUTLINE statement without the name of a
specific outline, the names of all the outlines stored in the
database are displayed. However, the invalid outlines are not
marked as invalid.
38 – PRIVILEGES
Syntax options:
PRIVILEGES | PROTECTION
Displays current user identifier and available access rights for
the specified object.
o The SHOW PRIVILEGES statement displays the current user
identifier and available access rights to the specified
databases, tables, views, columns, external functions,
external procedures, modules, or sequences.
This statement displays not only the privileges that are
explicitly granted to the user, but also any privileges
that the user inherits from database access or the operating
system.
In a client/server environment, the entry shows the identifier
of the client. For example, if a user attaches to a remote
database using the USER and USING clauses, SQL shows the
privileges for the user specified in those clauses.
In an environment that is not client/server, such as when you
attach to a local database, SQL shows not only the privileges
of the database user, but of the logged-on process. For
example, if user heleng, with the OpenVMS privilege BYPASS,
uses the USER and USING clauses to attach to the database as
user rhonda, SQL shows that user rhonda has the privileges
inherited from the logged-on process heleng, as well as
privileges for user rhonda.
o The SHOW PROTECTION statement displays all of the entries in
the access privilege set for the specified databases, tables,
views, columns, external functions, external procedures,
modules, or sequences.
39 – PROCEDURES
Displays information about a specified procedure; either external
or stored.
If you do not specify any of the SHOW PROCEDURES attributes
(COMMENT, ID, LANGUAGE, MODULE, OWNER, SOURCE, or PARAMETER),
by default you will see the display for all these options.
You
Specify
This: SQL Displays Information About:
COMMENT The description of the stored procedure. If none
exists, nothing displays.
ID The unique identification assigned to the procedure.
LANGUAGE The language in which the procedure source is coded.
MODULE The identification number of the module to which a
procedure belongs.
OWNER The owner of the procedure.
PARAMETER Information about the parameters; including the
number of arguments, the data type, and how the
parameter is passed.
SOURCE Displays the source definitions for the specified
procedures.
40 – PROFILES
Displays the definition of the specified profile. If you do not
specify a wildcard or list of profile names, SQL displays the
names of all the profiles in all attached databases.
41 – PROTECTION
Displays access privilege set entries for the specified tables,
columns, database, external functions, external procedures, or
modules.
The SHOW PROTECTION statement displays all the entries in the
access privilege set for the specified databases, tables, views,
columns, external functions, external procedures, or modules.
42 – QUERY_CONFIRM
Shows whether or not SQL displays the cost estimates for a query
before executing that query.
43 – QUERY_LIMIT
Displays information about the number of rows a query can return
and the amount of time used to optimize a query for execution.
44 – RADIX_POINT
Displays the character that will be used as the radix point in
output displays. (The radix point is the symbol that separates
units from decimal fractions. For example, in the number 98.6,
the period is the radix point.)
45 – ROLES
Displays the definition of the specified role. SQL displays the
role name, ID number, and any comments associated with the role
definition.
46 – SCHEMAS
Displays the names of specified schemas. If you do not specify an
alias as part of a schema name, SQL displays schema information
for all the attached databases. For each database that is not
multischema, SQL displays the message, "No schemas found". For
each multischema database, SQL displays the alias, followed by a
list of schemas contained in that database. Each schema name in
the list is preceded by the catalog and alias names.
47 – SEQUENCES
Displays the definition of the specified sequence. SQL displays
the sequence name, ID number, and the sequence attributes.
48 – SQLCA
Displays the contents of the SQL Communications Area (SQLCA).
The SQLCA is a collection of variables that SQL uses to provide
information about the execution of SQL statements to application
programs. In interactive SQL, you can use the SHOW SQLCA
statement to learn about the different variables in the SQLCA.
See the Oracle Rdb SQL Reference Manual for more information
about the SQLCA.
49 – STATISTICS
Displays simple process statistics for the current process. This
command is used primarily to compare resource usage and elapsed
time for different queries.
The following example shows the output after performing a typical
query:
SQL> select count (*)
cont> from employees natural full outer join job_history;
274
1 row selected
SQL> show statistics;
process statistics at 5-MAR-2006 05:57:48.28
elapsed time = 0 00:00:00.16 CPU time = 0 00:00:00.05
page fault count = 430 pages in working set = 22768
buffered I/O count = 26 direct I/O count = 83
open file count = 12 file quota remaining = 7988
locks held = 138 locks remaining = 16776821
CPU utilization = 31.2% AST quota remaining = 995
The statistics are reset after each execution of the SHOW
STATISTICS command.
50 – STORAGE_AREAS
Displays information about storage areas. If you do not specify a
wildcard or list of storage area names, SQL displays the names of
all the storage areas in all attached databases.
You
Specify
This: SQL Displays Information About:
USAGE Usage, object name, storage map, and storage map
partition number for the specified storage area.
Partition numbers are always shown in parentheses,
and may be accompanied by a storage map name. For
example, for an index there is no special map because
it is part of the index. For a table, the map is an
extra object and therefore is reported.
ATTRIBUTES Storage area type, access, page format, page size,
storage area file, storage area allocation, storage
area extent minimum and maximum, storage area extent
percent, snapshot file, snapshot allocation, snapshot
extent minimum and maximum, snapshot extent percent,
whether extents are enabled or disabled, and the
locking level for the specified storage area.
51 – STORAGE_MAPS
Displays information about storage maps. If you do not specify a
wildcard or list of storage map names, SQL displays the names of
all the storage maps in all attached databases.
You
Specify
This: SQL Displays Information About:
PARTITIONS Storage map partitions showing the partition name,
number and the name of the storage area used for the
partition
52 – SYNONYMS
Displays information about the specified synonyms. If you do not
specify any aliases in the synonym names that you specify, SQL
displays this information about all attached databases. The name
of the target object, possibly another synonym, is displayed.
53 – SYSTEM
Controls whether SQL displays system-defined domains, indexes,
storage maps, tables, or views in the SHOW DOMAINS, SHOW
FUNCTIONS, SHOW INDEXES, SHOW MODULES, SHOW STORAGE MAPS, SHOW
TABLES, SHOW TRIGGERS, and SHOW VIEWS statements.
o If you do not specify either SYSTEM or ALL, the display
includes only user-defined elements.
o If you specify SYSTEM, the display includes elements created
for use by the database system, or layered applications such
as the OCI Services component of SQL/Services.
o If you specify ALL, the display includes both user-defined and
system-defined elements.
54 – TABLES
Displays information about tables and views. If you do not
specify a wildcard or list of table and view names, SQL displays
the names of all the tables and views in all attached databases.
If you do not specify any of the SHOW TABLES options (COLUMNS,
COMMENT, CONSTRAINTS, INDEXES, STORAGE MAPS, or TRIGGERS), by
default you will see the display for all these options including
the character set for each column of the specified table.
You
Specify
This: SQL Displays Information About:
COLUMNS Each column name, data type, and domain name for the
specified tables.
COMMENT Comments for the specified tables.
CONSTRAINTS Constraints for the specified tables and the
constraints referencing the specified tables. The
display shows the name and type of each constraint,
its evaluation time, and its source definition.
55 – TO option
Syntax options:
{identifier ansi style | PUBLIC}
Specifies the identifiers for the new or modified access
privilege set entry. Specifying PUBLIC is equivalent to a
wildcard specification of all user identifiers.
56 – TRANSACTION
Displays the characteristics of the current transaction or, if
there is no active transaction, the characteristics specified in
the last DECLARE TRANSACTION statement. For each database within
the scope of the transaction, SQL displays the following:
o Transaction
o Tables specified in the RESERVING clause of the DECLARE
TRANSACTION or SET TRANSACTION statement
o Share mode and lock type for each of those tables
o If fast commit processing is enabled
In addition, the SHOW TRANSACTION statement displays transaction
information returned by the base database system about the
transaction, such as whether or not the transaction is active.
57 – TRIGGERS
Displays information about the specified trigger. If you do not
specify a wildcard or list of trigger names, SQL displays the
names of all the triggers in all attached databases.
58 – USERS
Displays the definition of the specified database user. SQL
displays the database user name (such as defined by the CREATE
USER statement), how the user will be authenticated (currently,
only through the operating system), whether the account is
locked or unlocked, and any comments associated with the user
definition.
59 – USERS_GRANTING
Displays all the users who gave a particular privilege to a
particular user. This statement displays the privileges that
need to be revoked to take a privilege away from the user, either
directly or indirectly.
60 – USERS_WITH
Displays all the users who received a particular privilege from a
particular user, including all the users who indirectly received
privileges. This is also the list of users who lose a particular
privilege when it is taken away from any users who granted the
privilege.
61 – VARIABLES
Displays information about declared variables.
62 – VERSIONS
Displays the version of SQL and the underlying software
components.
63 – VIEWS
Displays information about views. If you do not specify a
wildcard or list of view names, SQL displays the names of all
the views in all attached databases.
If you do not specify any of the SHOW VIEW options (COLUMNS,
COMMENT, or SOURCE), by default you will see the display for all
these options.
You
Specify
This: SQL Displays Information About:
SOURCE Source definitions for the specified views.
64 – WARNING_MODE
Displays the default setting for warning messages. If WARNING
MODE is set to ON, SQL flags statements containing obsolete SQL
syntax. Obsolete syntax is syntax that was allowed in previous
versions of SQL but has changed. Oracle Rdb recommends that you
avoid using such syntax because it may not be supported in future
versions. By default, SQL displays a warning message after any
statement containing obsolete syntax (WARNING MODE ON).
To suppress messages about obsolete syntax, use the SET WARNING
NODEPRECATE statement.