replicate in oracle

From the top off my head: (supposing the schemas have the correct grants)

create table A.table_replica as select * from B.view_orig;

Select allOpen in new window

it's not a copy, but you'll get the same data in the other schema:

create synonym this_schema.replica for other_schema.view;

Select allOpen in new window

and you keep only 1 set of data

If you want the table kept up to date as the view data changes, I would look at Materialized Views:
http://docs.oracle.com/database/121/SQLRF/statements_6002.htm#SQLRF01302

In a nutshell it physically stores the results of a view and behaves just like a table.

no no. my problem is i have a very complex view ( is not perform). this view is created from many views and function and triger.
for running report on this view i want reclicate data to another schema (and replicate every 10 min. or something like that).
synonym of a very slow view is the same, and snapshut(matview) is the same too.
Alexander: how to replicate (select from and inert into is for first time), but how to keep sync?

Alexander: how to replicate (select from and inert into is for first time), but how to keep sync?

If you choose this way (CTAS), you'd have to do the sync manually. So, I'd rather suggest you stick to Materialized Views as mentioned above...

@Geert: Awesome as usual :-)) I'll c&p that comment into my "Tool stuff" ! Nice stuff, very handy!! Thanks a lot ;-)

>>for running report on this view i want reclicate data to another schema (and replicate every 10 min. or something like that).

By definition that is a Materialized View.

I do agree that tuning the SQL in the view might help but Oracle created a way to physically materialize views for a reason:  Some times, you just have to.

well, we are stepping back from Materialized views
caused some deadlocks last weekend

are they auto refresh ?
yes, ... ah no, they were, as this caused too much redo ... we converted that to refresh every 10 min.
3Tb redo in 2 hours was a little much for a 200Gb database with only 1Gb max of the data changing

the 10 min solution, wasn't a good solution either ... caused the deadlocks

now we are back to tuning the nested materialized views

every bad solution is like a boomerang
it comes back sooner or later

with autorefresh on commit materialized views you need to be very carefull on how many updates you'll get

very nice tools Geert. is it working for plsql too?

no, this is for tuning 1 normal sql statement
the plsql function  calls within the statement are summarized a level higher

you need to read between the lines to see how long functions take

you can see the number calls to pl/sql functions in the recursive calls item in a trace in sqlplus
reducing the number of calls to pl/sql functions usually gives better performance

especially if the function gets called for every row on a certain resultset
limit the resultset first and call functions as late as possible

very nice tools and perfect answer.