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>