İNDEX BULUNAN TABLOLARIN LİSTESİ

İndex bulunan tabloların listesini aşağida script ile bulabiliriz. Ayrıca bu tabloda kaç adet index olduğunuda listeler.

SELECT table_owner, table_name, column_name, COUNT (*) index_count
FROM dba_ind_columns
WHERE table_owner = 'HR'
GROUP BY table_owner, table_name, column_name

İndex bulunmayan tabloların listesini aşağida script ile bulabiliriz.
SELECT owner, table_name
FROM (SELECT owner, table_name
FROM dba_tables
--Burada where kosulu sema sinirlandirmasi yapilabilir.
MINUS
SELECT table_owner, table_name
FROM dba_indexes) orasnap_noindex
WHERE owner NOT IN ('SYS', 'SYSTEM')
ORDER BY owner, table_name

Ağaıdaki sorguda PK (birincil anahtar) olmayan tabloları döndürür.

SELECT owner, table_name
FROM dba_tables dt
WHERE NOT EXISTS (
SELECT 'TRUE'
FROM dba_constraints dc
WHERE dc.table_name = dt.table_name
AND dc.constraint_type = 'P')
AND owner = 'HR'
ORDER BY owner, table_name

Bu sorguda aşağıdaki kriterlere göre farklı aramalarda yapılabilir. Tercih sizin.

CONSTRAINT_TYPE VARCHAR2(1) Type of constraint definition:

• C (check constraint on a table)
• P (primary key)
• U (unique key)
• R (referential integrity)
• V (with check option, on a view)
• O (with read only, on a view)

SQL> desc all_constraints

Adı Boş? Tür

----------------------------------------- -------- ----------------------------

OWNER NOT NULL VARCHAR2(30)

CONSTRAINT_NAME NOT NULL VARCHAR2(30)

CONSTRAINT_TYPE VARCHAR2(1)

TABLE_NAME NOT NULL VARCHAR2(30)

SEARCH_CONDITION LONG

R_OWNER VARCHAR2(30)

R_CONSTRAINT_NAME VARCHAR2(30)

DELETE_RULE VARCHAR2(9)

STATUS VARCHAR2(8)

DEFERRABLE VARCHAR2(14)

DEFERRED VARCHAR2(9)

VALIDATED VARCHAR2(13)

GENERATED VARCHAR2(14)

BAD VARCHAR2(3)

RELY VARCHAR2(4)

LAST_CHANGE DATE

INDEX_OWNER VARCHAR2(30)

INDEX_NAME VARCHAR2(30)

INVALID VARCHAR2(7)

VIEW_RELATED VARCHAR2(14)

Daha ayrıntılı bilgi için kaynak http://vsbabu.org/oracle/ adresine bakınız.