Query :
SELECT PARAMETER_ID, PARAMETER_VALUE, MODIFIED_BY
FROM PARAMETER_VALUES as of scn 236522284 WHERE PARAMETER_ID = 1;
Returns the below error : *
ERROR at line 1:
ORA-01555: snapshot too old: rollback segment number 10 with name "_SYSSMU10_930580995$" too small
DB parameters are as follows :
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_flashback_retention_target integer 1440
temp_undo_enabled boolean FALSE
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS1
UNDOTBS1 has one datafile, which is 13 GB of size.
SQL> select tablespace_name, retention from DBA_TABLESPACES where upper(tablespace_name) like '%UNDO%'
2 ;
TABLESPACE_NAME RETENTION
------------------------------ -----------
UNDOTBS1 NOGUARANTEE
The query execution and scn times are as follows:
SQL> select to_char(sysdate,'DD-MM-YYYYhh24:mi:ss') as CD from dual;
CD
------------------
26-08-202117:47:10
SQL> select scn_to_timestamp(236522284) as TS from dual;
---------------------------------------------------------------------------
26-AUG-21 12.46.19.000000000 PM
query is executed within the db_flashback_retention_target (i.e within 1 day) yet why this error ?
is the only solution available, is to increase undo_retention ?
what are the performance consequences of increasing undo_retention ?
what is the relation between db_flashback_retention_target and undo_retention ?
although undo_retention is for 15 minutes, it is noted that same query for certain SCNs ( which are around 1 hour old) executes successfully without ORA-01555. how is this possible ?
how is tablespace retention GUARANTEE vs NOGUARANTEE affect in this case ?