CREATE ROLE

Purpose

To create a role. A role is a set of privileges that can be granted to users or to other roles. See also "Using Roles".

For a detailed description and explanation of using global roles, see Oracle8 Distributed Database Systems.

Prerequisites

You must have CREATE ROLE system privilege.

Syntax

Keywords and Parameters

role	is the name of the role to be created. Oracle recommends that the role contain at least one single-byte character regardless of whether the database character set also contains multibyte characters. See also "Roles Predefined by Oracle".
NOT IDENTIFIED	indicates that this role is authorized by the database and that no password is required to enable the role.
IDENTIFIED	indicates that a user must be authorized by the specified method before the role is enabled with the SET ROLE command:
	BY password	The user must specify the password to Oracle when enabling the role. The password can contain only single-byte characters from your database character set regardless of whether this character set also contains multibyte characters.
	EXTERNALLY	indicates that a user must be authorized by an external service (such as an operating system or third-party service) before enabling the role.
		Depending on the operating system, the user may have to specify a password to the operating system before the role is enabled. For more information about third-party service, see Oracle Security Server Guide.
	GLOBALLY	indicates that a user must be authorized to use the role by the Oracle Security Service before the role is enabled with the SET ROLE command, or at login.
If you omit both the NOT IDENTIFIED option and the IDENTIFIED clause, the role defaults to NOT IDENTIFIED.

Using Roles

A role is a set of privileges that can be granted to users or to other roles. You can use roles to administer database privileges. You can add privileges to a role and then grant the role to a user. The user can then enable the role and exercise the privileges granted by the role. For information on enabling roles, see ALTER USER.

A role contains all privileges granted to the role and all privileges of other roles granted to it. A new role is initially empty. You add privileges to a role with the GRANT command.

When you create a role, Oracle grants you the role with ADMIN OPTION. The ADMIN OPTION allows you to

grant the role to another user or role, unless the role is a GLOBAL role
revoke the role from another user or role
alter the role to change the authorization needed to access it
drop the role

Note:

When you create a role IDENTIFIED GLOBALLY, Oracle does not grant you the role as it does with nonglobal roles.

Roles Predefined by Oracle

Some roles are defined by SQL scripts provided on your distribution media. The following roles are predefined:

CONNECT
RESOURCE
DBA
EXP_FULL_DATABASE
IMP_FULL_DATABASE
DELETE_CATALOG_ROLE
EXECUTE_CATALOG_ROLE
SELECT_CATALOG_ROLE

The CONNECT, RESOURCE, and DBA roles are provided for compatibility with previous versions of Oracle. You should not rely on these roles; rather, Oracle recommends that you to design your own roles for database security. These roles may not be created automatically by future versions of Oracle.

The SELECT_CATALOG_ROLE, EXECUTE_CATALOG_ROLE, and DELETE_CATALOG_ROLE roles are provided for accessing exported data dictionary views and packages. For more information on these roles, see Oracle8 Application Developer's Guide.

The EXP_FULL_DATABASE and IMP_FULL_DATABASE roles are provided for convenience in using the Import and Export utilities.

Oracle also creates other roles that authorize you to administer the database. On many operating systems, these roles are called OSOPER and OSDBA. Their names may be different on your operating system.

Example I

The following example creates global role VENDOR:

CREATE ROLE vendor IDENTIFIED GLOBALLY;

Example II

The following statement creates the role TELLER:

CREATE ROLE teller 
IDENTIFIED BY cashflow;

Users who are subsequently granted the TELLER role must specify the password CASHFLOW to enable the role with the SET ROLE command.

CREATE ROLLBACK SEGMENT

Purpose

To create a rollback segment. A rollback segment is an object that Oracle uses to store data necessary to reverse, or undo, changes made by transactions.

Prerequisites

You must have CREATE ROLLBACK SEGMENT system privilege. Also, you must have either space quota on the tablespace to contain the rollback segment or UNLIMITED TABLESPACE system privilege.

Syntax

storage_clause: See the STORAGE clause.

Keyword and Parameters

PUBLIC	specifies that the rollback segment is public and is available to any instance. If you omit this option, the rollback segment is private and is available only to the instance naming it in its initialization parameter ROLLBACK_SEGMENTS.
rollback_segment	is the name of the rollback segment to be created.
TABLESPACE	identifies the tablespace in which the rollback segment is created. If you omit this option, Oracle creates the rollback segment in the SYSTEM tablespace. See also "Rollback Segments and Tablespaces".
storage_clause	specifies the characteristics for the rollback segment. See the STORAGE clause. Note: The PCTINCREASE option of the storage_clause is not permitted with CREATE ROLLBACK SEGMENT.
OPTIMAL	This part of the STORAGE clause specifies an optimal size in bytes for a rollback segment. You can use K or M to specify this size in kilobytes or megabytes. Oracle tries to maintain this size for the rollback segment by dynamically deallocating extents when their data is no longer needed for active transactions. Oracle deallocates as many extents as possible without reducing the total size of the rollback segment below the OPTIMAL value.
	NULL	specifies no optimal size for the rollback segment, meaning that Oracle never deallocates the rollback segment's extents. This is the default behavior.
	The value of this parameter cannot be less than the space initially allocated for the rollback segment specified by the MINEXTENTS, INITIAL, and NEXT parameters. The maximum value depends on your operating system. Oracle rounds values to the next multiple of the data block size.

Rollback Segments and Tablespaces

The tablespace must be online for you to add a rollback segment to it.

When you create a rollback segment, it is initially offline. To make it available for transactions by your Oracle instance, you must bring it online using one of the following:

ALTER ROLLBACK SEGMENT command
ROLLBACK_SEGMENTS initialization parameter

For more information on creating rollback segments and making them available, see Oracle8 Administrator's Guide.

A tablespace can have multiple rollback segments. Generally, multiple rollback segments improve performance.

Example

The following statement creates a rollback segment with default storage values in the system tablespace:

CREATE ROLLBACK SEGMENT rbs_2
TABLESPACE system;

The above statement is equivalent to the following:

CREATE ROLLBACK SEGMENT rbs_2
TABLESPACE system
STORAGE
( INITIAL 10 K
  NEXT 10 K
  MAXEXTENTS UNLMIITED);

CREATE SCHEMA

Purpose

To create multiple tables and views and perform multiple grants in a single transaction. See also "Creating Schemas".

Prerequisites

The CREATE SCHEMA statement can include, CREATE TABLE, CREATE VIEW, and GRANT statements. To issue a CREATE SCHEMA statement, you must have the privileges necessary to issue the included statements.

Syntax

Keyword and Parameters

schema	is the name of the schema. The schema name must be the same as your Oracle username.
CREATE TABLE command	is a CREATE TABLE statement to be issued as part of this CREATE SCHEMA statement. See the CREATE TABLE
CREATE VIEW command	is a CREATE VIEW statement to be issued as part of this CREATE SCHEMA statement. See the CREATE VIEW.
GRANT command	is a GRANT statement (Object Privileges) to be issued as part of this CREATE SCHEMA statement. See GRANT (Object Privileges).
	The CREATE SCHEMA statement supports the syntax of these commands only as defined by standard SQL, rather than the complete syntax supported by Oracle.

Creating Schemas

With the CREATE SCHEMA command, you can issue multiple data definition language (DDL) statements in a single transaction. To execute a CREATE SCHEMA statement, Oracle executes each included statement. If all statements execute successfully, Oracle commits the transaction. If any statement results in an error, Oracle rolls back all the statements.

Terminate a CREATE SCHEMA statement just as you would any other SQL statement using the terminator character specific to your tool. For example, if you issue a CREATE SCHEMA statement in SQL*Plus or Server Manager, terminate the statement with a semicolon (;). Do not separate the individual statements within a CREATE SCHEMA statement with the terminator character.

The order in which you list the CREATE TABLE, CREATE VIEW, and GRANT statements is unimportant:

A CREATE VIEW statement can create a view that is based on a table that is created by a later CREATE TABLE statement.
A CREATE TABLE statement can create a table with a foreign key that depends on the primary key of a table that is created by a later CREATE TABLE statement.
A GRANT statement can grant privileges on a table or view that is created by a later CREATE TABLE or CREATE VIEW statement.

The statements within a CREATE SCHEMA statement can also reference existing objects:

A CREATE VIEW statement can create a view on a table that existed before the CREATE SCHEMA statement.
A GRANT statement can grant privileges on a previously existing object.

Note:

The syntax of the PARALLEL clause is allowed for a CREATE TABLE, INDEX, or CLUSTER, when used in CREATE SCHEMA, but parallelism is not used when creating the objects.

Example

The following statement creates a schema named BLAIR for the user BLAIR, creates the table SOX, creates the view RED_SOX, and grants SELECT privilege on the RED_SOX view to the user WAITES.

CREATE SCHEMA AUTHORIZATION blair 
CREATE TABLE sox 
(color VARCHAR2(10)  PRIMARY KEY, quantity NUMBER) 
CREATE VIEW red_sox 
AS SELECT color, quantity FROM sox WHERE color = 'RED' 
GRANT select ON red_sox TO waites;

CREATE SEQUENCE

Purpose

To create a sequence. A sequence is a database object from which multiple users may generate unique integers. You can use sequences to automatically generate primary key values. See also "Using Sequences", "Sequence Defaults", and "Accessing Sequence Values".

Prerequisites

To create a sequence in your own schema, you must have CREATE SEQUENCE privilege.

To create a sequence in another user's schema, you must have CREATE ANY SEQUENCE privilege.

Syntax

Keywords and Parameters

