Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Drop a partition on a table

Posted on 2014-01-18
9
Medium Priority
?
450 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
8 Comments
 
LVL 78

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 500 total points
ID: 39791563
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 26

Assisted Solution

by:Tomas Helgi Johannsson
Tomas Helgi Johannsson earned 1500 total points
ID: 39792200
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
ID: 39792402
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 26

Assisted Solution

by:Tomas Helgi Johannsson
Tomas Helgi Johannsson earned 1500 total points
ID: 39792434
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
 

Author Comment

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

Accepted Solution

by:
Tomas Helgi Johannsson earned 1500 total points
ID: 39792530
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 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39795609
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
ID: 39795679
I reviewed it and I would like to assign A for this, how do I edit my response
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
Suggested Courses

879 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