Link to home
Start Free TrialLog in
Avatar of lojayn
lojayn

asked on

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.
Avatar of lojayn
lojayn

ASKER

oracle database
Avatar of lojayn

ASKER

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>
ASKER CERTIFIED SOLUTION
Avatar of Jacobfw
Jacobfw
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial