February 26, 2020

You can check index on a table in Oracle using DBA_INDEXES view and using dba_segments y'all can check size of alphabetize in Oracle. To detect index on a table and its size you can likewise use user_indexes and user_segment views.

Check Index on a table in Oracle and index size

Important view used in the Query 

                Useful views to get alphabetize details:                
As a DBA - if connected as a sys user:
dba_indexes;
                dba_segments;
dba_ind_columns;
dba_ind_partitions;                
                  If y'all are connected as a normal user                                    use following views:                  
user_indexes;
user_segments;
user_ind_columns;
user_ind_partitions;

Query to check Alphabetize on a Table in Oracle

Annotation: Please enter table_name and OWNER in Upper-case letter:

set lines 200 col index_name from a30 col column_name for a30  select index_name from                dba_indexes                where table_name='&TABLE_NAME' and owner='&TABLE_OWNER';  select index_name from                user_indexes                where table_name='&TABLE_NAME';  select OWNER,INDEX_NAME,STATUS,TABLE_NAME from dba_indexes where TABLE_NAME='&tablename';

Query To Check Index Column on a Table in Oracle 

prepare lines 200
col index_name from a30
col column_name for a30
select index_name, column_name from dba_ind_columns where table_name='&TABLE_NAME' and TABLE_OWNER='&OWNER';
                select index_name, column_name from user_ind_columns where table_name='&TABLE_NAME';

Query to check index size in Oracle 

select sum(bytes)/1024/1024 as "Index Size (MB)" from dba_segments where segment_name='&INDEX_NAME';
select sum(bytes)/1024/1024 as "Index Size (MB)" from user_segments where segment_name='&INDEX_NAME';

Conclusion: After reading this mail you will be able to check alphabetize on a table and alphabetize size in Oracle. Yous can also detect index column in Oracle all other details from the above query. You tin can besides bank check more about partition index from the dba_ind_partitions view.