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

Recompiling broken objects in Oracle

Sometimes , changing procedures, functions etc breaks other objects in the datbase. To recompile all broken objects, run the following as SYSTEM user on the database

@?/rdbms/admin/utlrp.sql

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 =

Oracle: Create and Drop user

Create User: http://www.dba-oracle.com/concepts/create_users.htm

Drop Ref: http://www.dba-oracle.com/t_oracle_drop_user_cascade.htm

Alter table

Reference:

http://www.techonthenet.com/sql/tables/alter_table.php

Is a very good page on alter table syntax

Running javadoc

A quick handy reference to run javadocs from the command prompt:

% javadoc -d DOC_OUTPUT_DIR -sourcepath SOURCE_PATH -subpackages LIST_OF_PACKAGES separated by space

further ref: http://java.sun.com/j2se/1.4.2/docs/tooldocs/windows/javadoc.html

Vi: shortcut to replace word

Go to the relevant word. To replace it type cw . Then enter the word. To replace the line from the cursor onwards type c$ and type the replacement.

To do the same change on multiple lines: Go to the position on the line where the change needs to be done. Then type . This will do the same replacement on the other line too

Catchback: Oracle Reg Expressions

This is useful in cases where there is a need to replace only a part of a text and the before and after parts have to be retained.

Eg: http://mywebsite/page/1.htm has to be changed to http://yoururl/page/1.htm

select regexp_replace(URL, 'http://.*mywebsite(.*)','http://yoururl\1')
from table


Using the (.*) to put the /page/1.htm is called catchback

Getting sql plans

One of our sql procedure executions took a lot of time. But it was not being monitored when it was executing. So we need a way to figure out the execution plan for the query so we can pinpoint the problem. That is where the oracle history tables helped us

select * from dba_hist_sqltext
where sql_text like '%TABLE_NAME%'

We knew the table name which was being accessed and so got the sql_id for the execution.

Next step was to get the execution plan:


select * from DBA_HIST_SQL_PLAN
where sql_id = 'ABCDEF';


This showed us that the plan was using NESTED loops for execution.

The respective tables for run time monitoring are gv$sqltext and gv$sql_plan