Square DBA – Oracle DBA Talk

The Power of Inline Views

A subquery in the FROM clause of a SELECT statement is also called an inline view. When a query contains an inline view, the inline is merged into the query. Powerful constructs can be built using inline views as the next example shows: check the tablespace available, free and used space.

Example

set linesize 100
column file_name format a40 heading "File"
column tablespace_name format a10 heading "Tablespace"
column allocated format 999,999,999 heading "Allocated"
column free format 999,999,999 heading "Free"
column used format 999,999,999 heading "Used"

select a.file_name file_name,
       a.tablespace_name tablespace_name,
       a.bytes allocated,
       nvl(b.free,0) free,
       a.bytes-nvl(b.free,0) used
  from dba_data_files a, (select file_id, sum(bytes) free
                          from dba_free_space
                          group by file_id) b
 where a.file_id = b.file_id (+);

How to enable dead connection detection

Dead database connections can be detected and killed by SQL*Net if you specify the SQLNET.EXPIRE_TIME=n parameter in your SQLNET.ORA file (usually in $TNS_ADMIN). This parameter will instruct SQL*Net to send a probe through the network to the client every n minutes, if the client doesn’t respond, it will be killed.

NOTE: This parameter is only useful on the database server side, specifying it on a client workstation will have no effect.

Oracle Alert Log via SQL with External Tables

Because I love to do things in the database I write little script and functions to keep me in SQLplus. Access the Alert Log is no different. In reality I access the Alert Log inside and outside the database. It’s too important to monitor in one place.

Starting in Oracle9i you can map external flat files to Oracle tables.
Mapping the Oracle alert log is easy and once defined, all you have to do is query it with standard SQL syntax:

create directory BDUMP as '/u01/app/oracle/admin/mysid/bdump';

create table
   alert_log ( msg varchar2(80) )
organization external (
   type oracle_loader
   default directory BDUMP
   access parameters (
      records delimited by newline
   )
   location('alrt_mysid.log')
)
reject limit 1000;

Now we can easily extract important Oracle alert log information without leaving SQL*Plus:

select msg from alert_log where msg like '%ORA-00600%';

Negative Parameters in Oracle Functions

I don’t see a lot of example of using negative values in Oracle functions. I am sure seasoned Oracle professionals are well aware of this functionalty, but are beginners?

Did you know when you use the function ADD_MONTHS you can pass in a negative number?

select add_months('01-Aug-03', 3) from dual;

would return ’01-Nov-03′, however

select add_months('01-Aug-03', -3) from dual;

would return ’01-May-03′

There many built-in Oracle functions that support negative values. Try SUBSTR and see what the results are.

Clean up phone numbers

I needed to clean up a list of phone numbers today. The phone numbers where in a varchar2 column in the database and were listed like this: (916) 555 -1212.

I knew I could used some instr and substr functions to cut the data out, but I wanted to use some of the regex functions found in Oracle 10G and above. This handy little piece of code simply removed all non numeric digits from a string.

select REGEXP_REPLACE(CALLNUMBER,'[^[:digit:]]', NULL)
from temp_cima where rownum < 11;

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.