Square DBA - Oracle DBA Talk

Changing the location of trace files

The location of Oracle trace and log files can be changed.  I normally recommend leaving them in the default location, but there are reasons to put them somewhere else. In SQLPlus type:

show parameter

This will display all of the non-hidden parameters.  Generally any of these can be modified with a ALTER SYSTEM or ALTER DATABASE.  Remember you will need to set the scope.  SCOPE=SPFILE; SCOPE=MEMORY; or SCOPE=BOTH;

When is ROWNUM evaluated?

I had someone ask me today when is ROWNUM evaluated?  Is it before the WHERE clause?  How about before or after the GROUP BY clause?  The answer is pretty simple.

ROWNUM is a pseudo-column that returns a row’s position in a result set. ROWNUM is evaluated AFTER records are selected from the database and BEFORE any sorting takes place.   So ROWNUM is evalated BEFORE an ORDER BY and BEFORE a GROUP BY.

Locating the Oracle Database Version

Ever need to know what version your database is running?  Well as the DBA you should already know.  I also know what major version I am running on all of my instances, however I am not always shure what patchset I am running.  To look it up it is pretty simple.

SQL> SELECT * FROM SYS.V_$VERSION;

or

SQL> SELECT * FROM v$instance

Rebuild indexes & gather statistics after table shrink.

Just a quick tip.  When you finish a table segment shrink, it is a good practice to rebuild any indexes on that table as well as gather statistics on that table. These tasks help Oracle to create a better execution plan for the SQL query related to the table.

Tip: Getting rid of spaces after TO_CHAR

Have you ever notice the exatra space in you output when using the TO_CHAR function?  Want to get rid of it?  Here is how.

If I run this script:

select to_char(1234.89, '$999,990.00') num_format
from dual
/
select to_char(SYSDATE, 'Day, Month DD, YYYY') date_format
from dual
/

I get:

NUM_FORMAT
------------
   $1,234.89

1 row selected.

DATE_FORMAT
-----------------------------
Friday   , August    14, 2006

1 row selected.

Do you notice the lead spacing in the first statement and the odd spacing in the second?  That can be annoying. To get rid of it, just use the FM format mask modifier.

Now I can run this:

select to_char(1234.89, 'FM$999,990.00') num_format
from dual
/
select to_char(SYSDATE, 'FMDay, Month DD, YYYY') date_format
from dual
/

And I get:

NUM_FORMAT
------------
$1,234.89   

1 row selected.

DATE_FORMAT
-----------------------------
Friday, August 14, 2006      

1 row selected.