"TT0802: Data store space exhausted" and "TT0778: Log write failed because filesystem is full" errors

A few days ago, we have encounter a timesten error:

[TimesTen][TimesTen 11.2.1.4.0 ODBC Driver][TimesTen]TT0802: Data store space exhausted -- file "blk.c", lineno 3260, procedure "sbBlkAlloc"

*** ODBC Error/Warning = S1000, TimesTen Error/Warning = 802

[TimesTen][TimesTen 11.2.1.4.0 ODBC Driver][TimesTen]TT6220: Permanent data partition free space insufficient to allocate 5640 bytes of memory -- file "blk.c", lineno 3260, procedure "sbBlkAlloc"

*** ODBC Error/Warning = S1000, TimesTen Error/Warning = 6220

When we analyze, we see that the PermSize parameter is insufficient for loading database into the memory.  We update the PermSize in the sys.odbc.ini file and restart timesten daemon.(you can read this post for restarting timesten daemon)

sys.odbc.ini

[tt01]

…

PermSize=51200

….

After fixing this error, we got another error:

[TimesTen][TimesTen 11.2.1.4.0 ODBC Driver][TimesTen]TT0778: Log write failed because filesystem is full -- file "logmgr.c", lineno

6065, procedure "sbLogRecInsert"

*** ODBC Error/Warning = S1000, TimesTen Error/Warning = 778

This error is because of DataStore path have not enough space as the parameter PermSize.

$ df -k .

/timesten         (/dev/lvtimesten) :  5205785 total allocated Kb

                                                 558788 free allocated Kb

                                                 4646997 used allocated Kb

                                                    90 % allocation used

sys.odbc.ini

[tt01]

..

DataStore=/timesten/TimesTen/tt1121/DataStore/ttds

…

PermSize=51200

After giving enough disk space to the filesytem, error is gone.

/dev/lvtimesten

                   58490880 4660048 50466552    8% /timesten

TimesTen In Memory Database - Data Source Name(DSN)

Timesten In memory database has a configuration file which named as sys.odbc.ini. In this file, DSN(Data Source Name)s  are defined and some configurations are setup( DataStore and PermSize parameters are defined and can be configured with requirements.). A sample of this file is shown below. DSN name is tt01 and PermSize is 10000 Mb and DataStore is saved in the /timesten/TimesTen/tt1121/DataStore/ttds directory, and so on…

[tt01]

Driver=/timesten/TimesTen/tt1121/lib/libtten.so

DataStore=/timesten/TimesTen/tt1121/DataStore/ttds

DatabaseCharacterSet=US7ASCII

AutoCreate=1

Logging=1

DurableCommits=0

LockLevel=0

Temporary=0

PermSize=10000

LogBufMB=64

PrivateCommands=1

CkptFrequency=0

CkptLogVolume=0

Isolation=1

Connections=64

TypeMode=1

When you connect timesten database with ttisql, the information is printed out.

$ ttisql tt01

Copyright (c) 1996-2009, Oracle.  All rights reserved.

Type ? or "help" for help, type "exit" to quit ttIsql.

connect "DSN=tt01";

Connection successful: DSN=tt01;UID=g112adm;DataStore=/timesten/TimesTen/tt1121/DataStore/ttds;DatabaseCharacterSet=US7ASCII;ConnectionCharacterSet=US7ASCII;DRIVER=/timesten/TimesTen/tt1121/lib/libtten.so;Isolation=1;PermSize=10000;Connections=64;CkptFrequency=0;CkptLogVolume=0;PrivateCommands=1;TypeMode=1;LogBufMB=64;

(Default setting AutoCommit=1)

If you change PermSize parameter of this file, during connection, an info message is printed and points out that the new parameter has not been taken into consideration. Suppose that you have changed PermSize as 5000 in the sys.odbc.ini file.

[tt01]

…

PermSize=5000

….

$ ttisql tt01

Copyright (c) 1996-2009, Oracle.  All rights reserved.

Type ? or "help" for help, type "exit" to quit ttIsql.

connect "DSN=tt01";

Warning  6226: Ignoring value requested for first connection attribute 'PermSize' -- value currently in use: 10000, requested value: 5000

Connection successful: DSN=tt01;UID=g112adm;DataStore=/timesten/TimesTen/tt1121/DataStore/ttds;DatabaseCharacterSet=US7ASCII;ConnectionCharacterSet=US7ASCII;DRIVER=/timesten/TimesTen/tt1121/lib/libtten.so;Isolation=1;PermSize=51200;Connections=64;CkptFrequency=0;CkptLogVolume=0;PrivateCommands=1;TypeMode=1;LogBufMB=64;

(Default setting AutoCommit=1)

You should stop&start timesten daemon in order to take affect the changed parameter:

