Managing Objects with Data Dictionary Views

  1. Data Dictionary

User Tables – Tables created by the Oracle User
Eg. EMP, DEPT etc..

Data Dictionary – Collection of Tables and views created and maintained by the Oracle Server and contains information about the database.

We can use SQL Statements to access the data from the Data Dictionary.
Since it is a Read – Only we can issue only queries against its tables and views.

Query the Data Dictionary views to find information :
a.Definition of all schema objects in Database ie Tables, Views,Indexes, Synonyms, Sequences, Procedures, Functions, Packages,
Triggers

b.Default Values of Columns

c.Column Constraints information

d.Names of Oracle Users

e.Privileges and roles that each user has been granted

f.General Database Information

2. Data Dictionary Structure

The Oracle Server consists of :

  • Base Tables
  • User-Accessible Views

The Oracle User SYS (Central Repository Schema) owns all Base Tables and User – Accessible views of the data dictionary.
No Oracle User should ever alter (INSERT, UPDATE, DELETE) any rows or schema objects contained in the SYS schema.

USER_OBJECTS – Information about objects that the User owns or has created.
ALL_OBJECTS – Information about all objects to which you have access
DBA_OBJECTS – Information about all objects that are owned by all the Users.

Views prefixed with ALL or DBA contains an additional column named OWNER to identify who owns the object.

3. How to use the Dictionary Views

Start with DICTIONARY which contains the names and descriptions of dictionary tables and views.

DESCRIBE dictionary

SELECT * FROM DICTIONARY WHERE TABLE_NAME = ‘USER_OBJECTS’
/

We can even write queries to search the COMMENTS column for a word or phrase.

SELECT table_name
FROM dictionary
WHERE LOWER(comments) LIKE ‘%columns’
/

4.USER_OBJECTS and ALL_OBJECTS Views

a.Use the USER_OBJECTS view to :

  • See all of the objects that are owned by the User.
  • Obtain a listing of all object names and types in schema along with
    Date created
    Date of last modification
    Status (Valid or Invalid)
    b.Use the ALL_OBJECTS view to see all objects to which the User has access.

We can query the USER_OBJECTS view to see the names and types of all the objects in our schema.

OBJECT_NAME – Name of the Object
OBJECT_ID – System generated a number of the object
OBJECT_TYPE – Type of the object (Table,View,Index,Sequence)
CREATED – Timestamp for the creation of the object
LAST_DDL_TIME – Timestamp for the last modification of the object resulting from a DDL Command
STATUS – Status of the Object (VALID, INVALID, N/A)
GENERATED – Was the name of the object system-generated Eg. Index automatically created for a Primary Key Column

The OBJECT_TYPE column under this table holds a value of TABLE, VIEW, SEQUENCE, INDEX, PROCEDURE, FUNCTION, PACKAGE, or TRIGGER

The CAT View contains only two columns: TABLE_NAME and TABLE_TYPE and provides the names of all INDEX, TABLE, CLUSTER, VIEW, SYNONYM, SEQUENCE

5. Table Information

All the Tables created by the User are stored in USER_TABLES

DESCRIBE user_tables

SELECT
table_name
FROM
user_tables
/

The TAB View is a synonym of the USER_TABLES View.
We can query it to see a listing of tables that we own.
We can even query the ALL_TABLES view to see a listing of all tables to which we have access.

6.Column Information

USER_TAB_COLUMNS

DESCRIBE user_tab_columns

We can query the USER_TAB_COLUMNS to find detailed information about the columns in our tables.

The view contains the information :
a.Column Name
b.Column Data Types
c.Length of Data Types
d.Precision and Scale for NUMBER Columns
e.Whether NULLS are allowed
f.Default Value

SELECT column_name,data_type,data_length,data_precision,
data_scale,nullable
FROM user_tab_columns
WHERE table_name = ‘EMP’
/

7. Constraint Information

a.USER_CONSTRAINTS describes the constraint definitions on your tables.
b.USER_CONS_COLUMNS describes columns that are owned by you and that are specified in constraints.

DESCRIBE user_constraints

We can find out the names of the constraints, the type of constraint, the table name to which constraint applies the condition for check constraints, foreign key constraint information, deletion rule for foreign key constraints, the status.

