ORA-04043: object x$bh does not exist


SQL> conn rajat/rajat;
Connected.
SQL> desc v$bh;
Name Null? Type


FILE# NUMBER
BLOCK# NUMBER
CLASS# NUMBER
STATUS VARCHAR2(10)
XNC NUMBER
FORCED_READS NUMBER
FORCED_WRITES NUMBER
LOCK_ELEMENT_ADDR RAW(8)
LOCK_ELEMENT_NAME NUMBER
LOCK_ELEMENT_CLASS NUMBER
DIRTY VARCHAR2(1)
TEMP VARCHAR2(1)
PING VARCHAR2(1)
STALE VARCHAR2(1)
DIRECT VARCHAR2(1)
NEW CHAR(1)
OBJD NUMBER
TS# NUMBER

SQL> desc x$bh;
ERROR:
ORA-04043: object x$bh does not exist

solution: It is there in the SYS schema. It is just no longer exposed. If you want to expose it, you can do it like this…

  1. WARNING _ THIS IS NOT SUPPORTED BY ORACLE – you are completely on your own here.
  2. Login as SYS
  3. Execute the following
  • create view x_$bh as select * from x$bh;
  • create public synonym x$bh for x_$bh;
  • grant select on x_$bh to <some dba-only role>;

SQL> conn / as sysdba
Connected.
SQL> create view x_$bh as select * from x$bh;

View created.

SQL> create public synonym x$bh for x_$bh;

Synonym created.

SQL> grant select on x_$bh to rajat;

Grant succeeded.

SQL> conn rajat/rajat;
Connected.
SQL> desc x$bh;
Name Null? Type
—————————————– ——– —————————-
ADDR RAW(8)
INDX NUMBER
INST_ID NUMBER
HLADDR RAW(8)
BLSIZ NUMBER
NXT_HASH RAW(8)
PRV_HASH RAW(8)
NXT_REPL RAW(8)
PRV_REPL RAW(8)
FLAG NUMBER
FLAG2 NUMBER
LOBID NUMBER
RFLAG NUMBER
SFLAG NUMBER
LRU_FLAG NUMBER
TS# NUMBER
FILE# NUMBER
DBARFIL NUMBER
DBABLK NUMBER
CLASS NUMBER
STATE NUMBER
MODE_HELD NUMBER
CHANGES NUMBER
CSTATE NUMBER
LE_ADDR RAW(8)
DIRTY_QUEUE NUMBER
SET_DS RAW(8)
OBJ NUMBER
BA RAW(8)
CR_SCN_BAS NUMBER
CR_SCN_WRP NUMBER
CR_XID_USN NUMBER
CR_XID_SLT NUMBER
CR_XID_SQN NUMBER
CR_UBA_FIL NUMBER
CR_UBA_BLK NUMBER
CR_UBA_SEQ NUMBER
CR_UBA_REC NUMBER
CR_SFL NUMBER
CR_CLS_BAS NUMBER
CR_CLS_WRP NUMBER
LRBA_SEQ NUMBER
LRBA_BNO NUMBER
HSCN_BAS NUMBER
HSCN_WRP NUMBER
HSUB_SCN NUMBER
US_NXT RAW(8)
US_PRV RAW(8)
WA_NXT RAW(8)
WA_PRV RAW(8)
OQ_NXT RAW(8)
OQ_PRV RAW(8)
AQ_NXT RAW(8)
AQ_PRV RAW(8)
OBJ_FLAG NUMBER
TCH NUMBER
TIM NUMBER
CR_RFCNT NUMBER
SHR_RFCNT NUMBER

Happy reading and don’t be shy to comment :)

Find size of a table and Associated segments ( index , lob )


COLUMN TABLE_NAME FORMAT A32
COLUMN OBJECT_NAME FORMAT A32
COLUMN OWNER FORMAT A10

SELECT
owner, table_name,segment_type, TRUNC(sum(bytes)/1024/1024/1024) Size_GB
FROM
(SELECT segment_name table_name, owner, bytes ,segment_type
FROM dba_segments  WHERE segment_type = ‘TABLE’
UNION ALL
SELECT i.table_name, i.owner, s.bytes ,segment_type FROM dba_indexes i, dba_segments s
WHERE s.segment_name = i.index_name AND   s.owner = i.owner
AND   s.segment_type = ‘INDEX’
UNION ALL
SELECT l.table_name, l.owner, s.bytes ,segment_type
FROM dba_lobs l, dba_segments s
WHERE s.segment_name = l.segment_name
AND   s.owner = l.owner
AND   s.segment_type = ‘LOBSEGMENT’
UNION ALL
SELECT l.table_name, l.owner, s.bytes ,segment_type
FROM dba_lobs l, dba_segments s
WHERE s.segment_name = l.index_name
AND   s.owner = l.owner
AND   s.segment_type = ‘LOBINDEX’)
WHERE owner =’&owner’
and table_name=’&table’
GROUP BY owner,table_name,segment_type
ORDER BY SUM(bytes) desc
/

Enter value for owner: SYS
old 22: WHERE owner =’&owner’
new 22: WHERE owner =’SYS’
Enter value for table: T1
old 23: and table_name=’&table’
new 23: and table_name=’T1′

Flash Recovery Area Usage

OWNER TABLE_NAME SEGMENT_TYPE SIZE_GB
———- ——————————– —————— ———-
SYS           T1                     TABLE                                1.2
SYS            T1                     INDEX                                1

Reference: http://ora10gadmin.blogspot.sg/