Oracle DATA DICTIONARY Views-I

DATA DICTIONARY views:

We create many objects like TABLE, VIEW, SYNONYM, INDEX, SEQUENCE etc.,
To get information about the objects oracle provided us data dictionary views. It is like metadata.

USER_OBJECTS and DBA_OBJECTS holds data of all objects created.

We shall try to see usage of other data dict views:

CREATE TABLE students
(
    id    NUMBER(4)     CONSTRAINT students_pk PRIMARY KEY,
    name    VARCHAR2(30)  CONSTRAINT students_nn NOT NULL,
    email    VARCHAR2(40)  CONSTRAINT students_U  UNIQUE,
    phone    NUMBER(10)
);

begin
insert into students values (1,'TOM','tom@live.com',123456);
insert into students values (2,'JAI','jai@gmail.com',654321);
commit;
end;
/

-- To check constraints on a table use USER_CONSTRAINTS
select * from user_constraints
where table_name='STUDENTS';

Note : For primary key and unique key constraints index are created upon table creation.

--To check info about indexes
select * from user_indexes
where index_name='STUDENTS_PK';

CREATE TABLE course
(
     id number(4) constraint course_fk references students(id),
    course_name  varchar2(20) constraint course_chk check
                                     (course_name in ('eee','cse','ece','mech'))
);

begin
insert into course values (1,'eee');
insert into course values (2,'ece');
commit;
end;
/

select * from user_constraints
where table_name='COURSE';

Create a view on tables students and course                            

create or replace view stud_details
as
select s.id,s.name,c.course_name from students s,course c
where s.id=c.id;

-- To see query used to create view we use USER_VIEWS
select TEXT from user_views
where view_name='STUD_DETAILS';

CREATE OR REPLACE SYNONYM stud
for studnets;

select * from user_synonyms
where table_name='STUDENTS';

create sequence stud_id_seq
start with 100
increment by 1;

insert into students values (stud_id_seq.nextval,'adi','adi@live.com',789);

select * from user_sequences
where sequence_name='STUD_ID_SEQ'

Few Other Data Dictionary Views are :

USER_TRIGGERS
USER_ERRORS 

Query to retrieve active responsibilities assigned to a user

Input : User Name

select distinct fu.user_name "User Name", fu.description "User Full Name", frv.responsibility_name "Responsibility Name", fur.start_date "Resp Assignment Start Date", fur.end_date "Resp Assignment End Date" from fnd_responsibility_vl frv, fnd_user_resp_groups_direct fur, fnd_user fu where fur.responsibility_id=frv.responsibility_id and fu.user_id=fur.user_id AND SYSDATE BETWEEN fu.start_date AND NVL(fu.end_date,SYSDATE) AND SYSDATE BETWEEN frv.start_date AND NVL(frv.end_date,SYSDATE) and fu.user_name=:User_name order by frv.responsibility_name ;

Query to get all responsibilities to which a concurrent program is attached

Query to get all responsibilities to which a concurrent program is attached
Input : Program Name

select distinct
frl.responsibility_name "Responsibility Name",
fcp.user_concurrent_program_name "Concurrent Program Name",
frg.request_group_name "Request Group Name"
from
fnd_responsibility_vl frl
,fnd_request_groups frg
,fnd_request_group_units frgu
,fnd_concurrent_programs_vl fcp
where
frg.request_group_id=frl.request_group_id
and frg.request_group_id=frgu.request_group_id
and frgu.request_unit_id=fcp.concurrent_program_id
and fcp.user_concurrent_program_name=:Program --conc program
order by frl.responsibility_name
;