20.1 About Tables

Tables are the basic unit of data storage in an Oracle Database. Data is stored in rows and columns.

You define a table with a table name, such as employees, and a set of columns. You give each column a column name, such as employee_id, last_name, and job_id; a data type, such as VARCHAR2, DATE, or NUMBER; and a width. The width can be predetermined by the data type, as in DATE. If columns are of the NUMBER data type, define precision and scale instead of width. A row is a collection of column information corresponding to a single record.

You can specify rules for each column of a table. These rules are called integrity constraints. One example is a NOT NULL integrity constraint. This constraint forces the column to contain a value in every row.

You can invoke Transparent Data Encryption to encrypt data before storing it. If users attempt to circumvent the database access control mechanisms by looking inside Oracle data files directly with operating system tools, encryption prevents these users from viewing sensitive data.

Tables can also include virtual columns. A virtual column is like any other table column, except that its value is derived by evaluating an expression. The expression can include columns from the same table, constants, SQL functions, and user-defined PL/SQL functions. You cannot explicitly write to a virtual column.

Some column types, such as LOBs, varrays, and nested tables, are stored in their own segments. LOBs and varrays are stored in LOB segments, while nested tables are stored in storage tables. You can specify a STORAGE clause for these segments that will override storage parameters specified at the table level.

After you create a table, you insert rows of data using SQL statements or using an Oracle bulk load utility. Table data can then be queried, deleted, or updated using SQL.

20.2 Guidelines for Managing Tables

Following guidelines can make the management of your tables easier and can improve performance when creating the table, as well as when loading, updating, and querying the table data.

20.2.1 Design Tables Before Creating Them

Usually, the application developer is responsible for designing the elements of an application, including the tables. Database administrators are responsible for establishing the attributes of the underlying tablespace that will hold the application tables.

Either the DBA or the applications developer, or both working jointly, can be responsible for the actual creation of the tables, depending upon the practices for a site. Working with the application developer, consider the following guidelines when designing tables:

Use descriptive names for tables, columns, indexes, and clusters.

Be consistent in abbreviations and in the use of singular and plural forms of table names and columns.

Document the meaning of each table and its columns with the COMMENT command.

Normalize each table.

Select the appropriate data type for each column.

Consider whether your applications would benefit from adding one or more virtual columns to some tables.

Before creating a table, you should also determine whether to use integrity constraints. Integrity constraints can be defined on the columns of a table to enforce the business rules of your database automatically.

20.2.2 Specify the Type of Table to Create

You can create different types of tables with Oracle Database.

Here are the types of tables that you can create:

Type of Table

Description

Ordinary (heap-organized) table

This is the basic, general purpose type of table which is the primary subject of this chapter. Its data is stored as an unordered collection (heap).

Clustered table

A clustered table is a table that is part of a cluster. A cluster is a group of tables that share the same data blocks because they share common columns and are often used together.

Unlike an ordinary (heap-organized) table, data for an index-organized table is stored in a B-tree index structure in a primary key sorted manner. Besides storing the primary key column values of an index-organized table row, each index entry in the B-tree stores the nonkey column values as well.

Partitioned tables enable your data to be broken down into smaller, more manageable pieces called partitions, or even subpartitions. Each partition can have separate physical attributes, such as compression enabled or disabled, type of compression, physical storage settings, and tablespace, thus providing a structure that can be better tuned for availability and performance. In addition, each partition can be managed individually, which can simplify and reduce the time required for backup and administration.

20.2.3 Specify the Location of Each Table

It is advisable to specify the TABLESPACE clause in a CREATE TABLE statement to identify the tablespace that is to store the new table. For partitioned tables, you can optionally identify the tablespace that is to store each partition.

Ensure that you have the appropriate privileges and quota on any tablespaces that you use. If you do not specify a tablespace in a CREATE TABLE statement, the table is created in your default tablespace.

When specifying the tablespace to contain a new table, ensure that you understand implications of your selection. By properly specifying a tablespace during the creation of each table, you can increase the performance of the database system and decrease the time needed for database administration.

The following situations illustrate how not specifying a tablespace, or specifying an inappropriate one, can affect performance:

If users' objects are created in the SYSTEM tablespace, the performance of the database can suffer, since both data dictionary objects and user objects must contend for the same data files. Users' objects should not be stored in the SYSTEM tablespace. To avoid this, ensure that all users are assigned default tablespaces when they are created in the database.

If application-associated tables are arbitrarily stored in various tablespaces, the time necessary to complete administrative operations (such as backup and recovery) for the data of that application can be increased.

20.2.4 Consider Parallelizing Table Creation

You can use parallel execution when creating tables using a subquery (AS SELECT) in the CREATE TABLE statement. Because multiple processes work together to create the table, performance of the table creation operation is improved.

20.2.5 Consider Using NOLOGGING When Creating Tables

To create a table most efficiently use the NOLOGGING clause in the CREATE TABLE...AS SELECT statement. The NOLOGGING clause causes minimal redo information to be generated during the table creation.

Using the NOLOGGING clause has the following benefits:

Space is saved in the redo log files.

The time it takes to create the table is decreased.

Performance improves for parallel creation of large tables.

The NOLOGGING clause also specifies that subsequent direct loads using SQL*Loader and direct load INSERT operations are not logged. Subsequent DML statements (UPDATE, DELETE, and conventional path insert) are unaffected by the NOLOGGING attribute of the table and generate redo.

If you cannot afford to lose the table after you have created it (for example, you will no longer have access to the data used to create the table) you should take a backup immediately after the table is created. In some situations, such as for tables that are created for temporary use, this precaution may not be necessary.

In general, the relative performance improvement of specifying NOLOGGING is greater for larger tables than for smaller tables. For small tables, NOLOGGING has little effect on the time it takes to create a table. However, for larger tables the performance improvement can be significant, especially when also parallelizing the table creation.

20.2.6 Consider Using Table Compression

As your database grows in size, consider using table compression to save space and improve performance.

20.2.6.1 About Table Compression

Compression saves disk space, reduces memory use in the database buffer cache, and can significantly speed query execution during reads.

Compression has a cost in CPU overhead for data loading and DML. However, this cost is offset by reduced I/O requirements. Because compressed table data stays compressed in memory, compression can also improve performance for DML operations, as more rows can fit in the database buffer cache (and flash cache if it is enabled).

Table compression is completely transparent to applications. It is useful in decision support systems (DSS), online transaction processing (OLTP) systems, and archival systems.

You can specify compression for a tablespace, a table, or a partition. If specified at the tablespace level, then all tables created in that tablespace are compressed by default.

Oracle Database supports several methods of table compression. They are summarized in Table 20-1.

When you use basic table compression, warehouse compression, or archive compression, compression only occurs when data is bulk loaded into a table.

When you use advanced row compression, compression occurs while data is being inserted, updated, or bulk loaded into a table. Operations that permit compression include:

Single-row or array inserts and updates

Inserts and updates are not compressed immediately. When updating an already compressed block, any columns that are not updated usually remain compressed. Updated columns are stored in an uncompressed format similar to any uncompressed block. The updated values are re-compressed when the block reaches a database-controlled threshold. Inserted data is also compressed when the data in the block reaches a database-controlled threshold.

Warehouse compression and archive compression achieve the highest compression levels because they use Hybrid Columnar Compression technology. Hybrid Columnar Compression technology uses a modified form of columnar storage instead of row-major storage. This enables the database to store similar data together, which improves the effectiveness of compression algorithms. For data that is updated, Hybrid Columnar Compression uses more CPU and moves the updated rows to row format so that future updates are faster. Because of this optimization, you should use it only for data that is updated infrequently.

The higher compression levels of Hybrid Columnar Compression are achieved only with data that is direct-path inserted. Conventional inserts and updates are supported, but cause rows to be moved from columnar to row format, and reduce the compression level. You can use Automatic Data Optimization (ADO) policies to move these rows back to the desired level of Hybrid Columnar Compression automatically.

Regardless of the compression method, DELETE operations on a compressed block are identical to DELETE operations on a non-compressed block. Any space obtained on a data block, caused by SQL DELETE operations, is reused by subsequent SQL INSERT operations. With Hybrid Columnar Compression technology, when all the rows in a compression unit are deleted, the space in the compression unit is available for reuse.

Rows inserted without using direct-path insert and updated rows are uncompressed.

Advanced row compression

ROW STORE COMPRESS ADVANCED

Rows are compressed with advanced row compression.

Rows inserted with or without using direct-path insert and updated rows are compressed using advanced row compression.

Warehouse compression (Hybrid Columnar Compression)

COLUMN STORE COMPRESS FOR QUERY [LOW|HIGH]

Rows are compressed with warehouse compression.

This compression method can result in high CPU overhead.

Updated rows and rows inserted without using direct-path insert are stored in row format instead of column format, and thus have a lower compression level.

Archive compression (Hybrid Columnar Compression)

COLUMN STORE COMPRESS FOR ARCHIVE [LOW|HIGH]

Rows are compressed with archive compression.

This compression method can result in high CPU overhead.

Updated rows and rows inserted without using direct-path insert are stored in row format instead of column format, and thus have a lower compression level.

You specify table compression with the COMPRESS clause of the CREATETABLE statement. You can enable compression for an existing table by using these clauses in an ALTERTABLE statement. In this case, only data that is inserted or updated after compression is enabled is compressed. Using the ALTERTABLEMOVE statement also enables compression for data that is inserted and updated, but it compresses existing data as well. Similarly, you can disable table compression for an existing compressed table with the ALTERTABLE...NOCOMPRESS statement. In this case, all data that was already compressed remains compressed, and new data is inserted uncompressed.

The COLUMN STORE COMPRESS FOR QUERY HIGH option is the default data warehouse compression mode. It provides good compression and performance when using Hybrid Columnar Compression on Exadata storage. The COLUMN STORE COMPRESS FOR QUERY LOW option should be used in environments where load performance is critical. It loads faster than data compressed with the COLUMN STORE COMPRESS FOR QUERY HIGH option.

The COLUMN STORE COMPRESS FOR ARCHIVE LOW option is the default archive compression mode. It provides a high compression level and is ideal for infrequently-accessed data. The COLUMN STORE COMPRESS FOR ARCHIVE HIGH option should be used for data that is rarely accessed.

A compression advisor, provided by the DBMS_COMPRESSION package, helps you determine the expected compression level for a particular table with a particular compression method.

This example enables Hybrid Columnar Compression on the table sales_history:

CREATE TABLE sales_history ... COLUMN STORE COMPRESS FOR QUERY;

The table is created with the default COLUMN STORE COMPRESS FOR QUERY HIGH option. This option provides a higher level of compression than basic table compression or advanced row compression. It works well when frequent queries are run against this table and no DML is expected.

Example 20-5 Creating a Table with Archive Compression

The following example enables Hybrid Columnar Compression on the table sales_history:

CREATE TABLE sales_history ... COLUMN STORE COMPRESS FOR ARCHIVE;

The table is created with the default COLUMN STORE COMPRESS FOR ARCHIVE LOW option. This option provides a higher level of compression than basic, advanced row, or warehouse compression. It works well when load performance is critical and data is accessed infrequently. The default COLUMN STORE COMPRESS FOR ARCHIVE LOW option provides a lower level of compression than the COLUMN STORE COMPRESS FOR ARCHIVE HIGH option.

20.2.6.3 Compression and Partitioned Tables

A table can have both compressed and uncompressed partitions, and different partitions can use different compression methods. If the compression settings for a table and one of its partitions do not match, then the partition setting has precedence for the partition.

To change the compression method for a partition, do one of the following:

To change the compression method for new data only, use ALTERTABLE ... MODIFYPARTITION ... COMPRESS ...

To change the compression method for both new and existing data, use either ALTERTABLE ... MOVEPARTITION ... COMPRESS ... or online table redefinition.

When you execute these statements, specify the compression method. For example, run the following statement to change the compression method to advanced row compression for both new and existing data:

ALTER TABLE ... MOVE PARTITION ... ROW STORE COMPRESS ADVANCED...

20.2.6.4 Determining If a Table Is Compressed

In the *_TABLES data dictionary views, compressed tables have ENABLED in the COMPRESSION column.

For partitioned tables, this column is null, and the COMPRESSION column of the *_TAB_PARTITIONS views indicates the partitions that are compressed. In addition, the COMPRESS_FOR column indicates the compression method in use for the table or partition.

20.2.6.6 Changing the Compression Level

You can change the compression level for a partition, table, or tablespace.

For example, suppose a company uses warehouse compression for its sales data, but sales data older than six months is rarely accessed. If the sales data is stored in a table that is partitioned based on the age of the data, then the compression level for the older data can be changed to archive compression to free disk space.

To change the compression level for a partition or subpartition, you can use the following statements:

ALTERTABLE...MOVEPARTITION...ONLINE

ALTERTABLE...MOVESUBPARTITION...ONLINE

These two statements support the ONLINE keyword, which enables DML operations to run uninterrupted on the partition or subpartition that is being moved. These statements also automatically keep all the indexes updated while the partition or subpartition is being moved. You can also use the ALTER TABLE...MODIFY PARTITION statement or online redefinition to change the compression level for a partition.

If a table is not partitioned, then you can use the ALTER TABLE...MOVE...COMPRESS FOR... statement to change the compression level. The ALTER TABLE...MOVE statement does not permit DML statements against the table while the command is running. However, you can also use online redefinition to compress a table, which keeps the table available for queries and DML statements during the redefinition.

To change the compression level for a tablespace, use the ALTER TABLESPACE statement.

20.2.6.7 Adding and Dropping Columns in Compressed Tables

Some restrictions apply when adding columns to a compressed table or dropping columns from a compressed table.

The following restrictions apply when adding columns to compressed tables:

Basic table compression: You cannot specify a default value for an added column.

Advanced row compression, warehouse compression, and archive compression: If a default value is specified for an added column and the table is already populated, then the conditions for optimized add column behavior must be met. These conditions are described in Oracle Database SQL Language Reference.

The following restrictions apply when dropping columns in compressed tables:

20.2.6.8 Exporting and Importing Hybrid Columnar Compression Tables

Hybrid Columnar Compression tables can be imported using the impdp command of the Data Pump Import utility.

By default, the impdp command preserves the table properties, and the imported table is a Hybrid Columnar Compression table. On tablespaces not supporting Hybrid Columnar Compression, the impdp command fails with an error. The tables can also be exported using the expdp command.

You can import the Hybrid Columnar Compression table as an uncompressed table using the TRANSFORM=SEGMENT_ATTRIBUTES:n option clause of the impdp command.

An uncompressed or advanced row-compressed table can be converted to Hybrid Columnar Compression format during import. To convert a non-Hybrid Columnar Compression table to a Hybrid Columnar Compression table, do the following:

Specify default compression for the tablespace using the ALTER TABLESPACE ... SET DEFAULT COMPRESS command.

Override the SEGMENT_ATTRIBUTES option of the imported table during import.

20.2.6.9 Restoring a Hybrid Columnar Compression Table

There may be times when a Hybrid Columnar Compression table must be restored from a backup. The table can be restored to a system that supports Hybrid Columnar Compression, or to a system that does not support Hybrid Columnar Compression.

When restoring a table with Hybrid Columnar Compression to a system that supports Hybrid Columnar Compression, restore the file using Oracle Recovery Manager (RMAN) as usual.

When a Hybrid Columnar Compression table is restored to a system that does not support Hybrid Columnar Compression, you must convert the table from Hybrid Columnar Compression to advanced row compression or an uncompressed format. To restore the table, do the following:

Ensure there is sufficient storage in environment to hold the data in uncompressed or advanced row compression format.

Use RMAN to restore the Hybrid Columnar Compression tablespace.

Complete one of the following actions to convert the table from Hybrid Columnar Compression to advanced row compression or an uncompressed format:

Use the following statement to change the data compression from Hybrid Columnar Compression to ROW STORE COMPRESS ADVANCED:

ALTER TABLE table_name MOVE ROW STORE COMPRESS ADVANCED;

