Monday, April 16, 2007

Oracle9i DBA -- Database: Fundamentals I (5)

Data Dictionary Content and Usage


  • The DICTIONARY view can also be queried by using its synonym DICT. And also static view.
  • Data Dictionary Views
    1. DBA_ : show what is in all schemas.
    2. USER_ : show what is in the user’s schema
    3. ALL_ : show what the user can access
  • User SYS is the owner of the dynamic performance tables. User SYSTEM is the owner of additional tables and views.
  • To get an overview of the columns in the data dictionary and dynamic performance views, the DICT_COLUMNS view can be queried. To get an overview of the data dictionary and dynamic performance views, the DICTIONARY view or its synonym DICT can be queried.
  • Information about tables can be obtained by querying the data dictionary. 1. DBA_TABLES 2.DBA_OBJECTS
  • Scripts starting with :
    1. cat*.sql are used to create data dictionary views.
    2. Dbms*.sql are used for database package specification
    3. Utl*.sql are used to create additional views and tables.
    4. Prvt*plb are used to creating database package code.
  • Base table are created using sql.bsq script and is automatically run at the time of database creation. The data dictionary views are created using the catalog.sql script.
  • The control files contains such information as location of datafiles, redo log files, dynamic performance view, etc.
  • Dynamic performance views obtain availability of an object, information about locks, active state of a session
  • The V$PWFILE_USERS view can be used to gather information on all users with SYSDBA or SYSOPER privileges.
  • V$LOG status field shows CLEARING when an ALTER DATABASE CLEAR LOGFILE command is issued. CLEARING_CURRENT means the current logfile is being cleared of a closed thread.
  • LOG_CHECKPOINT_INTERVAL specifies the number of redo blocks after which the checkpoint will be caused
  • LOG_CHECKPOINT_TIMEOUT indicates the seconds to resist before causing a checkpoint.
  • FAST_START_IO_TARGET indicates the target IO manipulation.
  • The AUD$ is a base table. Base tables end up with a $ mark and are created at the time of db create.
  • The USER_SOURCE data dictionary view contains the actual program text used to write a stored procedure.
  • The data dictionary provides information about: Logical and physical database structure, Definitions and space allocations of objects, Integrity constraints, Users, Roles, Privileges, Auditing.

Read more!

No comments: