Monday, August 25, 2008

DBA_INDEXES

Oracle 11gR1

DBA_INDEXES describes all indexes in the database. To gather statistics for this view, use the SQL ANALYZE statement. This view supports parallel partitioned index scans. Its columns are the same as those in ALL_INDEXES.

Related Views

· ALL_INDEXES describes the indexes on the tables accessible to the current user. To gather statistics for this view and the related views DBA_INDEXES and USER_INDEXES, use the SQL ANALYZE statement.

· USER_INDEXES describes the indexes owned by the current user. This view does not display the OWNER column.

Note:

Column names followed by an asterisk are populated only if you collect statistics on the index using the ANALYZE statement or the DBMS_STATS package.

Column

Datatype

NULL

Description

OWNER

VARCHAR2(30)

NOT NULL

Owner of the index

INDEX_NAME

VARCHAR2(30)

NOT NULL

Name of the index

INDEX_TYPE

VARCHAR2(27)


Type of the index:

· NORMAL

· BITMAP

· FUNCTION-BASED NORMAL

· FUNCTION-BASED BITMAP

· DOMAIN

TABLE_OWNER

VARCHAR2(30)

NOT NULL

Owner of the indexed object

TABLE_NAME

VARCHAR2(30)

NOT NULL

Name of the indexed object

TABLE_TYPE

CHAR(5)


Type of the indexed object (for example, TABLE, CLUSTER)

UNIQUENESS

VARCHAR2(9)


Indicates whether the index is UNIQUE or NONUNIQUE

COMPRESSION

VARCHAR2(8)


Indicates whether index compression is enabled (ENABLED) or not (DISABLED)

PREFIX_LENGTH

NUMBER


Number of columns in the prefix of the compression key

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


Column ID of the last column to be included in index-organized table primary key (non-overflow) index. This column maps to the COLUMN_ID column of the *_TAB_COLUMNS data dictionary views.

FREELISTS

NUMBER


Number of process freelists allocated to 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 information

BLEVEL*

NUMBER


B*-Tree level: depth of the index from its root block to its leaf blocks. A depth of 0 indicates that the root block and leaf block are the same.

LEAF_BLOCKS*

NUMBER


Number of leaf blocks in the index

DISTINCT_KEYS*

NUMBER


Number of distinct indexed values. For indexes that enforce UNIQUE and PRIMARY KEY constraints, this value is the same as the number of rows in the table (USER_TABLES.NUM_ROWS)

AVG_LEAF_BLOCKS_PER_KEY*

NUMBER


Average number of leaf blocks in which each distinct value in the index appears, rounded to the nearest integer. For indexes that enforce UNIQUE and PRIMARY KEY constraints, this value is always 1.

AVG_DATA_BLOCKS_PER_KEY*

NUMBER


Average number of data blocks in the table that are pointed to by a distinct value in the index rounded to the nearest integer. This statistic is the average number of data blocks that contain rows that contain a given value for the indexed columns.

CLUSTERING_FACTOR*

NUMBER


Indicates the amount of order of the rows in the table based on the values of the index.

· If the value is near the number of blocks, then the table is very well ordered. In this case, the index entries in a single leaf block tend to point to rows in the same data blocks.

· If the value is near the number of rows, then the table is very randomly ordered. In this case, it is unlikely that index entries in the same leaf block point to rows in the same data blocks.

For bitmap indexes, this column is not applicable and is not used.

STATUS

VARCHAR2(8)


Indicates whether a nonpartitioned index is VALID or UNUSABLE

NUM_ROWS

NUMBER


Number of rows in the index

SAMPLE_SIZE

NUMBER


Size of the sample used to analyze the index

LAST_ANALYZED

DATE


Date on which this index was most recently analyzed

DEGREE

VARCHAR2(40)


Number of threads per instance for scanning the index

INSTANCES

VARCHAR2(40)


Number of instances across which the indexes to be scanned