Use the following statement to change the data compression from Hybrid Columnar Compression to NOCOMPRESS:

ALTER TABLE table_name MOVE NOCOMPRESS;

Use the following statement to change each partition to NOCOMPRESS:

ALTER TABLE table_name MOVE PARTITION partition_name NOCOMPRESS;

Change each partition separately.

If DML is required on the partition while it is being moved, then include the ONLINE keyword:

Compression technology uses CPU. Ensure that you have enough available CPU to handle the additional load.

Tables created with basic table compression have the PCT_FREE parameter automatically set to 0 unless you specify otherwise.

20.2.6.11 Packing Compressed Tables

If you use conventional DML on a table compressed with basic table compression or Hybrid Columnar Compression, then all inserted and updated rows are stored uncompressed or in a less-compressed format. To "pack" the compressed table so that these rows are compressed, use an ALTERTABLEMOVE statement.

This operation takes an exclusive lock on the table, and therefore prevents any updates and loads until it completes. If this is not acceptable, then you can use online table redefinition.

When you move a partition or subpartition, you can use the ALTERTABLEMOVE statement to compress the partition or subpartition while still allowing DML operations to run interrupted on the partition or subpartition that is being moved.

20.2.7.1 Table Compression and Enterprise Manager Cloud Control

Enterprise Manager displays several central compression pages that summarize the compression features at the database and tablespace levels and contains links to different compression pages. The Compression pages display summaries of the compressed storage space at the database level and the tablespace level.

On the database level, the Compression Summary for Database page shows the total database size (total size of all the objects, both compressed and uncompressed), the total size of compressed objects in the database, the total size of uncompressed objects in the database and the ratio of the total size of compressed objects to the total database size. This provides you with a general idea on how much storage space within a database is compressed. You can then take action based on the information displayed.

Likewise on the tablespace level, the Compression Summary for Tablespace page shows the total tablespace size (total size of all the objects, both compressed and uncompressed), the total size of compressed objects in the tablespace, the total size of uncompressed objects in the tablespace and the ratio of the total size of compressed objects to the total tablespace size.

You can use the Compression feature to perform the following tasks:

View a summary of the compressed storage space for the top 100 tablespaces at the database level or the top 100 objects at the tablespace level. You can view a summary on how much storage space is compressed within each of top 100 tablespaces that use the most database storage, including the total size of the tablespace, the compressed size of a tablespace, the uncompressed size of tablespace, and the percentage of compressed storage within a tablespace. You can then perform compression tasks based on the information displayed.

View the storage size that is compressed by each compression type for four object types: Table, Index, LOB (Large Objects), and DBFS (Oracle Database File System).

Calculate the compression ratio for a specific object.

Compress an object (tablespace, table, partition or LOB). This allows you to save storage space. You can run the Compression Advisor to ascertain how much space can be saved and then perform the compression action on the object.

View compression advice from the Segment Advisor. You can access a link to the Segment Advisor to compress segments.

20.2.7.2 Viewing the Compression Summary at the Database Level

You can view the Compression Summary information at the database level.

From the Administration menu, choose Storage, then select Compression.

You can view the summary information about the storage compression at the database level, including in the Space Usage section the total database size, the total size of compressed objects in the database, and the ratio of the total size of compressed objects to the total database size, and the uncompressed objects size. Similar information for segment counts is also shown here in the Segment Count section.

You can view the storage size that is used by each compression type for four object types: Table, Index, LOB (Large Objects), and DBFS (Oracle Database File System). Clicking each color in the chart displays a Compression Summary of Segments page, which shows compression information for the top 100 segments by size in the database for a particular object type and compression type.

20.2.7.3 Viewing the Compression Summary at the Tablespace Level

You can view the Compression Summary information at the tablespace level.

From the Administration menu, choose Storage, then select Compression.

In the Top 100 Permanent Tablespaces by Size table, click on the row for the tablespace for which you want to view the compression summary.

Click Show Compression Details.

Enterprise Manager displays the Compression Summary for Top 100 Objects in Tablespace page. From this page, you can view the total tablespace size, the total size of compressed objects in the tablespace, the ratio of the total size of compressed objects to the total tablespace size, and the uncompressed objects size in a tablespace.

You can also view the compressed tablespace storage size by each compression type for four object types: Table, Index, LOB and DBFS. Clicking each color in the chart displays the Compression Summary of Segments dialog box, which shows compression information for the top 100 segments by size in the tablespace for a particular object type and compression type.

Finally, you can view the compression summary for each of the top 100 segments that use the most tablespace storage.

20.2.7.4 Estimating the Compression Ratio

You can run the Compression Advisor to calculate the compression ratio for a specific object.

From the Administration menu, choose Storage, then select Compression.

In the Compression Advice section, click the number that displays in the Segments with Compression Advice field.

Enterprise Manager displays the Segment Advisor Recommendations page. You can use the Automatic Segment Advisor job to detect segment issues within maintenance windows. The recommendations are derived from the most recent runs of automatic and user-scheduled segment advisor jobs.

20.2.7.7 Initiating Automatic Data Optimization on an Object

You can initiate Automatic Data Optimization on an object.

From the Administration menu, choose Storage, then select Compression.

From the Top 100 Permanent Tablespaces by Size table, select a tablespace and click Show Compression Details to view the compression details for the selected tablespace.

Enterprise Manager displays the Compression Summary for Top 100 Objects in Tablespace page.

From the Top 100 Objects by Size table, select an object and click Automatic Data Compression.

Enterprise Manager displays the Edit page for the object where you can initiate Automatic Data Optimization on the object.

20.2.8 Consider Using Segment-Level and Row-Level Compression Tiering

Segment-level compression tiering enables you to specify compression at the segment level within a table. Row-level compression tiering enables you to specify compression at the row level within a table. You can use a combination of these on the same table for fine-grained control over how the data in the table is stored and managed.

As user modifications to segments and rows change over time, it is often beneficial to change the compression level for them. For example, some segments and rows might be modified often for a short period of time after they are added to the database, but modifications might become less frequent over time.

You can use compression tiering to specify which segments and rows are compressed based on rules. For example, you can specify that rows that have not been modified in two weeks are compressed with advanced row compression. You can also specify that segments that have not been modified in six months are compressed with warehouse compression.

The following prerequisites must be met before you can use segment-level and row-level compression tiering:

The HEAT_MAP initialization parameter must be set to ON.

The COMPATIBLE initialization parameter must be set to 12.0.0 or higher.

To use segment-level compression tiering or row-level compression tiering, execute one of the following SQL statements and include an Automatic Data Optimization (ADO) policy that specifies the rules:

This example specifies segment-level compression tiering for the oe.order_items table. Oracle Database compresses segments using warehouse (ARCHIVEHIGH) compression after six months with no modifications to any rows in the segment and no queries accessing any rows in the segment.

20.2.9 Consider Using Attribute-Clustered Tables

An attribute-clustered table is a heap-organized table that stores data in close proximity on disk based on user-specified clustering directives.

Note:

This feature is available starting with Oracle Database 12c Release 1 (12.1.0.2).

The directives are as follows:

The CLUSTERING...BYLINEARORDER directive orders data in a table according to specified columns.

BYLINEARORDER clustering, which is the default, is best when queries qualify the prefix of columns specified in the clustering clause. For example, if queries of sh.sales often specify either a customer ID or both customer ID and product ID, then you could cluster data in the table using the linear column order cust_id, prod_id. Note that the specified columns can be in multiple tables.

The CLUSTERING...BYINTERLEAVEDORDER directive orders data in one or more tables using a special algorithm, similar to a z-order function, that permits multicolumn I/O reduction.

BYINTERLEAVEDORDER clustering is best when queries specify a variety of column combinations. The columns can be in one or more tables. For example, if queries of sh.sales specify different dimensions in different orders, then you could cluster data in the sales table according to columns in these dimensions.

Attribute clustering is available for the following types of operations:

More optimized single block I/O is possible for table lookups when attribute clustering is aligned with common index access. For example, optimized I/O is possible for an index range scan on the leading column you chose for attribute clustering.

You can cluster fact tables based on joined attributes from other tables.

Attribute clustering can improve data compression and in this way indirectly improve table scan costs. When the same values are close to each other on disk, the database can more easily compress them.

Attribute-clustered tables are often used in data warehousing environments, but they are useful in any environment that can benefit from these advantages. Use the CLUSTERING clause in a CREATETABLE SQL statement to create an attribute-clustered table.

20.2.10 Consider Using Zone Maps

A zone is a set of contiguous data blocks on disk. A zone map tracks the minimum and maximum of specified columns for all individual zones.

Note:

This feature is available starting with Oracle Database 12c Release 1 (12.1.0.2).

When a SQL statement contains predicates on columns stored in a zone map, the database compares the predicate values to the minimum and maximum stored in the zone to determine which zones to read during SQL execution. The primary benefit of zone maps is I/O reduction for table scans. I/O is reduced by skipping table blocks that are not needed in the query result. Use the CREATEMATERIALIZEDZONEMAP SQL statement to create a zone map.

Whenever attribute clustering is specified on a table, you can automatically create a zone map on the clustered columns. Due to clustering, minimum and maximum values of the columns are correlated with consecutive data blocks in the attribute-clustered table, which allows for more effective I/O pruning using the associated zone map.

Note:

Zone maps and attribute-clustered tables can be used together or separately.

20.2.11 Consider Storing Tables in the In-Memory Column Store

The In-Memory Column Store is an optional portion of the system global area (SGA) that stores copies of tables, table partitions, and other database objects that is optimized for rapid scans. In the In-Memory Column Store, table data is stored by column rather than row in the SGA.

Note:

This feature is available starting with Oracle Database 12c Release 1 (12.1.0.2).

20.2.12 Consider Using Invisible Columns

You can use invisible column to make changes to a table without disrupting applications that use the table.

20.2.12.1 Understand Invisible Columns

You can make individual table columns invisible. Any generic access of a table does not show the invisible columns in the table.

For example, the following operations do not display invisible columns in the output:

SELECT*FROM statements in SQL

DESCRIBE commands in SQL*Plus

%ROWTYPE attribute declarations in PL/SQL

Describes in Oracle Call Interface (OCI)

You can use a SELECT statement to display output for an invisible column only if you explicitly specify the invisible column in the column list. Similarly, you can insert a value into an invisible column only if you explicitly specify the invisible column in the column list for the INSERT statement. If you omit the column list in the INSERT statement, then the statement can only insert values into visible columns.

You can make a column invisible during table creation or when you add a column to a table, and you can later alter the table to make the same column visible. You can also alter a table to make a visible column invisible.

You might use invisible columns if you want to make changes to a table without disrupting applications that use the table. After you add an invisible column to a table, queries and other operations that must access the invisible column must refer to the column explicitly by name. When you migrate the application to account for the invisible columns, you can make the invisible columns visible.

Virtual columns can be invisible. Also, you can use an invisible column as a partitioning key during table creation.

The following restrictions apply to invisible columns:

The following types of tables cannot have invisible columns:

External tables

Cluster tables

Temporary tables

Attributes of user-defined types cannot be invisible.

Note:

Invisible columns are not the same as system-generated hidden columns. You can make invisible columns visible, but you cannot make hidden columns visible.

20.2.12.2 Invisible Columns and Column Ordering

There are special considerations for invisible columns and column ordering.

The database usually stores columns in the order in which they were listed in the CREATETABLE statement. If you add a new column to a table, then the new column becomes the last column in the table's column order.

When a table contains one or more invisible columns, the invisible columns are not included in the column order for the table. Column ordering is important when all of the columns in a table are accessed. For example, a SELECT*FROM statement displays columns in the table's column order. Because invisible columns are not included in this type of generic access of a table, they are not included in the column order.

When you make an invisible column visible, the column is included in the table's column order as the last column. When you make a visible column invisible, the invisible column is not included in the column order, and the order of the visible columns in the table might be re-arranged.

For example, consider the following table with an invisible column:

CREATE TABLE mytable (a INT, b INT INVISIBLE, c INT);

Because column b is invisible, this table has the following column order:

Column

Column Order

a

1

c

2

Next, make column b visible:

ALTER TABLE mytable MODIFY (b VISIBLE);

When you make column b visible, it becomes the last column in the table's column order. Therefore, the table has the following column order:

Column

Column Order

a

1

c

2

b

3

Consider another example that illustrates column ordering in tables with invisible columns. The following table does not contain any invisible columns:

CREATE TABLE mytable2 (x INT, y INT, z INT);

This table has the following column order:

Column

Column Order

x

1

y

2

z

3

Next, make column y invisible:

ALTER TABLE mytable2 MODIFY (y INVISIBLE);

When you make column y invisible, column y is no longer included in the table's column order, and it changes the column order of column z. Therefore, the table has the following column order:

Column

Column Order

x

1

z

2

Make column y visible again:

ALTER TABLE mytable2 MODIFY (y VISIBLE);

Column y is now last in the table's column order:

Column

Column Order

x

1

z

2

y

3

20.2.13 Consider Encrypting Columns That Contain Sensitive Data

You can encrypt individual table columns that contain sensitive data. Examples of sensitive data include social security numbers, credit card numbers, and medical records. Column encryption is transparent to your applications, with some restrictions.

Although encryption is not meant to solve all security problems, it does protect your data from users who try to circumvent the security features of the database and access database files directly through the operating system file system.

Column encryption uses the Transparent Data Encryption feature of Oracle Database, which requires that you create a keystore to store the master encryption key for the database. The keystore must be open before you can create a table with encrypted columns and before you can store or retrieve encrypted data. When you open the keystore, it is available to all sessions, and it remains open until you explicitly close it or until the database is shut down.

You choose the algorithm to use when you create the table. All encrypted columns in the table use the same algorithm. The default is AES192. The encryption key length is implied by the algorithm name. For example, the AES128 algorithm uses 128-bit keys.

If you plan on encrypting many columns in one or more tables, you may want to consider encrypting an entire tablespace instead and storing these tables in that tablespace. Tablespace encryption, which also uses the Transparent Data Encryption feature but encrypts at the physical block level, can perform better than encrypting many columns. Another reason to encrypt at the tablespace level is to address the following limitations of column encryption:

Certain data types, such as object data types, are not supported for column encryption.

You cannot use the transportable tablespace feature for a tablespace that includes tables with encrypted columns.

20.2.14 Understand Deferred Segment Creation

When you create heap-organized tables in a locally managed tablespace, the database defers table segment creation until the first row is inserted.

In addition, segment creation is deferred for any LOB columns of the table, any indexes created implicitly as part of table creation, and any indexes subsequently explicitly created on the table.

The advantages of this space allocation method are the following:

It saves a significant amount of disk space in applications that create hundreds or thousands of tables upon installation, many of which might never be populated.

It reduces application installation time.

There is a small performance penalty when the first row is inserted, because the new segment must be created at that time.

To enable deferred segment creation, compatibility must be set to 11.2.0 or higher.

The new clauses for the CREATETABLE statement are:

SEGMENTCREATIONDEFERRED

SEGMENTCREATIONIMMEDIATE

These clauses override the default setting of the DEFERRED_SEGMENT_CREATION initialization parameter, TRUE, which defers segment creation. To disable deferred segment creation, set this parameter to FALSE.

Note that when you create a table with deferred segment creation, the new table appears in the *_TABLES views, but no entry for it appears in the *_SEGMENTS views until you insert the first row.

You can verify deferred segment creation by viewing the SEGMENT_CREATED column in *_TABLES, *_INDEXES, and *_LOBS views for nonpartitioned tables, and in *_TAB_PARTITIONS, *_IND_PARTITIONS, and *_LOB_PARTITIONS views for partitioned tables.

Note:

With this new allocation method, it is essential that you do proper capacity planning so that the database has enough disk space to handle segment creation when tables are populated. See "Capacity Planning for Database Objects ".

The following example creates two tables to demonstrate deferred segment creation. The first table uses the SEGMENTCREATIONDEFERRED clause. No segments are created for it initially. The second table uses the SEGMENTCREATIONIMMEDIATE clause and, therefore, segments are created for it immediately.

