create table [Oracle SQL]

Prerequisites

For 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 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

The 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 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";

Creates a table for XML DB.

Create table ... as select

This 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 Maxtrans

See mintrans and maxtrans

pctfree and pctused

See pctfree and pctused

Displaying a tables definition

In 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.


	René Nyffenegger's collection of things on the web
	René Nyffenegger on Oracle - Most wanted - Feedback - Follow @renenyffenegger