Retrieving Information Related to the Database and Database Objects
Retrieving Information Related to the Database and Database Objects#
Performance Views#
A performance view is a type of data dictionary table capable of inquiring about the server status and database information. The following SELECT statement can be used to view the list of performance views provided by Altibase:
iSQL> SELECT * FROM V$TAB;
TABLE NAME TYPE
---------------------------------------------
V$ALLCOLUMN PERFORMANCE VIEW
V$ARCHIVE PERFORMANCE VIEW
V$BUFFPOOL_STAT PERFORMANCE VIEW
V$DATABASE PERFORMANCE VIEW
V$DATAFILES PERFORMANCE VIEW
V$DISKGC PERFORMANCE VIEW
V$DISKTBL_INFO PERFORMANCE VIEW
V$FLUSHINFO PERFORMANCE VIEW
or the complete list of the performance views provided with Altibase and the meanings of the columns, please refer to the Altibase General Reference Chapter 3: Data Dictionary.
Data in a particular performance view can be queried in the same way as an ordinary table using a SELECT statement, and using JOIN, etc., results can be output in various forms.
Viewing the List of Tables#
Information on all of the tables that exist in the database can be retrieved using the following SELECT statement. The SYS_TABLES_ meta table is an internal system table that contains information about the database catalog provided by Altibase.
iSQL> SELECT * FROM SYSTEM_.SYS_TABLES_;
.
.
iSQL> SELECT * FROM TAB; -- This command is available in iSQL only.
USER NAME TABLE NAME TYPE
-----------------------------------------------
.
..
Viewing a Table Structure#
The following command is used to retrieve information on user-created tables:
DESC table_name;
e.g.,
CREATE TABLE department (
DNO SMALLINT PRIMARY KEY,
DNAME CHAR(30) NOT NULL,
DEP_LOCATION CHAR(9),
MGR_NO INTEGER );
iSQL> DESC department; -- The name of a table whose information (table structure) you want to know.
[ TABLESPACE : SYS_TBS_MEM_DATA ]
[ ATTRIBUTE ]
-----------------------------------------------------------
NAME TYPE IS NULL
-----------------------------------------------------------
DNO SMALLINT FIXED NOT NULL
DNAME CHAR(30) FIXED NOT NULL
DEP_LOCATION CHAR(9) FIXED
MGR_NO INTEGER FIXED
[ INDEX ]
-----------------------------------------------------------
NAME TYPE IS UNIQUE COLUMN
-----------------------------------------------------------
__SYS_IDX_ID_122 BTREE UNIQUE DNO ASC
[ PRIMARY KEY ]
-----------------------------------------------------------
DNO
Use double quotation marks if the table name contains special characters or spaces.
iSQL> DESC "table name";
iSQL> DESC "user name"."table name";
Viewing Sequence Information#
The following commands are used to obtain information on all sequences that exist in the database:
SELECT * FROM SEQ;
e.g.,
iSQL> CONNECT sys/manager;
Connect success.
iSQL> CREATE USER user1 IDENTIFIED BY user1;
Create success.
iSQL> CONNECT user1/user1;
Connect success.
iSQL> CREATE SEQUENCE seq1 MAXVALUE 100 CYCLE;
Create success.
iSQL> CREATE SEQUENCE seq2;
Create success.
iSQL> CONNECT sys/manager;
Connect success.
iSQL> CREATE SEQUENCE seq2 START WITH 20 INCREMENT BY 30;
Create success.
iSQL> CREATE SEQUENCE seq3 CACHE 40;
Create success.
iSQL> SELECT * FROM SEQ; -- When accessing the database using the SYS account, information of all sequences will be displayed.
USER_NAME SEQUENCE_NAME CURRENT_VALUE INCREMENT_BY MIN_VALUE MAX_VALUE CYCLE CACHE_SIZE
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SYS SEQ2 30 1 9223372036854775806 NO 20
SYS SEQ3 1 1 9223372036854775806 NO 40
USER1 SEQ1 1 1 100 YES 20
USER1 SEQ2 1 1 9223372036854775806 NO 20
4 rows selected.
iSQL> CONNECT user1/user1;
Connect success.
iSQL> SELECT * FROM SEQ; -- Information of all sequences created by User 1 will be displayed.
SEQUENCE_NAME CURRENT_VALUE INCREMENT_BY MIN_VALUE MAX_VALUE CYCLE CACHE_SIZE
-------------------------------------------------------------------------------------------------------------------------------------------------------------
SEQ1 1 1 100 YES 20
SEQ2 1 1 9223372036854775806 NO 20
2 rows selected.