The following query against USER_SEGMENTS returns two rows for HOURLY_EMPLOYEES, one for each partition, but returns no rows for PART_TIME_EMPLOYEES because segment creation for that table was deferred.

For the HOURLY_EMPLOYEES table, which is partitioned, the segment_created column is N/A because the USER_TABLES view does not provide that information for partitioned tables. It is available from the USER_TAB_PARTITIONS view, shown below.

20.2.15 Materializing Segments

The DBMS_SPACE_ADMIN package includes the MATERIALIZE_DEFERRED_SEGMENTS() procedure, which enables you to materialize segments for tables, table partitions, and dependent objects created with deferred segment creation enabled.

You can add segments as needed, rather than starting with more than you need and using database resources unnecessarily.

The following example materializes segments for the EMPLOYEES table in the HR schema.

20.2.16 Estimate Table Size and Plan Accordingly

Estimate the sizes of tables before creating them. Preferably, do this as part of database planning. Knowing the sizes, and uses, for database tables is an important part of database planning.

You can use the combined estimated size of tables, along with estimates for indexes, undo space, and redo log files, to determine the amount of disk space that is required to hold an intended database. From these estimates, you can make correct hardware purchases.

You can use the estimated size and growth rate of an individual table to better determine the attributes of a tablespace and its underlying data files that are best suited for the table. This can enable you to more easily manage the table disk space and improve I/O performance of applications that use the table.

You cannot merge an exported table into a preexisting table having the same name in a different schema.

You cannot move types and extent tables to a different schema when the original data still exists in the database.

Oracle Database has a limit on the total number of columns that a table (or attributes that an object type) can have. See Oracle Database Reference for this limit.

Further, when you create a table that contains user-defined type data, the database maps columns of user-defined type to relational columns for storing the user-defined type data. This causes additional relational columns to be created. This results in "hidden" relational columns that are not visible in a DESCRIBE table statement and are not returned by a SELECT * statement. Therefore, when you create an object table, or a relational table with columns of REF, varray, nested table, or object type, be aware that the total number of columns that the database actually creates for the table can be more than those you specify.

20.3 Creating Tables

Create tables using the SQL statement CREATE TABLE.

To create a new table in your schema, you must have the CREATE TABLE system privilege. To create a table in another user's schema, you must have the CREATE ANY TABLE system privilege. Additionally, the owner of the table must have a quota for the tablespace that contains the table, or the UNLIMITED TABLESPACE system privilege.

Encryption is defined on one column (ssn), through the Transparent Data Encryption feature of Oracle Database. The keystore must therefore be open for this CREATETABLE statement to succeed.

The photo column is of data type BLOB, which is a member of the set of data types called large objects (LOBs). LOBs are used to store semi-structured data (such as an XML tree) and unstructured data (such as the stream of bits in a color image).

One column is defined as a virtual column (hrly_rate). This column computes the employee's hourly rate as the yearly salary divided by 2,080. See Oracle Database SQL Language Reference for a discussion of rules for virtual columns.

The comments column is a VARCHAR2 column that is larger than 4000 bytes. Beginning with Oracle Database 12c, the maximum size for the VARCHAR2, NVARCHAR2, and RAW data types is increased to 32767 bytes.

To use extended data types, set the MAX_STRING_SIZE initialization parameter to EXTENDED. See Oracle Database Reference for information about setting this parameter.

The status column is invisible.

A COMMENT statement is used to store a comment for the table. You query the *_TAB_COMMENTS data dictionary views to retrieve such comments. See Oracle Database SQL Language Reference for more information.

20.3.2 Creating a Temporary Table

Temporary tables are useful in applications where a result set is to be buffered (temporarily persisted), perhaps because it is constructed by running multiple DML operations.

For example, assume a Web-based airlines reservations application allows a customer to create several optional itineraries. Each itinerary is represented by a row in a temporary table. The application updates the rows to reflect changes in the itineraries. When the customer decides which itinerary she wants to use, the application moves the row for that itinerary to a persistent table.

During the session, the itinerary data is private. At the end of the session, the optional itineraries are dropped.

The definition of a temporary table is visible to all sessions, but the data in a temporary table is visible only to the session that inserts the data into the table.

Use the CREATE GLOBAL TEMPORARY TABLE statement to create a temporary table. The ON COMMIT clause indicates if the data in the table is transaction-specific (the default) or session-specific, the implications of which are as follows:

ON COMMIT Setting

Implications

DELETE ROWS

This creates a temporary table that is transaction specific. A session becomes bound to the temporary table with a transactions first insert into the table. The binding goes away at the end of the transaction. The database truncates the table (delete all rows) after each commit.

PRESERVE ROWS

This creates a temporary table that is session specific. A session gets bound to the temporary table with the first insert into the table in the session. This binding goes away at the end of the session or by issuing a TRUNCATE of the table in the session. The database truncates the table when you terminate the session.

This statement creates a temporary table that is transaction specific:

Indexes can be created on temporary tables. They are also temporary and the data in the index has the same session or transaction scope as the data in the underlying table.

By default, rows in a temporary table are stored in the default temporary tablespace of the user who creates it. However, you can assign a temporary table to another tablespace upon creation of the temporary table by using the TABLESPACE clause of CREATE GLOBAL TEMPORARY TABLE. You can use this feature to conserve space used by temporary tables. For example, if you must perform many small temporary table operations and the default temporary tablespace is configured for sort operations and thus uses a large extent size, these small operations will consume lots of unnecessary disk space. In this case it is better to allocate a second temporary tablespace with a smaller extent size.

The following two statements create a temporary tablespace with a 64 KB extent size, and then a new temporary table in that tablespace.

Unlike permanent tables, temporary tables and their indexes do not automatically allocate a segment when they are created. Instead, segments are allocated when the first INSERT (or CREATETABLEASSELECT) is performed. Therefore, if a SELECT, UPDATE, or DELETE is performed before the first INSERT, then the table appears to be empty.

DDL operations (except TRUNCATE) are allowed on an existing temporary table only if no session is currently bound to that temporary table.

If you rollback a transaction, the data you entered is lost, although the table definition persists.

A transaction-specific temporary table allows only one transaction at a time. If there are several autonomous transactions in a single transaction scope, each autonomous transaction can use the table only as soon as the previous one commits.

Because the data in a temporary table is, by definition, temporary, backup and recovery of temporary table data is not available in the event of a system failure. To prepare for such a failure, you should develop alternative methods for preserving temporary table data.

20.3.3 Parallelizing Table Creation

When you specify the AS SELECT clause to create a table and populate it with data from another table, you can use parallel execution.

The CREATE TABLE...AS SELECT statement contains two parts: a CREATE part (DDL) and a SELECT part (query). Oracle Database can parallelize both parts of the statement. The CREATE part is parallelized if one of the following is true:

A PARALLEL clause is included in the CREATE TABLE...AS SELECT statement

An ALTER SESSION FORCE PARALLEL DDL statement is specified

The query part is parallelized if all of the following are true:

The query includes a parallel hint specification (PARALLEL or PARALLEL_INDEX) or the CREATE part includes the PARALLEL clause or the schema objects referred to in the query have a PARALLEL declaration associated with them.

At least one of the tables specified in the query requires either a full table scan or an index range scan spanning multiple partitions.

If you parallelize the creation of a table, that table then has a parallel declaration (the PARALLEL clause) associated with it. Any subsequent DML or queries on the table, for which parallelization is possible, will attempt to use parallel execution.

The following simple statement parallelizes the creation of a table and stores the result in a compressed format, using table compression:

20.4 Loading Tables

There are several techniques for loading data into tables.

Note:

The default size of the first extent of any new segment for a partitioned table is 8 MB instead of 64 KB. This helps improve performance of inserts and queries on partitioned tables. Although partitioned tables will start with a larger initial size, once sufficient data is inserted, the space consumption will be the same as in previous releases. You can override this default by setting the INITIAL size in the storage clause for the table. This new default only applies to table partitions and LOB partitions.

20.4.1 Methods for Loading Tables

There are several means of inserting or initially loading data into your tables.

Most commonly used are the following:

Method

Description

SQL*Loader

This Oracle utility program loads data from external files into tables of an Oracle Database.

Starting with Oracle Database 12c, SQL*Loader supports express mode. SQL*Loader express mode eliminates the need for a control file. Express mode simplifies loading data from external files. With express mode, SQL*Loader attempts to use the external table load method. If the external table load method is not possible, then SQL*Loader attempts to use direct path. If direct path is not possible, then SQL*Loader uses conventional path.

SQL*Loader express mode automatically identifies the input datatypes based on the table column types and controls parallelism. SQL*Loader uses defaults to simplify usage, but you can override many of the defaults with command line parameters. You optionally can specify the direct path or the conventional path load method instead of using express mode.

The MERGE statement enables you to insert rows into or update rows of a table, by selecting rows from another existing table. If a row in the new data corresponds to an item that already exists in the table, then an UPDATE is performed, else an INSERT is performed.

Note:

Only a few details and examples of inserting data into tables are included in this book. Oracle documentation specific to data warehousing and application development provide more extensive information about inserting and manipulating data in tables. See:

20.4.2.1 About Direct-Path INSERT

During conventional INSERT operations, the database reuses free space in the table, interleaving newly inserted data with existing data. During such operations, the database also maintains referential integrity constraints.

During direct-path INSERT operations, the database appends the inserted data after existing data in the table. Data is written directly into data files, bypassing the buffer cache. Free space in the table is not reused, and referential integrity constraints are ignored. Direct-path INSERT can perform significantly better than conventional insert.

The database can insert data either in serial mode, where one process executes the statement, or in parallel mode, where multiple processes work together simultaneously to run a single SQL statement. The latter is referred to as parallel execution.

The following are benefits of direct-path INSERT:

During direct-path INSERT, you can disable the logging of redo and undo entries to reduce load time. Conventional insert operations, in contrast, must always log such entries, because those operations reuse free space and maintain referential integrity.

Direct-path INSERT operations ensure atomicity of the transaction, even when run in parallel mode. Atomicity cannot be guaranteed during parallel direct path loads (using SQL*Loader).

When performing parallel direct path loads, one notable difference between SQL*Loader and INSERT statements is the following: If errors occur during parallel direct path loads with SQL*Loader, the load completes, but some indexes could be marked UNUSABLE at the end of the load. Parallel direct-path INSERT, in contrast, rolls back the statement if errors occur during index update.

Note:

A conventional INSERT operation checks for violations of NOTNULL constraints during the insert. Therefore, if a NOTNULL constraint is violated for a conventional INSERT operation, then the error is returned during the insert. A direct-path INSERT operation checks for violations of NOTNULL constraints before the insert. Therefore, if a NOTNULL constraint is violated for a direct-path INSERT operation, then the error is returned before the insert.

20.4.2.2 How Direct-Path INSERT Works

You can use direct-path INSERT on both partitioned and nonpartitioned tables.

The single process inserts data beyond the current high water mark of the table segment or of each partition segment. (The high-water mark is the level at which blocks have never been formatted to receive data.) When a COMMIT runs, the high-water mark is updated to the new value, making the data visible to users.

20.4.2.2.2 Parallel Direct-Path INSERT into Partitioned Tables

This situation is analogous to serial direct-path INSERT. Each parallel execution server is assigned one or more partitions, with no more than one process working on a single partition.

Each parallel execution server inserts data beyond the current high-water mark of its assigned partition segment(s). When a COMMIT runs, the high-water mark of each partition segment is updated to its new value, making the data visible to users.

20.4.2.2.3 Parallel Direct-Path INSERT into Nonpartitioned Tables

Each parallel execution server allocates a new temporary segment and inserts data into that temporary segment. When a COMMIT runs, the parallel execution coordinator merges the new temporary segments into the primary table segment, where it is visible to users.

20.4.2.3 Loading Data with Direct-Path INSERT

You can load data with direct-path INSERT by using direct-path INSERT SQL statements, inserting data in parallel mode, or by using the Oracle SQL*Loader utility in direct-path mode. A direct-path INSERT can be done in either serial or parallel mode.

20.4.2.3.1 Serial Mode Inserts with SQL Statements

There are various ways to activate direct-path INSERT in serial mode with SQL.

You can activate direct-path INSERT in serial mode with SQL in the following ways:

If you are performing an INSERT with a subquery, specify the APPEND hint in each INSERT statement, either immediately after the INSERT keyword, or immediately after the SELECT keyword in the subquery of the INSERT statement.

If you are performing an INSERT with the VALUES clause, specify the APPEND_VALUES hint in each INSERT statement immediately after the INSERT keyword. Direct-path INSERT with the VALUES clause is best used when there are hundreds of thousands or millions of rows to load. The typical usage scenario is for array inserts using OCI. Another usage scenario might be inserts in a FORALL statement in PL/SQL.

If you specify the APPEND hint (as opposed to the APPEND_VALUES hint) in an INSERT statement with a VALUES clause, the APPEND hint is ignored and a conventional insert is performed.

The following is an example of using the APPEND hint to perform a direct-path INSERT:

You cannot query or modify data inserted using direct-path INSERT immediately after the insert is complete. If you attempt to do so, an ORA-12838 error is generated. You must first issue a COMMIT statement before attempting to read or modify the newly-inserted data.

20.4.2.4 Logging Modes for Direct-Path INSERT

Direct-path INSERT lets you choose whether to log redo and undo information during the insert operation.

You specify the logging mode for direct-path INSERT in the following ways:

You can specify logging mode for a table, partition, index, or LOB storage at create time (in a CREATE statement) or subsequently (in an ALTER statement).

If you do not specify either LOGGING or NOLOGGING at these times:

The logging attribute of a partition defaults to the logging attribute of its table.

The logging attribute of a table or index defaults to the logging attribute of the tablespace in which it resides.

The logging attribute of LOB storage defaults to LOGGING if you specify CACHE for LOB storage. If you do not specify CACHE, then the logging attributes defaults to that of the tablespace in which the LOB values resides.

You set the logging attribute of a tablespace in a CREATETABLESPACE or ALTERTABLESPACE statements.

Note:

If the database or tablespace is in FORCELOGGING mode, then direct-path INSERT always logs, regardless of the logging setting.

20.4.2.4.1 Direct-Path INSERT with Logging

In this mode, Oracle Database performs full redo logging for instance and media recovery.

If the database is in ARCHIVELOG mode, then you can archive redo logs to tape. If the database is in NOARCHIVELOG mode, then you can recover instance crashes but not disk failures.

20.4.2.4.2 Direct-Path INSERT without Logging

In this mode, Oracle Database inserts data without redo or undo logging. Instead, the database logs a small number of block range invalidation redo records and periodically updates the control file with information about the most recent direct write.

Direct-path INSERT without logging improves performance. However, if you subsequently must perform media recovery, the invalidation redo records mark a range of blocks as logically corrupt, because no redo data was logged for them. Therefore, it is important that you back up the data after such an insert operation.

You can significantly improve the performance of unrecoverable direct-path inserts by disabling the periodic update of the control files. You do so by setting the initialization parameter DB_UNRECOVERABLE_SCN_TRACKING to FALSE. However, if you perform an unrecoverable direct-path insert with these control file updates disabled, you will no longer be able to accurately query the database to determine if any data files are currently unrecoverable.

20.4.2.5 Additional Considerations for Direct-Path INSERT

20.4.2.5.1 Compressed Tables and Direct-Path INSERT

If a table is created with the basic table compression, then you must use direct-path INSERT to compress table data as it is loaded. If a table is created with advanced row, warehouse, or archive compression, then best compression ratios are achieved with direct-path INSERT.

20.4.2.5.2 Index Maintenance with Direct-Path INSERT

Oracle Database performs index maintenance at the end of direct-path INSERT operations on tables (partitioned or nonpartitioned) that have indexes.

This index maintenance is performed by the parallel execution servers for parallel direct-path INSERT or by the single process for serial direct-path INSERT. You can avoid the performance impact of index maintenance by making the index unusable before the INSERT operation and then rebuilding it afterward.

