Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 4487
  • Last Modified:

I get a ORA-02149

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
Rao_S
Asked:
Rao_S
4 Solutions
 
Steve WalesSenior Database AdministratorCommented:
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
 
Rao_SAuthor Commented:
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
 
Steve WalesSenior Database AdministratorCommented:
Does that partition exist ?

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

 
awking00Commented:
Don't really need everything - select partition_name from dba_tab_partitions where owner = 'SCHEMA1' and table_name = 'TABLE1';
0
 
Javier MoralesOracle DBACommented:
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
 
Rao_SAuthor Commented:
thank you all, i also found some more example on line and the solutions worked.
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.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now