oracle 9i flashback query
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: oracle 9i flashback query

  1. #1
    Join Date
    Sep 2001
    Posts
    120
    Hi dba's

    has any one has tried using oracle 9i flash back query .
    i have done setting undo_retention to 600sec but getting this error ora-01466.

    please help

    thanks

    saurabh
    Saurabh Garg
    OCP 9i

  2. #2
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    Time is only granular to 5 minutes, did you take this into acount?


  3. #3
    Join Date
    Sep 2001
    Posts
    120
    i will tell u what i have done

    i set the parameter in init.ora with default values as 900 and started the instance.
    crated a user and a table and inserted some recoreds.

    deleted the records and initialised the package for the flashback with enable_at_time opton.

    procedure compiled and then i run the pl/sql.
    it said the flashback is already running.
    i stopped it and only ran the pl/sql.

    it gave the erroe
    ora-01466 table defination has changed.

    please help

    saurabh
    Saurabh Garg
    OCP 9i

  4. #4
    Join Date
    Dec 2001
    Location
    UK
    Posts
    1,684
    Tim...
    OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
    OCA PL/SQL Developer
    Oracle ACE Director
    My website: www.oracle-base.com
    My blog: www.oracle-base.com/blog

  5. #5
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938

    i set the parameter in init.ora with default values as 900 and started the instance.
    Which parameter? UNDO_RETENTION?

    You don't have to bounce the instance, only:

    ALTER SYSTEM SET UNDO_RETENTION = 1200;

    1200 = 20 minutes.

    crated a user and a table and inserted some recoreds.

    deleted the records and initialised the package for the flashback with enable_at_time opton.
    ENABLE_AT_TIME is not an option, it is a procedure.
    When you do that the snapshot time is set to a SCN which most closely matches your timestamp.

    procedure compiled and then i run the pl/sql.
    What kind of PL/SQL?

    it said the flashback is already running.
    i stopped it and only ran the pl/sql.
    I am a bit lost here. It seems that you have not run the DISABLE procedure before running ENABLE_AT_TIME again.


  6. #6
    Join Date
    Sep 2001
    Posts
    120

    Thumbs down

    TimHall i also got the prod from there only but for me it is not running.

    please tell me what u have done.

    julian i did disable the flashback query.
    and then ran the proc again.

    i am getting the following errors

    SQL> ALTER SYSTEM SET UNDO_RETENTION = 1200;

    System altered.

    SQL> commit;

    Commit complete.

    SQL> delete years1;

    7 rows deleted.

    SQL> commit;

    Commit complete.

    SQL> DECLARE
    2 CURSOR c_emp IS
    3 SELECT *
    4 FROM years1;
    5 v_row c_emp%ROWTYPE;
    BEGIN
    Dbms_Flashback.Enable_At_System_Change_Number(30528);
    6 7 8 OPEN c_emp;
    9 Dbms_Flashback.Disable;
    10
    11 LOOP
    12 FETCH c_emp INTO v_row;
    13 EXIT WHEN c_emp%NOTFOUND;
    14 INSERT INTO years1 VALUES
    15 (v_row.YEAR_CODE, v_row.YEAR_NAME);
    16 END LOOP;
    17 CLOSE c_emp;
    18 COMMIT;
    19 END;
    20 /
    DECLARE
    *
    ERROR at line 1:
    ORA-01466: unable to read data - table definition has changed
    ORA-06512: at line 3
    ORA-06512: at line 8

    thanks


    [Edited by saurabhg29 on 01-03-2002 at 02:02 AM]
    Saurabh Garg
    OCP 9i

  7. #7
    Join Date
    Dec 2001
    Location
    UK
    Posts
    1,684
    Looks like you've recovered to a point before the table has been created.

    Remember, Flashback only workd for DML, not DDL.

    Try again but use a time/SCN after the table was created but before the data was deleted.

    Cheers
    Tim...
    OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
    OCA PL/SQL Developer
    Oracle ACE Director
    My website: www.oracle-base.com
    My blog: www.oracle-base.com/blog

  8. #8
    Join Date
    Sep 2001
    Posts
    120
    thanks tim,

    i am now getting no error but when i run select statement it is showing no results.

    what have done :

    insert into years1 select * from years;
    7 rows created.
    SQL> commit;

    Commit complete.

    SQL> delete years1;

    7 rows deleted.

    SQL>
    SQL> DECLARE
    2 CURSOR c_emp IS
    3 SELECT *
    4 FROM years1;
    5 v_row c_emp%ROWTYPE;
    6 BEGIN
    Dbms_Flashback.Enable_At_Time('05-JAN-02 07:15:12');
    7 8 OPEN c_emp;
    9 Dbms_Flashback.Disable;
    10 LOOP
    11 FETCH c_emp INTO v_row;
    12 EXIT WHEN c_emp%NOTFOUND;
    13 INSERT INTO years1 VALUES
    14 (v_row.YEAR_CODE, v_row.YEAR_NAME);
    15 END LOOP;
    16 CLOSE c_emp;
    17 COMMIT;
    18 END;
    19 /

    PL/SQL procedure successfully completed.

    SQL>
    SQL>
    SQL> commit;

    Commit complete.

    SQL> execute Dbms_Flashback.Disable;

    PL/SQL procedure successfully completed.

    SQL>
    SQL> select * from years1;

    no rows selected

    SQL>


    please help where i have gone wrong
    Saurabh Garg
    OCP 9i

  9. #9
    Join Date
    Dec 2001
    Location
    UK
    Posts
    1,684
    Hi.

    Your code works fine but I don't think you are using the correct date-time. You are not checking the exact time before the delete. My guess is the time you are using is fractionally too late. Remember, Oracle picks it's best estimate for the SCN based on the time you specifiy. Try using:

    SQL> insert into years1 values ('one', '0001');

    1 row created.

    SQL> insert into years1 values ('two', '0002');

    1 row created.

    SQL> commit;

    Commit complete.

    SQL> select to_char(sysdate, 'DD-MON-YY HH24:MI:SS') from dual;

    TO_CHAR(SYSDATE,'D
    ------------------
    05-JAN-02 10:50:36

    SQL> delete years1;

    2 rows deleted.

    SQL> commit;

    Commit complete.

    SQL> select * from years1;

    no rows selected

    SQL> DECLARE
    2 CURSOR c_emp IS
    3 SELECT *
    4 FROM years1;
    5 v_row c_emp%ROWTYPE;
    6 BEGIN
    7 Dbms_Flashback.Enable_At_Time('05-JAN-02 10:50:36');
    8 OPEN c_emp;
    9 Dbms_Flashback.Disable;
    10 LOOP
    11 FETCH c_emp INTO v_row;
    12 EXIT WHEN c_emp%NOTFOUND;
    13 INSERT INTO years1 VALUES
    14 (v_row.YEAR_CODE, v_row.YEAR_NAME);
    15 END LOOP;
    16 CLOSE c_emp;
    17 COMMIT;
    18 END;
    19 /

    PL/SQL procedure successfully completed.

    SQL>
    SQL> select * from years1;

    YEA YEAR_NAME
    --- --------------------------------------------------
    one 0001
    two 0002

    SQL>

    You see here that I check the time before the delete so I know I'm using an accurate time.

    If you want you can use the SCN directly by using the Get_System_Change_Number function before the deletion. This is even more exact as you are not letting Oracle quess the SCN. Obviously, in a real world recovery you may not know the exact SCN.

    Cheers
    Tim...
    OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
    OCA PL/SQL Developer
    Oracle ACE Director
    My website: www.oracle-base.com
    My blog: www.oracle-base.com/blog

  10. #10
    Join Date
    Sep 2001
    Posts
    120
    hi ,
    i am still not having any success with flashback query
    I did what you gave me but it give me the following
    ---------------------------------------------------------------
    i did this command by connecting as sys
    ALTER SYSTEM SET UNDO_RETENTION = 1200
    ---------------------------------------------------------------
    i did the other command as a normal user.

    SQL> create table years1 (YEAR_CODE varchar2(5),YEAR_NAME varchar2(5));

    Table created.

    SQL>
    SQL>
    SQL> commit;

    Commit complete.

    SQL> insert into years1 values ('one', '0001');
    insert into years1 values ('two', '0002');

    1 row created.

    SQL>
    1 row created.

    SQL> commit;

    Commit complete.

    SQL> select to_char(sysdate, 'DD-MON-YY HH:MI:SS') from dual;

    TO_CHAR(SYSDATE,'D
    ------------------
    05-JAN-02 01:48:25

    SQL> delete years1;

    2 rows deleted.

    SQL> commit;

    Commit complete.

    SQL> select * from years1;

    no rows selected

    SQL> DECLARE
    2 CURSOR c_emp IS
    3 SELECT *
    4 FROM years1;
    5 v_row c_emp%ROWTYPE;
    6 BEGIN
    7 Dbms_Flashback.Enable_At_Time('05-JAN-02 01:48:25');
    8 OPEN c_emp;
    Dbms_Flashback.Disable;
    9 10 LOOP
    11 FETCH c_emp INTO v_row;
    12 EXIT WHEN c_emp%NOTFOUND;
    13 INSERT INTO years1 VALUES
    14 (v_row.YEAR_CODE, v_row.YEAR_NAME);
    15 END LOOP;
    16 CLOSE c_emp;
    17 COMMIT;
    18 END;
    19 /
    DECLARE
    *
    ERROR at line 1:
    ORA-01466: unable to read data - table definition has changed
    ORA-06512: at line 3
    ORA-06512: at line 8


    SQL> select * from years1;
    select * from years1
    *
    ERROR at line 1:
    ORA-01466: unable to read data - table definition has changed


    SQL> execute Dbms_Flashback.Disable;

    PL/SQL procedure successfully completed.

    SQL> select * from years1;

    no rows selected


    -------------------------

    you have written you can get the system change number but how can i do it because i have read it is impossible to get the system change no(in the posts here) but has to be calculated


    thanks
    Saurabh Garg
    OCP 9i

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width