Database in READ WRITE Mode; desc dba_ gives error ;ORA-04043

advertisements

_____________________________________________________________________________________________________________________

Error Description:

The database was working perfectly and there was no major changes done in the database. Database is in Open mode (read write); But still the describe the data dictionary views like DBA_tables gives following error.

 

SQL> desc dba_tables

ERROR:

ORA-04043: object dba_tables does not exist

 

Solution Description

 

This is mainly because you might have tried to execute desc dba_xxxx when the database was in mount stage. The same views will fail even when you turn the database into open mode also. Other views whichever is not tried during the mount stage will work properly. You will have to flush the shared pool once to clear the error. See one example below.

 

SQL> startup mount;

ORACLE instance started.

 

Total System Global Area 1.0689E+10 bytes

Fixed Size                  2237776 bytes

Variable Size            8254393008 bytes

Database Buffers         2415919104 bytes

Redo Buffers               16924672 bytes

Database mounted.

SQL> desc dba_data_files;

ERROR:

ORA-04043: object dba_data_files does not exist

 

 

 

SQL> desc dba_tables

ERROR:

ORA-04043: object dba_tables does not exist

 

SQL> alter database open;

Database altered.

 

SQL> desc dba_tables;

ERROR:

ORA-04043: object dba_tables does not exist

 

desc DBA_VIEWS is giving output as it was not executed when the database was in mount stage.

 

SQL> desc dba_views;

 Name                                      Null?    Type

 ----------------------------------------- -------- -----------------

 OWNER                                     NOT NULL VARCHAR2(30)

 VIEW_NAME                                 NOT NULL VARCHAR2(30)

 TEXT_LENGTH                                        NUMBER

 TEXT                                               LONG

 .....

 .....

 

SQL> alter system flush shared_pool;

 

System altered.

Flush shared_pool .. It started working. J

SQL> desc dba_tables;

 Name                                      Null?    Type

 ----------------------------------------- -------- ---------------

 OWNER                                     NOT NULL VARCHAR2(30)

 TABLE_NAME                                NOT NULL VARCHAR2(30)

 TABLESPACE_NAME                                    VARCHAR2(30)

 CLUSTER_NAME                                       VARCHAR2(30)

 IOT_NAME                                           VARCHAR2(30)

 .....

 .....

_____________________________________________________________________________________________________________________

Website Stats