AskTom

Hi,

As SYS
============================================================================
SQL> SELECT flashback_on, log_mode
FROM v$database;  2

FLASHBACK_ON       LOG_MODE
—————— ————
YES                ARCHIVELOG

SQL> set linesize 121
col name format a30
col value format a30

SELECT name, value
FROM gv$parameter
WHERE name LIKE ‘%flashback%’;SQL> SQL> SQL> SQL>   2    3

NAME                           VALUE
—————————— ——————————
db_flashback_retention_target  1440

SQL> SELECT estimated_flashback_size
FROM gv$flashback_database_log;  2

ESTIMATED_FLASHBACK_SIZE
————————
263331840

SQL> SELECT current_scn
FROM v$database;  2

CURRENT_SCN
———–
2876929432

SQL> SELECT oldest_flashback_scn,
oldest_flashback_time
FROM gv$flashback_database_log;  2    3

OLDEST_FLASHBACK_SCN OLDEST_FL
——————– ———
2876844665 15-AUG-09

SQL> GRANT flashback any table TO test;

Grant succeeded.

As TEST
============================================================================
SQL> show user
USER is “TEST”
SQL>    create table t (
mycol VARCHAR2(20))
ROWDEPENDENCIES;  2    3

Table created.

SQL> INSERT INTO t VALUES (’ABC’);

1 row created.

SQL> INSERT INTO t VALUES (’DEF’);

1 row created.

SQL> commit;

Commit complete.

SQL> CREATE RESTORE POINT bef_damage;

Restore point created.

SQL> INSERT INTO t VALUES (’GHI’);

1 row created.

SQL> commit;

Commit complete.

SQL> SELECT ora_rowscn, mycol FROM t;

ORA_ROWSCN MYCOL
———- ——————–
2876930705 ABC
2876930705 DEF
2876930762 GHI

As SYS
============================================================================
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount exclusive;
ORACLE instance started.

Total System Global Area 1043886080 bytes
Fixed Size                  2166288 bytes
Variable Size             301990384 bytes
Database Buffers          729808896 bytes
Redo Buffers                9920512 bytes
Database mounted.
SQL> FLASHBACK DATABASE TO RESTORE POINT bef_damage;

Flashback complete.

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

SQL> alter database open resetlogs;

Database altered.

alert.log
———-
FLASHBACK DATABASE TO RESTORE POINT bef_damage
Flashback Restore Start
Flashback Restore Complete
Flashback Media Recovery Start
Fast Parallel Media Recovery enabled
parallel recovery started with 2 processes
Recovery of Online Redo Log: Thread 1 Group 3 Seq 7 Reading mem 0
Mem# 0: /opt/oradata/TPROD/onlinelog/o1_mf_3_58b7213v_.log
Incomplete Recovery applied until change 2876930740 time 08/17/2009 06:56:54
Flashback Media Recovery Complete
Completed: FLASHBACK DATABASE TO RESTORE POINT bef_damage

As TEST
============================================================================

SQL> connect test/test123
Connected.
SQL> SELECT ora_rowscn, mycol FROM t;

ORA_ROWSCN MYCOL
———- ——————–
2876930705 ABC
2876930705 DEF

I used testing scenario from psoug

Regards,

Tom

Hi,

this is easy example how flashback drop working.
SQL> CREATE TABLE t1 (
testcol DATE);  2

Table created.

SQL> DROP TABLE t1;

Table dropped.

SQL> SELECT object_name, original_name
FROM user_recyclebin;  2

OBJECT_NAME                    ORIGINAL_NAME
—————————— ——————————–
BIN$cRpMvRASmyPgQEAKogoa8w==$0 T1

SQL> desc t1
ERROR:
ORA-04043: object t1 does not exist

SQL> FLASHBACK TABLE t1 TO BEFORE DROP;

Flashback complete.

SQL> desc t1
Name                                      Null?    Type
—————————————– ——– —————————-
TESTCOL                                            DATE

Regards,

Tom