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

x
?
Solved

Date Format for Partitions In Oracle

Posted on 2013-05-13
14
Medium Priority
?
327 Views
Last Modified: 2014-04-22
I want to change this statment:
 From tablename  partition(p20130511) tb

To look two days back, so I don't have to keep on inputting the dates.

I want to change it to something like this:
From tablename  partition(p (sysdate  - interval '2' day)) tb

I am returning an invalid partition error when I try this.  Could someone please let me know how I can do this?
0
Comment
Question by:jjrr007
[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
  • 5
  • 5
  • 2
  • +1
14 Comments
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39161885
You cannot do dynamic SQL like this on a table (or partition).

It is sort of the equivalent of this:
say you have a table named BOB1:  You cannot do this:
select * from 'BOB' || '1';

You will need to use dynamic SQL.
0
 
LVL 1

Author Comment

by:jjrr007
ID: 39161909
Could you please provide an example of how to do this (using dynamic SQL or another method)?  I have increase the point value of this question to the max.
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39161932
What tool are you using to run this and what is the end result?

In pl/sql it goes something like:

declare
    mycursor sys_refcursor;
begin
    open mycursor for 'select * from ' || 'BOB' || '1';
end;
/
0
Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

 
LVL 74

Expert Comment

by:sdstuber
ID: 39161966
building off of slightwv's example using your date format...


DECLARE
    mycursor   SYS_REFCURSOR;
BEGIN
    OPEN mycursor FOR
        'select * from ' || 'tablename partition(p' || TO_CHAR(SYSDATE - 2, 'yyyymmdd') || ')';
END;
0
 
LVL 1

Author Comment

by:jjrr007
ID: 39162501
I am using SQL Plus.  Woud the examples provided work in SQL Plus?
0
 
LVL 77

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 400 total points
ID: 39162543
You cannot do dynamic sql directly in sqlplus.

try this:
SQL>  var mycur refcursor
SQL> open :mycur for  'select * from ' || 'tablename partition(p' || TO_CHAR(SYSDATE - 2, 'yyyymmdd') || ')';
SQL> print mycur
0
 
LVL 1

Author Comment

by:jjrr007
ID: 39162919
I'm not following why your earlier posts will not work in SQL Plus.  I am just running a query in SQL Plus.  Why do I need to change the query, because of using SQL Plus?
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39162948
sqlplus does not allow dynamic SQL like you are wanting to do.

The latest code I posted can run in sqlplus but the 'exec' call is actually handed off to the PL/SQL engine.
0
 
LVL 1

Author Comment

by:jjrr007
ID: 39162977
Thanks for the responses!  I appreciate your time.

 So I just paste the code you wrote (with the table names) in a query and have SQL plus run it.  Is that right or do I enter it into the command window?

How do I use the results- could I use mycurr as a table like this or using a temp table?

select *
From mycur

If I can use it in a temp table, please provide an example.  I should be all done once I know where to enter this (in the query or command using SQL plus) and how do I use the cursor results.
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39163120
>>select * From mycur

You cannot select from a cursor.

>>If I can use it in a temp table, please provide an example.  

This goes back to my post asking how the information will be used.

We need to know more about what you are doing before we can suggest the 'best' way to do it ('best' is in quotes because what is 'best' changes with differing opinions).

What we know:
You have data in a partition.
You want to select just from that partition.

We need to know the rest:
Where and how will it be used?
Is this part of a larger script?
Will you be using the select data for something else?
???
0
 
LVL 7

Accepted Solution

by:
Docteur_Z earned 1600 total points
ID: 39165321
I don't understand why you don't simply use a "where" clause with the date, and let Oracle do the partition prunning itself.....
something like :
select * from tablename  where 
partition_key_column between 
sysdate  - interval '3' day and sysdate  - interval '2' day 

Open in new window

0
 
LVL 7

Expert Comment

by:Docteur_Z
ID: 39187767
Hi,
Any news on this one ?
0
 
LVL 1

Author Closing Comment

by:jjrr007
ID: 40016039
Placing it in the where statment worked. Sometimes we forget the simple stuff.  

I appreciate the info that you can not do dynamic sql in sql plus from slightwv
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

Your data is at risk. Probably more today that at any other time in history. There are simply more people with more access to the Web with bad intentions.
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
This video shows how to recover a database from a user managed backup
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

661 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