marți, 11 noiembrie 2014

Need a lot of undo capabilities? You have to set this...

When you experience the "error ORA-01555: snapshot too old", i am sure you wish to have had larger undo provisioned space.

sql>  show parameter undo_retention

undo_retention                       integer     300000

Keep in mind the displayed value is measured in seconds. The first thought will be to raise ad infinitum the value of the above parameter. But this is not posible, because you need a lot of space, and the database storage is not endless. Anyway, the database engine will not honour any request of keeping undo information beyond of its capabilities. Will erase the oldest undo info and will continue to keep in shape the data. 
So, you need to set properly the undo_retention parameter. Take the following script and use it to obtain the optimal value:

select (SELECT SUM(a.bytes)
 FROM v$datafile a,
 v$tablespace b,
 dba_tablespaces c
 WHERE c.contents = 'UNDO'
 AND c.status = 'ONLINE'
 AND = c.tablespace_name
 AND a.ts# = b.ts#)

( SELECT TO_NUMBER(value)   FROM v$parameter  WHERE name = 'db_block_size') -
(SELECT MAX(undoblks/((end_time-begin_time)*3600*24)

)   FROM v$undostat)) 
"undo_retention optimal value" 
from dual;

marți, 4 noiembrie 2014

How to workaround a hanging situation when run an Oracle APEX saved report

The problem seems to be impossible, at first sight. As a regular user you cannot do anything but wait.
Log on as developer or admin OF THE WORKSPACE WHERE THE REPORT BELONGS. Go to:

Home>>Administration>>Manage Service>>Interactive Report Settings (link on the right of the page) >>Saved Reports

 select the desired saved report and click <delete>. Unfortunately, there is no other way to stop the hung report. Of course, maybe it will need to rebuild the report, but, anyway, it was designed in a poorly fashion, since is not finishing the run and displaying some data.