Oracle simple SQL query result in: ORA-08103: object no longer exists


 
Thread Tools Search this Thread
Top Forums Programming Oracle simple SQL query result in: ORA-08103: object no longer exists
# 1  
Old 12-04-2015
Oracle simple SQL query result in: ORA-08103: object no longer exists

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:
Code:
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:
Quote:
ERROR at line 1:
ORA-08103: object no longer exists
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
# 2  
Old 12-04-2015
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.
# 3  
Old 12-05-2015
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
# 4  
Old 12-07-2015
What is the partition key in your table? Or, which columns form a partition in your table? What type of partitioning is being used?
# 5  
Old 12-07-2015
Quote:
Originally Posted by durden_tyler
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
# 6  
Old 12-08-2015
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.
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Get SQL query result to file in putty

How to Get SQL query result to file in putty? I have one SQL query and I want that query output to be redirected to the file. uname -a SunOS XXX 5.8 Generic_117350-58 sun4u sparc SUNW,Sun-Fire-480R Please suggest. (7 Replies)
Discussion started by: pamu
7 Replies

2. UNIX for Dummies Questions & Answers

Removing unnecessary eol ($) character from Oracle sql query output

Hi All, I am fetching oracle query result in shell variable. As columns numbers are more the output wraps in unix terminal .i.e one complete record in db gets store in multiple lines. with each line ends with $ character. I want to remove these unnecessary $ character but to keep required $... (8 Replies)
Discussion started by: Harshal22
8 Replies

3. Shell Programming and Scripting

How to run a SQL select query in Oracle database through shell script?

I need to run a SQL select query in Oracle database and have to capture the list of retrieved records in shell script. Also i would like to modify the query for certain condition and need to fetch it again. How can i do this? Is there a way to have a persistent connection to oracle database... (9 Replies)
Discussion started by: vel4ever
9 Replies

4. Shell Programming and Scripting

perl- oracle sql query

Hi, I am new to perl.How to query oracle database with perl??? Thanks (1 Reply)
Discussion started by: tdev457
1 Replies

5. Shell Programming and Scripting

Read value from user and use it in Oracle SQL query

Guys can anyone just tell me whether i can pass a value(from UNIX SCRIPT) as an ARGUMENT in Oracle Query? e.g. echo "enter value" read value insert into tablename where col=$value /*something like this*/ (1 Reply)
Discussion started by: subodh.thakar
1 Replies

6. UNIX for Advanced & Expert Users

Output the SQL Query result to a File

Hello Guys, This message is somewhat relates with last thread. But I need to re-write thing. I start over a little. I am stuck now and need your help. Here is my script- #! /bin/ksh export ORACLE_HOME=/opt/oracle/app/oracle/product/9.2 /opt/oracle/app/oracle/product/9.2/bin/sqlplus -s... (5 Replies)
Discussion started by: thepurple
5 Replies

7. Shell Programming and Scripting

Oracle SQL Query & connect?

Hi I'm looking to query a table on a database and then iterate over the results in a loop. I believe this is the last part of my script that I need (after finding out threads for passing variables to other scripts and calling functions in other scripts). I've searched the forums but the best... (8 Replies)
Discussion started by: Dird
8 Replies

8. Shell Programming and Scripting

How to Format the result driven from a SQL Query

Hi All, I want to format the result driven from the query into neat format. For example pls find the below code, #! /bin/sh result=' sqlplus -s uname/passwrd@DBname select no,name,address,ph_no, passport_no,salary,designation from emp_table where salary>1000; exit EOF' ... (8 Replies)
Discussion started by: little_wonder
8 Replies

9. Shell Programming and Scripting

Redirecting sql select query result to txt file

Hi Yogesh, Lucky that i caught you online. Yeah i read about DBI and the WriteExcel module. But the server is not supporting these modules. It said..."Cannot locate DBI"..."Cannot locate Spreadsheet::WriteExcel" I tried creating a simple text file to get the query output, but the... (1 Reply)
Discussion started by: dolphin123
1 Replies

10. Shell Programming and Scripting

Redirecting sql select query result to txt file

Hi , I just found you while surfing for the string 'Redirecting sql select query output from within a shell script to txt file/excel file' Could you find time sending me the code for the above question? It'll be great help for me. I have a perl file that calls the sql file... (1 Reply)
Discussion started by: dolphin123
1 Replies
Login or Register to Ask a Question