Snippet Name: TABLE: Options
Description: List of table modifiers and options.
Also see: » TABLESPACE: Show contiguous space
» TABLESPACE: SYSAUX tablespace
» TABLESPACE: Tablespace management
» TABLESPACE: List tablespaces, files, ...
» TABLESPACE: Dropping Tablespaces
» TABLESPACE: Alter Permanent Tablespace
» TABLESPACE: Transportable tablespaces
» TABLESPACE: Tempfile operations
» TABLESPACE: Create temp tablespace
» TABLESPACE: Change UNDO tablespace
» TABLESPACE: Undo Tablespace
» TABLESPACE: SYSAUX Tablespace
» TABLESPACE: Set default tablespace type
» TABLESPACE: Oracle Managed Auto-extend...
» TABLESPACE: Permanent Tablespace Using...
» TABLE: Constraints
» TABLE: Using Select Statement With Data
» TABLE: Multiple Columns
» TABLE: queries
» TABLE: simple heap table
» TABLESPACE: Using Raw Devices
» TABLESPACE: permanent tablespace
Comment: (none)
Language: PL/SQL
Highlight Mode: PLSQL
Last Modified: March 12th, 2009
|
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;
|