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

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)
 .....
 .....
DBA Tips Data Pump Reference

0 comments:

Post a Comment

 

dba topics. Copyright 2011-16 All Rights Reserved | Site Map | Contact | Disclaimer | Google