İ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.
No comments:
Post a Comment