eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

DBA Data [Home] [Help] [Dependency Information]

VIEW: SYS.ALL_INDEXES

Object Details

Object Name:	ALL_INDEXES
Object Type:	VIEW
Owner:	SYS
Subobject Name:
Status:	VALID

Descriptions of indexes on tables accessible to the user

[View Source]

Columns

Name	Datatype	Length	Mandatory	Comments
OWNER	VARCHAR2	(128)	Yes	Username of the owner of the index
INDEX_NAME	VARCHAR2	(128)	Yes	Name of the index
INDEX_TYPE	VARCHAR2	(27)
TABLE_OWNER	VARCHAR2	(128)	Yes	Owner of the indexed object
TABLE_NAME	VARCHAR2	(128)	Yes	Name of the indexed object
TABLE_TYPE	CHAR	(5)		Type of the indexed object
UNIQUENESS	VARCHAR2	(9)		Uniqueness status of the index: "UNIQUE", "NONUNIQUE", or "BITMAP"
COMPRESSION	VARCHAR2	(13)		Compression property of the index: "ENABLED", "DISABLED", or NULL
PREFIX_LENGTH	NUMBER			Number of key columns in the prefix used for compression
TABLESPACE_NAME	VARCHAR2	(30)		Name of the tablespace containing the index
INI_TRANS	NUMBER			Initial number of transactions
MAX_TRANS	NUMBER			Maximum number of transactions
INITIAL_EXTENT	NUMBER			Size of the initial extent
NEXT_EXTENT	NUMBER			Size of secondary extents
MIN_EXTENTS	NUMBER			Minimum number of extents allowed in the segment
MAX_EXTENTS	NUMBER			Maximum number of extents allowed in the segment
PCT_INCREASE	NUMBER			Percentage increase in extent size
PCT_THRESHOLD	NUMBER			Threshold percentage of block space allowed per index entry
INCLUDE_COLUMN	NUMBER			User column-id for last column to be included in index-organized table top index
FREELISTS	NUMBER			Number of process freelists allocated in this segment
FREELIST_GROUPS	NUMBER			Number of freelist groups allocated to this segment
PCT_FREE	NUMBER			Minimum percentage of free space in a block
LOGGING	VARCHAR2	(3)		Logging attribute
BLEVEL	NUMBER			B-Tree level
LEAF_BLOCKS	NUMBER			The number of leaf blocks in the index
DISTINCT_KEYS	NUMBER			The number of distinct keys in the index
AVG_LEAF_BLOCKS_PER_KEY	NUMBER			The average number of leaf blocks per key
AVG_DATA_BLOCKS_PER_KEY	NUMBER			The average number of data blocks per key
CLUSTERING_FACTOR	NUMBER			A measurement of the amount of (dis)order of the table this index is for
STATUS	VARCHAR2	(8)		Whether the non-partitioned index is in USABLE or not
NUM_ROWS	NUMBER
SAMPLE_SIZE	NUMBER			The sample size used in analyzing this index
LAST_ANALYZED	DATE			The date of the most recent time this index was analyzed
DEGREE	VARCHAR2	(40)		The number of threads per instance for scanning the partitioned index
INSTANCES	VARCHAR2	(40)		The number of instances across which the partitioned index is to be scanned
PARTITIONED	VARCHAR2	(3)		Is this index partitioned? YES or NO
TEMPORARY	VARCHAR2	(1)		Can the current session only see data that it place in this object itself?
GENERATED	VARCHAR2	(1)		Was the name of this index system generated?
SECONDARY	VARCHAR2	(1)		Is the index object created as part of icreate for domain indexes?
BUFFER_POOL	VARCHAR2	(7)		The default buffer pool to be used for index blocks
FLASH_CACHE	VARCHAR2	(7)		The default flash cache hint to be used for index blocks
CELL_FLASH_CACHE	VARCHAR2	(7)		The default cell flash cache hint to be used for index blocks
USER_STATS	VARCHAR2	(3)		Were the statistics entered directly by the user?
DURATION	VARCHAR2	(15)		If index on temporary table, then duration is sys$session or sys$transaction else NULL
PCT_DIRECT_ACCESS	NUMBER			If index on IOT, then this is percentage of rows with Valid guess
ITYP_OWNER	VARCHAR2	(128)		If domain index, then this is the indextype owner
ITYP_NAME	VARCHAR2	(128)		If domain index, then this is the name of the associated indextype
PARAMETERS	VARCHAR2	(1000)		If domain index, then this is the parameter string
GLOBAL_STATS	VARCHAR2	(3)		Are the statistics calculated without merging underlying partitions?
DOMIDX_STATUS	VARCHAR2	(12)		Is the indextype of the domain index valid
DOMIDX_OPSTATUS	VARCHAR2	(6)		Status of the operation on the domain index
FUNCIDX_STATUS	VARCHAR2	(8)		Is the Function-based Index DISABLED or ENABLED?
JOIN_INDEX	VARCHAR2	(3)		Is this index a join index?
IOT_REDUNDANT_PKEY_ELIM	VARCHAR2	(3)		Were redundant primary key columns eliminated from iot secondary index?
DROPPED	VARCHAR2	(3)		Whether index is dropped and is in Recycle Bin
VISIBILITY	VARCHAR2	(9)		Whether the index is VISIBLE or INVISIBLE to the optimizer
DOMIDX_MANAGEMENT	VARCHAR2	(14)		If this a domain index, then whether it is system managed or user managed
SEGMENT_CREATED	VARCHAR2	(3)		Whether the index segment has been created
ORPHANED_ENTRIES	VARCHAR2	(3)
INDEXING	VARCHAR2	(7)

