Oracle : Key Tables

This lists some of the tables that are of good use when looking at data at the session level.
You may have to login as the system user to access some of the tables

1. v$session_longops: Has data regarding operations that are running on the database.
2. v$session: Has data about the session.
3. dba_jobs: Has information about the jobs in the database.
4. dict: Has information about the system tables.
5. dba_source : Has the source for all objects. Useful for finding out which line of your procedure/function is causing errors. The line # in the error message is not the same as in your source code.
6. dba_tablespaces : Has information about the tablespaces in the Database.
7. DBA_TAB_PRIVS: Has information about the grants given to all the objects in the database. Can use this to find out grants given to a user for any object in the database.


Oracle database directory

Oracle database directory is used when uploading lobs into the database. When we need to do this, the file must be stored in the database directory and the reference given during lob upload.

The database directory is a physical directory on the machine where the database is running.
DBA_DIRECTORIES table has data for all the directories .

To add a new directory in the database , need to run the following as SYS user

CREATE DIRECTORY MY_DIR AS ‘/home/database/mydir’;

Logging in applications – Approach

I work as a Java Developer and two things that I think are not given the due consideration by many developers(me included) are error handling and logging. What makes it important is that there are no fast rules on the two though we do use thumb rules. In this post I will put forth my thoughts on logging.

Many times we hear people saying “Log everything” and developers to be on the “safe” side, put lots of logging information. While it certainly helps in debugging, there are disadvantages:

1. Logs involve I/O: This is expensive and causes overhead on your system and can slow down your program.
2. Too many logs are counterproductive: The main purpose of having logs is that, once the program goes into production, you cannot “debug” it in case of issues like you did in “development” systems. Hence application logs help you by giving the direction taken by the program execution.
If the logs are too bulky, then looking for this direction can be a nightmare. Needle, haystack anyone..
3. Logs occupy space: Logs are physical files and occupy space. Based on your requirements and strategy, you may want to retain logs for certain period of time. Unnecessarily heavy logs eat up on disk space and can cause issues within a short span of time.

So what is a good logging strategy?
Whatever one may say about excessive logging, it is true that we do need logging so that in case of issues we are able to fix it quickly. We do need to strike a balance between logging and its performance.

If you use an utility like Log4J for logging, you can configure it to log only those statements that you need. Hence your code can have all the logger statements at any granularity, but your log files will be light. If you really need to go fine grain, you just change the configuration.

In this case, the trick really is to decide which statement needs what level of logging. There can only be pointers to these and no hard rules

1. Trace: The lowest level: Log every state of object. Frankly , I am yet to use this in my code till now.
2. Debug: The most used one. Use this to log inputs got/ values returned in a method, or intermediate states after some state changes.
3. Info: Information about a business decision taken. X is this and Y is this so we are doing that
4. Warn: You see some business logic not working as expected but not a cause for alarm. Can be used to highlight corner cases .
5. Error: There has been a business logic failure. The sequence of operations is not maintained. The workflow cannot be carried on without fixing these .. mostly inputs from users.
6. Fatal: System error unable to proceed with the workflow at all. Possibly database connection reset, IO operation failure etc.

Keep in mind the following when logging:

1. When a logger statement is encountered, the statement is written and then the logger checks for the logging level . Say the statement is debug but the level is fatal, the object is created and then discarded. In log4j logging, always use logger.isDebugEnabled(), logger.isInfoEnabled etc
2. Loggers are not immune from NullPointerException !!: Quite a few times had this situation when a logger was causing a failure . So try to log direct values of objects than doing getters .

Will write about my experience in Error handling in a later post

Job run details

select j.what,v.SID, v.id2 JOB, j.FAILURES,
LAST_DATE, substr(to_char(last_date,'HH24:MI:SS'),1,8) LAST_SEC,
THIS_DATE, substr(to_char(this_date,'HH24:MI:SS'),1,8) THIS_SEC,
j.field1 INSTANCE, c.inst_id, c.sid, c.serial#, p.spid, 'size='||t.used_ublk*16384/1024/1024||'mb' usedmb,c.event,C.SQL_ID
from sys.job$ j, gv$lock v, gv$session c, gv$process p, gv$transaction t
where v.type = 'JQ' and j.job (+)= v.id2 and v.sid=c.sid and c.paddr=p.addr and c.inst_id=p.inst_id
and v.inst_id=c.inst_id and t.inst_id(+)=c.inst_id and t.addr(+)=c.taddr;

Use the sql_id got to get details of the run

select sql_text,executions from gv$sqlarea where sql_id =