SQL*Plus SET NULL Statement

I have used the NVL function in several other posts but here is another example. First a table is created. It has never been analyzed so its last_analyzed date is null. The NVL function spots this and replaces the null by the text, Not yet:

SQL> create table andrew (col1 number)
  2  /

Table created.

SQL> select nvl(to_char(last_analyzed),'Not yet')
  2  from dba_tables
  3  where table_name = 'ANDREW'
  4  /

NVL(TO_CH
---------
Not yet

SQL>

If you don't like the NVL function, you can use the SQL*Plus SET NULL statement instead. Here is one way to use it:

SQL> set null 'Null'
SQL> select last_analyzed from dba_tables
  2  where table_name = 'ANDREW'
  3  /

LAST_ANAL
---------
Null

SQL>

And here is another:

SQL> col last_analyzed null Never
SQL> select last_analyzed from dba_tables
  2  where table_name = 'ANDREW'
  3  /

LAST_ANAL
---------
Never

SQL>

V$SESSION_LONGOPS

You can query long running SQL in V$SESSION_LONGOPS.

 

In the example shown, the SQL is a simple delete statement so I have managed to shorten the output by using a col sql_text format a20. Normally it is much longer.

 

Each time you run the SQL, the figures are recalculated. The elapsed_seconds column, which I have renamed as time_taken, should increase every time the SQL is rerun.

 

The time_remaining column, which I have renamed as time_left, is only an estimate. Normally it goes down each time the SQL is rerun but sometimes it goes up.

 

Once the SQL is finished, the executions column changes to 1 and the time_remaining column goes to 0:

 

  1  SELECT SQL_TEXT, EXECUTIONS,

  2  ELAPSED_SECONDS TIME_TAKEN,

  3  TIME_REMAINING TIME_LEFT

  4  FROM V$SESSION SES, V$SQL SQL,

  5  V$SESSION_LONGOPS LONGOPS

  6  WHERE SES.USERNAME       = 'BRAID'

  7  AND   SES.SQL_ADDRESS    = SQL.ADDRESS

  8  AND   SES.SQL_HASH_VALUE = SQL.HASH_VALUE

  9  AND   SQL.ADDRESS        = LONGOPS.SQL_ADDRESS

 10* AND   SQL.HASH_VALUE     = LONGOPS.SQL_HASH_VALUE

SQL> /

 

SQL_TEXT             EXECUTIONS TIME_TAKEN  TIME_LEFT

-------------------- ---------- ---------- ----------

delete b_alp                  0        204       1010

 

SQL> /

 

SQL_TEXT             EXECUTIONS TIME_TAKEN  TIME_LEFT

-------------------- ---------- ---------- ----------

delete b_alp                  0        710        507

 

SQL> /

 

SQL_TEXT             EXECUTIONS TIME_TAKEN  TIME_LEFT

-------------------- ---------- ---------- ----------

delete b_alp                  1       1220          0

 

SQL>