Solved

I get a ORA-02149

Posted on 2013-01-11
6
3,862 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
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 7

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

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

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

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Suggested Courses

617 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