System catalog Data on data System Catalog 1
- Slides: 7
System catalog Data on data System Catalog 1
Meta-data • A relational DBMS keeps data on itself (meta-data) – name of tables – name of attributes – primary and foreign keys – constraints – and much more System Catalog 2
System catalog • Meta-data is stored in tables inside the database • Meta-data is accessible like ordinary data – Examples • select * from user_tables • select * from user_constraints • The structure of the system catalog is not standardized - varies between DBMS'es System Catalog 3
Using the system catalog • Ordinary users / application programmers seldom uses the system catalog. • The DBMS itself uses the system catalog all the time – Example • select fname, lname from employee where postcode = 4000 – check that the table (or view) employee exists – check that employee has attributes: fname, lname, postcode – check that the current user has the right to access the attributes – check that the data type of postcode is a number (comparable to 4000) System Catalog 4
Updating the system catalog • Never update the system catalog directly! • Indirect updates – DDL statements are really DML statements on the system catalog • create table … → insert into RELATION … • drop table … → delete into RELATION … • alter table … → update RELATION … – Ordinary DML (select, insert, update, delete) only affect ordinary tables, not the system catalog. System Catalog 5
The system catalog in Oracle • Some useful tables in the system catalog – all_users – user_tables • Log in as SYS – Role SYSDBA – To view the full system catalog • your (the current user) tables [those tables that you created] – all_tables • all the tables that you have access to – user_indexes – user_constraints – user_views System Catalog 6
How to select from the system catalog • The tables / views in the system catalog generally has many attributes and many rows – select * from all_tables • will produce a huge result – select table_name from all_tables where table_name like '%stud%' • will produce a smaller result System Catalog 7
