Experience: is what you get soon after you need it.

Experience: is what you get soon after you need it.

Rasul Allah (sal Allahu alaihi wa sallam) said: "Restore the trusts of those who trust you, and deal not falsely with him who deals falsely with you." [Abu Dawud, Tirmidhi]

Search This Blog

Friday, September 25, 2015

Oracle 11gR2 Create Table

Oracle create table syntax:


CREATE [ GLOBAL TEMPORARY ] TABLE
   [ schema.]table OF
   [ schema.]object_type
   [ ( relational_properties ) ]
   [ ON COMMIT { DELETE | PRESERVE } ROWS ]
   [ OID_clause ]
   [ OID_index_clause ]
   [ physical_properties ]
   [ table_properties ] ;
 
<relational_properties> ::= 
{ column_definition
| { out_of_line_constraint
  | out_of_line_ref_constraint
  | supplemental_logging_props
  }
}
  [, { column_definition
     | { out_of_line_constraint
       | out_of_line_ref_constraint
       | supplemental_logging_props
       }
  ]...
 
<column_definition> ::= 
column data_type [ SORT ]
      [ DEFAULT expr ]
      [ ENCRYPT encryption_spec ]
      [ ( inline_constraint [ inline_constraint ] ... )
      | inline_ref_constraint 
      ]
 
<data_type> ::=
{ Oracle_built_in_datatypes
| ANSI_supported_datatypes
| user_defined_types
| Oracle_supplied_types
}
 
<Oracle_built_in_datatypes> ::=
{ character_datatypes
| number_datatypes
| long_and_raw_datatypes
| datetime_datatypes
| large_object_datatypes
| rowid_datatypes
}
 
<large_object_datatypes> ::= 
{ BLOB | CLOB | NCLOB| BFILE }
 
 <table_properties> ::=
  [ column_properties ]
  [ table_partitioning_clauses ]
  [ CACHE | NOCACHE ]
  [ parallel_clause ]
  [ ROWDEPENDENCIES | NOROWDEPENDENCIES ]
  [ enable_disable_clause ]
  [ enable_disable_clause ]...
  [ row_movement_clause ]
  [ AS subquery ]
 
<column_properties> ::=
  { object_type_col_properties
  | nested_table_col_properties
  | { varray_col_properties | LOB_storage_clause }
    [ (LOB_partition_storage
        [, LOB_partition_storage ]...
      )
    ]
  | XMLType_column_properties
  }
  [ { object_type_col_properties
    | nested_table_col_properties
    | { varray_col_properties | LOB_storage_clause }
      [ ( LOB_partition_storage
          [, LOB_partition_storage ]...
        )
      ]
    | XMLType_column_properties
    }
  ]...
 
<LOB_partition_storage> ::=
  PARTITION partition
  { LOB_storage_clause | varray_col_properties }
    [ LOB_storage_clause | varray_col_properties ]...
  [ ( SUBPARTITION subpartition
     { LOB_storage_clause | varray_col_properties }
       [ LOB_storage_clause
       | varray_col_properties
       ]...
    )
  ]
 
<LOB_storage_clause> ::=
  LOB
  { (LOB_item [, LOB_item ]...)
      STORE AS [ SECUREFILE | BASICFILE ] (LOB_storage_parameters)
  | (LOB_item)
      STORE AS [ SECUREFILE | BASICFILE ]
        { LOB_segname (LOB_storage_parameters)
        | LOB_segname
        | (LOB_storage_parameters)
        }
  }
 
<LOB_storage_parameters> ::=
  { TABLESPACE tablespace
  | { LOB_parameters [ storage_clause ]
    }
  | storage_clause
  }
    [ TABLESPACE tablespace
    | { LOB_parameters [ storage_clause ]
      }
    ]...
 
<LOB_parameters> ::=
  [ { ENABLE | DISABLE } STORAGE IN ROW
  | CHUNK integer
  | PCTVERSION integer
  | RETENTION [ { MAX | MIN integer | AUTO | NONE } ]
  | FREEPOOLS integer
  | LOB_deduplicate_clause
  | LOB_compression_clause
  | LOB_encryption_clause
  | { CACHE | { { NOCACHE | CACHE READS } [ logging_clause ] } }
  ]
 
<logging_clause> ::=
  { LOGGING | NOLOGGING | FILESYSTEM_LIKE_LOGGING }
 
<storage_clause> ::=
  STORAGE
  ({ INITIAL integer [ K | M ]
   | NEXT integer [ K | M ]
   | MINEXTENTS integer
   | MAXEXTENTS { integer | UNLIMITED }
   | PCTINCREASE integer
   | FREELISTS integer
   | FREELIST GROUPS integer
   | OPTIMAL [ integer [ K | M ]
             | NULL
             ]
   | BUFFER_POOL { KEEP | RECYCLE | DEFAULT }
   }
     [ INITIAL integer [ K | M ]
     | NEXT integer [ K | M ]
     | MINEXTENTS integer
     | MAXEXTENTS { integer | UNLIMITED }
     | MAXSIZE { { integer { K | M | G | T | P } } | UNLIMITED }
     | PCTINCREASE integer
     | FREELISTS integer
     | FREELIST GROUPS integer
     | OPTIMAL [ integer [ K | M ]
               | NULL
               ]
     | BUFFER_POOL { KEEP | RECYCLE | DEFAULT }
     ]...
  )
 
<LOB_deduplicate_clause> ::=
  { DEDUPLICATE 
  | KEEP_DUPLICATES
  }
 
<LOB_compression_clause> ::=
  { COMPRESS [ HIGH | MEDIUM | LOW ]
  | NOCOMPRESS }
 
<LOB_encryption_clause> ::=
  { ENCRYPT [ USING 'encrypt_algorithm' ] 
    [ IDENTIFIED BY password ]
  | DECRYPT 
  }
 
<XMLType_column_properties> ::= 
XMLTYPE [ COLUMN ] column
   [ XMLType_storage ]
   [ XMLSchema_spec ]
 
<XMLType_storage> ::=
STORE AS
   { OBJECT RELATIONAL
   | [ SECUREFILE | BASICFILE ] { CLOB | BINARY XML }
       [ { LOB_segname [ (LOB_parameters) ]
         | LOB_parameters
         }
         ]
 
<varray_col_properties> ::=
VARRAY varray_item 
   { [ substitutable_column_clause ]
     STORE AS [ SECUREFILE | BASICFILE ] LOB
        { [ LOB_segname ] (LOB_parameters)
        | LOB_segname 
        }
   | substitutable_column_clause
   }
 
 
ALTER TABLE [ schema.]table
  [ alter_table_properties
  | column_clauses
  | constraint_clauses
  | alter_table_partitioning
  | alter_external_table_clauses
  | move_table_clause
  ]
    [ enable_disable_clause
    | { ENABLE | DISABLE }
      { TABLE LOCK | ALL TRIGGERS }
    [ enable_disable_clause
    | { ENABLE | DISABLE }
      { TABLE LOCK | ALL TRIGGERS }
    ]...
  ] ;
 
 
<column_clauses> ::=
  { { add_column_clause
    | modify_column_clause
    | drop_column_clause
    }
    [ add_column_clause
    | modify_column_clause
    | drop_column_clause
    ]...
  | rename_column_clause
  | modify_collection_retrieval
    [ modify_collection_retrieval ]...
  | modify_LOB_storage_clause
    [ modify_LOB_storage_clause ] ...
  | alter_varray_col_properties
    [ alter_varray_col_properties ]
  }
 
<modify_LOB_storage_clause> ::=
MODIFY LOB (LOB_item) ( modify_LOB_parameters )
 
<modify_LOB_parameters> ::=
{ storage_clause
| PCTVERSION integer
| FREEPOOLS integer
| REBUILD FREEPOOLS
| LOB_retention_clause
| LOB_deduplicate_clause
| LOB_compression_clause
| { ENCRYPT encryption_spec | DECRYPT }
| { CACHE 
  | { NOCACHE | CACHE READS } [ logging_clause ]
  }
| allocate_extent_clause
| shrink_clause
| deallocate_unused_clause
} ... 
 
 

No comments: