CREATE TABLE Options
 
BUFFER POOL      
Defines a DEFAULT buffer pool (cache) FOR a schema object.
 
 
<KEEP | RECYCLE | DEFAULT>
CREATE TABLE buffer_test (
testcol  VARCHAR2(20))
STORAGE (buffer_pool DEFAULT);
 
SELECT table_name, buffer_pool
FROM user_tables;
 
ALTER TABLE buffer_test STORAGE (buffer_pool RECYCLE);
 
SELECT table_name, buffer_pool
FROM user_tables;
 
ALTER TABLE buffer_test STORAGE (buffer_pool KEEP);
 
SELECT table_name, buffer_pool
FROM user_tables;
 
Note: Additional information can be found here.
 
CACHE      
USE the CACHE clauses TO indicate how Oracle Database 
should store blocks IN the buffer cache. IF you specify 
neither CACHE nor NOCACHE, THEN:
 
    * IN a CREATE TABLE statement, NOCACHE IS the DEFAULT
    * IN an ALTER TABLE statement, the existing VALUE IS 
      NOT changed. 
 
CACHE
FOR data that IS accessed frequently, this clause indicates 
that the blocks retrieved FOR this TABLE are placed AT the 
most recently used END OF the LEAST recently used (LRU) list 
IN the buffer cache WHEN a full TABLE scan IS performed. This 
attribute IS useful FOR small lookup tables.
 
You cannot specify CACHE FOR an index-organized TABLE. However, 
index-organized tables implicitly provide CACHE behavior.
 
NOCACHE
FOR data that IS NOT accessed frequently, this clause indicates 
that the blocks retrieved FOR this TABLE are placed AT the 
LEAST recently used END OF the LRU list IN the buffer cache 
WHEN a full TABLE scan IS performed. NOCACHE IS the DEFAULT FOR 
LOB storage.
 
AS a parameter IN the LOB_storage_clause, NOCACHE specifies 
that the LOB VALUE either IS NOT brought INTO the buffer cache 
OR IS brought INTO the buffer cache AND placed AT the LEAST 
recently used END OF the LRU list. The latter IS the DEFAULT 
behavior.
 
<CACHE | NOCACHE>
CREATE TABLE heap_cache (
testcol  VARCHAR2(20))
CACHE;
 
SELECT table_name, cache
FROM user_tables
WHERE table_name = 'HEAP_CACHE';
 
ALTER TABLE heap_cache NOCACHE;
 
SELECT table_name, cache
FROM user_tables
WHERE table_name = 'HEAP_CACHE';
CACHE READS      Follow the link, below, TO LOBs
 
DEFAULT      The VALUE inserted INTO the column IF the INSERT 
OR UPDATE would leave the column VALUE NULL.
 
<DEFAULT <value> | NULL>
CREATE TABLE default_test (
active     VARCHAR2(1) DEFAULT 'Y',
created_by VARCHAR2(30) DEFAULT USER,
created_on TIMESTAMP DEFAULT SYSTIMESTAMP);
 
SET LONG 100000
SET linesize 121
col data_default format a50
 
SELECT column_name, data_default
FROM user_tab_cols
WHERE table_name = 'DEFAULT_TEST';
 
ALTER TABLE default_test MODIFY (active DEFAULT 'N');
 
SELECT column_name, data_default
FROM user_tab_cols
WHERE table_name = 'DEFAULT_TEST';
 
ALTER TABLE default_test MODIFY (active DEFAULT NULL);
 
SELECT column_name, data_default
FROM user_tab_cols
WHERE table_name = 'DEFAULT_TEST';
 
INSERT INTO default_test
(active)
VALUES
('X');
 
SELECT * FROM default_test;
 
INSERT INTO default_test
(active, created_by)
VALUES
('Y', 'Morgan');
 
SELECT * FROM default_test;
 
UPDATE default_test
SET created_by = DEFAULT;
 
SELECT * FROM default_test;
Encryption Specification      
Valid algorithms are 3DES168, AES128, AES192, AND AES256
 
USING '<encryption_algorithm>' IDENTIFIED BY <password> [NO] [SALT]
See Transparent Data Encryption
 
Flashback Archive      
FLASHBACK [ARCHIVE <flashback_archive>]
OR
NO FLASHBACK ARCHIVE
See Flashback Archive Page
 
FREELISTS      
The NUMBER OF lists maintained ON a TABLE that can be 
used TO identify a block available FOR INSERT. SET this 
TO 1 ON ALL tables except those receiving very large 
numbers OF simultaneous inserts. WHEN a process requests 
a free list, it uses a 'hashing' FUNCTION TO SELECT which 
free list based ON the process id. Using a prime NUMBER 
WITH such mechanisms usually reduces the NUMBER OF collisions 
that occur IF the input IS randomly distributed. 
Therefore, IF you need more than one free list make the 
NUMBER OF lists a prime NUMBER (FOR example 1, 2, 3, 5, 7, 11, 
13, 17, 19, 23, .... FOR optimal performance).
 
Oracle ignores a setting OF FREELISTS IF the tablespace 
IN which the object resides IS IN automatic segment-SPACE 
management MODE.
 
FREELISTS <integer>
SELECT table_name, freelists
FROM user_tables;
 
FREELIST GROUPS      
The NUMBER OF groups OF free lists FOR the database object 
you are creating. The database uses the instance NUMBER 
OF Oracle REAL Application CLUSTER instances TO map 
each instance TO one free list GROUP.
 
This parameter IS ignored FOR objects created IN locally managed 
tablespaces WITH segment SPACE management specified AS AUTO.
 
FREELIST GROUPS <integer>
SELECT table_name, freelist_groups
FROM user_tables;
 
INITIAL      
IN a tablespace that IS specified AS EXTENT MANAGEMENT LOCAL. 
The database uses the VALUE OF INITIAL IN conjunction WITH 
the extent size FOR the tablespace TO determine the 
initial amount OF SPACE TO reserve FOR the object. FOR example, 
IN a uniform locally managed tablespace WITH 5M extents, IF 
you specify an INITIAL VALUE OF 1M, THEN the database must 
allocate one 5M extent, because that IS the uniform size OF 
extents FOR the tablespace. IF the extent size OF the tablespace 
IS smaller than the VALUE OF INITIAL, THEN the initial amount 
OF SPACE allocated will IN fact be more than one extent.
 
INITIAL <integer> <K | M | G>
SELECT tablespace_name, initial_extent
FROM user_tablespaces
 
SELECT table_name, tablespace_name, initial_extent
FROM user_tables;
 
INITRANS      
Specifies the NUMBER OF DML transaction entries FOR which 
SPACE IS initially reserved IN the data block header. 
SPACE IS reserved IN the headers OF ALL data blocks IN the 
associated segment.
 
Oracle uses control information stored IN the data block TO 
indicates which rows IN the block contain committed AND 
uncommitted changes. IN a sense, the block contains a 
recent history OF transactions that affected each ROW IN 
the block. The amount OF history that IS retained IS 
controlled BY the INITRANS parameter OF CREATE TABLE AND 
ALTER TABLE.
 
Under some circumstances, Oracle can have insufficient history 
information TO determine whether a ROW has been updated BY 
a "too recent" transaction. This can occur WHEN many 
transactions concurrently modify the same data block, OR DO 
so IN a very short period. You can avoid this situation BY 
setting higher VALUES OF INITRANS FOR tables that will 
experience many transactions updating the same blocks. 
Doing so enables Oracle TO allocate sufficient storage IN 
each block TO RECORD the history OF recent transactions 
that accessed the block.
 
INITRANS <integer>
SELECT table_name, ini_trans
FROM user_tables;
 
LOGGING      
Specify whether the creation OF the TABLE AND OF ANY 
indexes required because OF constraints, PARTITION, 
OR LOB storage characteristics will be logged IN the 
redo LOG file (LOGGING) OR NOT (NOLOGGING). 
The logging attribute OF the TABLE IS independent OF 
that OF its indexes.
 
This attribute also specifies whether subsequent direct 
loader (SQL*Loader) AND direct-PATH 
INSERT operations against the TABLE, PARTITION, 
OR LOB storage are logged (LOGGING) OR NOT logged (NOLOGGING).
CREATE TABLE logging_test (
testcol  VARCHAR2(20))
LOGGING;
 
SELECT table_name, logging
FROM user_tables;
 
ALTER TABLE test1 NOLOGGING;
 
SELECT table_name, logging
FROM user_tables;
 
MAXTRANS      
Once the SPACE reserved BY INITRANS IS depleted, 
SPACE FOR additional transaction entries IS allocated 
OUT OF the free SPACE IN a block, IF available. 
Once allocated, this SPACE effectively becomes a 
permanent part OF the block header. 
The MAXTRANS parameter limits the NUMBER OF transaction 
entries that can concurrently USE data IN a data block. 
Therefore, you can limit the amount OF free SPACE that 
can be allocated FOR transaction entries IN a data block 
using MAXTRANS.
 
MAXTRANS <integer>
SELECT table_name, max_trans
FROM user_tables;
 
MINEXTENTS      
The total NUMBER OF extents TO be allocated WHEN the 
segment IS created. This allows FOR a large allocation 
OF SPACE AT creation TIME, even IF contiguous SPACE 
IS NOT available.
 
IN a tablespace that IS specified AS 
EXTENT MANAGEMENT LOCAL, MINEXTENTS IS used only TO 
compute the initial amount OF SPACE that IS allocated. 
The initial amount OF SPACE that IS allocated AND IS equal 
TO INITIAL * MINEXTENTS. Thereafter it IS SET TO 1 FOR 
these tablespaces. 
 
MINEXTENTS <integer>
SELECT table_name, min_extents
FROM user_tables;