Skip to main content

Data Dictionary


  • When you use the Database Configuration Assistant to create a database, Oracle automatically creates the data dictionary. Thereafter, whenever the database is in operation, Oracle updates the data dictionary in response to every DDL statement.
  • The data dictionary base tables are the first objects created in any Oracle database. They are created in the SYSTEM tablespace and must remain there. The data dictionary base tables store information about all user-defined objects in the database.
  • catalog.sql --->Creates the data dictionary and public synonyms for many of its views. Grants PUBLIC access to the synonyms
  • catproc.sql -->Runs all scripts required for, or used with, PL/SQL
  • catclust.sql-->Creates Oracle Real Application Clusters data dictionary views


Script NameNeeded ForRun ByDescription
catblock.sql
Performance management
SYS
Creates views that can dynamically display lock dependency graphs

caths.sql
Heterogeneous Services
SYS
Installs packages for administering heterogeneous services

catio.sql
Performance management
SYS
Allows I/O to be traced on a table-by-table basis
catqueue.sql
Advanced Queuing
Creates the dictionary objects required for Advanced Queuing
catrep.sql
Oracle Replication
SYS
Runs all SQL scripts for enabling database replication
catwrr.sql
Database Replay
SYS
Master script that creates the entire schema related to Database Replay - calls the create schema scripts for Workload Capture, Database Replay, Workload Replay, and Workload Intelligence
catwrrwitb.sql
Database Replay
SYS
Creates the schema for Workload Intelligence
dbmsiotc.sql
Storage management
Any user
Analyzes chained rows in index-organized tables
dbmspool.sql
Performance management
SYS or SYSDBA
Enables DBA to lock PL/SQL packages, SQL statements, and triggers into the shared pool

userlock.sql
Concurrency control
SYS or SYSDBA
Provides a facility for user-named locks that can be used in a local or clustered environment to aid in sequencing application actions
utlbstat.sql and utlestat.sql
Performance monitoring
SYS
Respectively start and stop collecting performance tuning statistics
utlchn1.sql
Storage management
Any user
For use with the Oracle Database. Creates tables for storing the output of the ANALYZEcommand with the CHAINED ROWS option. Can handle both physical and logical rowids.

utlconst.sql
Year 2000 compliance
Any user
Provides functions to validate that CHECKconstraints on date columns are year 2000 compliant
utldtree.sql
Metadata management
Any user
Creates tables and views that show dependencies between objects

utlexpt1.sql
Constraints
Any user
For use with the Oracle Database. Creates the default table (EXCEPTIONS) for storing exceptions from enabling constraints. Can handle both physical and logical rowids.

utlip.sql
PL/SQL
SYS
Used primarily for upgrade and downgrade operations. It invalidates all existing PL/SQL modules by altering certain dictionary tables so that subsequent recompilations will occur in the format required by the database. It also reloads the packages STANDARD and DBMS_STANDARD, which are necessary for any PL/SQL compilations.
utlirp.sql
PL/SQL
SYS
Used to change from 32-bit to 64-bit word size or vice versa. This script recompiles existing PL/SQL modules in the format required by the new database. It first alters some data dictionary tables. Then it reloads the packages STANDARDand DBMS_STANDARD, which are necessary for using PL/SQL. Finally, it triggers a recompilation of all PL/SQL modules, such as packages, procedures, and types.
utllockt.sql
Performance monitoring
SYS or SYSDBA
Displays a lock wait-for graph, in tree structure format

utlpwdmg.sql
Security
SYS or SYSDBA
Creates PL/SQL functions for default password complexity verification. Sets the default password profile parameters and enables password management features.

utlrp.sql
PL/SQL
SYS
Recompiles all existing PL/SQL modules that were previously in an INVALID state, such as packages, procedures, and types.

utlsampl.sql
Examples
SYS or any user with DBA role
Creates sample tables, such as emp and dept, and users, such as scott
utlscln.sql
Oracle Replication
Any user
Copies a snapshot schema from another snapshot site
utltkprf.sql
Performance management
SYS
Creates the TKPROFER role to allow the TKPROF profiling utility to be run by non-DBA users
utlvalid.sql
Partitioned tables
Any user
Creates tables required for storing output of ANALYZE TABLE ...VALIDATE STRUCTURE of a partitioned table
utlxplan.sql
Performance management
Any user
Creates the table PLAN_TABLE, which holds output from the EXPLAIN PLAN statement

Comments

Post a Comment

Popular posts from this blog

Cold Backup Cloning of Database .

 Cold Backup :  A cold backup is done when there is no user activity going on with the system. Also called as offline backup, is taken when the database is not running and no users are logged in. all files of the database are copied and no changes during the copy are made. Cold backup can be preformed when Database is in archive mode as well as Non archive log mode  . Backup Strategy :  Gather all the details of physical files (CRD) files in  database . redo log files are online redo log and archived redo log ones .   Select file_name,tablespace_name from dba_data_files  ; select * from v$controlfile  select member from v$logfile ; select name from v$archived_Log ;  create pfile from spfile  Backup  the contol file manually . alter database backup controlfile to trace as '/tmp/coldbackup_controlfile.sql' ;     Stop all the DB services (Database ,Listener)on source DB Server. Copy all the files(Datafiles,redologfiles,pfile,contol file backup) from source to backup location 
Read more

Find ALert log Location

 The alert log is an XML file that is a the log of messages and errors. Each alert log is specific to its component type, such as database, Oracle ASM, listener, and Oracle Clusterware. For the database, the alert log includes messages about the following:     Critical errors (incidents)     Administrative operations, such as starting up or shutting down the database,         recovering the database, creating or dropping a tablespace, and others.     Errors during automatic refresh of a materialized view     Other database events      Trace files are updated periodically over the life of the process and can contain information on the process environment,  status, activities, and errors. In addition, when a process detects a critical error,  it writes information about the error to its trace file.      Using Find Command    find $ORACLE_BASE -type f -name alert_$ORACLE_SID.log    select value from GV$DIAG_INFO WHERE name='Diag Trace';    find /opt/oracle -name alert*log
Read more

FRA Usage and Administration

Image
 In Oracle database, the Flash Recovery Area or FRA is a location on disk where the database can create and manage several kinds of backup and recovery-related files. Main file types are archivelog, flashback log, backups, as well as mirrors for your control files and redo log files. All files in the FRA are Oracle-managed files. Using a Flash Recovery Area simplifies the administration of your database by automatically retaining them for as long as they are needed for restore and recovery activities, and deleting them when they are no longer needed, because the space is needed for another backup and recovery-related purpose. Checking the current usage  You can check the configuration by looking at two parameters.The parameters are db_receovery_file_dest and second one db_recovery_file_dest_size .  db_receovery_file_dest point to location where the files will be located in Server . FOr normal single Databases this will be any directory for RAC this will be set to a Diskgroup whi
Read more