Solved

Partitioning Oracle table

Posted on 2015-01-16
5
154 Views
Last Modified: 2015-02-13
I have a table and I like create partitioning and like to keep only last 10 days of data.The idea is to drop the partitioning using a  script (data which is older than 10 days ).Every day the table gets around half a million of rows except saturday and sunday . The table have the timestamp column.
How can I do the partitioning of this table so that it can be manageable using partitioning and I can manage and keep only 10 days of data. Please note that the data is not required older than 7 days but I will keep for another day or 2 ( so may be 10 days at the max).
0
Comment
Question by:Oranew
[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
  • 2
5 Comments
 
LVL 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 total points
ID: 40553907
What version of Oracle?

If you are on 11gR2 take a look interval partitioning.

There are restrictions so I suggest you look in the docs to see if this is an option for you:
Restrictions on Interval Partitioning

http://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_7002.htm#SQLRF01402


Example and discussion here:
https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:549370300346187664
0
 

Author Comment

by:Oranew
ID: 40553911
Yes, 11g rel 2.
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40554035
Then the links above should be what you want.
0
 

Author Comment

by:Oranew
ID: 40554060
The above link says the interval partition only works on date or number column  but I have timestamp so this is not for me.
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40554071
I'm not sure what you are reading that states a timestamp won't work but it will.

Check out the example in the AskTom link and you will see that it is based on a timestamp.
0

Featured Post

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.

Question has a verified solution.

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

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
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…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
Suggested Courses

627 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