Prerequisites
Additionally, the user needs to have enough quota on the tablespace where he wants to create the table. Heap tables
Usually, 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 tables
The 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 external
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 tables
create 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 XMLType
create 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";
Create table ... as select
This is one of the few statements that can make use of the nologging option. Mintrans and Maxtrans
pctfree and pctused
Displaying a tables definition
|
