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