CODEWITHSUNDEEP
sqloracle-databaseplsqltriggersalter
Andy
Andy

Reputation: 11

Keeping Archive table structure (columns, etc.) in sync with Live table

I have a table (TMain) with large amount of data, which is archived to an archive table (TArchive) - where records are more than 3 months old.

Since TMain is a volatile table and columns on it are altered/dropped over period of time, I want to have some sort of control so that TArchive is always in sync, when TMain is changed.

What's the best way to achieve this in Oracle?

Any suggestion and solution are appreciated.

Upvotes: 1

Views: 472

Answers (2)

haki
haki

Reputation: 9779

best solution for keeping historical data the same way current data is stored is partitioning.
in 11G you can also set the interval and oracle will automatically create new partitions. one thing though, if you'l get tempted to use compression take under consideration that DDL changes are not possible when there are compressed partitions.

Upvotes: 1

Vincent Malgrat
Vincent Malgrat

Reputation: 67762

DDL in Oracle is not transactional. This means that any automated process to replicate DDL in Oracle across two objects will be somewhat flawed. What if the second DDL statement fails? You can't rollback the first one, you're stuck.

Furthermore, you should already have an external organizational process to validate DDL. For instance in many organizations developers can only make changes on development databases. These changed are propagated to a staging database for testing purpose. Only DBAs can push these changes, once validated, to production. Even if the developer and the DBA are the same person, you should follow this guideline: verify each and every DDL.

For these reasons, I would advise against automatizing DDL replication. This looks like too much trouble. Instead:

  • If you don't make changes to the table structure often, just add a rule in the DBA checklist.
  • If you alter the table too often, maybe you could look into alternate architectures that wouldn't require DDL. For instance storing volatile information into a less structured format such as XML, you could also add generic columns that may hold different kind of information depending on the row. Even a localized key-value table can alleviate the need to alter the table structure.

Upvotes: 1

Related Questions