System Tables Related to Database Operation

{question}

1. Do you have any dictionary tables/views which I can query from?

2. In Oracle we use v$session table so we can see all the sessions which are connected to the database.

{question}

{answer}

1. In NuoDB that kind of information is stored in tables in the NuoDB SYSTEM schema. Originally these tables were used internally for NuoDB operations, however in many cases they can provide useful information to NuoDB users. We keep more the 60 tables with useful information, you can review these tables in our documentation here.

To view these tables, you can run this query in NuoSQL:

SQL> show schema system;

Or, 

SQL> SELECT tablename, extends, type, cardinality FROM SYSTEM.TABLES WHERE schema = 'SYSTEM';
      TABLENAME       EXTENDS      TYPE     CARDINALITY 
-------------------- -------- ------------ ------------
TABLES                <null>  SYSTEM TABLE    <null>    
FIELDS                <null>  SYSTEM TABLE    <null>    
TABLECONSTRAINTS      <null>  SYSTEM TABLE    <null>    
FORMATS               <null>  SYSTEM TABLE    <null>    
DUAL                  <null>  SYSTEM TABLE    <null>    
INDEXES               <null>  SYSTEM TABLE    <null>    
INDEXFIELDS           <null>  SYSTEM TABLE    <null>    
FOREIGNKEYS           <null>  SYSTEM TABLE    <null>    
DOMAINS               <null>  SYSTEM TABLE    <null>    
VIEW_TABLES           <null>  SYSTEM TABLE    <null>    
SCHEMAS               <null>  SYSTEM TABLE    <null>    
PROPERTIES            <null>  SYSTEM TABLE    <null>    
VERSIONS              <null>  SYSTEM TABLE    <null>    
DATATYPES             <null>  SYSTEM TABLE    <null>    
DEFAULTSTORAGEGROUPS  <null>  SYSTEM TABLE    <null>    
PARTITIONEDTABLES     <null>  SYSTEM TABLE    <null>    
PARTITIONIDS          <null>  SYSTEM TABLE    <null>    
PARTITIONCRITERIA     <null>  SYSTEM TABLE    <null>    
PRIVILEGES            <null>  SYSTEM TABLE    <null>    
ROLES                 <null>  SYSTEM TABLE    <null>    
PASSWORDS             <null>  SYSTEM TABLE    <null>    
USERS                 <null>  VIEW            <null>    
USERROLES             <null>  SYSTEM TABLE    <null>    
PRIVTYPES             <null>  SYSTEM TABLE    <null>    
DEPENDENCIES          <null>  SYSTEM TABLE    <null>    
SEQUENCES             <null>  SYSTEM TABLE    <null>    
TRIGGERS              <null>  SYSTEM TABLE    <null>    
TRIGGERCLASSES        <null>  SYSTEM TABLE    <null>    
PROCEDURES            <null>  SYSTEM TABLE    <null>    
JAVACLASSES           <null>  SYSTEM TABLE    <null>    
PROCEDURECOLUMNS      <null>  SYSTEM TABLE    <null>    
FUNCTIONS             <null>  SYSTEM TABLE    <null>    
FUNCTIONCOLUMNS       <null>  SYSTEM TABLE    <null>    
ALLSYSTEMFIELDS       <null>  VIEW            <null>    
ALLSYSTEMTABLES       <null>  VIEW            <null>


2. In NuoDB we keep the connection information in SYSTEM.CONNECTIONS table;

SQL> set output vertical;
SQL> SELECT sqlstring, count, runtime, user, schema, numparam, params, connid, open, handle, nodeid, execid FROM system.connections;

 

  • You can review the table's fields here.
  • In this case it's better to use SET OUTPUT VERTICAL because there are many columns in this table and it is more readable if displayed this way.

{answer}

Have more questions? Submit a request

Comments