V$SESSION_LONGOPS Time Remaining

V$SESSION_LONGOPS with Time Remaining in day, hour, minute, and second format:

col ETA form a20
col TARGET form a30
col MESSAGE form a70
col USERNAME form a12
SELECT *
FROM (SELECT INST_ID
     ,SID
     ,SERIAL#
     ,USERNAME
     ,OPNAME
     ,DECODE(TO_CHAR(TRUNC(ROUND(ELAPSED_SECONDS*(TOTALWORK-SOFAR)/SOFAR)/(24*60*60)))||'d ','0d ',NULL,TO_CHAR(TRUNC(ROUND(ELAPSED_SECONDS*(TOTALWORK-SOFAR)/SOFAR)/(24*60*60)))||'d ','0d ')||
      DECODE(TO_CHAR(TRUNC(MOD(ROUND(ELAPSED_SECONDS*(TOTALWORK-SOFAR)/SOFAR),(24*60*60))/(60*60))) ||'h ','0h ',NULL,TO_CHAR(TRUNC(MOD(ROUND(ELAPSED_SECONDS*(TOTALWORK-SOFAR)/SOFAR),(24*60*60))/(60*60))) ||'h ')||
      DECODE(TO_CHAR(TRUNC(MOD(ROUND(ELAPSED_SECONDS*(TOTALWORK-SOFAR)/SOFAR),(60*60))/60)) ||'s ','0m ',NULL,TO_CHAR(TRUNC(MOD(ROUND(ELAPSED_SECONDS*(TOTALWORK-SOFAR)/SOFAR),(60*60))/60)) ||'m ')||
     TO_CHAR(MOD(ROUND(ELAPSED_SECONDS*(TOTALWORK-SOFAR)/SOFAR), 60)) ||'s' ETA
     ,MESSAGE
     FROM GV$SESSION_LONGOPS
     WHERE TOTALWORK<>SOFAR
     AND SOFAR<>0
     ORDER BY START_TIME DESC);
    

Database Link Syntax

It is tempting to create database links by refencing the aliases from a tnsnames.ora file. e.g.

CREATE DATABASE LINK SCOTTS_DB_LINK
CONNECT TO SCOTT IDENTIFIED BY tiger
USING 'PROD_SERV';

But the "one ring to rule them all" tnsnames.ora approach can come back and bite you if the tnsnames.ora file is ever accidentally lost or - worse - replaced with one that is pointing, say, to Production, rather than Development databases!

Rather than using tnsnames.ora aliases, hard-code instead. e.g.

CREATE DATABASE LINK SCOTTS_DB_LINK
CONNECT TO SCOTT IDENTIFIED BY tiger
USING '(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=PROD01.my.domain)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=PROD_SERVICE))))';

Now, no matter what happens to your tnsnames.ora file, you know that your database link is pointing to the correct database.

Either the database is not running from Oracle Home or the correct pfile was not found

When using dbua to upgrade a database, you might get a pop-up asking for the location of the init.ora, with a message like this:

"Either the database is not running from Oracle Home or the correct pfile was not found" 

Cause:
An earlier upgrade attempt failed, and a dbua file got left "in limbo"

Solution:

[linux1] cd /opt/oracle/cfgtoollogs/dbua/logs

[linux1] ls
PreUpgradeResults.html  sqls.log  trace.log  Welcome_mydb.txt

[linux1] rm Welcome_mydb.txt

From MOS Note: [ID 1303325.1]