PARTITIONED

VARCHAR2(3)


Indicates whether the index is partitioned (YES) or not (NO)

TEMPORARY

VARCHAR2(1)


Indicates whether the index is on a temporary table

GENERATED

VARCHAR2(1)


Indicates whether the name of the index is system generated (Y) or not (N)

SECONDARY

VARCHAR2(1)


Indicates whether the index is a secondary object created by the ODCIIndexCreate method of the Oracle Data Cartridge (Y) or not (N)

BUFFER_POOL

VARCHAR2(7)


Name of the default buffer pool to be used for the index blocks

USER_STATS

VARCHAR2(3)


Indicates whether statistics were entered directly by the user (YES) or not (NO)

DURATION

VARCHAR2(15)


Indicates the duration of a temporary table:

· SYS$SESSION - Rows are preserved for the duration of the session

· SYS$TRANSACTION - Rows are deleted after COMMIT

Null for a permanent table

PCT_DIRECT_ACCESS

NUMBER


For a secondary index on an index-organized table, the percentage of rows with VALID guess

ITYP_OWNER

VARCHAR2(30)


For a domain index, the owner of the indextype

ITYP_NAME

VARCHAR2(30)


For a domain index, the name of the indextype

PARAMETERS

VARCHAR2(1000)


For a domain index, the parameter string

GLOBAL_STATS

VARCHAR2(3)


For partitioned indexes, indicates whether statistics were collected by analyzing the index as a whole (YES) or were estimated from statistics on underlying index partitions and subpartitions (NO)

DOMIDX_STATUS

VARCHAR2(12)


Status of the domain index:

· NULL - Index is not a domain index

· VALID - Index is a valid domain index

· IDXTYP_INVLD - Indextype of the domain index is invalid

DOMIDX_OPSTATUS

VARCHAR2(6)


Status of the operation on the domain index:

· NULL - Index is not a domain index

· VALID - Operation performed without errors

· FAILED - Operation failed with an error

FUNCIDX_STATUS

VARCHAR2(8)


Status of a function-based index:

· NULL - Index is not a function-based index

· ENABLED - Function-based index is enabled

· DISABLED - Function-based index is disabled

JOIN_INDEX

VARCHAR2(3)


Indicates whether the index is a join index (YES) or not (NO)

IOT_REDUNDANT_PKEY_ELIM

VARCHAR2(3)


Indicates whether redundant primary key columns are eliminated from secondary indexes on index-organized tables (YES) or not (NO)

DROPPED

VARCHAR2(3)


Indicates whether the index has been dropped and is in the recycle bin (YES) or not (NO); NULL for partitioned tables

VISIBILITY

VARCHAR2(10)


Whether the index is VISIBLE or INVISIBLE to the Optimizer

DOMIDX_MANAGEMENT

VARCHAR2(14)


If this is a domain index, then whether it is system-managed or user-managed

Note:

1.

select owner, index_name, index_type, table_name, uniqueness, compression, logging, status, last_analyzed, ityp_name

from DBA_INDEXES;

OWNER

INDEX_

NAME

INDEX_

TYPE

TABLE_

NAME

UNIQUENESS

COMPRESSION

LOGGING

STATUS

LAST_

ANALYZED

ITYP_

NAME

USERA

POLY_IDX

DOMAIN

POLY_MVW

NONUNIQUE

DISABLED

YES

VALID

2008-07-03 9:59:09 AM

SPATIAL_INDEX

USERA

LN_IDX

DOMAIN

LINE_MVW

NONUNIQUE

DISABLED

YES

VALID

2008-07-03 9:59:16 AM

SPATIAL_INDEX

USERA

PT_IDX

DOMAIN

POINT_MVW

NONUNIQUE

DISABLED

YES

VALID

2008-07-03 9:59:18 AM

SPATIAL_INDEX

USERA

POLY_IDX

DOMAIN

