create table [Oracle SQL] | ||
PrerequisitesFor a user to be able to create a table, he needs the create table system privilege, otherwise he'll receive the ORA-01031: insufficient privileges error message. Additionally, the user needs to have enough quota on the tablespace where he wants to create the table. Heap tablesUsually, if we refer to tables, we mean heap tables, although there are other types as well. A heap table is created like this: create table t ( a number, b varchar2(10) ) It is possible to create the constraints together with the create statement. As a foreign key references a known type, it is not necessary to specify the foreign key's column type. create table orders ( order_id number primary key order_dt date, cust_id references customers ) A primary key needs to have an associated (unique) index. It is possible to specify on what tablespace this index is going to be created: create table orders ( order_id number, order_dt date, cust_id references customer constraint pk_orders (order_id) using index tablespace ts_idx ) Index organized tables (IOT)create table iot_ ( a number, b varchar2(10), constraint pk_iot_ primary key (a, b) ) organization index; Global temporary tablesThe following example shows the difference for redo generated when using global temporary tables and "ordinary" heap tables. It uses the redo_diff package. create global temporary table gtt_ ( x_ varchar2(100) ) on commit delete rows; create table t_ ( x_ varchar2(100) ); exec redo_diff.diff_it; declare i number; begin for i in 1 .. 1000 loop insert into gtt_ values(dbms_random.string('a',99)); end loop; end; / exec redo_diff.diff_it; declare i number; begin for i in 1 .. 1000 loop insert into t_ values(dbms_random.string('a',99)); end loop; end; / exec redo_diff.diff_it; Organization externalThe following create table statement creates an external table. create table (....) organization external ( type oracle_loader default directory some_dir access parameters ( records delimited by newline fields terminated by ',' missing field are values null ) location ('some_file.csv') ) reject limit unlimited; Nested tablescreate or replace type item as object ( item_id Number ( 6 ), descr varchar2(30 ), quant Number ( 4,2) ); / create or replace type items as table of item; / create table bag_with_items ( bag_id number(7) primary key, bag_name varchar2(30) not null, the_items_in_the_bag items ) nested table the_items_in_the_bag store as bag_items_nt; Adding a unique constraint for item_id: alter table bag_items_nt add constraint uq_item_id unique(item_id); of XMLTypecreate table table_name of xmltype; create table table_name of xmltype XMLSchema "some_url" element "root-element"; create table table_name (columns..., xml_doc) XMLType xmltype column xml_doc element "root-element"; Creates a table for XML DB. Create table ... as selectThis is one of the few statements that can make use of the nologging option. In fact, if the database is running noarchive log, the create table .. as select statement is nologging.. Mintrans and Maxtranspctfree and pctusedDisplaying a tables definitionIn SQL*Plus, a table's definition can be displayed with describe. A more verbose (and more complete) output can be optained with dbms_metadata.get_ddl. See also On a better describe. |
create table [Oracle SQL] | ||
PrerequisitesFor a user to be able to create a table, he needs the create table system privilege, otherwise he'll receive the ORA-01031: insufficient privileges error message. Additionally, the user needs to have enough quota on the tablespace where he wants to create the table. Heap tablesUsually, if we refer to tables, we mean heap tables, although there are other types as well. A heap table is created like this: create table t ( a number, b varchar2(10) ) It is possible to create the constraints together with the create statement. As a foreign key references a known type, it is not necessary to specify the foreign key's column type. create table orders ( order_id number primary key order_dt date, cust_id references customers ) A primary key needs to have an associated (unique) index. It is possible to specify on what tablespace this index is going to be created: create table orders ( order_id number, order_dt date, cust_id references customer constraint pk_orders (order_id) using index tablespace ts_idx ) Index organized tables (IOT)create table iot_ ( a number, b varchar2(10), constraint pk_iot_ primary key (a, b) ) organization index; Global temporary tablesThe following example shows the difference for redo generated when using global temporary tables and "ordinary" heap tables. It uses the redo_diff package. create global temporary table gtt_ ( x_ varchar2(100) ) on commit delete rows; create table t_ ( x_ varchar2(100) ); exec redo_diff.diff_it; declare i number; begin for i in 1 .. 1000 loop insert into gtt_ values(dbms_random.string('a',99)); end loop; end; / exec redo_diff.diff_it; declare i number; begin for i in 1 .. 1000 loop insert into t_ values(dbms_random.string('a',99)); end loop; end; / exec redo_diff.diff_it; Organization externalThe following create table statement creates an external table. create table (....) organization external ( type oracle_loader default directory some_dir access parameters ( records delimited by newline fields terminated by ',' missing field are values null ) location ('some_file.csv') ) reject limit unlimited; Nested tablescreate or replace type item as object ( item_id Number ( 6 ), descr varchar2(30 ), quant Number ( 4,2) ); / create or replace type items as table of item; / create table bag_with_items ( bag_id number(7) primary key, bag_name varchar2(30) not null, the_items_in_the_bag items ) nested table the_items_in_the_bag store as bag_items_nt; Adding a unique constraint for item_id: alter table bag_items_nt add constraint uq_item_id unique(item_id); of XMLTypecreate table table_name of xmltype; create table table_name of xmltype XMLSchema "some_url" element "root-element"; create table table_name (columns..., xml_doc) XMLType xmltype column xml_doc element "root-element"; Creates a table for XML DB. Create table ... as selectThis is one of the few statements that can make use of the nologging option. In fact, if the database is running noarchive log, the create table .. as select statement is nologging.. Mintrans and Maxtranspctfree and pctusedDisplaying a tables definitionIn SQL*Plus, a table's definition can be displayed with describe. A more verbose (and more complete) output can be optained with dbms_metadata.get_ddl. See also On a better describe. |
聯(lián)系客服
