ORA-08102 while creating a view

oracleoracle-10goracle-11g-r2

I am learning PL/SQL programming. I'm trying to create a view with the following statement:

create or replace view viewName as select ID from employees;

The view creation throws the following error:

ORA-00604 error occurred at recursive level 1
ORA-08102 Index not found, obj #39 , file 1 block 61248

How can I fix it?

The problem is with all objects, procedures,functions,views and i am using the HR Database of Oracle 11G for learning purposes.

Best Answer

File 1 belongs to the SYSTEM tablespace. Objects with such low id as 39 are dictionary objects. For example in my database it is an index of OBJ$ (I_OBJ4). ORA-08102 also points us towards an index, which seems to be corrupt based on the error you get.

Find the index name by:

select object_name from dba_objects where object_id = 39;

Unfortunately you will not be able to rebuild this index (ORA-00701), but you can recreate it:

shutdown immediate
startup upgrade
alter session set nls_length_semantics=byte;
@?/rdbms/admin/utlmmig

Related Solutions

What to do with those invalid JAVA CLASS object type objects in SYS prior to the upgrade the oracle db from 10g to 11g

From this blog you can try this on development first...

SQL> spool resolve.sql;

SQL> select ‘alter java class “‘||object_name||’” resolve;’
2 from user_objects
3 where object_type like ‘%JAVA%’;

SQL> spool off;

SQL> @resolve

The clean sweep approach would be to remove and reinstall the java JVM

The Java VM is created and populated with system classes during CREATE OR REPLACE JAVA SYSTEM command. Run rmjvm.sql to remove the JVM first, then bounce the database then initjvm.sql

I repeat, these should be tried on your development stack first!

Configure Oracle user in Enterprise Manager to CREATE VIEW on any of his tables

I'm not sure if I'm understanding your question properly. I created a user with the ability to create views for his own schema.

SQL> create user view_user identified by password default tablespace users quota unlimited on users;

User created.

SQL> grant create session, create table, create view to view_user;

Grant succeeded.

SQL> connect view_user
Enter password: 
Connected.
SQL> CREATE TABLE Members (ID number(10), Name varchar2(100), Street_No varchar2(100), ZIP number(5), City varchar2(100));

Table created.

SQL> CREATE VIEW MembersNamesOnly AS SELECT ID, Name FROM Members;

View created.

SQL>

Best Answer

Related Solutions

What to do with those invalid JAVA CLASS object type objects in SYS prior to the upgrade the oracle db from 10g to 11g

Configure Oracle user in Enterprise Manager to CREATE VIEW on any of his tables

Related Question