Data Dictionary, Built-in Database Objects

 Hey Folks,

We will see here some interesting facts like If we create/Install a Database and then what all database objects are created by default. So please go through this post 

Built-In Database objects :

When we Create/Install a Database, In Addition to creating the database files, Several other structures are created. Below are the objects that are created with the database :

• Data Dictionary
• Performance Tables 
• PL/SQL Packages
• Database event triggers

 

Data Dictionary: Data Dictionary contains descriptions of the objects in the Database.

Dynamic performance tables: Dynamic performance tables Contains information used by the database administrator (DBA) to Monitor and tune the database and instance.

PL/SQL packages: PL/SQL packages program units adding the functionality of the database. These packages are created when the catproc.sql script is run after the CREATE DATABASE command.

Database Event Triggers: Triggers are procedures that execute implicitly whenever a table or view is modified, or when some user actions or database system actions occur.

 

Data Dictionary:  Characteristics of Data Dictionary : 

• Central to every Oracle Database
• Describes the database and its objects
• Contains read-only tables and views
• Stored in the SYSTEM tablespace
• Owned by the user SYS
• Maintained by the Oracle Server
• Accessed with SELECT statement

One of the most important parts of an Oracle database is its data dictionary, which is a read-only set of tables and views that provides information about its associated database.

The Data dictionary is updated by the Oracle Server whenever a data definition language (DDL) command is executed. Also, Data manipulation language (DML) commands, Such as one that the cause of the table to extend, Can update the data dictionary.

The Data dictionary is not only the central part of every Oracle database, but it is an important source of information for all users, From end-user to Application Designers and Database Administrator.

SQL statements are used to access the data dictionary. You can issue queries only against the tables and views of the data dictionary.

 

Base Tables and Data Dictionary Views : 

The Data Dictionary contains two Parts :

• Base Tables
• Stores description of the database
• Created with CREATE DATABASE

• Data Dictionary Views
• Used to simplify the base table information
• Accessed through public synonyms
• Created with the catalog.sql script

The Data Dictionary contains descriptions of the objects in the database. It includes two types of objects :

 

Base Tables :

Base tables are underlying tables, Which store information about the database. The base tables are the first objects created in any Oracle Database. They are automatically created when the Oracle server runs the sql.bsq script at the time the Database is created. Only the Oracle Server should write to these tables. Users rarely access them directly, because most of the data is stored in cryptic format. Never use DML commands to Update the base table directly, except for the AUD$ tables.

An example of a base table is the IND$ table, which contains information about the indexes in the Database. 

 

Data Dictionary Views :

The Data dictionary views are base table summaries, which provide for a more useful display of base table information. 

For Example, In the data dictionary views, object names are used instead of only objects numbers. 

The Data dictionary views are created using the catalog.sql script which is run after the CREATE DATABASE command.

Creating Data Dictionary Views :

Below Scripts are responsible for Creating Data Dictionary Views :

catalog.sql: Creates  commonly used data dictionary views and synonyms

catproc.sql: Runs Scripts required for server-side PL/SQL

The base tables of the data dictionary are automatically created when the database is created. When the database is created using the Oracle Universal Installer (OUI),  the scripts to create a data dictionary and dynamic performance views and scripts for your Oracle Server options are run automatically.

You must run these scripts manually when creating a new database manually. In addition, you may need to run them again when upgrading to a new release of the Oracle Server.  These Scripts must be run as the user SYS with the SYSDBA privilege.

The scripts are located in the following Directories :

UNIX :  $ORACLE_HOME/rdbms/admin

NT : %ORACLLE_HOME%\rdbms\admin

Data Dictionary Contents

The data dictionary provides information about:

• Logical and physical database structures
• Definitions and space allocations of objects 
• Integrity constraints 
• Users
• Roles 
• Privileges 
• Auditing

The Data Dictionary contains :

• The definitions of all schema objects in the database (tables, views, indexes, clusters, synonyms,  sequences, procedures, functions,  packages, triggers, and so on)
• How much space has been allocated for, and is currently used by, the schema objects
• Default values for columns
• Integrity constraint information 
• The names of the Oracle Users
• Privileges and roles each user has been granted
• Auditing Information, Such as who has accessed or updated various schema objects

How the Data Dictionary is Used:

Primary Uses :

• Oracle Server uses it to find information about 
• Users
• Schema Objects
• Storage Structures

• Oracle Server modifies it when as DDL statement is executed.
• Users and DBAs use it as a read-only reference for information about the database. 

How the Oracle server Uses the Data Dictionary 

Data is the base tables of the data dictionary that are necessary for the Oracle Server to function. Therefore,  Only the Oracle server should write or change Data Dictionary information. During  Database operation, the Oracle Server read the data dictionary to ascertain that schema objects exist and that users have proper access to them. The Oracle Server also updates the Data Dictionary continuously to reflect changes in Database Structures.

How Users and DBAs Use the Data Dictionary :

The views of the Data Dictionary serve as a reference for all database users. Some Views are accessible to all Oracle Users. Others are intended for Database Administrator Only.

 

Data Dictionary View Categories : 

• Three Sets of Static views
• Distinguished by their Scope

- DBA: What is in all the Schema

- ALL: What the users can access

- USER: What is in the User's Schema

 

Data Dictionary Views with DBA Prefixes :

Views with the DBA prefix show a global view of the entire Database. They are meant to be queried only by the database administrators. Any User granted the system privilege SELECT ANY TABLE can query the DBA prefixed views of the data dictionary.

To Query on all objects in the database, the DBA cab issue the statement  like :

SQL> SELECT owner, object_name, object_type from  dba_objects;

 

Data Dictionary Views with ALL Prefixes :

Views with the ALL prefix refer to the user's overall perspective of the database. These views return information about schema objects to which the user has accessed by way of public or explicit grants of privileges and role, in addition to schema objects that the user owns.

For example,  The Following query returns information about all the objects to which a user has access :

SQL> SELECT owner, object_name, object_type FROM all_objects;

 

Data Dictionary Views with USERS Prefixes :

The views most likely to be of interest to typical database users are those with USER prefix. These Views :

• Refer to the user's environment in the database
• Generally, refer to the objects owned by the current users
• Have column identical to the other views, Except that the column OWNER is implied to be a current user
• Return a subset of the information in the ALL views
• Can have short/compact public synonyms for convenience

For Example,  The following query returns all the objects contained in the user's schema :

SQL> SELECT owner, object_name, object_type, from users_objects;

 

Data Dictionary Views : 

Data Dictionary views are Static views that answer to a question such as :

• Was the object ever created?
• What is the object a part of?
• Who owns the object?
• What Privileges do users have?
• What restrictions are on the objects?

Data Dictionary Examples:

• General Overview: DICTIONARY, DICT_COLUMNS
• Schema Objects: DBA_TABLES, DBA_INDEXES, DBS_TAB_COLUMNS, DBA_CONSTRAINTS
• Space Allocation: DBA_SEGMENTS, DBA_EXTENTS
• Database Structures: DBA_TABLESPACES, DBA_DATA_FILES

To get an overview of the data dictionary views, the DICTIONARY view or its synonym DICT can be quired. For example :

SQL>  SELECT * FROM dictionary;

Include the where clause to make the responses narrow or short output as required. For example :

SQL> SELECT * FROM dictionary

 WHERE table_ name LIKE 'DBA_SEG';

To get a list of columns within  a view, use the DESCRIBE  keyword. For example :

SQL>  DESCRIBE dbs_users;

To get an overview of the columns in the data dictionary views, you can query the DISCT_COLUMNS view.

To view the content of a data dictionary view, use the SELECT command.

SQL>  SELECT * from dba_users;

Dynamic Performance Tables: Characteristics : 

• Virtual Tables
• Record current database activity
• Continually updated while the database is operational
• Information is accessed from memory and control file
• Used to monitor and tune the database 
• Owned by SYS user
• Synonyms begin with V$
• Listed in V$FIXED_TABLE

Throughout  its operation, the Oracle Server records current database activity in a set of virtual tables called Dynamic Performance Views. These virtual tables exist in memory only when the database is running, to reflect real-time conditions of the database operation. They point to actual sources of information in memory and the control file.

These tables are not true tables, and are not to be accessed by most users; however, DBA can query, grant the SELECT privilege, and create views on the views. These views are sometimes called fixed views because they cannot be altered or removed by the DBA.

The Dynamic performance tables are owned by SYS, and their names all begin with V_$. Views are created on these tables, and then public synonyms are created for the views. The Synonyms names begin with V$. For example, the V$DATAFILE view contains information about the database's data files, and the V$FIXED_TABLES view contain information about all of the dynamic performance tables and views in the database.

The dynamic performance tables provide information such as :

• Is the object online and available?
• Is the object open?
• What locks are being held?
• Is the session active?

 

Dynamic Performance Examples: Below is the few important Dynamic performance views :

V$CONTROLFILE  : Lists the names of the control files

V$DATABASE : Contains Database information from the Control file.

V$DATAFILE : Contains Data file information from the control file.

V$INSTANCE : Displays the state of the current instance.

V$PARAMETER : Lists Parameters and values currently in effect for the session.

V$SESSION : Lists session information for each current session.

V$SGA : Contains Summary information in the System Global Area (SGA).

V$SPPARAMETER : Lists the contents of the SPFILE.

V$TABLESPACE : Displays tablespace information from the control file.

V$THREAD : Contains thread information from the control file

V$VERSION : Version Numbers of the core library components in the Oracle Server.

 

The V$FIXED_TABLE view can also be queried to get a listing of the dynamic performance views:

SQL> SELECT * FROM V$FIXED_TABLE;

To get a list of columns within a view, use the DESCRIBE keyword:

SQL> DESCRIBE V$INSTANCE;

To view the contents of the view, use the SELECT command.

SQL> SELECT * FROM V$INSTANCE; 

 

Administrative Script Naming Conventions :

