Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Drop a partition on a table

Posted on 2014-01-18
9
Medium Priority
?
448 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 2
9 Comments
 
LVL 77

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 25

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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
LVL 25

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 25

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 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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

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…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines

688 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