Language: PL/SQL Highlight Mode: PLSQL Last Modified: March 12th, 2009
CREATETABLE Options
BUFFER POOL
Defines a DEFAULT buffer pool (cache)FOR a schema object.<KEEP | RECYCLE | DEFAULT>CREATETABLE buffer_test (
testcol VARCHAR2(20))
STORAGE (buffer_pool DEFAULT);SELECT table_name, buffer_pool
FROM user_tables;ALTERTABLE buffer_test STORAGE (buffer_pool RECYCLE);SELECT table_name, buffer_pool
FROM user_tables;ALTERTABLE 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 CREATETABLE statement, NOCACHE IS the DEFAULT*IN an ALTERTABLE statement, the existing VALUEISNOT 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 ENDOF 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 ISNOT accessed frequently, this clause indicates
that the blocks retrieved FOR this TABLE are placed AT the
LEAST recently used ENDOF the LRU list IN the buffer cache
WHEN a full TABLE scan IS performed. NOCACHE IS the DEFAULTFOR
LOB storage.AS a parameter IN the LOB_storage_clause, NOCACHE specifies
that the LOB VALUE either ISNOT brought INTO the buffer cache
ORIS brought INTO the buffer cache AND placed AT the LEAST
recently used ENDOF the LRU list. The latter IS the DEFAULT
behavior.<CACHE | NOCACHE>CREATETABLE heap_cache (
testcol VARCHAR2(20))
CACHE;SELECT table_name, cache
FROM user_tables
WHERE table_name ='HEAP_CACHE';ALTERTABLE 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 INSERTORUPDATE would leave the column VALUENULL.<DEFAULT <value> | NULL>CREATETABLE default_test (
active VARCHAR2(1)DEFAULT'Y',
created_by VARCHAR2(30)DEFAULTUSER,
created_on TIMESTAMPDEFAULTSYSTIMESTAMP);SETLONG100000SET linesize 121
col data_default format a50
SELECT column_name, data_default
FROM user_tab_cols
WHERE table_name ='DEFAULT_TEST';ALTERTABLE default_test MODIFY (active DEFAULT'N');SELECT column_name, data_default
FROM user_tab_cols
WHERE table_name ='DEFAULT_TEST';ALTERTABLE default_test MODIFY (active DEFAULTNULL);SELECT column_name, data_default
FROM user_tab_cols
WHERE table_name ='DEFAULT_TEST';INSERTINTO default_test
(active)VALUES('X');SELECT*FROM default_test;INSERTINTO 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 NUMBEROF lists maintained ON a TABLE that can be
used TO identify a block available FORINSERT.SET this
TO1ONALL tables except those receiving very large
numbers OF simultaneous inserts.WHEN a process requests
a free list, it uses a 'hashing'FUNCTIONTOSELECT which
free list based ON the process id. Using a prime NUMBERWITH such mechanisms usually reduces the NUMBEROF collisions
that occur IF the input IS randomly distributed.
Therefore,IF you need more than one free list make the
NUMBEROF 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 ISIN automatic segment-SPACE
management MODE.
FREELISTS <integer>SELECT table_name, freelists
FROM user_tables;
FREELIST GROUPS
The NUMBEROF groups OF free lists FOR the database object
you are creating. The database uses the instance NUMBEROF 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 VALUEOF INITIAL IN conjunction WITH
the extent size FOR the tablespace TO determine the
initial amount OFSPACETO reserve FOR the object.FOR example,IN a uniform locally managed tablespace WITH 5M extents,IF
you specify an INITIAL VALUEOF 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 VALUEOF INITIAL,THEN the initial amount
OFSPACE 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 NUMBEROF DML transaction entries FOR which
SPACEIS initially reserved IN the data block header.SPACEIS reserved IN the headers OFALL 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 ROWIN
the block. The amount OF history that IS retained IS
controlled BY the INITRANS parameter OFCREATETABLEANDALTERTABLE.
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,ORDO
so IN a very short period. You can avoid this situation BY
setting higher VALUESOF INITRANS FOR tables that will
experience many transactions updating the same blocks.
Doing so enables Oracle TO allocate sufficient storage IN
each block TORECORD 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 TABLEANDOFANY
indexes required because OF constraints,PARTITION,OR LOB storage characteristics will be logged IN the
redo LOG file (LOGGING)ORNOT(NOLOGGING).
The logging attribute OF the TABLEIS independent OF
that OF its indexes.
This attribute also specifies whether subsequent direct
loader (SQL*Loader)AND direct-PATHINSERT operations against the TABLE,PARTITION,OR LOB storage are logged (LOGGING)ORNOT logged (NOLOGGING).CREATETABLE logging_test (
testcol VARCHAR2(20))
LOGGING;SELECT table_name, logging
FROM user_tables;ALTERTABLE test1 NOLOGGING;SELECT table_name, logging
FROM user_tables;
MAXTRANS
Once the SPACE reserved BY INITRANS IS depleted,SPACEFOR additional transaction entries IS allocated
OUTOF the free SPACEIN a block,IF available.
Once allocated, this SPACE effectively becomes a
permanent part OF the block header.
The MAXTRANS parameter limits the NUMBEROF 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 NUMBEROF extents TO be allocated WHEN the
segment IS created. This allows FOR a large allocation
OFSPACEAT creation TIME, even IF contiguous SPACEISNOT available.IN a tablespace that IS specified AS
EXTENT MANAGEMENT LOCAL, MINEXTENTS IS used only TO
compute the initial amount OFSPACE that IS allocated.
The initial amount OFSPACE that IS allocated ANDIS equal
TO INITIAL * MINEXTENTS. Thereafter it ISSETTO1FOR
these tablespaces.
MINEXTENTS <integer>SELECT table_name, min_extents
FROM user_tables;
SQL University.net courses meet the most demanding needs of the business world for advanced education
in a cost-effective manner. SQL University.net courses are available immediately for IT professionals
and can be taken without disruption of your workplace schedule or processes.
Compared to traditional travel-based training, SQL University.net saves time and valuable corporate
resources, allowing companies to do more with less. That's our mission, and that's what we deliver.