Oracle8 Administrator's Guide Release 8.0 A58397-01 |
Library | Product | Contents | Index

# 17
General Management of Schema Objects

This chapter describes general schema object management issues that fall outside the scope of Chapters 10 through 15, and includes the following topics:

- Creating Multiple Tables and Views in A Single Operation
- Renaming Schema Objects
- Analyzing Tables, Indexes, and Clusters
- Truncating Tables and Clusters
- Enabling and Disabling Triggers
- Managing Integrity Constraints
- Managing Object Dependencies
- Managing Object Name Resolution
- Changing Storage Parameters for the Data Dictionary
- Displaying Information About Schema Objects

## Creating Multiple Tables and Views in A Single Operation

To create schema objects you must have the required privileges for any included operation. For example, to create multiple tables using the CREATE SCHEMA command, you must have the privileges required to create tables.

You can create several tables and views and grant privileges in one operation using the SQL command CREATE SCHEMA. The CREATE SCHEMA command is useful if you want to guarantee the creation of several tables and views and grants in one operation. If an individual table, view or grant fails, the entire statement is rolled back. None of the objects are created, nor are the privileges granted. The following statement creates two tables and a view that joins data from the two tables:

    CREATE SCHEMA AUTHORIZATION scott
        CREATE TABLE dept (
            deptno NUMBER(3,0) PRIMARY KEY,
            dname VARCHAR2(15),
            loc VARCHAR2(25)
        CREATE TABLE emp (
            empno NUMBER(5,0) PRIMARY KEY,
            ename VARCHAR2(15) NOT NULL,
            job VARCHAR2(10),
            mgr NUMBER(5,0),
            hiredate DATE DEFAULT (sysdate),
            sal NUMBER(7,2),
            comm NUMBER(7,2),
            deptno NUMBER(3,0) NOT NULL
            CONSTRAINT dept_fkey REFERENCES dept)
       CREATE VIEW sales_staff AS
            SELECT empno, ename, sal, comm
            FROM emp
            WHERE deptno = 30
            WITH CHECK OPTION CONSTRAINT sales_staff_cnst
            GRANT SELECT ON sales_staff TO human_resources;

The CREATE SCHEMA command does not support Oracle extensions to the ANSI CREATE TABLE and CREATE VIEW commands; this includes the STORAGE clause.

## Renaming Schema Objects

To rename an object, you must own it. You can rename schema objects in either of the following ways:

- drop and re-create the object
- rename the object using the SQL command RENAME

If you drop and re-create an object, all privileges granted for that object are lost. Privileges must be re-granted when the object is re-created. Alternatively, a table, view, sequence, or a private synonym of a table, view, or sequence can be renamed using the RENAME command. When using the RENAME command, grants made for the object are carried forward for the new name. For example, the following statement renames the SALES_STAFF view:

    RENAME sales_staff TO dept_30;

Note: You cannot rename a stored PL/SQL program unit, public synonym, index, or cluster. To rename such an object, you must drop and re-create it.

Before renaming a schema object, consider the following effects:

- All views and PL/SQL program units dependent on a renamed object become invalid, and must be recompiled before next use.
- All synonyms for a renamed object return an error when used.

See Also: For more information about how Oracle manages object dependencies, see "Managing Object Dependencies".

## Analyzing Tables, Indexes, and Clusters

This section describes how to analyze tables, indexes, and clusters, and includes the following topics:

- Using Statistics for Tables, Indexes, and Clusters
- Validating Tables, Indexes, and Clusters
- Listing Chained Rows of Tables and Clusters

You can analyze a table, index, or cluster to gather data about it, or to verify the validity of its storage format. To analyze a table, cluster, or index, you must own the table, cluster, or index or have the ANALYZE ANY system privilege.

These schema objects can also be analyzed to collect or update statistics about specific objects. When a DML statement is issued, the statistics for the referenced objects are used to determine the most efficient execution plan for the statement. This optimization is called "cost-based optimization." The statistics are stored in the data dictionary.

A table, index, or cluster can be analyzed to validate the structure of the object. For example, in rare cases such as hardware or other system failures, an index can become corrupted and not perform correctly. When validating the index, you can confirm that every entry in the index points to the correct row of the associated table. If a schema object is corrupt, you can drop and re-create it.

A table or cluster can be analyzed to collect information about chained rows of the table or cluster. These results are useful in determining whether you have enough room for updates to rows. For example, this information can show whether PCTFREE is set appropriately for the table or cluster.

See Also: For more information about analyzing tables, indexes, and clusters for performance statistics and the optimizer, see Oracle8 Tuning.

### Using Statistics for Tables, Indexes, and Clusters

Statistics about the physical storage characteristics of a table, index, or cluster can be gathered and stored in the data dictionary using the SQL command ANALYZE with the STATISTICS option. Oracle can use these statistics when cost-based optimization is employed to choose the most efficient execution plan for SQL statements accessing analyzed objects. You can also use statistics generated by this command to write efficient SQL statements that access analyzed objects.

You can compute or estimate statistics using the ANALYZE command, with either the COMPUTE STATISTICS or ESTIMATE STATISTICS option:

#####

COMPUTE STATISTICS | When computing statistics, an entire object is scanned to gather data about the object. This data is used by Oracle to compute exact statistics about the object. Slight variances throughout the object are accounted for in these computed statistics. Because an entire object is scanned to gather information for computed statistics, the larger the size of an object, the more work that is required to gather the necessary information.
ESTIMATE STATISTICS | When estimating statistics, Oracle gathers representative information from portions of an object. This subset of information provides reasonable, estimated statistics about the object. The accuracy of estimated statistics depends upon how representative the sampling used by Oracle is. Only parts of an object are scanned to gather information for estimated statistics, so an object can be analyzed quickly. You can optionally specify the number or percentage of rows that Oracle should use in making the estimate.

Note: When calculating statistics for tables or clusters, the amount of temporary space required to perform the calculation is related to the number of rows specified. For COMPUTE STATISTICS, enough temporary space to hold and sort the entire table plus a small overhead for each row is required. For ESTIMATE STATISTICS, enough temporary space to hold and sort the requested sample of rows plus a small overhead for each row is required. For indexes, no temporary space is required for analyzing.

See Also: For more information about the SQL command ANALYZE, see the Oracle8 SQL Reference.

For more information about the data dictionary views containing statistics, see the Oracle8 Reference.

#### Viewing Object Statistics

Whether statistics for an object are computed or estimated, the statistics are stored in the data dictionary. The statistics can be queried using the following data dictionary views:

- USER_INDEXES, ALL_INDEXES, DBA_INDEXES
- USER_TABLES, ALL_TABLES, DBA_TABLES
- USER_TAB_COLUMNS, ALL_TAB_COLUMNS, DBA_TAB_COLUMNS

  Note: Rows in these views contain entries in the statistics columns only for indexes, tables, and clusters for which you have gathered statistics. The entries are updated for an object each time you ANALYZE the object.

##### Table Statistics

You can gather the following statistics on a table:

Note: The * symbol indicates that the numbers will always be an exact value when computing statistics.

- number of rows
- number of blocks that have been used *
- number of blocks never used
- average available free space
- number of chained rows
- average row length
- number of distinct values per column
- the second smallest value per column *
- the second largest value per column *

  Note: Statistics for all indexes associated with a table are automatically gathered when the table is analyzed.

##### Index Statistics

You can gather the following statistics on an index:

- index level *
- number of leaf blocks
- number of distinct keys
- average number of leaf blocks/key
- average number of data blocks/key
- clustering factor
- minimum key value *
- maximum key value*

##### Cluster Statistics

The only statistic that can be gathered for a cluster is the average cluster key chain length; this statistic can be estimated or computed. Statistics for tables in a cluster and all indexes associated with the cluster's tables (including the cluster key index) are automatically gathered when the cluster is analyzed for statistics.

Note: If the data dictionary currently contains statistics for the specified object when an ANALYZE statement is issued, the new statistics replace the old statistics in the data dictionary.

#### Computing Statistics

The following statement computes statistics for the EMP table:

    ANALYZE TABLE emp COMPUTE STATISTICS;

The following query estimates statistics on the EMP table, using the default statistical sample of 1064 rows:

    ANALYZE TABLE emp ESTIMATE STATISTICS;

To specify the statistical sample that Oracle should use, include the SAMPLE option with the ESTIMATE STATISTICS option. You can specify an integer that indicates either a number of rows or index values, or a percentage of the rows or index values in the table. The following statements show examples of each option:

    ANALYZE TABLE emp
       ESTIMATE STATISTICS
          SAMPLE 2000 ROWS;
    ANALYZE TABLE emp
       ESTIMATE STATISTICS
          SAMPLE 33 PERCENT;

In either case, if you specify a percentage greater than 50, or a number of rows or index values that is greater than 50% of those in the object, Oracle computes the exact statistics, rather than estimating.

#### Removing Statistics for a Schema Object

You can remove statistics for a table, index, or cluster from the data dictionary using the ANALYZE command with the DELETE STATISTICS option. For example, you might want to delete statistics for an object if you do not want cost-based optimization to be used for statements regarding the object. The following statement deletes statistics for the EMP table from the data dictionary:

    ANALYZE TABLE emp DELETE STATISTICS;

#### Shared SQL and Analyzing Statistics

Analyzing a table, cluster, or index can affect current shared SQL statements, which are statements currently in the shared pool. Whenever an object is analyzed to update or delete statistics, all shared SQL statements that reference the analyzed object are flushed from memory so that the next execution of the statement can take advantage of the new statistics.

You can call the following procedures:

#####

DBMS_UTILITY.-ANALYZE_SCHEMA() | This procedure takes two arguments: the name of a schema and an analysis method ('COMPUTE', 'ESTIMATE', or 'DELETE'). It gathers statistics on all of the objects in the schema.
DBMS_DDL.- ANALYZE_OBJECTS() | This procedure takes four arguments: the type of an object ('CLUSTER', 'TABLE', or 'INDEX'), the schema of the object, the name of the object, and an analysis method ('COMPUTE', 'ESTIMATE', or 'DELETE'). It gathers statistics on the object.

You should call these procedures periodically to update the statistics.

### Validating Tables, Indexes, and Clusters

To verify the integrity of the structure of a table, index, cluster, or snapshot, use the ANALYZE command with the VALIDATE STRUCTURE option. If the structure is valid, no error is returned. However, if the structure is corrupt, you receive an error message. If a table, index, or cluster is corrupt, you should drop it and re-create it. If a snapshot is corrupt, perform a complete refresh and ensure that you have remedied the problem; if not, drop and re-create the snapshot.

The following statement analyzes the EMP table:

    ANALYZE TABLE emp VALIDATE STRUCTURE;

You can validate an object and all related objects by including the CASCADE option. The following statement validates the EMP table and all associated indexes:

    ANALYZE TABLE emp VALIDATE STRUCTURE CASCADE;

### Listing Chained Rows of Tables and Clusters

You can look at the chained and migrated rows of a table or cluster using the ANALYZE command with the LIST CHAINED ROWS option. The results of this command are stored in a specified table created explicitly to accept the information returned by the LIST CHAINED ROWS option.

To create an appropriate table to accept data returned by an ANALYZE... LIST CHAINED ROWS statement, use the UTLCHAIN.SQL script provided with Oracle. The UTLCHAIN.SQL script creates a table named CHAINED_ROWS in the schema of the user submitting the script.

After a CHAINED_ROWS table is created, you can specify it when using the ANALYZE command. For example, the following statement inserts rows containing information about the chained rows in the EMP_DEPT cluster into the CHAINED_ROWS table:

    ANALYZE CLUSTER emp_dept LIST CHAINED ROWS INTO chained_rows;

See Also: The name and location of the UTLCHAIN.SQL script are operating system-dependent; see your operating system-specific Oracle documentation.

For more information about reducing the number of chained and migrated rows in a table or cluster, see Oracle8 Tuning.

## Truncating Tables and Clusters

You can delete all rows of a table or all rows in a group of clustered tables so that the table (or cluster) still exists, but is completely empty. For example, you may have a table that contains monthly data, and at the end of each month, you need to empty it (delete all rows) after archiving its data.

To delete all rows from a table, you have the following three options:

1. Using the DELETE command

   You can delete the rows of a table using the DELETE command. For example, the following statement deletes all rows from the EMP table:

          DELETE FROM emp;

    Using the DROP and CREATE commands

    You can drop a table and then re-create the table. For example, the following statements drop and then re-create the EMP table:

          DROP TABLE emp;
          CREATE TABLE emp ( . . . );

    Using TRUNCATE

    You can delete all rows of the table using the SQL command TRUNCATE. For example, the following statement truncates the EMP table:

          TRUNCATE TABLE emp;

#### Using DELETE

If there are many rows present in a table or cluster when using the DELETE command, significant system resources are consumed as the rows are deleted. For example, CPU time, redo log space, and rollback segment space from the table and any associated indexes require resources. Also, as each row is deleted, triggers can be fired. The space previously allocated to the resulting empty table or cluster remains associated with that object.

#### Using DROP and CREATE

When dropping and re-creating a table or cluster, all associated indexes, integrity constraints, and triggers are also dropped, and all objects that depend on the dropped table or clustered table are invalidated. Also, all grants for the dropped table or clustered table are dropped.

#### Using TRUNCATE

Using the TRUNCATE command provides a fast, efficient method for deleting all rows from a table or cluster. A TRUNCATE statement does not generate any rollback information and it commits immediately; it is a DDL statement and cannot be rolled back. A TRUNCATE statement does not affect any structures associated with the table being truncated (constraints and triggers) or authorizations. A TRUNCATE statement also specifies whether space currently allocated for the table is returned to the containing tablespace after truncation.

You can truncate any table or cluster in the user's associated schema. Also, any user that has the DROP ANY TABLE system privilege can truncate a table or cluster in any schema.

Before truncating a table or clustered table containing a parent key, all referencing foreign keys in different tables must be disabled. A self-referential constraint does not have to be disabled.

As a TRUNCATE statement deletes rows from a table, triggers associated with the table are not fired. Also, a TRUNCATE statement does not generate any audit information corresponding to DELETE statements if auditing is enabled. Instead, a single audit record is generated for the TRUNCATE statement being issued.

A hash cluster cannot be truncated. Also, tables within a hash or index cluster cannot be individually truncated; truncation of an index cluster deletes all rows from all tables in the cluster. If all the rows must be deleted from an individual clustered table, use the DELETE command or drop and re-create the table.

The REUSE STORAGE or DROP STORAGE options of the TRUNCATE command control whether space currently allocated for a table or cluster is returned to the containing tablespace after truncation. The default option, DROP STORAGE, reduces the number of extents allocated to the resulting table to the original setting for MINEXTENTS. Freed extents are then returned to the system and can be used by other objects.

Alternatively, the REUSE STORAGE option specifies that all space currently allocated for the table or cluster remains allocated to it. For example, the following statement truncates the EMP_DEPT cluster, leaving all extents previously allocated for the cluster available for subsequent inserts and deletes:

    TRUNCATE CLUSTER emp_dept REUSE STORAGE;

The REUSE or DROP STORAGE option also applies to any associated indexes. When a table or cluster is truncated, all associated indexes are also truncated. Also note that the storage parameters for a truncated table, cluster, or associated indexes are not changed as a result of the truncation.

See Also: See Chapter 22, Auditing Database Use, for information about auditing.

## Enabling and Disabling Triggers

This section describes database trigger management, and includes the following topics:

- Enabling Triggers
- Disabling Triggers

Oracle enables you to define procedures, called database triggers, that are implicitly executed when an INSERT, UPDATE, or DELETE statement is issued against an associated table.

A trigger can be in either of two distinct modes:

#####

enabled | An enabled trigger executes its trigger body if a triggering statement is issued and the trigger restriction, if any, evaluates to TRUE.
disabled | A disabled trigger does not execute its trigger body, even if a triggering statement is issued and the trigger restriction (if any) evaluates to TRUE.

To enable or disable triggers using the ALTER TABLE command, you must own the table, have the ALTER object privilege for the table, or have the ALTER ANY TABLE system privilege. To enable or disable an individual trigger using the ALTER TRIGGER command, you must own the trigger or have the ALTER ANY TRIGGER system privilege.

### Enabling Triggers

You enable a disabled trigger using the ALTER TRIGGER command with the ENABLE option. To enable the disabled trigger named REORDER on the INVENTORY table, enter the following statement:

    ALTER TRIGGER reorder ENABLE;

To enable all triggers defined for a specific table, use the ALTER TABLE command with the ENABLE clause and ALL TRIGGERS option. To enable all triggers defined for the INVENTORY table, enter the following statement:

    ALTER TABLE inventory
        ENABLE ALL TRIGGERS;

### Disabling Triggers

You may want to temporarily disable a trigger if one of the following conditions is true:

- An object that the trigger references is not available.
- You have to perform a large data load and want it to proceed quickly without firing triggers.
- You are loading data into the table to which the trigger applies.

By default, triggers are enabled when first created. You disable a trigger using the ALTER TRIGGER command with the DISABLE option. To disable the trigger REORDER on the INVENTORY table, enter the following statement:

    ALTER TRIGGER reorder DISABLE;

You can disable all triggers associated with a table at the same time using the ALTER TABLE command with the DISABLE clause and ALL TRIGGERS option. For example, to disable all triggers defined for the INVENTORY table, enter the following statement:

    ALTER TABLE inventory
        DISABLE ALL TRIGGERS;

## Managing Integrity Constraints

Integrity constraints are rules or statements about data in a database. Constraints check data as it is entered or updated in the database and prevent data that does not conform to the constraint's rule from being entered. Constraints can guarantee uniqueness, maintain master-detail relationships, check for compliance with an expression, or maintain that NULLs can not be entered.

These rules or statements are always true when the constraint is enabled and validated. However, the statement may or may not be true when the constraint is disabled (or "enabled novalidate") because data in violation of the integrity constraint can be in the database.The following sections explain the mechanisms and procedures for managing integrity constraints:

- Integrity Constraint States
- Deferring Constraint Checks
- Managing Constraints That Have Associated Indexes
- Disabling, Enable Novalidating and Enabling Integrity Constraints Upon Definition
- Enabling and Disabling Existing Integrity Constraints
- Dropping Integrity Constraints
- Reporting Constraint Exceptions

See Also: You can identify exceptions to a specific integrity constraint while attempting to enable the constraint. See "Reporting Constraint Exceptions".

### Integrity Constraint States

An integrity constraint defined on a table can be in one of three states:

#####

disabled | When a constraint is disabled, the rule defined by the constraint is not enforced on the data values in the columns included in the constraint; however, the definition of the constraint is retained in the data dictionary. This mode is useful when you are performing a data warehouse rollup or load and you want to speed up the load process.
enable novalidated | A table with enable novalidated constraints can contain invalid data, but it is not possible to add new invalid data to it. Useful as an intermediate state before validating the data in the table using enable validate. This ensures no new data can violate the constraint, and no locks are held when taking constraints from enable no validate to enable validate. This mode is useful when you don't want to enable the constraint to check for exceptions, for example, after a data warehouse load.
enabled and validated | An enabled constraint is enforced and known to be valid (validity of table data is checked). The definition of the constraint is stored in the data dictionary. This is the normal operational state for constraint processing. This state is useful for preventing invalid data entry during regular OLTP processing.

#### Disabling Constraints

To enforce the rules defined by integrity constraints, the constraints should always be enabled. However, you may wish to temporarily disable the integrity constraints of a table for the following performance reasons:

- when loading large amounts of data into a table
- when performing batch operations that make massive changes to a table (for example, changing every employee's number by adding 1000 to the existing number)
- when importing or exporting one table at a time

In all three cases, temporarily disabling integrity constraints can improve the performance of the operation, especially in data warehouse configurations.

It is possible to enter data that violates a constraint while that constraint is disabled. Thus, you should always enable the constraint after completing any of the operations listed in the bullets above.

#### Enabling Constraints Novalidate

When a constraint is in the enable novalidated state, all subsequent statements are checked for conformity to the constraint; however, any existing data in the table is not checked. A table with enable novalidated constraints can contain invalid data, but it is not possible to add new invalid data to it. Enabling constraints in the novalidated state is most useful in data warehouse configurations that are uploading valid OLTP data.

Enabling a constraint does not require validation. Enabling a constraint novalidate is much faster than enabling and validating a constraint. Also, validating a constraint that is already enabled does not require any DML locks during validation (unlike validating a previously disabled constraint). Enforcement guarantees that no violations are introduced during the validation. Hence, enabling without validating enables you to reduce the downtime typically associated with enabling a constraint.

#### Enabling Constraints

While a constraint is enabled, no row violating the constraint can be inserted into the table. However, while the constraint is disabled such a row can be inserted; this row is known as an exception to the constraint. If the constraint is in the enable novalidated state, violations resulting from data entered while the constraint was disabled remain. The rows that violate the constraint must be either updated or deleted in order for the constraint to be put in the enable state.

You can examine all rows violating constraints in the EXCEPTIONS table

See Also: For details about the EXCEPTIONS table, see Oracle8 Reference.

#### Integrity Constraint States: Procedures and Benefits

Using integrity constraint states in the following order can ensure the best benefits:

1. disable state
2. perform the operation (load, export, import)
3. enable novalidated state
4. enable state

Some benefits of using constraints in this order are:

- no locks are held
- all constraints can go to enable state concurrently
- constraint enabling is done in parallel
- concurrent activity on table permitted

### Deferring Constraint Checks

When Oracle checks a constraint, it signals an error if the constraint is not satisfied. You can defer checking the validity of constraints until the end of a transaction.

When you issue the SET CONSTRAINTS statement, the SET CONSTRAINTS mode lasts for the duration of the transaction, or until another SET CONSTRAINTS statement resets the mode.

Note: You cannot issue a SET CONSTRAINT statement inside a trigger.

See Also: For more details about the SET CONSTRAINTS statement, see the Oracle8 SQL Reference.

For general information about constraints, see Oracle8 Concepts.

#### How To Defer Constraint Checks

##### Select Appropriate Data

You may wish to defer constraint checks on UNIQUE and FOREIGN keys if the data you are working with has any of the following characteristics:

- tables are snapshots
- tables that contain a large amount of data being manipulated by another application, which may or may not return the data in the same order
- update cascade operations on FOREIGN keys

When dealing with bulk data being manipulated by outside applications, you can defer checking constraints for validity until the end of a transaction.

##### Ensure Constraints Are Created Deferrable

After you have identified and selected the appropriate tables, make sure the tables' FOREIGN and UNIQUE key constraints are created as deferrable. You can do so by issuing a statement similar to the following:

    CREATE TABLE dept (
         deptno NUMBER PRIMARY KEY,
         dname VARCHAR2 (30)
       );
    CREATE TABLE emp (
         empno NUMBER,
         ename VARCHAR2 (30),
         deptno NUMBER REFERENCES (dept),
         CONSTRAINT epk PRIMARY KEY (empno),
         CONSTRAINT efk FOREIGN KEY (deptno)
    REFERENCES (dept. deptno) DEFERABLE);
    INSERT INTO dept VALUES (10, 'Accounting');
    INSERT INTO dept VALUES (20, 'SALES');
    INSERT INTO emp VALUES (1, 'Corleone', 10);
    INSERT INTO emp VALUES (2, 'Costanza', 20);
    COMMIT;

    SET CONSTRAINT efk DEFERRED;
    UPDATE dept SET deptno = deptno + 10
         WHERE deptno = 20;

    SELECT * from emp ORDER BY deptno;
    EMPNO   ENAME          DEPTNO
    -----   -------------- -------
       1    Corleone       10
       2    Costanza       20
    UPDATE emp SET deptno = deptno + 10
         WHERE deptno = 20;
    SELECT * FROM emp ORDER BY deptno;

    EMPNO   ENAME          DEPTNO
    -----   -------------- -------
       1    Corleone       10
       2    Costanza       30
    COMMIT;

##### Set All Constraints Deferred

Within the application being used to manipulate the data, you must set all constraints deferred before you actually begin processing any data. Use the following DML statement to set all constraints deferred:

    SET CONSTRAINTS ALL DEFERRED;

    Note:

    The SET CONSTRAINTS statement applies only to the current transaction. The defaults specified when you create a constraint remain as long as the constraint exists. The ALTER SESSION SET CONSTRAINTS statement applies for the current session only.

##### Check the Commit (Optional)

You can check for constraint violations before committing by issuing the SET ALL CONSTRAINTS IMMEDIATE statement just before issuing the COMMIT. If there are any problems with a constraint, this statement will fail and the constraint causing the error will be identified. If you commit while constraints are violated, the transaction will be rolled back and you will receive an error message.

### Managing Constraints That Have Associated Indexes

When you create a UNIQUE or PRIMARY key, Oracle checks to see if an existing index can be used to enforce uniqueness for the constraint. If there is no such index, Oracle creates one.

When constraints associated with unique indexes are dropped or disabled, the index is dropped. Oracle can use non-unique indexes to enforce UNIQUE and PRIMARY key constraints. If you allow Oracle to create a UNIQUE index automatically, and constraints associated with UNIQUE index are dropped or disabled, then the index is dropped.

While enabled foreign keys reference a PRIMARY or UNIQUE key, you cannot disable or drop the PRIMARY or UNIQUE key constraint or the index.

Note: Deferrable UNIQUE and PRIMARY keys all must use non-unique indexes. Always create UNIQUE and PRIMARY key constraints in the disabled state. Then create any indexes on the table and enable the constraint. This ensures the index will not be dropped when you disable the constraint.

### Disabling, Enable Novalidating and Enabling Integrity Constraints Upon Definition

When an integrity constraint is defined in a CREATE TABLE or ALTER TABLE statement, it can be enabled, disabled, or enable novalidated by including the ENABLE, DISABLE or ENABLE NOVALIDATE clause in the constraint's definition. If none of these clauses are identified in a constraint's definition, Oracle automatically enables and validates the constraint.

#### Disabling Constraints Upon Definition

The following CREATE TABLE and ALTER TABLE statements both define and disable integrity constraints:

    CREATE TABLE emp (
        empno NUMBER(5) PRIMARY KEY DISABLE,   . . . ;

    ALTER TABLE emp
       ADD PRIMARY KEY (empno) DISABLE;

An ALTER TABLE statement that defines and disables an integrity constraint never fails because of rows of the table that violate the integrity constraint. The definition of the constraint is allowed because its rule is not enforced.

See Also: For more information about constraint exceptions, see "Reporting Constraint Exceptions".

#### Enabling Constraints Upon Definition

The following CREATE TABLE and ALTER TABLE statements both define and enable integrity constraints:

    CREATE TABLE emp (
        empno NUMBER(5) CONSTRAINT emp.pk PRIMARY KEY,   . . . ;
    ALTER TABLE emp
        ADD CONSTRAINT emp.pk PRIMARY KEY (empno);

An ALTER TABLE statement that defines and attempts to enable an integrity constraint may fail because rows of the table may violate the integrity constraint. In this case, the statement is rolled back and the constraint definition is not stored and not enabled.

To enable a UNIQUE key or PRIMARY KEY, which creates an associated index, the owner of the table also needs a quota for the tablespace intended to contain the index, or the UNLIMITED TABLESPACE system privilege.

### Enabling and Disabling Existing Integrity Constraints

You can use the ALTER TABLE command with the ENABLE clause to enable a disabled constraint., or, with the DISABLE clause, to disable an enabled constraint.

#### Disabling Enabled Constraints

The following statements disable integrity constraints:

    ALTER TABLE dept
        DISABLE CONSTRAINT dname_ukey;
    ALTER TABLE dept
        DISABLE PRIMARY KEY,
        DISABLE UNIQUE (dname, loc);

To disable or drop a UNIQUE key or PRIMARY KEY constraint and all dependent FOREIGN KEY constraints in a single step, use the CASCADE option of the DISABLE or DROP clauses. For example, the following statement disables a PRIMARY KEY constraint and any FOREIGN KEY constraints that depend on it:

    ALTER TABLE dept
        DISABLE PRIMARY KEY CASCADE;

#### Enable Novalidating Constraints that are Disabled

Enabling a constraint novalidate only checks new statements for compliance with the constraint; thus, enabling a constraint novalidate is much faster than enabling a constraint because no old data is checked. Also, enabling a constraint that is already enforced does not require any DML locks during validation (unlike validating a disabled constraint). The enabled constraint guarantees that no violations are introduced during validation.

The following statements enable novalidate disabled integrity constraints:

    ALTER TABLE dept
        ENABLE NOVALIDATE CONSTRAINT dname_ukey;
    ALTER TABLE dept
        ENABLE NOVALIDATE PRIMARY KEY,
        ENABLE NOVALIDATE UNIQUE (dname, loc);

To enable or enable novalidate a UNIQUE key or PRIMARY KEY (which creates an associated index), the owner of the table also needs a quota for the tablespace intended to contain the index, or the UNLIMITED TABLESPACE system privilege. If the UNIQUE or PRIMARY key is using an existing index, then no index is created and no quota is required.

When you enable novalidate a constraint you should use non-unique indexes for the UNIQUE and PRIMARY key so that you don't have to create them.

Note: In order to enable an enable novalidated constraint without holding DML locks, each ALTER TABLE ENABLE statement must enable one and only one constraint.

#### Enabling Disabled Constraints

The following statements enable disabled integrity constraints:

    ALTER TABLE dept
        ENABLE CONSTRAINT dname_ukey;
    ALTER TABLE dept
        ENABLE PRIMARY KEY,
        ENABLE UNIQUE (dname, loc);

An ALTER TABLE statement that attempts to enable an integrity constraint may fail because rows of the table may violate the integrity constraint. In this case, the statement is rolled back and the constraint is not enabled.

To enable a UNIQUE key or PRIMARY KEY (which creates an associated index), the owner of the table also needs a quota for the tablespace intended to contain the index, or the UNLIMITED TABLESPACE system privilege.

Note: If you ENABLE NOVALIDATE a constraint before you enable it, no locks will be held during the ENABLE. This allows the following: all constraints are enabled concurrently each constraint is internally parallelized concurrent activity on the table is permitted

### Dropping Integrity Constraints

You can drop an integrity constraint if the rule that it enforces is no longer true, or if the constraint is no longer needed. You can drop the constraint using the ALTER TABLE command with the DROP clause. The following two statements drop integrity constraints:

    ALTER TABLE dept
        DROP UNIQUE (dname, loc);
    ALTER TABLE emp
        DROP PRIMARY KEY,
        DROP CONSTRAINT dept_fkey;

Dropping UNIQUE key and PRIMARY KEY constraints drops the associated indexes. Also, if FOREIGN KEYs reference a UNIQUE or PRIMARY KEY, you must include the CASCADE CONSTRAINTS clause in the DROP statement, or you cannot drop the constraint.

### Reporting Constraint Exceptions

If no exceptions are present when a CREATE TABLE . . . ENABLE. . . or ALTER TABLE . . . ENABLE. . . statement is issued, the integrity constraint is enabled and all subsequent DML statements are subject to the enabled integrity constraints.

If exceptions exist when a constraint is enabled, an error is returned and the integrity constraint remains disabled. When a statement is not successfully executed because integrity constraint exceptions exist, the statement is rolled back. If exceptions exist, you cannot enable the constraint until all exceptions to the constraint are either updated or deleted.

You cannot use the CREATE TABLE statement to determine which rows are in violation. To determine which rows violate the integrity constraint, issue the ALTER TABLE statement with the EXCEPTIONS option in the ENABLE clause. The EXCEPTIONS option places the ROWID, table owner, table name, and constraint name of all exception rows into a specified table.

Note: You must create an appropriate exceptions report table to accept information from the EXCEPTIONS option of the ENABLE clause before enabling the constraint. You can create an exception table by submitting the script UTLEXCPT.SQL, which creates a table named EXCEPTIONS. You can create additional exceptions tables with different names by modifying and re-submitting the script.

The following statement attempts to enable the PRIMARY KEY of the DEPT table, and if exceptions exist, information is inserted into a table named EXCEPTIONS:

    ALTER TABLE dept ENABLE PRIMARY KEY EXCEPTIONS INTO exceptions;

If duplicate primary key values exist in the DEPT table and the name of the PRIMARY KEY constraint on DEPT is SYS_C00610, the following rows might be placed in the table EXCEPTIONS by the previous statement:

    SELECT * FROM exceptions;

    ROWID                OWNER      TABLE_NAME    CONSTRAINT
    ------------------  ---------  --------------  -----------
    AAAAZ9AABAAABvqAAB     SCOTT        DEPT       SYS_C00610
    AAAAZ9AABAAABvqAAG     SCOTT        DEPT       SYS_C00610

A more informative query would be to join the rows in an exception report table and the master table to list the actual rows that violate a specific constraint, as shown in the following example:

    SELECT deptno, dname, loc FROM dept, exceptions
        WHERE exceptions.constraint = 'SYS_C00610'
        AND dept.rowid = exceptions.row_id;

    DEPTNO      DNAME            LOC
    ---------- --------------    -----------
    10         ACCOUNTING        NEW YORK
    10         RESEARCH          DALLAS

All rows that violate a constraint must be either updated or deleted from the table containing the constraint. When updating exceptions, you must change the value violating the constraint to a value consistent with the constraint or a null. After the row in the master table is updated or deleted, the corresponding rows for the exception in the exception report table should be deleted to avoid confusion with later exception reports. The statements that update the master table and the exception report table should be in the same transaction to ensure transaction consistency.

To correct the exceptions in the previous examples, you might issue the following transaction:

    UPDATE dept SET deptno = 20 WHERE dname = 'RESEARCH';
    DELETE FROM exceptions WHERE constraint = 'SYS_C00610';
    COMMIT;

When managing exceptions, the goal is to eliminate all exceptions in your exception report table.

Note: While you are correcting current exceptions for a table with the constraint disabled, other users may issue statements creating new exceptions. You can avoid this by enable novalidating the constraint before you start eliminating exceptions.

See Also: The exact name and location of the UTLEXCPT.SQL script is operating system specific. For more information, see your operating system-specific Oracle documentation.

## Managing Object Dependencies

This section describes the various object dependencies, and includes the following topics:

- Manually Recompiling Views
- Manually Recompiling Procedures and Functions
- Manually Recompiling Packages

First, review Table 17-1, which shows how objects are affected by changes in other objects on which they depend.

##### Table 17-1 Operations that Affect Object Status

Operation | Resulting Status of Object | Resulting Status of Dependent Objects
--- | --- | ---
CREATE table, sequence, synonym | VALID if there are no errors | No change1
ALTER table (ADD column MODIFY column) RENAME table, sequence, synonym, view | VALID if there no errors | INVALID
DROP table, sequence, synonym, view, procedure, function, package | None; the object is dropped | INVALID
CREATE view, procedure2 | VALID if there are no errors; INVALID if there are syntax or authorization errors | No change1
CREATE OR REPLACE view or procedure2 | VALID if there are no error; INVALID if there are syntax or authorization errors | INVALID
REVOKE object privilege3 ON objectTO/FROM user | No change | All objects of user that depend on object are INVALID3
REVOKE object privilege3 ON object TO/FROM PUBLIC | No change | All objects in the database that depend on object are INVALID3
REVOKE system privilege4 TO/FROM user | No change | All objects of user are INVALID4
REVOKE system privilege4 TO/FROM PUBLIC | No change | All objects in the database are INVALID4
1 May cause dependent objects to be made INVALID, if object did not exist earlier. 2 Stand-alone procedures and functions, packages, and triggers. 3 Only DML object privileges, including SELECT, INSERT, UPDATE, DELETE, and EXECUTE; revalidation does not require recompiling. 4 Only DML system privileges, including SELECT, INSERT, UPDATE, DELETE ANY TABLE, and EXECUTE ANY PROCEDURE; revalidation does not require recompiling.

Oracle automatically recompiles an invalid view or PL/SQL program unit the next time it is used. In addition, a user can force Oracle to recompile a view or program unit using the appropriate SQL command with the COMPILE parameter. Forced compilations are most often used to test for errors when a dependent view or program unit is invalid, but is not currently being used. In these cases, automatic recompilation would not otherwise occur until the view or program unit was executed. To identify invalid dependent objects, query the views USER_/ALL_/DBA_OBJECTS.

### Manually Recompiling Views

To recompile a view manually, you must have the ALTER ANY TABLE system privilege or the view must be contained in your schema. Use the ALTER VIEW command with the COMPILE parameter to recompile a view. The following statement recompiles the view EMP_DEPT contained in your schema:

    ALTER VIEW emp_dept COMPILE;

### Manually Recompiling Procedures and Functions

To recompile a procedure manually, you must have the ALTER ANY PROCEDURE system privilege or the procedure must be contained in your schema. Use the ALTER PROCEDURE/FUNCTION command with the COMPILE parameter to recompile a stand-alone procedure or function. The following statement recompiles the stored procedure UPDATE_SALARY contained in your schema:

    ALTER PROCEDURE update_salary COMPILE;

### Manually Recompiling Packages

To recompile a package manually, you must have the ALTER ANY PROCEDURE system privilege or the package must be contained in your schema. Use the ALTER PACKAGE command with the COMPILE parameter to recompile either a package body or both a package specification and body. The following statements recompile just the body, and the body and specification of the package ACCT_MGMT, respectively:

    ALTER PACKAGE acct_mgmt COMPILE BODY;
    ALTER PACKAGE acct_mgmt COMPILE PACKAGE;

## Managing Object Name Resolution

This section describes how Oracle resolves an object name.

1. First. Oracle attempts to qualify the first piece of the name referenced in the SQL statement. For example, in SCOTT.EMP, SCOTT is the first piece. If there is only one piece, the one piece is considered the first piece.
   1. In the current schema, Oracle searches for an object whose name matches the first piece of the object name. If it does not find such an object, it continues with Step b.
   2. If no schema object is found in the current schema, Oracle searches for a public synonym that matches the first piece of the name. If it does not find one, it continues with Step c.
   3. If no public synonym is found, Oracle searches for a schema whose name matches the first piece of the object name. If it finds one, it returns to Step a, now using the second piece of the name as the object to find in the qualified schema. If the second piece does not correspond to a object in the previously qualified schema or there is not a second piece, Oracle returns an error.

      If no schema is found in Step c, the object cannot be qualified and Oracle returns an error.

2. A schema object has been qualified. Any remaining pieces of the name must match a valid part of the found object. For example, if SCOTT.EMP.DEPTNO is the name, SCOTT is qualified as a schema, EMP is qualified as a table, and DEPTNO must correspond to a column (because EMP is a table). If EMP is qualified as a package, DEPTNO must correspond to a public constant, variable, procedure, or function of that package.

When global object names are used in a distributed database, either explicitly or indirectly within a synonym, the local Oracle resolves the reference locally. For example, it resolves a synonym to a remote table's global object name. The partially resolved statement is shipped to the remote database, and the remote Oracle completes the resolution of the object as described here.

## Changing Storage Parameters for the Data Dictionary

This section describes aspects of changing data dictionary storage parameters, and includes the following topics:

- Structures in the Data Dictionary
- Errors that Require Changing Data Dictionary Storage

If your database is very large or contains an unusually large number of objects, columns in tables, constraint definitions, users, or other definitions, the tables that make up the data dictionary might at some point be unable to acquire additional extents. For example, a data dictionary table may need an additional extent, but there is not enough contiguous space in the SYSTEM tablespace. If this happens, you cannot create new objects, even though the tablespace intended to hold the objects seems to have sufficient space. To remedy this situation, you can change the storage parameters of the underlying data dictionary tables to allow them to be allocated more extents, in the same way that you can change the storage settings for user-created segments. For example, you can adjust the values of NEXT or PCTINCREASE for the data dictionary table.

Warning: Exercise caution when changing the storage settings for the data dictionary objects. If you choose inappropriate settings, you could damage the structure of the data dictionary and be forced to re-create your entire database. For example, if you set PCTINCREASE for the data dictionary table USER$ to 0 and NEXT to 2K, that table will quickly reach the maximum number of extents for a segment, and you will not be able to create any more users or roles without exporting, re-creating, and importing the entire database.

### Structures in the Data Dictionary

The following tables and clusters contain the definitions of all the user-created objects in the database:

#####

SEG$ | segments defined in the database (including temporary segments)
OBJ$ | user-defined objects in the database (including clustered tables); indexed by I_OBJ1 and I_OBJ2
UNDO$ | rollback segments defined in the database; indexed by I_UNDO1
FET$ | available free extents not allocated to any segment
UET$ | extents allocated to segments
TS$ | tablespaces defined in the database
FILE$ | files that make up the database; indexed by I_FILE1
FILEXT$ | datafiles with the AUTOEXTEND option set on
TAB$ | tables defined in the database (includes clustered tables); indexed by I_TAB1
CLU$ | clusters defined in the database
IND$ | indexes defined in the database; indexed by I_IND1
ICOL$ | columns that have indexes defined on them (includes individual entries for each column in a composite index); indexed by I_ICOL1
COL$ | columns defined in tables in the database; indexed by I_COL1 and I_COL2
CON$ | constraints defined in the database (includes information on constraint owner); indexed by I_CON1 and I_CON2
CDEF$ | definitions of constraints in CON$; indexed by I_CDEF1, I_CDEF2, and I_CDEF3
CCOL$ | columns that have constraints defined on them (includes individual entries for each column in a composite key); indexed by I_CCOL1
USER$ | users and roles defined in the database; indexed by I_USER1
TSQ$ | tablespace quotas for users (contains one entry for each tablespace quota defined for each user)
C_OBJ# | cluster containing TAB$, CLU$, ICOL$, IND$, and COL$: indexed by I_OBJ#
C_TS# | cluster containing FET$, TS$, and FILE$; indexed by I_TS#
C_USER# | cluster containing USER and TSQ$$; indexed by I_USER#
C_COBJ# | cluster containing CDEF$ and CCOL$; indexed by I_COBJ#

Of all of the data dictionary segments, the following are the most likely to require change:

#####

C_TS# | if the free space in your database is very fragmented
C_OBJ# | if you have many indexes or many columns in your tables
CON$, C_COBJ# | if you use integrity constraints heavily
C_USER# | If you have a large number of users defined in your database

For the clustered tables, you must change the storage settings for the cluster, not for the table.

### Errors that Require Changing Data Dictionary Storage

Oracle returns an error if a user tries to create a new object that requires Oracle to allocate an additional extent to the data dictionary when it is unable to allocate an extent. The error message ORA-1653, "failed to allocate extent of size num in tablespace 'name'" indicates this kind of problem.

If you receive this error message and the segment you were trying to change (such as a table or rollback segment) has not reached the limits specified for it in its definition, check the storage settings for the object that contains its definition.

For example, if you received an ORA-1547 while trying to define a new PRIMARY KEY constraint on a table and there is sufficient space for the index that Oracle must create for the key, check if CON$ or C_COBJ# cannot be allocated another extent; to do this, query DBA_SEGMENTS and consider changing the storage parameters for CON$ or C_COBJ#.

See Also: For more information, see "Example 7: Displaying Segments that Cannot Allocate Additional Extents".

## Displaying Information About Schema Objects

The data dictionary provides many views about the schema objects described in Chapters 10-16. The following list summarizes the views associated with schema objects:

- ALL_OBJECTS, USER_OBJECTS, DBA_OBJECTS
- ALL_CATALOG, USER_CATALOG, DBA_CATALOG
- ALL_TABLES, USER_TABLES, DBA_TABLES
- ALL_TAB_COLUMNS, USER_TAB_COLUMNS, DBA_TAB_COLUMNS
- ALL_TAB_COMMENTS, USER_TAB_COMMENTS
- ALL_COL_COMMENTS, USER_COL_COMMENTS, DBA_COL_COMMENTS
- ALL_VIEWS, USER_VIEWS, DBA_VIEWS
- ALL_INDEXES, USER_INDEXES, DBA_INDEXES
- ALL_IND_COLUMNS, USER_IND_COLUMNS, DBA_IND_COLUMNS
- USER_CLUSTERS, DBA_CLUSTERS
- USER_CLU_COLUMNS, DBA_CLU_COLUMNS
- ALL_SEQUENCES, USER_SEQUENCES, DBA_SEQUENCES
- ALL_SYNONYMS, USER_SYNONYMS, DBA_SYNONYMS
- ALL_DEPENDENCIES, USER_DEPENDENCIES, DBA_DEPENDENCIES

The following data dictionary views contain information about the segments of a database:

- USER_SEGMENTS
- DBA_SEGMENTS

The following data dictionary views contain information about a database's extents:

- USER_EXTENTS
- DBA_EXTENTS
- USER_FREE_SPACE
- DBA_FREE_SPACE

### Dictionary Storage Oracle Packages

Table 17-2 describes packages that are supplied with Oracle to either allow PL/SQL access to some SQL features, or to extend the functionality of the database.

##### Table 17-2 Supplied Packages: Additional Functionality

Procedure | Description
--- | ---
    dbms_space.unused_space
| Returns information about unused space in an object (table, index, or cluster).
    dbms_space.free_blocks
| Returns information about free blocks in an object (table, index, or cluster).
    dbms_session.free_unuse
    d_ user_memory
| Procedure for reclaiming unused memory after performing operations requiring large amounts of memory (where large>100K). This procedure should only be used in cases where memory is at a premium.
    dbms_system.set_sql_tra
    ce_in_session
| Enables `sql_trace` in the session identified by serial number and SID (these values are located in v$session).

The following examples demonstrate ways to display miscellaneous schema objects.

### Example 1: Displaying Schema Objects By Type

The following query lists all of the objects owned by the user issuing the query:

    SELECT object_name, object_type FROM user_objects;

    OBJECT_NAME               OBJECT_TYPE
    -------------------------  -------------------
    EMP_DEPT                   CLUSTER
    EMP                        TABLE
    DEPT                       TABLE
    EMP_DEPT_INDEX             INDEX
    PUBLIC_EMP                 SYNONYM
    EMP_MGR                    VIEW

### Example 2: Displaying Column Information

Column information, such as name, datatype, length, precision, scale, and default data values can be listed using one of the views ending with the _COLUMNS suffix. For example, the following query lists all of the default column values for the EMP and DEPT tables:

    SELECT table_name, column_name, data_default
        FROM user_tab_columns
        WHERE table_name = 'DEPT' OR table_name = 'EMP';

    TABLE_NAME  COLUMN_NAME    DATA_DEFAULT
    ----------   ------------- --------------------
    DEPT         DEPTNO
    DEPT         DNAME
    DEPT         LOC          'NEW YORK'
    EMP          EMPNO
    EMP          ENAME
    EMP          JOB
    EMP          MGR
    EMP          HIREDATE      SYSDATE
    EMP          SAL
    EMP          COMM
    EMP          DEPTNO

Notice that not all columns have user-specified defaults. These columns automatically have NULL as the default.

### Example 3: Displaying Dependencies of Views and Synonyms

When you create a view or a synonym, the view or synonym is based on its underlying base object. The ALL/USER/DBA_DEPENDENCIES data dictionary views can be used to reveal the dependencies for a view and the ALL/USER/DBA_SYNONYMS data dictionary views can be used to list the base object of a synonym. For example, the following query lists the base objects for the synonyms created by the user JWARD:

    SELECT table_owner, table_name, synonym_name
        FROM sys.dba_synonyms
        WHERE owner = 'JWARD';

    TABLE_OWNER             TABLE_NAME   SYNONYM_NAME
    ----------------------  -----------  -----------------
    SCOTT                   DEPT         DEPT
    SCOTT                   EMP          EMP

### Example 4: Displaying General Segment Information

The following query returns the name of each rollback segment, the tablespace that contains each, and the size of each rollback segment:

    SELECT segment_name, tablespace_name, bytes, blocks, extents
        FROM sys.dba_segments
        WHERE segment_type = 'ROLLBACK';

    SEGMENT_NAME  TABLESPACE_NAME     BYTES       BLOCKS     EXTENTS
    ------------ ---------------      ---------   -------    ---------
    RS1          SYSTEM              20480         10         2
    RS2          TS1                 40960         20         3
    SYSTEM       SYSTEM             184320         90         3

### Example 5: Displaying General Extent Information

General information about the currently allocated extents in a database is stored in the DBA_EXTENTS data dictionary view. For example, the following query identifies the extents associated with rollback segments and the size of each of those extents:

    SELECT segment_name, bytes, blocks
       FROM sys.dba_extents
        WHERE segment_type = 'ROLLBACK';

    SEGMENT_NAME      BYTES          BLOCKS
    ---------------   ---------      --------
    RS1               10240           5
    RS1               10240           5
    SYSTEM            51200          25
    SYSTEM            51200          25
    SYSTEM            51200          25

Notice that the RS1 rollback segment is comprised of two extents, both 10K, while the SYSTEM rollback segment is comprised of three equally sized extents of 50K.

### Example 6: Displaying the Free Space (Extents) of a Database

Information about the free extents (extents not allocated to any segment) in a database is stored in the DBA_FREE_SPACE data dictionary view. For example, the following query reveals the amount of free space available via free extents in each tablespace:

    SELECT tablespace_name, file_id, bytes, blocks
        FROM sys.dba_free_space;

    TABLESPACE_NAME        FILE_ID     BYTES       BLOCKS
    -------------------    ---------   --------    ----------
    SYSTEM                 1           8120320      3965
    SYSTEM                 1             10240         5
    TS1                    2          10432512      5094

### Example 7: Displaying Segments that Cannot Allocate Additional Extents

You can also use DBA_FREE_SPACE, in combination with the views DBA_SEGMENTS, DBA_TABLES, DBA_CLUSTERS, DBA_INDEXES, and DBA_ROLLBACK_SEGS, to determine if any other segment is unable to allocate additional extents for data dictionary objects only.

A segment may not be allocated to an extent for any of the following reasons:

- The tablespace containing the segment does not have enough room for the next extent.
- The segment has the maximum number of extents, as recorded in the data dictionary (in SEG.MAX_EXTENTS).
- The segment has the maximum number of extents allowed by the data block size, which is operating system specific.

  Note: While the STORAGE clause value for MAXEXTENTS can be UNLIMITED, data dictionary tables cannot have MAXEXTENTS greater than the allowed block maximum. Thus, data dictionary tables cannot be converted to unlimited format.

The following query returns the names, owners, and tablespaces of all segments that fit any of the above criteria:

    SELECT seg.owner, seg.segment_name,
        seg.segment_type, seg.tablespace_name,
        DECODE(seg.segment_type,
            'TABLE', t.next_extent,
            'CLUSTER', c.next_extent,
            'INDEX', i.next_extent,
            'ROLLBACK', r.next_extent)
    FROM sys.dba_segments seg,
       sys.dba_tables t,
       sys.dba_clusters c,
       sys.dba_indexes i,
       sys.dba_rollback_segs r

    WHERE ((seg.segment_type = 'TABLE'
       AND seg.segment_name = t.table_name
       AND seg.owner = t.owner
       AND NOT EXISTS (SELECT tablespace_name
           FROM dba_free_space free
           WHERE free.tablespace_name = t.tablespace_name
           AND free.bytes >= t.next_extent))
    OR (seg.segment_type = 'CLUSTER'
        AND seg.segment_name = c.cluster_name
        AND seg.owner = c.owner
        AND NOT EXISTS (SELECT tablespace_name
           FROM dba_free_space free
           WHERE free.tablespace_name = c.tablespace_name
           AND free.bytes >= c.next_extent))
    OR (seg.segment_type = 'INDEX'
        AND seg.segment_name = i.index_name
        AND seg.owner = i.owner
        AND NOT EXISTS (SELECT tablespace_name
        FROM dba_free_space free
           WHERE free.tablespace_name = i.tablespace_name
           AND free.bytes >= i.next_extent))
    OR     (seg.segment_type = 'ROLLBACK'
        AND seg.segment_name = r.segment_name
        AND seg.owner = r.owner
        AND NOT EXISTS (SELECT tablespace_name
          FROM dba_free_space free
            WHERE free.tablespace_name = r.tablespace_name
        AND free.bytes >= r.next_extent)))
    OR seg.extents = seg.max_extents OR seg.extents = data_block_size;

    Note:

    When you use this query, replace data_block_size with the data block size for your system.

Once you have identified a segment that cannot allocate additional extents, you can solve the problem in either of two ways, depending on its cause:

- If the tablespace is full, add datafiles to the tablespace.
- If the segment has too many extents, and you cannot increase MAXEXTENTS for the segment, perform the following steps: first, export the data in the segment; second, drop and recreate the segment, giving it a larger INITIAL setting so that it does not need to allocate so many extents; and third, import the data back into the segment.

Prev | Next
| Copyright © 1997 Oracle Corporation. All Rights Reserved. |
Library | Product | Contents | Index