Difference between revisions of "Flashback query with database in noarchivelog mode"
From dbawiki
| (3 intermediate revisions by the same user not shown) | |||
| Line 2: | Line 2: | ||
You can still flashback to any time within the online redo logs: | You can still flashback to any time within the online redo logs: | ||
| − | + | alter session set nls_timestamp_format = 'dd-mon-yyyy hh24:mi:ss.ff3'; | |
| − | |||
| − | |||
set lines 9999 | set lines 9999 | ||
| − | set | + | set trims on |
set pages 9999 | set pages 9999 | ||
| − | set | + | set headi off |
spool old_version.sql | spool old_version.sql | ||
| − | select text from dba_source | + | select text |
| − | + | from dba_source | |
| − | where owner=' | + | as of timestamp to_timestamp('22-sep-2011 07:30:00.000') |
| − | + | where 1=1 | |
| − | order by type, line; | + | and owner = 'scott' |
| + | and name = 'mypackage' | ||
| + | order by type | ||
| + | , line; | ||
spool off | spool off | ||
Latest revision as of 15:44, 30 November 2011
Typically, development databases are not started in archivelog mode. This causes a problem for hot backups but not necessarily for flashback queries. You can still flashback to any time within the online redo logs:
alter session set nls_timestamp_format = 'dd-mon-yyyy hh24:mi:ss.ff3';
set lines 9999
set trims on
set pages 9999
set headi off
spool old_version.sql
select text
from dba_source
as of timestamp to_timestamp('22-sep-2011 07:30:00.000')
where 1=1
and owner = 'scott'
and name = 'mypackage'
order by type
, line;
spool off
