Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 454
  • Last Modified:

Drop a partition on a table

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
d27m11y
Asked:
d27m11y
  • 3
  • 3
  • 2
4 Solutions
 
slightwv (䄆 Netminder) Commented:
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
 
Tomas Helgi JohannssonCommented:
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
 
d27m11yAuthor Commented:
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
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Tomas Helgi JohannssonCommented:
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
 
d27m11yAuthor Commented:
How do I determine the partition name based on date, please suggest. Would I see the date in high_value column
0
 
Tomas Helgi JohannssonCommented:
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
 
slightwv (䄆 Netminder) Commented:
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
 
d27m11yAuthor Commented:
I reviewed it and I would like to assign A for this, how do I edit my response
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 3
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now