Solved

Drop a partition on a table

Posted on 2014-01-18
9
443 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 77

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 125 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 25

Assisted Solution

by:Tomas Helgi Johannsson
Tomas Helgi Johannsson earned 375 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
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.

 
LVL 25

Assisted Solution

by:Tomas Helgi Johannsson
Tomas Helgi Johannsson earned 375 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 25

Accepted Solution

by:
Tomas Helgi Johannsson earned 375 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 77

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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
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 Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

830 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