How to extract all datafile names without path using substr and instr functions

select trim(
            substr(file_name,
                (instr(file_name,'/', -1, 1) +1)
                )
               )  "file name"
from  dba_data_files;

Result:

file name
system01.dbf
sysaux01.dbf
undotbs01.dbf
users01.dbf
appl_data.dbf

How to use dbms_application_info to create session details

The package dbms_application_info lets you add supplemental information in your session, which can be picked up in the V$SESSION view.

A very useful package, which I have used many times myself, but for some reason it is rarely used by Developers. It should typically be called before beginning a transaction in order to register and name a transaction/client_info/module for later use when measuring performance across an application.

exec dbms_application_info.set_client_info('Step one.');
exec dbms_application_info.set_module('module1','gettrades');

Here is an example where I was troubleshooting a "hanging" delete statement for a customer.
Turned out the problem was concurrency locks:

select SID,SERIAL#,SQL_ID,STATUS,osuser,machine,schemaname,MODULE,ACTION,CLIENT_INFO,LOGON_TIME,EVENT,STATE,FINAL_BLOCKING_SESSION "blocking"
from v$session 
WHERE schemaname='SALES'
AND STATUS='ACTIVE'
UNION
select SID,SERIAL#,SQL_ID,STATUS,osuser,machine,schemaname,MODULE,ACTION,CLIENT_INFO,LOGON_TIME,EVENT,STATE,FINAL_BLOCKING_SESSION "blocking"
from v$session 
where SID = (SELECT FINAL_BLOCKING_SESSION FROM V$SESSION WHERE schemaname='SALES' AND STATUS='ACTIVE');

In the resulting two rows, the first one is mine, the second is the blocking session:

SID	SERIAL#	SQL_ID	STATUS	OSUSER	MACHINE	SCHEMANAME	MODULE	ACTION	CLIENT_INFO	LOGON_TIME	EVENT	STATE	blocking
32	4759	5jg1839cyxzgh	ACTIVE	oracle	mytestserver.com	SALES	DELETE MODULE	DELETE_SCHEMA	Delete XSD.	15.02.2018 12:04:43	library cache lock	WAITING	2285
2285	48625	g3bc37vx8fy3u	INACTIVE	JIM	COMPANY\PC-157	SALES	SQL*Plus			14.02.2018 14:08:57	SQL*Net message from client	WAITING	NULL

For more information, check the documentation
Tracing Enhancements Using DBMS_MONITOR (In 10g, 11g and Above) (Doc ID 293661.1) at My Oracle Support.

SYSTIMESTAMP and SYSDATE VS. CURRENT_TIMESTAMP and CURRENT_DAY

Most DBAs I've spoken to are familiar with the function SYSDATE, but there are some other functions, too, that may prove to be useful, even for DBAs that aren't directly involved in developing applications.
For example, when querying the data dictionary, it can certainly be an advantage to be familiar with some other built-in date functions that comes with the Oracle database.

The two first functions reflect the datetime settings of the operating system on which the database runs:

* The SYSTIMESTAMP function returns a value of the TIMESTAMP WITH TIME ZONE datatype. It includes fractional seconds and time zone.
* the SYSDATE function returns a value of the DATE datatype.It includes timestamp, but not fractional seconds, nor time stamp.

The three functions below reflects the settings of your session:

* The CURRENT_DATE fuction returns a value of the DATE datatype within the session time zone
* The CURRENT_TIMESTAMP function returns a value of the TIMESTAMP WITH TIME ZONE data type within the session time zone
* The LOCALTIMESTAMP function returns a value of the TIMESTAMP data type, within the session time zone

Sitting in Oslo and querying a database with dbtimezone set to +2:00:

select dbtimezone from dual;

DBTIME
+02:00

I get the following default output when I execute the queries at 11:45:

select sysdate, systimestamp, current_date,current_timestamp(5), localtimestamp(3) from dual

   
SYSDATE
   
SYSTIMESTAMP
   
CURRENT_DATE
   
