• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 831
  • Last Modified:

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.
0
lojayn
Asked:
lojayn
  • 2
1 Solution
 
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
 
JacobfwCommented:
24hour time?
Should have been FLASHBACK DATABASE TO TIMESTAMP(TO_DATE(16-OCT-11 16:49:48'));
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now