Data Dictionary Views and Control Files

Chapter 11 Data Dictionary Views and Control Files Mohd Shahizan Othman Jabatan Sistem Maklumat Fakulti Sains Komputer & Sistem Maklumat

Objectives • Use the data dictionary components and views • List useful dynamic performance views • View control file & redo log files information

Data Dictionary Components Data Dictionary Views: • Use underlying tables owned by SYS. SYS owns the underlying tables and the data dictionary views • Can be queried like other views. Provide system-related information by querying the database’s internal management tables • Some of the views are available for anyone to query. Some are reserved for DBAs only • A user needs SELECT CATALOG privilege usually part of the CONNECT role

Data Dictionary Components - 2 2 types of data dictionary views • Static data dictionary views • USER: Views focused on a user’s own objects. Ex. USER_TABLES, USER_VIEWS • ALL: Views about objects a user either owns or can query. Ex. ALL_TABLES, ALL_TAB-COLUMNS • DBA: Views for DBAs only, showing information about all objects in the database. Ex. DBA_TABLES, DBA_USERS • Dynamic performance views • V$: Views displaying current activities in the database • GV$: Views that combine activities across multiple instances

Data Dictionary Components - 3 The DICTIONARY view lists data dictionary views with a short description

Data Dictionary Views Uses for Data Dictionary Views • Supply information to the Enterprise Manager console • Help you build queries • Help you review naming standards • Help you find invalid views

Data Dictionary Views - 2 • Use the SQL* Plus DESCRIBE (or DESC) command to list the column names and data types. Ex. DESC DBA_TABLES • Information frequently obtained from data dictionary views such as users, tables, objects, indexes and roles. Ex. of the views used: USER_TABLE: tables you own USER_VIEWS: view name and query that created the view USER_INDEXES: indexes you own ALL_TAB_PRIVS_MADE: all grants given out by you for any table DBA_USERS: all users in the database and information about them

Dynamic Performance Views • Dynamic performance views are similar to data dictionary views except: • The contents of the views change with database activity • They are used mostly for tuning and monitoring • Answer questions such as: • What session is using the most CPU time now? • How many users are currently logged on? • How much memory is being used?

Dynamic Performance Views - 2 Example of some useful views include: • V$SYSSTAT: statistical details about all sessions running on the database • V$SQL: Details on individual SQL statements running on the database • V$SESSTAT: Information by session to help identify memory usage • V$DATAFILE: information on the file# and filename of all control log and datafiles

Viewing Control File Data Use dynamic performance views to see control file information

Viewing Redo File Data

Chapter Summary • Data dictionary views and dynamic performance views are owned by SYS • Use normal queries on these views • Most data dictionary views have a prefix of USER, ALL, or DBA • Most dynamic performance views have a prefix of V$ or GV$

Index Management

Objectives • Learn the types of indexes (b-tree and bitmap) Oracle offers and when to use each type • Understand how to create b-tree and bitmap index

Introduction to Indexes An index: • Is a database structure that speeds up data retrieval • Is automatically updated when rows are inserted updated or deleted

Introduction to Indexes - 2 An index: • Enforces PRIMARY KEY and UNIQUE constraints • Can have up to 32 (30 for bitmap) columns • Stores an entry for rows except those with all nulls in indexed columns (except bitmap, which stores all rows)

Introduction to Indexes - 3 Example of table and index data:

Types and Uses of Indexes The types of indexes offered in Oracle are: • B-tree index • Bitmap index Oracle's default type Compact but limited

Types and Uses of Indexes - 3 Components of CREATE INDEX command: • UNIQUE / BITMAP • (<colname>|<expression> ASC|DESC, ...) • COMPUTE STATISTICS • NOCOMPRESS / COMPRESS <nn> • NOSORT / REVERSE • NOPARALLEL / PARALLEL <nn> • PARTITION / NOPARTITION

B-tree Index B-tree structure example:

B-tree Index –2 Example: A unique, single column index: CREATE UNIQUE INDEX CLASSMATE.DEWEY_IX ON CLASSMATE.CH09LIBRARYBOOK (DEWEY_DECIMAL) INITRANS 2 PCTFREE 20 LOGGING COMPUTE STATISTICS;

Bitmap Index A bitmap index: • Does not use b-tree algorithm • Stores information in a bitmap • More compact than b-tree index • Useful only in certain circumstances: • Columns with low cardinality • Queries comply to a list of criteria of the way data is accessed • Few if any updates • Not unique and not using the DESC clause

Bitmap Index - 2 Example of bitmap structure:

Bitmap Index - 3 Example: CREATE BITMAP INDEX PATIENT_BITMAP_X ON PATIENT (BLOOD_TYPE, GENDER);

Dropping Indexes Use DROP INDEX to remove an index DROP INDEX <schema>.<indexname>;

Data Dictionary Information on Indexes Data dictionary view: SELECT * FROM USER_IND_COLUMNS; • INDEX_NAME • TABLE_NAME • COLUMN_NAME • COLUMN_POSITION • COLUMN_LENGTH • CHAR_LENGTH • DESC

Data Dictionary Views and Control Files