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.

WFH in Corona Times

Its been more than 4 months since we started WFH due to the pandemic. The experience has kept varying over the period. Here is how it went for me.

1. The initial couple of weeks – The first few days were a bit relaxing and a little confusing since we got WFH in bulk. That saved time but also meant need for better co ordination. We were testing the waters and that also meant multiple innovations, confusions all over the place .
2. From 15 days to a month – Once it became clear this was going to continue, there was a phase where it became depressing. No contact with folks, long hours and no going out meant spending a lot of time in confined spaces. This was when I took a step back and worked on this. Which lead me to the third phase.
3. The next couple of months – This was where I decided to make the best use of the extra time available. Put up a routine where I started and ended the day at approximately the same time, took breaks in between. This was also the time of the year where I took week long breaks, this time it was not possible due to the work, so it was essential to use the weekends properly to rest and recharge. Also used this time to learn more about technology etc. Took a couple of courses.
4. The last month – this has again led to a bit of stagnation. Days are not managed properly. Rains begun and cut down on the time I could step out. And more lockdowns and the fear of the virus has made it difficult to stick to a routine. So its time to revisit the schedule and make changes.

Here is what I think we should do

1. Keep making changes to schedule ever so often. That will bring a difference and keep things fresh.
2. Challenge yourself to do better. Be it learning, engaging with others etc
3. Take breaks. May be take a couple of days off and just read a book , or binge watch movies. Catch up with friends online .
4. Good time to think deeply about your career, relationships etc.

Thats it .. Be positive, stay safe.

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