CURRENTTIMESTAMP
   
LOCALTIMESTAMP
   
13.07.2016 11:45:52
   
13.07.2016 11.45.52,597707 +02:00 
   
13.07.2016 11:45:52 
 13.07.2016 11.45.52,59771 +02:00 
  
 13.07.2016 11.45.52,598

Now I will change my session time zone:

alter session set time_zone='America/New_York';

Session altered.

Execute the functions again, and the difference between the function becomes apparent:

select sysdate, systimestamp, current_date,current_timestamp(5), localtimestamp(3) from dual;

SYSDATE	SYSTIMESTAMP	CURRENT_DATE	CURRENTTIMESTAMP	LOCALTIMESTAMP
13.07.2016 11:49:15	13.07.2016 11.49.15,381888 +02:00	13.07.2016 05:49:15	13.07.2016 05.49.15,38189 AMERICA/NEW_YORK	13.07.2016 05.49.15,382

Current_date now returns the date and time as it would be in New York
Current_timestamp does the same, but adds the region name as the time zone indicator
Localtimestamp returns the timestamp as it would be in New York

The precision in the current_timestamp and localtimestamp (5 and 3, respectively) are set to override the default precision of 6 for fractional seconds.

How to use multiple delimiters in awk

When creating a script for cloning, I wanted to perform some initial checks. One of them was to make sure the listener for the source database was up. For this, I wanted to use the tnsping utility and grep for the result:

tnsping mydb

where mydb corresponds to the tnsnames.ora alias of interest.

* If there was an error, exit script.
* If listener was up, continue.

I directed the output to a file, and after that grepped for the string that would indicate success or failure.
Problem was that the files would look very different and the interesting string would need to be grepped for using two different delimiters.

When successful, the file would look as follows:

OK (10 msec)

When not successful, the output would be:

TNS-03505: Failed to resolve name

So how to pull out either "TNS" or "OK" and use these values programmatically further on in the script?

I accomplished this task simply by using the notation '[-(]'in my awk statement:

REMOTE_LSNR_STATUS=`cat ${LOG_DIR}/test_tnsping.log | egrep -e 'TNS-|OK' | awk -F '[-(]' '{ print $1 }'`

Using the procedure SEARCHFILES in DBMS_BACKUP_RESTORE

I found the following great article about how to list files in directory from within sqlplus here, written by David Marco. As pointed out by several oracle bloggers, there is very little documentation around for this package.

It just so happened that a user was in need of such functionality, and his code could be used straight-off.

However, my customer had already ammended a very useful functionality, by adding support for sending the directory name in as a parameter.

The code as it looks now:

CREATE OR REPLACE FUNCTION LIST_FILES( p_directory IN VARCHAR2, p_file_pattern IN VARCHAR2 default null)
RETURN file_array pipelined AS

l_path VARCHAR2(1024);
l_dummy VARCHAR2(1024);

BEGIN

   SELECT directory_path
   INTO l_path
   FROM all_directories
   WHERE directory_name = p_directory;

   sys.DBMS_BACKUP_RESTORE.SEARCHFILES(l_path, l_dummy);

   FOR file_list IN (SELECT FNAME_KRBMSFT AS file_name
                     FROM X$KRBMSFT
                     WHERE FNAME_KRBMSFT LIKE '%'|| NVL(p_file_pattern, FNAME_KRBMSFT)||'%' ) LOOP
      PIPE ROW(file_list.file_name);
   END LOOP;

END;
/

The call will thus be:

SELECT * FROM TABLE(LIST_FILES('MY_DIR','%*.dmp%'));

How to save a value in a bind variable

Useful for tracing or explaining queries that use bind variables:

connect scott/tiger
var leom char(10);
var eod char(10);

exec :leom := to_date('30.09.2013','DD.MM.YYYY');
exec  :eod := to_date('31.10.2013','DD.MM.YYYY');

SELECT col1, col2 ....col n
FROM   TABLE1
WHERE  START_DATE <= :eod
AND    END_DATE   <= :leom;