No question at this time
DBA Top 10
1 A. Kavsek 11100
2 M. Cadot 7600
3 B. Vroman 5100
4 P. Wisse 5000
5 T. Boles 2000
5 . Lauri 2000
7 J. PĂ©ran 1100
8 J. Schnackenberg 700
9 R. Pattyn 600
10 T. P 500
About
DBA-Village
The DBA-Village forum
Forum as RSS
as RSS feed
Site Statistics
Ever registered users48435
Total active users1504
Act. users last 24h5
Act. users last hour0
Registered user hits last week139
Registered user hits last month778
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, 209 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, 209 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, 209 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, 208 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, 204 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