CREATE TABLE [schema.]table ( { column datatype [DEFAULT expr] [column_constraint] ... | table_constraint} [, { column datatype [DEFAULT expr] [column_constraint] ... | table_constraint} ]...) [ [PCTFREE integer] [PCTUSED integer] [INITRANS integer] [MAXTRANS integer] [TABLESPACE tablespace] [STORAGE storage_clause] [ PARALLEL ( [ DEGREE { integer | DEFAULT } ] [ INSTANCES { integer | DEFAULT } ] ) | NOPARALLEL ] [ CACHE | NOCACHE ] | [CLUSTER cluster (column [, column]...)] ] [ ENABLE enable_clause | DISABLE disable_clause ] ... [AS subquery] where: 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 to be created. column specifies the name of a column of the table. The number of columns in a table can range from 1 to 254. datatype is the datatype of a column. 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. 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_constraint defines an integrity constraint as part of the column definition. table_constraint defines an integrity constraint as part of the table definition. PCTFREE specifies the percentage of space in each of the table's data blocks reserved for future updates to the table's rows. The value of PCTFREE must be a positive integer from 1 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 commands that create and alter clusters, indexes, snapshots, and snapshot logs. The combination of PCTFREE and PCTUSED determines whether inserted rows will go 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. 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 commands that create and alter clusters, snapshots, and snapshot logs. The sum of PCTFREE and PCTUSED must be less than 100. You can use PCTFREE and PCTUSED together use space within a table more efficiently. INITRANS specifies the initial number of transaction entries allocated within each data block allocated to the table. 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 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. 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 clusters, snapshots, and snapshot logs as in tables. TABLESPACE specifies the tablespace in which Oracle creates the table. If you omit this option, then Oracle creates the table in the default tablespace of the owner of the schema containing the table. STORAGE specifies the storage characteristics for the table. This clause has performance ramifications for large tables. Storage should be allocated to minimize dynamic allocation of additional space. PARALLEL DEGREE specifies the number of query server processes that can scan the table in parallel. Either specify a positive integer or DEFAULT which signifies to use the initialization parameter PARALLEL_DEFAULT_SCANSIZE to estimate the number of query servers to use. INSTANCES specifies the minimum number of instances that need to be available before the table can be spread across all available instances of a Parallel Server. A positive integer specifies the number of instances. DEFAULT signifies that the parameter PARALLEL_MAX_PARTITIONSIZE is used to calculate whether a table is split across all instances' buffer caches. NOPARALLEL specifies that queries on this table are not performed in parallel by default. A hint in the query still causes the query to be performed in parallel.
CACHE specifies that blocks of this table are placed on the most recently used end of the LRU list of the buffer cache when the a full table scan is performed. This option is useful for small lookup tables. NOCACHE specifies that blocks of the table in the buffer cache follow the standard LRU algorithm when a full table scan is performed. 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 comprise 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. Since a clustered table uses the cluster's space allocation, do not use the PCTFREE, PCTUSED, INITRANS, or MAXTRANS parameters, the TABLESPACE option, or the STORAGE clause in conjunction with the CLUSTER option. ENABLE enables an integrity constraint. DISABLE disables an integrity constraint. 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. If you include this clause, the column definitions can only specify column names, default values, and integrity constraints, not datatypes. Oracle derives column datatypes and lengths from the subquery. Oracle also automatically defines NOT NULL constraints on columns in the new table if they existed on the corresponding columns of the selected table and the subquery does not modify the column value with a SQL function or operator. A CREATE TABLE statement cannot contain both the AS clause and a referential integrity constraint definition. The number of columns must equal the number of expressions in the subquery. If all expressions in the subquery are columns, 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. PREREQUISITES: To create a 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.
( { column datatype [DEFAULT expr] [column_constraint] ... | table_constraint}
[, { column datatype [DEFAULT expr] [column_constraint] ...
| table_constraint} ]...)
[ [PCTFREE integer] [PCTUSED integer]
[INITRANS integer] [MAXTRANS integer]
[TABLESPACE tablespace]
[STORAGE storage_clause]
[ PARALLEL ( [ DEGREE { integer | DEFAULT } ]
[ INSTANCES { integer | DEFAULT } ]
) | NOPARALLEL ]
[ CACHE | NOCACHE ]
| [CLUSTER cluster (column [, column]...)] ]
[ ENABLE enable_clause
| DISABLE disable_clause ] ...
[AS subquery] where: 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 to be created. column specifies the name of a column of the table. The number of columns
in a table can range from 1 to 254. datatype
is the datatype of a column. 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. 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_constraint
defines an integrity constraint as part of the column definition. table_constraint
defines an integrity constraint as part of the table definition. PCTFREE
specifies the percentage of space in each of the table's data blocks
reserved for future updates to the table's rows. The value of PCTFREE must be a positive integer from 1 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 commands that create and alter
clusters, indexes, snapshots, and snapshot logs. The combination of PCTFREE and PCTUSED determines whether inserted rows will go 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. 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 commands that create and alter
clusters, snapshots, and snapshot logs. The sum of PCTFREE and PCTUSED must be less than 100. You can use
PCTFREE and PCTUSED together use space within a table more
efficiently. INITRANS
specifies the initial number of transaction entries allocated within
each data block allocated to the table. 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 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. 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 clusters, snapshots, and snapshot logs as in tables. TABLESPACE
specifies the tablespace in which Oracle creates the table. If you
omit this option, then Oracle creates the table in the default
tablespace of the owner of the schema containing the table. STORAGE
specifies the storage characteristics for the table. This clause
has performance ramifications for large tables. Storage should be allocated to minimize dynamic allocation of additional space. PARALLEL
DEGREE specifies the number of query server processes that can scan
the table in parallel. Either specify a positive integer or DEFAULT
which signifies to use the initialization parameter
PARALLEL_DEFAULT_SCANSIZE to estimate the number of query servers to use. INSTANCES specifies the minimum number of instances that need to be available before the table can be spread across all available instances
of a Parallel Server. A positive integer specifies the number of
instances. DEFAULT signifies that the parameter PARALLEL_MAX_PARTITIONSIZE
is used to calculate whether a table is split across all instances' buffer
caches. NOPARALLEL
specifies that queries on this table are not performed in parallel
by default. A hint in the query still causes the query to be performed in parallel.
specifies that blocks of this table are placed on the most recently
used end of the LRU list of the buffer cache when the a full table scan
is performed.
This option is useful for small lookup tables. NOCACHE
specifies that blocks of the table in the buffer cache follow the
standard LRU algorithm when a full table scan is performed. 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 comprise 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. Since a clustered table uses the cluster's space allocation, do not use the
PCTFREE, PCTUSED, INITRANS, or MAXTRANS parameters, the TABLESPACE
option, or the STORAGE clause in conjunction with the CLUSTER
option. ENABLE
enables an integrity constraint. DISABLE
disables an integrity constraint. 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. If you include this clause, the column definitions can only specify
column names, default values, and integrity constraints, not
datatypes. Oracle derives column datatypes and lengths from the
subquery. Oracle also automatically defines NOT NULL constraints on
columns in the new table if they existed on the corresponding
columns of the selected table and the subquery does not modify the column value with a SQL function or operator. A CREATE TABLE
statement cannot contain both the AS clause and a referential
integrity constraint definition. The number of columns must equal the number of expressions in the
subquery. If all expressions in the subquery are columns, 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. PREREQUISITES: To create a 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.
我还是不懂,能解释一下oracle怎样仿照建表对字符串进行处理的吗?
"INFOSEND"."ADDRESSBOOK" 怎么用双引号?infosend是用户名吗?
"INFOSEND"."ADDRESSBOOK" 生成脚本的问题
加双引号是为了强制表名大写,尽管ORACLE会默认将表名大写。
附注:
如果你用双引号里面的表名是小写的话,以后你引用表名也必须
用小写表名才行,大写ORACLE就不认了。
如果不用双引号的话,你引用表名则大写小写都可以使用。字段上的双引号也是这个原因。