SQL - Database Objects

Previous Chapter | Next Chapter

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

* Constraints

* Database links

* Database triggers

* Dimensions

* External procedure libraries

* Index-organized tables

* Indexes

* Indextypes

* Java classes, Java resources, Java sources

* Materialized views

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

* Parameter files (PFILEs) and server parameter files (SPFILEs)

* Profiles

* Roles

* Rollback segments

* Tablespaces

* Users

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 list method, you specify actual values 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 Names
Partition-extended and subpartition-extended 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 names, such operations would require that you specify a predicate (WHERE clause). For range- and list-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 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:







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


Partition Extended Name

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.


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_Q1_2000 AS
SELECT * FROM sales PARTITION (Q1_2000);

DELETE FROM sales_Q1_2000 WHERE amount < 0;

Previous Chapter | Next Chapter

Discuss Database Objects | More Tutorials on Oracle dba | Source : Oracle Documentation

Liked it ? Want to share it ? Social Bookmarking

Add to: Mr. Wong Add to: BoniTrust Add to: Newsider Add to: Digg Add to: Del.icio.us Add to: Reddit Add to: Jumptags Add to: StumbleUpon Add to: Slashdot Add to: Netscape Add to: Furl Add to: Yahoo Add to: Spurl Add to: Google Add to: Blinklist Add to: Technorati Add to: Newsvine Information

Want to share or request more about Database Objects to become a Oracle DBA. Direct your requests to webmaster@oracleonline.info