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.

log_archive_format default value

Having trouble finding out why the value of the parameter LOG_ARCHIVE_FORMAT does not seem to apply to your database?

From Oracle 10g and onwards, the default value for LOG_ARCHIVE_FORMAT is %t_%S_%r.dbf

However, this parameter will only make a difference if you are NOT using a "Fast Recovery Area" (in Version 10 Oracle called the same thing a "Flash Recovery Area")

If you have defined a FRA by using the parameters db_recovery_file_dest and db_recovery_file_dest_size, the archivelogs will be automatically named according to OMF (Oracle Managed Files) standard and the value of LOG_ARCHIVE_FORMAT will be ignored.

An example of an OMF managed file in the FRA:

/fra/PRODDB01/archivelog/2014_06_30/o1mv_1_1_9vxzch5_.arc

From the documentation:

"The fast recovery area is an Oracle Database managed space that can be used to hold RMAN disk backups, control file autobackups and archived redo log files. The files placed in this location are maintained by Oracle Database and the generated file names are maintained in Oracle Managed Files (OMF) format."

At first glance, letting the LOG_ARCHIVE_FORMAT default to such a poor naming standard may seem like a bad idea, but I guess it illustrates the point of defining an FRA vs. using conventional archive log destinations.

How to gather dictionary statistics and fixed-objects statistics

Use the procedures

dbms_stats.gather_dictionary_stats
and
dbms_stats.gather_fixed_objects_stats

Examples:

BEGIN
   DBMS_STATS.GATHER_DICTIONARY_STATS (
     estimate_percent  => DBMS_STATS.AUTO_SAMPLE_SIZE
     ,method_opt        => 'FOR ALL COLUMNS SIZE AUTO'
     ,degree            => NULL
     ,cascade           => DBMS_STATS.AUTO_CASCADE
     ,granularity       => 'AUTO'
     ,no_Invalidate     => DBMS_STATS.AUTO_INVALIDATE);
 END;
 /

 BEGIN
   DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;
 END;
 /

Used in a simple shell script:

#!/usr/bin/ksh
# Gather dictionary and fixed objects stats as recommended by oracle

cat << EoF > ${SCRIPT_BASE}/sql/gather_dict_stats.sql
alter session set nls_date_format='DD-MM-YYYY HH24:MI:SS';
spool ${SCRIPT_BASE}/log/gather_dict_stats.log append
set timing on
select 'Start time: ' || sysdate as "startime" from dual;
execute dbms_stats.gather_dictionary_stats(estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE,method_opt=>'FOR ALL COLUMNS SIZE AUTO',degree=>DBMS_STATS.DEFAULT_DEGREE,granularity=>'ALL',cascade=>DBMS_STATS.AUTO_CASCADE,options=>'GATHER AUTO',no_invalidate=>DBMS_STATS.AUTO_INVALIDATE);
execute dbms_stats.gather_fixed_objects_stats(NULL);
select 'End time: ' || sysdate as "endtime" from dual;
exit
EoF
sqlplus / as sysdba @${SCRIPT_BASE}/sql/gather_dict_stats.sql
rm -r ${SCRIPT_BASE}/sql/gather_dict_stats.sql
exit

Good sources for further reading: Maria Colgan's blog

Where to find the database characterset in the dictionary

SQL> select value$ from sys.props$ where name = 'NLS_CHARACTERSET';

 VALUE$
 --------------------------------------------------------------------------------
 WE8MSWIN1252

 SQL> select * from nls_database_parameters WHERE PARAMETER='NLS_CHARACTERSET';

 PARAMETER                      VALUE
 ------------------------------ ----------------------------------------
 NLS_CHARACTERSET               WE8MSWIN1252

How to work around dbca error "The Oracle system identifier ... already exists, specify another SID"

If you have previously created a database through dbca and saved it as a template for future use, you may see the following error when trying to specify the same SID again:

The Oracle system identifier ... alerady exists, specify another SID

Solution to the problem is to remove the entry for the SID in /etc/oratab

Then click next in dbca, and the configuration wizard will proceed.

Thanks to Elena for pointing this out in her blog.

Potential cause of ORA-12547: TNS:lost contact

I was running a script against the database directly on the host, when the script timed out with:

ERROR:
 ORA-12547: TNS:lost contact

After a search on Oracle's support site I found doc 555565.1: Troubleshooting ORA-12547 TNS: Lost Contact

One potential cause was incorrect permissions on the oracle binary in the $ORACLE_HOME/bin directory. I checked my old OH and compared it with the new OH:

server1>cd $ORACLE_HOME
server1>pwd
 /u01/oracle/product/11204
server1>ls -latr bin/oracle
 -rwxr-x--x    1 ora11g   dba       309704130 Jun 11 11:00 bin/oracle
server1>ls -latr /u01/oracle/product/11.2.0.2/bin/oracle
 -rwsr-s--x    1 ora11g   dba       279291858 Mar 12 13:42 /u01/oracle/product/11.2.0.2/bin/oracle

As can be seen from the above statement, the oracle server executable was lacking the setuid bit

This bit is needed, since we need to make sure that any user who runs the executable file will inherit the user ID of the owner (or group) of the executable file. Note that LOCAL connections would work just fine, even if the permissions on the oracle executable is set incorrectly; only connections that are passed from the listener to the Oracle server are affected. These sessions must be able to spawn a new server process.

Oracle points out that this error is commonly seen "in environments where the listener is running in the GRID home and servicing connections to an instance in a different $ORACLE_HOME."

Connections via the listener are failing with ORA-12547. It is likely in this scenario that LOCAL or BEQ connections to the instance are successful.

Solution to my particular problem this time was to give $ORACLE_HOME/bin/oracle the proper permissions:

server1> sqlplus / as sysdba
SQL> shutdown immediate

server1>chmod 6751 oracle
server1>ls -latr oracle
 -rwsr-s--x    1 ora11g   dba       309704130 Jun 11 11:00 oracle

server1> sqlplus / as sysdba
SQL> startup

After this change the script ran through with no ORA-12547 errors.

Sources: Oracle System Administration Guide