POLY_MVW

NONUNIQUE

DISABLED

YES

VALID

2008-07-03 9:59:22 AM

SPATIAL_INDEX

USERB

SI_I

NORMAL

COMP_LAYER

NONUNIQUE

DISABLED

YES

VALID

2008-01-25 3:13:33 PM


USERB

PA1_I

NORMAL

COMP_LAYER

NONUNIQUE

DISABLED

YES

VALID

2008-01-25 3:13:16 PM


Oracle data dictionary views

DBA_SYNONYMS

Oracle 11gR1
DBA_SYNONYMS describes all synonyms in the database. Its columns are the same as those in ALL_SYNONYMS.
ALL_SYNONYMS describes the synonyms accessible to the current user. The following criteria determine the list of synonyms that ALL_SYNONYMS shows:
· All private synonyms owned by the logged-in user, even if the base object pointed to is not accessible.
· All public synonyms, even if the base object pointed to is not accessible.
· All private synonyms owned by a different user, where the ultimate base object pointed to by that synonym or by any chain of nested synonyms, is know to be accessible because of a grant to the logged-in user, or a grant to a role in effect for this session.
· If the current session has any of the following privileges, then all synonyms that point directly to local objects are shown because it is assumed that the session can access those objects:
o LOCK ANY TABLE
o SELECT ANY TABLE
o INSERT ANY TABLE
o UPDATE ANY TABLE
o DELETE ANY TABLE
Synonyms that point to remote objects are excluded because the system privileges just listed do not automatically convey access to those remote objects. Also, if the synonyms point to objects other than tables and views (such as sequences, PL/SQL procedures, and so on) then this rule may show synonyms that ultimately resolve to objects that this session cannot access.
· All private synonyms owned by a different user, where the synonym is via a database link, are excluded.
Related Views
· DBA_SYNONYMS describes all synonyms in the database.
· USER_SYNONYMS describes the synonyms owned by the current user. The USER_SYNONYMS view does not display the OWNER column.
Column
Datatype
NULL
Description
OWNER
VARCHAR2(30)
NOT NULL
Owner of the synonym
SYNONYM_NAME
VARCHAR2(30)
NOT NULL
Name of the synonym
TABLE_OWNER
VARCHAR2(30)

Owner of the object referenced by the synonym, or creator of the referring synonym if the target is a public synonym (that is, the object referred to by TABLE_NAME).
Although the column is called TABLE_OWNER, the object owned is not necessarily a table. It can be any general object such as a view, sequence, stored procedure, synonym, and so on.
TABLE_NAME
VARCHAR2(30)
NOT NULL
Name of the object referenced by the synonym. Although the column is called TABLE_NAME, the object does not necessarily have to be a table. It can be any general object such as a view, sequence, stored procedure, synonym, and so on.
DB_LINK
VARCHAR2(128)

Name of the database link referenced, if any
Note:
1.
select * from DBA_SYNONYMS;
OWNER
SYNONYM_NAME
TABLE_OWNER
TABLE_NAME
DB_LINK
PUBLIC
DUAL
SYS
DUAL

PUBLIC
SYSTEM_PRIVILEGE_MAP
SYS
SYSTEM_PRIVILEGE_MAP

PUBLIC
DBMS_STANDARD
SYS
DBMS_STANDARD

PUBLIC
V$LOG
SYS
V_$LOG

PUBLIC
V$SGA
SYS
V_$SGA

2. A synonym is an alias for any table, view, materialized view, sequence, procedure, function, package, type, Java class schema object, user-defined object type, or another synonym. Because a synonym is simply an alias, it requires no storage other than its definition in the data dictionary.
You can create both public and private synonyms. A public synonym is owned by the special user group named PUBLIC and every user in a database can access it. A private synonym is in the schema of a specific user who has control over its availability to others.

Oracle data dictionary views


More Oracle DBA tips, please visit Oracle DBA Tips