$ ttDaemonAdmin -stop

TimesTen Daemon stopped.

Daemon is stopped. If you want to connect timesten db, you will get an error:

$ ttisql tt01

Copyright (c) 1996-2009, Oracle.  All rights reserved.

Type ? or "help" for help, type "exit" to quit ttIsql.

connect "DSN=tt01";

  799: Unable to connect to daemon; check daemon status

The command failed.

Done.

After starting daemon, you can connect.

$ ttDaemonAdmin -start

TimesTen Daemon startup OK.

It is also possible to check daemon status:

$ ttStatus

TimesTen status report as of Wed Mar 27 18:51:04 2013

Daemon pid 16195 port 59000 instance tt1121

TimesTen server pid 16200 started on port 59001

------------------------------------------------------------------------

Data store /timesten/TimesTen/tt1121/DataStore/ttds

DATA STORE IN PROCESS OF BEING LOADED INTO RAM

There are no connections to the data store

Obsolete or not yet active connection(s):

Process 16196 context 0x60000000000a7d40 name Manager connid 0, nascent connection, shmKey 0xE8C11AD PLSQL shmKey 0xF8C11AD

RAM residence policy: Always

Replication policy  : Manual

Cache Agent policy  : Manual

PL/SQL enabled.

------------------------------------------------------------------------

Accessible by group g112

End of report

Please note that, if it is configured, the database is flushed into the memory. You will see the message(DATA STORE IN PROCESS OF BEING LOADED INTO RAM). It means that, please wait the flush operation , it is in-progress now.

Oracle - Drop Table If Exists

Oracle does not have a drop table statement to drop a table if exists, like some other RDBMSes does. If you do not want get error messages for a non-existent table drop operation, you can follow workarounds as I showed below. It may be useful, during setup processes, especially you don't want to generate error message to the user,  if the table that you are dropping does not exist.

--CREATE TABLE MENNAN.TABLE_EXAMPLE AS SELECT * FROM DUAL;

---SIMPLE WAY, if execute immediate fails, because table not exists, transaction will be committed

BEGIN

  EXECUTE IMMEDIATE 'DROP TABLE MENNAN.TABLE_EXAMPLE';

EXCEPTION

  WHEN OTHERS THEN

    IF SQLCODE <> -942 THEN

      RAISE;

    END IF;

END;

/

---- NAMED EXCEPTION, the same as SIMPLE WAY

DECLARE

  ve_TableNotExists EXCEPTION;

  PRAGMA EXCEPTION_INIT(ve_TableNotExists, -942);

  vs_DynamicDropTableSQL VARCHAR2(1024);

  vs_TableName           VARCHAR2(64);

BEGIN

  vs_TableName           := 'MENNAN.TABLE_EXAMPLE';

  vs_DynamicDropTableSQL := 'DROP TABLE ' || vs_TableName;

  EXECUTE IMMEDIATE vs_DynamicDropTableSQL;

EXCEPTION

  WHEN ve_TableNotExists THEN

    dbms_output.put_line(vs_TableName || ' not exist, skipping....');

  WHEN OTHERS THEN

    dbms_output.put_line(SQLERRM);

    RAISE;

END;

/

---- NAMED EXCEPTION, the same as SIMPLE WAY but more general one

DECLARE

  ve_TableNotExists EXCEPTION;

  PRAGMA EXCEPTION_INIT(ve_TableNotExists, -942);

  PROCEDURE DropTable(pis_TableName IN VARCHAR2) IS

    vs_DynamicDropTableSQL VARCHAR2(1024);

  BEGIN

 

    vs_DynamicDropTableSQL := 'DROP TABLE ' || pis_TableName; 

    EXECUTE IMMEDIATE vs_DynamicDropTableSQL;

   

  EXCEPTION

    WHEN ve_TableNotExists THEN

      dbms_output.put_line(pis_TableName || ' not exist, skipping....');

    WHEN OTHERS THEN

      dbms_output.put_line(SQLERRM);

      RAISE;

  END DropTable;

 

 

BEGIN

  DropTable('MENNAN.TABLE_EXAMPLE');

END;

/

---Yet another way, first check from dictionary, if exists drop the table. Checking from dictionary may take time but avoids unnecessary transactions

DECLARE

  vs_TableName VARCHAR2(64);

  vn_Count     PLS_INTEGER;

BEGIN

  vs_TableName := 'MENNAN.TABLE_EXAMPLE';

 

  SELECT COUNT(*)

    INTO vn_Count

    FROM ALL_TABLES

   WHERE OWNER || '.' || TABLE_NAME = vs_TableName;

  IF vn_Count > 0 THEN

    EXECUTE IMMEDIATE 'DROP TABLE ' || vs_TableName;

  END IF;

END;

/