Query Text

Cut, paste (and edit) the following text to query this object:


SELECT OWNER
,      INDEX_NAME
,      INDEX_TYPE
,      TABLE_OWNER
,      TABLE_NAME
,      TABLE_TYPE
,      UNIQUENESS
,      COMPRESSION
,      PREFIX_LENGTH
,      TABLESPACE_NAME
,      INI_TRANS
,      MAX_TRANS
,      INITIAL_EXTENT
,      NEXT_EXTENT
,      MIN_EXTENTS
,      MAX_EXTENTS
,      PCT_INCREASE
,      PCT_THRESHOLD
,      INCLUDE_COLUMN
,      FREELISTS
,      FREELIST_GROUPS
,      PCT_FREE
,      LOGGING
,      BLEVEL
,      LEAF_BLOCKS
,      DISTINCT_KEYS
,      AVG_LEAF_BLOCKS_PER_KEY
,      AVG_DATA_BLOCKS_PER_KEY
,      CLUSTERING_FACTOR
,      STATUS
,      NUM_ROWS
,      SAMPLE_SIZE
,      LAST_ANALYZED
,      DEGREE
,      INSTANCES
,      PARTITIONED
,      TEMPORARY
,      GENERATED
,      SECONDARY
,      BUFFER_POOL
,      FLASH_CACHE
,      CELL_FLASH_CACHE
,      USER_STATS
,      DURATION
,      PCT_DIRECT_ACCESS
,      ITYP_OWNER
,      ITYP_NAME
,      PARAMETERS
,      GLOBAL_STATS
,      DOMIDX_STATUS
,      DOMIDX_OPSTATUS
,      FUNCIDX_STATUS
,      JOIN_INDEX
,      IOT_REDUNDANT_PKEY_ELIM
,      DROPPED
,      VISIBILITY
,      DOMIDX_MANAGEMENT
,      SEGMENT_CREATED
,      ORPHANED_ENTRIES
,      INDEXING
FROM SYS.ALL_INDEXES;

Dependencies

[top of page]

SYS.ALL_INDEXES references the following:

SYS: DEFERRED_STG$; IND$; OBJ$; OBJAUTH$; SEG$; TS$; USER$

SYS.ALL_INDEXES is referenced by following:

APPS: BEN_DM_CREATE_CONTROL_FILES - show dependent code; HZ_ELOCATION_PKG - show dependent code; HZ_PARTY_STAGE - show dependent code; HZ_PARTY_STAGE_SHADOW - show dependent code; RRS_ELOCATION_PKG - show dependent code
PUBLIC: ALL_INDEXES
SYS: DBMSOBJG_DP - show dependent code; DBMS_COMPARISON - show dependent code; DBMS_DBFS_SFS - show dependent code; DMP_SEC - show dependent code; PRVT_COMPRESSION - show dependent code; PRVT_PARTREC_NOPRIV - show dependent code