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

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

****************I am authoring a Book on Oracle Database Cloud Services.......More details to follow.***************

Title : Oracle Database Cloud Revealed
Publisher : Apress
Release Date : Jan-2019

**********************************************************************************


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]


My Cloud Certifications:

AWS Certified Solutions Architect Associate

Oracle Cloud Infrastructure 2018 Certified Architect Associate.

Oracle Cloud Infrastructure Classic 2018 Certified Architect Associate.

Oracle Database Cloud Administrator Certified Professional.

Oracle Database Cloud Service Operations Certified Associate.

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: