Solved

Drop a partition on a table

Posted on 2014-01-18
9
439 Views
Last Modified: 2014-01-21
This was a question in an interview.

Scenario:  I have a table with almost 365 partitions and let us say I want to drop a partition which is almost 200th partition. How would I find the partition name..

Please suggest
0
Comment
Question by:d27m11y
  • 3
  • 3
  • 2
9 Comments
 
LVL 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 125 total points
Comment Utility
Check the dba_tab_partitions view.  Look at partition_name.

Here's the doc link for ALL_TAB_PARTITIONS because it has the descriptions:
http://docs.oracle.com/cd/E11882_01/server.112/e40402/statviews_2108.htm#i1591118

Now, what are you after for the 200th partition?  Can you get it by name?
0
 
LVL 24

Assisted Solution

by:Tomas Helgi Johannsson
Tomas Helgi Johannsson earned 375 total points
Comment Utility
Hi!

The query below will give you all partitions that are between 180th and 220th partition including them and their partitionnames.

select partition_name, partition_position , high_value, tablespace_name from dba_tab_partitions
where table_name = '<table_name>'
and table_owner = '<owner>'
and partition_position between 180 and 220
order by partition_postition

Open in new window


Regards,
     Tomas Helgi
0
 

Author Comment

by:d27m11y
Comment Utility
Thank you all.

Just one other question, let us say if I want to drop partition for the data older than 24 months based on order_date, how do I find the partition and drop it.

Would I be able to query the dba_tab_partitions or user_tab_partitions and if so, how would I be able to find partiton name,  kindly reply asap..

thank you so much for your help !
0
 
LVL 24

Assisted Solution

by:Tomas Helgi Johannsson
Tomas Helgi Johannsson earned 375 total points
Comment Utility
Hi!

The high_value column should give you the highest value of that partition so to determine the highest and lowest values of your partition you will need to get the high_value for partition(n) and the high_value of partition(n-1) as the low_value of partition(n)


http://www.oracle-base.com/articles/11g/partitioning-enhancements-11gr1.php

Regards,
    Tomas Helgi
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:d27m11y
Comment Utility
How do I determine the partition name based on date, please suggest. Would I see the date in high_value column
0
 
LVL 24

Accepted Solution

by:
Tomas Helgi Johannsson earned 375 total points
Comment Utility
Yes.
See examples in the link i provided in earlier post.
Also you can look at these views listed here
http://docs.oracle.com/cd/E11882_01/server.112/e25523/part_admin005.htm
as well as this view
http://docs.oracle.com/cd/B28359_01/server.111/b28320/statviews_2001.htm#REFRN20154



Regards,
   Tomas Helgi
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
d27m11y,

Can I ask why the "B" penalty grade and what additional information you required before providing an "A" grade?

Please review:
http://support.experts-exchange.com/customer/portal/articles/481419-what-grade-should-i-award-?b_id=44

B is the grade given for acceptable solutions, or a link to an acceptable solution. A B grade means the solution given lacked some information or required you to do a good amount of extra work to resolve the problem. When closing the question, the asker should explain why a B grade was awarded.
0
 

Author Comment

by:d27m11y
Comment Utility
I reviewed it and I would like to assign A for this, how do I edit my response
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now