No question at this time
DBA Top 10
1 A. Kavsek 8600
2 M. Cadot 7200
3 B. Vroman 6200
4 T. Boles 4550
5 J. Schnackenberg 4100
6 P. Wisse 3200
7 T. P 1200
8 G. Lambregts 1100
8 . Lauri 1100
10 R. Pattyn 800
About
DBA-Village
The DBA-Village forum
Forum as RSS
as RSS feed
Site Statistics
Ever registered users48373
Total active users1542
Act. users last 24h7
Act. users last hour0
Registered user hits last week192
Registered user hits last month1051
Go up

Monthly Table Partition Export (datapump)
Next thread: Issue in GG Replication - OGG-01296
Prev thread: datapump and dbfs

Message Score Author Date
Hello, We have a scenario, We want to export mo...... Taoqir Hassan Mar 20, 2019, 08:11
The best and safest way, I think, is to exchange...... Score: 300 PtsScore: 300 PtsScore: 300 PtsScore: 300 PtsScore: 300 Pts Michel Cadot Mar 20, 2019, 08:55
Hi, I agree with Michel. But, be very careful ...... Score: 300 PtsScore: 300 PtsScore: 300 PtsScore: 300 PtsScore: 300 Pts Ales Kavsek Mar 20, 2019, 14:54
Hello Taoqir, you can also use the QUERY clause...... Score: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 Pts Bruno Vroman Mar 21, 2019, 15:26
Thanks to all my seniors, I will search on tabl...... Taoqir Hassan Mar 25, 2019, 09:39

Follow up by mail Click here


Subject: Monthly Table Partition Export (datapump)
Author: Taoqir Hassan, Pakistan
Date: Mar 20, 2019, 08:11, 63 days ago
Os info: AIX
Oracle info: 11gR [11.2.0.4]
Message: Hello,

We have a scenario, We want to export monthly table partition/s (not current, current-1 month) and drop that partition if it's export is successfully.


Regards,
Taoqir
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Monthly Table Partition Export (datapump)
Author: Michel Cadot, France
Date: Mar 20, 2019, 08:55, 63 days ago
Score:   Score: 300 PtsScore: 300 PtsScore: 300 PtsScore: 300 PtsScore: 300 Pts
Message:
The best and safest way, I think, is to exchange the partition with an empty table, export the exchanged table then, if successful, drop the (now empty) partition.

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

Subject: Re: Monthly Table Partition Export (datapump)
Author: Ales Kavsek, Slovenia
Date: Mar 20, 2019, 14:54, 63 days ago
Score:   Score: 300 PtsScore: 300 PtsScore: 300 PtsScore: 300 PtsScore: 300 Pts
Message: Hi,

I agree with Michel. But, be very careful how you declare that datapump export is a success. I would do this:

1) as Michel suggested, exchange monthly partition with an empty table

2) run datapump export (either via PL/SQL API or expdp) of this table

** I would not rely on expdp exit code alone to declare that dump file is ok, instead I would...

3) run datapump import with sqlfile=testimp.sql to check, that dump file is valid (refer to MOS DOC ID 2113967.1)

4) if step 3 is ok, I would truncate table and drop the partition

Regards,
Ales

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

Subject: Re: Monthly Table Partition Export (datapump)
Author: Bruno Vroman, Belgium
Date: Mar 21, 2019, 15:26, 62 days ago
Score:   Score: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 Pts
Message: Hello Taoqir,

you can also use the QUERY clause of expdp to export only the relevant data (and Oracle will use partition pruning to address only the relevant partition)

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: Monthly Table Partition Export (datapump)
Author: Taoqir Hassan, Pakistan
Date: Mar 25, 2019, 09:39, 58 days ago
Message: Thanks to all my seniors,

I will search on table exchange and will let you know accordingly.

Thanks,

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