SELECT constraint_name,constraint_type,search_condition,r_constraint_name,
delete_rule,status
FROM user_constraints
WHERE table_name = ‘EMP’
/

The CONSTRAINT_TYPE can be :

  • C (Check Constraint on a Table)
  • P (Primary Key)
  • U (Unique)
  • R (Referential Integrity)
  • V (with Check Option on a view)
  • O (with Read – Only on a View)

The DELETE_RULE can be :
CASCADE: If the parent record is deleted the child records are deleted too.
NO ACTION: A parent record can be deleted only if no child records exist.

The Status can be :
ENABLED: Constraint is active
DISABLED: Constraint is currently inactive for the column under the Table.

DESCRIBE user_cons_columns

SELECT constraint_name,column_name
FROM user_cons_columns
WHERE table_name = ‘EMP’
/

CREATE TABLE A
(ID1 NUMBER,
ID2 NUMBER,
NAME VARCHAR2(30),
SAL NUMBER CONSTRAINT A_SAL_POSITIVE CHECK(SAL > 0),
CONSTRAINT A_PRIMARY_KEY PRIMARY KEY(ID1,ID2))
/

SELECT CONSTRAINT_NAME,POSITION, COLUMN_NAME
FROM USER_CONS_COLUMNS
WHERE TABLE_NAME = ‘A’
ORDER BY CONSTRAINT_NAME, POSITION
/

A Primary Key Constraint can have multiple columns. The position shows the order of columns is in the primary key definition.
A check constraint does not have any such ordering of the columns involved so therefore position is NULL.
This POSITION applies only to primary key, unique and foreign keys.

8. View Information

DESCRIBE user_views

SELECT view_name FROM USER_VIEWS
/

SELECT text FROM user_views
WHERE view_name = ‘EMPVU20’
/

After the view is created we can query the data dictionary view called USER_VIEWS to see the name of the view and its definition.

The text of the SELECT statement that constitutes the view is
stored in a LONG column.
By default when you select from a LONG column only the 80 characters of the column’s value are displayed. To see the complete listing of the query
use the command SET LONG 1000

9. Sequence Information

DESCRIBE user_sequences

The USER_SEQUENCES view describes all sequences that are owned by the User.

a.SEQUENCE_NAME – Name of the sequence
b.MIN_VALUE – Minimum value of the sequence
c.MAX_VALUE – Maximum value of the sequence
d.INCREMENT_BY – Value by which the sequence is incremented
e.CYCLE_FLAG – Does sequence wrap around on reaching the limit
f.CACHE_SIZE – No.of sequence numbers to cache

Verify your sequence values in the USER_SEQUENCES Table

SELECT sequence_name,min_value,max_value,increment_by,last_number
FROM user_sequences WHERE sequence_name = ‘ID_SEQ’
/

The LAST_NUMBER column displays the next available sequence number if NOCACHE is specified

10. Synonym Information

DESCRIBE user_synonyms

SELECT *
FROM user_synonyms
/

We can even query the ALL_SYNONYMS to find out the names of all synonyms available to the User and objects on which these synonyms apply.

SYNONYM_NAME – Name of the Synonym
TABLE_OWNER – Owner of the object that is referenced by the synonym
TABLE_NAME – Name of the table or view that is referenced by the synonym
DB_LINK – Name of the database link reference (if any)

11. Adding Comments to a Table

a.We can add comments to a Table or column by using a COMMENT statement

COMMENT ON TABLE emp
IS ‘Employee Information’
/

b.These comments can be viewed through the Data Dictionary view
ALL_COLS_COMMENTS
USER_COL_COMMENTS
ALL_TAB_COMMENTS
USER_TAB_COMMENTS

SELECT COMMENTS FROM USER_TAB_COMMENTS WHERE
TABLE_NAME = ‘EMP’
/

We can drop a comment from the Database by setting it to empty string (‘ ‘)

COMMENT ON TABLE emp IS ‘ ‘
/

SELECT COMMENTS FROM USER_TAB_COMMENTS WHERE
TABLE_NAME = ‘EMP’
/

© 2024 AWS. Learn from Scratch