Tuesday, April 15, 2008

Dictinary Views: a primer

Now that there are people who want to know more about their database in terms of metadata ....where they could go?

These people may be application evelopers and they may be interested in knowing that a particulat table has how many constraints and what are those ..so that they can have a naming convension and seeing that name itself they can say that this is related to that table ...

 

These people may be an on call 24 x 7 DBA who might get a call with a message that such and such database is down with such and such errors ...

 

These people may be performance tuners who want to know about the value of a particular parameter at a particular point of time ...

 

So all of them need to come back to the views called dictionary views ...

Once one customer of mine was very much interested in knowing all about the foreign keys inside a particular schema..say .. SCOTT ...so for him I wrote the block below ...

 

The PL/SQL block is basically querying the dictionary views only ...

/* ----------------------- -------      START   -------------------------------------------- */

DECLARE
PROCEDURE fk_details (powner VARCHAR2) IS
CURSOR c1 IS
SELECT t.table_name tbl
FROM dba_tables t
WHERE t.owner = powner;

CURSOR c2 (powner VARCHAR2, lptable VARCHAR2) IS
SELECT a.tt tt, a.owner o, b.table_name tn1, a.constraint_name cn1,
b.column_name col1, b.POSITION, a.r_constraint_name rcn,
c.column_name col2, c.POSITION p, c.table_name r_table_name,
a.r_owner ron
FROM (SELECT owner, constraint_name, r_constraint_name, r_owner,
1 tt
FROM dba_constraints
WHERE owner = UPPER (powner)
AND table_name = UPPER (lptable)
AND constraint_type != 'C'
UNION
SELECT owner, constraint_name, r_constraint_name, r_owner, 2
FROM dba_constraints
WHERE (r_constraint_name, r_owner) IN (
SELECT constraint_name, owner
FROM dba_constraints
WHERE owner = UPPER (powner)
AND table_name = UPPER (lptable))) a,
dba_cons_columns b,
dba_cons_columns c
WHERE b.constraint_name = a.constraint_name
AND b.owner = a.owner
AND c.constraint_name = a.r_constraint_name
AND c.owner = a.r_owner
AND b.POSITION = c.POSITION;
BEGIN
FOR rec1 IN c1 LOOP
FOR rec2 IN c2 (powner, rec1.tbl) LOOP
DBMS_OUTPUT.put_line ( rec2.tt
|| ','
|| rec2.o
|| ','
|| rec2.tn1
|| ','
|| rec2.cn1
|| ','
|| rec2.col1
);
END LOOP;
END LOOP;
END fk_details;
BEGIN
fk_details ('SCOTT');
END;

/* ----------------------- -------       END   ---------------------------------------------- */

So here I'm querying only 2 dictionary views to pull my info ....

Below are some other queries & PL/SQL blocks that fetches data from dictionay views basically for internal auditing -- from a developers point of view --

 

DISCLAIMER: YOU CAN WRITE FAR BETTER AND EFFICIENT QUERIES >>

                    THIS IS ONLY 1 WAY OF DOING IT....

1.Total number of tables

SELECT COUNT(*) FROM all_objects WHERE owner='SCHEMA_NAME' AND object_type='TABLE'

2.Tables without PK

SELECT
object_name,object_type
FROM all_objects WHERE owner='SCHEMA_NAME' AND object_type='TABLE'
and
object_name not in (select distinct table_name from all_constraints ac
where
ac.owner='SCHEMA_NAME' and ac.CONSTRAINT_TYPE='P')

3.Total number of Pks

SELECT COUNT(constraint_name) FROM all_constraints ac
WHERE owner='SCHEMA_NAME' AND constraint_type='P'
No. of tables with alternate keys SELECT a.TABLE_NAME,a.OWNER,
DECODE(COUNT(*),1,'X','Alternate Key exists') AS "alternate key Yes/No JOIN"
FROM
all_cons_columns a,all_constraints b
WHERE
a.TABLE_NAME=b.TABLE_NAME AND
a.OWNER=b.OWNER AND
a.CONSTRAINT_NAME=b.CONSTRAINT_NAME AND
a.owner='SCHEMA_NAME' AND
b.CONSTRAINT_TYPE='P'
GROUP BY a.TABLE_NAME,a.OWNER
ORDER BY a.table_name

4.Total no. of Fks

SELECT COUNT(constraint_name) FROM all_constraints ac
WHERE owner='SCHEMA_NAME' AND constraint_type='R'
No. of un-indexed Fks SELECT ucc.table_name,
ucc.column_name,
ucc.CONSTRAINT_NAME,
ucc.position,
0 AS Y_N
FROM
all_cons_columns ucc,
all_constraints uc,
all_tables ut
WHERE
ut.table_name=uc.table_name AND
uc.constraint_type='R'
AND
uc.constraint_name=ucc.constraint_name AND
ut.OWNER=uc.OWNER AND
uc.OWNER=ucc.OWNER AND
ucc.OWNER='SCHEMA_NAME' AND
NOT EXISTS
(SELECT 1 FROM
all_ind_columns uic
WHERE
ucc.table_name=uic.table_name AND
ucc.column_name=uic.column_name AND
uic.INDEX_OWNER=ucc.OWNER AND
ucc.OWNER='SCHEMA_NAME'
AND
ucc.position=uic.column_position)

