Using MVIEWS to take in time data snapshot of tablesUse sqlldr control file to automatically create several tables in OracleOracle query is slower the second timeCan I use RMAN to backup and restore data in a certain point in time?Repopulating Oracle tables using data from Excel/CSV fileExtract certain rows from table and importhow to prevent an oracle block corruption from duplicating a database with a few nologging tablesSnapshot too old from materialized view refresh jobAllow refresh of lower environment from production when using a HSMOracle SQL Developer - Database Diff tables & constraints without table dataWhat is the standard time delay taken for the data to get reflected in standby database when a new data is inserted in primary database
Should this code fail to compile in C++17?
Is the world in Game of Thrones spherical or flat?
How can an eldritch abomination hide its true form in public?
Why is Colorado so different politically from nearby states?
Is having a hidden directory under /etc safe?
Is the capacitor drawn or wired wrongly?
Working in the USA for living expenses only; allowed on VWP?
Can you keep a readied action even through incapacitation?
What does the behaviour of water on the skin of an aircraft in flight tell us?
Asking bank to reduce APR instead of increasing credit limit
C++ variable that is true and false at the same time
How can a single Member of the House block a Congressional bill?
Does Peach's float negate shorthop knockback multipliers?
what's the equivalent of helper in LWC?
What caused the tendency for conservatives to not support climate change regulations?
How to decline physical affection from a child whose parents are pressuring them?
Neither Advanced Search nor Search Builder offer option to save as Smart Group
How do I get a list of only the files (not the directories) from a package?
Can a magnetic field of a large body be stronger than it's gravity?
What do you call the small burst of laugh that people let out when they want to refrain from laughing, but can't?
Looking for an old image of designing a cpu with plan laid out / being edited on a literal floor
Solving symbolic equation for fraction of variables
Estimate related to the Möbius function
Singlequote and backslash
Using MVIEWS to take in time data snapshot of tables
Use sqlldr control file to automatically create several tables in OracleOracle query is slower the second timeCan I use RMAN to backup and restore data in a certain point in time?Repopulating Oracle tables using data from Excel/CSV fileExtract certain rows from table and importhow to prevent an oracle block corruption from duplicating a database with a few nologging tablesSnapshot too old from materialized view refresh jobAllow refresh of lower environment from production when using a HSMOracle SQL Developer - Database Diff tables & constraints without table dataWhat is the standard time delay taken for the data to get reflected in standby database when a new data is inserted in primary database
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty margin-bottom:0;
I have a requirement where I have to Load data periodically from more than 100 tables of an OLTP oracle database into MS SQL database. In my solution I want to use MVIEWs of these tables with Fast Refresh as the point of data extraction in oracle.
Every time before triggering the data load into MS SQL database I will refresh the MVIEWs to get the data. However have below mentioned doubts:
1) Is there a way through which i can refresh all the MVIEWs at once?
2) How can I ensure that data changes happening during the MVIEW refresh will not be reflected in the refreshed MVIEW. e.g. I started a refresh of all the MVIEWs at 10:00 AM then how can I ensure that no changes happening after 10:00 AM reflects in the refreshed MVIEWs?
3) Is there a better way of achieving this? I don't have the option of using mirror database.
oracle oracle-11g-r2
edited Apr 14 at 14:57
Vérace
16.7k33652
asked Apr 14 at 13:30
VisheshVishesh
184
add a comment |
I have a requirement where I have to Load data periodically from more than 100 tables of an OLTP oracle database into MS SQL database. In my solution I want to use MVIEWs of these tables with Fast Refresh as the point of data extraction in oracle.
Every time before triggering the data load into MS SQL database I will refresh the MVIEWs to get the data. However have below mentioned doubts:
1) Is there a way through which i can refresh all the MVIEWs at once?
2) How can I ensure that data changes happening during the MVIEW refresh will not be reflected in the refreshed MVIEW. e.g. I started a refresh of all the MVIEWs at 10:00 AM then how can I ensure that no changes happening after 10:00 AM reflects in the refreshed MVIEWs?
3) Is there a better way of achieving this? I don't have the option of using mirror database.
oracle oracle-11g-r2
edited Apr 14 at 14:57
Vérace
16.7k33652
asked Apr 14 at 13:30
VisheshVishesh
184
+1 for a good first question! p.s. welcome to the forum! :-)
– Vérace
Apr 14 at 14:58
add a comment |
I have a requirement where I have to Load data periodically from more than 100 tables of an OLTP oracle database into MS SQL database. In my solution I want to use MVIEWs of these tables with Fast Refresh as the point of data extraction in oracle.
Every time before triggering the data load into MS SQL database I will refresh the MVIEWs to get the data. However have below mentioned doubts:
1) Is there a way through which i can refresh all the MVIEWs at once?
2) How can I ensure that data changes happening during the MVIEW refresh will not be reflected in the refreshed MVIEW. e.g. I started a refresh of all the MVIEWs at 10:00 AM then how can I ensure that no changes happening after 10:00 AM reflects in the refreshed MVIEWs?
3) Is there a better way of achieving this? I don't have the option of using mirror database.
oracle oracle-11g-r2
edited Apr 14 at 14:57
Vérace
16.7k33652
asked Apr 14 at 13:30
VisheshVishesh
184
I have a requirement where I have to Load data periodically from more than 100 tables of an OLTP oracle database into MS SQL database. In my solution I want to use MVIEWs of these tables with Fast Refresh as the point of data extraction in oracle.
Every time before triggering the data load into MS SQL database I will refresh the MVIEWs to get the data. However have below mentioned doubts:
1) Is there a way through which i can refresh all the MVIEWs at once?
2) How can I ensure that data changes happening during the MVIEW refresh will not be reflected in the refreshed MVIEW. e.g. I started a refresh of all the MVIEWs at 10:00 AM then how can I ensure that no changes happening after 10:00 AM reflects in the refreshed MVIEWs?
3) Is there a better way of achieving this? I don't have the option of using mirror database.
oracle oracle-11g-r2
oracle oracle-11g-r2
edited Apr 14 at 14:57
Vérace
16.7k33652
asked Apr 14 at 13:30
VisheshVishesh
184
edited Apr 14 at 14:57
Vérace
16.7k33652
asked Apr 14 at 13:30
VisheshVishesh
184
edited Apr 14 at 14:57
Vérace
16.7k33652
edited Apr 14 at 14:57
Vérace
16.7k33652
edited Apr 14 at 14:57
Vérace
16.7k33652
16.7k33652
asked Apr 14 at 13:30
VisheshVishesh
184
asked Apr 14 at 13:30
VisheshVishesh
184
asked Apr 14 at 13:30
VisheshVishesh
184
184
+1 for a good first question! p.s. welcome to the forum! :-)
– Vérace
Apr 14 at 14:58
add a comment |
+1 for a good first question! p.s. welcome to the forum! :-)
– Vérace
Apr 14 at 14:58
+1 for a good first question! p.s. welcome to the forum! :-)
– Vérace
Apr 14 at 14:58
+1 for a good first question! p.s. welcome to the forum! :-)
– Vérace
Apr 14 at 14:58
add a comment |
1 Answer
1
active
oldest
votes
DBMS_MVIEW.REFRESH
list - Comma-delimited list of materialized views that you want to
refresh. ... Alternatively, you may pass in a PL/SQL index-by table of type DBMS_UTILITY.UNCL_ARRAY, where each element is the name of a materialized view.
atomic_refresh - If this parameter is set to true, then the list of materialized views
is refreshed in a single transaction. All of the refreshed
materialized views are updated to a single point in time. If the
refresh fails for any of the materialized views, none of the
materialized views are updated.Maybe flashback query. Using Oracle Flashback Query DBMS_FLASHBACK.ENABLE_AT_TIME.
answered Apr 14 at 14:25
Balazs PappBalazs Papp
27.4k2931
Does MVIEW referesh locks the source tables till they are completely refereshed?. If that is so then using it in OLTP system will cause issues .
– Vishesh
Apr 17 at 13:53
@Vishesh Tables need not to be locked to have a consistent state of them, even if that is from the past and not the present. That is what you have undo for.
– Balazs Papp
Apr 17 at 18:43
add a comment |
Your Answer
StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "182"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);
else
createEditor();
);
function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);
);
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f234758%2fusing-mviews-to-take-in-time-data-snapshot-of-tables%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
DBMS_MVIEW.REFRESH
list - Comma-delimited list of materialized views that you want to
refresh. ... Alternatively, you may pass in a PL/SQL index-by table of type DBMS_UTILITY.UNCL_ARRAY, where each element is the name of a materialized view.
atomic_refresh - If this parameter is set to true, then the list of materialized views
is refreshed in a single transaction. All of the refreshed
materialized views are updated to a single point in time. If the
refresh fails for any of the materialized views, none of the
materialized views are updated.Maybe flashback query. Using Oracle Flashback Query DBMS_FLASHBACK.ENABLE_AT_TIME.
answered Apr 14 at 14:25
Balazs PappBalazs Papp
27.4k2931
Does MVIEW referesh locks the source tables till they are completely refereshed?. If that is so then using it in OLTP system will cause issues .
– Vishesh
Apr 17 at 13:53
@Vishesh Tables need not to be locked to have a consistent state of them, even if that is from the past and not the present. That is what you have undo for.
– Balazs Papp
Apr 17 at 18:43
add a comment |
DBMS_MVIEW.REFRESH
list - Comma-delimited list of materialized views that you want to
refresh. ... Alternatively, you may pass in a PL/SQL index-by table of type DBMS_UTILITY.UNCL_ARRAY, where each element is the name of a materialized view.
atomic_refresh - If this parameter is set to true, then the list of materialized views
is refreshed in a single transaction. All of the refreshed
materialized views are updated to a single point in time. If the
refresh fails for any of the materialized views, none of the
materialized views are updated.Maybe flashback query. Using Oracle Flashback Query DBMS_FLASHBACK.ENABLE_AT_TIME.
answered Apr 14 at 14:25
Balazs PappBalazs Papp
27.4k2931
Does MVIEW referesh locks the source tables till they are completely refereshed?. If that is so then using it in OLTP system will cause issues .
– Vishesh
Apr 17 at 13:53
@Vishesh Tables need not to be locked to have a consistent state of them, even if that is from the past and not the present. That is what you have undo for.
– Balazs Papp
Apr 17 at 18:43
add a comment |
DBMS_MVIEW.REFRESH
list - Comma-delimited list of materialized views that you want to
refresh. ... Alternatively, you may pass in a PL/SQL index-by table of type DBMS_UTILITY.UNCL_ARRAY, where each element is the name of a materialized view.
atomic_refresh - If this parameter is set to true, then the list of materialized views
is refreshed in a single transaction. All of the refreshed
materialized views are updated to a single point in time. If the
refresh fails for any of the materialized views, none of the
materialized views are updated.Maybe flashback query. Using Oracle Flashback Query DBMS_FLASHBACK.ENABLE_AT_TIME.
answered Apr 14 at 14:25
Balazs PappBalazs Papp
27.4k2931
DBMS_MVIEW.REFRESH
list - Comma-delimited list of materialized views that you want to
refresh. ... Alternatively, you may pass in a PL/SQL index-by table of type DBMS_UTILITY.UNCL_ARRAY, where each element is the name of a materialized view.
atomic_refresh - If this parameter is set to true, then the list of materialized views
is refreshed in a single transaction. All of the refreshed
materialized views are updated to a single point in time. If the
refresh fails for any of the materialized views, none of the
materialized views are updated.Maybe flashback query. Using Oracle Flashback Query DBMS_FLASHBACK.ENABLE_AT_TIME.
answered Apr 14 at 14:25
Balazs PappBalazs Papp
27.4k2931
answered Apr 14 at 14:25
Balazs PappBalazs Papp
27.4k2931
answered Apr 14 at 14:25
Balazs PappBalazs Papp
27.4k2931
answered Apr 14 at 14:25
Balazs PappBalazs Papp
27.4k2931
27.4k2931
Does MVIEW referesh locks the source tables till they are completely refereshed?. If that is so then using it in OLTP system will cause issues .
– Vishesh
Apr 17 at 13:53
@Vishesh Tables need not to be locked to have a consistent state of them, even if that is from the past and not the present. That is what you have undo for.
– Balazs Papp
Apr 17 at 18:43
add a comment |
Does MVIEW referesh locks the source tables till they are completely refereshed?. If that is so then using it in OLTP system will cause issues .
– Vishesh
Apr 17 at 13:53
@Vishesh Tables need not to be locked to have a consistent state of them, even if that is from the past and not the present. That is what you have undo for.
– Balazs Papp
Apr 17 at 18:43
Does MVIEW referesh locks the source tables till they are completely refereshed?. If that is so then using it in OLTP system will cause issues .
– Vishesh
Apr 17 at 13:53
Does MVIEW referesh locks the source tables till they are completely refereshed?. If that is so then using it in OLTP system will cause issues .
– Vishesh
Apr 17 at 13:53
@Vishesh Tables need not to be locked to have a consistent state of them, even if that is from the past and not the present. That is what you have undo for.
– Balazs Papp
Apr 17 at 18:43
@Vishesh Tables need not to be locked to have a consistent state of them, even if that is from the past and not the present. That is what you have undo for.
– Balazs Papp
Apr 17 at 18:43
add a comment |
Thanks for contributing an answer to Database Administrators Stack Exchange!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f234758%2fusing-mviews-to-take-in-time-data-snapshot-of-tables%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
+1 for a good first question! p.s. welcome to the forum! :-)
– Vérace
Apr 14 at 14:58