Skip to Main Content
  • Questions

  • Show error about 'ORA-04043: object dba_data_files does not exist' on my Oracle DataGuard 11.2.0.1.0 physical standby when query 'desc dba_data_files' ??

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Quanwen.

Asked: August 29, 2017 - 7:25 am UTC

Last updated: August 30, 2017 - 5:23 am UTC

Version: 11.2.0.1.0

Viewed 10K+ times! This question is

You Asked

Hi,
When i query description of table 'dba_data_files' and 'dba_temp_files' with SQL command 'desc dba_data_files' and 'desc dba_temp_files' on Oracle DataGuard 11.2.0.1.0 physical standby,there has an error,content as follows,
SYS@standby> desc dba_data_files;
ERROR:
ORA-04043: object dba_data_files does not exist
 
SYS@standby> desc dba_temp_files;
ERROR:
ORA-04043: object dba_temp_files does not exist


But query view 'dba_tablespaces' is Ok,check as follows,
SYS@standby> desc dba_tablespaces;
Name                                                              Null?    Type
----------------------------------------------------------------- -------- --------------------------------------------
TABLESPACE_NAME                                                   NOT NULL VARCHAR2(30)
BLOCK_SIZE                                                        NOT NULL NUMBER
INITIAL_EXTENT                                                             NUMBER
NEXT_EXTENT                                                                NUMBER
MIN_EXTENTS                                                       NOT NULL NUMBER
MAX_EXTENTS                                                                NUMBER
MAX_SIZE                                                                   NUMBER
PCT_INCREASE                                                               NUMBER
MIN_EXTLEN                                                                 NUMBER
STATUS                                                                     VARCHAR2(9)
CONTENTS                                                                   VARCHAR2(9)
LOGGING                                                                    VARCHAR2(9)
FORCE_LOGGING                                                              VARCHAR2(3)
EXTENT_MANAGEMENT                                                          VARCHAR2(10)
ALLOCATION_TYPE                                                            VARCHAR2(9)
PLUGGED_IN                                                                 VARCHAR2(3)
SEGMENT_SPACE_MANAGEMENT                                                   VARCHAR2(6)
DEF_TAB_COMPRESSION                                                        VARCHAR2(8)
RETENTION                                                                  VARCHAR2(11)
BIGFILE                                                                    VARCHAR2(3)
PREDICATE_EVALUATION                                                       VARCHAR2(7)
ENCRYPTED                                                                  VARCHAR2(3)
COMPRESS_FOR                                                               VARCHAR2(12)


Now, how to sove this problem for me ? Thanks!

Best Regards
Quanwen Zhao

and Connor said...

If your database is in mount state (which your standby probably is if it is doing recovery), then not all dictionary structures can be seen, because the database is not open. Only those structures that can be seen via the fact that the *instance* is running are accessible.

eg, on my 12.2 database

SQL> startup mount
ORACLE instance started.

Total System Global Area  838860800 bytes
Fixed Size                  8752376 bytes
Variable Size             343933704 bytes
Database Buffers          478150656 bytes
Redo Buffers                8024064 bytes
Database mounted.
SQL> desc dba_data_files
ERROR:
ORA-04043: object dba_data_files does not exist

SQL> desc dba_tablespaces
ERROR:
ORA-04043: object dba_tablespaces does not exist

SQL> desc v$session
 Name                                                                    Null?    Type
 ----------------------------------------------------------------------- -------- --------------------------
 SADDR                                                                            RAW(8)
 SID                                                                              NUMBER
 SERIAL#                                                                          NUMBER
 AUDSID                                                                           NUMBER
 PADDR                                                                            RAW(8)
 USER#                                                                            NUMBER
 USERNAME                                                                         VARCHAR2(128)
 COMMAND                                                                          NUMBER
 OWNERID                                                                          NUMBER
 TADDR                                                                            VARCHAR2(16)
 LOCKWAIT                                                                         VARCHAR2(16)
 STATUS                                                                           VARCHAR2(8)
 SERVER                                                                           VARCHAR2(9)
 SCHEMA#                                                                          NUMBER
 SCHEMANAME                                                                       VARCHAR2(128)
 OSUSER                                                                           VARCHAR2(128)
 PROCESS                                                                          VARCHAR2(24)
 MACHINE                                                                          VARCHAR2(64)
 PORT                                                                             NUMBER
 TERMINAL                                                                         VARCHAR2(16)
 PROGRAM                                                                          VARCHAR2(64)
 TYPE                                                                             VARCHAR2(10)
 SQL_ADDRESS                                                                      RAW(8)
 SQL_HASH_VALUE                                                                   NUMBER
 SQL_ID                                                                           VARCHAR2(13)
 SQL_CHILD_NUMBER                                                                 NUMBER
 SQL_EXEC_START                                                                   DATE
 SQL_EXEC_ID                                                                      NUMBER
 PREV_SQL_ADDR                                                                    RAW(8)
 PREV_HASH_VALUE                                                                  NUMBER
 PREV_SQL_ID                                                                      VARCHAR2(13)
 PREV_CHILD_NUMBER                                                                NUMBER
 PREV_EXEC_START                                                                  DATE
 PREV_EXEC_ID                                                                     NUMBER
 PLSQL_ENTRY_OBJECT_ID                                                            NUMBER
 PLSQL_ENTRY_SUBPROGRAM_ID                                                        NUMBER
 PLSQL_OBJECT_ID                                                                  NUMBER
 PLSQL_SUBPROGRAM_ID                                                              NUMBER
 MODULE                                                                           VARCHAR2(64)
 MODULE_HASH                                                                      NUMBER
 ACTION                                                                           VARCHAR2(64)
 ACTION_HASH                                                                      NUMBER
 CLIENT_INFO                                                                      VARCHAR2(64)


Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Following Document ID 296235.1 to solve

Quanwen Zhao, August 30, 2017 - 8:16 am UTC

Yep,i query 'desc dba_data_files(or dba_temp_files) actually on MOUNT state of DB,and later,afterwards i switch to state of OPEN READ ONLY,therefore it has also error.
Following Oracle Support Document ID 296235.1 to solve it finally(via Flush the shared pool,because it has Data Guard physical standby and have no business running on it).

Regards
Quanwen Zhao


more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database