Results 1 to 5 of 5

Thread: Flashback

  1. #1
    Join Date
    Dec 2003
    Posts
    1,074

    Unanswered: Flashback

    Most of the examples that draw you to the Flashback feature describe flashing back a single table. Normally, we'd have to return several tables back to the same point in time.

    Is there a way to make a particular tablespace read-only, while performing Flashback on several tables, then allowing users rights to the tables afterwards?

    Otherwise, I don't see how the data would be consistent.

    -cf

  2. #2
    Join Date
    Jul 2003
    Posts
    2,296
    Flashback doesn't seem terribly useful (except in 10g).

    I once tried to use it and then found out it is not possible if you also
    ran DDL (or something to that effect).

    Anyways, it seemed to me while I was researching it, that you:
    1. flashback the table
    2. possibly retrieve the records you want into a different table
    3. then put those records into your current table

    bah! I could be wrong.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  3. #3
    Join Date
    Jun 2004
    Posts
    796
    Provided Answers: 1
    Not that I've tried it, but my notes for Flashback state the following:

    The package allows you to see a consistent version of the database at a time or SCN that you specify.
    Flashback only applies to DML transactions, not DDL transactions.
    It doesn't apply to packages, procedures, or functions.
    The data retrieved is read only.
    If you execute dbms_flashback you specify a point in time for the whole database, so this leads me to believe that ALL tables will be taken back, not just one. (What may be confusing is that the examples in the book then show a query on only one table.)

    If 'flashback any table' is granted to a user, that user can then use an 'as of' clause e.g. select * from ??? as of timestamp [timestamp]; a few of these selects together would also allow you to read more than one table.
    90% of users' problems can be resolved by punching them - the other 10% by switching off their PCs.

  4. #4
    Join Date
    Jul 2003
    Posts
    2,296
    groupie confirms my thoughts.

    from the READ ONLY table, you get the data you need.
    From there you have to repopulate your realtime table with that flashback
    read-only table.

    (or something like that)

    here is a good article and examples of what I am stating:
    Code:
    http://www.databasejournal.com/features/oracle/article.php/10893_2203041_2
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  5. #5
    Join Date
    Dec 2003
    Posts
    1,074
    10g offers an upgrade, I think, as the May/June 2004 Oraclemag demonstrates that you can Flashback a table to a particular point in time using the following general syntax:

    flashback table employee to timestamp to_timestamp('12/31/2003 23:00:00','mm/dd/yyyy hh24:mi:ss');

    I can see this being useful if you could suspend interaction with this table and the handful of others which are related by referential integrity while flashback occurs, without opening the database in EXCLUSIVE mode.

    -Chuck

Posting Permissions

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