Database Objects

Oracle recognizes objects that are associated with a particular schema and objects that are not associated with a particular schema, as described in the sections that follow.

Schema Objects

A schema is a collection of logical structures of data, or schema objects. A schema is owned by a database user and has the same name as that user. Each user owns a single schema. Schema objects can be created and manipulated with SQL and include the following types of objects:

• clusters

• database links

• database triggers

• dimensions

• external procedure libraries

• index-organized tables

• indexes

• indextypes

• Java classes, Java resources, Java sources

• materialized views / snapshots

• materialized view logs / snapshot logs

• object tables

• object types

• object views

• operators

• packages

• sequences

• stored functions, stored procedures

• synonyms

• tables

• views

Nonschema Objects

Other types of objects are also stored in the database and can be created and manipulated with SQL but are not contained in a schema:

• contexts

• directories

• profiles

• roles

• rollback segments

• tablespaces

• users

In this reference, each type of object is briefly defined in Chapter 7, "SQL Statements", in the section describing the statement that creates the database object. These statements begin with the keyword CREATE. For example, for the definition of a cluster, see "CREATE CLUSTER". For an overview of database objects, see Oracle8i Concepts.

You must provide names for most types of schema objects when you create them. These names must follow the rules listed in the following sections.

Parts of Schema Objects

Some schema objects are made up of parts that you can or must name, such as:

• columns in a table or view

• index and table partitions and subpartitions

• integrity constraints on a table

• packaged procedures, packaged stored functions, and other objects stored within a package

Partitioned Tables and Indexes

Tables and indexes can be partitioned. When partitioned, these schema objects consist of a number of parts called partitions, all of which have the same logical attributes. For example, all partitions in a table share the same column and constraint definitions, and all partitions in an index share the same index columns.

When you partition a table or index using the range method, you specify a maximum value for the partitioning key column(s) for each partition. When you partition a table or index using the hash method, you instruct Oracle to distribute the rows of the table into partitions based on a system-defined hash function on the partitioning key column(s). When you partition a table or index using the composite-partitioning method, you specify ranges for the partitions, and Oracle distributes the rows in each partition into one or more hash subpartitions based on a hash function. Each subpartition of a table or index partitioned using the composite method has the same logical attributes.

Partition-Extended and Subpartition-Extended Table Names

Partition-extended and subpartition-extended table names let you perform some partition-level and subpartition-level operations, such as deleting all rows from a partition or subpartition, on only one partition or subpartition. Without extended table names, such operations would require that you specify a predicate (WHERE clause). For range-partitioned tables, trying to phrase a partition-level operation with a predicate can be cumbersome, especially when the range partitioning key uses more than one column. For hash partitions and subpartitions, using a predicate is more difficult still, because these partitions and subpartitions are based on a system-defined hash function.

Partition-extended table names let you use partitions as if they were tables. An advantage of this method, which is most useful for range-partitioned tables, is that you can build partition-level access control mechanisms by granting (or revoking) privileges on these views to (or from) other users or roles.To use a partition as a table, create a view by selecting data from a single partition, and then use the view as a table.

You can specify partition-extended or subpartition-extended table names for the following DML statements:

• DELETE

• INSERT

• LOCK TABLE

• SELECT

• UPDATE

  Note: For application portability and ANSI syntax compliance, Oracle strongly recommends that you use views to insulate applications from this Oracle proprietary extension.

Syntax

The basic syntax for using partition-extended and subpartition-extended table names is:

Restrictions

Currently, the use of partition-extended and subpartition-extended table names has the following restrictions:

• No remote tables: A partition-extended or subpartition-extended table name cannot contain a database link (dblink) or a synonym that translates to a table with a dblink. To use remote partitions and subpartitions, create a view at the remote site that uses the extended table name syntax and then refer to the remote view.

• No synonyms: A partition or subpartition extension must be specified with a base table. You cannot use synonyms, views, or any other objects.

Example

In the following statement, SALES is a partitioned table with partition JAN97. You can create a view of the single partition JAN97, and then use it as if it were a table. This example deletes rows from the partition.

CREATE VIEW sales_jan97 AS
     SELECT * FROM sales PARTITION (jan97); 
DELETE FROM sales_jan97 WHERE amount < 0;