20.4.2.5.3 Space Considerations with Direct-Path INSERT

Direct-path INSERT requires more space than conventional path INSERT.

All serial direct-path INSERT operations, as well as parallel direct-path INSERT into partitioned tables, insert data above the high-water mark of the affected segment. This requires some additional space.

Parallel direct-path INSERT into nonpartitioned tables requires even more space, because it creates a temporary segment for each degree of parallelism. If the nonpartitioned table is not in a locally managed tablespace in automatic segment-space management mode, you can modify the values of the NEXT and PCTINCREASE storage parameter and MINIMUMEXTENT tablespace parameter to provide sufficient (but not excess) storage for the temporary segments. Choose values for these parameters so that:

The size of each extent is not too small (no less than 1 MB). This setting affects the total number of extents in the object.

The size of each extent is not so large that the parallel INSERT results in wasted space on segments that are larger than necessary.

After the direct-path INSERT operation is complete, you can reset these parameters to settings more appropriate for serial operations.

20.4.2.5.4 Locking Considerations with Direct-Path INSERT

During direct-path INSERT, the database obtains exclusive locks on the table (or on all partitions of a partitioned table).

As a result, users cannot perform any concurrent insert, update, or delete operations on the table, and concurrent index creation and build operations are not permitted. Concurrent queries, however, are supported, but the query will return only the information before the insert operation.

20.4.3 Using Conventional Inserts to Load Tables

During conventional INSERT operations, the database reuses free space in the table, interleaving newly inserted data with existing data. During such operations, the database also maintains referential integrity constraints. Unlike direct-path INSERT operations, conventional INSERT operations do not require an exclusive lock on the table.

Several other restrictions apply to direct-path INSERT operations that do not apply to conventional INSERT operations. See Oracle Database SQL Language Reference for information about these restrictions.

You can perform a conventional INSERT operation in serial mode or in parallel mode using the NOAPPEND hint.

The following is an example of using the NOAPPEND hint to perform a conventional INSERT in serial mode:

The following is an example of using the NOAPPEND hint to perform a conventional INSERT in parallel mode:

INSERT /*+ NOAPPEND PARALLEL */ INTO sales_hist
SELECT * FROM sales;

To run in parallel DML mode, the following requirements must be met:

You must have Oracle Enterprise Edition installed.

You must enable parallel DML in your session. To do this, submit the following statement:

ALTER SESSION { ENABLE | FORCE } PARALLEL DML;

You must meet at least one of the following requirements:

Specify the parallel attribute for the target table, either at create time or subsequently

Specify the PARALLEL hint for each insert operation

Set the database initialization parameter PARALLEL_DEGREE_POLICY to AUTO

20.4.4 Avoiding Bulk INSERT Failures with DML Error Logging

You can avoid bulk INSERT failures by using the DML error logging feature.

20.4.4.1 Inserting Data with DML Error Logging

When you load a table using an INSERT statement with subquery, if an error occurs, the statement is terminated and rolled back in its entirety. This can be wasteful of time and system resources. For such INSERT statements, you can avoid this situation by using the DML error logging feature.

To use DML error logging, you add a statement clause that specifies the name of an error logging table into which the database records errors encountered during DML operations. When you add this error logging clause to the INSERT statement, certain types of errors no longer terminate and roll back the statement. Instead, each error is logged and the statement continues. You then take corrective action on the erroneous rows at a later time.

Execute an INSERT statement and include an error logging clause. This clause:

Optionally references the error logging table that you created. If you do not provide an error logging table name, the database logs to an error logging table with a default name. The default error logging table name is ERR$_ followed by the first 25 characters of the name of the table that is being inserted into.

Optionally includes a tag (a numeric or string literal in parentheses) that gets added to the error log to help identify the statement that caused the errors. If the tag is omitted, a NULL value is used.

Optionally includes a REJECT LIMIT subclause.

This subclause indicates the maximum number of errors that can be encountered before the INSERT statement terminates and rolls back. You can also specify UNLIMITED. The default reject limit is zero, which means that upon encountering the first error, the error is logged and the statement rolls back. For parallel DML operations, the reject limit is applied to each parallel execution server.

Note:

If the statement exceeds the reject limit and rolls back, the error logging table retains the log entries recorded so far.

The following statement inserts rows into the DW_EMPL table and logs errors to the ERR_EMPL table. The tag 'daily_load' is copied to each log entry. The statement terminates and rolls back if the number of errors exceeds 25.

An optional set of columns that contain data from the row that caused the error. The column names match the column names from the table being inserted into (the "DML table").

The number of columns in this part of the error logging table can be zero, one, or more, up to the number of columns in the DML table. If a column exists in the error logging table that has the same name as a column in the DML table, the corresponding data from the offending row being inserted is written to this error logging table column. If a DML table column does not have a corresponding column in the error logging table, the column is not logged. If the error logging table contains a column with a name that does not match a DML table column, the column is ignored.

Because type conversion errors are one type of error that might occur, the data types of the optional columns in the error logging table must be types that can capture any value without data loss or conversion errors. (If the optional log columns were of the same types as the DML table columns, capturing the problematic data into the log could suffer the same data conversion problem that caused the error.) The database makes a best effort to log a meaningful value for data that causes conversion errors. If a value cannot be derived, NULL is logged for the column. An error on insertion into the error logging table causes the statement to terminate.

Table 20-4 lists the recommended error logging table column data types to use for each data type from the DML table. These recommended data types are used when you create the error logging table automatically with the DBMS_ERRLOG package.

Table 20-3 Mandatory Error Description Columns

Column Name

Data Type

Description

ORA_ERR_NUMBER$

NUMBER

Oracle error number

ORA_ERR_MESG$

VARCHAR2(2000)

Oracle error message text

ORA_ERR_ROWID$

ROWID

Rowid of the row in error (for update and delete)

ORA_ERR_OPTYP$

VARCHAR2(2)

Type of operation: insert (I), update (U), delete (D)

Note: Errors from the update clause and insert clause of a MERGE operation are distinguished by the U and I values.

ORA_ERR_TAG$

VARCHAR2(2000)

Value of the tag supplied by the user in the error logging clause

Table 20-4 Error Logging Table Column Data Types

DML Table Column Type

Error Logging Table Column Type

Notes

NUMBER

VARCHAR2(4000)

Able to log conversion errors

CHAR/VARCHAR2(n)

VARCHAR2(4000)

Logs any value without information loss

NCHAR/NVARCHAR2(n)

NVARCHAR2(4000)

Logs any value without information loss

DATE/TIMESTAMP

VARCHAR2(4000)

Logs any value without information loss. Converts to character format with the default date/time format mask

RAW

RAW(2000)

Logs any value without information loss

ROWID

UROWID

Logs any rowid type

LONG/LOB

Not supported

User-defined types

Not supported

20.4.4.3 Creating an Error Logging Table

You can create an error logging table manually, or you can use a PL/SQL package to automatically create one for you.

20.4.4.3.1 Creating an Error Logging Table Automatically

You use the DBMS_ERRLOG package to automatically create an error logging table.

The CREATE_ERROR_LOG procedure creates an error logging table with all of the mandatory error description columns plus all of the columns from the named DML table, and performs the data type mappings shown in Table 20-4.

The following statement creates the error logging table used in the previous example.

Errors resulting from type conversion between a column in a subquery and the corresponding column of the table

Partition mapping errors

Certain MERGE operation errors (ORA-30926: Unable to get a stable set of rows for MERGE operation.)

Some errors are not logged, and cause the DML operation to terminate and roll back. For a list of these errors and for other DML logging restrictions, see the discussion of the error_logging_clause in the INSERT section of Oracle Database SQL Language Reference.

20.4.4.4.1 Space Considerations

Ensure that you consider space requirements before using DML error logging. You require available space not only for the table being inserted into, but also for the error logging table.

20.4.4.4.2 Security

The user who issues the INSERT statement with DML error logging must have INSERT privileges on the error logging table.

20.5 Automatically Collecting Statistics on Tables

The PL/SQL package DBMS_STATS lets you generate and manage statistics for cost-based optimization. You can use this package to gather, modify, view, export, import, and delete statistics. You can also use this package to identify or name statistics that have been gathered.

Formerly, you enabled DBMS_STATS to automatically gather statistics for a table by specifying the MONITORING keyword in the CREATE (or ALTER) TABLE statement. The MONITORING and NOMONITORING keywords have been deprecated and statistics are collected automatically. If you do specify these keywords, they are ignored.

Monitoring tracks the approximate number of INSERT, UPDATE, and DELETE operations for the table since the last time statistics were gathered. Information about how many rows are affected is maintained in the SGA, until periodically (about every three hours) SMON incorporates the data into the data dictionary. This data dictionary information is made visible through the DBA_TAB_MODIFICATIONS,ALL_TAB_MODIFICATIONS, or USER_TAB_MODIFICATIONS views. The database uses these views to identify tables with stale statistics.

The default for the STATISTICS_LEVEL initialization parameter is TYPICAL, which enables automatic statistics collection. Automatic statistics collection and the DBMS_STATS package enable the optimizer to generate accurate execution plans. Setting the STATISTICS_LEVEL initialization parameter to BASIC disables the collection of many of the important statistics required by Oracle Database features and functionality. To disable monitoring of all tables, set the STATISTICS_LEVEL initialization parameter to BASIC. Automatic statistics collection and the DBMS_STATS package enable the optimizer to generate accurate execution plans.

20.6 Altering Tables

You alter a table using the ALTER TABLE statement. To alter a table, the table must be contained in your schema, or you must have either the ALTER object privilege for the table or the ALTER ANY TABLE system privilege.

Note:

Before altering a table, familiarize yourself with the consequences of doing so. The Oracle Database SQL Language Reference lists many of these consequences in the descriptions of the ALTER TABLE clauses.

If a view, materialized view, trigger, domain index, function-based index, check constraint, function, procedure of package depends on a base table, the alteration of the base table or its columns can affect the dependent object. See "Managing Object Dependencies" for information about how the database manages dependencies.

20.6.1 Reasons for Using the ALTER TABLE Statement

There are several reasons to use the ALTER TABLE statement.

You can use the ALTERTABLE statement to perform any of the following actions that affect a table:

Enable or disable integrity constraints or triggers associated with the table

Modify the degree of parallelism for the table

Rename a table

Put a table in read-only mode and return it to read/write mode

Add or modify index-organized table characteristics

Alter the characteristics of an external table

Add or modify LOB columns

Add or modify object type, nested table, or varray columns

Modify table partitions

Starting with Oracle Database 12c, you can perform some operations on more than two partitions or subpartitions at a time, such as split partition and merge partitions operations. See Oracle Database VLDB and Partitioning Guide for information.

Many of these operations are discussed in succeeding sections.

20.6.2 Altering Physical Attributes of a Table

There are several considerations when you alter the physical attributes of a table.

When altering the transaction entry setting INITRANS of a table, note that a new setting for INITRANS applies only to data blocks subsequently allocated for the table.

The storage parameters INITIAL and MINEXTENTS cannot be altered. All new settings for the other storage parameters (for example, NEXT, PCTINCREASE) affect only extents subsequently allocated for the table. The size of the next extent allocated is determined by the current values of NEXT and PCTINCREASE, and is not based on previous values of these parameters.

20.6.3 Moving a Table to a New Segment or Tablespace

You can move a table to a new segment or tablespace to enable compression or to perform data maintenance.

20.6.3.1 About Moving a Table to a New Segment or Tablespace

The ALTER TABLE...MOVE statement enables you to relocate data of a nonpartitioned table or of a partition of a partitioned table into a new segment, and optionally into a different tablespace for which you have quota.

This statement also lets you modify any of the storage attributes of the table or partition, including those which cannot be modified using ALTER TABLE. You can also use the ALTER TABLE...MOVE statement with a COMPRESS clause to store the new segment using table compression.

Tables are usually moved either to enable compression or to perform data maintenance. For example, you can move a table from one tablespace to another.

Most ALTER TABLE...MOVE statements do not permit DML against the table while the statement is executing. The exceptions are the following statements:

ALTERTABLE...MOVEPARTITION...ONLINE

ALTERTABLE...MOVESUBPARTITION...ONLINE

These two statements support the ONLINE keyword, which enables DML operations to run uninterrupted on the partition or subpartition that is being moved. For operations that do not move a partition or subpartition, you can use online redefinition to leave the table available for DML while moving it. See "Redefining Tables Online".

20.6.3.2 Moving a Table

Use the ALTER TABLE...MOVE statement to move a table to a new segment or tablespace.

Moving a table changes the rowids of the rows in the table. This causes indexes on the table to be marked UNUSABLE, and DML accessing the table using these indexes receive an ORA-01502 error. The indexes on the table must be dropped or rebuilt. Likewise, any statistics for the table become invalid, and new statistics should be collected after moving the table.

If the table includes LOB column(s), then this statement can be used to move the table along with LOB data and LOB index segments (associated with this table) which the user explicitly specifies. If not specified, then the default is to not move the LOB data and LOB index segments.

To move a table:

In SQL*Plus, connect as a user with the necessary privileges to alter the table.

20.6.3.3 Moving a Table Partition or Subpartition Online

Use the ALTER TABLE...MOVE PARTITION statement or ALTER TABLE...MOVE SUBPARTITION statement to move a table partition or subpartition, respectively.

When you use the ONLINE keyword with either of these statements, DML operations can continue to run uninterrupted on the partition or subpartition that is being moved. If you do not include the ONLINE keyword, then DML operations are not permitted on the data in the partition or subpartition until the move operation is complete.

When you include the UPDATEINDEXES clause, these statements maintain both local and global indexes during the move. Therefore, using the ONLINE keyword with these statements eliminates the time it takes to regain partition performance after the move by maintaining global indexes and manually rebuilding indexes.

20.6.4 Manually Allocating Storage for a Table

Oracle Database dynamically allocates additional extents for the data segment of a table, as required. However, perhaps you want to allocate an additional extent for a table explicitly. For example, in an Oracle Real Application Clusters environment, an extent of a table can be allocated explicitly for a specific instance.

You can allocate a new extent for a table using the ALTER TABLE...ALLOCATE EXTENT statement.

You can also explicitly deallocate unused space using the DEALLOCATE UNUSED clause of ALTER TABLE. This is described in "Reclaiming Unused Space".

You can increase the length of an existing column, or decrease it, if all existing data satisfies the new length. Beginning with Oracle Database 12c, you can specify a maximum size of 32767 bytes for the VARCHAR2, NVARCHAR2, and RAW data types. Before this release, the maximum size was 4000 bytes for the VARCHAR2 and NVARCHAR2 data types, and 2000 bytes for the RAW data type. To use extended data types, set the MAX_STRING_SIZE initialization parameter to EXTENDED.

You can change a column from byte semantics to CHAR semantics or vice versa. You must set the initialization parameter BLANK_TRIMMING=TRUE to decrease the length of a non-empty CHAR column.

If you are modifying a table to increase the length of a column of data type CHAR, then realize that this can be a time consuming operation and can require substantial additional storage, especially if the table contains many rows. This is because the CHAR value in each row must be blank-padded to satisfy the new column length.

If you modify the visible/invisible property of a column, then you cannot include any other column modification options in the same SQL statement.

Example 20-11 Changing the Length of a Column to a Size Larger Than 4000 Bytes

This example changes the length of the product_description column in the oe.product_information table to 32767 bytes.

If a new column is added to a table, then the column is initially NULL unless you specify the DEFAULT clause. If you specify the DEFAULT clause for a nullable column for some table types, then the default value is stored as metadata, but the column itself is not populated with data. However, subsequent queries that specify the new column are rewritten so that the default value is returned in the result set. This behavior optimizes the resource usage and storage requirements for the operation.

You can add a column with a NOTNULL constraint only if the table does not contain any rows, or you specify a default value.

Note:

If you enable basic table compression on a table, then you can add columns only if you do not specify default values.

If you enable advanced row compression on a table, then you can add columns to that table with or without default values.