5.Indexed vs. non indexed Fks

-- not having index
SELECT vw.table_name,vw.column_name,vw.CONSTRAINT_NAME,vw.position,
DECODE(vw.Y_N,0,'Not Indexed','Indexed') AS Index_Status
FROM
(SELECT ucc.table_name,
ucc.column_name,
ucc.CONSTRAINT_NAME,
ucc.position,
0 AS Y_N
FROM
all_cons_columns ucc,
all_constraints uc,
all_tables ut
WHERE
ut.table_name=uc.table_name AND
uc.constraint_type='R'
AND
uc.constraint_name=ucc.constraint_name AND
ut.OWNER=uc.OWNER AND
uc.OWNER=ucc.OWNER AND
ucc.OWNER='SCHEMA_NAME' AND
NOT EXISTS
(SELECT 1 FROM
all_ind_columns uic
WHERE
ucc.table_name=uic.table_name AND
ucc.column_name=uic.column_name AND
uic.INDEX_OWNER=ucc.OWNER AND
ucc.OWNER='SCHEMA_NAME'
AND
ucc.position=uic.column_position)
UNION
-- having index
SELECT ucc.table_name,
ucc.column_name,
ucc.CONSTRAINT_NAME,
ucc.position,
1 AS Y_N
FROM
all_cons_columns ucc,
all_constraints uc,
all_tables ut
WHERE
ut.table_name=uc.table_name AND
uc.constraint_type='R'
AND
uc.constraint_name=ucc.constraint_name AND
ut.OWNER=uc.OWNER AND
uc.OWNER=ucc.OWNER AND
ucc.OWNER='SCHEMA_NAME' AND
EXISTS
(SELECT 1 FROM
all_ind_columns uic
WHERE
ucc.table_name=uic.table_name AND
ucc.column_name=uic.column_name AND
uic.INDEX_OWNER=ucc.OWNER AND
ucc.OWNER='SCHEMA_NAME'
AND
ucc.position=uic.column_position)) vw

6.Total no. of Unique Key

SELECT COUNT(constraint_name) FROM all_constraints ac
WHERE owner='SCHEMA_NAME' AND constraint_type='U'
Columns with length 1 SELECT table_name,column_name,data_type,data_length FROM all_tab_columns
WHERE
owner='SCHEMA_NAME'
AND
data_length=1
AND
(data_type LIKE '%CHAR%' OR data_type LIKE '%NUMBER%')

7.Number of nullable columns

a)select
ac.TABLE_NAME,ac.COLUMN_NAME
from all_tab_columns ac
where
ac.OWNER='SCHEMA_NAME' and ac.NULLABLE='Y'
order by 1
b)select
ac.TABLE_NAME,count(*)
from all_tab_columns ac
where
ac.OWNER='SCHEMA_NAME' and ac.NULLABLE='Y'
group by ac.TABLE_NAME
order by 1

8.No. of check constraints with In condition

SELECT
CONSTRAINT_NAME,TABLE_NAME,SEARCH_CONDITION
FROM all_constraints ac
WHERE owner='SCHEMA_NAME' AND constraint_type='C'

9.Total no. of constraint s

SELECT count(0)
FROM all_constraints ac
WHERE owner='SCHEMA_NAME'

10.Constraints with system generated name

a) SELECT count(0) FROM all_constraints ac WHERE owner='SCHEMA_NAME'
AND constraint_name LIKE 'SYS%'

b) SELECT constraint_type,count(constraint_name) FROM all_constraints ac
WHERE owner='SCHEMA_NAME' AND constraint_name LIKE 'SYS%'
group by constraint_type

10.Triggers

select
trg.TRIGGER_NAME,trg.TRIGGERING_EVENT,trg.TABLE_NAME
from all_triggers trg where trg.owner='SCHEMA_NAME'

11.Indexes

select * from all_ind_columns aic
where
aic.INDEX_OWNER='SCHEMA_NAME'

11.BMP IDX for FKs

select index_name,index_type from all_indexes where
index_type='BITMAP' and
index_name in
(select
index_name
from all_ind_columns where index_owner='SCHEMA_NAME' and column_name in
(
select v.column_name from
(
SELECT ucc.table_name,
ucc.column_name,
ucc.CONSTRAINT_NAME,
ucc.position,
1 AS Y_N
FROM
all_cons_columns ucc,
all_constraints uc,
all_tables ut
WHERE
ut.table_name=uc.table_name AND
uc.constraint_type='R'
AND
uc.constraint_name=ucc.constraint_name AND
ut.OWNER=uc.OWNER AND
uc.OWNER=ucc.OWNER AND
ucc.OWNER='SCHEMA_NAME' AND
EXISTS
(
SELECT 1 FROM
all_ind_columns uic
WHERE
ucc.table_name=uic.table_name AND
ucc.column_name=uic.column_name AND
uic.INDEX_OWNER=ucc.OWNER AND
ucc.OWNER='SCHEMA_NAME'
AND
ucc.position=uic.column_position))v))

No comments:

Post a Comment