Previous Chapter | Next Chapter
Want to share or request more about Database Objects to become a Oracle DBA. Direct your requests
to
webmaster@oracleonline.info
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:
* DELETE
* INSERT
* LOCK TABLE
* SELECT
* UPDATE
Syntax
The basic syntax for using partition-extended and subpartition-extended table names is:
partition_extended_name::=
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_Q1_2000 AS
SELECT * FROM sales PARTITION (Q1_2000);
DELETE FROM sales_Q1_2000 WHERE amount < 0;
Discuss Database Objects | More Tutorials on Oracle dba | Source : Oracle Documentation
Liked it ? Want to share it ? Social Bookmarking
