Pro Oracle Database 11g Administration (42 page)

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 227

CHAPTER 10 ■ DATA DICTIONARY BASICS

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***

228

CHAPTER 10 ■ DATA DICTIONARY BASICS

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.

229

CHAPTER 10 ■ DATA DICTIONARY BASICS

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

■
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'

230

CHAPTER 10 ■ DATA DICTIONARY BASICS

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.

231

CHAPTER 10 ■ DATA DICTIONARY BASICS

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

232

CHAPTER 10 ■ DATA DICTIONARY BASICS

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