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.
ERROR at line 1:
ORA-38796: Not enough flashback database log data to undo FLASHBACK.
what is the actual code to solve the problem.
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','STEVENSO N',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-1 1 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>
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'
1 row created.
SQL> INSERT INTO PATIENT_HISTORY VALUES(2,'SMITH','STEVENSO
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
1 row created.
SQL> INSERT INTO PATIENT_HISTORY VALUES(5,'APRIL','NORWOOD'
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',
1 row created.
SQL> INSERT INTO PATIENT_HISTORY VALUES(8,'NATALIE','COLINS
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
Session altered.
SQL> FLASHBACK DATABASE TO TIMESTAMP(TO_DATE(16-OCT-1
ERROR:
ORA-01756: quoted string not properly terminated
SQL> FLASHBACK DATABASE TO TIMESTAMP(TO_DATE('16-OCT-
FLASHBACK DATABASE TO TIMESTAMP(TO_DATE('16-OCT-
*
ERROR at line 1:
ORA-38796: Not enough flashback database log data to undo FLASHBACK.
SQL>
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER