The topic probably does not say much of what I need so I'll try to explain:
  • I've got a Source Data in complex relational form
  • For reporting purposes, a simpler, less normalized data model is needed
  • There are two Target views from the Source Data:
    • one of them with full access to all data
    • the second one with access only to a subset of the data (same columns, but not all the records)
  • For both target groups, a separate schema shall be available, each containing only relevant data
  • Today, these schemas are physically located on the same DB instance and host as the source data
  • A daily refresh is sufficient
  • A later relocation of the reporting schemes to other DB instances shall be possible without major changes needed
  • Oracle 10g should be used

I tried to accomplish this using Materialized Views (Materialized seems better since there will be sometime a need to have all the apropriate data somewhere else, geographically, AND it provides Complete Refresh from the Source), but there is a problem:
when creating the MV there is a possibility to type 'SELECT *' - but after execution it changes into real columns names. It is important because later after adding a new column into Source Data it WILL NOT appear in MV after refresh.

I also thought about Data Guard, Streams and RAC, but I think only in the Materialized Views you may choose the data to show (rows, columns).

If you had any solutions or even ideas - I'd definately appreciate!