schema	is the schema to contain the sequence. If you omit schema, Oracle creates the sequence in your own schema.
sequence	is the name of the sequence to be created.
INCREMENT BY	specifies the interval between sequence numbers. This integer value can be any positive or negative integer, but it cannot be 0. This value can have 28 or fewer digits. The absolute of this value must be less than the difference of MAXVALUE and MINVALUE. If this value is negative, then the sequence descends. If the increment is positive, then the sequence ascends. If you omit this clause, the interval defaults to 1. See also "Incrementing Sequence Values".
START WITH	specifies the first sequence number to be generated. Use this option to start an ascending sequence at a value greater than its minimum or to start a descending sequence at a value less than its maximum. For ascending sequences, the default value is the sequence's minimum value. For descending sequences, the default value is the sequence's maximum value. This integer value can have 28 or fewer digits.
MAXVALUE	specifies the maximum value the sequence can generate. This integer value can have 28 or fewer digits. MAXVALUE must be equal to or less than START WITH and must be greater than MINVALUE.
NOMAXVALUE	specifies a maximum value of 10^27 for an ascending sequence or -1 for a descending sequence. This is the default.
MINVALUE	specifies the sequence's minimum value. This integer value can have 28 or fewer digits. MINVALUE must be less than or equal to START WITH and must be less than MAXVALUE.
NOMINVALUE	specifies a minimum value of 1 for an ascending sequence or -(10^26) for a descending sequence. This is the default.
CYCLE	specifies that the sequence continues to generate values after reaching either its maximum or minimum value. After an ascending sequence reaches its maximum value, it generates its minimum value. After a descending sequence reaches its minimum, it generates its maximum.
NOCYCLE	specifies that the sequence cannot generate more values after reaching its maximum or minimum value. This is the default.
CACHE	specifies how many values of the sequence Oracle preallocates and keeps in memory for faster access. This integer value can have 28 or fewer digits. The minimum value for this parameter is 2. For sequences that cycle, this value must be less than the number of values in the cycle. You cannot cache more values than will fit in a given cycle of sequence numbers; thus, the maximum value allowed for CACHE must be less than the value determined by the following formula:
	(CEIL (MAXVALUE - MINVALUE)) / ABS (INCREMENT) See also "Caching Sequence Numbers".
NOCACHE	specifies that values of the sequence are not preallocated.
If you omit both the CACHE parameter and the NOCACHE option, Oracle caches 20 sequence numbers by default.
ORDER	guarantees that sequence numbers are generated in order of request. You may want to use this option if you are using the sequence numbers as timestamps. Guaranteeing order is usually not important for sequences used to generate primary keys.
NOORDER	does not guarantee sequence numbers are generated in order of request.
If you omit both the ORDER and NOORDER options, Oracle chooses NOORDER by default. Note that the ORDER option is necessary only to guarantee ordered generation if you are using Oracle with the Parallel Server option in parallel mode. If you are using exclusive mode, sequence numbers are always generated in order.

Using Sequences

You can use sequence numbers to automatically generate unique primary key values for your data, and you can also coordinate the keys across multiple rows or tables.

Values for a given sequence are automatically generated by special Oracle routines and, consequently, sequences avoid the performance bottleneck that results from implementation of sequences at the application level. For example, one common application-level implementation is to force each transaction to lock a sequence number table, increment the sequence, and then release the table. Under this implementation, only one sequence number can be generated at a time. In contrast, Oracle sequences permit the simultaneous generation of multiple sequence numbers while guaranteeing that every sequence number is unique.

When a sequence number is generated, the sequence is incremented, independent of the transaction committing or rolling back. If two users concurrently increment the same sequence, the sequence numbers each user acquires may have gaps because sequence numbers are being generated by the other user. One user can never acquire the sequence number generated by another user. Once a sequence value is generated by one user, that user can continue to access that value regardless of whether the sequence is incremented by another user.

Sequence numbers are generated independently of tables, so the same sequence can be used for one or for multiple tables. It is possible that individual sequence numbers will appear to be skipped, because they were generated and used in a transaction that ultimately rolled back. Additionally, a single user may not realize that other users are drawing from the same sequence.

Sequence Defaults

The sequence defaults are designed so that if you specify none of the clauses, you create an ascending sequence that starts with 1 and increases by 1 with no upper limit. Specifying only INCREMENT BY -1 creates a descending sequence that starts with -1 and decreases with no lower limit.

Incrementing Sequence Values

You can create a sequence so that its values increment in one of following ways:

The sequence values increment without bound.
The sequence values increment to a predefined limit and then stop.
The sequence values increment to a predefined limit and then restart.

To create a sequence that increments without bound, omit the MAXVALUE parameter or specify the NOMAXVALUE option for ascending sequences or omit the MINVALUE parameter or specify the NOMINVALUE for descending sequences.

To create a sequence that stops at a predefined limit, specify a value for the MAXVALUE parameter for an ascending sequence or a value for the MINVALUE parameter for a descending sequence. Also specify the NOCYCLE option. Any attempt to generate a sequence number once the sequence has reached its limit results in an error.

To create a sequence that restarts after reaching a predefined limit, specify values for both the MAXVALUE and MINVALUE parameters. Also specify the CYCLE option. If you do not specify MINVALUE, then it defaults to NOMINVALUE; that is, the value 1.

The value of the START WITH parameter establishes the initial value generated after the sequence is created. Note that this value is not necessarily the value to which an ascending cycling sequence cycles after reaching its maximum or minimum value.

Caching Sequence Numbers

The number of values cached in memory for a sequence is specified by the value of the sequence's CACHE parameter. Cached sequences allow faster generation of sequence numbers. A cache for a given sequence is populated at the first request for a number from that sequence. The cache is repopulated every CACHE requests. If there is a system failure, all cached sequence values that have not been used in committed data manipulation language (DML) statements are lost. The potential number of lost values is equal to the value of the CACHE parameter.

A CACHE of 20 future sequence numbers is the default.

Accessing Sequence Values

Once a sequence is created, you can access its values in SQL statements with the following pseudocolumns:

CURRVAL

returns the current value of the sequence.

NEXTVAL

increments the sequence and returns the new value.

For more information on using the above pseudocolumns, see the section "Pseudocolumns".

Example

The following statement creates the sequence ESEQ:

CREATE SEQUENCE eseq 
  INCREMENT BY 10

The first reference to ESEQ.NEXTVAL returns 1. The second returns 11. Each subsequent reference will return a value 10 greater than the one previous.

CREATE SNAPSHOT

Purpose

To create a snapshot. A snapshot is a table that contains the results of a query of one or more tables, often located on a remote database.

Prerequisites

The following prerequisites apply to creating snapshots:

To create a snapshot in your own schema, you must have the CREATE SNAPSHOT, CREATE TABLE, and CREATE VIEW system privileges.
To create a snapshot in another user's schema, you must have the CREATE ANY SNAPSHOT system privilege.
The schema that contains the snapshot must have sufficient quota in the target tablespace to store the snapshot's base table and index or have the UNLIMITED TABLESPACE system privilege.
To create and refresh a snapshot, both the creator and snapshot owner must be able to issue the defining query of the snapshot. This capability depends directly on the database link that the snapshot's defining query uses.

By default, Oracle creates all new snapshots as primary key snapshots. To create a snapshot:

The master table for a snapshot must contain an enabled PRIMARY KEY constraint before you create the new snapshot.
The defining query of the snapshot must reference all columns in the master table's primary key.

When you create a snapshot, Oracle creates one table, one view, and at least one index in the schema of the snapshot. Oracle uses these objects to maintain the snapshot's data. You must have the privileges necessary to create these objects. For information on these privileges, see CREATE TABLE, CREATE VIEW, and CREATE INDEX.

For complete information about the prerequisites that apply to creating snapshots, see Oracle8 Replication.

Syntax

physical_attributes_clause: See ALTER TABLE.

parallel_clause: See the PARALLEL clause.

index_physical_attributes_clause: See ALTER INDEX.

select_command: See SELECT.

LOB_storage_clause: See CREATE TABLE.

table_partition_clause: See CREATE TABLE.

Keywords and Parameters

schema	is the schema to contain the snapshot. If you omit schema, Oracle creates the snapshot in your schema.
snapshot	is the name of the snapshot to be created. Oracle generates names for the table, view, and indexes used to maintain the snapshot by adding a prefix or suffix to the snapshot name. Oracle recommends that you limit your snapshot names to 19 bytes, so that the Oracle-generated names will be 30 bytes or less and will contain the entire snapshot name. See also "About Snapshots".

physical_attributes_clause	establishes values for the PCTFREE, PCTUSED, INITRANS, and MAXTRANS parameters (or, when used in the USING INDEX clause, for the INITRANS and MAXTRANS parameters only) and the storage parameters for the internal table Oracle uses to maintain the snapshot's data. For information on the PCTFREE, PCTUSED, INITRANS, and MAXTRANS parameters, see CREATE TABLE. For information, about the STORAGE clause, see the STORAGE clause.
TABLESPACE	specifies the tablespace in which the snapshot is to be created. If you omit this option, Oracle creates the snapshot in the default tablespace of the owner of the snapshot's schema.
LOB_storage_clause	specifies the LOB storage characteristics. For detailed information about specifying the parameters of the LOB storage clause, see CREATE TABLE.
STORAGE	establishes storage characteristics for the table Oracle uses to maintain the snapshot's data.
CLUSTER	creates the snapshot as part of the specified cluster. Since a clustered snapshot uses the cluster's space allocation, do not use the physical_attributes_clause or the TABLESPACE option with the CLUSTER option.
table_partition_clause	specifies that the table is partitioned on specified ranges of values. For detailed information about specifying the parameters of the table partition clause, see CREATE TABLE. See also "Partitioned Snapshots".
USING INDEX	specifies parameters for the index Oracle creates to maintain the snapshot. See physical_attributes_clause, above.
USING ROLLBACK SEGMENT	specifies the local snapshot and/or remote master rollback segments to be used during snapshot refresh.
	rollback_segment	is the name of the rollback segment to be used.
	DEFAULT	specifies that Oracle will choose which rollback segment to use.
	MASTER	specifies the rollback segment to be used at the remote master for the individual snapshot.
	LOCAL	specifies the rollback segment to be used for the local refresh group that contains the snapshot.
	If you do not specify MASTER or LOCAL, Oracle uses LOCAL by default. If you do not specify rollback_segment, Oracle automatically chooses the rollback segment to be used. If you specify DEFAULT, you cannot specify rollback_segment. See also "Specifying Rollback Segments".
