Server error message ORA - 01502

Thanks dbalaski,

But that's where the problem is, I can't find it so cannot rebuild it. however, I found a reference in the SYS.OBJ$ table(it is the only object with TYPE# as 0). I did not find it anywhere else in the system.

Also no additional products are installed and I did not try the Export/ Import before this problem occured either.

Try these two queries:

  1  select owner,object_name,object_type
  2  from dba_objects
  3  where object_name like '\_%' escape '\'
  4* and owner ='SYS'
SQL> /

OWNER                          OBJECT_NAME                    OBJECT_TYPE
------------------------------ ------------------------------ ------------------
SYS                            _ALL_REPCOLUMN                 VIEW
SYS                            _ALL_REPFLAVOR_OBJECTS         VIEW

SQL> l
  1  select owner,segment_name,segment_type
  2  from dba_segments
  3  where segment_name like '\_%' escape '\'
  4* and owner ='SYS'
SQL> /

OWNER                          SEGMENT_NAME                   SEGMENT_TYPE
------------------------------ ------------------------------ ------------------
SYS                            _default_auditing_options_     TABLE




dBalaski

You'll find the reference to _NEXT_OBJECT with the following

select * from sys.obj$  where name like '_NEXT_OBJECT';

This is typically hidden by the DBA_OBJECTS view as it is used to determine the id for the next object created.

The problem you are encountering may indicate that there is a corruption in the catalog or the database, I recommend you call Oracle on this, as they may be able to provide you with a script to do some simple verification.

Like SMERRIGA, I suggest that you contact Oracle Support.  If the corruption is in the data dictionary, they may ask you to re-run catalog.sql and/or catproc.sql (as SYS).

Yes -- I tend to agree with SMERRIGA  &  johnnyce  too.

Calling oracle support would be the best option at this point.

dBalaski

Something to try before you call --

1)  Before calling Oracle Support,  I  recommends that you check the data dictionary objects for possible corruption.  The following SQL script to do this can be generated as follows from SQL*PLUS (connected as SYS) -- once it completes,   run the spooled output file   "analyze_objects1.sql"
===============
set verify off timing off echo off heading off feedback off
set pagesize 0 linesize 150
spool analyze_objects1.sql
select 'ANALYZE TABLE "'||table_name||'" VALIDATE STRUCTURE CASCADE;'
from dba_tables
where tablespace_name='SYSTEM'
  and owner='SYS' union all
select 'ANALYZE CLUSTER "'||cluster_name||'" VALIDATE STRUCTURE CASCADE;'
from dba_clusters
where tablespace_name='SYSTEM'
  and owner='SYS';
set verify on timing on echo on heading on
set feedback on pagesize 20 linesize 80
spool off
=======================

 Once completed,  you should have an idea if the problem is caused by a corrupt block.

2) This won't hurt anything,  but it "may" clear up the problem...
Log in to svrmgrl  connectintg as SYS  ("connect internal" will do the same).
Shutdown and restart the database ...
run the following scripts

@$ORACLE_HOME/rdbms/admin/catalog.sql
@$ORACLE_HOME/rdbms/admin/catproc.sql


These scripts are usually run at creation time -- however, they can be re-run whenever....
catalog.sql: Creates the data dictionary and public synonyms for many of its views & grants PUBLIC access to the synonyms
catproc.sql:  Runs all scripts required for or used with PL/SQL  

Once you run them --   check for any invalid objects and recompile them...
-----------------------------------------


Hope this helps,

dBalaski

Thanks All,

However, since I could do all operations on the database other than insert, I took an export and generated a new database.
Thanks for your help.

Samit

Please update and finalize this old, open question.  Please:

1) Award points ... if you need Moderator assistance to split points, comment here with details please or advise us in Community Support with a zero point question and this question link.
2) Ask us to delete it if it has no value to you or others
3) Ask for a refund so that we can move it to our PAQ at zero points if it did not help you but may help others.

EXPERT INPUT WITH CLOSING RECOMMENDATIONS IS APPRECIATED IF ASKER DOES NOT RESPOND.

Thanks,
Moondancer - EE Moderator

P.S.  Click your Member Profile, choose View Question History to go through all your open and locked questions to update them.

The answer is to export and re-import.
JT

Q. answered by questioner.
Either force accept of his own comment or delete question altogether (though the question and answer are both valid they come from the same person)
JT