SYS schema

General Information EXASolution offers numerous system tables describing the metadata of the database and the current status of the system. 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 ;