REFRESH	specifies how and when Oracle automatically refreshes the snapshot:
	FAST	specifies a fast refresh, or a refresh using only the updated data stored in the snapshot log associated with the master table.
	COMPLETE	specifies a complete refresh, or a refresh that reexecutes the snapshot's query.
	FORCE	specifies a fast refresh if one is possible or complete refresh if a fast refresh is not possible. Oracle decides whether a fast refresh is possible at refresh time.
	If you omit the FAST, COMPLETE, and FORCE options, Oracle uses FORCE by default. See also "Refreshing Snapshots".
	START WITH	specifies a date expression for the first automatic refresh time.
	NEXT	specifies a date expression for calculating the interval between automatic refreshes.
	Both the START WITH and NEXT values must evaluate to a time in the future. If you omit the START WITH value, Oracle determines the first automatic refresh time by evaluating the NEXT expression when you create the snapshot. If you specify a START WITH value but omit the NEXT value, Oracle refreshes the snapshot only once. If you omit both the START WITH and NEXT values, or if you omit the REFRESH clause entirely, Oracle does not automatically refresh the snapshot.
	WITH PRIMARY KEY	specifies that primary-key snapshots are to be created. Primary-key snapshots allow snapshot master tables to be reorganized without affecting the snapshot's ability to continue to fast refresh.
		Primary-key snapshots can also be defined as simple snapshots with subqueries.
	WITH ROWID	specifies that ROWID snapshots are to be created.
		ROWID snapshots provide compatibility with Oracle7 Release 7.3 master tables.
	If you omit both WITH PRIMARY KEY and WITH ROWID, Oracle creates primary-key snapshots by default. See also "Specifying Primary-Key or ROWID Snapshots".
FOR UPDATE	allows a simple snapshot to be updated. When used in conjunction with the Replication Option, these updates will be propagated to the master. For more information, see Oracle8 Replication.
AS select_command	specifies the snapshot query. When you create the snapshot, Oracle executes this query and places the results in the snapshot. This query is any valid SQL query, but not all queries are fast refreshable. See also "Types of Snapshots".

About Snapshots

A snapshot is a table that contains the results of a query of one or more tables, often located on a remote database. The tables in the query are called master tables. The databases containing the master tables are called the master databases. Note that a snapshot query cannot select from tables or views owned by the user SYS.

Snapshots are useful in distributed databases. Snapshots allow you to maintain read-only copies of remote data on your local node. You can select data from a snapshot as you would from a table or view.

Oracle recommends that you qualify each table and view in the FROM clause of the snapshot query with the schema containing it. For some additional caveats, see "The View Query" (in the context of the CREATE VIEW command). The same recommendations apply to creating snapshots.

Snapshots cannot contain long columns.

For more information on snapshots, see Oracle8 Replication.

Types of Snapshots

You can create two types of snapshots: simple and complex.

A simple snapshot is based on a single remote table, or is defined on multiple tables using restricted types of subqueries. For more information about simple snapshots with subqueries, see Oracle8 Replication.

Simple snapshots do not contain any of the following items in the snapshot query (select_command_clause):

GROUP BY clause
CONNECT BY clause
distinct or aggregate functions
joins (other than allowable types of subqueries)
set operations

A complex snapshot is one in which the snapshot query contains one or more of the constructs not allowed in the query of a simple snapshot. A complex snapshot can be based on multiple master tables on multiple master databases.

Refreshing Snapshots

A snapshot's master tables can be modified, so the data in a snapshot must be updated occasionally to ensure that the snapshot accurately reflects the data currently in its master tables. The process of updating a snapshot for this purpose is called refreshing the snapshot. With the REFRESH clause of the CREATE SNAPSHOT command, you can schedule the times and specify the mode for Oracle to refresh the snapshot automatically.

After you create a snapshot, you can subsequently change its automatic refresh mode and time with the REFRESH clause of the ALTER SNAPSHOT command. You can also refresh a snapshot immediately with the DBMS_SNAPSHOT.REFRESH() procedure.

Specifying Refresh Modes

Use the FAST or COMPLETE options of the REFRESH clause to specify the refresh mode.

Fast

To perform a fast refresh, Oracle updates the snapshot with the changes to the master table recorded in its snapshot log. For more information on snapshot logs, see CREATE SNAPSHOT LOG.

Oracle can only perform a fast refresh if all of the following conditions are true:

The snapshot is a simple snapshot.
The snapshot's master table has a snapshot log.
The snapshot log was created before the snapshot was last refreshed or created.

If you specify a fast refresh and all of above conditions are true, then Oracle performs a fast refresh. If any of the conditions are not true, Oracle returns an error at refresh time and does not refresh the snapshot.

Complete

To perform a complete refresh, Oracle reexecutes the snapshot query and places the results in the snapshot. If you specify a complete refresh, Oracle performs a complete refresh regardless of whether a fast refresh is possible.

A fast refresh is often faster than a complete refresh because it sends less data from the master database across the network to the snapshot's database. A fast refresh sends only changes to master table data since the last refresh, while a complete refresh sends the complete result of the snapshot query.

You can also use the FORCE option of the REFRESH clause to allow Oracle to decide how to refresh the snapshot at the scheduled refresh time. If a fast refresh is possible based on the fast refresh conditions, then Oracle performs a fast refresh. If a fast refresh is not possible, then Oracle performs a complete refresh.

Example

The following statement creates the simple snapshot EMP_SF that contains the data from SCOTT's employee table in New York:

CREATE SNAPSHOT emp_sf 
PCTFREE 5 PCTUSED 60 
TABLESPACE users 
STORAGE INITIAL 50K NEXT 50K
REFRESH FAST NEXT sysdate + 7 
AS 
SELECT * FROM scott.emp@ny;

The statement does not include a START WITH parameter, so Oracle determines the first automatic refresh time by evaluating the NEXT value using the current SYSDATE. Provided a snapshot log currently exists for the employee table in New York, Oracle performs a fast refresh of the snapshot every 7 days, beginning 7 days after the snapshot is created.

The above statement also establishes for the table storage characteristics that Oracle uses to maintain the snapshot.

Specifying Automatic Refresh Times

To cause Oracle to refresh a snapshot automatically, you must perform the following tasks:

Specify the START WITH and NEXT parameters in the REFRESH clause of the CREATE SNAPSHOT statement. These parameters establish the time of the first automatic refresh and the interval between automatic refreshes.
Enable one or more job queue processes using the initialization parameters SNAPSHOT_REFRESH_PROCESSES, SNAPSHOT_REFRESH_INTERVAL, SNAPSHOT_REFRESH_KEEP_CONNECTIONS. The job queue processes then examine the automatic refresh time of each snapshot in the database. For each snapshot that is scheduled to be refreshed at or before the current time, one job queue process performs the following operations:

reevaluates the snapshot's NEXT value to determine the next automatic refresh time
refreshes the snapshot
stores the next automatic refresh time in the data dictionary

For more information on these initialization parameters, see Oracle8 Reference.

Example

The following statement creates the complex snapshot ALL_EMPS that queries the employee tables in Dallas and Baltimore:

