Hide
Note: This solution is no longer maintained. For the latest information, please visit our Knowledge Base:
https://community.exasol.com/t5/database-features/sys-schema/ta-p/1414
------------------------------------------------------------------------------------------------------------------------------------------------------------------
Background
EXASolution offers numerous system tables describing the metadata of the database and the current
status of the system.
Explanation
General Information
These system tables are located in the "SYS" schema but are automatically integrated into the current namespace. This means that if an object with the same name does not exist in the current schema, they can be queried without stating the schema name, "SYS". Otherwise, the system tables can be accessed via the respective schema-qualified name, SYS.<table_name> (e.g. "SELECT * FROM SYS.DUAL").
There are some system tables that are critical to security, these can only be accessed by users with the "SELECT
ANY DICTIONARY" system privilege (users with the DBA role have this privilege implicitly). This includes all system tables with the "EXA_DBA_" prefix.
There are also system tables to which everyone has access, however, the content of these is dependent on the current user. In EXA_ALL_OBJECTS, for example, only the database objects the current user has access to are displayed.
System table classes
In general EXASolution's system tables divide into three classes:
- DBA: Detailed information for all appropriate objects
- ALL: Limited information on all appropriate objects to which the current user has access (any privilege)
- USER: Detailed information for all appropriate objects owned by the current user
Example:
- EXA_DBA_TABLES shows all tables in the database
- EXA_ALL_TABLES shows all tables to which the current user has access via a privilege
- EXA_USER_TABLES shows all tables owned by the current user
System table types
EXASolution stores metadata for all database object types:
Type name |
Content |
COLUMNS |
Column type, default value, identity value, IS_NULLABLE, comment, … |
CONNECTIONS |
Connection string, user, … |
CONSTRAINTS |
Constraint type (PK, FK, not NULL), ENABLED, … |
CONSTRAINT_COLUMNS |
Constraint schema, table, type, name, referenced schema,table,column |
DEPENDENCIES |
Object name, referenced object, reference type (VIEW, CONSTRAINT) |
FUNCTIONS |
Schema, text, comment, … |
INDICES |
Table, schema, type (GLOBAL, LOCAL), last commit, string representation, … |
OBJECTS |
Object type, created, last commit, owner, comment, … |
OBJECT_SIZES |
Raw and mem size of database objects |
OBJ_PRIVS |
Granted object privileges, grantor, grantee, … |
OBJ_PRIVS_MADE |
granted object privileges, grantor, grantee... |
OBJ_PRIVS_RECD |
granted object privileges, grantor, grantee... |
ROLES |
Name, created, priority, comment |
SCRIPTS |
Name, schema, owner, language, type, return type, text, comment |
SESSIONS |
User, status, duration, activity, … |
SYS_PRIVS |
Granted system privileges, grantee, grantor, … |
TABLES |
Name, schema, owner, comment, … |
USERS |
Name, created, hash value of password, priority, comment, … |
VIEWS |
Name, schema, owner, text, comment, … |
VIRTUAL_COLUMNS |
Schema, Table, Name, object Id, Adapter Notes |
VIRTUAL_SCHEMA_PROPERTIES |
Schema name, Object_id, property name, value |
VIRTUAL_TABLES |
Schema, name, object_id, refresh time, refresher, adapter notes |
Further system tables
Table name |
Content |
EXA_SCHEMAS |
All schemas of the database, owner, comment, … |
EXA_METADATA |
Describes properties of the database (name, version, …) |
EXA_PARAMETERS |
Session and system parameters (query timeout, language, …) |
EXA_SPATIAL_REF_SYS |
All supported spatial reference systems |
EXA_SQL_KEYWORDS |
SQL keywords |
EXA_SQL_TYPES |
SQL data types |
EXA_STATISTICS_OBJECT_SIZES |
Size of statistical system tables |
EXA_TIME_ZONES |
Supported time zones |
Example
If a user wants to query all active sessions, the command the session is executing, the time spent in this command and the database user he can use the following query:
SELECT SESSION_ID, COMMAND_NAME, DURATION, USER_NAME
FROM EXA_ALL_SESSIONS
WHERE STATUS <> 'IDLE'
ORDER BY DURATION DESC;
Show
Note: This solution is no longer maintained. For the latest information, please visit our Knowledge Base:
https://community.exasol.com/t5/database-features/sys-schema/ta-p/1414
------------------------------------------------------------------------------------------------------------------------------------------------------------------
Background
EXASolution offers numerous system tables describing the metadata of the database and the current
status of the system.
Explanation
General Information
These system tables are located in the "SYS" schema but are automatically integrated into the current namespace. This means that if an object with the same name does not exist in the current schema, they can be queried without stating the schema name, "SYS". Otherwise, the system tables can be accessed via the respective schema-qualified name, SYS.<table_name> (e.g. "SELECT * FROM SYS.DUAL").
There are some system tables that are critical to security, these can only be accessed by users with the "SELECT
ANY DICTIONARY" system privilege (users with the DBA role have this privilege implicitly). This includes all system tables with the "EXA_DBA_" prefix.
There are also system tables to which everyone has access, however, the content of these is dependent on the current user. In EXA_ALL_OBJECTS, for example, only the database objects the current user has access to are displayed.
System table classes
In general EXASolution's system tables divide into three classes:
DBA: Detailed information for all appropriate objects
ALL: Limited information on all appropriate objects to which the current user has access (any privilege)
USER: Detailed information for all appropriate objects owned by the current user
Example:
EXA_DBA_TABLES shows all tables in the database
EXA_ALL_TABLES shows all tables to which the current user has access via a privilege
EXA_USER_TABLES shows all tables owned by the current user
System table types
EXASolution stores metadata for all database object types:
Type name
Content
COLUMNS
Column type, default value, identity value, IS_NULLABLE, comment, …
CONNECTIONS
Connection string, user, …
CONSTRAINTS
Constraint type (PK, FK, not NULL), ENABLED, …
CONSTRAINT_COLUMNS
Constraint schema, table, type, name, referenced schema,table,column
DEPENDENCIES
Object name, referenced object, reference type (VIEW, CONSTRAINT)
FUNCTIONS
Schema, text, comment, …
INDICES
Table, schema, type (GLOBAL, LOCAL), last commit, string representation, …
OBJECTS
Object type, created, last commit, owner, comment, …
OBJECT_SIZES
Raw and mem size of database objects
OBJ_PRIVS
Granted object privileges, grantor, grantee, …
OBJ_PRIVS_MADE
granted object privileges, grantor, grantee...
OBJ_PRIVS_RECD
granted object privileges, grantor, grantee...
ROLES
Name, created, priority, comment
SCRIPTS
Name, schema, owner, language, type, return type, text, comment
SESSIONS
User, status, duration, activity, …
SYS_PRIVS
Granted system privileges, grantee, grantor, …
TABLES
Name, schema, owner, comment, …
USERS
Name, created, hash value of password, priority, comment, …
VIEWS
Name, schema, owner, text, comment, …
VIRTUAL_COLUMNS
Schema, Table, Name, object Id, Adapter Notes
VIRTUAL_SCHEMA_PROPERTIES
Schema name, Object_id, property name, value
VIRTUAL_TABLES
Schema, name, object_id, refresh time, refresher, adapter notes
Further system tables
Table name
Content
EXA_SCHEMAS
All schemas of the database, owner, comment, …
EXA_METADATA
Describes properties of the database (name, version, …)
EXA_PARAMETERS
Session and system parameters (query timeout, language, …)
EXA_SPATIAL_REF_SYS
All supported spatial reference systems
EXA_SQL_KEYWORDS
SQL keywords
EXA_SQL_TYPES
SQL data types
EXA_STATISTICS_OBJECT_SIZES
Size of statistical system tables
EXA_TIME_ZONES
Supported time zones
Example
If a user wants to query all active sessions, the command the session is executing, the time spent in this command and the database user he can use the following query:
SELECT SESSION_ID, COMMAND_NAME, DURATION, USER_NAME
FROM EXA_ALL_SESSIONS
WHERE STATUS <> 'IDLE'
ORDER BY DURATION DESC ;