Home » SQL & PL/SQL » SQL & PL/SQL » EMP Table  () 1 Vote
EMP Table [message #255457] Wed, 01 August 2007 00:01 Go to next message
nivas007
Messages: 7
Registered: July 2007
Location: Hydera bad
Junior Member

Hi to all,

My original emp table is modified and has been committed. Can anyone help me to get the actual emp table. Help me!!


Regards,
Nivas...
Re: EMP Table [message #255462 is a reply to message #255457] Wed, 01 August 2007 00:16 Go to previous messageGo to next message
laksha
Messages: 42
Registered: June 2006
Member
You can use flashback query such as this if you know the modification date

 select * from emp as of timestamp TO_TIMESTAMP('2007-07-21 10:08:00', 'YYYY-MM-DD HH:MI:SS') 
Re: EMP Table [message #255469 is a reply to message #255457] Wed, 01 August 2007 00:35 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Creation script is $ORACLE_HOME/rdbms/admin/scott.sql or $ORACLE_HOME/sqlplus/demo/demobld.sql

Regards
Michel
Re: EMP Table [message #255473 is a reply to message #255462] Wed, 01 August 2007 00:41 Go to previous messageGo to next message
nivas007
Messages: 7
Registered: July 2007
Location: Hydera bad
Junior Member

hi lakshya,

Thanks for your help. I have executed the query suggested by you, it is triggering the following error.

ERROR at line 1:
ORA-08180: no snapshot found based on specified time

Regards,
Nivas.
Re: EMP Table [message #255478 is a reply to message #255473] Wed, 01 August 2007 00:45 Go to previous messageGo to next message
shanthkumaar
Messages: 156
Registered: February 2007
Location: india,chennai
Senior Member

hi,

ORA-08180:	no snapshot found based on specified time
Cause:	Could not match the time to an SCN from the mapping table.
Action:	try using a larger time.


regards
shanth.
Re: EMP Table [message #255616 is a reply to message #255457] Wed, 01 August 2007 05:30 Go to previous message
iamdurai
Messages: 96
Registered: April 2007
Location: Chennai
Member

SQL> select * from emp1;

     EMPNO ENAME      JOB       HIREDATE       DEPTNO        SAL
---------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK     17-12-1980         20       1234
      7499 ALLEN      SALESMAN  20-02-1981         30       4563
      7521 WARD       SALESMAN  22-02-1981         30       9865
      7566 JONES      MANAGER   02-04-1981         20       2975
      7654 MARTIN     SALESMAN  28-09-1981         30       2356
      7698 BLAKE      MANAGER   01-05-1981         30       2415
      7782 CLARK      MANAGER   09-06-1981         10       5624
      7788 SCOTT      ANALYST   13-07-1987         20       3000
      7839 KING       PRESIDENT 17-11-1981         10       5000
      7844 TURNER     SALESMAN  08-09-1981         30       4521
      7876 ADAMS      CLERK     13-07-1987         20       5687
      7900 JAMES      CLERK     03-12-1981         30       2415
      7902 FORD       ANALYST   03-12-1981         20       3000
      7934 MILLER     CLERK     23-01-1982         10       3547

14 rows selected.

SQL> update emp1 set sal=sal+5;

14 rows updated.

SQL> commit;

Commit complete.

SQL> select * from emp1;

     EMPNO ENAME      JOB       HIREDATE       DEPTNO        SAL
---------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK     17-12-1980         20       1239
      7499 ALLEN      SALESMAN  20-02-1981         30       4568
      7521 WARD       SALESMAN  22-02-1981         30       9870
      7566 JONES      MANAGER   02-04-1981         20       2980
      7654 MARTIN     SALESMAN  28-09-1981         30       2361
      7698 BLAKE      MANAGER   01-05-1981         30       2420
      7782 CLARK      MANAGER   09-06-1981         10       5629
      7788 SCOTT      ANALYST   13-07-1987         20       3005
      7839 KING       PRESIDENT 17-11-1981         10       5005
      7844 TURNER     SALESMAN  08-09-1981         30       4526
      7876 ADAMS      CLERK     13-07-1987         20       5692
      7900 JAMES      CLERK     03-12-1981         30       2420
      7902 FORD       ANALYST   03-12-1981         20       3005
      7934 MILLER     CLERK     23-01-1982         10       3552

14 rows selected.

SQL> select * from emp1 as of timestamp TO_TIMESTAMP('2007-08-01 03:00:00','YYYY-MM-DD HH:MI:SS');

     EMPNO ENAME      JOB       HIREDATE       DEPTNO        SAL
---------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK     17-12-1980         20       1234
      7499 ALLEN      SALESMAN  20-02-1981         30       4563
      7521 WARD       SALESMAN  22-02-1981         30       9865
      7566 JONES      MANAGER   02-04-1981         20       2975
      7654 MARTIN     SALESMAN  28-09-1981         30       2356
      7698 BLAKE      MANAGER   01-05-1981         30       2415
      7782 CLARK      MANAGER   09-06-1981         10       5624
      7788 SCOTT      ANALYST   13-07-1987         20       3000
      7839 KING       PRESIDENT 17-11-1981         10       5000
      7844 TURNER     SALESMAN  08-09-1981         30       4521
      7876 ADAMS      CLERK     13-07-1987         20       5687
      7900 JAMES      CLERK     03-12-1981         30       2415
      7902 FORD       ANALYST   03-12-1981         20       3000
      7934 MILLER     CLERK     23-01-1982         10       3547

14 rows selected.





The error ORA-8180 is reported when the time which is specified, is mapped to a SCN which is beyond the bounds of a valid scn.

I think this query working fine.

Reds
Thangam.

[Updated on: Wed, 01 August 2007 05:35]

Report message to a moderator

Previous Topic: Adding a new line between selected lines in sql query..
Next Topic: v$sort_usage
Goto Forum:
  


Current Time: Mon Dec 05 19:01:22 CST 2016

Total time taken to generate the page: 0.22486 seconds