Alter Table Storage in Oracle Database

Table Creation

This section illustrates the creation of a standard table (invoice) and a table which contains a blob column (document).

CREATE TABLE
   INVOICE (
     INV_ID   NUMBER(4),
     INV_DATE DATE)
  PCTFREE 20
  PCTUSED 50
STORAGE (
   INITIAL 200K NEXT 200K
   PCTINCREASE 0 MAXEXTENTS 50 )
TABLESPACE ts01
LOGGING ;


CREATE TABLE documents
 ( doc_id       INTEGER
   doc_text     BLOB)
STORAGE (INITIAL 256 NEXT 256)
LOB (doc_text) STORE AS
   (TABLESPACE ts04
    STORAGE (INITIAL 6144 NEXT 6144)
    CHUNK 4
    NOCACHE NOLOGGING
       INDEX (TABLESPACE ts05
       STORAGE (INITIAL 256 NEXT 256)
             )
   );


/* index-organized table, similar to Sybase's clustered index construct */

CREATE TABLE postal_customer
( zip     char(5),
  addr    numeric(5),
  dir     char(1),
  street  varchar2(20),
  last_name  varchar2(20),
  first_name varchar2(20),
  item_ct    numeric(3)
)
CONSTRAINT pk_postal PRIMARY KEY (zip,addr,dir,street)
ORGANIZATION INDEX 
TABLESPACE ts05
PCTTHRESHOLD 25
OVERFLOW TABLESPACE ts06 ;


/* cloning a table without data */

create table city2 as select * from city where 1=2 ;

create table brand2 as select * from brand where 1=2 ;

　

Altering table storage

alter table employee move
 STORAGE (
    INITIAL 200K
    NEXT 200K
    PCTINCREASE 0 
    MAXEXTENTS 50 )
 TABLESPACE PROD_DATA_04 ;