Solved

I get a ORA-02149

Posted on 2013-01-11
6
3,672 Views
Last Modified: 2013-01-17
iget the following error with a pl/sql block...
Error: -2149;ORA-02149: Specified partition does not exist
can you plese help?
i have attached the script..
Test-Partition-Archiving.txt
0
Comment
Question by:Rao_S
[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
6 Comments
 
LVL 22

Assisted Solution

by:Steve Wales
Steve Wales earned 250 total points
ID: 38768324
You're building a dynamic partition name based upon current date.

What is the output of: dbms_output.put_line ('lv_partition: '||lv_partition)

(You'll need to do set serveroutput on first in order to see it).

Does that partition exist ?
0
 

Author Comment

by:Rao_S
ID: 38768358
i get:
lv_partition: P_201112
Error: -2149;ORA-02149: Specified partition does not exist

i am building it dynamic because i want to scedule it later..
0
 
LVL 22

Assisted Solution

by:Steve Wales
Steve Wales earned 250 total points
ID: 38768508
Does that partition exist ?

select * from dba_tab_partitions
0
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!

 
LVL 32

Accepted Solution

by:
awking00 earned 125 total points
ID: 38768588
Don't really need everything - select partition_name from dba_tab_partitions where owner = 'SCHEMA1' and table_name = 'TABLE1';
0
 
LVL 6

Assisted Solution

by:Javier Morales
Javier Morales earned 125 total points
ID: 38769621
Your code has an error.

Look:
The EXECUTE IMMEDIATE can't solve the value of lv_partition as its a variable of pl/SQL block

That's why "partition lv_partition does not exists". It works for the SQL sentence, but execute immediate is a literal.

    EXECUTE IMMEDIATE 'ALTER TABLE SCHEMA1.TABLE1 DROP PARTITION(lv_partition)';

You should write:

EXECUTE IMMEDIATE 'ALTER TABLE SCHEMA1.TABLE1 DROP PARTITION('''||lv_partition||''')';

To solve lv_partition value took from the previous select into statement.

Hope it helps,
Javier
0
 

Author Closing Comment

by:Rao_S
ID: 38786920
thank you all, i also found some more example on line and the solutions worked.
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering 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

Suggested Solutions

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.
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
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.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

752 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