Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, kevin.

Asked: June 30, 2016 - 2:21 am UTC

Last updated: July 15, 2016 - 1:27 am UTC

Version: 11g

Viewed 1000+ times

You Asked

Hi Tom
I have table A which goes through various DML and owned by different team, we want to capture a similar data with all the DML that's happening.
we want to avoid triggers as this is owned by different team , is there a solution to capture without triggers ?


and Connor said...

Your options include

Goldengate (requires a license)

http://www.oracle.com/technetwork/middleware/goldengate/overview/index.html

Streams/CDC

https://docs.oracle.com/cd/B28359_01/server.111/b28321/strms_over.htm

Flashback data archive

https://docs.oracle.com/database/121/ADFNS/adfns_flashback.htm#ADFNS01011

or third party options

Hope this helps.

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Flashback

kevin smith, July 07, 2016 - 12:38 am UTC

Thank You so much for the response, went through all these references and just have few open questions on flash back as this looks something in terms of what I want

1. Suppose one of the column is modified on the FB table say like rename of a column or the numeric precision. how can the flashback handle this scenario and what happens to the existing data. I ask because document says flashback cannot handle modify column.

2. If company policy says to archive 5 years of historical data, if flashback has 5 years retention on table. can we move this historical data into some other new table with just a regular insert into this new archive table.in simple terms I just don't want to delete any data in flashback but still keep 5yrs retention and before oracle purge this data want to move it to some new table permanently.

3. Can we do a SELECT from flashback tables and insert,delete,update into some other non flashback tables ? any restrictions on using joins with regular tables,sub queries or any limitations at all from querying the flashback? I ask as my requirements is to select some data at some point in time on flashback , filter , aggregate and move to new table.

4. any performance related issues that we should be aware of using flashback.

5. Finally our requirement sometimes is to flow the production data into test or development servers. can we achieve this for flashback archive tables? I seen an option to take a complete backup of this FB table, can we just load this to test/dev environments FB tables and achieve this goal or any other better approach that you could recommend


Connor McDonald
July 07, 2016 - 3:29 pm UTC

1. That applies to standard flashback query. Flashback Data Archive persists the history to disk. So you can query across DDL that would break the undo reconstruction.

2. FDA creates sys_hist_* tables in your schema. These store the history and metadata. You can export these using the dbms_flashback_archive package:

https://oracle-base.com/articles/12c/flashback-data-archive-fda-enhancements-12cr1#export-import-table-history

3. Yes. You can use your flashback archive enabled table in exactly the same way as normal tables.

4. You can import existing history into your archive. But be careful before doing so. As the docs say:

"Importing user-generated history can lead to inaccurate, or unreliable results. This procedure should only be used after consulting with Oracle Support."

So you could run into problems if you do this. I'm not aware of other issues.

5. I'm not sure what you're asking. Do you want all the history information imported to test/dev too?

Chris

Avoid Triggers

Kevin, July 14, 2016 - 1:48 pm UTC

Thank You so much Chris , it was very helpful.

For the final follow up question, yes our application need the capability of inserting the Production flash back data into our test/development servers for our testing


Connor McDonald
July 15, 2016 - 1:27 am UTC

You can use

DBMS_FLASHBACK_ARCHIVE.create_temp_history_table

to unload history into a table, and

DBMS_FLASHBACK_ARCHIVE.import_history

to load into a new schema. Or you could datapump export the table, datapump import into a new database, and then use the "import_history" call.

Cheers,
Connor
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library