Oracle SQL Developer: Copy paste tables, with 2 different instances, with different table structure
Here I have 2 difference instances, one is called DEV and one is called SIT2. I created a public database link, called DBLINKSIT2(Basically just to create a bridge between DEV and SIT2) and I need to copy all(make a backup) the tables from DEV to SIT2, with additional filtration and joining with another table called LKUP.CTL_RWA_VERSION Below is the syntax that I have that is running in DEV.
begin
for r in (select DISTINCT TABLE_NAME from all_tab_columns where owner = 'DDSHIST' and COLUMN_NAME = 'SNAPSHOT_DT')
loop
begin
execute immediate 'INSERT INTO ||r.table_name|| @DBLINKSIT2
select a.*
from DDSHIST.||r.table_name|| a
INNER JOIN LKUP.CTL_RWA_VERSION b ON a.SNAPSHOT_DT = b.SNAPSHOT_DT and a.DDS_VERSION = b.DDS_VERSION
WHERE b.GOLDEN_COPY = 'N'';
exception when others then null;
end;
end loop;
end;
I put COLUMN_NAME = 'SNAPSHOT_DT' because some of the tables do not contain this column. So the joining condition is both SNAPSHOT_DT are the same, and DDS_VERSION are the same, WHERE golden copy in LKUP table = 'Y'. then loop the script, and insert into @DBLINKSIT2.
But I can't get the script to run and I don't know where I am getting this wrong.
Any help would be appreciated. Thank you.
oracle oracle-sql-developer
edited Aug 10 '16 at 12:27
Chun
51
asked Aug 5 '16 at 7:35
ChunChun
61
bumped to the homepage by Community♦ 17 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
add a comment |
Here I have 2 difference instances, one is called DEV and one is called SIT2. I created a public database link, called DBLINKSIT2(Basically just to create a bridge between DEV and SIT2) and I need to copy all(make a backup) the tables from DEV to SIT2, with additional filtration and joining with another table called LKUP.CTL_RWA_VERSION Below is the syntax that I have that is running in DEV.
begin
for r in (select DISTINCT TABLE_NAME from all_tab_columns where owner = 'DDSHIST' and COLUMN_NAME = 'SNAPSHOT_DT')
loop
begin
execute immediate 'INSERT INTO ||r.table_name|| @DBLINKSIT2
select a.*
from DDSHIST.||r.table_name|| a
INNER JOIN LKUP.CTL_RWA_VERSION b ON a.SNAPSHOT_DT = b.SNAPSHOT_DT and a.DDS_VERSION = b.DDS_VERSION
WHERE b.GOLDEN_COPY = 'N'';
exception when others then null;
end;
end loop;
end;
I put COLUMN_NAME = 'SNAPSHOT_DT' because some of the tables do not contain this column. So the joining condition is both SNAPSHOT_DT are the same, and DDS_VERSION are the same, WHERE golden copy in LKUP table = 'Y'. then loop the script, and insert into @DBLINKSIT2.
But I can't get the script to run and I don't know where I am getting this wrong.
Any help would be appreciated. Thank you.
oracle oracle-sql-developer
edited Aug 10 '16 at 12:27
Chun
51
asked Aug 5 '16 at 7:35
ChunChun
61
bumped to the homepage by Community♦ 17 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
1
Is this a one time only solution? I would go with expdp with network link. oracle-base.com/articles/10g/…
– vercelli
Aug 5 '16 at 8:55
Are you talking about Database Replication? Also as vercelli said you can use Datapump.
– JSapkota
Aug 5 '16 at 10:09
1
To regain control of your question follow the instructions here to merge your unregistered and registered accounts. Once that is completed, you will be able to edit your question without peer review, comment anywhere on this page, and accept an answer when the time comes.
– Paul White♦
Aug 10 '16 at 12:28
add a comment |
Here I have 2 difference instances, one is called DEV and one is called SIT2. I created a public database link, called DBLINKSIT2(Basically just to create a bridge between DEV and SIT2) and I need to copy all(make a backup) the tables from DEV to SIT2, with additional filtration and joining with another table called LKUP.CTL_RWA_VERSION Below is the syntax that I have that is running in DEV.
begin
for r in (select DISTINCT TABLE_NAME from all_tab_columns where owner = 'DDSHIST' and COLUMN_NAME = 'SNAPSHOT_DT')
loop
begin
execute immediate 'INSERT INTO ||r.table_name|| @DBLINKSIT2
select a.*
from DDSHIST.||r.table_name|| a
INNER JOIN LKUP.CTL_RWA_VERSION b ON a.SNAPSHOT_DT = b.SNAPSHOT_DT and a.DDS_VERSION = b.DDS_VERSION
WHERE b.GOLDEN_COPY = 'N'';
exception when others then null;
end;
end loop;
end;
I put COLUMN_NAME = 'SNAPSHOT_DT' because some of the tables do not contain this column. So the joining condition is both SNAPSHOT_DT are the same, and DDS_VERSION are the same, WHERE golden copy in LKUP table = 'Y'. then loop the script, and insert into @DBLINKSIT2.
But I can't get the script to run and I don't know where I am getting this wrong.
Any help would be appreciated. Thank you.
oracle oracle-sql-developer
edited Aug 10 '16 at 12:27
Chun
51
asked Aug 5 '16 at 7:35
ChunChun
61
Here I have 2 difference instances, one is called DEV and one is called SIT2. I created a public database link, called DBLINKSIT2(Basically just to create a bridge between DEV and SIT2) and I need to copy all(make a backup) the tables from DEV to SIT2, with additional filtration and joining with another table called LKUP.CTL_RWA_VERSION Below is the syntax that I have that is running in DEV.
begin
for r in (select DISTINCT TABLE_NAME from all_tab_columns where owner = 'DDSHIST' and COLUMN_NAME = 'SNAPSHOT_DT')
loop
begin
execute immediate 'INSERT INTO ||r.table_name|| @DBLINKSIT2
select a.*
from DDSHIST.||r.table_name|| a
INNER JOIN LKUP.CTL_RWA_VERSION b ON a.SNAPSHOT_DT = b.SNAPSHOT_DT and a.DDS_VERSION = b.DDS_VERSION
WHERE b.GOLDEN_COPY = 'N'';
exception when others then null;
end;
end loop;
end;
I put COLUMN_NAME = 'SNAPSHOT_DT' because some of the tables do not contain this column. So the joining condition is both SNAPSHOT_DT are the same, and DDS_VERSION are the same, WHERE golden copy in LKUP table = 'Y'. then loop the script, and insert into @DBLINKSIT2.
But I can't get the script to run and I don't know where I am getting this wrong.
Any help would be appreciated. Thank you.
oracle oracle-sql-developer
oracle oracle-sql-developer
edited Aug 10 '16 at 12:27
Chun
51
asked Aug 5 '16 at 7:35
ChunChun
61
edited Aug 10 '16 at 12:27
Chun
51
asked Aug 5 '16 at 7:35
ChunChun
61
edited Aug 10 '16 at 12:27
Chun
51
edited Aug 10 '16 at 12:27
Chun
51
edited Aug 10 '16 at 12:27
Chun
51
51
asked Aug 5 '16 at 7:35
ChunChun
61
asked Aug 5 '16 at 7:35
ChunChun
61
asked Aug 5 '16 at 7:35
ChunChun
61
61
bumped to the homepage by Community♦ 17 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
bumped to the homepage by Community♦ 17 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
1
Is this a one time only solution? I would go with expdp with network link. oracle-base.com/articles/10g/…
– vercelli
Aug 5 '16 at 8:55
Are you talking about Database Replication? Also as vercelli said you can use Datapump.
– JSapkota
Aug 5 '16 at 10:09
1
To regain control of your question follow the instructions here to merge your unregistered and registered accounts. Once that is completed, you will be able to edit your question without peer review, comment anywhere on this page, and accept an answer when the time comes.
– Paul White♦
Aug 10 '16 at 12:28
add a comment |
1
Is this a one time only solution? I would go with expdp with network link. oracle-base.com/articles/10g/…
– vercelli
Aug 5 '16 at 8:55
Are you talking about Database Replication? Also as vercelli said you can use Datapump.
– JSapkota
Aug 5 '16 at 10:09
1
To regain control of your question follow the instructions here to merge your unregistered and registered accounts. Once that is completed, you will be able to edit your question without peer review, comment anywhere on this page, and accept an answer when the time comes.
– Paul White♦
Aug 10 '16 at 12:28
1
1
Is this a one time only solution? I would go with expdp with network link. oracle-base.com/articles/10g/…
– vercelli
Aug 5 '16 at 8:55
Is this a one time only solution? I would go with expdp with network link. oracle-base.com/articles/10g/…
– vercelli
Aug 5 '16 at 8:55
Are you talking about Database Replication? Also as vercelli said you can use Datapump.
– JSapkota
Aug 5 '16 at 10:09
Are you talking about Database Replication? Also as vercelli said you can use Datapump.
– JSapkota
Aug 5 '16 at 10:09
1
1
To regain control of your question follow the instructions here to merge your unregistered and registered accounts. Once that is completed, you will be able to edit your question without peer review, comment anywhere on this page, and accept an answer when the time comes.
– Paul White♦
Aug 10 '16 at 12:28
To regain control of your question follow the instructions here to merge your unregistered and registered accounts. Once that is completed, you will be able to edit your question without peer review, comment anywhere on this page, and accept an answer when the time comes.
– Paul White♦
Aug 10 '16 at 12:28
add a comment |
1 Answer
1
active
oldest
votes
"However, the tables in the DEV keeps adding/changing, which is why when I run the query, it hit the error saying the table/view does not exist."
Short of putting a lock on every object in the remote database during your copy, I'm not sure this is avoidable. In truth your approach this is unneccessarily complicated. If you want to clone your DB, use the existing cloning tools. There's no need to reinvent the wheel for this application. I'd suggest using datapump to export/import your schema if you want a consistent snapshot.
answered Aug 5 '16 at 10:52
Andrew BrennanAndrew Brennan
1,926610
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%2f145965%2foracle-sql-developer-copy-paste-tables-with-2-different-instances-with-differ%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
"However, the tables in the DEV keeps adding/changing, which is why when I run the query, it hit the error saying the table/view does not exist."
Short of putting a lock on every object in the remote database during your copy, I'm not sure this is avoidable. In truth your approach this is unneccessarily complicated. If you want to clone your DB, use the existing cloning tools. There's no need to reinvent the wheel for this application. I'd suggest using datapump to export/import your schema if you want a consistent snapshot.
answered Aug 5 '16 at 10:52
Andrew BrennanAndrew Brennan
1,926610
add a comment |
"However, the tables in the DEV keeps adding/changing, which is why when I run the query, it hit the error saying the table/view does not exist."
Short of putting a lock on every object in the remote database during your copy, I'm not sure this is avoidable. In truth your approach this is unneccessarily complicated. If you want to clone your DB, use the existing cloning tools. There's no need to reinvent the wheel for this application. I'd suggest using datapump to export/import your schema if you want a consistent snapshot.
answered Aug 5 '16 at 10:52
Andrew BrennanAndrew Brennan
1,926610
add a comment |
"However, the tables in the DEV keeps adding/changing, which is why when I run the query, it hit the error saying the table/view does not exist."
Short of putting a lock on every object in the remote database during your copy, I'm not sure this is avoidable. In truth your approach this is unneccessarily complicated. If you want to clone your DB, use the existing cloning tools. There's no need to reinvent the wheel for this application. I'd suggest using datapump to export/import your schema if you want a consistent snapshot.
answered Aug 5 '16 at 10:52
Andrew BrennanAndrew Brennan
1,926610
"However, the tables in the DEV keeps adding/changing, which is why when I run the query, it hit the error saying the table/view does not exist."
Short of putting a lock on every object in the remote database during your copy, I'm not sure this is avoidable. In truth your approach this is unneccessarily complicated. If you want to clone your DB, use the existing cloning tools. There's no need to reinvent the wheel for this application. I'd suggest using datapump to export/import your schema if you want a consistent snapshot.
answered Aug 5 '16 at 10:52
Andrew BrennanAndrew Brennan
1,926610
answered Aug 5 '16 at 10:52
Andrew BrennanAndrew Brennan
1,926610
answered Aug 5 '16 at 10:52
Andrew BrennanAndrew Brennan
1,926610
answered Aug 5 '16 at 10:52
Andrew BrennanAndrew Brennan
1,926610
1,926610
add a comment |
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%2f145965%2foracle-sql-developer-copy-paste-tables-with-2-different-instances-with-differ%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
Is this a one time only solution? I would go with expdp with network link. oracle-base.com/articles/10g/…
– vercelli
Aug 5 '16 at 8:55
Are you talking about Database Replication? Also as vercelli said you can use Datapump.
– JSapkota
Aug 5 '16 at 10:09
1
To regain control of your question follow the instructions here to merge your unregistered and registered accounts. Once that is completed, you will be able to edit your question without peer review, comment anywhere on this page, and accept an answer when the time comes.
– Paul White♦
Aug 10 '16 at 12:28