7 System and Replication Tables

TimesTen stores metadata (information about the contents of your data store) in system tables in your data store.

Your applications can read the system tables, but it cannot update the system tables. If your application defines a table with the same name as a system table, then your application can read a system table by prefixing the system table name with SYS. For example, SELECT * FROM SYS.TABLES selects rows from the TABLES system table. Use the TTREP prefix when using the replication tables.

Information specific to system tables:

Locks acquired by users on system tables may prevent others from defining data or executing the SQLPrepare ODBC function or the Connection.prepareStatement JDBC method.
The last character in name columns is always a space. Therefore, while the column length for name columns is 31, the maximum object name length is 30.
On 64-bit systems, TImesTen system tables declare certain fields as data type TT_BIGINT. When retrieving these columns with an ODBC program, the application must bind them using SQL_C_BINARY. For information about SQL_C_BINARY, see the Microsoft ODBC 2.0 -Programmer's Reference and SDK Guide.

Note:
Some tables contain columns named SYSnumber. Because these columns contain values used internally by TimesTen, they are not documented in this chapter.

Tables and views reserved for internal or future use

Several system tables and views in TimesTen are reserved for internal or future use. These tables are not described in detail in this chapter:

SYS.ALL_EXTERNAL_TABLES
SYS.ALL_SYNONYMS, SYS.DBA_SYNONYMS, SYS.USER_SYNONYMS
SYS.COLUMN_HISTORY
SYS.DIR$
SYS.OBJAUTH$
SYS.SYN$
SYS.SYSTEMSTATS
SYS.TABLE_HISTORY
SYS.USER_ASTATUS_MAP
TTREP.CLIENTFAILOVER

PL/SQL system tables are reserved for internal use. Use the PL/SQL system views instead. PL/SQL system tables in TimesTen are:

SYS.ARGUMENT$
SYS.DEPENDENCY$
SYS.ERROR$
SYS.IDL_CHAR$
SYS.IDL_SB4$
SYS.IDL_UB1$
SYS.IDL_UB2$
SYS.NCOMP_DLL$
SYS.OBJ$
SYS.OBJERROR$
SYS.PLSCOPE_ACTION$
SYS.PLSCOPE_IDENTIFIER$
SYS.PROCEDURE$
SYS.PROCEDUREINFO$
SYS.PROCEDUREPLSQL$
SYS.SETTINGS$
SYS.SOURCE$
SYS.USER$
SYS.WARNING_SETTINGS$

If PL/SQL is enabled in your database, there are tables and views created for the operation of the package UTL_RECOMP:

SYS.UTL_RECOMP_COMPILED
SYS.UTL_RECOMP_ERRORS
SYS.UTL_RECOMP_SORTED
SYS.UTL_RECOMP_ALL_OBJECTS (PL/SQL view)
SYS.UTL_RECOMP_INVALID_ALL (PL/SQL view)
SYS.UTL_RECOMP_INVALID_PARALLEL (PL/SQL view)

These PL/SQL system views are reserved for internal use:

SYS.CODE_PIECES
SYS.CODE_SIZE
SYS.DBA_INVALID_OBJECTS
SYS.DISK_AND_FIXED_OBJECTS
SYS.ERROR_SIZE
SYS.PARSED_PIECES
SYS.PARSED_SIZE
SYS.SOURCE_SIZE

Required privileges to access system tables and views

By default PUBLIC has SELECT privileges on various system tables and views and EXECUTE privileges on various PL/SQL objects. You can see the list of objects by using this query:

SELECT * FROM sys.dba_tab_privs WHERE grantee='PUBLIC';

The ADMIN or SELECT ANY TABLE privilege is required to access other system tables and views unless otherwise noted in the description of the table or view.