No question at this time
DBA Top 10
1 B. Vroman 15100
2 M. Cadot 14200
3 A. Kavsek 9300
4 J. Schnackenberg 6100
5 T. Boles 5500
6 M. Hidayathullah ... 5400
7 P. Wisse 2500
8 T. P 900
9 R. Wauben 500
9 H. Steijntjes 500
About
DBA-Village
Download PLATO
The free tool for auditing and tuning your database
Version 55 now available
Sep 02, 2016
The DBA-Village forum
Forum as RSS
as RSS feed
Site Statistics
Ever registered users48178
Total active users1670
Act. users last 24h2
Act. users last hour0
Registered user hits last week257
Registered user hits last month1287
Go up

Trying to create a procedure to load partition data form one database to another using db links
Next thread: SPM 12c
Prev thread: Special Charecters

Message Score Author Date
Hello all... It's been years I posted somethin...... raghavendra rao yella Apr 03, 2018, 22:11
<pre> CREATE OR REPLACE PROCEDURE MATRIX_OWNER.A...... raghavendra rao yella Apr 03, 2018, 22:16
Any thoughts on this? Thanks in advance... ... raghavendra rao yella Apr 04, 2018, 15:37
Your procedure runs dynamic SQL in EXECUTE IMMEDIA...... Score: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 Pts Jan Schnackenberg Apr 06, 2018, 09:45
Hello, not a reply to your question, but what a...... Bruno Vroman Apr 06, 2018, 19:07
haha.. Thanks Jan... That made the difference... r...... raghavendra rao yella Apr 09, 2018, 20:53
Hi Bruno.. Yeah.. previously.. (6 years back) ...... raghavendra rao yella Apr 09, 2018, 20:57
Just wanted to give an update to everyone... I was...... raghavendra rao yella Apr 12, 2018, 20:57
Thanks for the update. Did you notice the little "...... Jan Schnackenberg Apr 13, 2018, 09:51

Follow up by mail Click here


Subject: Trying to create a procedure to load partition data form one database to another using db links
Author: raghavendra rao yella, United States
Date: Apr 03, 2018, 22:11, 15 days ago
Os info: Solaris Spark
Oracle info: 11.2.0.3
Error info: Error at line 2
ORA-00904: "CUR_ARRAY": invalid identifier
ORA-06512: at line 136
Message: Hello all...

It's been years I posted something here...
I'm currently looking for a way to copy partition data(20 million records each partition) from database to another.

I'm planning on creating a procedure which will be scheduled to run once a month to copy previous months data into reporting environment.

Created a procedure with cursors, bulk collect and using database link without issues.. but when i execute it.. it is erroring out saying invalid identifier...

Data types in this table are NUMBERS, VARCHAR, DATE fields.

Any thoughts?

Will post the code in my 1st comment...
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Trying to create a procedure to load partition data form one database to another using db links
Author: raghavendra rao yella, United States
Date: Apr 03, 2018, 22:16, 15 days ago
Message:
 

CREATE OR REPLACE PROCEDURE MATRIX_OWNER.ADT_LOAD_PROC_V3
IS
var1 VARCHAR2 (4000);
COUNT1 NUMBER := 0;

CURSOR ESN_DATA_CUR
IS
SELECT ID,
DATE_ENTERED,
CYCLE_DATE,
ACCOUNT_SUMMARY_TRANSACTION
FROM ADT@test.WORLD
WHERE date_entered > TRUNC (ADD_MONTHS (SYSDATE, -1), 'month')
AND date_entered < TRUNC (ADD_MONTHS (SYSDATE, 0), 'month');

TYPE FETCH_ARRAY IS TABLE OF ESN_DATA_CUR%ROWTYPE;

CUR_ARRAY FETCH_ARRAY;
BEGIN
OPEN ESN_DATA_CUR;
LOOP
FETCH ESN_DATA_CUR BULK COLLECT INTO CUR_ARRAY LIMIT 200000;

FOR I IN 1 .. CUR_ARRAY.COUNT
LOOP
BEGIN
var1 := '
INSERT INTO ADT_LOCAL (ID,
DATE_ENTERED,
CYCLE_DATE,
ACCOUNT_SUMMARY_TRANSACTION)
values( CUR_ARRAY(I).ID,
CUR_ARRAY(I).DATE_ENTERED,
CUR_ARRAY(I).CYCLE_DATE,
CUR_ARRAY(I).ACCOUNT_SUMMARY_TRANSACTION)';

EXECUTE IMMEDIATE var1;
COUNT1 := COUNT1 + 1;
IF (COUNT1 >= 200000)
THEN
COMMIT;
COUNT1 := 0;
END IF;
END;
END LOOP;
EXIT WHEN ESN_DATA_CUR%NOTFOUND;
END LOOP;
COMMIT;
CLOSE ESN_DATA_CUR;
COMMIT;
END ADT_LOAD_PROC_V3;
/

Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Trying to create a procedure to load partition data form one database to another using db links
Author: raghavendra rao yella, United States
Date: Apr 04, 2018, 15:37, 14 days ago
Message: Any thoughts on this? Thanks in advance...
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Trying to create a procedure to load partition data form one database to another using db links
Author: Jan Schnackenberg, Germany
Date: Apr 06, 2018, 09:45, 13 days ago
Score:   Score: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 Pts
Message: Your procedure runs dynamic SQL in EXECUTE IMMEDIATE.

In the command-string for this you use variable-references only known in the PL/SQL block.

You need to rewrite how you build the command-string for EXECUTE IMMEDIATE.

Regards,
Jan
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Trying to create a procedure to load partition data form one database to another using db links
Author: Bruno Vroman, Belgium
Date: Apr 06, 2018, 19:07, 12 days ago
Message: Hello,

not a reply to your question, but what about another solution:
run expdp for the relevant tables with a 'where' clause to export the right data
run impdp in in target with table_exists_action = append to load it in already existing tables...

(just an Idea)

Best regards,

Bruno Vroman.
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Trying to create a procedure to load partition data form one database to another using db links
Author: raghavendra rao yella, United States
Date: Apr 09, 2018, 20:53, 9 days ago
Message: haha.. Thanks Jan... That made the difference... removed the variable portion.. and
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Trying to create a procedure to load partition data form one database to another using db links
Author: raghavendra rao yella, United States
Date: Apr 09, 2018, 20:57, 9 days ago
Message: Hi Bruno..

Yeah.. previously.. (6 years back) one of the dba used to perform similar activity...
export the required partition.
Import it into a temp table on target and perform exchange partition.

I'm trying for less manually work...may be if above process is hitting performance issues on source.. then only option is exp/imp :)

Thanks.. Raghu
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Trying to create a procedure to load partition data form one database to another using db links
Author: raghavendra rao yella, United States
Date: Apr 12, 2018, 20:57, 6 days ago
Message: Just wanted to give an update to everyone... I was able to successfully create the procedure..
Was able to load 19 million records(1 month partition) in about 2 hours.

Created job to kick off this procedure every month 1st Saturday... All set now :) one less thing to look into...

Thanks everyone.. for your valuable suggestions and time...
Appreciate it!
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Trying to create a procedure to load partition data form one database to another using db links
Author: Jan Schnackenberg, Germany
Date: Apr 13, 2018, 09:51, 6 days ago
Message: Thanks for the update. Did you notice the little "good" and "excellent" links? We use them to show our appreciation for helpful posts in this forum. :)
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here