Jasmin shahrzad
asked on
replicate in oracle
Hello,
Which possibility i have if i want to replicate data between to schema.
I have a complex view and i want to create a table in another schema then replicate my table from the view.
Which possibility i have if i want to replicate data between to schema.
I have a complex view and i want to create a table in another schema then replicate my table from the view.
Last Comment
it's not a copy, but you'll get the same data in the other schema:
and you keep only 1 set of data
create synonym this_schema.replica for other_schema.view;
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.
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.
ASKER
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?
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?
![Avatar of Alex [***Alex140181***]](https://filedb.experts-exchange.com/files/public/2022/11/13/8257e912-ee3c-4a93-9df0-fe9a8a1515cf.jpeg){kind=small}
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...
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
@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.
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
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
ASKER
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
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
ASKER
very nice tools and perfect answer.
Oracle Database
Oracle is an object-relational database management system. It supports a large number of languages and application development frameworks. Its primary languages are SQL, PL/SQL and Java, but it also includes support for C and C++. Oracle also has its own enterprise modules and application server software.
81K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
Open in new window