[Update 2009-11] The very useful views v$compatseg and v$compatibility disappeared after 9iR2.[see Appendix for its content] They're useful because that's the only place where Oracle documents what features are available for which setting of the initialization parameter compatible, unlike optimizer_features_enable whose features at each level are documented in the Reference, and semi-documented in v$sql_feature and v$sql_feature_hierarchy in 11g. Even the underlined x$ tables of the views disappeared too. There's one more layer of digging we can do to see if the information is truly eliminated from 10g and up. The compatibility information disappears together with the disappearance of the cache segment (select * from dba_segments where segment_type='CACHE'). If you dump the cache segment in 9iR2 or earlier, you'll see the same information as shown in v$compatseg and v$compatibility. Now, how do we find the location in 10g and up where the cache segment would be? In 9i, the cache segment name such as 1.285 has a dba that is the same as "root dba". The root dba can be obtained by dumping file header for file #1 (with event file_hdrs), or from x$kcvfh.fhrdb in decimal[note1]. So find the segment the block belongs to. At last, unfortunately, the segment is no longer the cache segment which definitely does not exist; instead it is bootstrap$, which has a bunch of SQLs that create the basic database "building blocks". See [note2]. [note1] http://dbsnake.com/2009/05/10g-x-kcvfh-continue-2.html [note2] http://www.itpub.net/viewthread.php?tid=199099 Old note: Hi, all, Periodically we have space alert on sys.301.499 of the prod database. Aaron asked me to investigate whether we need to do anything about it. This 301.499 is a CACHE type segment at file# 301 block# 499 (by the way, one other type of segment named by file#.block# is temporary segments). Metalink note 18926.997 says: ********** begin quote ********** I have an automated check on segments that cannot extend, and in all my systems it picks up the cache segment: Segements that won't be able to extend due to max/min extents OWNER SEGNAME SEGTYPE EXTENTS MAX_EXTENTS MIN_EXTENTS MBYTES TABLESPACE_NAME ----- ------- ------- ------- ----------- ----------- --------- --------------- SYS 1.285 CACHE 1 0 0 .00390625 SYSTEM Another thread in the forum indicated that this segment is used for sorting. If so, can I increase the size for performance? And should I be concerned that it is only one extent, or that it is defined with max_extents of zero? -------------------------------------------------------------------------------- From: Oracle, COBY ADAMS 07-Nov-00 14:31 Subject: Re : cache segment Steve, Do not do anything to this segment. Please refer to the note below: [snipped] When the database is created, a segment of type 'CACHE' is created. It is owned by 'SYS' and resides in the system tablespace. It can been found by querying DBA_SEGMENTS or USER_SEGMENTS for a segment_type of 'CACHE' and has one extent allocated but MAX_EXTENTS = 0. [snipped] ********** end quote ********** I believe that answers the question. So we can safely ignore the space alert on it. If you're still interested in this segment type, Steve Adams' Web site is probably the only reference we can find outside of Oracle's Web site: http://www.ixora.com.au/q+a/dd.htm: CACHE segment 22 July 1999 I was just curious about the segment types in Oracle (7.3.4), and found that there is a CACHE segment called 1.377. What is this CACHE segment all about? In the old days this segment was used to bootstrap the data dictionary cache - hence the name. However, that job is now done with BOOTSTRAP$, so the only thing left in the CACHE segment is the compatibility information. That is, the list of features in use in the database that might prevent the database being opened with an earlier release of Oracle. The name of the CACHE segment, as for temporary segments, is derived from the file and block numbers of its segment header block. If even that does not satisfy your curiosity, I dumped block 499 at file 301 of pifa on tpn into the file tpn:/oracle/trace/pifa/udump/pifa_ora_10501.trc. The database pifa is a partial clone of prod on hera. It contains the same CACHE segment at the same location. The trace file indicates that the segment contains database compatibility information, as Steve Adams says. Yong Huang Appendix: Database compatibility features, from 9.2.0.6 SQL> select * from v$compatseg order by 2, 1; TYPE_ID RELEASE UPDATED -------- --------- ---------------- BOOTSTRP 8.0.0.0.0 9.2.0.0.0 COMPATSG 8.0.0.0.0 9.2.0.0.0 APPCTX 8.1.0.0.0 9.2.0.0.0 ASSOC 8.1.0.0.0 9.2.0.0.0 DATETIME 8.1.0.0.0 9.2.0.0.0 DOMIND 8.1.0.0.0 9.2.0.0.0 FGACCESS 8.1.0.0.0 9.2.0.0.0 FUNCIND 8.1.0.0.0 9.2.0.0.0 INDONIOT 8.1.0.0.0 9.2.0.0.0 INDXTYP 8.1.0.0.0 9.2.0.0.0 JAVA 8.1.0.0.0 9.2.0.0.0 KCMPRIND 8.1.0.0.0 9.2.0.0.0 KCMPRIOT 8.1.0.0.0 9.2.0.0.0 LOCMGDTS 8.1.0.0.0 9.2.0.0.0 NWIMGFMT 8.1.0.0.0 9.2.0.0.0 OPERATOR 8.1.0.0.0 9.2.0.0.0 QUETABLE 8.1.0.0.0 9.2.0.0.0 SEVTRIG 8.1.0.0.0 9.2.0.0.0 TEMPTAB 8.1.0.0.0 9.2.0.0.0 EDTRIG 8.1.6.0.0 9.2.0.0.0 FASTDROP 8.1.6.0.0 9.2.0.0.0 ANYTABLE 9.0.0.0.0 9.2.0.0.0 APPFGA 9.0.0.0.0 9.2.0.0.0 FDOMIND 9.0.0.0.0 9.2.0.0.0 LGINDKEY 9.0.0.0.0 9.2.0.0.0 MLCTABLE 9.0.0.0.0 9.2.0.0.0 NFSTABLE 9.0.0.0.0 9.2.0.0.0 PDMLITLS 9.0.0.0.0 9.2.0.0.0 UNDOTBSP 9.0.0.0.0 9.2.0.0.0 WRDIR 9.0.0.0.0 9.2.0.0.0 PGTMGDTS 9.0.1.3.0 9.2.0.0.0 LMST 9.2.0.0.0 9.2.0.0.0 LOB_RET 9.2.0.0.0 9.2.0.0.0 PGTMGDLB 9.2.0.0.0 9.2.0.0.0 RLENG 9.2.0.0.0 9.2.0.0.0 UNDODATA 9.2.0.0.0 9.2.0.0.0 XMLSBSTR 9.2.0.0.0 9.2.0.0.0 37 rows selected. SQL> select * from v$compatibility order by 2, 1; TYPE_ID RELEASE DESCRIPTION -------- --------- ----------------------------------------- ALTERFRL 0.0.0.0.0 Alter freelists APPROLE 0.0.0.0.0 Application Role BMINVPRT 0.0.0.0.0 Bitmap Index Invalidation Protection CARELOB 0.0.0.0.0 Cache Reads LOB COLLFN 0.0.0.0.0 Collection Image Fixed Number COLLOCT 0.0.0.0.0 Ord Colls in Tables COLLSTO 0.0.0.0.0 Collection Storage CPTLEN 0.0.0.0.0 Code PoinT length DEFPART 0.0.0.0.0 9.2 DEFAULT Partition DIMENSIO 0.0.0.0.0 Dimension DISVAL 0.0.0.0.0 Disable Validate DOMINDEA 0.0.0.0.0 Domain Index on Embedded ADT DOMINDRM 0.0.0.0.0 Domain Index with Row Movement DOMINIOT 0.0.0.0.0 Domain Index on IOT DROPCOL 0.0.0.0.0 Drop Column EJTYPE 0.0.0.0.0 External Java Type ENCRPROF 0.0.0.0.0 Encryption Profiles EXTTAB 0.0.0.0.0 Create External Tables FAKEIND 0.0.0.0.0 Fake Index FGASYNPL 0.0.0.0.0 Fine-grained Security Synonym Policy FORCEVSN 0.0.0.0.0 Force undo/redo versions to exist HASHPIOT 0.0.0.0.0 Hash Partitioned IOT HSC 0.0.0.0.0 Heap segment block compression INDCOAL 0.0.0.0.0 Index Segment Coalesce IOTBULOG 0.0.0.0.0 IOT Batch Update Logging IOTCVLOG 0.0.0.0.0 IOT Column Vector Logging IOTWLOB 0.0.0.0.0 IOT with Lob or Array column IOTWMAP 0.0.0.0.0 IOT with Map table JOININD 0.0.0.0.0 Join Index KNL92 0.0.0.0.0 9.2 Streams LDOMIND 0.0.0.0.0 Local Domain Index LGMR_F 0.0.0.0.0 Full LogMiner Info LOCKING 0.0.0.0.0 Instance locking protocol LOGSTDBY 0.0.0.0.0 Logical Standby MULTBZ 0.0.0.0.0 Multiple Block Sizes MV81 0.0.0.0.0 8.1 Materialized Views MV90 0.0.0.0.0 9.0 Materialized Views MV92 0.0.0.0.0 9.2 Materialized Views NESTEDTX 0.0.0.0.0 redo/undo for nested txn NEWANY 0.0.0.0.0 AnyData NEWPARTM 0.0.0.0.0 8.1 Partitioning Methods NTIERAUT 0.0.0.0.0 N-Tier Authentication NTTRIG 0.0.0.0.0 NT Instead-of Trigger OBOUTBND 0.0.0.0.0 Object Out Bind ONLINDCR 0.0.0.0.0 Online Index Segment Creation OPQTYPE 0.0.0.0.0 Opaque Type OUTLN 0.0.0.0.0 Stored Outlines PARTIOT 0.0.0.0.0 Partitioned IOT PARTM81 0.0.0.0.0 8.1 Partitioning Methods PARTM82 0.0.0.0.0 9.0 Partitioning Methods PARTMCLS 0.0.0.0.0 9.2 Partitioning Method PARTOBJ 0.0.0.0.0 Partitioned Objects / LOBs PIOTLOBS 0.0.0.0.0 Partitioned IOT with Lobs PLUGTS 0.0.0.0.0 Plugged in Tablespaces PROPAGAT 0.0.0.0.0 Advanced Queueing propagation RDONLYTS 0.0.0.0.0 Read only tablespace REFCREFC 0.0.0.0.0 Referential Constraint on REF col REPAIR 0.0.0.0.0 REPAIR ROWDEP 0.0.0.0.0 Row Level Dependencies RSRCMGR 0.0.0.0.0 Resource Manager SNTABCLU 0.0.0.0.0 Single Table Hash Cluster SPTEMPL 0.0.0.0.0 9.2 Subpartition Template SUMMARY 0.0.0.0.0 Summary SYNUDC 0.0.0.0.0 Type Synonyms or User Defined Constructors TBSMIGTN 0.0.0.0.0 Tablespace Migration TBSTRNSG 0.0.0.0.0 Transient Segments TYPEVL 0.0.0.0.0 Type Evolution UNVRSRWD 0.0.0.0.0 Universal ROWID URIDIND 0.0.0.0.0 Index on UROWID USEROID 0.0.0.0.0 User-defined OID VWCONSTR 0.0.0.0.0 View Constraint VYCLOB 0.0.0.0.0 Varying width CLOB BOOTSTRP 8.0.0.0.0 bootstrap$ COMPATSG 8.0.0.0.0 Compatibility segment CONTROLF 8.0.0.0.0 Control file DATAFILE 8.0.0.0.0 Data file structure LOGSTRUC 8.0.0.0.0 Log structure RCVREDO 8.0.0.0.0 Recovery redo TXNAUDIT 8.0.5.0.0 redo for transaction auditing APPCTX 8.1.0.0.0 Application Context ASSOC 8.1.0.0.0 User Defined Associate Statistics BLTRAPPL 8.1.0.0.0 block level transaction recovery application DATETIME 8.1.0.0.0 DateTime DOMIND 8.1.0.0.0 Domain Index FGACCESS 8.1.0.0.0 Fine-grained Access Control FUNCIND 8.1.0.0.0 Functional Index INDONIOT 8.1.0.0.0 Index on IOT INDXTYP 8.1.0.0.0 Indextype JAVA 8.1.0.0.0 Java KCMPRIND 8.1.0.0.0 Key compressed Index KCMPRIOT 8.1.0.0.0 Key compressed IOT LOCMGDTS 8.1.0.0.0 locally managed tablespaces NWIMGFMT 8.1.0.0.0 New Image Format OPERATOR 8.1.0.0.0 Operator PROXY 8.1.0.0.0 Proxy Copy QUETABLE 8.1.0.0.0 Advanced Queueing queue tables SEVTRIG 8.1.0.0.0 System Event Triggers TEMPTAB 8.1.0.0.0 Temporary Table EDTRIG 8.1.6.0.0 Enhanced DDL/DML Triggers FASTDROP 8.1.6.0.0 Faster segment drop ANYTABLE 9.0.0.0.0 XMLType/AnyType/AnyData in Tables APPFGA 9.0.0.0.0 Fine Grained Auditing FDOMIND 9.0.0.0.0 Function-Based Domain Index LGINDKEY 9.0.0.0.0 Large Index Key LGMR_B 9.0.0.0.0 Basic LogMiner Info MLCTABLE 9.0.0.0.0 Multi level collection in Tables NFSTABLE 9.0.0.0.0 Not Final type or Subtype in Tables PDMLITLS 9.0.0.0.0 PDML ITL invariant STAUTOFM 9.0.0.0.0 standby auto file management TXNAUDN 9.0.0.0.0 redo for transaction name auditing UNDOTBSP 9.0.0.0.0 undo tablespaces WRDIR 9.0.0.0.0 Directory Write Privilege PGTMGDTS 9.0.1.3.0 Auto segment space managed tablespaces BACKUP 9.2.0.0.0 Backup set LMST 9.2.0.0.0 Locally Managed SYSTEM tablespace LOB_RET 9.2.0.0.0 LOB Retention PGTMGDLB 9.2.0.0.0 Pagetable managed LOB REDODATA 9.2.0.0.0 Redo contents RLENG 9.2.0.0.0 Rules Engine UNDODATA 9.2.0.0.0 Undo contents XMLSBSTR 9.2.0.0.0 XMLSchema based XMLType storage 121 rows selected.