What is the difference between the columns PID and SPID in v$process?

The PID column is an internal counter that oracle uses for its own processes. It is incremented by one for every new process. Consequently, since every session creates its own server process (also called "shadow process") you will see the number increment by one for each session that connects.

The documentation for V$PROCESSES states that PID is the "Oracle Process Identifier" which is confusing.
In most circumstances, the column that you would need is the SPID, which shows the "Operating system process identifier".

More on the TO_DSINTERVAL conversion function

The TO_DSINTERVAL function is very flexible.
All you need to do is to specify the interval you're looking for, and it will convert your string into a valid INTERVAL datatype. Intervals are DAY, HOUR, MINUTE, SECOND

Examples:

Turn 2 days into a valid INTERVAL datatype

SELECT TO_DSINTERVAL(INTERVAL '2' DAY)
 FROM DUAL;

 TO_DSINTERVAL(INTERVAL'2'DAY)
 --------------------------------------------------
 +02 00:00:00.000000
 1 row selected.

Turn 75 minutes into a valid INTERVAL datatype

SELECT TO_DSINTERVAL(INTERVAL '75' MINUTE)
 FROM DUAL;

 TO_DSINTERVAL(INTERVAL'75'MINUTE)
 --------------------------------------------------
 +00 01:15:00.000000
 1 row selected.

Turn 60 minutes into a valid INTERVAL datatype

SELECT TO_DSINTERVAL(INTERVAL '60' MINUTE)
 FROM DUAL;

 TO_DSINTERVAL(INTERVAL'60'MINUTE)
 --------------------------------------------------
 +00 01:00:00.000000
 1 row selected.

Turn 60 seconds into a valid INTERVAL datatype

SELECT TO_DSINTERVAL(INTERVAL '60' SECOND)
 FROM DUAL;

 TO_DSINTERVAL(INTERVAL'60'SECOND)
 --------------------------------------------------
 +00 00:01:00.000000
 1 row selected.

Turn 24 hours into a valid INTERVAL datatype

SELECT TO_DSINTERVAL(INTERVAL '24' HOUR)
 FROM DUAL;

 TO_DSINTERVAL(INTERVAL'24'HOUR)
 --------------------------------------------------
 +01 00:00:00.000000
 1 row selected.

Turn 6 hours into a valid INTERVAL datatype

SELECT TO_DSINTERVAL(INTERVAL '6' HOUR)
 FROM DUAL;

 TO_DSINTERVAL(INTERVAL'6'HOUR)
 --------------------------------------------------
 +00 06:00:00.000000
 1 row selected.

Turn 61.2 seconds into a valid INTERVAL datatype

SELECT TO_DSINTERVAL(INTERVAL '61.2' SECOND)
 FROM DUAL;

 TO_DSINTERVAL(INTERVAL'61.2'SECOND)
 --------------------------------------------------
 +00 00:01:01.200000
 1 row selected.

Turn 1 hour into a valid INTERVAL datatype

SELECT TO_DSINTERVAL(INTERVAL '1' HOUR)
 FROM DUAL;

 TO_DSINTERVAL(INTERVAL'1'HOUR)
 --------------------------------------------------
 +00 01:00:00.000000
 1 row selected.

A convenient way to declare variables in PL/SQL

In PL/SQL programming, a convenient (and programmatically safe) way to specify data types for your variables is to let them be inherited from the table columns:

v_tab_name DBA_TABLES.TABLE_NAME%TYPE;
v_tabspc_name DBA_TABLES.TABLESPACE_NAME%TYPE;

How to drop stuck purge job created through DBMS_AUDIT_MGMT

In the process of implementing automatic purging of the audit trail, I came across a problem that I was really puzzled by: In the debugging process I had to drop and recreate the purge job that I had previously created through the dbms_audit_mgmt package.

I was completely unable to drop a particular scheduler job. All attempts threw different errors:

Trying to drop the job through the dbms_audit_mgmt.drop_purge_job procedure:

dbms_audit_mgmt.drop_purge_job(audit_trail_purge_name=>'PURGE_ALL_AUDIT_TRAILS');

