ORACLE 对象查询-索引

查看索引个数和类别

select index_name,index_type,table_name from user_indexes order by table_name;

查看索引被索引的字段

select * from user_ind_columns where index_name=upper('&index_name');

查看索引的大小

select sum(bytes)/(1024*1024) as "size(M)" from user_segments where segment_name=upper('&index_name');

 

SQL> desc user_indexes ;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 INDEX_NAME                                NOT NULL VARCHAR2(30)
 INDEX_TYPE                                         VARCHAR2(27)
 TABLE_OWNER                               NOT NULL VARCHAR2(30)
 TABLE_NAME                                NOT NULL VARCHAR2(30)
 TABLE_TYPE                                         VARCHAR2(11)
 UNIQUENESS                                         VARCHAR2(9)
 COMPRESSION                                        VARCHAR2(8)
 PREFIX_LENGTH                                      NUMBER
 TABLESPACE_NAME                                    VARCHAR2(30)
 INI_TRANS                                          NUMBER
 MAX_TRANS                                          NUMBER
 INITIAL_EXTENT                                     NUMBER
 NEXT_EXTENT                                        NUMBER
 MIN_EXTENTS                                        NUMBER
 MAX_EXTENTS                                        NUMBER
 PCT_INCREASE                                       NUMBER
 PCT_THRESHOLD                                      NUMBER
 INCLUDE_COLUMN                                     NUMBER
 FREELISTS                                          NUMBER
 FREELIST_GROUPS                                    NUMBER
 PCT_FREE                                           NUMBER
 LOGGING                                            VARCHAR2(3)
 BLEVEL                                             NUMBER
 LEAF_BLOCKS                                        NUMBER
 DISTINCT_KEYS                                      NUMBER
 AVG_LEAF_BLOCKS_PER_KEY                            NUMBER
 AVG_DATA_BLOCKS_PER_KEY                            NUMBER
 CLUSTERING_FACTOR                                  NUMBER
 STATUS                                             VARCHAR2(8)
 NUM_ROWS                                           NUMBER
 SAMPLE_SIZE                                        NUMBER
 LAST_ANALYZED                                      DATE
 DEGREE                                             VARCHAR2(40)
 INSTANCES                                          VARCHAR2(40)
 PARTITIONED                                        VARCHAR2(3)
 TEMPORARY                                          VARCHAR2(1)
 GENERATED                                          VARCHAR2(1)
 SECONDARY                                          VARCHAR2(1)
 BUFFER_POOL                                        VARCHAR2(7)
 USER_STATS                                         VARCHAR2(3)
 DURATION                                           VARCHAR2(15)
 PCT_DIRECT_ACCESS                                  NUMBER
 ITYP_OWNER                                         VARCHAR2(30)
 ITYP_NAME                                          VARCHAR2(30)
 PARAMETERS                                         VARCHAR2(1000)
 GLOBAL_STATS                                       VARCHAR2(3)
 DOMIDX_STATUS                                      VARCHAR2(12)
 DOMIDX_OPSTATUS                                    VARCHAR2(6)
 FUNCIDX_STATUS                                     VARCHAR2(8)
 JOIN_INDEX                                         VARCHAR2(3)

SQL> select INDEX_NAME||','||TABLESPACE_NAME from user_indexes where table_name='ACCOUNT_T';
ACCOUNT_COUNT,PIN00
I_ACCOUNT_ACTGNEXTT__ID,PINX01
I_ACCOUNT_NO__ID,PINX01
I_ACCOUNT_NO__PACKAGE,PINX01
I_ACCOUNT__ID,PINX00
I_ACCOUNT__NAME,PINX00
I_ACCOUNT__SERIAL,PINX01

获取索引DDL

SQL>SET SERVEROUTPUT ON
SQL>SET LINESIZE 1000
SQL>SET FEEDBACK OFF
SQL>SET LONG 999999
SQL>SET PAGESIZE 1000 SQL>SELECT DBMS_METADATA.GET_DDL('INDEX','IDX_OBJECT_NAME') FROM DUAL;