C H A P T E R  10

images

Data Dictionary Basics

The previous chapters in this book focus on topics such as managing users, basic security, tables, indexes, and constraints. This chapter focuses on extracting information from the data dictionary regarding those topics. Each of the sections in this chapter contains one or more SQL queries that demonstrate how to extract information from the data dictionary regarding a specific type of database object.

The Oracle data dictionary houses information about all aspects of the database. It stores critical information about the physical characteristics of the database, users, objects, and dynamic performance metrics. A senior-level DBA must posses an expert knowledge of the data-dictionary views. This chapter focuses on how you can use the information in the data dictionary.

This chapter is a turning point in the book. It divides the book between basic DBA tasks and more advanced topics. You must have a solid understanding of the data dictionary and how to proactively extract information and reactively resolve issues. The first few sections of this chapter detail the data-dictionary architecture. Sections after that contain techniques and queries that you can use to retrieve information about the database.

Data-Dictionary Architecture

If you ever inherit a database and are asked to maintain and manage it, typically you'll inspect the contents of the data dictionary to determine the physical structure of the database and see what events are currently transacting. Toward this end, Oracle provides to two general categories of data-dictionary views:

  • Static USER/ALL/DBA views
  • Dynamic V$ and GV$ views

The USER/ALL/DBA views contain metadata (information) describing the physical makeup of the database. For example, when you create a table, the description of that table is considered metadata and is stored in the database. Whenever you change the definition of an object, Oracle updates the data dictionary correspondingly. You typically use these views to view the structure of the database and information about users and corresponding objects.

The V$ and GV$ views provide real-time statistics about events currently transacting in the database. You use these views to determine which users are connected to the database, what SQL is executing, whether system bottlenecks exist, and so forth.

Static Views

Oracle describes a subset of the data-dictionary views as static. These views are based on physical tables maintained internally by Oracle. Oracle's documentation states that these views are static in the sense that the data they contain doesn't change at a rapid rate (at least, not rapid when compared to the dynamic V$ and GV$ views). These views are stored in the SYSTEM tablespace.

The term static can sometimes be a misnomer. For example, the DBA_SEGMENTS and DBA_EXTENTS views change dynamically as the amount of data in your database grows and shrinks. Regardless, Oracle has made the distinction between static and dynamic, and it's important to understand this architectural nuance when querying the data dictionary. There are three types or levels of static views:

  • USER
  • ALL
  • DBA

The USER views contain information available to the current user. For example, the USER_TABLES view contains information about tables owned by the current user. No special privileges are required to select from the USER-level views.

At the next level are the ALL static views. The ALL views show you all object information the current user has access to. For example, the ALL_TABLES view displays all database tables on which the current user can perform any type of Data Manipulation Language (DML) operation. No special privileges are required to query from the ALL-level views.

Next are the DBA static views. The DBA views contain metadata describing all objects in the database (regardless of ownership or access privilege). To access the DBA views, you must have a DBA role or SELECT_CATALOG_ROLE granted to the current user.

The static views are based on internal Oracle tables such as USER$, TAB$, and IND$. If you have access to the SYS schema, you can view underlying tables directly via SQL. For most situations, you only need to access the static views that are based on the underlying internal tables.

The data-dictionary tables (like USER$, TAB$, and IND$) are created during the execution of the CREATE DATABASE command. As part of creating a database, the sql.bsq file is executed, which builds these internal data-dictionary tables. The sql.bsq file is usually located in the ORACLE_HOME/rdbms/admin directory; you can view it via an operating system editing utility (such as vi in Unix or Notepad in Windows).

The static views are created when you run the catalog.sql script (usually, you run this script after the CREATE DATABASE operation succeeds). The catalog.sql script is located in the ORACLE_HOME/rdbms/admin directory. Figure 10–1 shows the process of creating the static data-dictionary views.

images

Figure 10–1. Creating the static data-dictionary views

Static views such as DBA_USERS, DBA_TABLES, and DBA_INDEXES are built on the static tables (such as USER$, TAB$ and IND$). You can view the creation scripts of these static views by querying the TEXT column of DBA_VIEWS. For example, this query selects the TEXT column of DBA_VIEWS:

SQL> set long 5000
SQL> select text from dba_views where view_name='DBA_VIEWS';

Here's the output:

select u.name, o.name, v.textlength, v.text, t.typetextlength, t.typetext,
       t.oidtextlength, t.oidtext, t.typeowner, t.typename,
       decode(bitand(v.property, 134217728), 134217728,
              (select sv.name from superobj$ h, "_CURRENT_EDITION_OBJ" sv
              where h.subobj# = o.obj# and h.superobj# = sv.obj#), null),
       decode(bitand(v.property, 32), 32, 'Y', 'N'),
       decode(bitand(v.property, 16384), 16384, 'Y', 'N')
from sys."_CURRENT_EDITION_OBJ" o, sys.view$ v, sys.user$ u, sys.typed_view$ t
where o.obj# = v.obj#
  and o.obj# = t.obj#(+)
  and o.owner# = u.user#

images Note If you manually create a database (not using the database-creation assistant), you must be connected as the SYS schema when you run the catalog.sql and catproc.sql scripts. The SYS schema is the owner of all objects in the data dictionary.

Dynamic Performance Views

The dynamic performance data-dictionary views are often referred to as the V$ and GV$ views. These views are constantly updated by Oracle and reflect the current condition of the instance and database. Dynamic views are critical for diagnosing real-time performance issues.

The V$ and GV$ views are indirectly based on the underlying X$ tables, which are internal memory structures that are instantiated when you start your Oracle instance. Some of the V$ views are available the moment the Oracle instance is started. For example, V$PARAMETER contains meaningful data after the STARTUP NOMOUNT command has been issued, and doesn't require the database to be mounted or open. Other dynamic views depend on information in the control file and therefore contain meaningful information only after the database has been mounted (like V$CONTROLFILE). Some V$ views provide kernel-processing information (like V$BH) and thus have useful results only after the database has been opened.

At the top layer, the V$ views are actually synonyms that point to underlying SYS.V_$ views. At the next layer down, the SYS.V_$ objects are views created on top of another layer of SYS.V$ views. The SYS.V$ views in turn are based on the SYS.GV$ views. At the bottom layer, the SYS.GV$ views are based on the X$ memory structures.

The top-level V$ synonyms and SYS.V_$ views are created when you run the catalog.sql script, which you usually run after the database is initially created. Figure 10–2 shows the process for creating the V$ dynamic performance views.

images

Figure 10–2. Creating the dynamic V$ performance data-dictionary views

Accessing the V$ views through the top-most synonyms is usually adequate for dynamic-performance information needs. On rare occasions, you'll want to query internal information that may not be available through the V$ views. In these situations, it's critical to understand the X$ underpinnings.

If you work with Oracle Real Application Clusters, you should be familiar with the GV$ global views. These views provide global dynamic-performance information regarding all instances in a cluster (whereas the V$ views are instance-specific). The GV$ views contain an INST_ID column for identifying specific instances in a clustered environment.

You can display the V$ and GV$ view definitions by querying the VIEW_DEFINITION column of the V$FIXED_VIEW_DEFINITION view. For example, this query displays the definition of the V$CONTROL_FILE:

select
view_definition
from v$fixed_view_definition
where view_name='V$CONTROLFILE';

Here's the output:

VIEW_DEFINITION
--------------------------------------------------------------------------------
select  STATUS , NAME, IS_RECOVERY_DEST_FILE, BLOCK_SIZE, FILE_SIZE_BLKS  from G
V$CONTROLFILE where inst_id = USERENV('Instance')

Derivable Documentation

Sometimes if you're troubleshooting an issue and are under pressure, you need to quickly extract information from the data dictionary to help resolve the problem. However, you may not know the exact name of a data-dictionary view or its associated columns. If you're like me, it's impossible to keep all the data-dictionary view names and column names in your head. Additionally, I work with databases from versions 8 through 11g, and it's difficult to keep track of which particular view may be available with a given release of Oracle.

Sometimes books and posters provide this information; but if you can't find exactly what you're looking for, you can use the documentation contained in the data dictionary itself. You can query from three views in particular:

  • DBA_OBJECTS
  • DICTIONARY
  • DICT_COLUMNS

If you know roughly the name of the view from which you want to select information, you can first query from DBA_OBJECTS. For example, if you're troubleshooting an issue regarding materialized views, and you can't remember the exact names of the data-dictionary views associated with materialized views, you can do this:

select
 object_name
from dba_objects
where object_name like '%MV%'
and owner='SYS';

Sometimes that's enough to get you in the ballpark. But often you need more information about each view. This is where the DICTIONARY and DICT_COLUMNS views can be invaluable. The DICTIONARY view stores the name of the data-dictionary views. It has two columns:

SQL> desc dictionary

Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
TABLE_NAME                                         VARCHAR2(30)
COMMENTS                                           VARCHAR2(4000)

For example, say you're troubleshooting an issue with materialized views, and you want to determine the name of data-dictionary views related to the materialized-view feature. You can run a query such as this:

select
 table_name
,comments
from dictionary where table_name like '%MV%';

Here's a short snippet of the output:

TABLE_NAME                COMMENTS
------------------------- -------------------------------------------------------
DBA_MVIEW_LOGS            All materialized view logs in the database
DBA_MVIEWS                All materialized views in the database
DBA_MVIEW_ANALYSIS        Description of the materialized views accessible to dba
DBA_MVIEW_COMMENTS        Comments on all materialized views in the database

In this manner, you can quickly determine which view you need to access. If you want further information about the view, you can describe it. For example:

SQL> desc dba_mviews

If that doesn't provide you with enough information regarding the column names, you can query the DICT_COLUMNS view. This view provides comments about the columns of a data-dictionary view. For example:

select
 column_name
,comments
from dict_columns
where table_name='DBA_MVIEWS';

Here's a fraction of the output:

COLUMN_NAME             COMMENTS
----------------------- ---------------------------------------------
OWNER                   Owner of the materialized view
MVIEW_NAME              Name of the materialized view
CONTAINER_NAME          Name of the materialized view container table
QUERY                   The defining query that the materialized view instantiates

This way, you can generate and view documentation regarding most data-dictionary objects. This technique allows you to quickly identify appropriate views and which columns may help you in a troubleshooting situation.

Logical and Physical Database Structures

When a database is created, it contains multiple logical space containers called tablespaces. Each tablespace consists of one or more physical datafiles. Each datafile consists of many operating-system blocks. Each database contains many users. Each user has a schema that is the logical container for objects such as tables and indexes. Each table or index consists of a segment. If a table or index is partitioned, there are many partition segments for each partitioned table or partitioned index.

Each segment contains one or more extents. As a segment needs space, it allocates additional extents. An extent consists of a set of database blocks. A typical database block size for an online transaction processing (OLTP) database is 8KB. Each database block contains one or more operating-system blocks. Figure 10–3 describes the relationships between logical and physical structures in an Oracle database.

images

Figure 10–3. Oracle database logical and physical structure relationships

Table 10–1 describes some of the views used to report on database physical-space management. This isn't an exhaustive list; rather, this table contains the most commonly used views for monitoring database space.

Table 10–1. Overview of Database Space-Management Views

Data-Dictionary View Purpose
V$DATABASE Information about the database in the control file
DBA/ALL/USER_USERS User account information
DBA/USER_TABLESPACES Tablespace descriptions
DBA_DATA_FILES Database datafile descriptions
DBA/USER_FREE_SPACE Free extents in tablespaces
V$DATAFILE Datafile information from the control file
V$DATAFILE_HEADER Datafile information derived from the datafile header
DBA/ALL/USER_TABLES Table attribute descriptions
DBA/ALL/USER_INDEXES Index attribute descriptions
DBA/USER_SEGMENTS Storage data for segments
DBA/ALL/USER_PART_TABLES Partitioned table data
DBA/ALL/USER_PART_INDEXES Partitioned index data
DBA/ALL/USER_TAB_PARTITIONS Storage information for partitioned tables
DBA/ALL/USER_IND_PARTITIONS Storage information for partitioned indexes
DBA/USER_EXTENTS Extent information of each segment
V$CONTROLFILE Names and size of control files
V$LOG Online redo-log file information in the control file
V$LOG_HISTORY Online redo-log file history information in control file
V$ARCHIVED_LOG Archive log-file information in the control file

This chapter doesn't contain an exhaustive set of SQL scripts for querying data-dictionary objects. Rather, it covers basic techniques for querying the data dictionary about topics covered up to this point in the book (for example, users, tables, indexes, and so on). You should be able to build on the concepts in this chapter to fulfill any requirement you have for viewing data-dictionary metadata.

Displaying User Information

You may find yourself in an environment that contains hundreds of databases located on dozens of different servers. In such a scenario, you want to ensure that you don't run the wrong commands in the incorrect database. When performing DBA tasks, it's prudent to verify that you're connected as the appropriate account and to the correct database.

Currently Connected User

You can run the following types of SQL commands to verify the currently connected user and database information:

SQL> show user;
SQL> select * from user_users;
SQL> select name from v$database;
SQL> select instance_name, host_name from v$instance;

As shown in Chapter 3, an efficient way of staying aware of your environment is to set your SQL*Plus prompt automatically via the login.sql script to display user and instance information. This example manually sets the SQL prompt:

SQL> set sqlprompt '&_USER.@&_CONNECT_IDENTIFIER.> '

Here's what the SQL prompt now looks like:

SYS@O11R2>

You can also use the SYS_CONTEXT built-in SQL function to display a wide variety of details about your currently connected session. The general syntax for this function is as follows:

SYS_CONTEXT('<namespace>','<parameter>',[length])

This example displays the user, authentication method, host, and instance:

select
 sys_context('USERENV','CURRENT_USER') usr
,sys_context('USERENV','AUTHENTICATION_METHOD') auth_mth
,sys_context('USERENV','HOST') host
,sys_context('USERENV','INSTANCE_NAME') inst
from dual;

USERENV is a built-in Oracle namespace. More than 50 parameters are available when you use the USERENV namespace with the SYS_CONTEXT function. Table 10–2 describes some of the more useful parameters. Refer to the Oracle SQL Reference guide for a complete list of parameters available.

Table 10–2. Useful USERENV Parameters Available with SYS_CONTEXT

Parameter Name Description
AUTHENTICATED_IDENTITY Identity used in authentication
AUTHENTICATION_METHOD Method of authentication
CURRENT_USER Username for the currently active session
DB_NAME Name specified by the DB_NAME initialization parameter
DB_UNIQUE_NAME Name specified by the DB_UNIQUE_NAME initialization parameter
HOST Hostname for the machine where the client initiated the database connection
INSTANCE_NAME Instance name
IP_ADDRESS IP address of the machine where the client initiated the database connection
ISDBA TRUE if the user authenticated with DBA privileges through the operating system or password file
NLS_DATE_FORMAT Date format for the session
OS_USER Operating-system user from the machine where the client initiated the database connection
SERVER_HOST Hostname of the machine where the database instance is running
SERVICE_NAME Service name for the connection
SID Session identifier
TERMINAL Operating-system identifier for the client terminal

Users Currently Logged In

When you're debugging performance or connectivity issues, it's useful to view which users are connected to the database and the number of connections per user. If you want to view dynamic information such as users currently logged on to your database, as a user assigned the SELECT_CATALOG_ROLE, execute the following query:

select
  count(*)
 ,username
from v$session
group by username;

In Oracle Database 11g, the V$SESSION view has nearly 100 columns. Other columns that are commonly queried are OSUSER, SQL_ID, PROCESS, MACHINE, PORT, TERMINAL, and PROGRAM. See the Oracle Database Reference guide (available on Oracle's OTN website) for a complete list of columns and their descriptions.

Currently Executing SQL

If you want to view SQL statements that currently connected users are running, issue this query:

select
 a.sid
,a.username
,b.sql_text
from v$session a
    ,v$sqltext_with_newlines b
where a.sql_id = b.sql_id
order by
 a.username
,a.sid
,b.piece;

If you're using an Oracle Database 9i or earlier, the previous query won't work because the SQL_ID column isn't available. Here's a query that works for older versions of Oracle:

select
 a.sid
,a.username
,b.sql_text
from v$session a
    ,v$sqltext_with_newlines b
where a.sql_address    = b.address
and   a.sql_hash_value = b.hash_value
order by
 a.username
,a.sid
,b.piece;

images Tip V$SQLTEXT_WITH_NEWLINES is identical to V$SQLTEXT with the exception that V$SQLTEXT_WITH_NEWLINES doesn't replace tabs and newlines with spaces.

User Accounts in the Database

If you want to view information about all user accounts that have been created (and not dropped) in the database, use the DBA_USERS view. The following displays information such as when each account was created, default and temporary tablespaces, and status:

set lines 132
col username form a15
col default_tablespace form a18
col temporary_tablespace form a20
col account_status form a16
--
select
  username
 ,default_tablespace
 ,temporary_tablespace
 ,account_status
 ,created
 ,lock_date
from dba_users
order by 1;

This query is useful for troubleshooting user-account issues such as determining whether an account is locked. The previous query uses SQL*Plus formatting statements to make the output readable. If you're not using SQL*Plus, you need to remove the formatting commands (everything preceding the two dashes --).

ALL_USERS AND SECURITY

Any schema with the CREATE SESSION system privilege can select schema information from the ALL_USERS view. For example:

select
  username
 ,created
from all_users
order by username;

This ability to select from ALL_USERS can be a security concern because it allows any user with minimal privileges (like CREATE SESSION) to view all user accounts in the database. Viewing all user information allows a malicious person to start guessing passwords for existing users. If you're a DBA, be sure you change the default passwords to well-known accounts and encourage users to use passwords that aren't easily guessable.

Viewing Table Information

DBAs often query the data dictionary for information about tables. Questions often arise regarding what users have access to what tables. Or when you're investigating physical space and performance issues, you may want to show how much storage a table consumes and how many rows are in each table. These types of topics are discussed in the next several sections.

Viewing Accessible Tables

Sometimes, when you're troubleshooting table-accessibility issues, the first thing to check is which tables you have access to. You can query the USER_TABLES view to display tables owned by the currently connected user:

select
 a.table_name
,b.created
,b.last_ddl_time
,a.last_analyzed
from user_tables  a, user_objects b
where a.table_name = b.object_name;

To view all tables to which your currently connected user has access—for example, via GRANT statements issued by other owners—use the ALL_TABLES view:

select
 table_name
,tablespace_name
from all_tables;

Querying the USER_TABLES view is a quick way to determine which tables exist in your current account, whereas the ALL_TABLES view contains every table to which you have any type of DML (SELECT, INSERT, UPDATE, and/or DELETE) access. If you have access to the DBA_TABLES view, you can also query the tables a user has access to via the following query:

select
 table_name
from dba_tables
where owner = upper('&owner'),

When you're troubleshooting, you can check columns like CREATED and LAST_DDL_TIME, which tell when the structure of a table was last modified. Use the following query to view this information:

select
 a.table_name
,b.created
,b.last_ddl_time
,a.last_analyzed
from dba_tables  a
    ,dba_objects b
where a.table_name = b.object_name
and   a.owner      = upper('&owner'),

Displaying Object Disk-Space Usage

When you're diagnosing database space issues, it's handy to view how much space a user's tables and indexes are consuming. The next query is useful when you want to view the space consumption of objects for a user:

UNDEFINE owner
COL summer FORM 999,999.999
SET LINES 132 TRIMSPOOL ON PAGES 100
SPO space.txt
SELECT
 segment_name
,partition_name
,tablespace_name
,segment_type
,SUM(bytes)/1024/1024 summer
FROM dba_extents
WHERE owner = UPPER('&&owner')
GROUP BY segment_name,partition_name,tablespace_name,segment_type
ORDER BY segment_name,partition_name;
SPO OFF;

This script prompts you for an object owner. If the table has partitions, the space per partition is displayed. You need access to DBA-level views to run the script. You can modify the script to point at the ALL or USER-level views to report on objects for the currently connected user account. This query also uses SQL*Plus-specific commands, such as setting the line size and column formatting, which are necessary to make the output readable.

Displaying Table Row Counts

When you're investigating performance or space issues, it's useful to display each table's row count. Run the following SQL code as a DBA-privileged schema. Notice that this script contains SQL*Plus-specific commands such as UNDEFINE and SPOOL. The script prompts you each time for a username:

UNDEFINE user
SPOOL tabcount_&&user..sql
SET LINESIZE 132 PAGESIZE 0 TRIMSPO OFF VERIFY OFF FEED OFF TERM OFF
SELECT
  'SELECT RPAD(' || '''' || table_name || '''' ||',30)'
  || ',' || ' COUNT(*) FROM &&user..' || table_name || ';'
FROM dba_tables
WHERE owner = UPPER('&&user')
ORDER BY 1;
SPO OFF;
SET TERM ON
@@tabcount_&&user..sql
SET VERIFY ON FEED ON

This code generates a file named tabcount_<user>.sql that contains the SQL statements that select row counts from all tables in the specified schema. If the username you provide to the script is INVUSER, then you can run the generated script as follows.

SQL> @tabcount_invuser.sql

Keep in mind that if the table row counts are high, then this script can take a long time to run (several minutes).

Developers and DBAs often use SQL to generate SQL statements. This is a useful technique when you need to apply the same SQL process (repetitively) to many different objects, such as all tables in a schema..If you don't have access to DBA-level views, you can query the USER_TABLES view. For example:

SPO tabcount.sql
SET LINESIZE 132 PAGESIZE 0 TRIMSPO OFF VERIFY OFF FEED OFF TERM OFF
SELECT
  'SELECT RPAD(' || '''' || table_name || '''' ||',30)'
  || ',' || ' COUNT(*) FROM ' || table_name || ';'
FROM user_tables
ORDER BY 1;
SPO OFF;
SET TERM ON
@@tabcount.sql
SET VERIFY ON FEED ON

If you have accurate statistics, you can query the NUM_ROWS column of the DBA/ALL/USER_TABLES view. This column normally has a close row count if statistics are generated on a regular basis. The following query selects NUM_ROWS from the USER_TABLES view:

select
 table_name
,num_rows
from user_tables;

MANUALLY GENERATING STATISTICS

If you want to generate statistics for a table, use the DBMS_STATS package. This example generates statistics for a user and a table:

SQL> exec dbms_stats.gather_table_stats(ownname=>'INV',-
          tabname=>'F_SALES',-
          cascade=>true,estimate_percent=>20,degree=>4);

You can generate statistics for all objects for a user with the following code:

SQL> exec dbms_stats.gather_schema_stats(ownname => 'INV',-
          estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,-
          degree => DBMS_STATS.AUTO_DEGREE,-
          cascade => true);

The prior code instructs Oracle to estimate the percentage of the table to be sampled with the ESTIMATE_PERCENT parameter using DBMS_STATS.AUTO_SAMPLE_SIZE. Oracle also chooses the appropriate degree of parallelism with the DEGREE parameter setting of DBMS_STATS.AUTO_DEGREE. The CASCADE parameter instructs Oracle to generate statistics for indexes.

If you have partitioned tables and want to show row counts by partition, use the next few lines of SQL and PL/SQL code:

UNDEFINE user
SET SERVEROUT ON SIZE 1000000 VERIFY OFF
SPO part_count_&&user..txt
DECLARE
  counter  NUMBER;
  sql_stmt VARCHAR2(1000);
  CURSOR c1 IS
  SELECT table_name, partition_name
  FROM dba_tab_partitions
  WHERE table_owner = UPPER('&&user'),
BEGIN
  FOR r1 IN c1 LOOP
    sql_stmt := 'SELECT COUNT(*) FROM &&user..' || r1.table_name
      ||' PARTITION ( '||r1.partition_name ||' )';
    EXECUTE IMMEDIATE sql_stmt INTO counter;
    DBMS_OUTPUT.PUT_LINE(RPAD(r1.table_name
      ||'('||r1.partition_name||')',30) ||' '||TO_CHAR(counter));
  END LOOP;
END;
/
SPO OFF

Displaying Index Information

DBAs often view index metadata. Sometimes you need to verify that an environment has all the correct indexes, or you may need to investigate a performance problem. This section contains several queries that are typically used to retrieve index information from the data dictionary.

Displaying Indexes for a Table

When you're dealing with performance issues, one of the first items to check is which columns are indexed on a table. First, ensure that the object you're dealing with is a table (and not a synonym or a view). Run the following query to check whether an object is a table or not:

select
  object_name
 ,object_type
from user_objects
where object_name=upper('&object_name'),

This query prompts you for a SQL*Plus ampersand variable (OBJECT_NAME). If you're not using SQL*Plus, you may have to modify the query to explicitly query for a particular object.

When you've verified that the object is a table, query the USER_INDEXES view to display indexes for a particular table in your user. The USER_INDEXES view contains the index name information, and the USER_IND_COLUMNS view contains the columns that are indexed. If the index is built on more than one column, the COLUMN_POSITION column provides the order in which the columns appear in the index. For example:

select
 a.index_name
,a.column_name
,b.status
,b.index_type
,a.column_position
from user_ind_columns a
    ,user_indexes     b
where a.table_name = upper('&table_name')
and   a.index_name = b.index_name
order by a.index_name, a.column_position;

This query prompts you for a SQL*Plus ampersand variable (TABLE_NAME). If you're not using SQL*Plus, you may have to modify the query and name the table of interest. The indexes and the corresponding columns are displayed for the table you enter.

If you use function-based indexes, sometimes it's handy to display the expression used to create those indexes. The function expression is contained in the COLUMN_EXPRESSION column of the DBA/ALL/USER_IND_EXPRESSIONS view. The following script displays that expression, along with the index and table names:

select
 table_name
,index_name
,column_expression
from user_ind_expressions
order by table_name;

Showing Foreign-Key Columns Not Indexed

After you've built an application, you should verify that all the foreign-key columns are indexed. The following query indicates for a schema which table columns have foreign-key constraints defined for them but don't have a corresponding index:

select
  a.constraint_name cons_name
 ,a.table_name  tab_name
 ,b.column_name cons_column
 ,nvl(c.column_name,'***No Index***') ind_column
from user_constraints  a
     join
     user_cons_columns b on a.constraint_name = b.constraint_name
     left outer join
     user_ind_columns  c on b.column_name = c.column_name
                        and b.table_name  = c.table_name
where constraint_type = 'R'
order by 2,1;

For older DBAs who aren't familiar with the ANSI SQL standard, here's a similar query that uses the Oracle-specific (+) syntax to denote an outer join:

select
  a.constraint_name cons_name
 ,a.table_name  tab_name
 ,b.column_name cons_column
 ,nvl(c.column_name,'***No Index***') ind_column
from user_constraints  a
    ,user_cons_columns b
    ,user_ind_columns  c
where constraint_type = 'R'
and a.constraint_name = b.constraint_name
and b.column_name     = c.column_name(+)
and b.table_name      = c.table_name(+)
order by 2,1;

Any column that has a foreign-key constraint but no corresponding index is noted in the last column of the output with the text ***No Index***. Here's some sample output:

CONS_NAME            TAB_NAME             CONS_COLUMN          IND_COLUMN
-------------------- -------------------- -------------------- ----------------
FK_DEPOSITS          DEPOSITS             BATCH_NO             ***No Index***

The USER_CONSTRAINTS view contains definitions of all constraints in a user's schema. This is joined to USER_CONS_COLUMNS, which contains information about the columns accessible to the user that are used in constraints. You place a LEFT OUTER JOIN clause between USER_CONS_COLUMNS and USER_IND_COLUMNS because there may be a case where the view on the left side of the join has rows without corresponding rows on the right. You then apply the condition that any constraints reported by this query are of type R (a referential or foreign-key constraint).

Displaying Constraint Information

Constraints play a critical role in ensuring that data conforms to well-defined business rules. You need to be well versed with techniques for displaying the various types of constraints that can exist in a database. The following sections detail some techniques on how to query the data dictionary for constraint information.

Displaying Table Constraints

You occasionally need to view constraint information. For example, a user or developer may report that a constraint violation occurs when they try to insert or update data in a table (and they want you to figure out what's wrong with the database). You can query the DBA_CONSTRAINTS view to display constraint information for an owner and table name. The following script prompts you for two SQL*Plus ampersand variables (OWNER and TABLE_NAME); if you aren't using SQL*Plus, then you may need to modify the script with the appropriate values before you run the script:

select
  table_name
 ,(case constraint_type
    when 'P' then 'Primary Key'
    when 'R' then 'Foreign Key'
    when 'C' then 'Check'
    when 'U' then 'Unique'
    when 'O' then 'Read Only View'
    when 'V' then 'Check view'
    when 'H' then 'Hash expression'
    when 'F' then 'REF column'
    when 'S' then 'Supplemental logging'
  end) cons_type
 ,constraint_name cons_name
 ,search_condition check_cons
 ,status
from dba_constraints
where owner      like upper('&owner')
and   table_name like upper('&table_name')
order by cons_type;

The DBA/ALL/USER_CONSTRAINTS views document the constraints defined for tables in your database. Integrity constraints allow you to define rules about your data that are verified by the database engine before the data can be successfully added or modified. This ensures that your data has a high degree of quality.

The CONSTRAINT_TYPE column of the DBA/ALL/USER_CONSTRAINTS views is a one-character code. Currently, there are nine different types of constraints. Table 10–3 describes the integrity constraints available.

Table 10–3. Integrity Constraint Descriptions

Constraint Code Meaning
C Checks for a condition
P Primary key
U Unique key
R Referential integrity (foreign key)
V With check option on a view
O With read-only on a view
H Hash expression
F Constraint with a REF column
S Supplemental logging

images Note The check-constraint types H, F, and S are available only in Oracle Database 11g or higher.

Showing Primary-Key and Foreign-Key Relationships

Sometimes, when you're diagnosing constraint issues, it's useful to show what primary-key constraint is associated with a foreign-key constraint. For example, perhaps you're attempting to insert into a child table and an error is thrown indicating that the parent key doesn't exist, and you want to display more information about the parent key constraint.

The following script queries the DBA_CONSTRAINTS view to determine the parent primary-key constraints that are related to child foreign-key constraints. You need to provide as input to the script the owner of the table and the child table for which you wish to display primary-key constraints:

select
 a.constraint_type cons_type
,a.table_name      child_table
,a.constraint_name child_cons
,b.table_name      parent_table
,b.constraint_name parent_cons
,b.constraint_type cons_type
from dba_constraints a
    ,dba_constraints b
where a.owner    = upper('&owner')
and a.table_name = upper('&table_name')
and a.constraint_type = 'R'
and a.r_owner = b.owner
and a.r_constraint_name = b.constraint_name;

The preceding script prompts you for two SQL*Plus ampersand variables (OWNER and TABLE_NAME); if you aren't using SQL*Plus, then you may need to modify the script with the appropriate values before you run the script.

The following output shows that there are two foreign-key constraints. It also shows the parent table primary-key constraints:

C CHILD_TABLE     CHILD_CONS          PARENT_TABLE    PARENT_CONS         C
- --------------- ------------------- --------------- ------------------- -
R REG_COMPANIES   REG_COMPANIES_FK2   D_COMPANIES     D_COMPANIES_PK      P
R REG_COMPANIES   REG_COMPANIES_FK1   CLUSTER_BUCKETS CLUSTER_BUCKETS_PK  P

When the CONSTRAINT_TYPE column (of DBA/ALL/USER_CONSTRAINTS) contains an R value, this indicates that the row describes a referential-integrity constraint, which means the child-table constraint references a primary-key constraint. You use the technique of joining to the same table twice to retrieve the primary-key constraint information. The child-constraint columns (R_OWNER and R_CONSTRAINT_NAME) match with another row in the DBA_CONSTRAINTS view that contains the primary-key information.

You can also do the reverse of the prior query in this section; for a primary-key constraint, you want to find the foreign-key columns (if any) that correlate to it. The next script takes the primary-key record and looks to see if it has any child records that have a constraint type of R. When you run this script, you're prompted for the primary-key table owner and name:

select
  b.table_name        primary_key_table
 ,a.table_name        fk_child_table
 ,a.constraint_name   fk_child_table_constraint
from dba_constraints a
    ,dba_constraints b
where a.r_constraint_name = b.constraint_name
and   a.r_owner           = b.owner
and   a.constraint_type   = 'R'
and   b.owner             = upper('&table_owner')
and   b.table_name        = upper('&table_name'),

Here's some sample output:

PRIMARY_KEY_TABLE    FK_CHILD_TABLE       FK_CHILD_TABLE_CONSTRAINT
-------------------- -------------------- ------------------------------
CLUSTER_BUCKETS      CB_AD_ASSOC          CB_AD_ASSOC_FK1
CLUSTER_BUCKETS      CLUSTER_CONTACTS     CLUSTER_CONTACTS_FK1
CLUSTER_BUCKETS      CLUSTER_NOTES        CLUSTER_NOTES_FK1
CLUSTER_BUCKETS      DOMAIN_NAMES         DOMAIN_NAMES_FK1
CLUSTER_BUCKETS      REG_COMPANIES        REG_COMPANIES_FK1
CLUSTER_BUCKETS      CB_MS_ASSOC          CB_MS_ASSOC_FK2

The output indicates that the CLUSTER_BUCKETS table has several foreign-key constraints that refer to it.

Viewing Basic Security Information

Basic database security is often administered through roles, object grants, and system-privilege grants. When there are data-accessibility issues, you must be proficient in retrieving information about these fundamental security constructs. The next few sections describe some techniques and queries for viewing role and grant assignments.

Displaying Granted Roles

You may find yourself investigating table-access issues and want to display the roles that a user has been granted. Use this query to view which roles are granted to the currently connected user:

select
 username
,granted_role
from user_role_privs;

The next query displays the roles that have been granted to a specific user (you're prompted for GRANTEE):

select
  grantee
 ,granted_role
from dba_role_privs
where grantee = upper('&grantee')
order by grantee;

The USER_ROLE_PRIVS and DBA_ROLE_PRIVS views describe roles granted to users. To display roles granted to roles, query the ROLE_ROLE_PRIVS view:

select
 role
,granted_role
from role_role_privs;

When you create a database, several predefined roles are created for you, including DBA and SELECT_CATALOG_ROLE. To view all the roles in your database (both predefined and user-created), select the ROLE column from DBA_ROLES:

select
 role
from dba_roles;

Here's some sample output of role names in a typical database:

CONNECT
RESOURCE
DBA
SELECT_CATALOG_ROLE
EXECUTE_CATALOG_ROLE
DELETE_CATALOG_ROLE
EXP_FULL_DATABASE
IMP_FULL_DATABASE

Displaying System Privileges

Database system privileges allow you to perform tasks such as connecting to the database and creating and modifying objects. For example, some commonly granted privileges are CREATE TABLE and CREATE VIEW. Query the DBA_SYS_PRIVS view to display which system privileges have been granted to users. Listed next is a simple script that prompts for the GRANTEE:

select
  grantee
 ,privilege
 ,admin_option
from dba_sys_privs
where grantee = UPPER('&grantee')
order by privilege;

To view system privileges granted to the currently connected user, run this query:

select
 username
,privilege
,admin_option
from user_sys_privs;

The USERNAME column shows whether the privilege has been granted to the currently connected user or if the privilege has been granted to PUBLIC.

The ROLE_SYS_PRIVS view displays what system privileges have been assigned to a role. When querying this view, you see only roles that have been granted to the currently connected schema. Here's an example query that lists privileges granted to a specified role:

select
 role
,privilege
from role_sys_privs
where role = upper('&role'),

The prior SQL displays only database system privileges that have been directly granted to a user. To view any system privileges that have been granted through a role to a user, you have to also query a view such as ROLE_SYS_PRIVS. The following query displays system privileges granted either directly to the currently connected user or through any roles granted to the user:

select
 privilege
,'DIRECT GRANT'
from user_sys_privs
union
select
privilege
,'ROLE GRANT'
from role_sys_privs;

Two roles—CONNECT and RESOURCE—are commonly assigned to newly created accounts. However, Oracle recommends that you not assign these roles to users because they may not be available in future releases. Instead, Oracle advises that you create your own roles and assign privileges as required. Run the following query to view privileges assigned to these roles:

select
 grantee
,privilege
from dba_sys_privs
where grantee IN ('CONNECT','RESOURCE')
order by grantee;

Here's the output:

ROLE                      PRIVILEGE
------------------------- -------------------------
CONNECT                   CREATE SESSION
RESOURCE                  CREATE CLUSTER
RESOURCE                  CREATE INDEXTYPE
RESOURCE                  CREATE OPERATOR
RESOURCE                  CREATE PROCEDURE
RESOURCE                  CREATE SEQUENCE
RESOURCE                  CREATE TABLE
RESOURCE                  CREATE TRIGGER
RESOURCE                  CREATE TYPE

You can use a vast array of data-dictionary views to determine what users and roles have been assigned which system and object privileges. This section has touched on only a few examples. See Table 10–4 for a description of the various privilege-related data-dictionary views and their purposes.

Table 10–4. Privilege-Related Data-Dictionary Views

View Description
DBA_ROLES All roles in the database
DBA_ROLE_PRIVS Roles granted to users and roles
DBA_SYS_PRIVS All system privileges granted to users and roles
DBA_TAB_PRIVS All object privileges granted to users and roles
DBA_COL_PRIVS All column object grants
ROLE_ROLE_PRIVS Roles granted to other roles; only for roles to which the user has access
ROLE_SYS_PRIVS Privileges granted to other roles; only for roles to which the user has access
ROLE_TAB_PRIVS Table privileges granted to roles; only for roles to which the user has access
ALL_TAB_PRIVS Object grants for which the user is the object owner, grantor, or grantee; also object grants for which PUBLIC is the grantee
ALL_TAB_PRIVS_MADE Object grants where the user is the object owner or grantor
ALL_TAB_PRIVS_RECD Object grants where the user is the grantee or where PUBLIC is the grantee
ALL_COL_PRIVS Column object grants where the user is the object owner, grantor, or grantee; also column grants where PUBLIC is the grantee
ALL_COL_PRIVS_MADE Column object grants where the user is the object owner or grantor
ALL_COL_PRIVS_RECD Column object grants where the user is the grantee or PUBLIC is the grantee
USER_ROLE_PRIVS Roles granted to the user
USER_SYS_PRIVS System privileges granted to the user
USER_TAB_PRIVS Object grants for which the user is the object owner, grantor, or grantee
USER_TAB_PRIVS_MADE Object grants where the user is the object owner
USER_TAB_PRIVS_RECD Object grants where the user is the grantee
USER_COL_PRIVS Column object grants where the user is the object owner, grantor, or grantee
USER_COL_PRIVS_MADE Column object grants where user is the object owner
USER_COL_PRIVS_RECD Column object grants where the user is the grantee

Displaying Object Privileges

Object privileges are grants that allow you to perform DML operations (INSERT, UPDATE, and DELETE) on another user's tables. Before you can perform DML operations on another user's objects, you must be granted the appropriate privileges. Object privileges are managed through the GRANT and REVOKE statements.

Sometimes, when you're troubleshooting table-access issues, you need to view what DML privileges have been granted. The following query selects from the USER_TAB_PRIVS_RECD view to display the table privileges that have been granted to the currently connected user:

select
  owner
 ,table_name
 ,grantor
 ,privilege
from user_tab_privs_recd;

To view privileges that the current user has granted to other users, select from the USER_TAB_PRIVS_MADE view:

select
  grantee
 ,table_name
 ,grantor
 ,privilege
from user_tab_privs_made;

Run the following query to view table privileges that have been granted to your current user:

select grantee, table_name, privilege
from user_tab_privs
where grantee = sys_context('USERENV','CURRENT_USER')
order by table_name, privilege;

In the previous lines of code, the SYS_CONTEXT function is used to extract the current username from the session. Without qualifying the GRANTEE with your current username, the query also displays object privileges you've granted and privileges that have been granted by other users to your objects. The query can alternatively prompt you for your current username. For example:

select grantee, table_name, privilege
from user_tab_privs
where grantee = UPPER('&your_user_name')
order by table_name, privilege;

This next query selects from USER_TAB_PRIVS and ROLE_TAB_PRIVS to check for any object privileges that have been granted directly to the user or granted through a role that has been granted to the user:

select
  grantee
 ,owner
 ,table_name
 ,grantor
 ,privilege
from user_tab_privs
union
select
  role
 ,owner
 ,table_name
 ,'ROLE'
 ,privilege
from role_tab_privs
order by 2, 3;

The ROLE_TAB_PRIVS view shows table privileges that have been granted to a role to which the current user has access.

Displaying Object Dependencies

Say you need to drop a table, but before you drop it you want to display any objects that are dependent on the table. For example, you may have a table that has synonyms, views, materialized views, functions, procedures, and triggers that rely on it. Before making the changes you want to review what other objects are dependent on the object you're modifying. You can use the DBA_DEPENDENCIES view to display object dependencies. The following query prompts you for a username and an object name:

select '+' || lpad(' ',level+2) || type || ' ' || owner || '.' || name  dep_tree
from dba_dependencies
connect by prior owner = referenced_owner and prior name = referenced_name
and prior type = referenced_type
start with referenced_owner = upper('&object_owner')
and referenced_name = upper('&object_name')
and owner is not null;

In the output, each object listed has a dependency on the object you entered. Lines are indented to show the dependency of an object on the object in the preceding line:

DEP_TREE
------------------------------------------------------------
+   TRIGGER STAR2.D_COMPANIES_BU_TR1
+   MATERIALIZED VIEW CIA.CB_RAD_COUNTS
+   SYNONYM STAR1.D_COMPANIES
+    SYNONYM CIA.D_COMPANIES
+     MATERIALIZED VIEW CIA.CB_RAD_COUNTS

In this example, the object being analyzed is a table named D_COMPANIES. Several synonyms, materialized views, and one trigger are dependent on this table. For example, the materialized view CB_RAD_COUNTS owned by CIA is dependent on the synonym D_COMPANIES owned by CIA, which in turn is dependent on the D_COMPANIES synonym owned by STAR1.

The DBA_DEPENDENCIES view contains a hierarchical relationship between the OWNER, NAME, and TYPE columns and their referenced column names of REFERENCED_OWNER, REFERENCED_NAME, and REFERENCED_TYPE. Oracle provides a number of constructs to perform hierarchical queries. For example, START WITH and CONNECT BY allow you to identify a starting point in a tree and walk either up or down the hierarchical relationship.

The previous SQL query in this section operates on only one object. If you want to inspect every object in a schema, you can use SQL to generate SQL to create scripts that display all dependencies for a schema's objects. The next section of code does that. For formatting and output, it uses some constructs specific to SQL*Plus, such as setting the page sizes and line size and spooling the output:

UNDEFINE owner
SET LINESIZE 132 PAGESIZE 0 VERIFY OFF FEEDBACK OFF TIMING OFF
SPO dep_dyn_&&owner..sql
SELECT 'SPO dep_dyn_&&owner..txt' FROM DUAL;
--
SELECT
'PROMPT ' || '_____________________________'|| CHR(10) ||
'PROMPT ' || object_type || ': ' || object_name || CHR(10) ||
'SELECT ' || '''' || '+' || '''' || ' ' ||  '|| LPAD(' || '''' || ' '
|| '''' || ',level+3)' || CHR(10) || ' || type || ' || '''' || ' ' || '''' ||
' || owner || ' || '''' || '.' || '''' || ' || name' || CHR(10) ||
' FROM dba_dependencies ' || CHR(10) ||
' CONNECT BY PRIOR owner = referenced_owner AND prior name = referenced_name '
|| CHR(10) ||
' AND prior type = referenced_type ' || CHR(10) ||
' START WITH referenced_owner = ' || '''' || UPPER('&&owner') || '''' || CHR(10) ||
' AND referenced_name = ' || '''' || object_name || '''' || CHR(10) ||
' AND owner IS NOT NULL;'
FROM dba_objects
WHERE owner = UPPER('&&owner')
AND object_type NOT IN ('INDEX','INDEX PARTITION','TABLE PARTITION'),
--
SELECT 'SPO OFF' FROM dual;
SPO OFF
SET VERIFY ON LINESIZE 80 FEEDBACK ON

You should now have a script named dep_dyn_<owner>.sql created in the same directory from which you ran the script. This script contains all the SQL required to display dependencies on objects in the owner you entered. Run the script to display object dependencies. In this example, the owner is CIA:

SQL> @dep_dyn_cia.sql

When the script runs, it spools a file with the format dep_dyn_<owner>.txt. You can open that text file with an operating-system editor to view its contents. Here's a sample of the output from this example:

TABLE: DOMAIN_NAMES
+    FUNCTION STAR2.GET_DERIVED_COMPANY
+    TRIGGER STAR2.DOMAIN_NAMES_BU_TR1
+    SYNONYM CIA_APP.DOMAIN_NAMES

This output shows that the table DOMAIN_NAMES has three objects that are dependent on it: a function, a trigger, and a synonym.

UTLDTREE

Oracle provides a script that builds objects that you can use to display a dependency tree. To install UTLDTREE, run this script in the schema in which you want to analyze dependencies:

SQL> @?/rdbms/admin/utldtree

Now, you can build a dependency tree by executing the DEPTREE_FILL procedure. This procedure accepts three arguments, object type, owner, and object name:

SQL> exec deptree_fill('table','inv_mgmt','inv'),

To display the dependency tree, issue this SQL statement:

SQL> select * from ideptree;

Be aware that when you run the UTLDTREE script, it drops and creates objects in the currently connected user account. Therefore, you should use this utility only in a test or development environment.

Displaying Differences in Schemas

Say you have a test database and a production database, and you want to determine whether there are any object differences between the test database schema and the production database schema. You don't have access to an expensive graphical tool that can show differences between schemas. What SQL techniques can you use to show the object differences between two schemas?

A basic technique for showing the differences between two schemas is as follows:

  1. If the schemas are in two different databases, create database links to point at the two different environments.
  2. Use the MINUS set operator to query the data dictionary views to display differences.

Here's an example that demonstrates how to display schema differences. In this example, you're connected to a central database that has Oracle Net access to two remote databases. You want to view the differences in schemas in the two remote databases. First, you create database links that point to the two different environments. This example uses SQL*Plus variables to define the two different schemas and passwords used to create the database links:

define user1=ccim_dev
define user1_pwd=ccim_pwd
define user2=ccim_prod
define user2_pwd=abc123
define conn1=@db1
define conn2=@db2
create database link db1 connect to &&user1 identified by &&user1_pwd
using 'sb-db5:1521/sb6';
create database link db2 connect to &&user2 identified by &&user2_pwd
using 'db-prod1:1521/scaprd';

The CREATE DATABASE LINK statements use the easy-connect naming method to determine the location of the remote database. The USING clause specifies the database-connection information using this syntax:

'<remote_server>:<port>/<service_name>'

After the database links are created, you run SQL statements that display metadata differences from the data-dictionary views. The next two statements use the MINUS set operator to determine whether there any differences between table names:

prompt ...Tables in db1 NOT IN db2
select table_name
from user_tables&&conn1
minus
select table_name
from user_tables&&conn2;

prompt ...Tables in db2 NOT IN db1 select table_name
from user_tables&&conn2
minus
select table_name
from user_tables&&conn1;

If you want to compare a local schema with a remote schema, then you need only one database link. In this situation, you must also define one of the connection variables to be blank:

define conn2=''

Now you can connect as a local user in your database and compare a remote schema to a local schema.

If you want to compare objects in two schemas in the same database, then you have to modify the scripts to include an OWNER and use the DBA or ALL data-dictionary view (instead of USER).

Listed next is a more complex example of comparing two schemas' objects. The following script compares several data-dictionary views for differences in metadata:

spo diff.txt

prompt Default or temp tablespace in db1 NOT IN db2 select default_tablespace, temporary_tablespace
from user_users&&conn1
minus
select default_tablespace, temporary_tablespace
from user_users&&conn2;

prompt Default or temp tablespace in db2 NOT IN db1 select default_tablespace, temporary_tablespace
from user_users&&conn2
minus
select default_tablespace, temporary_tablespace
from user_users&&conn1;

prompt Tablespace quotas in db1 NOT IN db2 select tablespace_name, max_bytes
from user_ts_quotas&&conn1
minus
select tablespace_name, max_bytes
from user_ts_quotas&&conn2;

prompt Tablespace quotas in db2 NOT IN db1 select tablespace_name, max_bytes
from user_ts_quotas&&conn2
minus
select tablespace_name, max_bytes
from user_ts_quotas&&conn1;

prompt Objects in db1 NOT IN db2 select object_name, object_type
from user_objects&&conn1
minus
select object_name, object_type
from user_objects&&conn2 order by 2;

prompt Objects in db2 NOT IN db1 select object_name, object_type
from user_objects&&conn2
minus
select object_name, object_type
from user_objects&&conn1 order by 2;

prompt Tables in db1 NOT IN db2 select table_name
from user_tables&&conn1
minus
select table_name
from user_tables&&conn2;

prompt Tables in db2 NOT IN db1 select table_name
from user_tables&&conn2
minus
select table_name
from user_tables&&conn1;

prompt Indexes in db2 NOT IN db1 select table_name, index_name, index_type, uniqueness
from user_indexes&&conn2
minus
select table_name, index_name, index_type, uniqueness
from user_indexes&&conn1 order by 1, 2;

prompt Table columns db1 NOT IN db2 select table_name, column_name
from user_tab_columns&&conn1
minus
select table_name, column_name
from user_tab_columns&&conn2 order by 1,2;

prompt Table columns in db2 NOT IN db1 select table_name, column_name
from user_tab_columns&&conn2
minus
select table_name, column_name
from user_tab_columns&&conn1 order by 1,2;

spo off;

This script is just a sample of what you can do with data-dictionary views to report on metadata differences between schemas. The script doesn't include every possible type of check. Rather, it includes enough to give you an example of how to find the most common types of differences that developers and DBAs look for. A full version of this script is available in the source code section of the Apress website (www.apress.com).

If you have access to a tool such as Enterprise Manager Change Management Pack, then you can use it to display differences between two schemas. A quick Google search shows dozens of tools available for comparing schemas. The purpose of the examples in this section isn't to compete with these tools, but to show that you can quickly create a set of SQL statements that display schema differences. You can easily augment and enhance these statements as required for your environment.

Summary

Sometimes you're handed an old database that has been running for years, and it's up to you to manage and maintain it. In some scenarios, you aren't given any documentation regarding the users and objects in the database. Even if you're provided with documentation, it may not be accurate or up to date. In this case, the data dictionary quickly becomes your source of documentation. You can use it to extract user information, the physical structure of the database, security information, objects and owners, currently connected users, and so forth.

Oracle provides static and dynamic views in the data dictionary. The static views contain information about the objects in the database. You can use these views to determine which tables are consuming the most space, contain the most rows, have the most extents allocated, and so on. The dynamic-performance views provide a real-time window into events currently transacting in the database. These views provide information about currently connected users, SQL executing, where resources are being consumed, and so on. DBAs use these views extensively to monitor and troubleshoot performance issues.

The book now turns its attention toward specialized Oracle features such as large objects, partitioning, Data Pump, and external tables. These topics are covered in the next several chapters.

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
18.188.216.249