I am tagged, so lets continue. Here is my life

Tonguc who is a milestone for my Oracle career, tagged me in his blog. What I understand from tagging is, after you give 8 information about yourlife, you tag someone else but I won’t tag anyone because I am always the last stop of forwarding

Here are my secrets.

1- I was born in Turkey years ago. Nobody in my life related with IT and to be honest, I touched computer for the first time in University when I was 18. My childhood dream was to be a James Bond, but now I am a DBA with only one gadget named Oracle… Maybe it is the only magical gadget I should need to be James Bond

2- if I am not dealing with databases, My life is mostly in gym . If I have to make a choice I should choose to go gym. I am addicted to endorphine and gym makes me secrete endorphine more than the databases make.

3- I am the fan of Galatasaray the only football team of Turkey which is known by real success, not by transfers:)

4- I moved to London this year and I think I will live here forever. Yes, I love dark weather.

5- I will engage with my girlfriend on this Saturday. Sometimes you have to disable your firewall against marriage My old release Coskan_28_m will be replaced by Coskan_28_m_m (male_married) in first quarter of 2008.

6- I still cannot use English effectively , but as you see, this is not secret, it is just a confess to myself:) Anyway, I could not write in Turkish too. None of my teachers gave good grade to my essays ((.

I dream a world on which only demonstrations talks on blogs

7- I was an ex fan of Definitive Oracle Guru till 2006 the year that I started to test what I read.

8- Sometimes I think to sell my non-existent ferrari, but I think, first I have to be a monk and after that get permission from my future wife to go Tailand for education. (I started to think like a married man I think I am ready )

Thank you for following this blog. It is nice to be read by people around the world. I was’t expecting over 50000 hit in one year but I reached despite my English I hope you you get something from this blog.

If you access a database link in a session, then the link remains open until you close the session (ORA-02020)

I hit this ORA-02020 error for the first time today and it helped me to learn the truth behind the DB_LINK (I have to confess that I did not read before ). The truth is,” if you access a database link in a session, then the link remains open until you close the session. A link is open in the sense that a process is active on each of the remote databases accessed through the link.”

My case was;

I created a cursor and run a statement which uses all the DB_LINKS on the server. The number of db_links I used was more than 30. The code was getting the ORA-02020:too many database links in use error.

The definition and the solution of the error was simple

Cause:	The current session has exceeded the INIT.ORA open_links maximum.
Action:	Increase the open_links limit, or free up some open links by committing or rolling back the transaction and canceling open cursors that reference remote databases.

I checked the open_links parameter (I was unaware that this parameter was existed) and saw that parameter had the default value which was 4 and cannot be modified online. I took the advised action and added commit clause before the cursor gets the new value for the db_link. This solution worked very well.

By the way Document says that, I can close a database link session with “alter session close database link XXXX” clause but it did not worked as I expected. You still need to do commit or rollback before closing a db_link session to do that.  Also  after commit, the session  stays  on the remote machine but the sessions are put on list to be closed when a new db_link opened. (list works last in first out (before commit) fashion as can be seen from demonstration ).

Cole Listing 9

The only benefit of alter session close database link is closing a link with knowledge otherwise LIFO will work after commit.

 References Used;

 Oracle® Database Administrator’s Guide 10g Release 2 (10.2)