Displays information about database entities and information
about the interactive SQL session.
1 – Environment
You can use the SHOW statement only in interactive SQL.
2 – Format
SHOW --+-> show-params-1 --------------+-->
+-> show-params-2 --------------+
+-> show-session-information ---+
show-params-1 =
----+-> show-aliases --------------+--->
+-> show-cache ----------------+
+-> show-catalogs -------------+
+-> CHARACTER SETS ------------+
+-> show-collating-sequence ---+
+-> show-connections ----------+
+-> CURSORS -------------------+
+-> show-databases ------------+
+-> DISPLAY -------------------+
+-> show-domains --------------+
+-> FLAGS ---------------------+
+-> show-functions ------------+
+-> show-indexes --------------+
+-> show-journals -------------+
+-> show-modules --------------+
+-> show-outlines -------------+
show-params-2 =
--+-> show-profiles -------------+->
+-> show-privileges -----------+
+-> show-procedures -----------+
+-> QUERY CONFIRM -------------+
+-> QUERY LIMIT ---------------+
+-> show-roles ----------------+
+-> show-schemas --------------+
+-> show-sequences ------------+
+-> show-storage-areas --------+
+-> show-storage-maps ---------+
+-> show-synonyms -------------+
+-> show-tables ---------------+
+-> show-triggers -------------+
+-> show-users ----------------+
+-> show-users-granting -------+
+-> show-users-with -----------+
+-> VARIABLES -----------------+
+-> show-views ----------------+
show-aliases =
--> ALIASES --+----------------+-->
+-+-> <alias> -+-+
| +----- , <---+ |
+---> * ---------+
show-cache =
---> CACHE -+---------------------+-->
+---> <name-list> ----+
show-catalogs =
---> CATALOGS --+-----------------+->
+-> name-list ----+
show-collating-sequence =
---> COLLATING SEQUENCE --+---------------+-->
+-> name-list --+
show-connections =
---> CONNECTIONS -+-+---------------------+-+->
| +-> DEFAULT ----------+ |
| +-> CURRENT ----------+ |
| +-> <connection-name> + |
+------------- , <--------+
show-databases =
--> DATABASES --+----------------+-->
+-+-> <alias> -+-+
| +----- , <---+ |
+---> * ---------+
show-domains =
-+-----------+-> DOMAINS -+---------------+-->
+-> SYSTEM -+ +-> name-list --+
+-> ALL ----+
show-functions =
-+------------+-> FUNCTIONS +-----------------------------+-+---------------+->
+-> SYSTEM -+ +> ( ++-> COMMENT -----++> ) -+ +-> name-list -+
+-> ALL -----+ |+-> ID ----------+|
|+-> LANGUAGE ----+|
|+-> MODULE ------+|
|+-> OWNER -------+|
|+-> PARAMETER ---+|
|+-> SOURCE ------+|
+------- , <-------+
show-indexes =
-+-----------++-> INDEXES -+-+---------------------------------------+->
+-> SYSTEM -++-> INDICES -+ +-> ON -+> <table-name> ----------------+
+-> ALL ----+ | +----- , <----------------------+
+-> name-list --------------------------+
+-> ( -+-> CARDINALITY +> name-list ) -+
+-- PARTITIONS |
+------- , ------+
show-journals
---> JOURNALS -+----------------+-->
+--> name-list --+
show-modules =
-+------------+-> MODULES -+----------------------------+-+---------------+->
+-> SYSTEM -+ +> ( ++> COMMENT ----++> ) --+ +-> name-list -+
+-> ALL -----+ |+> FUNCTIONS --+|
|+> ID ---------+|
|+> NAME -------+|
||> OWNER ------+|
|+> PROCEDURES -+|
|+> VARIABLES --+|
+------ , <------+
show-outlines
---> OUTLINES -+---------------+-->
+-> name-list --+
show-profiles =
--> PROFILES -+---------------+-->
+-> name-list --+
name-list =
-+-+---------------++- * --------------+-+->
| +-> <alias> . --++-> <object-name> -+ |
| |
+----------------- , <------------------+
show-privileges =
--+-> PROTECTION ON -++-> TABLES ----+-> <table-name> +----------+->
| || +------- , <-----+ |
+-> PRIVILEGES ON -++-> VIEWS -----+-> <view-name> -+----------+
| +------- , <-----+ |
+-> COLUMNS ---+-> <column-name> -+--------+
| +------- , <-------+ |
+-> DATABASE --+-> <alias> +---------------+
| +---- , <---+ |
+-> FUNCTION --+-> <function-name> ----+---+
| +----------- , <--------+ |
+-> PROCEDURE -+-> <procedure-name> -----+-+
| +----------- , <----------+ |
+-> MODULE ----+-> <module-name> +---------+
| +------- , <------+ |
+-> SEQUENCE --+-> <sequence-name> -+------+
+------------ , <----+
show-procedures =
-+-----------+--> PROCEDURES +--------------------------++---------------+->
+-> SYSTEM -+ +> ( ++> COMMENT ----++> ) ++-> name-list --+
+-> ALL ----+ |+> ID ---------+|
|+> LANGUAGE ---+|
|+> MODULE -----+|
|+> OWNER ------+|
|+> PARAMETER --+|
|+> SOURCE -----+|
+------ , <------+
show-roles =
---> ROLES -+--------------+->
+-> name-list -+
show-schemas =
--> SCHEMAS -+---------------+->
+-> name-list --+
show-sequences=
---> SEQUENCES -+---------------+->
+-> name-list --+
show-storage-areas =
--> STORAGE AREAS --+---------------------------------------+->
+-> name-list --------------------------+
+-> ( ++> USAGE -------++> ) name-list -+
|+> ATTRIBUTES --+|
+----- , <--------+
show-storage-maps =
-+-----------+-> STORAGE MAPS -+----------------+--------------------+->
+-> SYSTEM -+ +-> name-list ---+ |
+-> ALL ----+ +-> ( PARTITIONS -> ) -> name-list --+
show-synonyms =
---> SYNONYMS -+---------------+-->
+-> name-list --+
show-tables =
-+-----------+-> TABLES -+-------------------------------------------+->
+-> SYSTEM -+ +-> name-list ------------------------------+
+-> ALL ----+ +-> ( -++-> COLUMNS -----++-> ) name-list --+
|+-> COMMENT -----+|
|+-> CONSTRAINTS -+|
|+-> INDEXES -----+|
|+-> STORAGE MAPS +|
|+-> TRIGGERS ----+|
+-------- , <------+
show-triggers =
-+-----------+-> TRIGGERS -+---------------+-->
+-> SYSTEM -+ +-> name-list --+
+-> ALL ----+
show-users =
---> USERS --+---------------+-->
+-> name-list --+
show-users-granting =
--> USERS GRANTING ---+
+---------------------+
++> db-privs-ansi ------> ON DATABASE -+-> <alias> +-----------------++
| +---- , <---+ ||
+> table-privs-ansi ----> ON TABLE -+-> <table-name> +--------------+|
| +------- , <-----+ ||
+> column-privs-ansi ---> ON COLUMN -+-> <column-name> --+-----------+|
| +------- , <--------+ ||
+> ext-routine-privs-ansi +-> ON FUNCTION +> <function-name -----+---+|
| | +--------- , <---------+ ||
| +--> ON PROCEDURE +> <procedure-name -----++|
| +--------- , <----------+||
+> module-privs-ansi ---> ON MODULE --+-> <module-name> --+----------+|
| +----- , <----------+ ||
+> sequence-privs-ansi --> ON SEQUENCE -+> <sequence-name> -+--------+|
+--- , <------------+ |
+-------------------------------------------------------------------+
+---> TO -+-> identifier-ansi-style ---+--->
+-> PUBLIC ------------------+
show-views =
--+-----------+-> VIEWS -+------------------------------+-+
+-> SYSTEM -+ +-> ( -+-+-> COLUMNS -+-+-> ) -+ |
+-> ALL ----+ | +-> COMMENT -+ | |
| +-> SOURCE --+ | |
+------ , <------+ |
+--------------------------<-----------------------------+
+-+---------------+------------->
+-> name-list --+
db-privs-ansi =
----+---+-+-> SELECT -----+-+----+->
| | +-> INSERT -----+ | |
| | +-> OPERATOR ---+ | |
| | +-> DELETE -----+ | |
| | +-> CREATE -----+ | |
| | +-> ALTER ------+ | |
| | +-> DROP -------+ | |
| | +-> DBCTRL -----+ | |
| | +-> DBADM ------+ | |
| | +-> SHOW -------+ | |
| | +-> REFERENCES -+ | |
| | +-> UPDATE -----+ | |
| | +-> SECURITY ---+ | |
| | +-> DISTRIBTRAN + | |
| +------- , <--------+ |
+-------> ALL PRIVILEGES ----+
table-privs-ansi =
-+--+-+-> SELECT -------------------------------+-+-+->
| | +-> INSERT -------------------------------+ | |
| | +-> OPERATOR -----------------------------+ | |
| | +-> DELETE -------------------------------+ | |
| | +-> CREATE -------------------------------+ | |
| | +-> ALTER --------------------------------+ | |
| | +-> DROP ---------------------------------+ | |
| | +-> DBCTRL -------------------------------+ | |
| | +-> SHOW ---------------------------------+ | |
| | +-> REFERENCES +--------------------------+ | |
| | | +> ( +> <column-name> +> ) + | |
| | | +----- , <-------+ | | |
| | +-> UPDATE +---------------------- ------+ | |
| | +> ( +> <column-name> -+---> ) + | |
| | +----- , <--------+ | |
| +------------------- , <----------------------+ |
+------> ALL PRIVILEGES ---------------------------+
column-privs-ansi =
---+-+-+-> UPDATE -----+-+--+-->
| | +-> REFERENCES -+ | |
| +------- , <--------+ |
+-----> ALL PRIVILEGES --+
ext-routine-privs-ansi =
-+--+-+-> ALTER ------------------------------+-+--+-->
| | +-> DBCTRL -----------------------------+ | |
| | +-> DROP -------------------------------+ | |
| | +-> EXECUTE ----------------------------+ | |
| | +-> REFERENCES -------------------------+ | |
| | +-> SHOW -------------------------------+ | |
| +------------------- , <--------------------+ |
+------> ALL PRIVILEGES --------------------------+
module-privs-ansi =
-+--+-+-> ALTER ------------------------------+-+--+-->
| | +-> DBCTRL -----------------------------+ | |
| | +-> DROP -------------------------------+ | |
| | +-> EXECUTE ----------------------------+ | |
| | +-> REFERENCES -------------------------+ | |
| | +-> SHOW -------------------------------+ | |
| +------------------- , <--------------------+ |
+------> ALL PRIVILEGES --------------------------+
identifier-ansi-style =
------> user-identifier -------->
show-users-with =
--> USERS WITH -------+
+---------------------+
++> db-privs-ansi -------> ON DATABASE -+-> <alias> +-----------------++
| +---- , <---+ ||
+> table-privs-ansi -----> ON TABLE -+-> <table-name> +--------------+|
| +------- , <-----+ ||
+> column-privs-ansi ----> ON COLUMN -+-> <column-name> -+------------+|
| +------- , <-------+ ||
+> ext-routine-privs-ansi -+> ON FUNCTION -+> <function-name> ----+---+|
| | +--------- , <---------+ ||
| +-> ON PROCEDURE -+> <procedure-name> ----++|
| +--------- , <----------+||
+> module-privs-ansi ----> ON MODULE --+-> <module-name> +------------+|
| +----- , <--------+ ||
+> sequence-privs-ansi --> ON SEQUENCE -+-> <sequence-name> -+--------+|
+---- , <------------+ |
+--------------------------------------------------------------------+
+--> FROM +-> identifier-ansi-style ---+--->
+-> PUBLIC ------------------+
sequence-privs-ansi =
-+-+-+-> ALTER -----+-+-+-->
| | +-> DBCTRL ----+ | |
| | +-> DROP ------+ | |
| | +-> SELECT ----+ | |
| +------- , <-------+ |
+--> ALL PRIVILEGES ---+
show-session-information =
-+-> ANSI DATE MODE -----------+->
+-> ANSI IDENTIFIERS MODE ----+
+-> ANSI QUOTING MODE --------+
+-> AUTOMATIC TRANSLATION ----+
+-> CONSTRAINT MODE ----------+
+-> CONTINUATION CHARACTER ---+
+-> CURRENCY SIGN ------------+
+-> DATE FORMAT --------------+
+-> DICTIONARY ---------------+
+-> DIGIT SEPARATOR ----------+
+-> DISPLAY ------------------+
+-> EXECUTION MODE -----------+
+-> FLAGGER MODE -------------+
+-> HOLD CURSORS MODE --------+
+-> LANGUAGE -----------------+
+-> RADIX POINT --------------+
+-> SQLCA --------------------+
+-> TRANSACTION --------------+
+-> VERSIONS -----------------+
+-> WARNING MODE -------------+
3 – Arguments
3.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.
3.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.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.
3.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.
3.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.
3.6 – AUTOMATIC_TRANSLATION
Displays the current setting as established using SET AUTOMATIC
TRANSLATION.
3.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
3.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.
3.9 – CHARACTER_SETS
Displays information about the specified character sets for the
session and all attached databases.
3.10 – COLLATING_SEQUENCE
Displays the collating sequences for schemas and domains.
3.11 – CONNECTIONS
Syntax options:
CONNECTIONS DEFAULT
CONNECTIONS CURRENT
CONNECTIONS connection-name
Displays database information for the specified connection.
3.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.
3.13 – CONTINUE_CHARACTER
Displays the value for the continuation character, as established
using SET CONTINUE CHARACTER.
3.14 – CURRENCY_SIGN
Displays the currency indicator, such as the dollar sign ($),
that will be used in output displays.
3.15 – CURSORS
Displays current cursors.
3.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.
3.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.
3.18 – DICTIONARY
Displays the current default dictionary directory in the data
dictionary.
3.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.)
3.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.
3.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.
3.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.
3.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.
3.24 – FLAGS
Displays the database system debug flags that are enabled for the
current session.
3.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.
3.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.
3.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
3.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.
3.29 – JOURNALS
Displays information about specified journal files. SQL displays
the name of the file specification and, if created, the backup
file specification.
3.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.
3.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.
3.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.
3.33 – object-name
Specifies the name of an object whose definition you want to
display.
3.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.
3.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.
3.36 – ON table name
Specifies the table or tables for which you want to see
associated index definitions.
3.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.
3.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.
3.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.
3.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.
3.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.
3.42 – QUERY_CONFIRM
Shows whether or not SQL displays the cost estimates for a query
before executing that query.
3.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.
3.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.)
3.45 – ROLES
Displays the definition of the specified role. SQL displays the
role name, ID number, and any comments associated with the role
definition.
3.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.
3.47 – SEQUENCES
Displays the definition of the specified sequence. SQL displays
the sequence name, ID number, and the sequence attributes.
3.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.
3.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.
3.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.
3.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
3.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.
3.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.
3.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.
3.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.
3.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.
3.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.
3.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.
3.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.
3.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.
3.61 – VARIABLES
Displays information about declared variables.
3.62 – VERSIONS
Displays the version of SQL and the underlying software
components.
3.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.
3.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.
4 – Examples
Example 1: Using the SHOW statement displays
The following log file from an interactive SQL session
illustrates some of the arguments for the SHOW statement:
SQL> -- Show the session character sets.
SQL> --
SQL> SHOW CHARACTER SETS;
Default character set is DEC_MCS
National character set is DEC_MCS
Identifier character set is DEC_MCS
Literal character set is DEC_MCS
Display character set is UNSPECIFIED
SQL> --
SQL> -- Attach to the database and show database character sets.
SQL> --
SQL> ATTACH 'FILENAME MIA_CHAR_SET';
SQL> SHOW CHARACTER SETS;
Default character set is DEC_MCS
National character set is DEC_MCS
Identifier character set is DEC_MCS
Literal character set is DEC_MCS
Display character set is UNSPECIFIED
Alias RDB$DBHANDLE:
Identifier character set is DEC_KANJI
Default character set is DEC_KANJI
National character set is KANJI
SQL> --
SQL> -- Attach to the second database and show character sets of both.
SQL> --
SQL> ATTACH 'ALIAS MIA1 FILENAME MIA_CHAR_SET';
SQL> SHOW CHARACTER SETS;
Default character set is DEC_MCS
National character set is DEC_MCS
Identifier character set is DEC_MCS
Literal character set is DEC_MCS
Display character set is UNSPECIFIED
Alias RDB$DBHANDLE:
Identifier character set is DEC_KANJI
Default character set is DEC_KANJI
National character set is KANJI
Alias MIA1:
Identifier character set is DEC_KANJI
Default character set is DEC_KANJI
National character set is KANJI
SQL> --
SQL> -- SHOW ALIAS examples.
SQL> --
SQL> SHOW ALIAS;
Default alias:
Oracle Rdb database in file MIA_CHAR_SET
Alias MIA1:
Oracle Rdb database in file MIA_CHAR_SET
SQL> SHOW ALIAS MIA1;
Alias MIA1:
Oracle Rdb database in file MIA_CHAR_SET
Multischema mode is disabled
Default character set is DEC_KANJI
National character set is KANJI
Identifier character set is DEC_KANJI
Number of users: 50
Number of nodes: 16
Buffer Size (blocks/buffer): 6
Number of Buffers: 20
Number of Recovery Buffers: 20
Snapshots are Enabled Immediate
.
.
.
ACL based protections
Storage Areas in database with alias MIA1
RDB$SYSTEM Default and list storage area
Journals in database with alias MIA1
No Journals Found
Cache Objects in database MIA1
No Caches Found
SQL> --
SQL> -- SHOW CONNECTIONS examples.
SQL> --
SQL> CONNECT TO 'ALIAS MIA1 FILENAME MIA_CHAR_SET' AS 'TEST';
SQL> SHOW CONNECTIONS;
RDB$DEFAULT_CONNECTION
-> TEST
SQL> SHOW CONNECTIONS DEFAULT;
Connection: RDB$DEFAULT_CONNECTION
Default alias is RDB$DBHANDLE
Default catalog name is RDB$CATALOG
Default schema name is SMITH
Dialect: SQLV40
.
.
.
Default character set is DEC_MCS
National character set is DEC_MCS
Identifier character set is DEC_MCS
Literal character set is DEC_MCS
Display character set is UNSPECIFIED
Alias RDB$DBHANDLE:
Identifier character set is DEC_KANJI
Default character set is DEC_KANJI
National character set is KANJI
Alias MIA1:
Identifier character set is DEC_KANJI
Default character set is DEC_KANJI
National character set is KANJI
SQL> SHOW CONNECTIONS TEST;
Connection: TEST
Default alias is RDB$DBHANDLE
Default catalog name is RDB$CATALOG
Default schema name is SMITH
Dialect: SQLV40
Default character unit: OCTETS
Keyword Rules: SQLV40
View Rules: SQLV40
Default DATE type: DATE VMS
Quoting Rules: SQLV40
Optimization Level: DEFAULT
Hold Cursors default: WITH HOLD PRESERVE NONE
Quiet commit mode: OFF
Compound transactions mode: EXTERNAL
Default character set is DEC_MCS
National character set is DEC_MCS
Identifier character set is DEC_MCS
Literal character set is DEC_MCS
Display character set is UNSPECIFIED
Alias MIA1:
Identifier character set is DEC_KANJI
Default character set is DEC_KANJI
National character set is KANJI
SQL> --
SQL> CONNECT TO 'ALIAS MIA1 FILENAME MIA_CHAR_SET' AS 'test1';
SQL> --
SQL> -- You must set quoting rules to the SQL99 environment and use
SQL> -- double quotation marks (") to display the settings of the
SQL> -- 'test1' connection or use SHOW CONNECTIONS CURRENT.
SQL> --
SQL> SHOW CONNECTIONS;
RDB$DEFAULT_CONNECTION
TEST
-> test1
SQL> SHOW CONNECTIONS test1;
Connection: TEST1
%SQL-F-NOSUCHCON, There is not an active connection by that name
SQL> SET QUOTING RULES 'SQL99';
SQL> SHOW CONNECTIONS "test1";
Connection: test1
Default alias is RDB$DBHANDLE
Default catalog name is RDB$CATALOG
Default schema name is SMITH
Dialect: SQLV40
Default character unit: OCTETS
Keyword Rules: SQLV40
View Rules: SQLV40
Default DATE type: DATE VMS
Quoting Rules: ANSI/ISO
Optimization Level: DEFAULT
Hold Cursors default: WITH HOLD PRESERVE NONE
Quiet commit mode: OFF
Compound transactions mode: EXTERNAL
Default character set is DEC_MCS
National character set is DEC_MCS
Identifier character set is DEC_MCS
Literal character set is DEC_MCS
Display character set is UNSPECIFIED
Alias MIA1:
Identifier character set is DEC_KANJI
Default character set is DEC_KANJI
National character set is KANJI
SQL> SET CONNECT DEFAULT;
SQL> --
SQL> -- SHOW DATABASES examples.
SQL> --
SQL> SHOW DATABASES;
%SQL-I-SPELLCORR, identifier DATABASES replaced with DATABASE
Default alias:
Oracle Rdb database in file MIA_CHAR_SET
Alias MIA1:
Oracle Rdb database in file MIA_CHAR_SET
SQL> SHOW DATABASE RDB$DBHANDLE;
Default alias:
Oracle Rdb database in file MIA_CHAR_SET
Multischema mode is disabled
Default character set is DEC_KANJI
National character set is KANJI
Identifier character set is DEC_KANJI
Number of users: 50
Number of nodes: 16
Buffer Size (blocks/buffer): 6
Number of Buffers: 20
Number of Recovery Buffers: 20
Snapshots are Enabled Immediate
.
.
.
ACL based protections
Storage Areas in database with filename MIA_CHAR_SET
RDB$SYSTEM Default and list storage area
Journals in database with filename MIA_CHAR_SET
No Journals Found
Cache Objects in database with filename MIA_CHAR_SET
No Caches Found
SQL> --
SQL> -- SHOW DOMAINS example.
SQL> --
SQL> SHOW DOMAINS;
User domains in database with filename MIA_CHAR_SET
No Domains Found
User domains in database with alias MIA1
No Domains Found
SQL> --
SQL> -- SHOW TABLES example.
SQL> --
SQL> SHOW TABLES;
User tables in database with filename MIA_CHAR_SET
COLOURS
User tables in database with alias MIA1
MIA1.COLOURS
SQL> SHOW TABLE (COLUMNS) COLOURS;
Information for table COLOURS
Columns for table COLOURS:
Column Name Data Type Domain
----------- --------- ------
ENGLISH CHAR(8)
DEC_MCS 8 Characters, 8 Octets
FRENCH CHAR(8)
ISOLATIN9 8 Characters, 8 Octets
JAPANESE CHAR(8)
SHIFT_JIS 4 Characters, 8 Octets
ROMAJI CHAR(16)
KATAKANA CHAR(8)
KATAKANA 8 Characters, 8 Octets
HINDI CHAR(8)
DEVANAGARI 8 Characters, 8 Octets
GREEK CHAR(8)
ISOLATINGREEK 8 Characters, 8 Octets
ARABIC CHAR(8)
ISOLATINARABIC 8 Characters, 8 Octets
RUSSIAN CHAR(8)
ISOLATINCYRILLIC 8 Characters, 8 Octets
SQL> --
SQL> -- SHOW INDEXES example.
SQL> --
SQL> SHOW INDEXES;
User indexes in database with filename MIA_CHAR_SET
COLOUR_INDEX
User indexes in database with alias MIA1
MIA1.COLOUR_INDEX
SQL> SHOW INDEXES COLOUR_INDEX;
Indexes on table COLOURS:
COLOUR_INDEX with column JAPANESE
Duplicates are allowed
Type is Sorted
Key suffix compression is DISABLED
Example 2: Showing features that internationalize your terminal
session
The following example displays SHOW statements that let
you see the values for the SET statements dealing with
internationalization:
SQL> --
SQL> -- First, use the SET statement to specify nondefault values.
SQL> --
SQL> SET CURRENCY SIGN '£'
SQL> --
SQL> SET DATE FORMAT TIME 15
SQL> --
SQL> SET DIGIT SEPARATOR '.'
SQL> --
SQL> SET LANGUAGE GERMAN
SQL> --
SQL> SET RADIX POINT ','
SQL> --
SQL> -- Now look at the SHOW displays.
SQL> --
SQL> SHOW CURRENCY SIGN
Currency sign is '£'.
SQL> --
SQL> SHOW DATE FORMAT
Date format is TIME 15.
SQL> --
SQL> SHOW DIGIT SEPARATOR
Digit separator is '.'.
SQL> --
SQL> SHOW LANGUAGE
Language is GERMAN.
Example 3: Showing the setting for nonstandard syntax flagging
SQL> SHOW FLAGGER MODE
The flagger mode is OFF
SQL> SET FLAGGER SQL92_ENTRY ON
SQL> SHOW FLAGGER MODE
%SQL-I-NONSTASYN92E, Nonstandard SQL92 Entry-level syntax
The SQL92 Entry-level flagger mode is ON
Example 4: Showing after-image journal files
The following example displays journal information:
SQL> ATTACH 'FILENAME SAMPLE';
SQL> SHOW JOURNAL
Journals in database with filename SAMPLE
AIJ_ONE
AIJ_TWO
SQL> SHOW JOURNAL *
Journals in database with filename SAMPLE
AIJ_ONE
Journal File: DISK1:[DOCS]AIJ1.AIJ;1
Backup File: DISK1:[DOCS.AIJS]AIJ1.AIJ;
AIJ_TWO
Journal File: DISK1:[DOCS]AIJ2.AIJ;1
Backup File: DISK1:[DOCS.AIJS]AIJ2.AIJ;
Edit String: ('$'+HOUR+MINUTE+'_'+MONTH+DAY+'_'+SEQUENCE)
Example 5: Showing storage area usage and attribute information
The following example displays storage area information:
SQL> -- Display the usage of storage area TEST_AREA and JOBS
SQL> --
SQL> SHOW STORAGE AREAS (USAGE) TEST_AREA
No database objects use Storage Area TEST_AREA
SQL> SHOW STORAGE AREAS (USAGE) JOBS
Database objects using Storage Area JOBS:
Usage Object Name Map / Partition
---------------- ------------------------------- -------------------------------
Storage Map JOBS JOBS_MAP (1)
SQL> --
SQL> -- Display the attributes of storage area JOBS.
SQL> --
SQL> SHOW STORAGE AREAS (ATTRIBUTES) JOBS
JOBS
Access is: Read write
Page Format: Mixed
Page Size: 2 blocks
Area File: DISK1:[DOCS.WORK]JOBS.RDA;1
Area Allocation: 402 pages
Extent: Enabled
Area Extent Minimum: 99 pages
Area Extent Maximum: 9999 pages
Area Extent Percent: 20 percent
Snapshot File: DISK1:[DOCS.WORK]JOBS.SNP;1
Snapshot Allocation: 100 pages
Snapshot Extent Minimum: 99 pages
Snapshot Extent Maximum: 9999 pages
Snapshot Extent Percent: 20 percent
Locking is Row Level
No Cache Associated with Storage Area
Thresholds are (70, 85, 95)
Example 6: Showing query outline information
The following example displays query outline information:
SQL> SHOW OUTLINE MY_OUTLINE
MY_OUTLINE
Source:
create outline MY_OUTLINE
id '09ADFE9073AB383CAABC4567BDEF3832'
mode 0
as (
query (
subquery (
EMPLOYEES 0 access path index EMP_LAST_NAME
join by cross to
DEGREES 1 access path index DEG_EMP_ID
)
)
)
compliance optional ;
Example 7: Showing privileges
The following example demonstrates the SHOW PRIVILEGES statement:
SQL> ! Attach as the logged on user, [sql,heleng]
SQL> ATTACH 'FILENAME personnel';
SQL> SHOW PRIVILEGES ON DATABASE RDB$DBHANDLE
Privileges on Alias RDB$DBHANDLE
(IDENTIFIER=[sql,heleng],ACCESS=SELECT+INSERT+UPDATE+DELETE+SHOW+CREATE+
ALTER+DROP+DBCTRL+OPERATOR+DBADM+REFERENCES+SECURITY+DISTRIBTRAN)
SQL> !
SQL> ! Attach as user rhonda.
SQL> ATTACH 'FILENAME personnel USER ''rhonda'' USING ''newhampshire''';
SQL> ! User rhonda has SELECT privilege.
SQL> SHOW PRIVILEGES ON DATABASE RDB$DBHANDLE
Privileges on Alias RDB$DBHANDLE
(IDENTIFIER=[sql,rhonda],ACCESS=SELECT)
SQL> EXIT
$ !
$ ! On OpenVMS, give the process the BYPASS privilege, which
$ ! gives you access to any database object.
$ SET PROC/PRIVILEGES=BYPASS
$ SQL$
SQL> ! Attach as user rhonda.
SQL> ATTACH 'FILENAME personnel USER ''rhonda'' USING ''newhampshire''';
SQL> !
SQL> ! User rhonda now has all privileges, inherited from the logged-on
SQL> ! process.
SQL> SHOW PRIVILEGES ON DATABASE RDB$DBHANDLE
Privileges on Alias RDB$DBHANDLE
(IDENTIFIER=[sql,rhonda],ACCESS=SELECT+INSERT+UPDATE+DELETE+SHOW+CREATE+
ALTER+DROP+DBCTRL+OPERATOR+DBADM+REFERENCES+SECURITY+DISTRIBTRAN)
Example 8: Showing modules, stored procedures, and stored
functions
SQL> --
SQL> -- Show the modules in the database.
SQL> --
SQL> ATTACH 'FILENAME mf_personnel';
SQL> SHOW MODULES
Modules in database with filename mf_personnel
Module name is: UTILITY_FUNCTIONS
SQL> SHOW MODULES utility_functions
Module name is: UTILITY_FUNCTIONS
Header:
utility_functions
language sql
No description found.
Owner is:
Module ID is: 1
Functions/Procedures in Module:
Function ABS
Function MDY
Procedure TRACE_DATE
SQL> --
SQL> -- Show the procedures and functions of the module.
SQL> --
SQL> SHOW MODULES (PROCEDURES) utility_functions
Module name is: UTILITY_FUNCTIONS
Functions/Procedures in Module:
Function ABS
Function MDY
Procedure TRACE_DATE
SQL> SHOW PROCEDURE trace_date
Procedure name is: TRACE_DATE
Procedure ID is: 3
Source:
trace_date (:dt date);
begin
trace :dt;
end
No description found.
Module name is: UTILITY_FUNCTIONS
Module ID is: 1
Number of parameters is: 1
Parameter Name Data Type
-------------- ---------
DT DATE VMS
Parameter position is 1
Parameter is IN (read)
Parameter is passed by REFERENCE
SQL> SHOW FUNCTIONS abs
Function name is: ABS
Function ID is: 2
Source:
abs (in :arg integer) returns integer
comment 'Returns the absolute value of an integer';
begin
return case
when :arg < 0 then - :arg
else :arg
end;
end
Comment: Returns the absolute value of an integer
Module name is: UTILITY_FUNCTIONS
Module ID is: 1
Number of parameters is: 1
Parameter Name Data Type
-------------- ---------
INTEGER
Function result datatype
Return value is passed by VALUE
ARG INTEGER
Parameter position is 1
Parameter is IN (read)
Parameter is passed by REFERENCE
Example 9: Showing a storage map that defines both horizontal and
vertical record partitioning
SQL> SHOW STORAGE MAP EMPLOYEES_1_MAP2
EMPLOYEES_1_MAP2
For Table: EMP2
Partitioning is: UPDATABLE
Store clause: STORE COLUMNS (EMPLOYEE_ID, LAST_NAME, FIRST_NAME,
MIDDLE_INITIAL, STATUS_CODE)
USING (EMPLOYEE_ID)
IN ACTIVE_AREA_A WITH LIMIT OF ('00399')
IN ACTIVE_AREA_B WITH LIMIT OF ('00699')
OTHERWISE IN ACTIVE_AREA_C
STORE COLUMNS (ADDRESS_DATA_1, ADDRESS_DATA_2, CITY,
STATE, POSTAL_CODE)
USING (EMPLOYEE_ID)
IN INACTIVE_AREA_A WITH LIMIT OF ('00399')
IN INACTIVE_AREA_B WITH LIMIT OF ('00699')
OTHERWISE IN INACTIVE_AREA_C
STORE IN OTHER_AREA
Compression is: ENABLED
Partition 2: Compression is Enabled
Partition 3: Compression is Enabled
Example 10: Displaying a Sequence
SQL> SHOW SEQUENCE EMPIDS
EMPIDS
Sequence Id: 3
Initial Value: 1
Minimum Value: 1
Maximum Value: 9223372036854775787
Next Sequence Value: 1
Increment by: 1
Cache Size: 20
Order
No Cycle
No Randomize
Comment: Sequence for employee IDs.
Example 11: Displaying a Role
SQL> SHOW ROLE SECRETARY
SECRETARY
Identified Externally
Comment: Role for the secretarial staff
Example 12: Displaying a User
SQL> SHOW USER NSTEWART
NSTEWART
Identified Externally
Account Unlocked
Comment: Nicholas Stewart
Example 13: Show Details of One Profile
SQL> SHOW PROFILE
Profiles in database with filename SQL$DATABASE
DECISION_SUPPORT
SQL> SHOW PROFILE DECISION_SUPPORT
DECISION_SUPPORT
Comment: limit transactions used by report writers
Transaction modes (read only, no read write)
SQL> ALTER PROFILE DECISION_SUPPORT
cont> default transaction read only;
SQL> SHOW PROFILE DECISION_SUPPORT
DECISION_SUPPORT
Comment: limit transactions used by report writers
Default transaction read only
Transaction modes (read only, no read write)
SQL>
Example 14: Show the Use of Delimited Identifiers for Mixed-Case
Names
SQL> CREATE PROFILE "Decision_Support"
cont> COMMENT IS 'limit transactions used by report writers'
cont> TRANSACTION MODES (NO READ WRITE, READ ONLY);
SQL> SHOW PROFILE
Profiles in database with filename SQL$DATABASE
Decision_Support
SQL> SHOW PROFILE Decision_Support
No Users found
SQL> SHOW PROFILE "Decision_Support"
Decision_Support
Comment: limit transactions used by report writers
Transaction modes (read only, no read write)
Example 15: Displaying Synonyms
SQL> SHOW SYNONYMS
Synonyms in database with filename SQL$DATABASE
C_SAL View CURRENT_SALARY
E Table synonym EMPS
EMPS Table EMPLOYEES
ID_NUMBER Domain ID_DOM
SQL> SHOW SYNONYMS ID_NUMBER
ID_NUMBER
for domain ID_DOM
Comment: support the old name for this domain
SQL> SHOW VIEWS
User tables in database with filename SQL$DATABASE
CURRENT_INFO A view.
CURRENT_JOB A view.
CURRENT_SALARY A view.
C_SAL A synonym for view CURRENT_SALARY
Example 16: Using Synonyms to Identify Objects
This example creates a sequence and a synonym for a sequence, and
uses the SHOW SEQUENCE command with the synonym.
SQL> create sequence department_id_sequence;
SQL> create synonym dept_id_s for department_id_sequence;
SQL> show sequence
Sequences in database with filename personnel
DEPARTMENT_ID_SEQUENCE
DEPT_ID_S A synonym for sequence DEPARTMENT_ID_SEQUENCE
SQL> show sequence DEPT_ID_S
DEPT_ID_S A synonym for sequence DEPARTMENT_ID_SEQUENCE
Sequence Id: 1
Initial Value: 1
Minimum Value: 1
Maximum Value: 9223372036854775787
Next Sequence Value: 1
Increment by: 1
Next Sequence Value: 1
Increment by: 1
Cache Size: 20
No Order
No Cycle
No Randomize
Wait
SQL>