Dear community,
please help with a query on Oracle. I'm using SQLPlus (but with SQLDeveloper is the same) to accamplish a sinple query like:
select count(*) from ARCHIT_D_TB where (TYP_ID=22 OR TYP_ID=23) and SUB_TM like '%SEP%' and CONS=1234
This is a very simple query that works perfect until I'll execute it on a big table that contains tons of data. After a few minutes I got:
This because the database is partitioned and due to large ammount of data in the table and before my query finishes, oracle BT mechanism rotates the table partitions. That's why I got the message.
Now, is there a way to avoid this error? Maybe specify the partition or something like that.
Thanks
Lucas
Partitions are based on keys. You apparently are not using that key to select your resultset. So that you do not traverse parititions.
However.
11g and earlier has lots of bugs that also produce this error - I took your explanation of why you got the error to be correct. If you are fully patched on your version of oracle then this bogus error problem should not exist - and FWIW, I've never seen this error on a partitioned table, ever.
Thanks for reply, unfortunately I'm not the DB maintainer so I cannot upgrade/check the Oracle version, nor I cannot patch it. Btw, the query is simple, and as already wrote, the problem is related to large ammount of data contained on table. Executing it on other table with less data, it works perfect.
That's why I'm asking if there's any method to avoid the error caused by table rotation.
Lucas
What is the partition key in your table? Or, which columns form a partition in your table? What type of partitioning is being used?
Questions I'm not able to answer! 
If you have any commands to show what you need, I can execute them on the system!
Thanks
Lucas
What both of us are after: what is the name of the column in the table (partition) that is the key for the paritition? If you reference that column (or field) AND your assessment is correct, then your query may work correctly.
You do realize that really long-running queries - several hours or more - also run the risk of inducing rollback segment errors, which may actually be your problem as well.
A Large dataset is not necessarily the cause of any problem - it is usually either poor query design or poor indexing, partitioning. Did you check the EXPLAIN PLAN for your query?
If not, assume nothing, until you have created one and understood what it is telling you.
Okay? Without one we really cannot help you constructively.