Then trying to drop it directly through the dbms_scheduler.drop_job procedure:

dbms_scheduler.drop_job(job_name=>'PURGE_ALL_AUDIT_TRAILS',force=>TRUE);

Both attempts returned the error:

ERROR at line 1:
ORA-27475: "SYS.PURGE_ALL_AUDIT_TRAILS" must be a job
ORA-06512: at "SYS.DBMS_ISCHED", line 224
ORA-06512: at "SYS.DBMS_SCHEDULER", line 657
ORA-06512: at line 2

Trying to enable the job explicitly, in case the scheduled job was in an unknown state to the database:

exec dbms_scheduler.enable(name=>'PURGE_ALL_AUDIT_TRAILS');

which resulted in

ERROR at line 1:
ORA-27476: SYS.PURGE_ALL_AUDIT_TRAILS does not exist
ORA-06512: at "SYS.DBMS_ISCHED", line 4440
ORA-06512: at "SYS.DBMS_SCHEDULER", line 2803
ORA-06512: at line 1

Trying to recreate the job:

BEGIN
    DBMS_AUDIT_MGMT.create_purge_job(
      audit_trail_type           => DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL,
      audit_trail_purge_interval => 24 /* hours */,
      audit_trail_purge_name     => 'PURGE_ALL_AUDIT_TRAILS',
      use_last_arch_timestamp    => TRUE);
  EXCEPTION
    WHEN OTHERS THEN
     DBMS_OUTPUT.PUT_LINE(sqlerrm(sqlcode));
  END;
  /

Result:

ORA-46254: 'PURGE_ALL_AUDIT_TRAILS' already exists

Particuarly the last two error messages were discouraging. You cannot enable the job, because it doesn't exist, neither can you recreate the job, since it already DOES exist.

Solution:
From Oracle Support 1252235.1 "Unable to Create or Drop Purge Job Using DBMS_AUDIT_MGMT".

In short, log on as user sys and execute the following SQL statements:

SELECT JOB_NAME,STATE
FROM DBA_SCHEDULER_JOBS
WHERE JOB_NAME = 'PURGE_ALL_AUDIT_TRAILS'; <-- No rows should be returned

SELECT JOB_NAME,JOB_STATUS,AUDIT_TRAIL
FROM DBA_AUDIT_MGMT_CLEANUP_JOBS
WHERE JOB_NAME = 'PURGE_ALL_AUDIT_TRAILS'; <-- 1 row should be returned, the job that's "stuck"

DELETE FROM SYS.DAM_CLEANUP_JOBS$
WHERE JOB_NAME = 'PURGE_ALL_AUDIT_TRAILS'; <-- Delete the row from one of Oracle's internal tables,

COMMIT; <-- commit the change

And you're done, your job can be recreated, after which it will turn up as usual when you query the DBA_SCHEDULER_JOBS view:

SELECT STATE,ENABLED
FROM DBA_SCHEDULER_JOBS
WHERE  JOB_NAME = 'PURGE_ALL_AUDIT_TRAILS';

STATE	ENABLED
SCHEDULED	TRUE

How to purge the log entries for a specific dbms_scheduler job

exec dbms_scheduler.purge_log(log_history=>0, job_name=>'YOUR_JOB_NAME');

Zero is actually default for log_history, it doesn't have to be specified.

If you want to save some of the logs, you can specify for example that Oracle should only purge logs older than, say, two days:

exec dbms_scheduler.purge_log(log_history=>2, job_name=>'YOUR_JOB_NAME');

How to work around ORA-01830: date format picture ends before converting entire input string

When querying a column of type TIMESTAMP WITH TIMEZONE, I wanted to omit the fractional seconds + the time zone information.

The query

 SELECT STATE,
        START_DATE "START DATE"
 FROM   DBA_SCHEDULER_JOBS
 WHERE  OWNER = 'SYS' AND JOB_NAME = 'PURGE_ALL_AUDIT_TRAILS';

would return

STATE      START DATE
---------  --------------------------------
SCHEDULED  02.07.2014 13:351:16,558701 +02:00

and I simply didn't want to display that level of information. When trying to format the string as follows:

TO_DATE(START_DATE,'DD.MM.YYYY HH24:MI:SS') "START DATE"

I ran into the error:

   TO_DATE(START_DATE,'DD.MM.YYYY HH24:MI:SS') "START DATE"
                *
 ERROR at line 2:
 ORA-01830: date format picture ends before converting entire input string

Workaround:
In your session, set

ALTER SESSION SET NLS_TIMESTAMP_FORMAT='DD.MM.YYYY HH24:MI:SS';
ALTER SESSION SET NLS_DATE_FORMAT='DD.MM.YYYY HH24:MI:SS';

Then cast the data type from TIMESTAMP WITH TIMEZONE to TIMESTAMP:

CAST(START_DATE AS TIMESTAMP)

and finally, convert to DATE:

TO_DATE(CAST(START_DATE AS TIMESTAMP),'DD.MM.YYYY HH24:MI:SS')

Put together:

SELECT STATE,
        TO_DATE(CAST(START_DATE AS TIMESTAMP),'DD.MM.YYYY HH24:MI:SS') "START DATE"
 FROM   DBA_SCHEDULER_JOBS
 WHERE  OWNER = 'SYS' AND JOB_NAME = 'PURGE_ALL_AUDIT_TRAILS';

Output:

STATE           START DATE
 --------------- ------------------------------
 SCHEDULED       02.07.2014 13:51:16

Another example that worked for me was against a column defined like this:

  DATELASTUPDATED  TIMESTAMP(6)                 NOT NULL,

In my resultset I would only like dates from 29.12.2016, the time of the day is not interesting. Simply use the fuction TRUNC:

WHERE  trunc(DateLastUpdated) = '29.12.2016'

How to print the database incarnations from V$DATABASE_INCARNATION using an hierarchical query

I just realized that V$DATABASE_INCARNATION is a perfect candidate for using hierarchical queries for formatting.

Let's start with current incarnation as root node, then work our way down the older incarnations:

SELECT INCARNATION#, LPAD(' ', LEVEL*2) || STATUS "STATUS", RESETLOGS_TIME,RESETLOGS_CHANGE#,PRIOR_INCARNATION#
FROM V$DATABASE_INCARNATION
START WITH INCARNATION# = 4
CONNECT BY PRIOR PRIOR_INCARNATION# = INCARNATION#;


INCARNATION# STATUS               RESETLOGS_ RESETLOGS_CHANGE# PRIOR_INCARNATION#
------------ -------------------- ---------- ----------------- ------------------
           4   CURRENT            30.06.2014      136759423847                  3
           3     PARENT           07.05.2013      130807968161                  2
           2       PARENT         14.02.2011           1002970                  1
           1         PARENT       07.10.2010                 1                  0

The line

CONNECT BY PRIOR PRIOR_INCARNATION# = INCARNATION#

indicate where the recursion starts, and should be should be read as

"print the rows that has INCARNATION# equal to the previous row's PRIOR_INCARNATION#"

In the reverse order, starting with the oldest incarnation, and working our way through the incarnation list:

SELECT INCARNATION#,LPAD(' ', LEVEL*2) || STATUS "STATUS" , RESETLOGS_TIME, RESETLOGS_CHANGE#,PRIOR_INCARNATION#
FROM V$DATABASE_INCARNATION
START WITH INCARNATION# = 1
CONNECT BY PRIOR_INCARNATION# =  PRIOR INCARNATION#;

INCARNATION# STATUS               RESETLOGS_ RESETLOGS_CHANGE# PRIOR_INCARNATION#
------------ -------------------- ---------- ----------------- ------------------
           1   PARENT             07.10.2010                 1                  0
           2     PARENT           14.02.2011           1002970                  1
           3       PARENT         07.05.2013      130807968161                  2
           4         CURRENT      30.06.2014      136759423847                  3

Here, the line

CONNECT BY PRIOR_INCARNATION# = PRIOR INCARNATION#

should be read as

"print the next rows that has its PRIOR_INCARNATION# equal to the previous row's INCARNATION#"

I find the semantics of the recursion to be important; when you understand what you're actually selecting, it's so much easier to construct the SQL statement and get it right the first time around.