Oracle8
SQL Reference
Release 8.0 A58225-01 |
|
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.
You must have CREATE ROLE system privilege.
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. |
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
Note: When you create a role IDENTIFIED GLOBALLY, Oracle does not grant you the role as it does with nonglobal roles. |
Some roles are defined by SQL scripts provided on your distribution media. The following roles are predefined:
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.
The following example creates global role VENDOR:
CREATE ROLE vendor IDENTIFIED GLOBALLY;
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.
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.
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.
storage_clause: See the STORAGE
clause.
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. |
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:
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.
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);
To create multiple tables and views and perform multiple
grants in a single transaction. See also "Creating Schemas".
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.
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. |
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:
The statements within a CREATE SCHEMA statement can also reference existing objects:
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. |
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;
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".
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.
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. |
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.
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.
You can create a sequence so that its values increment in one of following ways:
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.
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.
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".
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.
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.
The following prerequisites apply to creating snapshots:
By default, Oracle creates all new snapshots as primary key snapshots. To create a snapshot:
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.
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.
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". |
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.
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):
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.
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.
Use the FAST or COMPLETE options of the REFRESH clause to
specify the refresh mode.
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:
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.
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.
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.
To cause Oracle to refresh a snapshot automatically, you must perform the following tasks:
For more information on these initialization parameters,
see Oracle8 Reference.
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:
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.
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;
To create a primary-key snapshot you must:
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.
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;
The following example creates a ROWID snapshot:
CREATE SNAPSHOT emp_data WITH ROWID AS SELECT * FROM emp_table73;
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:
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.
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".
The privileges required to create a snapshot log directly relate to the privileges necessary to create the underlying objects associated with a snapshot log.
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.
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.
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. |
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.
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.
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.
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;
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;
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);
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".
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.
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:
|
|
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. |
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:
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.
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:
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;
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;
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.
To create a table, the basic structure to hold user data, specifying the following information:
For illustrations of some of these purposes, "Examples".
Note: Descriptions of commands and clauses preceded by |
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.
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.
Relational table definition
::=
,
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"
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. |
|
|
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". |
|
|
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.
|
|
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: |
|
|
|
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. |
|
|
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 |
||
|
|
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. |
|
|
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). |
|
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. |
|
|
|
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,
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. |
|
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" |
|
|
||
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. |
|
|
|
|
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: |
|
|
|
|
|
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. |
|
|
|
|
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. |
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.
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.
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.
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.
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 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
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.
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;
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.
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.
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.
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;
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;
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.
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;
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.
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 );
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.
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') );
To create a tablespace. A tablespace is an allocation
of space in the database that can contain schema objects. See also "About
Tablespaces".
You must have CREATE TABLESPACE system privilege. Also, the
SYSTEM tablespace must contain at least two rollback segments including
the SYSTEM rollback segment.
filespec: See
"Filespec".
storage_clause:
See STORAGE clause.
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: DDL: |
|
|
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. |
A tablespace is an allocation of space in the database that can contain any of the following 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.
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;
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;
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;