You can execute the CREATE TABLE statement to create a table.
For information about how to create partitioned tables, see Create a partitioned table.
Create a non-partitioned table
A non-partitioned table is a table that has only one partition.
Example:
obclient>CREATE TABLE table_name1(w_id int,
w_ytd decimal(12,2),
w_tax decimal(4,4),
w_name varchar(10),
w_street_1 varchar(20),
w_street_2 varchar(20),
w_city varchar(20),
w_state char(2),
w_zip char(9),
unique(w_name, w_city),
primary key(w_id)
);
Query OK, 0 rows affected (0.09 sec)
obclient>CREATE TABLE table_name2 (c_w_id int NOT NULL,
c_d_id int NOT null,
c_id int NOT null,
c_discount decimal(4, 4),
c_credit char(2),
c_last varchar(16),
c_first varchar(16),
c_middle char(2),
c_balance decimal(12, 2),
c_ytd_payment decimal(12, 2),
c_payment_cnt int,
c_credit_lim decimal(12, 2),
c_street_1 varchar(20),
c_street_2 varchar(20),
c_city varchar(20),
c_state char(2),
c_zip char(9),
c_phone char(16),
c_since date,
c_delivery_cnt int,
c_data varchar(500),
index icust(c_last, c_d_id, c_w_id, c_first, c_id),
FOREIGN KEY (c_w_id) REFERENCES table_name1(w_id),
primary key (c_w_id, c_d_id, c_id)
);
Query OK, 0 rows affected (0.10 sec)
In the preceding example, two tables are created. Some constraints, including the primary keys and foreign keys, are defined on different columns. For more information about primary keys and foreign keys, see Define column constraints.
Pay attention to data types when you create table columns. For more information about SQL data types, see Data types.
Note
To ensure performance and facilitate maintenance, we recommend that you specify a primary key or a unique key when you create a table. If no existing field can be used as the primary key, you can add a numeric column as the primary key, and use the Sequence object of the Oracle tenant to generate sequential values for this column. For more information about sequences, see Manage sequences.
Create a table by copying the data in an existing table
You can execute the CREATE TABLE AS SELECT statement to copy data types and data from a table. This statement does not copy table attributes. For example, indexes and constraints such as NOT NULL are lost during the copy operation.
Example:
obclient>CREATE TABLE t2_copy AS SELECT * FROM t2;
Query OK, 0 rows affected (0.10 sec)
You cannot use the CREATE TABLE LIKE statement to copy table schemas.