CREATE SNAPSHOT all_emps 
   PCTFREE 5 PCTUSED 60 
   TABLESPACE users 
   STORAGE INITIAL 50K NEXT 50K 
   USING INDEX STORAGE (INITIAL 25K NEXT 25K)
   REFRESH START WITH ROUND(SYSDATE + 1) + 11/24 
   NEXT NEXT_DAY(TRUNC(SYSDATE, 'MONDAY') + 15/24 
AS 
   SELECT * FROM fran.emp@dallas 
      UNION
   SELECT * FROM marco.emp@balt;

Oracle automatically refreshes this snapshot tomorrow at 11:00 am and subsequently every Monday at 3:00 pm. This command does not specify either fast or complete refreshes, so Oracle must decide how to refresh the snapshot. Since ALL_EMPS is a complex snapshot, Oracle must perform a complete refresh.

The above statement also establishes storage characteristics for both the table and the index that Oracle uses to maintain the snapshot:

The first STORAGE clause establishes the sizes of the first and second extents of the table as 50 kilobytes each.
The second STORAGE clause (appearing with the USING INDEX option) establishes the sizes of the first and second extents of the index as 25 kilobytes each.

Specifying Rollback Segments

You can specify the rollback segments to be used during a refresh for both the master site and the local site.

The local snapshot rollback segment is stored at the refresh group level. If the auto-refresh parameters are specified, a new refresh group is automatically created to refresh the snapshot with a background process. The local rollback segment, if specified, is associated with this new refresh group. An error is raised if you specify a local rollback segment but do not specify the auto-refresh parameters.

The master rollback segment is stored on a per-snapshot basis. The master rollback segment is validated during snapshot creation and refresh. If the snapshot is complex, the master rollback segment, if specified, is ignored.

Note:

Specifying DEFAULT is most useful with ALTER SNAPSHOT. See ALTER SNAPSHOT.

To direct Oracle to select the rollback segment automatically after one has been specified using CREATE SNAPSHOT or ALTER SNAPSHOT, specify DEFAULT with ALTER SNAPSHOT.

Example

The following example creates snapshot SALE_EMP with rollback segment MASTER_SEG at the remote master and rollback segment SNAP_SEG for the local refresh group that contains the snapshot:

CREATE SNAPSHOT sales_emp 
REFRESH FAST START WITH SYSDATE NEXT SYSDATE + 7
USING MASTER ROLLBACK SEGMENT master_seg 
LOCAL ROLLBACK SEGMENT snap_seg  
AS SELECT * FROM bar;

The following statement is incorrect and generates an error because it specifies a segment name with a DEFAULT rollback segment:

CREATE SNAPSHOT bogus 
REFRESH FAST START WITH SYSDATE NEXT SYSDATE + 7
USING DEFAULT ROLLBACK SEGMENT snap_seg 
AS SELECT * FROM faux;

Specifying Primary-Key or ROWID Snapshots

To create a primary-key snapshot you must:

include all columns of the primary key in the snapshot definition
have an enabled primary-key constraint defined on the snapshot master

To fast refresh primary-key snapshots, you must first create a snapshot master log specifying WITH PRIMARY KEY. The snapshot master log can also record ROWIDs.

Primary-key snapshots are the default if the WITH clause is not specified.

The above conditions must be met in order to create a primary-key snapshot.

Example I

The following example creates primary-key snapshot HUMAN_GENOME:

CREATE SNAPSHOT human_genome   
   REFRESH FAST START WITH SYSDATE NEXT  SYSDATE + 1/4096 
   WITH PRIMARY KEY 
AS SELECT * FROM genome_catalog;

Example II

The following example creates a ROWID snapshot:

CREATE SNAPSHOT emp_data WITH ROWID 
AS SELECT * FROM emp_table73;

Partitioned Snapshots

Partitioned snapshots are the same as partitioned tables, because snapshots are basically tables. The options have the same syntax and semantics as the partitioned table options for CREATE TABLE and ALTER TABLE. The only difference is that the following operations are not allowed on snapshots and snapshot logs:

DROP PARTITION
TRUNCATE PARTITION
EXCHANGE PARTITION

You cannot perform bulk deletions by dropping or truncating partitions on master tables. Thus, after dropping or truncating a partition, all snapshots must be manually refreshed. A fast refresh will probably produce incorrect results, but Oracle will not raise an error.

CREATE SNAPSHOT LOG

Purpose

To create a snapshot log. A snapshot log is a table associated with the master table of a snapshot. Oracle stores changes to the master table's data in the snapshot log and then uses the snapshot log to refresh the master table's snapshots. See also "Using Snapshot Logs".

Prerequisites

The privileges required to create a snapshot log directly relate to the privileges necessary to create the underlying objects associated with a snapshot log.

If you own the master table, you can create an associated snapshot log if you have the CREATE TABLE privilege.
If you are creating a snapshot log for a table in another user's schema, you must have the CREATE ANY TABLE and COMMENT ANY TABLE privileges, as well as either the SELECT privilege for the master table or SELECT ANY TABLE.

In either case, the owner of the snapshot log must have sufficient quota in the tablespace intended to hold the snapshot log.

For detailed information about the prerequisites for creating a snapshot log, see Oracle8 Replication.

Syntax

parallel_clause: See PARALLEL clause.

storage_clause: See STORAGE clause.

LOB_storage_clause: See CREATE TABLE.

table_partition_clause: See CREATE TABLE.

physical_attributes_clause: See CREATE TABLE.

Keywords and Parameters

schema	is the schema containing the snapshot log's master table. If you omit schema, Oracle assumes the master table is contained in your own schema. Oracle creates the snapshot log in the schema of its master table. You cannot create a snapshot log for a table in the schema of the user SYS.
table	is the name of the master table for which the snapshot log is to be created. You cannot create a snapshot log for a view.
WITH	specifies whether the snapshot log should record the primary key, ROWID, or both primary key and ROWID when rows in the master are updated.
	This clause also specifies whether the snapshot log records filter columns--non-primary-key columns referenced by snapshots defined as simple snapshots with subqueries. See also "Recording Primary Keys, ROWIDs, and Filter Columns".
	PRIMARY KEY	specifies that the primary key of all rows updated should be recorded in the snapshot log.
	ROWID	specifies that the ROWID of all rows updated should be recorded in the snapshot log.
	filter_column	is a comma-separated list that specifies the list of filter columns to be recorded in the snapshot log.
	Oracle records the primary key of all rows updated in the master by default.
physical_attributes_clause	establishes values for the PCTFREE, PCTUSED, INITRANS, and MAXTRANS parameters and the storage characteristics for the snapshot log. See the descriptions of these parameters in CREATE TABLE and STORAGE clause.
TABLESPACE	specifies the tablespace in which the snapshot log is to be created. If you omit this option, Oracle creates the snapshot log in the default tablespace the owner of the snapshot log's schema.
STORAGE	establishes storage characteristics for the snapshot log. See the STORAGE clause.
LOB_storage_clause	specifies the LOB storage characteristics. For detailed information about specifying the parameters of the LOB storage clause, see STORAGE clause.
table_partition_clause	specifies that the table is partitioned on specified ranges of values. For detailed information about specifying the parameters of the table partition clause, see CREATE TABLE.

Using Snapshot Logs

A snapshot log is a table associated with the master table of a snapshot. When changes are made to the master table's data, Oracle adds rows describing these changes to the snapshot log. Later Oracle can use these rows to refresh snapshots based on the master table. This process is called a fast refresh. Without a snapshot log, Oracle must reexecute the snapshot query to refresh the snapshot. This process is called a complete refresh. Usually, a fast refresh takes less time than a complete refresh.

A snapshot log is located in the master database in the same schema as the master table. You need only a single snapshot log for a master table. Oracle can use this snapshot log to perform fast refreshes for all simple snapshots based on the master table. For more information on snapshots, including how Oracle refreshes snapshots, see CREATE SNAPSHOT and Oracle8 Replication.

Example

The following statement creates a snapshot log on an employee table that records only primary-key values:

CREATE SNAPSHOT LOG ON emp 
PCTFREE 5 
TABLESPACE users 
STORAGE (INITIAL 10K NEXT 10K PCTINCREASE 50);

Oracle can use this snapshot log to perform a fast refresh on any simple primary key snapshot subsequently created on the EMP table.

Recording Primary Keys, ROWIDs, and Filter Columns

For Oracle to perform primary-key snapshots, the primary key of updated rows in the master table must be recorded in the snapshot log. Similarly, for ROWID snapshots, the ROWID must be recorded in the snapshot log. Both primary keys and ROWIDs can be recorded to support configurations with both primary-key and ROWID snapshots.

For primary-key snapshots defined as simple snapshots with subqueries, all filter columns referenced by the defining subquery must be recorded in the snapshot log.

Example I

The following examples create snapshot logs that record only the primary keys of updated rows:

CREATE SNAPSHOT LOG ON emp; 
CREATE SNAPSHOT LOG ON emp WITH PRIMARY KEY;

Example II

The following example creates a snapshot log that records both primary keys and ROWIDs of updated rows:

CREATE SNAPSHOT LOG ON sales WITH ROWID, PRIMARY KEY;

Example III

The following example creates a snapshot log that records primary keys and updates to the filter column ZIP:

CREATE SNAPSHOT LOG ON address WITH (zip);

CREATE SYNONYM

Purpose

To create a synonym. A synonym is an alternative name for a table, view, sequence, procedure, stored function, package, snapshot, or another synonym. See also "Using Synonyms".

Prerequisites

To create a private synonym in your own schema, you must have CREATE SYNONYM system privilege.

To create a private synonym in another user's schema, you must have CREATE ANY SYNONYM system privilege.

To create a PUBLIC synonym, you must have CREATE PUBLIC SYNONYM system privilege.

Syntax

Keywords and Parameters

PUBLIC	creates a public synonym. Public synonyms are accessible to all users. If you omit this option, the synonym is private and is accessible only within its schema.
schema	is the schema to contain the synonym. If you omit schema, Oracle creates the synonym in your own schema. You cannot specify schema if you have specified PUBLIC. See also "Scope of Synonyms".
synonym	is the name of the synonym to be created.
FOR	identifies the object for which the synonym is created. If you do not qualify object with schema, Oracle assumes that the schema object is in your own schema. The schema object can be of the following types: table object table view object view sequence stored procedure, function, or package snapshot synonym You can create a synonym for an object table or an object view, but not for object types.
	The schema object cannot be contained in a package.
	Note that the schema object need not currently exist and you need not have privileges to access the object.
dblink	You can use a complete or partial dblink to create a synonym for a schema object on a remote database where the object is located. For more information on referring to database links, see "Referring to Objects in Remote Databases". If you specify dblink and omit schema, the synonym refers to an object in the schema specified by the database link. Oracle recommends that you specify the schema containing the object in the remote database.
	If you omit dblink, Oracle assumes the object is located on the local database.

Using Synonyms

You can use a synonym to stand for its base object in any of the following statements:

DML Statements DDL Statements

SELECT

AUDIT

INSERT

NOAUDIT

UPDATE

GRANT

DELETE

REVOKE

EXPLAIN PLAN

COMMENT

LOCK TABLE

Synonyms are used for security and convenience. Creating a synonym for an object allows you to:

reference the object without specifying its owner
reference the object without specifying the database on which it is located
provide another name for the object

Synonyms provide both data independence and location transparency; synonyms permit applications to function without modification regardless of which user owns the table or view and regardless of which database holds the table or view.

Scope of Synonyms

A private synonym name must be unique in its schema. Oracle attempts to resolve references to objects at the schema level before resolving them at the PUBLIC synonym level. Oracle uses a public synonym only when resolving references to an object if both of the following cases are true:

the object is not prefaced by a schema
the object is not followed by a database link

For example, assume the schemas SCOTT and BLAKE each contain tables named DEPT and the user SYSTEM creates a PUBLIC synonym named DEPT for BLAKE.DEPT. If the user SCOTT then issues the following statement, Oracle returns rows from SCOTT.DEPT:

SELECT * 
   FROM dept;

To retrieve rows from BLAKE.DEPT, the user SCOTT must preface DEPT with the schema name:

SELECT *
   FROM blake.dept;

If the user ADAM's schema does not contain an object named DEPT, then ADAM can access the DEPT table in BLAKE's schema by using the public synonym DEPT:

SELECT * 
   FROM dept;

Example I

To define the synonym MARKET for the table MARKET_RESEARCH in the schema SCOTT, issue the following statement:

CREATE SYNONYM market 
   FOR scott.market_research;

Example II

To create a PUBLIC synonym for the EMP table in the schema SCOTT on the remote SALES database, you could issue the following statement:

CREATE PUBLIC SYNONYM emp 
   FOR scott.emp@sales;

Note that a synonym may have the same name as the base table, provided the base table is contained in another schema.

CREATE TABLE

Purpose

To create a table, the basic structure to hold user data, specifying the following information:

column definitions
table organization definition
column definitions using objects
integrity constraints
the table's tablespace
storage characteristics
an optional cluster
data from an arbitrary query
degree of parallelism used to create the table and the default degree of parallelism for queries on the table
partitioning definitions
index-organized or heap-organized

For illustrations of some of these purposes, "Examples".

Note:

Descriptions of commands and clauses preceded by are available only if the Oracle objects option is installed on your database server.

Use CREATE TABLE to create an object table or a table that uses an object type for a column definition. An object table is a table explicitly defined to hold object instances of a particular type.

You can also create an object type and then use it in a column when creating a relational table. For more information about creating objects, see Oracle8 Application Developer's Guide and CREATE TYPE.

Prerequisites

To create a relational table in your own schema, you must have CREATE TABLE system privilege. To create a table in another user's schema, you must have CREATE ANY TABLE system privilege. Also, the owner of the schema to contain the table must have either space quota on the tablespace to contain the table or UNLIMITED TABLESPACE system privilege.

In addition to the table privileges above, to create a table that uses types, the owner of the table must have the EXECUTE object privilege in order to access all types referenced by the table, or you must have the EXECUTE ANY TYPE system privilege. These privileges must be granted explicitly and not acquired through a role.

Additionally, if the table owner intends to grant access to the table to other users, the owner must have been granted the EXECUTE privileges to the referenced types with the GRANT OPTION, or have the EXECUTE ANY TYPE system privilege with the ADMIN OPTION. If not, the table owner has insufficient privileges to grant access on the table to other users.

For more information about the privileges required to create tables using types, see Oracle8 Application Developer's Guide.

Syntax

Relational table definition ::=

,

Object table definition ::=

column_ref_clause::=

table_ref_clause::=

segment_attributes_clause::=

physical_attributes_clause::=

storage_clause: See the STORAGE clause.

disable_clause: See the DISABLE clause.

enable_clause: See the ENABLE clause.

parallel_clause: See the PARALLEL clause

storage_clause: see STORAGE clause

subquery: See "Subqueries"

Keywords and Parameters

schema	is the schema to contain the table. If you omit schema, Oracle creates the table in your own schema.
table	is the name of the table (or object table) to be created. A partitioned table cannot be a clustered table or an object table.
OF object_type	explicitly creates an object table of type object_type. The columns of an object table correspond to the top-level attributes of type object_type. Each row will contain an object instance, and each instance will be assigned a unique, system-generated object identifier (OID) when a row is inserted. If you omit schema, Oracle creates the object table in your own schema. For more information about creating objects, see CREATE TYPE. See also "Object Tables".
column	specifies the name of a column of the table. A table can have up to 1000 columns. You may omit column definitions only when using the AS subquery clause. See also "LOB Column Example".
attribute	specifies the qualified column name of an item in an object.
datatype	is the datatype of a column. Oracle-supplied datatypes are defined in "Datatypes". You can omit the datatype only if the statement also designates the column as part of a foreign key in a referential integrity constraint. Oracle automatically assigns to the column the datatype of the corresponding column of the referenced key of the referential integrity constraint. Object types, REF object_type, VARRAYs, and nested tables are valid datatypes. See also "REFs".
DEFAULT	specifies a value to be assigned to the column if a subsequent INSERT statement omits a value for the column. The datatype of the expression must match the datatype of the column. The column must also be long enough to hold this expression. For the syntax of expr, see "Expressions". A DEFAULT expression cannot contain references to other columns, the pseudocolumns CURRVAL, NEXTVAL, LEVEL, and ROWNUM, or date constants that are not fully specified.
column_ref_clause	lets you further specify a column of type REF:
	WITH ROWID	stores the ROWID and the REF value in column or attribute. Storing a REF value with a ROWID can improve the performance of dereferencing operations, but will also use more space. Default storage of REF values is without ROWIDs.
	SCOPE IS scope_table_name	restricts the scope of the column REF values to scope_table_name. The REF values for the column must come from REF values obtained from the object table specified in the clause. You can only specify one scope table per REF column.
		The scope_table_name is the name of the object table in which object instances (of the same type as the REF column) are stored. The values in the REF column point to objects in the scope table. You must have SELECT privileges on the table or SELECT ANY TABLE system privileges.
column_constraint	defines an integrity constraint as part of the column definition. See the syntax description of column_constraint in the CONSTRAINT clause.
table_constraint	defines an integrity constraint as part of the table definition. See the syntax description of table_constraint in the CONSTRAINT clause.
table_ref_clause
	SCOPE FOR... IS...	restricts the scope of the REF values in ref_column or ref_attribute to scope_table_name. The REF values for the column or attribute must come from REF values obtained from the object table specified in the clause.
		The ref_column or ref_attribute is the name of a REF column in an object table or an embedded REF attribute within an object column of a relational table. The values in the REF column or attribute point to objects in the scope table.
	REF	is a reference to a row in an object table. You can specify either a REF column name of an object or relational table (ref_column) or an embedded REF attribute within an object column (ref_attribute).
OIDINDEX	specifies an index on the hidden object identifier column and/or the storage specification for the index. Either index or storage_specification must be specified.
	index	is the name of the index on the hidden object identifier column. If not specified, Oracle generates a name.
physical_attributes_clause	specifies the value of the PCTFREE, PCTUSED, INITRANS, and MAXTRANS parameters and the storage characteristics of the table. Note: For a nonpartitioned table, each parameter and storage characteristic specified is the actual physical attribute of the segment associated with the table. For partitioned tables, the value specified for the parameter or storage characteristic is the default physical attribute of the segments associated with all partitions specified in the CREATE statement (and in subsequent ALTER TABLE ADD PARTITION statements), unless you explicitly override that value in the PARTITION clause of this command.
PCTFREE	specifies the percentage of space in each data block of the table, object table OIDINDEX, or partition reserved for future updates to the table's rows. The value of PCTFREE must be a value from 0 to 99. A value of 0 allows the entire block to be filled by inserts of new rows. The default value is 10. This value reserves 10% of each block for updates to existing rows and allows inserts of new rows to fill a maximum of 90% of each block.
	PCTFREE has the same function in the PARTITION description clause and in the commands that create and alter clusters, indexes, snapshots, and snapshot logs. The combination of PCTFREE and PCTUSED determines whether new rows will be inserted into existing data blocks or into new blocks.
PCTUSED	specifies the minimum percentage of used space that Oracle maintains for each data block of the table, object table OIDINDEX, or index-organized table overflow data segment. A block becomes a candidate for row insertion when its used space falls below PCTUSED. PCTUSED is specified as a positive integer from 1 to 99 and defaults to 40.
	PCTUSED has the same function in the PARTITION description clause and in the commands that create and alter clusters, snapshots, and snapshot logs.
	PCTUSED is not a valid table storage characteristic for an index-organized table (ORGANIZATION INDEX).
	The sum of PCTFREE and PCTUSED must be less than 100. You can use PCTFREE and PCTUSED together to utilize space within a table more efficiently. For information on the performance effects of different values PCTUSED and PCTFREE, see Oracle8 Tuning.
INITRANS	specifies the initial number of transaction entries allocated within each data block allocated to the table, object table OIDINDEX, partition, LOB index segment, or overflow data segment. This value can range from 1 to 255 and defaults to 1. In general, you should not change the INITRANS value from its default.
	Each transaction that updates a block requires a transaction entry in the block. The size of a transaction entry depends on your operating system.
	This parameter ensures that a minimum number of concurrent transactions can update the block and helps avoid the overhead of dynamically allocating a transaction entry.
	The INITRANS parameter serves the same purpose in the PARTITION description clause, clusters, indexes, snapshots, and snapshot logs as in tables. The minimum and default INITRANS value for a cluster or index is 2, rather than 1.
MAXTRANS	specifies the maximum number of concurrent transactions that can update a data block allocated to the table, object table OIDINDEX, partition, LOB index segment, or index-organized overflow data segment. This limit does not apply to queries. This value can range from 1 to 255 and the default is a function of the data block size. You should not change the MAXTRANS value from its default.
	If the number concurrent transactions updating a block exceeds the INITRANS value, Oracle dynamically allocates transaction entries in the block until either the MAXTRANS value is exceeded or the block has no more free space.
	The MAXTRANS parameter serves the same purpose in the PARTITION description clause, clusters, snapshots, and snapshot logs as in tables.
storage_clause	specifies the storage characteristics for the table, object table OIDINDEX, partition, LOB storage, LOB index segment, or index-organized table overflow data segment. This clause has performance ramifications for large tables. Storage should be allocated to minimize dynamic allocation of additional space. See the STORAGE clause.
TABLESPACE	specifies the tablespace in which Oracle creates the table, object table OIDINDEX, partition, LOB storage, LOB index segment, or index-organized table overflow data segment. If you omit this option, then Oracle creates that item in the default tablespace of the owner of the schema containing the table.
	For nonpartitioned tables, the value specified for TABLESPACE is the actual physical attribute of the segment associated with the table. For partitioned tables, the value specified for TABLESPACE is the default physical attribute of the segments associated with all partitions specified in the CREATE statement (and on subsequent ALTER TABLE ADD PARTITION statements), unless you specify TABLESPACE in the PARTITION description clause.
			LOGGING/NOLOGGING	specifies whether the creation of the table (and any indexes required because of constraints), partition, or LOB storage characteristics will be logged in the redo log file. It also specifies that subsequent Direct Loader (SQL*Loader) and direct-load INSERT operations against the table, partition, or LOB storage are logged (LOGGING) or not logged (NOLOGGING). If you omit the LOGGING/NOLOGGING clause, the logging attribute of the table or table partition defaults to the logging attribute of the tablespace in which it resides. For LOBs, if you omit the LOGGING/NOLOGGING clause, if you specify CACHE, then LOGGING is used (because you cannot have CACHE NOLOGGING); otherwise, the logging attribute defaults to the logging attribute of the tablespace in which it resides. For nonpartitioned tables, the value specified for LOGGING is the actual physical attribute of the segment associated with the table. For partitioned tables, the logging attribute value specified is the default physical attribute of the segments associated with all partitions specified in the CREATE statement (and in subsequent ALTER TABLE ADD PARTITION statements), unless you specify LOGGING/NOLOGGING in the PARTITION description clause.
	In NOLOGGING mode, data is modified with minimal logging (to mark new extents invalid and to record dictionary changes). When applied during media recovery, the extent invalidation records mark a range of blocks as logically corrupt, because the redo data is not fully logged. Therefore, if you cannot afford to lose this table, you should take a backup after the NOLOGGING operation.
				The size of a redo log generated for an operation in NOLOGGING mode is significantly smaller than the log generated with the LOGGING option set. If the database is run in ARCHIVELOG mode, media recovery from a backup taken before the LOGGING operation restores the table. However, media recovery from a backup taken before the NOLOGGING operation does not restore the table.
	The logging attribute of the table is independent of that of its indexes.
	NOLOGGING is not a valid keyword for creating index-organized tables.
	For more information about the LOGGING option and Parallel DML, see Oracle8 Concepts and Oracle8 Administrator's Guide.
	Note: In future versions of Oracle, the LOGGING keyword will replace the RECOVERABLE option. RECOVERABLE is still available as a valid keyword in Oracle when creating nonpartitioned tables, however, it is not recommended.
ORGANIZATION INDEX	specifies that table is created as an index-organized table. In an index-organized table, the data rows are held in an index defined on the primary key for the table. See also "Index-Organized Tables".
ORGANIZATION HEAP	specifies that the data rows of table are stored in no particular order. This is the default.
index_organized_table_clause	PCTTHRESHOLD integer	specifies the percentage of space reserved in the index block for an index-organized table row. Any portion of the row that exceeds the specified threshold is stored in the area. PCTTHRESHOLD must be a value from 0 to 50.
	OVERFLOW	specifies that index-organized table data rows exceeding the specified threshold are placed in the data segment listed in this clause. If OVERFLOW is not specified, then rows exceeding the PCTTHRESHOLD limit are rejected.
	INCLUDING column_name	specifies a column at which to divide an index-organized table row into index and overflow portions. All columns that follow column_name are stored in the overflow data segment. A column_name is either the name of the last primary-key column or any nonprimary-key column.
RECOVERABLE	is a deprecated option. RECOVERABLE is not a valid keyword for creating partitioned tables or LOB storage characteristics.
UNRECOVERABLE	is a deprecated option. It specifies that the creation of the table (and any indices required because of constraints) will not be logged in the redo log file. This keyword can only be specified with the AS subquery clause. UNRECOVERABLE is not a valid keyword for creating partitioned or index-organized tables.
	Note: In future versions of Oracle, the LOGGING keyword will replace the RECOVERABLE option. RECOVERABLE is still available as a valid keyword in Oracle when creating nonpartitioned tables, however, it is not recommended.
LOB_storage_clause	LOB	specifies the LOB storage characteristics. For detailed information about LOBs, see Oracle8 Application Developer's Guide.
	lob_item	is the LOB column name or LOB object attribute for which you are explicitly defining tablespace and storage characteristics that are different from those of the table.
STORE AS	lob_segname	specifies the name of the LOB data segment. You cannot use lob_segname if you specify more than one lob_item.
lob_parameters	ENABLE STORAGE IN ROW	specifies that the LOB value is stored in the row (in-line) if its length is less than approximately 4000 bytes minus system control information. This is the default.
	DISABLE STORAGE IN ROW	specifies that the LOB value is stored outside of the row regardless of the length of the LOB value.
	Note that the LOB locator is always stored in the row regardless of where the LOB value is stored. You cannot change the value of STORAGE IN ROW once it is set.
	CHUNK integer	specifies the number of bytes to be allocated for LOB manipulation. If integer is not a multiple of the database block size, Oracle rounds up (in bytes) to the next multiple. For example, if the database block size is 2048 and integer is 2050, Oracle allocates 4096 bytes (2 blocks). The maximum value is 32768 (32K), which is the largest Oracle block size allowed. Note: The value of CHUNK must be less than or equal to the values of both INITIAL and NEXT (either the default values or those specified in the storage clause). If CHUNK exceeds the value of either INITIAL or NEXT, Oracle returns an error.
	PCTVERSION integer	is the maximum percentage of overall LOB storage space used for creating new versions of the LOB. The default value is 10, meaning that older versions of the LOB data are not overwritten until 10% of the overall LOB storage space is used.
	INDEX lob_index_name	is the name of the LOB index segment. You cannot specify lob_index_name if you specify more than one lob_item in the associated lob_item list. Note that you cannot alter the LOB index through the ALTER INDEX statement. You can alter a LOB index specification only through the ALTER TABLE statement (see ALTER TABLE). Note also that a user cannot drop the LOB index. It is a system index created and maintained by the system.
NESTED TABLE ... STORE AS ...	specifies storage_table as the name of the storage table in which the rows of all nested_item values reside. You must include this clause when creating a table with columns or column attributes whose type is a nested table. See also "Nested Table Storage"
	nested_item is the name of a column or a column-qualified attribute whose type is a nested table. storage_table is the name of the storage table. The storage table is created in the same schema and the same tablespace as the parent table.
			CLUSTER	specifies that the table is to be part of the cluster. The columns listed in this clause are the table columns that correspond to the cluster's columns. Generally, the cluster columns of a table are the column or columns that make up its primary key or a portion of its primary key.
	Specify one column from the table for each column in the cluster key. The columns are matched by position, not by name. A clustered table uses the cluster's space allocation. Therefore, do not use the PCTFREE, PCTUSED, INITRANS, or MAXTRANS parameters, the TABLESPACE option, or the STORAGE clause with the CLUSTER option.
	Object tables cannot be part of a cluster.
table_partitioning_clause:	PARTITION BY RANGE	specifies that the table is partitioned on ranges of values from column_list. See also "Partitioned Tables".
	column_list	is an ordered list of columns used to determine into which partition a row belongs. You cannot specify more than 16 columns in column_list. The column_list cannot contain the ROWID pseudocolumn or any columns of datatype ROWID or LONG.
	PARTITION partition_name	specifies the physical partition clause. If partition_name is omitted, Oracle generates a name with the form SYS_Pn for the partition. The partition_name must conform to the rules for naming schema objects and their part as described in "Schema Object Naming Rules".
	VALUES LESS THAN	specifies the noninclusive upper bound for the current partition.
	value_list	is an ordered list of literal values corresponding to column_list in the PARTITION BY RANGE clause. You can substitute the keyword MAXVALUE for any literal in value_list. MAXVALUE specifies a maximum value that will always sort higher than any other value, including NULL. Specifying a value other than MAXVALUE for the highest partition bound imposes an implicit integrity constraint on the table. See Oracle8 Concepts for more information about partition bounds.
parallel_clause	specifies the degree of parallelism for creating the table and the default degree of parallelism for queries on the table once created. This is not a valid option when creating index-organized tables. For more information, see PARALLEL clause.
enable_clause	enables an integrity constraint. See the ENABLE clause.
disable_clause	disables an integrity constraint. See the DISABLE clause.
	Constraints specified in the ENABLE and DISABLE clauses of a CREATE TABLE statement must be defined in the statement. You can also enable and disable constraints with the ENABLE and DISABLE keywords of the CONSTRAINT clause. If you define a constraint but do not explicitly enable or disable it, Oracle enables it by default.
	You cannot use the ENABLE and DISABLE clauses in a CREATE TABLE statement to enable and disable triggers.
AS subquery	inserts the rows returned by the subquery into the table upon its creation. See "Subqueries". Note: This subquery is not supported for index-organized tables with overflow.
	The number of columns in the table must equal the number of expressions in the subquery. The column definitions can specify only column names, default values, and integrity constraints, not datatypes. Oracle derives datatypes and lengths from the subquery. Oracle also follows the following rules for integrity constraints:
	Oracle automatically defines any NOT NULL constraints on columns in the new table that existed on the corresponding columns of the selected table if the subquery selects the column rather than an expression containing the column. A CREATE TABLE statement cannot contain both an AS clause and a referential integrity constraint definition. If a CREATE TABLE statement contains both the AS clause and a CONSTRAINT clause or an ENABLE clause with the EXCEPTIONS option, Oracle ignores the EXCEPTIONS option. If any rows violate the constraint, Oracle does not create the table and returns an error message.
	If all expressions in the subquery are columns, rather than expressions, you can omit the columns from the table definition entirely. In this case, the names of the columns of table are the same as the columns in the subquery.
	For object tables, subquery can contain either one expression corresponding to the table type, or the number of top-level attributes of the table type.
CACHE	for data that will be accessed frequently, specifies that the blocks retrieved for this table are placed at the most recently used end of the LRU list in the buffer cache when a full table scan is performed. This option is useful for small lookup tables.
	As a parameter in the LOB storage clause, CACHE specifies that Oracle preallocates and retains LOB data values in memory for faster access. This is not a valid keyword when creating index-organized tables.
NOCACHE	for data that will not be accessed frequently, specifies that the blocks retrieved for this table are placed at the least recently used end of the LRU list in the buffer cache when a full table scan is performed. For LOBs, the LOB value either is not brought into the buffer cache or is brought into the buffer cache and placed at the least recently used end of the LRU list.
	This is the default behavior except when creating index-organized tables. This is not a valid keyword when creating index-organized tables.
	As a parameter in the LOB storage clause, NOCACHE specifies that LOB values are not preallocated in memory. This is the LOB storage default.

Examples

Tables are created with no data unless a query is specified. You can add rows to a table with the INSERT command.

After creating a table, you can define additional columns, partitions, and integrity constraints with the ADD clause of the ALTER TABLE command. You can change the definition of an existing column or partition with the MODIFY clause of the ALTER TABLE command. To modify an integrity constraint, you must drop the constraint and redefine it.

Example I

To define the EMP table owned by SCOTT, you could issue the following statement:

CREATE TABLE scott.emp 
   (empno     NUMBER        CONSTRAINT pk_emp PRIMARY KEY, 
    ename     VARCHAR2(10)  CONSTRAINT nn_ename NOT NULL 
                            CONSTRAINT upper_ename 
CHECK (ename = UPPER(ename)), 
    job        VARCHAR2(9), 
    mgr      NUMBER         CONSTRAINT fk_mgr 
                            REFERENCES scott.emp(empno), 
    hiredate  DATE          DEFAULT SYSDATE, 
    sal       NUMBER(10,2)  CONSTRAINT ck_sal 
CHECK (sal > 500), 
       comm      NUMBER(9,0)   DEFAULT NULL, 
       deptno    NUMBER(2)     CONSTRAINT nn_deptno NOT NULL 
                               CONSTRAINT fk_deptno 
                               REFERENCES scott.dept(deptno) ) 
PCTFREE 5 PCTUSED 75;

This table contains 8 columns. The EMPNO column is of datatype NUMBER and has an associated integrity constraint named PK_EMP. The HIRDEDATE column is of datatype DATE and has a default value of SYSDATE, and so on.

This table definition specifies a PCTFREE of 5 and a PCTUSED of 75, which is appropriate for a relatively static table. The definition also defines integrity constraints on some columns of the EMP table.

Example II

To define the sample table SALGRADE in the HUMAN_RESOURCE tablespace with a small storage capacity and limited allocation potential, issue the following statement:

CREATE TABLE salgrade 
   ( grade  NUMBER  CONSTRAINT pk_salgrade 
                    PRIMARY KEY 
                    USING INDEX TABLESPACE users_a, 
     losal  NUMBER, 
     hisal  NUMBER ) 
TABLESPACE human_resource 
STORAGE (INITIAL     6144  
         NEXT        6144 
         MINEXTENTS     1  
         MAXEXTENTS     5 
         PCTINCREASE    5);

The above statement also defines a PRIMARY KEY constraint on the GRADE column and specifies that the index Oracle creates to enforce this constraint is created in the USERS_A tablespace.

For more examples of defining integrity constraints, see the CONSTRAINT clause. For examples of enabling and disabling integrity constraints, see the ENABLE clause and the DISABLE clause.

Example III

When using parallel query, the fastest way to create a table that has the same columns as the EMP table, but only for those employees in department 10, is to issue a command similar to the following:

CREATE TABLE emp_tmp
   NOLOGGING
   PARALLEL (DEGREE 3)
   AS SELECT * FROM emp WHERE deptno = 10;

Using parallelism speeds up the creation of the table because Oracle uses three processes to create the table. After the table is created, querying the table is also faster, because the same degree of parallelism is used to access the table.

LOB Column Example

The following example creates table LOB_TAB with two LOB columns and specifies the LOB storage characteristics:

CREATE TABLE lob_tab (col1 BLOB, col2 CLOB)
STORAGE (INITIAL 256 NEXT 256)
LOB (col1, col2) STORE AS
   (TABLESPACE lob_seg_ts
    STORAGE (INITIAL 6144 NEXT 6144)
    CHUNK 4
    NOCACHE LOGGING
       INDEX (TABLESPACE lob_index_ts
       STORAGE (INITIAL 256 NEXT 256)
   )
);

Index-Organized Tables

Index-organized tables are special kinds of tables that keep data sorted on the primary key and are therefore best suited for primary key-based access and manipulation.

An index-organized table is an alternative to

a nonclustered table indexed on the primary key by using the CREATE INDEX command
a clustered table stored in an indexed cluster that has been created using the CREATE CLUSTER command that maps the primary key for the table to the cluster key

Index-organized tables differ from other kinds of tables in that Oracle maintains the table rows in a B*-tree index built on the primary key. However, the index row contains both the primary key column values and the associated non-key column values for the corresponding row.

You must specify a primary key for an index-organized table, because the primary key uniquely identifies a row. Use the primary key instead of the ROWID for directly accessing index-organized rows.

Example

The following statement creates an index-organized table:

CREATE TABLE docindex
  ( token CHAR(20),
    doc_oid INTEGER,
    token_frequency SMALLINT,
    token_occurrence_data VARCHAR(512),
       CONSTRAINT pk_docindex PRIMARY KEY (token, doc_oid) )
  ORGANIZATION INDEX TABLESPACE text_collection
  PCTTHRESHOLD 20 INCLUDING token_frequency
  OVERFLOW TABLESPACE text_collection_overflow;

Partitioned Tables

A partitioned table consists of a number of pieces all of which have the same logical attributes. For example, all partitions share the same column and constraint definitions.

You can create a partitioned table with just one partition. Note, however, that a partitioned table with one partition is different from a nonpartitioned table. For instance, you cannot add a partition to a nonpartitioned table.

Example

The following example creates a table with three partitions:

CREATE TABLE stock_xactions
   (stock_symbol CHAR(5),
    stock_series CHAR(1),
    num_shares NUMBER(10),
    price NUMBER(5,2),
    trade_date DATE)
STORAGE (INITIAL 100K NEXT 50K) LOGGING
PARTITION BY RANGE (trade_date)
  (PARTITION sx1992 VALUES LESS THAN (TO_DATE('01-JAN-1993','DD-MON-YYYY'))
     TABLESPACE ts0 NOLOGGING,
   PARTITION sx1993 VALUES LESS THAN (TO_DATE('01-JAN-1994','DD-MON-YYYY'))
     TABLESPACE ts1,
   PARTITION sx1994 VALUES LESS THAN (TO_DATE('01-JAN-1995','DD-MON-YYYY'))
     TABLESPACE ts2);

For information about partitioned table maintenance operations, see the Oracle8 Administrator's Guide.

Object Tables

In order to have Oracle assign an object identifier to an object, the object must reside in a special kind of table called an object table. Objects residing in an object table are referenceable. For more information about using REFs, see "User-Defined Types", "User Functions", "Expressions", CREATE TYPE, andOracle8 Administrator's Guide.

The columns of an object table correspond to the top-level attributes of the corresponding type. Each row will contain an object instance, and each instance will be assigned a unique, system-generated object identifier (OID) when a row is inserted.

Example I

For example, consider object type DEPT_T:

CREATE TYPE dept_t AS OBJECT
( dname VARCHAR2(100), 
  address VARCHAR2(200) );

Object table DEPT holds department objects of type DEPT_T:

CREATE TABLE dept OF dept_t;

Example II

The following example creates object table SALESREPS with a user-defined object type, SALESREP_T:

CREATE OR REPLACE TYPE salesrep_t AS OBJECT
  ( repId NUMBER,
    repName VARCHAR2(64));
CREATE TABLE salesreps OF salesrep_t;

Nested Table Storage

Creating a table with columns of type TABLE implicitly creates a storage table for each nested table column. The storage table is created in the same tablespace as its parent table (using the default storage characteristics) and stores the nested table values of the column for which it was created.

You cannot query or perform DML statements on the storage table directly, but you can modify the nested table column storage characteristics by using the name of storage table in an ALTER TABLE statement. For information about modifying nested table column storage characteristics, see ALTER TABLE.

Example

The following example creates relational table EMPLOYEE with a nested table column PROJECTS:

CREATE TABLE employee (empno NUMBER, name CHAR(31),
projects PROJ_TABLE_TYPE)
NESTED TABLE projects STORE AS nested_proj_table;

REFs

A REF value is a reference to a row in an object table. A table can have top-level REF columns or REF attributes embedded within an object type column. In general, if a table has a REF column, each REF value in the column could reference a row in a different object table. A SCOPE clause restricts the scope of references to a single table.

For example, if you create an object table DEPT which stores all the departments in an organization, you could then create table EMP that contains a REF column (E_DEPT) to point to the department in which each employee works. Because all employees work in some department stored in the DEPT table, a scope clause can be specified on the E_DEPT column of EMP to restrict the scope of references to the DEPT table.

You can increase the performance of queries with dereference operations and decrease the amount of storage needed for REF values by using the scope clause. Note that a SCOPE clause does not have the same semantics as referential constraints. Referential constraints do not allow dangling references. Also, referential constraints do not necessarily restrict the scope of references to a single table (one can specify multiple referential constraints on the same foreign key, with each one of them pointing to a different table).

You can also store REF values with or without ROWIDs. Storing REF values WITH ROWID can enhance the performance of dereference operations, but takes up more space. The default behavior is to store REF values without the ROWID.

You cannot specify REF clauses on REF columns in nested tables using the CREATE TABLE statement. To specify REF clauses on REF columns in nested tables, use the ALTER TABLE to modify the nested table 's storage table.

Example

The following example creates object type DEPT_T and object table DEPT to store instances of all departments. A table with a scoped REF is then created.

CREATE TYPE dept_t AS OBJECT
( dname VARCHAR2(100),
  address VARCHAR2(200) );

CREATE TABLE dept OF dept_t;

CREATE TABLE emp
( ename VARCHAR2(100),
  enumber NUMBER,
  edept REF dept_t SCOPE IS dept );

Constraints on Object Type Columns

You can create UNIQUE, PRIMARY KEY, and REFERENCES constraints on scalar attributes of object type columns. You can also create NOT NULL constraints on object type columns, and CHECK constraints that reference object type columns or any attribute of an object type column.

Example

CREATE TYPE address AS OBJECT
  ( hno NUMBER,
    street VARCHAR2(40),
    city VARCHAR2(20),
    zip VARCHAR2(5),
    phone VARCHAR2(10) );

CREATE TYPE person AS OBJECT
  ( name VARCHAR2(40),
    dateofbirth DATE,
    homeaddress address,
    manager REF person );

CREATE TABLE persons OF person
  ( homeaddress NOT NULL
      UNIQUE (homeaddress.phone),
      CHECK (homeaddress.zip IS NOT NULL),
      CHECK (homeaddress.city <> 'San Francisco') );

CREATE TABLESPACE

Purpose

To create a tablespace. A tablespace is an allocation of space in the database that can contain schema objects. See also "About Tablespaces".

Prerequisites

You must have CREATE TABLESPACE system privilege. Also, the SYSTEM tablespace must contain at least two rollback segments including the SYSTEM rollback segment.

Syntax

filespec: See "Filespec".

autoextend_clause::=

storage_clause: See STORAGE clause.

Keywords and Parameters

tablespace DATAFILE filespec	is the name of the tablespace to be created.
	specifies the datafile or files to make up the tablespace. See "Filespec".

autoextend_clause	enables or disables the automatic extension of the datafile.
	OFF	disables autoextend if it is turned on. NEXT and MAXSIZE are set to zero. Values for NEXT and MAXSIZE must be respecified in further ALTER TABLESPACE AUTOEXTEND commands.
	ON	enables autoextend.
	NEXT	specifies the disk space to allocate to the datafile when more extents are required.
	MAXSIZE	specifies the maximum disk space allowed for allocation to the datafile.
	UNLIMITED	sets no limit on allocating disk space to the datafile.
MINIMUM EXTENT integer	controls free space fragmentation in the tablespace by ensuring that every used and/or free extent size in a tablespace is at least as large as, and is a multiple of, integer. For more information about using MINIMUM EXTENT to control space fragmentation, see Oracle8 Administrator's Guide.
LOGGING/NOLOGGING	specifies the default logging attributes of all tables, index, and partitions within the tablespace. LOGGING is the default.
	The tablespace-level logging attribute can be overridden by logging specifications at the table, index, and partition levels. Only the following operations support the NOLOGGING mode: DML: direct-load INSERT (serial or parallel) Direct Loader (SQL*Loader) DDL: CREATE TABLE ... AS SELECT CREATE INDEX ALTER INDEX ... REBUILD ALTER INDEX ... REBUILD PARTITION ALTER INDEX ... SPLIT PARTITION ALTER TABLE ... SPLIT PARTITION ALTER TABLE ... MOVE PARTITION
	In NOLOGGING mode, data is modified with minimal logging (to mark new extents invalid and to record dictionary changes). When applied during media recovery, the extent invalidation records mark a range of blocks as logically corrupt, because the redo data is not logged. Therefore, if you cannot afford to lose the object, you should take a backup after the NOLOGGING operation.
			DEFAULT storage_clause	specifies the default storage parameters for all objects created in the tablespace. For information on storage parameters, see the STORAGE clause.
ONLINE	makes the tablespace available immediately after creation to users who have been granted access to the tablespace.
OFFLINE	makes the tablespace unavailable immediately after creation.
	If you omit both the ONLINE and OFFLINE options, Oracle creates the tablespace online by default. The data dictionary view DBA_TABLESPACES indicates whether each tablespace is online or offline.
PERMANENT	specifies that the tablespace will be used to hold permanent objects. This is the default.
TEMPORARY	specifies that the tablespace will be used only to hold temporary objects--for example, segments used by implicit sorts to handle ORDER BY clauses.

About Tablespaces

A tablespace is an allocation of space in the database that can contain any of the following segments:

data segments
index segments
rollback segments
temporary segments

All databases have at least one tablespace, SYSTEM, which Oracle creates automatically when you create the database.

When you create a tablespace, it is initially a read-write tablespace. After creating the tablespace, you can subsequently use the ALTER TABLESPACE command to take it offline or online, add datafiles to it, or make it a read-only tablespace.

Many schema objects have associated segments that occupy space in the database. These objects are located in tablespaces. The user creating such an object can optionally specify the tablespace to contain the object. The owner of the schema containing the object must have space quota on the object's tablespace. You can assign space quota on a tablespace to a user with the QUOTA clause of the CREATE USER or ALTER USER commands.

Warning:

For operating systems that support raw devices, be aware that the STORAGE clause REUSE keyword has no meaning when specifying a raw device as a datafile in a CREATE TABLESPACE command; such a command will always succeed even if REUSE is not specified.

Example I

This command creates a tablespace named TABSPACE_2 with one data file:

CREATE TABLESPACE tabspace_2 
   DATAFILE 'diska:tabspace_file2.dat' SIZE 20M 
   DEFAULT STORAGE (INITIAL 10K NEXT 50K 
                    MINEXTENTS 1 MAXEXTENTS 999 
                    PCTINCREASE 10) 
   ONLINE;

Example II

This command creates a tablespace named TABSPACE_3 with one data file; when more space is required, 50 kilobyte extents will be added up to a maximum size of 10 megabytes:

CREATE TABLESPACE tabspace_5 
   DATAFILE 'diskb:tabspace_file3.dat' SIZE 500K REUSE
   AUTOEXTEND ON NEXT 500K MAXSIZE 10M;

Example III

This command creates tablespace TABSPACE_5 with one data file and allocates every extent as a multiple of 64K:

CREATE TABLESPACE tabspace_3 
   DATAFILE 'tabspace_file5.dbf' SIZE 2M
   MINIMUM EXTENT 64K
   DEFAULT STORAGE (INITIAL 128K NEXT 128K)
   LOGGING;

CURRVAL	returns the current value of the sequence.
NEXTVAL	increments the sequence and returns the new value.

DML Statements	DDL Statements
SELECT	AUDIT
INSERT	NOAUDIT
UPDATE	GRANT
DELETE	REVOKE
EXPLAIN PLAN	COMMENT
LOCK TABLE

CREATE ROLE

Purpose

Prerequisites

Syntax

Keywords and Parameters

Using Roles

Roles Predefined by Oracle

Example I

Example II

Related Topics

CREATE ROLLBACK SEGMENT

Purpose

Prerequisites

Syntax

Keyword and Parameters

Rollback Segments and Tablespaces

Example

Related Topics

CREATE SCHEMA

Purpose

Prerequisites

Syntax

Keyword and Parameters

Creating Schemas

Example

Related Topics

CREATE SEQUENCE

Purpose

Prerequisites

Syntax

Keywords and Parameters

Using Sequences

Sequence Defaults

Incrementing Sequence Values

Caching Sequence Numbers

Accessing Sequence Values

Example

Related Topics

CREATE SNAPSHOT

Purpose

Prerequisites

Syntax

Keywords and Parameters

About Snapshots

Types of Snapshots

Refreshing Snapshots

Specifying Refresh Modes

Fast

Complete

Example

Specifying Automatic Refresh Times

Example

Specifying Rollback Segments

Example

Specifying Primary-Key or ROWID Snapshots

Example I

Example II

Partitioned Snapshots

Related Topics

CREATE SNAPSHOT LOG

Purpose

Prerequisites

Syntax

Keywords and Parameters

Using Snapshot Logs

Example

Recording Primary Keys, ROWIDs, and Filter Columns

Example I

Example II

Example III

Related Topics

CREATE SYNONYM

Purpose

Prerequisites

Syntax

Keywords and Parameters

Using Synonyms

Scope of Synonyms

Example I

Example II