Wednesday, January 24, 2007

Shrinking Oracle table sizes

This morning I ran into this great post, when I was wandering on the internet.
It points out that Oracle does not free storage space with the delete statement. You can use alter table ... move defragments and rebuild the indexes.

How do I enable autotrace for oracle

If you run into the following error messages when you try to set autotrace on in sqlplus for oracle, you may need to enable autotrace for oracle and grant plustrace role to the user you are using.
SP2-0618: Cannot find the Session Identifier. Check PLUSTRACE role is enabled
SP2-0611: Error enabling STATISTICS report

  1. Run plustrce.sql in SYS schema if it did not run before, $ORACLE_HOME\sqlplus\admin\plustrce.sql
  2. Grant plustrace role to
Please refer to here.

Oracle v$session_longops view

During one meeting, one of my colleagues mentioned Oracle's v$session_longops view.
"This view displays the status of various operations that run for longer than 6 seconds (in absolute time). These operations currently include many backup and recovery functions, statistics gathering, and query execution, and more operations are added for every Oracle release."
Further reference is here and here.

Wednesday, January 17, 2007

How to decide whether any of the two strings is null in Oracle?

I ran into a problem in my project, I have to decide whether there is a string in a list of strings is null or not. The problem is we have two columns from the Oracle DB: first_name, last_name, both of them could be null, we need to format the fullname like "last_name, first_name", but we do not to have the ',' if either last_name or first_name is null. Here comes my solution:
select last_name || nvl2(length(last_name) * length(first_name), ',', null) || first_name from contact_info;

The terminal server has exceeded the maximum number of allowed connections?!

From time to time, I ran into the following warning(errors) when I tried to remote desktop to one of our development box. Here is something I found to figure out who has left his connected connections on the desktop, maybe you can boot them out, if you have permission:
1. mstsc /v: /console
Replace with your server's IP Address.
2. Start Terminal Services Manger (Administrative Tools -> Terminal Services Manager, or tsadmin.exe).

Here are some details.

Thursday, January 11, 2007

Having trouble to install Oracle Lite Win32?!

Senario1: For some unknown reason, I screwed up my local installation of the Oracle Lite Win32 client.
Every time when I try to install Oracle Lite Win32 client on my desktop, the setup program always stops at "Stopping clients", finally I found out a workaround for it:
Just remove or rename the c:\windows\odbc.ini and c:\windows\polite.ini file, give the installation a retry.
Now you should have a smoother ride ;-)
Senario2: If your Oracle Lite WIN32 uninstallation program halt on "Loading 'Oracle Lite WIN32'", then you could try to kill the Oracle Lite Device Manger process (dmagent), retry the uninstallation process. You can use command line: taskkill /F /T /IM dmagent.exe