220 likes | 755 Views
Data Dictionaries. CSCI 4227/5227 Advanced Database. Overview. What Is a Data Dictionary? How Does the DBMS Use a Data Dictionary? How Do You Use a Data Dictionary? What Is In a Data Dictionary? Oracle’s Data Dictionary?. What Is a Data Dictionary?.
E N D
Data Dictionaries CSCI 4227/5227 Advanced Database
Overview • What Is a Data Dictionary? • How Does the DBMS Use a Data Dictionary? • How Do You Use a Data Dictionary? • What Is In a Data Dictionary? • Oracle’s Data Dictionary?
What Is a Data Dictionary? • Contains information about the structures in the database • Also called • System Catalog • Strictly speaking this is what Oracle has • Meta Data • Generic term, for data about data • Data Repository • Synonym for Data Dictionary used to imply stand-alone systems • According to Codd an RDB MUST use tables for its Data Dictionary
How Does the DBMS Use a Data Dictionary? • Security • Integrity • View Definition • Parsing SQL • Optimizing SQL
How Do You Use a Data Dictionary? • By the DBA • Who built what when • Who gave you access to this item and when • By the programmer • Checking information when building programs • Building self adapting code • Documenting the database layout
What Is In a Data Dictionary? • Each “object” in the database • Tables, Views,Types, Procedures, Functions, Columns … • Who created it • Its Definition • What it uses • What uses it
Oracle’s Data Dictionary Tables • For details see • Palinski Chapter 3 • Elmasri Navathe Chapter 17 • Three types of views of the meta data • USER (created by this user) • ALL (accessible to this user) • USER is those tuples in ALL where owner = current user • DBA (accessible only to persons with DBA privileges)
Average User All User DBA (Cannot be viewed)
DBA User All and DBA are the same and visible User
An example: User_Tables (7 items): BRANCH STAFF PROPERTYFORRENT CLIENT PRIVATEOWNER VIEWING REGISTRATION
All_tables (109 items) (non-DBA user)* * for brevity sake, this list does not include user created tables
DBA_Tables • Too many to list (2,429 additional items) • Cannot be seen by a non-DBA user. For a DBA user DBA_xxx and All_xxx are equivalent.
Meta-Meta Data • The tables DICT and DICT_COLUMNS describe the data dictionary tables • In technical terms they are meta-meta data • In practical terms they are an on-line guide to the data dictionary
The Big Picture • CATALOG (CAT) • Describes Tables, Views Select * From user_catalog; TABLE_NAME TABLE_TYPE ----------------------- ----------- P TABLE S TABLE SP TABLE SP2 TABLE SP_V VIEW
The Other Big Picture • User_objects • Describes all DB Objects select object_name, object_type,object_id, created, last_ddl_time from user_objects where object_name like 'S%' OBJECT_NAME OBJECT_TYPE OBJECT_ID CREATED LAST_DDL_ --------------- ------------------- ---------- --------- --------- SYS_C0013194 INDEX 75702 02-SEP-08 02-SEP-08 SYS_C0013193 INDEX 75700 02-SEP-08 02-SEP-08 SYS_C0013192 INDEX 75698 02-SEP-08 02-SEP-08 SYS_C0013191 INDEX 75696 02-SEP-08 02-SEP-08 SYS_C0013190 INDEX 75694 02-SEP-08 02-SEP-08 SYS_C0013189 INDEX 75692 02-SEP-08 02-SEP-08 SYS_C0013188 INDEX 75690 02-SEP-08 02-SEP-08 SYS_C0011684 INDEX 72962 14-AUG-08 14-AUG-08 STAFFGLASGOW VIEW 76394 03-SEP-08 03-SEP-08 STAFF TABLE 75691 02-SEP-08 02-SEP-08
Sequences Comments Table Column Constraints Exceptions to Constraints LOBS Indexes Clusters Types Triggers Procedures and Functions Packages Tablespaces and Quotas AND MANY MANY MORE
Adding a comment to the data dictionary • Syntax: • COMMENT ON TABLE [schema.]obj1 IS 'text‘ • COMMENT ON COLUMN [schema.]obj1.column IS 'text' • To drop a comment from the database, set it to the empty string ' ‘ • Related Views: • DICTIONARY • DICT_COLUMNS • USER_COL_COMMENTS • USER_TAB_COMMENTS 1. obj may be a table or a view
Views versus Synonyms • A view is a virtual table made up of one or many tables and columns derived from columns within those tables. • A synonym is an alternate name for an existing object. • Oracle will resolve object names in the following order: current user private synonym public synonym
Synonym create synonym big_sp for sanderso.sp_v create public synonym pub_sp for scott.sp_v (note public synonym must be created by DBA) select synonym_name, table_owner, table_name from user_synonyms; SYNONYM_NAME TABLE_OWNE TABLE_NAME --------------- ---------- ---------- BIG_SP SCOTT SP_V select synonym_name, table_name, owner from all_synonyms where table_owner = 'SCOTT' SYNONYM_NAME TABLE_NAME OWNER --------------- ---------- ----------------------- BIG_SP SP_V SCOTT PUB_SP SP_V PUBLIC
Synonym Caution Oracle will resolve object names in the following order: • current user: if you override an existing private synonym • private synonym : If you omit the public clause, then the synonym is private and is accessible only within its schema. • public synonym : Public synonyms are accessible to all users. However each user must have appropriate privileges on the underlying object in order to use the synonym. Thus you can accidentally overlay another object. SQL Format: CREATE [OR REPLACE] [PUBLIC] SYNONYM [schema.]synonym FOR [schema.]object [@dblink NOTE:You must have CREATE SYNONYM system privilege to create a PRIVATE synonym. You must have CREATE PUBLIC SYNONYM privilege or be a DBA or to create a PUBLIC synonym.
Example of Synonym Precedence SQL> select * from dual; D - X SQL> create synonym dual for staffglasgow; Synonym created. SQL> select * from dual; STAFF FNAME LNAME POSITION S DOB SALARY BRANC ----- ---------- ---------- ---------- - --------- ---------- ----- SG37 Ann Beech Assistant F 10-NOV-60 12000 B003 SG14 David Ford Supervisor M 24-MAR-58 18000 B003 SG5 Susan Brand Manager F 03-JUN-40 24000 B003 SQL> drop synonym dual; Synonym dropped. SQL> select * from dual; D - X