If the new column is a virtual column, its value is determined by its column expression. (Note that a virtual column's value is calculated only when it is queried.)

As noted earlier, altering a table column can invalidate dependent objects. However, when you rename a column, the database updates associated data dictionary tables to ensure that function-based indexes and check constraints remain valid.

Oracle Database also lets you rename column constraints. This is discussed in "Renaming Constraints".

Note:

The RENAME TO clause of ALTER TABLE appears similar in syntax to the RENAME COLUMN clause, but is used for renaming the table itself.

20.6.8 Dropping Table Columns

You can drop columns that are no longer needed from a table, including an index-organized table. This provides a convenient means to free space in a database, and avoids your having to export/import data then re-create indexes and constraints.

Note:

You cannot drop all columns from a table, nor can you drop columns from a table owned by SYS. Any attempt to do so results in an error.

20.6.8.1 Removing Columns from Tables

When you issue an ALTER TABLE...DROP COLUMN statement, the column descriptor and the data associated with the target column are removed from each row in the table. You can drop multiple columns with one statement.

The following statements are examples of dropping columns from the hr.admin_emp table. The first statement drops only the sal column:

20.6.8.2 Marking Columns Unused

If you are concerned about the length of time it could take to drop column data from all of the rows in a large table, you can use the ALTER TABLE...SET UNUSED statement.

This statement marks one or more columns as unused, but does not actually remove the target column data or restore the disk space occupied by these columns. However, a column that is marked as unused is not displayed in queries or data dictionary views, and its name is removed so that a new column can reuse that name. In most cases, constraints, indexes, and statistics defined on the column are also removed. The exception is that any internal indexes for LOB columns that are marked unused are not removed.

To mark the hiredate and mgr columns as unused, execute the following statement:

You can later remove columns that are marked as unused by issuing an ALTER TABLE...DROP UNUSED COLUMNS statement. Unused columns are also removed from the target table whenever an explicit drop of any particular column or columns of the table is issued.

The data dictionary views USER_UNUSED_COL_TABS, ALL_UNUSED_COL_TABS, or DBA_UNUSED_COL_TABS can be used to list all tables containing unused columns. The COUNT field shows the number of unused columns in the table.

For external tables, the SETUNUSED statement is transparently converted into an ALTERTABLEDROPCOLUMN statement. Because external tables consist of metadata only in the database, the DROPCOLUMN statement performs equivalently to the SETUNUSED statement.

20.6.8.3 Removing Unused Columns

The ALTER TABLE...DROP UNUSED COLUMNS statement is the only action allowed on unused columns. It physically removes unused columns from the table and reclaims disk space.

In the ALTER TABLE statement that follows, the optional clause CHECKPOINT is specified. This clause causes a checkpoint to be applied after processing the specified number of rows, in this case 250. Checkpointing cuts down on the amount of undo logs accumulated during the drop column operation to avoid a potential exhaustion of undo space.

20.6.8.4 Dropping Columns in Compressed Tables

20.6.9 Placing a Table in Read-Only Mode

You can place a table in read-only mode with the ALTERTABLE...READONLY statement, and return it to read/write mode with the ALTERTABLE...READWRITE statement.

An example of a table for which read-only mode makes sense is a configuration table. If your application contains configuration tables that are not modified after installation and that must not be modified by users, your application installation scripts can place these tables in read-only mode.

To place a table in read-only mode, you must have the ALTERTABLE privilege on the table or the ALTERANYTABLE privilege. In addition, the COMPATIBLE initialization parameter must be set to 11.1.0 or higher.

The following example places the SALES table in read-only mode:

ALTER TABLE SALES READ ONLY;

The following example returns the table to read/write mode:

ALTER TABLE SALES READ WRITE;

When a table is in read-only mode, operations that attempt to modify table data are disallowed. A SELECT column_list ON table_name statement on a table must always return the same data set after a table has been placed in read-only mode.

The following operations are not permitted on a read-only table:

All DML operations on the table or any of its partitions

TRUNCATETABLE

SELECTFORUPDATE

ALTERTABLERENAME/DROPCOLUMN

ALTERTABLESETCOLUMNUNUSED

ALTERTABLEDROP/TRUNCATE/EXCHANGE(SUB)PARTITION

ALTERTABLEUPGRADEINCLUDINGDATA or ALTERTYPECASCADEINCLUDINGTABLEDATA for a type with read-only table dependents

20.7 Redefining Tables Online

You can modify the logical or physical structure of a table.

20.7.1 About Redefining Tables Online

In any database system, it is occasionally necessary to modify the logical or physical structure of a table to improve the performance of queries or DML, accommodate application changes, or Manage storage. You can redefine tables online with the DBMS_REDEFINITION package.

Oracle Database provides a mechanism to make table structure modifications without significantly affecting the availability of the table. The mechanism is called online table redefinition. Redefining tables online provides a substantial increase in availability compared to traditional methods of redefining tables.

When a table is redefined online, it is accessible to both queries and DML during much of the redefinition process. Typically, the table is locked in the exclusive mode only during a very small window that is independent of the size of the table and complexity of the redefinition, and that is completely transparent to users. However, if there are many concurrent DML operations during redefinition, then a longer wait might be necessary before the table can be locked.

Online table redefinition requires an amount of free space that is approximately equivalent to the space used by the table being redefined. More space may be required if new columns are added.

You can perform online table redefinition with the Oracle Enterprise Manager Cloud Control (Cloud Control) Reorganize Objects wizard or with the DBMS_REDEFINITION package.

Note:

To invoke the Reorganize Objects wizard:

On the Tables page of Cloud Control, click in the Select column to select the table to redefine.

Change physical properties of a single table partition or subpartition, including moving it to a different tablespace in the same schema

Starting with Oracle Database 12c, you can move a partition or subpartition online without using online table redefinition. DML operations can continue to run uninterrupted on the partition or subpartition that is being moved. See "Moving a Table to a New Segment or Tablespace".

When your online redefinition operation is not limited to these changes, you must perform online redefinition of the table using multiple steps. The steps include invoking multiple procedures in the DBMS_REDEFINITION package, including the following procedures: CAN_REDEF_TABLE, START_REDEF_TABLE, COPY_TABLE_DEPENDENTS, and FINISH_REDEF_TABLE.

You can use multiple procedures in the DBMS_REDEFINITION package to perform online redefinition of a table.

To redefine a table online using multiple steps:

Choose the redefinition method: by key or by rowid

By key—Select a primary key or pseudo-primary key to use for the redefinition. Pseudo-primary keys are unique keys with all component columns having NOT NULL constraints. For this method, the versions of the tables before and after redefinition should have the same primary key columns. This is the preferred and default method of redefinition.

By rowid—Use this method if no key is available. In this method, a hidden column named M_ROW$$ is added to the post-redefined version of the table. It is recommended that this column be dropped or marked as unused after the redefinition is complete. The final phase of redefinition automatically sets this column unused. You can then use the ALTERTABLE ... DROPUNUSEDCOLUMNS statement to drop it.

You cannot use this method on index-organized tables.

Verify that the table can be redefined online by invoking the CAN_REDEF_TABLE procedure. If the table is not a candidate for online redefinition, then this procedure raises an error indicating why the table cannot be redefined online.

Create an empty interim table (in the same schema as the table to be redefined) with all of the desired logical and physical attributes. If columns are to be dropped, then do not include them in the definition of the interim table. If a column is to be added, then add the column definition to the interim table. If a column is to be modified, then create it in the interim table with the properties that you want.

It is not necessary to create the interim table with all the indexes, constraints, grants, and triggers of the table being redefined, because these will be defined in step 7 when you copy dependent objects.

If you are redefining a partitioned table with the rowid method, then enable row movement on the interim table.

ALTER TABLE ... ENABLE ROW MOVEMENT;

(Optional) If you are redefining a large table and want to improve the performance of the next step by running it in parallel, issue the following statements:

Package constants are provided for specifying the redefinition method. DBMS_REDEFINITION.CONS_USE_PK is used to indicate that the redefinition should be done using primary keys or pseudo-primary keys. DBMS_REDEFINITION.CONS_USE_ROWID is use to indicate that the redefinition should be done using rowids. If this argument is omitted, the default method of redefinition (CONS_USE_PK) is assumed.

Optionally, the columns to be used in ordering rows in the orderby_cols parameter

The partition name or names in the part_name parameter when redefining one partition or multiple partitions of a partitioned table

Because this process involves copying data, it may take a while. The table being redefined remains available for queries and DML during the entire process.

Note:

You can query the DBA_REDEFINITION_OBJECTS view to list the objects currently involved in online redefinition.

If START_REDEF_TABLE fails for any reason, you must call ABORT_REDEF_TABLE, otherwise subsequent attempts to redefine the table will fail.

Copy dependent objects (such as triggers, indexes, materialized view logs, grants, and constraints) and statistics from the table being redefined to the interim table, using one of the following two methods. Method 1 is the preferred method because it is more automatic, but there may be times that you would choose to use method 2. Method 1 also enables you to copy table statistics to the interim table.

Method 1: Automatically Creating Dependent Objects

Use the COPY_TABLE_DEPENDENTS procedure to automatically create dependent objects on the interim table. This procedure also registers the dependent objects. Registering the dependent objects enables the identities of these objects and their copied counterparts to be automatically swapped later as part of the redefinition completion process. The result is that when the redefinition is completed, the names of the dependent objects will be the same as the names of the original dependent objects.

In Oracle9i, you were required to manually create the triggers, indexes, grants, and constraints on the interim table, and there may still be situations where you want to or must do so. In such cases, any referential constraints involving the interim table (that is, the interim table is either a parent or a child table of the referential constraint) must be created disabled. When online redefinition completes, the referential constraint is automatically enabled. In addition, until the redefinition process is either completed or aborted, any trigger defined on the interim table does not execute.

Execute the FINISH_REDEF_TABLE procedure to complete the redefinition of the table. During this procedure, the original table is locked in exclusive mode for a very short time, independent of the amount of data in the original table. However, FINISH_REDEF_TABLE will wait for all pending DML to commit before completing the redefinition.

You can use the dml_lock_timeout parameter in the FINISH_REDEF_TABLE procedure to specify how long the procedure waits for pending DML to commit. The parameter specifies the number of seconds to wait before the procedure ends gracefully. When you specify a non-NULL value for this parameter, you can restart the FINISH_REDEF_TABLE procedure, and it continues from the point at which it timed out. When the parameter is set to NULL, the procedure does not time out. In this case, if you stop the procedure manually, then you must abort the online table redefinition using the ABORT_REDEF_TABLE procedure and start over from step 6.

Wait for any long-running queries against the interim table to complete, and then drop the interim table.

If you drop the interim table while there are active queries running against it, you may encounter an ORA-08103 error ("object no longer exists").

20.7.4.2 Constructing a Column Mapping String

The column mapping string that you pass as an argument to START_REDEF_TABLE contains a comma-delimited list of column mapping pairs.

Each pair has the following syntax:

[expression] column_name

The column_name term indicates a column in the interim table. The optional expression can include columns from the table being redefined, constants, operators, function or method calls, and so on, in accordance with the rules for expressions in a SQL SELECT statement. However, only simple deterministic subexpressions—that is, subexpressions whose results do not vary between one evaluation and the next—plus sequences and SYSDATE can be used. No subqueries are permitted. In the simplest case, the expression consists of just a column name from the table being redefined.

If an expression is present, its value is placed in the designated interim table column during redefinition. If the expression is omitted, it is assumed that both the table being redefined and the interim table have a column named column_name, and the value of that column in the table being redefined is placed in the same column in the interim table.

For example, if the override column in the table being redefined is to be renamed to override_commission, and every override commission is to be raised by 2%, the correct column mapping pair is:

override*1.02 override_commission

If you supply '*' or NULL as the column mapping string, it is assumed that all the columns (with their names unchanged) are to be included in the interim table. Otherwise, only those columns specified explicitly in the string are considered. The order of the column mapping pairs is unimportant.

When mapping columns, you can convert data types, with some restrictions.

If you provide '*' or NULL as the column mapping string, only the implicit conversions permitted by SQL are supported. For example, you can convert from CHAR to VARCHAR2, from INTEGER to NUMBER, and so on.

To perform other data type conversions, including converting from one object type to another or one collection type to another, you must provide a column mapping pair with an expression that performs the conversion. The expression can include the CAST function, built-in functions like TO_NUMBER, conversion functions that you create, and so on.

If the original table being redefined has VPD policies specified for it, then you can use the copy_vpd_opt parameter in the START_REDEF_TABLE procedure to handle these policies during online redefinition.

You can specify the following values for this parameter:

Parameter Value

Description

DBMS_REDEFINITION.CONS_VPD_NONE

Specify this value if there are no VPD policies on the original table. This value is the default.

If this value is specified, and VPD policies exist for the original table, then an error is raised.

DBMS_REDEFINITION.CONS_VPD_AUTO

Specify this value to copy the VPD policies automatically from the original table to the new table during online redefinition.

DBMS_REDEFINITION.CONS_VPD_MANUAL

Specify this value to copy the VPD policies manually from the original table to the new table during online redefinition.

If there are no VPD policies specified for the original table, then specify the default value of DBMS_REDEFINITION.CONS_VPD_NONE for the copy_vpd_opt parameter.

Specify DBMS_REDEFINITION.CONS_VPD_AUTO for the copy_vpd_opt parameter when the column names and column types are the same for the original table and the interim table. To use this value, the column mapping string between original table and interim table must be NULL or '*'. When you use DBMS_REDEFINITION.CONS_VPD_AUTO for the copy_vpd_opt parameter, only the table owner and the user invoking online redefinition can access the interim table during online redefinition.

Specify DBMS_REDEFINITION.CONS_VPD_MANUAL for the copy_vpd_opt parameter when either of the following conditions are true:

There are VPD policies specified for the original table, and there are column mappings between the original table and the interim table.

You want to add or modify VPD policies during online redefinition of the table.

To copy the VPD policies manually, you specify the VPD policies for the interim table before you run the START_REDEF_TABLE procedure. When online redefinition of the table is complete, the redefined table has the modified policies.

20.7.4.4 Creating Dependent Objects Automatically

You use the COPY_TABLE_DEPENDENTS procedure to automatically create dependent objects on the interim table.

You can discover if errors occurred while copying dependent objects by checking the num_errors output argument. If the ignore_errors argument is set to TRUE, the COPY_TABLE_DEPENDENTS procedure continues copying dependent objects even if an error is encountered when creating an object. You can view these errors by querying the DBA_REDEFINITION_ERRORS view.

Reasons for errors include:

A lack of system resources

A change in the logical structure of the table that would require recoding the dependent object.

If ignore_errors is set to FALSE, the COPY_TABLE_DEPENDENTS procedure stops copying objects as soon as any error is encountered.

After you correct any errors you can again attempt to copy the dependent objects by reexecuting the COPY_TABLE_DEPENDENTS procedure. Optionally you can create the objects manually and then register them as explained in "Creating Dependent Objects Manually". The COPY_TABLE_DEPENDENTS procedure can be used multiple times as necessary. If an object has already been successfully copied, it is not copied again.

20.7.4.5 Creating Dependent Objects Manually

If you manually create dependent objects on the interim table with SQL*Plus or Cloud Control, then you must use the REGISTER_DEPENDENT_OBJECT procedure to register the dependent objects. Registering dependent objects enables the redefinition completion process to restore dependent object names to what they were before redefinition.

The following are examples changes that require you to create dependent objects manually:

Moving an index to another tablespace

Modifying the columns of an index

Modifying a constraint

Modifying a trigger

Modifying a materialized view log

When you run the REGISTER_DEPENDENT_OBJECT procedure, you must specify that type of the dependent object with the dep_type parameter. You can specify the following constants in this parameter:

DEMS_REDEFINITION.CONS_INDEX when the dependent object is an index

DEMS_REDEFINITION.CONS_CONSTRAINT when the dependent object type is a constraint

DEMS_REDEFINITION.CONS_TRIGGER when the dependent object is a trigger

DEMS_REDEFINITION.CONS_MVLOG when the dependent object is a materialized view log

You would also use the REGISTER_DEPENDENT_OBJECT procedure if the COPY_TABLE_DEPENDENTS procedure failed to copy a dependent object and manual intervention is required.

You can query the DBA_REDEFINITION_OBJECTS view to determine which dependent objects are registered. This view shows dependent objects that were registered explicitly with the REGISTER_DEPENDENT_OBJECT procedure or implicitly with the COPY_TABLE_DEPENDENTS procedure. Only current information is shown in the view.

The UNREGISTER_DEPENDENT_OBJECT procedure can be used to unregister a dependent object on the table being redefined and on the interim table.

Note:

Manually created dependent objects do not have to be identical to their corresponding original dependent objects. For example, when manually creating a materialized view log on the interim table, you can log different columns. In addition, the interim table can have more or fewer dependent objects.

If the table being redefined includes named LOB segments, then the LOB segment names are replaced by system-generated names during online redefinition. To avoid this, you can create the interim table with new LOB segment names.

20.7.5 Results of the Redefinition Process

The original table is redefined with the columns, indexes, constraints, grants, triggers, and statistics of the interim table, assuming that either REDEF_TABLE or COPY_TABLE_DEPENDENTS was used.

Dependent objects that were registered, either explicitly using REGISTER_DEPENDENT_OBJECT or implicitly using COPY_TABLE_DEPENDENTS, are renamed automatically so that dependent object names on the redefined table are the same as before redefinition.

Note:

If no registration is done or no automatic copying is done, then you must manually rename the dependent objects.

The referential constraints involving the interim table now involve the redefined table and are enabled.

Any indexes, triggers, materialized view logs, grants, and constraints defined on the original table (before redefinition) are transferred to the interim table and are dropped when the user drops the interim table. Any referential constraints involving the original table before the redefinition now involve the interim table and are disabled.

Some PL/SQL objects, views, synonyms, and other table-dependent objects may become invalidated. Only those objects that depend on elements of the table that were changed are invalidated. For example, if a PL/SQL procedure queries only columns of the redefined table that were unchanged by the redefinition, the procedure remains valid. See "Managing Object Dependencies" for more information about schema object dependencies.

20.7.6 Performing Intermediate Synchronization

During the redefinition process, you can synchronize the interim table with the original table if there a large number of DML statement executed on the original table.

After the redefinition process has been started by calling START_REDEF_TABLE and before FINISH_REDEF_TABLE has been called, a large number of DML statements might have been executed on the original table. If you know that this is the case, then it is recommended that you periodically synchronize the interim table with the original table.

To synchronize the interim table with the original table:

Run the SYNC_INTERIM_TABLE procedure.

Calling this procedure reduces the time taken by FINISH_REDEF_TABLE to complete the redefinition process. There is no limit to the number of times that you can call SYNC_INTERIM_TABLE.

The small amount of time that the original table is locked during FINISH_REDEF_TABLE is independent of whether SYNC_INTERIM_TABLE has been called.

20.7.7 Aborting Online Table Redefinition and Cleaning Up After Errors

You can abort the online redefinition process. Doing so drops temporary logs and tables associated with the redefinition process. After this procedure is called, you can drop the interim table and its dependent objects.

To abort the online redefinition process in the event that an error is raised during the redefinition process, or if you choose to terminate the redefinition process manually:

Run the ABORT_REDEF_TABLE procedure.

If the online redefinition process must be restarted, if you do not first call ABORT_REDEF_TABLE, then subsequent attempts to redefine the table will fail.

20.7.8 Restrictions for Online Redefinition of Tables

Several restrictions apply to online redefinition of tables.

The following restrictions apply to the online redefinition of tables:

If the table is to be redefined using primary key or pseudo-primary keys (unique keys or constraints with all component columns having not null constraints), then the post-redefinition table must have the same primary key or pseudo-primary key columns. If the table is to be redefined using rowids, then the table must not be an index-organized table.

After redefining a table that has a materialized view log, the subsequent refresh of any dependent materialized view must be a complete refresh.

Tables that are replicated in an n-way master configuration can be redefined, but horizontal subsetting (subset of rows in the table), vertical subsetting (subset of columns in the table), and column transformations are not allowed.

The overflow table of an index-organized table cannot be redefined online independently.

Tables for which Flashback Data Archive is enabled cannot be redefined online. You cannot enable Flashback Data Archive for the interim table.

Tables with BFILE columns cannot be redefined online.

Tables with LONG columns can be redefined online, but those columns must be converted to CLOBS. Also, LONG RAW columns must be converted to BLOBS. Tables with LOB columns are acceptable.

On a system with sufficient resources for parallel execution, and in the case where the interim table is not partitioned, redefinition of a LONG column to a LOB column can be executed in parallel, provided that:

The segment used to store the LOB column in the interim table belongs to a locally managed tablespace with Automatic Segment Space Management (ASSM) enabled.

There is a simple mapping from one LONG column to one LOB column, and the interim table has only one LOB column.

In the case where the interim table is partitioned, the normal methods for parallel execution for partitioning apply.

Tables in the SYS and SYSTEM schema cannot be redefined online.

Temporary tables cannot be redefined.

A subset of rows in the table cannot be redefined.

Only simple deterministic expressions, sequences, and SYSDATE can be used when mapping the columns in the interim table to those of the original table. For example, subqueries are not allowed.

If new columns are being added as part of the redefinition and there are no column mappings for these columns, then they must not be declared NOTNULL until the redefinition is complete.

There cannot be any referential constraints between the table being redefined and the interim table.

Table redefinition cannot be done NOLOGGING.

For materialized view logs and queue tables, online redefinition is restricted to changes in physical properties. No horizontal or vertical subsetting is permitted, nor are any column transformations. The only valid value for the column mapping string is NULL.

You cannot perform online redefinition on a partition that includes one or more nested tables.

You can convert a VARRAY to a nested table with the CAST operator in the column mapping. However, you cannot convert a nested table to a VARRAY.

When the columns in the col_mapping parameter of the DBMS_REDEFINITION.START_REDEF_TABLE procedure include a sequence, the orderby_cols parameter must be NULL.

For tables with a Virtual Private Database (VPD) security policy, when the copy_vpd_opt parameter is specified as DBMS_REDEFINITION.CONS_VPD_AUTO, the following restrictions apply:

The column mapping string between the original table and interim table must be NULL or '*'.

20.7.9 Online Redefinition of One or More Partitions

You can redefine online one or more partitions of a table. This is useful if, for example, you want to move partitions to a different tablespace and keep the partitions available for DML during the operation.

You can redefine multiple partitions in a table at one time. If you do, then multiple interim tables are required during the table redefinition process. Ensure that you have enough free space and undo space to complete the table redefinition.

When you redefine multiple partitions, you can specify that the redefinition continues even if it encounters an error for a particular partition. To do so, set the continue_after_errors parameter to TRUE in redefinition procedures in the DBMS_REDEFINITION package. You can check the DBA_REDEFINITION_STATUS view to see if any errors were encountered during the redefinition process. The STATUS column in this view shows whether the redefinition process succeeded or failed for each partition.

You can also redefine an entire table one partition at a time to reduce resource requirements. For example, to move a very large table to a different tablespace, you can move it one partition at a time to minimize the free space and undo space required to complete the move.

Redefining partitions differs from redefining a table in the following ways:

There is no need to copy dependent objects. It is not valid to use the COPY_TABLE_DEPENDENTS procedure when redefining a single partition.

You must manually create and register any local indexes on the interim table.

Starting with Oracle Database 12c, you can use the simpler ALTERTABLE...MOVEPARTITION...ONLINE statement to move a partition or subpartition online without using online table redefinition. DML operations can continue to run uninterrupted on the partition or subpartition that is being moved. See "Moving a Table to a New Segment or Tablespace".

20.7.9.1 Rules for Online Redefinition of a Single Partition

The underlying mechanism for redefinition of a single partition is the exchange partition capability of the database (ALTERTABLE...EXCHANGEPARTITION).

Rules and restrictions for online redefinition of a single partition are therefore governed by this mechanism. Here are some general restrictions:

No logical changes (such as adding or dropping a column) are permitted.

No changes to the partitioning method (such as changing from range partitioning to hash partitioning) are permitted.

Here are the rules for defining the interim table:

If the partition being redefined is a range, hash, or list partition, then the interim table must be nonpartitioned.

If the partition being redefined is a range partition of a composite range-hash partitioned table, then the interim table must be a hash partitioned table. In addition, the partitioning key of the interim table must be identical to the subpartitioning key of the range-hash partitioned table, and the number of partitions in the interim table must be identical to the number of subpartitions in the range partition being redefined.

If the partition being redefined is a range partition of a composite range-list partitioned table, then the interim table must be a list partitioned table. In addition, the partitioning key of the interim table must be identical to the subpartitioning key of the range-list partitioned table, and the values lists of the interim table's list partitions must exactly match the values lists of the list subpartitions in the range partition being redefined.

If you define the interim table as compressed, then you must use the by-key method of redefinition, not the by-rowid method.

These additional rules apply if the table being redefined is a partitioned index-organized table:

The interim table must also be index-organized.

The original and interim tables must have primary keys on the same columns, in the same order.

If prefix compression is enabled, then it must be enabled for both the original and interim tables, with the same prefix length.

Both the original and interim tables must have overflow segments, or neither can have them. Likewise for mapping tables.

Note that the ignore_errors argument is set to TRUE for this call. The reason is that the interim table was created with a primary key constraint, and when COPY_TABLE_DEPENDENTS attempts to copy the primary key constraint and index from the original table, errors occur. You can ignore these errors, but you must run the query shown in the next step to see if there are other errors.

These errors are caused by the existing primary key constraint on the interim table and can be ignored. Note that with this approach, the names of the primary key constraint and index on the post-redefined table are changed. An alternate approach, one that avoids errors and name changes, would be to define the interim table without a primary key constraint. In this case, the primary key constraint and index are copied from the original table.

Note:

The best approach is to define the interim table with a primary key constraint, use REGISTER_DEPENDENT_OBJECT to register the primary key constraint and index, and then copy the remaining dependent objects with COPY_TABLE_DEPENDENTS. This approach avoids errors and ensures that the redefined table always has a primary key and that the dependent object names do not change.

The table hr.emp_redef is locked in the exclusive mode only for a small window toward the end of this step. After this call the table hr.emp_redef is redefined such that it has all the attributes of the hr.int_emp_redef table.

Wait for any long-running queries against the interim table to complete, and then drop the interim table.

Example 4

This example addresses the situation where a dependent object must be manually created and registered.

The table to be redefined is defined as follows:

CREATE TABLE steve.t1
(c1 NUMBER);

The table has an index for column c1:

CREATE INDEX steve.index1 ON steve.t1(c1);

Consider the case where column c1 becomes column c2 after the redefinition. In this case, COPY_TABLE_DEPENDENTS tries to create an index on the interim table corresponding to index1, and tries to create it on a column c1, which does not exist in the interim table. This results in an error. You must therefore manually create the index on column c2 and register it.

Here are the steps for this redefinition:

In SQL*Plus, connect as a user with the required privileges for performing online redefinition of a table.

Wait for any long-running queries against the interim table to complete, and then drop the interim table.

Example 5

This example demonstrates redefining multiple partitions. It moves two of the partitions of a range-partitioned sales table to new tablespaces. The table containing the partitions to be redefined is defined as follows:

Notice that the part_name parameter specifies both of the partitions and that the int_table parameter specifies the interim table for each partition. Also, the continue_after_errors parameter is set to TRUE so that the redefinition process continues even if it encounters an error for a particular partition.

If redefinition failed for any partition, then query the DBA_REDEFINITION_ERRORS view to determine the cause of the failure. Correct the conditions that caused the failure, and rerun online redefinition.

The following query shows that two of the partitions in the table have been moved to the new tablespaces:

This example illustrates online redefinition of a table with virtual private database (VPD) policies. The example disables all triggers for a table without changing any of the column names or column types in the table.

In this example, the hr.employees table is redefined to disable all of its triggers. No column names or column types are changed during redefinition. Therefore, specify DBMS_REDEFINITION.CONS_VPD_AUTO for the copy_vpd_opt in the START_REFEF_TABLE procedure.

The steps in this redefinition are illustrated below.

In SQL*Plus, connect as a user with the required privileges for performing online redefinition of a table and the required privileges for managing VPD policies.

The table hr.employees is locked in the exclusive mode only for a small window toward the end of this step. After this call the table hr.employees is redefined such that it has all the attributes of the hr.int_employees table.

In this example, the table is redefined to change the sales_rep_id column to sale_pid. When one or more column names or column types change during redefinition, you must specify DBMS_REDEFINITION.CONS_VPD_MANUAL for the copy_vpd_opt in the START_REFEF_TABLE procedure.

The steps in this redefinition are illustrated below.

In SQL*Plus, connect as a user with the required privileges for performing online redefinition of a table and the required privileges for managing VPD policies.

Note that the ignore_errors argument is set to TRUE for this call. The reason is that the original table has an index and a constraint related to the sales_rep_id column, and this column is changed to sales_pid in the interim table. The next step shows the errors and describes how to create the index and the constraint on the interim table.

In this example, original table has an index and a foreign key constraint on the sales_rep_id column. The index and the constraint could not be copied to the interim table because the name of the column changed from sales_rep_id to sales_pid.

To correct the problems, add the index and the constraint on the interim table by completing the following steps:

The table oe.orders is locked in the exclusive mode only for a small window toward the end of this step. After this call the table oe.orders is redefined such that it has all the attributes of the oe.int_orders table.

The table's tablespace is changed from example to testredeftbs, and the table's block size is changed from 8KB to 16KB.

This example assumes that the database block size is 8KB. This example also assumes that the DB_16K_CACHE_SIZE initialization parameter is set and that the testredef tablespace was created with a 16KB block size. For example:

20.7.11 Privileges Required for the DBMS_REDEFINITION Package

Execute privileges on the DBMS_REDEFINITION package are required to run subprograms in the package. Execute privileges on the DBMS_REDEFINITION package are granted to EXECUTE_CATALOG_ROLE.

In addition, for a user to redefine a table in the user's schema using the package, the user must be granted the following privileges:

CREATETABLE

CREATEMATERIALIZEDVIEW

The CREATE TRIGGER privilege is also required to execute the COPY_TABLE_DEPENDENTS procedure.

For a user to redefine a table in other schemas using the package, the user must be granted the following privileges:

CREATEANYTABLE

ALTERANYTABLE

DROPANYTABLE

LOCKANY TABLE

SELECTANYTABLE

The following additional privileges are required to execute COPY_TABLE_DEPENDENTS on tables in other schemas:

CREATEANYTRIGGER

CREATEANYINDEX

20.8 Researching and Reversing Erroneous Table Changes

To enable you to research and reverse erroneous changes to tables, Oracle Database provides a group of features that you can use to view past states of database objects or to return database objects to a previous state without using point-in-time media recovery. These features are known as Oracle Flashback features.

To research an erroneous change, you can use multiple Oracle Flashback queries to view row data at specific points in time. A more efficient approach would be to use Oracle Flashback Version Query to view all changes to a row over a period of time. With this feature, you append a VERSIONS clause to a SELECT statement that specifies a system change number (SCN) or timestamp range between which you want to view changes to row values. The query also can return associated metadata, such as the transaction responsible for the change.

After you identify an erroneous transaction, you can use Oracle Flashback Transaction Query to identify other changes that were made by the transaction. You can then use Oracle Flashback Transaction to reverse the erroneous transaction. (Note that Oracle Flashback Transaction must also reverse all dependent transactions—subsequent transactions involving the same rows as the erroneous transaction.) You also have the option of using Oracle Flashback Table, described in "Recovering Tables Using Oracle Flashback Table".

20.9 Recovering Tables Using Oracle Flashback Table

Oracle Flashback Table enables you to restore a table to its state as of a previous point in time.

It provides a fast, online solution for recovering a table that has been accidentally modified or deleted by a user or application. In many cases, Oracle Flashback Table eliminates the need for you to perform more complicated point-in-time recovery operations.

Oracle Flashback Table:

Restores all data in a specified table to a previous point in time described by a timestamp or SCN.

Performs the restore operation online.

Automatically maintains all of the table attributes, such as indexes, triggers, and constraints that are necessary for an application to function with the flashed-back table.

Maintains any remote state in a distributed environment. For example, all of the table modifications required by replication if a replicated table is flashed back.

Maintains data integrity as specified by constraints. Tables are flashed back provided none of the table constraints are violated. This includes any referential integrity constraints specified between a table included in the FLASHBACK TABLE statement and another table that is not included in the FLASHBACK TABLE statement.

Even after a flashback operation, the data in the original table is not lost. You can later revert to the original state.

20.10 Dropping Tables

To drop a table that you no longer need, use the DROP TABLE statement.

The table must be contained in your schema or you must have the DROP ANY TABLE system privilege.

Note:

Before dropping a table, familiarize yourself with the consequences of doing so:

Dropping a table removes the table definition from the data dictionary. All rows of the table are no longer accessible.

All indexes and triggers associated with a table are dropped.

All views and PL/SQL program units dependent on a dropped table remain, yet become invalid (not usable). See "Managing Object Dependencies" for information about how the database manages dependencies.

All synonyms for a dropped table remain, but return an error when used.

All extents allocated for a table that is dropped are returned to the free space of the tablespace and can be used by any other object requiring new extents or new objects. All rows corresponding to a clustered table are deleted from the blocks of the cluster. Clustered tables are the subject of Managing Clusters.

The following statement drops the hr.int_admin_emp table:

DROP TABLE hr.int_admin_emp;

If the table to be dropped contains any primary or unique keys referenced by foreign keys of other tables and you intend to drop the FOREIGN KEY constraints of the child tables, then include the CASCADE clause in the DROP TABLE statement, as shown below:

DROP TABLE hr.admin_emp CASCADE CONSTRAINTS;

When you drop a table, normally the database does not immediately release the space associated with the table. Rather, the database renames the table and places it in a recycle bin, where it can later be recovered with the FLASHBACKTABLE statement if you find that you dropped the table in error. If you should want to immediately release the space associated with the table at the time you issue the DROP TABLE statement, include the PURGE clause as shown in the following statement:

DROP TABLE hr.admin_emp PURGE;

Perhaps instead of dropping a table, you want to truncate it. The TRUNCATE statement provides a fast, efficient method for deleting all rows from a table, but it does not affect any structures associated with the table being truncated (column definitions, constraints, triggers, and so forth) or authorizations. The TRUNCATE statement is discussed in "Truncating Tables and Clusters".

20.11 Using Flashback Drop and Managing the Recycle Bin

When you drop a table, the database does not immediately remove the space associated with the table. The database renames the table and places it and any associated objects in a recycle bin, where, in case the table was dropped in error, it can be recovered at a later time. This feature is called Flashback Drop, and the FLASHBACKTABLE statement is used to restore the table.

Before discussing the use of the FLASHBACKTABLE statement for this purpose, it is important to understand how the recycle bin works, and how you manage its contents.

20.11.1 What Is the Recycle Bin?

The recycle bin is actually a data dictionary table containing information about dropped objects. Dropped tables and any associated objects such as indexes, constraints, nested tables, and so on are not removed and still occupy space.

They continue to count against user space quotas, until specifically purged from the recycle bin or the unlikely situation where they must be purged by the database because of tablespace space constraints.

Each user can be thought of as having his own recycle bin, because, unless a user has the SYSDBA privilege, the only objects that the user has access to in the recycle bin are those that the user owns. A user can view his objects in the recycle bin using the following statement:

SELECT * FROM RECYCLEBIN;

Only the DROPTABLE SQL statement places objects in the recycle bin. It adds the table and its associated objects so that they can be recovered as a group. In addition to the table itself, the associated objects that are added to the recycle bin can include the following types of objects:

Nested tables

LOB segments

Indexes

Constraints (excluding foreign key constraints)

Triggers

Clusters

When you drop a tablespace including its contents, the objects in the tablespace are not placed in the recycle bin and the database purges any entries in the recycle bin for objects located in the tablespace. The database also purges any recycle bin entries for objects in a tablespace when you drop the tablespace, not including contents, and the tablespace is otherwise empty. Likewise:

When you drop a user, any objects belonging to the user are not placed in the recycle bin and any objects in the recycle bin are purged.

When you drop a cluster, its member tables are not placed in the recycle bin and any former member tables in the recycle bin are purged.

When you drop a type, any dependent objects such as subtypes are not placed in the recycle bin and any former dependent objects in the recycle bin are purged.

Object Naming in the Recycle Bin

When a dropped table is moved to the recycle bin, the table and its associated objects are given system-generated names. This is necessary to avoid name conflicts that may arise if multiple tables have the same name. This could occur under the following circumstances:

A user drops a table, re-creates it with the same name, then drops it again.

Two users have tables with the same name, and both users drop their tables.

The renaming convention is as follows:

BIN$unique_id$version

where:

unique_id is a 26-character globally unique identifier for this object, which makes the recycle bin name unique across all databases

version is a version number assigned by the database

20.11.2 Enabling and Disabling the Recycle Bin

When the recycle bin is enabled, dropped tables and their dependent objects are placed in the recycle bin. When the recycle bin is disabled, dropped tables and their dependent objects are not placed in the recycle bin; they are dropped, and you must use other means to recover them (such as recovering from backup).

Disabling the recycle bin does not purge or otherwise affect objects already in the recycle bin. The recycle bin is enabled by default.

You enable and disable the recycle bin by changing the recyclebin initialization parameter. This parameter is not dynamic, so a database restart is required when you change it with an ALTERSYSTEM statement.

You can query objects that are in the recycle bin, just as you can query other objects. However, you must specify the name of the object as it is identified in the recycle bin. For example:

SELECT * FROM "BIN$yrMKlZaVMhfgNAgAIMenRA==$0";

20.11.4 Purging Objects in the Recycle Bin

If you decide that you are never going to restore an item from the recycle bin, then you can use the PURGE statement to remove the items and their associated objects from the recycle bin and release their storage space. You need the same privileges as if you were dropping the item.

When you use the PURGE statement to purge a table, you can use the name that the table is known by in the recycle bin or the original name of the table. The recycle bin name can be obtained from either the DBA_ or USER_RECYCLEBIN view as shown in "Viewing and Querying Objects in the Recycle Bin". The following hypothetical example purges the table hr.int_admin_emp, which was renamed to BIN$jsleilx392mk2=293$0 when it was placed in the recycle bin:

PURGE TABLE "BIN$jsleilx392mk2=293$0";

You can achieve the same result with the following statement:

PURGE TABLE int_admin_emp;

You can use the PURGE statement to purge all the objects in the recycle bin that are from a specified tablespace or only the tablespace objects belonging to a specified user, as shown in the following examples:

PURGE TABLESPACE example;
PURGE TABLESPACE example USER oe;

Users can purge the recycle bin of their own objects, and release space for objects, by using the following statement:

PURGE RECYCLEBIN;

If you have the SYSDBA privilege or the PURGEDBA_RECYCLEBIN system privilege, then you can purge the entire recycle bin by specifying DBA_RECYCLEBIN, instead of RECYCLEBIN in the previous statement.

You can also use the PURGE statement to purge an index from the recycle bin or to purge from the recycle bin all objects in a specified tablespace.

20.11.5 Restoring Tables from the Recycle Bin

Use the FLASHBACKTABLE ... TOBEFOREDROP statement to recover objects from the recycle bin.

You can specify either the name of the table in the recycle bin or the original table name. An optional RENAME TO clause lets you rename the table as you recover it. The recycle bin name can be obtained from either the DBA_ or USER_RECYCLEBIN view as shown in "Viewing and Querying Objects in the Recycle Bin". To use the FLASHBACKTABLE ... TOBEFOREDROP statement, you need the same privileges required to drop the table.

The following example restores int_admin_emp table and assigns to it a new name:

FLASHBACK TABLE int_admin_emp TO BEFORE DROP
RENAME TO int2_admin_emp;

The system-generated recycle bin name is very useful if you have dropped a table multiple times. For example, suppose you have three versions of the int2_admin_emp table in the recycle bin and you want to recover the second version. You can do this by issuing two FLASHBACK TABLE statements, or you can query the recycle bin and then flashback to the appropriate system-generated name, as shown in the following example. Including the create time in the query can help you verify that you are restoring the correct table.

When you restore a table from the recycle bin, dependent objects such as indexes do not get their original names back; they retain their system-generated recycle bin names. You must manually rename dependent objects to restore their original names. If you plan to manually restore original names for dependent objects, ensure that you make note of each dependent object's system-generated recycle bin name before you restore the table.

The following is an example of restoring the original names of some of the indexes of the dropped table JOB_HISTORY, from the HR sample schema. The example assumes that you are logged in as the HR user.

After dropping JOB_HISTORY and before restoring it from the recycle bin, run the following query:

Note that double quotes are required around the system-generated names.

20.12 Managing Index-Organized Tables

An index-organized table's storage organization is a variant of a primary B-tree index. Unlike a heap-organized table, data is stored in primary key order.

20.12.1 What Are Index-Organized Tables?

An index-organized table has a storage organization that is a variant of a primary B-tree. Unlike an ordinary (heap-organized) table whose data is stored as an unordered collection (heap), data for an index-organized table is stored in a B-tree index structure in a primary key sorted manner. Each leaf block in the index structure stores both the key and nonkey columns.

The structure of an index-organized table provides the following benefits:

Fast random access on the primary key because an index-only scan is sufficient. And, because there is no separate table storage area, changes to the table data (such as adding new rows, updating rows, or deleting rows) result only in updating the index structure.

Fast range access on the primary key because the rows are clustered in primary key order.

Lower storage requirements because duplication of primary keys is avoided. They are not stored both in the index and underlying table, as is true with heap-organized tables.

Index-organized tables have full table functionality. They support features such as constraints, triggers, LOB and object columns, partitioning, parallel operations, online reorganization, and replication. And, they offer these additional features:

Prefix compression

Overflow storage area and specific column placement

Secondary indexes, including bitmap indexes.

Index-organized tables are ideal for OLTP applications, which require fast primary key access and high availability. For example, queries and DML on an orders table used in electronic order processing are predominantly based on primary key access, and heavy volume of concurrent DML can cause row chaining and inefficient space usage in indexes, resulting in a frequent need to reorganize. Because an index-organized table can be reorganized online and without invalidating its secondary indexes, the window of unavailability is greatly reduced or eliminated.

Index-organized tables are suitable for modeling application-specific index structures. For example, content-based information retrieval applications containing text, image and audio data require inverted indexes that can be effectively modeled using index-organized tables. A fundamental component of an internet search engine is an inverted index that can be modeled using index-organized tables.

20.12.2 Creating Index-Organized Tables

20.12.2.1 About Creating Index-Organized Tables

When you create an index-organized table, but you must provide additional information:

An ORGANIZATION INDEX qualifier, which indicates that this is an index-organized table

A primary key, specified through a column constraint clause (for a single column primary key) or a table constraint clause (for a multiple-column primary key).

Optionally, you can specify the following:

An OVERFLOW clause, which preserves dense clustering of the B-tree index by enabling the storage of some of the nonkey columns in a separate overflow data segment.

A PCTTHRESHOLD value, which, when an overflow segment is being used, defines the maximum size of the portion of the row that is stored in the index block, as a percentage of block size. Rows columns that would cause the row size to exceed this maximum are stored in the overflow segment. The row is broken at a column boundary into two pieces, a head piece and tail piece. The head piece fits in the specified threshold and is stored along with the key in the index leaf block. The tail piece is stored in the overflow area as one or more row pieces. Thus, the index entry contains the key value, the nonkey column values that fit the specified threshold, and a pointer to the rest of the row.

An INCLUDING clause, which can be used to specify the nonkey columns that are to be stored in the index block with the primary key.

This example creates an index-organized table named admin_docindex, with a primary key composed of the columns token and doc_id. The OVERFLOW and PCTTHRESHOLD clauses specify that if the length of a row exceeds 20% of the index block size, then the column that exceeded that threshold and all columns after it are moved to the overflow segment. The overflow segment is stored in the admin_tbs2 tablespace.

20.12.2.3 Restrictions for Index-Organized Tables

Several restrictions apply when you are creating an index-organized table.

The following are restrictions on creating index-organized tables.

The maximum number of columns is 1000.

The maximum number of columns in the index portion of a row is 255, including both key and nonkey columns. If more than 255 columns are required, you must use an overflow segment.

The maximum number of columns that you can include in the primary key is 32.

PCTTHRESHOLD must be in the range of 1–50. The default is 50.

All key columns must fit within the specified threshold.

If the maximum size of a row exceeds 50% of the index block size and you do not specify an overflow segment, the CREATETABLE statement fails.

Index-organized tables cannot have virtual columns.

When a table has a foreign key, and the parent of the foreign key is an index-organized table, a session that updates a row that contains the foreign key can hang when another session is updating a non-key column in the parent table.

For example, consider a scenario in which a departments table is an index-organized table, and department_id is its primary key. There is an employees table with a department_id column that is a foreign key of the departments table. Assume a session is updating the department_name in a row in the departments table for which the department_id is 20 while another session is updating a row in the employees table for which the department_id is 20. In this case, the session updating the employees table can hang until the session updating the departments table commits or rolls back.

20.12.2.4 Creating Index-Organized Tables That Contain Object Types

Index-organized tables can store object types.

The following example creates object type admin_typ, then creates an index-organized table containing a column of object type admin_typ:

Another example, that follows, shows that index-organized tables store nested tables efficiently. For a nested table column, the database internally creates a storage table to hold all the nested table rows.

The rows belonging to a single nested table instance are identified by a nested_table_id column. If an ordinary table is used to store nested table columns, the nested table rows typically get de-clustered. But when you use an index-organized table, the nested table rows can be clustered based on the nested_table_id column.

20.12.2.5 Choosing and Monitoring a Threshold Value

Choose a threshold value that can accommodate your key columns, as well as the first few nonkey columns (if they are frequently accessed).

After choosing a threshold value, you can monitor tables to verify that the value you specified is appropriate. You can use the ANALYZETABLE ... LISTCHAINEDROWS statement to determine the number and identity of rows exceeding the threshold value.

20.12.2.6 Using the INCLUDING Clause

In addition to specifying PCTTHRESHOLD, you can use the INCLUDING clause to control which nonkey columns are stored with the key columns in an index-organized table.

The database accommodates all nonkey columns up to and including the column specified in the INCLUDING clause in the index leaf block, provided it does not exceed the specified threshold. All nonkey columns beyond the column specified in the INCLUDING clause are stored in the overflow segment. If the INCLUDING and PCTTHRESHOLD clauses conflict, PCTTHRESHOLD takes precedence.

Note:

Oracle Database moves all primary key columns of an indexed-organized table to the beginning of the table (in their key order) to provide efficient primary key–based access. As an example:

The stored column order is: c b a d (instead of: a b c d). The last primary key column is b, based on the stored column order. The INCLUDING column can be the last primary key column (b in this example), or any nonkey column (that is, any column after b in the stored column order).

The following CREATE TABLE statement is similar to the one shown earlier in "Example: Creating an Index-Organized Table" but is modified to create an index-organized table where the token_offsets column value is always stored in the overflow area:

This statement provides an alternative to parallel bulk-load using SQL*Loader.

20.12.2.8 Using Prefix Compression

Creating an index-organized table using prefix compression (also known as key compression) enables you to eliminate repeated occurrences of key column prefix values.

Prefix compression breaks an index key into a prefix and a suffix entry. Compression is achieved by sharing the prefix entries among all the suffix entries in an index block. This sharing can lead to huge savings in space, allowing you to store more keys in each index block while improving performance.

You can enable prefix compression using the COMPRESS clause while:

Creating an index-organized table

Moving an index-organized table

You can also specify the prefix length (as the number of key columns), which identifies how the key columns are broken into a prefix and suffix entry.

For the list of values (1,2,3), (1,2,4), (1,2,7), (1,3,5), (1,3,4), (1,4,4), the repeated occurrences of 1 are compressed away.

You can disable compression as follows:

ALTER TABLE admin_iot5 MOVE NOCOMPRESS;

One application of prefix compression is in a time-series application that uses a set of time-stamped rows belonging to a single item, such as a stock price. Index-organized tables are attractive for such applications because of the ability to cluster rows based on the primary key. By defining an index-organized table with primary key (stock symbol, time stamp), you can store and manipulate time-series data efficiently. You can achieve more storage savings by compressing repeated occurrences of the item identifier (for example, the stock symbol) in a time series by using an index-organized table with prefix compression.

20.12.3 Maintaining Index-Organized Tables

Index-organized tables differ from ordinary tables only in physical organization. Logically, they are manipulated in the same manner as ordinary tables. You can specify an index-organized table just as you would specify a regular table in INSERT, SELECT, DELETE, and UPDATE statements.

20.12.3.1 Altering Index-Organized Tables

All of the alter options available for ordinary tables are available for index-organized tables. This includes ADD, MODIFY, and DROPCOLUMNS and CONSTRAINTS. However, the primary key constraint for an index-organized table cannot be dropped, deferred, or disabled.

You can use the ALTER TABLE statement to modify physical and storage attributes for both primary key index and overflow data segments. All the attributes specified before the OVERFLOW keyword are applicable to the primary key index segment. All attributes specified after the OVERFLOW key word are applicable to the overflow data segment. For example, you can set the INITRANS of the primary key index segment to 4 and the overflow of the data segment INITRANS to 6 as follows:

ALTER TABLE admin_docindex INITRANS 4 OVERFLOW INITRANS 6;

You can also alter PCTTHRESHOLD and INCLUDING column values. A new setting is used to break the row into head and overflow tail pieces during subsequent operations. For example, the PCTHRESHOLD and INCLUDING column values can be altered for the admin_docindex table as follows:

ALTER TABLE admin_docindex PCTTHRESHOLD 15 INCLUDING doc_id;

By setting the INCLUDING column to doc_id, all the columns that follow token_frequency and token_offsets, are stored in the overflow data segment.

For index-organized tables created without an overflow data segment, you can add an overflow data segment by using the ADD OVERFLOW clause. For example, you can add an overflow segment to table admin_iot3 as follows:

ALTER TABLE admin_iot3 ADD OVERFLOW TABLESPACE admin_tbs2;

20.12.3.2 Moving (Rebuilding) Index-Organized Tables

Because index-organized tables are primarily stored in a B-tree index, you can encounter fragmentation as a consequence of incremental updates. However, you can use the ALTER TABLE...MOVE statement to rebuild the index and reduce this fragmentation.

The following statement rebuilds the index-organized table admin_docindex:

ALTER TABLE admin_docindex MOVE;

You can rebuild index-organized tables online using the ONLINE keyword. The overflow data segment, if present, is rebuilt when the OVERFLOW keyword is specified. For example, to rebuild the admin_docindex table but not the overflow data segment, perform a move online as follows:

ALTER TABLE admin_docindex MOVE ONLINE;

To rebuild the admin_docindex table along with its overflow data segment perform the move operation as shown in the following statement. This statement also illustrates moving both the table and overflow data segment to new tablespaces.

20.12.4 Creating Secondary Indexes on Index-Organized Tables

A secondary index is an index on an index-organized table. The secondary index is an independent schema object and is stored separately from the index-organized table.

20.12.4.1 About Secondary Indexes on Index-Organized Tables

You can create secondary indexes on an index-organized tables to provide multiple access paths.

Secondary indexes on index-organized tables differ from indexes on ordinary tables in two ways:

They store logical rowids instead of physical rowids. This is necessary because the inherent movability of rows in a B-tree index results in the rows having no permanent physical addresses. If the physical location of a row changes, its logical rowid remains valid. One effect of this is that a table maintenance operation, such as ALTER TABLE ... MOVE, does not make the secondary index unusable.

The logical rowid also includes a physical guess which identifies the database block address at which the row is likely to be found. If the physical guess is correct, a secondary index scan would incur a single additional I/O once the secondary key is found. The performance would be similar to that of a secondary index-scan on an ordinary table.

20.12.4.2 Creating a Secondary Index on an Index-Organized Table

The following statement shows the creation of a secondary index on the docindex index-organized table where doc_id and token are the key columns:

CREATE INDEX Doc_id_index on Docindex(Doc_id, Token);

This secondary index allows the database to efficiently process a query, such as the following, the involves a predicate on doc_id:

SELECT Token FROM Docindex WHERE Doc_id = 1;

20.12.4.3 Maintaining Physical Guesses in Logical Rowids

A logical rowid can include a guess, which identifies the block location of a row at the time the guess is made. Instead of doing a full key search, the database uses the guess to search the block directly.

However, as new rows are inserted, guesses can become stale. The indexes are still usable through the primary key-component of the logical rowid, but access to rows is slower.

Collect index statistics with the DBMS_STATS package to monitor the staleness of guesses.

The database checks whether the existing guesses are still valid and records the percentage of rows with valid guesses in the data dictionary.

Query the PCT_DIRECT_ACCESS column of the DBA_INDEXES view (and related views) to show the statistics related to existing guesses.

To obtain fresh guesses, you can rebuild the secondary index.

Rebuilding a secondary index on an index-organized table involves reading the base table, unlike rebuilding an index on an ordinary table.

A quicker, more light weight means of fixing the guesses is to use the ALTERINDEX ... UPDATEBLOCKREFERENCES statement. This statement is performed online, while DML is still allowed on the underlying index-organized table.

After you rebuild a secondary index, or otherwise update the block references in the guesses, collect index statistics again.

20.12.4.4 Specifying Bitmap Indexes on Index-Organized Tables

Bitmap indexes on index-organized tables are supported, provided the index-organized table is created with a mapping table.

This is done by specifying the MAPPINGTABLE clause in the CREATETABLE statement that you use to create the index-organized table, or in an ALTERTABLE statement to add the mapping table later.

20.12.5 Analyzing Index-Organized Tables

Just like ordinary tables, index-organized tables are analyzed using the DBMS_STATS package, or the ANALYZE statement.

20.12.5.1 Collecting Optimizer Statistics for Index-Organized Tables

To collect optimizer statistics, use the DBMS_STATS package.

For example, the following statement gathers statistics for the index-organized countries table in the hr schema:

EXECUTE DBMS_STATS.GATHER_TABLE_STATS ('HR','COUNTRIES');

The DBMS_STATS package analyzes both the primary key index segment and the overflow data segment, and computes logical as well as physical statistics for the table.

The logical statistics can be queried using USER_TABLES, ALL_TABLES or DBA_TABLES.

You can query the physical statistics of the primary key index segment using USER_INDEXES, ALL_INDEXES or DBA_INDEXES (and using the primary key index name). For example, you can obtain the primary key index segment physical statistics for the table admin_docindex as follows:

You can query the physical statistics for the overflow data segment using the USER_TABLES, ALL_TABLES or DBA_TABLES. You can identify the overflow entry by searching for IOT_TYPE = 'IOT_OVERFLOW'. For example, you can obtain overflow data segment physical attributes associated with the admin_docindex table as follows:

Before converting an index-organized table to a regular table, be aware that index-organized tables cannot be exported using pre-Oracle8 versions of the Export utility.

See Also:

Oracle Database Utilities for more details about using the original IMP and EXP utilities and the Data Pump import and export utilities

20.13 Managing External Tables

External tables do not reside in the database.

20.13.1 About External Tables

Oracle Database allows you read-only access to data in external tables. External tables are defined as tables that do not reside in the database, and can be in any format for which an access driver is provided.

By providing the database with metadata describing an external table, the database is able to expose the data in the external table as if it were data residing in a regular database table. The external data can be queried directly and in parallel using SQL.

You can, for example, select, join, or sort external table data. You can also create views and synonyms for external tables. However, no DML operations (UPDATE, INSERT, or DELETE) are possible, and no indexes can be created, on external tables.

External tables provide a framework to unload the result of an arbitrary SELECT statement into a platform-independent Oracle-proprietary format that can be used by Oracle Data Pump. External tables provide a valuable means for performing basic extraction, transformation, and loading (ETL) tasks that are common for data warehousing.

The means of defining the metadata for external tables is through the CREATE TABLE...ORGANIZATION EXTERNAL statement. This external table definition can be thought of as a view that allows running any SQL query against external data without requiring that the external data first be loaded into the database. An access driver is the actual mechanism used to read the external data in the table. When you use external tables to unload data, the metadata is automatically created based on the data types in the SELECT statement.

Oracle Database provides two access drivers for external tables. The default access driver is ORACLE_LOADER, which allows the reading of data from external files using the Oracle loader technology. The ORACLE_LOADER access driver provides data mapping capabilities which are a subset of the control file syntax of SQL*Loader utility. The second access driver, ORACLE_DATAPUMP, lets you unload data—that is, read data from the database and insert it into an external table, represented by one or more external files—and then reload it into an Oracle Database.

Note:

The ANALYZE statement is not supported for gathering statistics for external tables. Use the DBMS_STATS package instead.

20.13.2 Creating External Tables

You create external tables using the CREATETABLE statement with an ORGANIZATIONEXTERNAL clause. This statement creates only metadata in the data dictionary.

Note:

External tables cannot have virtual columns.

The following example creates an external table and then uploads the data to a database table. Alternatively, you can unload data through the external table framework by specifying the ASsubquery clause of the CREATE TABLE statement. External table data pump unload can use only the ORACLE_DATAPUMP access driver.

EXAMPLE: Creating an External Table and Loading Data

In this example, the data for the external table resides in the two text files empxt1.dat and empxt2.dat.

The following paragraphs contain descriptive information about this example.

The first few statements in this example create the directory objects for the operating system directories that contain the data sources, and for the bad record and log files specified in the access parameters. You must also grant READ or WRITE directory object privileges, as appropriate.

Note:

When creating a directory object or BFILEs, ensure that the following conditions are met:

The operating system file must not be a symbolic or hard link.

The operating system directory path named in the Oracle Database directory object must be an existing OS directory path.

The operating system directory path named in the directory object should not contain any symbolic links in its components.

The TYPE specification indicates the access driver of the external table. The access driver is the API that interprets the external data for the database. If you omit the TYPE specification, ORACLE_LOADER is the default access driver. You must specify the ORACLE_DATAPUMP access driver if you specify the ASsubquery clause to unload data from one Oracle Database and reload it into the same or a different Oracle Database.

The access parameters, specified in the ACCESS PARAMETERS clause, are opaque to the database. These access parameters are defined by the access driver, and are provided to the access driver by the database when the external table is accessed. See Oracle Database Utilities for a description of the ORACLE_LOADER access parameters.

The PARALLEL clause enables parallel query on the data sources. The granule of parallelism is by default a data source, but parallel access within a data source is implemented whenever possible. For example, if PARALLEL=3 were specified, then multiple parallel execution servers could be working on a data source. But, parallel access within a data source is provided by the access driver only if all of the following conditions are met:

The media allows random positioning within a data source

It is possible to find a record boundary from a random position

The data files are large enough to make it worthwhile to break up into multiple chunks

Note:

Specifying a PARALLEL clause is of value only when dealing with large amounts of data. Otherwise, it is not advisable to specify a PARALLEL clause, and doing so can be detrimental.

The REJECTLIMIT clause specifies that there is no limit on the number of errors that can occur during a query of the external data. For parallel access, the REJECTLIMIT applies to each parallel execution server independently. For example, if a REJECTLIMIT of 10 is specified, then each parallel query process can allow up to 10 rejections. Therefore, with a parallel degree of two and a REJECTLIMIT of 10, the statement might fail with between 10 and 20 rejections. If one parallel server processes all 10 rejections, then the limit is reached, and the statement is terminated. However, one parallel execution server could process nine rejections and another parallel execution server could process nine rejections and the statement will succeed with 18 rejections. Hence, the only precisely enforced values for REJECTLIMIT on parallel query are 0 and UNLIMITED.

In this example, the INSERTINTOTABLE statement generates a dataflow from the external data source to the Oracle Database SQL engine where data is processed. As data is parsed by the access driver from the external table sources and provided to the external table interface, the external data is converted from its external representation to its Oracle Database internal data type.

20.13.3 Altering External Tables

You can use any of the ALTER TABLE clauses shown in Table 20-5 to change the characteristics of an external table. No other clauses are permitted.

Table 20-5 ALTER TABLE Clauses for External Tables

ALTER TABLE Clause

Description

Example

REJECT LIMIT

Changes the reject limit. The default value is 0.

ALTER TABLE admin_ext_employees
REJECT LIMIT 100;

PROJECTCOLUMN

Determines how the access driver validates rows in subsequent queries:

PROJECTCOLUMNREFERENCED: the access driver processes only the columns in the select list of the query. This setting may not provide a consistent set of rows when querying a different column list from the same external table.

PROJECTCOLUMNALL: the access driver processes all of the columns defined on the external table. This setting always provides a consistent set of rows when querying an external table. This is the default.

For example, a user may want to access data stored in a compressed format. Specifying a decompression program for the ORACLE_LOADER access driver allows the data to be decompressed as the access driver processes the data.

To use the preprocessing feature, you must specify the PREPROCESSOR clause in the access parameters of the ORACLE_LOADER access driver. The preprocessor must be a directory object, and the user accessing the external table must have EXECUTE privileges for the directory object. The following example includes the PREPROCESSOR clause and specifies the directory and preprocessor program.

20.14 Tables Data Dictionary Views

You can query a set of data dictionary views for information about tables.

The following views allow you to access information about tables.

View

Description

DBA_TABLES

ALL_TABLES

USER_TABLES

DBA view describes all relational tables in the database. ALL view describes all tables accessible to the user. USER view is restricted to tables owned by the user. Some columns in these views contain statistics that are generated by the DBMS_STATS package or ANALYZE statement.

DBA_TAB_COLUMNS

ALL_TAB_COLUMNS

USER_TAB_COLUMNS

These views describe the columns of tables, views, and clusters in the database. Some columns in these views contain statistics that are generated by the DBMS_STATS package or ANALYZE statement.

DBA_ALL_TABLES

ALL_ALL_TABLES

USER_ALL_TABLES

These views describe all relational and object tables in the database. Object tables are not specifically discussed in this book.

DBA_TAB_COMMENTS

ALL_TAB_COMMENTS

USER_TAB_COMMENTS

These views display comments for tables and views. Comments are entered using the COMMENT statement.

DBA_COL_COMMENTS

ALL_COL_COMMENTS

USER_COL_COMMENTS

These views display comments for table and view columns. Comments are entered using the COMMENT statement.

DBA_EXTERNAL_TABLES

ALL_EXTERNAL_TABLES

USER_EXTERNAL_TABLES

These views list the specific attributes of external tables in the database.

These views describe tables that have been modified since the last time table statistics were gathered on them. They are not populated immediately, but after a time lapse (usually 3 hours).

DBA_ENCRYPTED_COLUMNS

USER_ENCRYPTED_COLUMNS

ALL_ENCRYPTED_COLUMNS

These views list table columns that are encrypted, and for each column, lists the encryption algorithm in use.

DBA_UNUSED_COL_TABS

ALL_UNUSED_COL_TABS

USER_UNUSED_COL_TABS

These views list tables with unused columns, as marked by the ALTER TABLE ... SET UNUSED statement.

DBA_PARTIAL_DROP_TABS

ALL_PARTIAL_DROP_TABS

USER_PARTIAL_DROP_TABS

These views list tables that have partially completed DROP COLUMN operations. These operations could be incomplete because the operation was interrupted by the user or a system failure.

Example: Displaying Column Information

Column information, such as name, data type, 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: