how to solve this error in oracle, trying to flashback to a certain time using sql

how to solve this error in oracle, trying to flashback to a certain time using sql

ERROR at line 1:
ORA-38796: Not enough flashback database log data to undo FLASHBACK.

what is the actual code to solve the problem.
lojaynAsked:
Who is Participating?
 
JacobfwConnect With a Mentor Commented:
24hour time?
Should have been FLASHBACK DATABASE TO TIMESTAMP(TO_DATE(16-OCT-11 16:49:48'));
0
 
lojaynAuthor Commented:
oracle database
0
 
lojaynAuthor Commented:
here is the my work :

SQL> DROP TABLE PATIENT_HISTORY;
DROP TABLE PATIENT_HISTORY
           *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL>
SQL> CREATE TABLE PATIENT_HISTORY
  2  (PATIENT_ID NUMBER,
  3  PATIENT_FNAME  VARCHAR2(40),
  4  PATIENT_LNAME  VARCHAR2(40),
  5  WARD_NO           NUMBER(2));

Table created.

SQL>
SQL> INSERT INTO PATIENT_HISTORY VALUES(1,'JOEY','LANNINGS',2);

1 row created.

SQL> INSERT INTO PATIENT_HISTORY VALUES(2,'SMITH','STEVENSON',3);

1 row created.

SQL> INSERT INTO PATIENT_HISTORY VALUES(3,'MARIO','RAJ',2);

1 row created.

SQL> INSERT INTO PATIENT_HISTORY VALUES(4,'HARRY','CUMMINGS',2);

1 row created.

SQL> INSERT INTO PATIENT_HISTORY VALUES(5,'APRIL','NORWOOD',3);

1 row created.

SQL> INSERT INTO PATIENT_HISTORY VALUES(6,'EAN','SHERRY',3);

1 row created.

SQL> INSERT INTO PATIENT_HISTORY VALUES(7,'SCOTT','PETERS',2);

1 row created.

SQL> INSERT INTO PATIENT_HISTORY VALUES(8,'NATALIE','COLINS',1);

1 row created.

SQL> INSERT INTO PATIENT_HISTORY VALUES(9,'ROB','RIDER',1);

1 row created.

SQL> INSERT INTO PATIENT_HISTORY VALUES(10,'SID','AYRTON',2);

1 row created.

SQL> COMMIT;

Commit complete.

SQL>
SQL> SELECT* FROM PATIENT_HISTORY
  2  ;

PATIENT_ID PATIENT_FNAME
---------- ----------------------------------------
PATIENT_LNAME                               WARD_NO
---------------------------------------- ----------
         1 JOEY
LANNINGS                                          2

         2 SMITH
STEVENSON                                         3

         3 MARIO
RAJ                                               2


PATIENT_ID PATIENT_FNAME
---------- ----------------------------------------
PATIENT_LNAME                               WARD_NO
---------------------------------------- ----------
         4 HARRY
CUMMINGS                                          2

         5 APRIL
NORWOOD                                           3

         6 EAN
SHERRY                                            3


PATIENT_ID PATIENT_FNAME
---------- ----------------------------------------
PATIENT_LNAME                               WARD_NO
---------------------------------------- ----------
         7 SCOTT
PETERS                                            2

         8 NATALIE
COLINS                                            1

         9 ROB
RIDER                                             1


PATIENT_ID PATIENT_FNAME
---------- ----------------------------------------
PATIENT_LNAME                               WARD_NO
---------------------------------------- ----------
        10 SID
AYRTON                                            2


10 rows selected.

SQL> SELECT CURRENT_TIMESTAMP FROM DUAL;

CURRENT_TIMESTAMP
---------------------------------------------------------------------------
16-OCT-11 04.49.48.929000 PM -05:00

SQL> DROP TABLE PATIENT_HISTORY;

Table dropped.

SQL>  SELECT CURRENT_TIMESTAMP FROM DUAL;

CURRENT_TIMESTAMP
---------------------------------------------------------------------------
16-OCT-11 04.51.26.179000 PM -05:00

SQL> SHUTDOWN IMMEDIATE
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> STARTUP MOUNT;
ORACLE instance started.

Total System Global Area  293601280 bytes
Fixed Size                  1248624 bytes
Variable Size             117441168 bytes
Database Buffers          171966464 bytes
Redo Buffers                2945024 bytes
Database mounted.
SQL> ALTER SESSION SET NLS_DATE_FORMAT='DD-MON-YY HH24:MI:SS';

Session altered.

SQL> FLASHBACK DATABASE TO TIMESTAMP(TO_DATE(16-OCT-11 04:49:48'));
ERROR:
ORA-01756: quoted string not properly terminated


SQL> FLASHBACK DATABASE TO TIMESTAMP(TO_DATE('16-OCT-11 04:49:48'));
FLASHBACK DATABASE TO TIMESTAMP(TO_DATE('16-OCT-11 04:49:48'))
*
ERROR at line 1:
ORA-38796: Not enough flashback database log data to undo FLASHBACK.


SQL>
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.