We have a test DB with version 11.2.0.4.0, that is restored from the prd DB backup.
Recently, there are some strange phenomena that result in the failure work of this test DB！
We highly doubt that there should be some problematic metadata about the dictionary table/views damaged for some unknown reason！
So we tried to firstly recompile the test DB with the catalog sql ...., but this was also failed！
SQL>
SQL>
SQL>
SQL> select * from v$tempseg_usage;
select * from v$tempseg_usage
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01652: unable to extend temp segment by 128 in tablespace PSAPTEMP
SQL>
SQL>
SQL>
SQL> select inst_id, tablespace_name, total_blocks, used_blocks, free_blocks from gv$sort_segment;
INST_ID TABLESPACE_NAME TOTAL_BLOCKS USED_BLOCKS FREE_BLOCKS
---------- ------------------------------- ------------ ----------- -----------
1 PSAPTEMP 4194048 108288 4085760
SQL>
SQL>
SQL> desc v$tempseg_usage
ERROR:
ORA-00604: error occurred at recursive SQL level 1
ORA-01652: unable to extend temp segment by 128 in tablespace PSAPTEMP
SQL>
SQL>
SQL>
SQL> desc gv$sort_segment
Name Null? Type
----------------------------------------------------------------------------------------- -------- ------------------------------------------------------------
INST_ID NUMBER
TABLESPACE_NAME VARCHAR2(31)
SEGMENT_FILE NUMBER
SEGMENT_BLOCK NUMBER
EXTENT_SIZE NUMBER
CURRENT_USERS NUMBER
TOTAL_EXTENTS NUMBER
TOTAL_BLOCKS NUMBER
USED_EXTENTS NUMBER
USED_BLOCKS NUMBER
FREE_EXTENTS NUMBER
FREE_BLOCKS NUMBER
ADDED_EXTENTS NUMBER
EXTENT_HITS NUMBER
FREED_EXTENTS NUMBER
FREE_REQUESTS NUMBER
MAX_SIZE NUMBER
MAX_BLOCKS NUMBER
MAX_USED_SIZE NUMBER
MAX_USED_BLOCKS NUMBER
MAX_SORT_SIZE NUMBER
MAX_SORT_BLOCKS NUMBER
RELATIVE_FNO NUMBER
SQL>
SQL>
SQL>
SQL> desc v$tempfile
Name Null? Type
----------------------------------------- -------- ----------------------------
FILE# NUMBER
CREATION_CHANGE# NUMBER
CREATION_TIME DATE
TS# NUMBER
RFILE# NUMBER
STATUS VARCHAR2(7)
ENABLED VARCHAR2(10)
BYTES NUMBER
BLOCKS NUMBER
CREATE_BYTES NUMBER
BLOCK_SIZE NUMBER
NAME VARCHAR2(257)
SQL>
SQL>
SQL>
SQL> desc dba_tablespaces
ERROR:
ORA-00604: error occurred at recursive SQL level 1
ORA-01652: unable to extend temp segment by 128 in tablespace PSAPTEMP
SQL>
SQL>
SQL>
SQL> desc v$tablespace
Name Null? Type
----------------------------------------- -------- ----------------------------
TS# NUMBER
NAME VARCHAR2(30)
INCLUDED_IN_DATABASE_BACKUP VARCHAR2(3)
BIGFILE VARCHAR2(3)
FLASHBACK_ON VARCHAR2(3)
ENCRYPT_IN_BACKUP VARCHAR2(3)
SQL>
SQL>
SQL>
SQL>
SQL> select comp_id, comp_name,version, status from dba_registry;
select comp_id, comp_name,version, status from dba_registry
*
ERROR at line 1:
ORA-04063: view "SYS.DBA_REGISTRY" has errors
SQL>
SQL>
SQL>
SQL> desc dba_registry
ERROR:
ORA-24372: invalid object for describe
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL> @$ORACLE_HOME/rdbms/admin/catalog.sql
DOC>######################################################################
DOC>######################################################################
DOC> The following statement will cause an "ORA-01722: invalid number"
DOC> error and terminate the SQLPLUS session if the user is not SYS.
DOC> Disconnect and reconnect with AS SYSDBA.
DOC>######################################################################
DOC>######################################################################
DOC>#
no rows selected
Session altered.
TIMESTAMP
------------------------------------------------------------
COMP_TIMESTAMP CATALG_BGN 2020-05-05 15:54:55 2458965 57295
Package created.
Package body created.
Grant succeeded.
Package created.
Synonym created.
Grant succeeded.
Table created.
Table created.
Table created.
Table created.
Table created.
Table created.
Table created.
Table created.
Package created.
CREATE OR REPLACE PACKAGE BODY dbms_registry
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01652: unable to extend temp segment by 128 in tablespace PSAPTEMP
Package created.
BEGIN
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01652: unable to extend temp segment by 128 in tablespace PSAPTEMP
ORA-06508: PL/SQL: could not find program unit being called:
"SYS.DBMS_REGISTRY"
ORA-06512: at line 2