The administrative scripts can be separated into categories by their filenames:

cat*.sql :

This script creates the data dictionary views. In addition to the  catalog.sql and catproc.sql scripts. these are scripts  that create information for Oracle Utilities. For example, The  catadt.sql script creates data dictionary views for showing metadata information for types and other objects features in the Oracle RDBMS.  The catnoadt.sql  script drops these tables and views.

 

dbms.sql and prvt.plb :

These scripts creates objects for predefined Oracle packages that extend the Oracle Server functionality. These Programs simplify the task of administering the database. Most SQL scripts are run during the execution of the  catproc.sql script. A few additional scripts must be executed by the Database Administrator. An example is a dbmspool.sql script, which enables you to the sizes of objects in the shared pool and marks them to be kept or removed in the SGA in order to reduce shared pool fragmentation. 

 

utl*.sql 

These scripts must be run when the database needs additional views and tables. For example, the utlxplan.sql  script creates a table used to view the execution plan of a SQL statement.

Note: Most of these scripts must be executed under the user SYS  with SYSDBA privileges. The DBA should examine the scripts to determine which user account must be used to run the scripts

Instance Monitoring Using Diagnostic Files, Alert Log File, Background Trace File, User Trace File, Enabling/Disabling User Tracing

Hey Folks

We will see here some basic stuff about instance monitoring, that how to monitor the database using Diagnostic files, Alert log file, Background trace file User Trace File, Enabling/ Disabling user tracing.

Monitoring an Instance using Diagnostic files :

• Diagnostic Files 
• Contains Information about significant events encountered
• Used to resolve problems
• Used to better managed the database on a day-to-day  basis
• Several type of diagnostic file exists:
• alertSID.log file
• Background trace files
• User trace files.

Diagnostic files means to capture information about the database's activities. They are also useful tools for you when you are managing an instance.  Several type of files exists. The type of diagnostic file created depends on the problem that occurred or the information that need to be communicated.

• Alert Log File (alertSID.log) : Information for day-to-day operation of the database.
• Background trace files : It Records important Information when background processes, such as SMON, PMON, DBWn, and others fail.
• User Trace File : It is as text file and  It records important information for fatal user errors or user forced to traced files.

 

Alert Log Files :

• alertSID.log files :
• Records the commands
• Records results of major events
• Used for day-to-day operational information
• Used for diagnostic database errors
• Each Entry has a time stamp associated with it
• Must be managed by DBA 
• Location defined by BACKGROUND_DUMP_DEST

Each Oracle instance has an alert log file. If not already created , it is created during instance start up.  The alert log File must be managed by DBA. It continues to grow while the database continues to work. The alert log file should be the first place you look when diagnosing day-to-day operations or errors. The alert log file also contains pointers to trace files for more details information.

The alert log file keeps a records of the fallowing information : 

• When the database was started or shut down
• A list of all non-default initialization parameters
• The startup of background processes
• The thread being used by the instance
• The log Sequence number LGWR writing to 
• Information regarding a log switch
• Creation of tablespaces and undo segments
• Alter statements that have been issued
• Information regarding error message such as ORA-600 and other ORA- errors and extent errors.

The alertSID.log file location is defined by the BACKGROUND_DUMP_DEST  initialization parameter. 

 

Background Trace Files :

• Background trace files
• Log Errors detected by any background process
• Are used to diagnose and troubleshoot errors.
• Created when a background process encounters an error 
• Location defined by BACKGROUND_DUMP_DEST

Background trace files are used to log errors that have been encountered by a background process, such are SMON, PMON, DBWn  and other background processes. This files exist only when an error requires writing to the trace files. You use these files to diagnose and troubleshoot problems. Initially when a background trace file is  created it contains  header information indicating the version number of the data server and the operating system.

 Naming convention for user trace file : sid_processname_PID.trc  ( orcl_lgwr_12345.trc).

 Its Location is defined by the BACKGROUND_DUMP_DEST initialization parameter. 

 

User Trace Files :

• User trace files :
• Produced By the user process
• Can be generated by a server process
• Contain Statistics for traced SQL statements
• Contain user error messages
• Created when a user encounters user session errors
• Location is defined by USER_DUMP_DEST
• Size defined by MAX_SUMP_FILE_SIZE

 User trace file contains statistics for traced SQL statements, Which are useful for SQL tuning. In Addition, user trace files contains user error messages.

 Naming convention for user trace file : sid_ora_PID.trc  (orcl_ora_12345.trc)

 Its Location is defined by the USER_DUMP_DEST initialization parameter.

 

Enabling or Disabling User Tracing :

• Session Level :
• Using the alter session command :

                    ALTER SESSION SET SQL_TRACE = TRUE

 Executing DBMS  Procedure :

                     dbms_system.SET_SQL_TRACE_IN_SESSION

• Instance Level :

• Setting the  initialization parameter :

                    SQL_TRACE = TRUE

Setting SQL_TRACE = TRUE  at the instance level will produce a significant volume of trace data. This option should be used with care.

 

Thank You !!