SnowConvert AI - Oracle - Create Table¶

このセクションでは、 TABLES と、その構文および現在の変換に関する情報を確認できます。

説明¶

Oracleでは、CREATE TABLE ステートメントを使用して、ユーザーデータを保持する基本構造であるリレーショナルテーブル、または列定義にオブジェクト型を使用するテーブルであるオブジェクトテーブルのいずれかのタイプのテーブルを作成します。(Oracleドキュメント)

Oracle構文

CREATE [ { GLOBAL | PRIVATE } TEMPORARY | SHARDED | DUPLICATED | [ IMMUTABLE ] BLOCKCHAIN 
  | IMMUTABLE  ] 
   TABLE
  [ schema. ] table
  [ SHARING = { METADATA | DATA | EXTENDED DATA | NONE } ]
  { relational_table | object_table | XMLType_table }
  [ MEMOPTIMIZE FOR READ ]
  [ MEMOPTIMIZE FOR WRITE ]
  [ PARENT [ schema. ] table ] ;

Copy

Snowflake構文

CREATE [ OR REPLACE ]
    [ { [ { LOCAL | GLOBAL } ] TEMP | TEMPORARY | VOLATILE | TRANSIENT } ]
  TABLE [ IF NOT EXISTS ] <table_name> (
    -- Column definition
    <col_name> <col_type>
      [ inlineConstraint ]
      [ NOT NULL ]
      [ COLLATE '<collation_specification>' ]
      [
        {
          DEFAULT <expr>
          | { AUTOINCREMENT | IDENTITY }
            [
              {
                ( <start_num> , <step_num> )
                | START <num> INCREMENT <num>
              }
            ]
            [ { ORDER | NOORDER } ]
        }
      ]
      [ [ WITH ] MASKING POLICY <policy_name> [ USING ( <col_name> , <cond_col1> , ... ) ] ]
      [ [ WITH ] TAG ( <tag_name> = '<tag_value>' [ , <tag_name> = '<tag_value>' , ... ] ) ]
      [ COMMENT '<string_literal>' ]

    -- Additional column definitions
    [ , <col_name> <col_type> [ ... ] ]

    -- Out-of-line constraints
    [ , outoflineConstraint [ ... ] ]
  )
  [ CLUSTER BY ( <expr> [ , <expr> , ... ] ) ]
  [ ENABLE_SCHEMA_EVOLUTION = { TRUE | FALSE } ]
  [ STAGE_FILE_FORMAT = (
     { FORMAT_NAME = '<file_format_name>'
       | TYPE = { CSV | JSON | AVRO | ORC | PARQUET | XML } [ formatTypeOptions ]
     } ) ]
  [ STAGE_COPY_OPTIONS = ( copyOptions ) ]
  [ DATA_RETENTION_TIME_IN_DAYS = <integer> ]
  [ MAX_DATA_EXTENSION_TIME_IN_DAYS = <integer> ]
  [ CHANGE_TRACKING = { TRUE | FALSE } ]
  [ DEFAULT_DDL_COLLATION = '<collation_specification>' ]
  [ COPY GRANTS ]
  [ COMMENT = '<string_literal>' ]
  [ [ WITH ] ROW ACCESS POLICY <policy_name> ON ( <col_name> [ , <col_name> ... ] ) ]
  [ [ WITH ] TAG ( <tag_name> = '<tag_value>' [ , <tag_name> = '<tag_value>' , ... ] ) ]

Copy

注釈

Snowflakeの詳細情報については、以下のドキュメントを参照してください。

サンプルソースパターン¶

2.1.物理的プロパティとテーブルプロパティ¶

Oracle¶

CREATE TABLE "MySchema"."BaseTable"
(
    BaseId NUMBER DEFAULT 10 NOT NULL ENABLE
) SEGMENT CREATION IMMEDIATE
  PCTFREE 0 PCTUSED 40 INITRANS 1 MAXTRANS 255
  COLUMN STORE COMPRESS FOR QUERY HIGH NO ROW LEVEL LOCKING LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "MyTableSpace"
  PARTITION BY LIST ("BaseId")
 (
    PARTITION "P20211231"  VALUES (20211231) SEGMENT CREATION DEFERRED
    PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
    ROW STORE COMPRESS ADVANCED LOGGING
    STORAGE(
    BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
    TABLESPACE "MyTableSpace" 
  )
  PARALLEL;

Copy

Snowflake¶

CREATE OR REPLACE TABLE "MySchema"."BaseTable"
 (
     BaseId NUMBER(38, 18) /*** SSC-FDM-0006 - NUMBER TYPE COLUMN MAY NOT BEHAVE SIMILARLY IN SNOWFLAKE. ***/ DEFAULT 10 NOT NULL
 )
 COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
 ;

Copy

注釈

テーブルプロパティは、Snowflakeでの移行後には必要ないため、削除されています。

2.2.制約と制約状態¶

以下の制約はコメントアウトされます。

CHECK 制約

注釈

USING INDEX 制約は、変換中に出力コードから完全に削除されます。

Oracle¶

CREATE TABLE "MySchema"."BaseTable"
(
    BaseId NUMBER DEFAULT 10 NOT NULL ENABLE NOVALIDATE,
    "COL1" NUMBER CHECK( "COL1" IS NOT NULL ),
	  CHECK( "COL1" IS NOT NULL ),
    CONSTRAINT "Constraint1BaseTable" PRIMARY KEY (BaseId)
        USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
        STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
        PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1) ENABLE
);

Copy

Snowflake¶

CREATE OR REPLACE TABLE "MySchema"."BaseTable"
	(
	    BaseId NUMBER(38, 18) /*** SSC-FDM-0006 - NUMBER TYPE COLUMN MAY NOT BEHAVE SIMILARLY IN SNOWFLAKE. ***/ DEFAULT 10 NOT NULL,
	    "COL1" NUMBER(38, 18) /*** SSC-FDM-0006 - NUMBER TYPE COLUMN MAY NOT BEHAVE SIMILARLY IN SNOWFLAKE. ***/ NOT NULL
 	                                                                                                                     !!!RESOLVE EWI!!! /*** SSC-EWI-0035 - CHECK STATEMENT NOT SUPPORTED ***/!!!
 	                                                                                                                     CHECK( "COL1" IS NOT NULL ),
	!!!RESOLVE EWI!!! /*** SSC-EWI-0035 - CHECK STATEMENT NOT SUPPORTED ***/!!!
		  CHECK( "COL1" IS NOT NULL ),
	    CONSTRAINT "Constraint1BaseTable" PRIMARY KEY (BaseId)
	)
	COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"oracle"}}'
	;

Copy

一方、同じように、NOT NULL 制約の後に任意の制約状態がある場合は次のようになります。

RELY
NO RELY
RELY ENABLE
RELY DISABLE
VALIDATE
NOVALIDATE

これらもコメントアウトされます。

注釈

ENABLE の制約状態は、変換処理中に出力コードから完全に削除されます。DISABLE 状態の場合、NOT NULL 制約と同時に削除されます。

Oracle¶

CREATE TABLE Table1(
  col1 INT NOT NULL ENABLE,
  col2 INT NOT NULL DISABLE,
  col3 INT NOT NULL RELY
);

Copy

Snowflake¶

CREATE OR REPLACE TABLE Table1 (
    col1 INT NOT NULL,
    col2 INT ,
    col3 INT NOT NULL /*** SSC-FDM-OR0006 - CONSTRAINT STATE RELY REMOVED FROM NOT NULL INLINE CONSTRAINT ***/
  )
  COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
  ;

Copy

2.3.外部キー¶

精度もスケールもない NUMBER 列を持つテーブルと、前述の NUMBER 列を参照する NUMBER(*,0)列を持つ別のテーブルがある場合、この外部キーをコメントアウトします。

Oracle¶

CREATE TABLE "MySchema"."MyTable"
(
    "COL1" NUMBER, 
    CONSTRAINT "PK" PRIMARY KEY ("COL1")
);

Copy

Snowflake¶

CREATE OR REPLACE TABLE "MySchema"."MyTable"
    (
        "COL1" NUMBER(38, 18) /*** SSC-FDM-0006 - NUMBER TYPE COLUMN MAY NOT BEHAVE SIMILARLY IN SNOWFLAKE. ***/,
        CONSTRAINT "PK" PRIMARY KEY ("COL1")
    )
    COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
    ;

Copy

2.4.仮想列¶

Oracle¶

CREATE TABLE "MySchema"."MyTable"
(
    "COL1" NUMBER GENERATED ALWAYS AS (COL1 * COL2) VIRTUAL
);

Copy

Snowflake¶

CREATE OR REPLACE TABLE "MySchema"."MyTable"
    (
        "COL1" NUMBER(38, 18) /*** SSC-FDM-0006 - NUMBER TYPE COLUMN MAY NOT BEHAVE SIMILARLY IN SNOWFLAKE. ***/ AS (COL1 * COL2)
    )
    COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
    ;

Copy

2.5.アイデンティティ列¶

アイデンティティ列の場合、シーケンスが作成され、列に割り当てられます。

Oracle¶

CREATE TABLE "MySchema"."BaseTable"
(
	"COL0" NUMBER GENERATED BY DEFAULT ON NULL 
		AS IDENTITY MINVALUE 1 MAXVALUE 9999999999999999999999999999 
		INCREMENT BY 1 
		START WITH 621 
		CACHE 20 
		NOORDER  NOCYCLE  NOT NULL ENABLE
);

Copy

Snowflake¶

CREATE OR REPLACE TABLE "MySchema"."BaseTable"
	(
		"COL0" NUMBER(38, 18) /*** SSC-FDM-0006 - NUMBER TYPE COLUMN MAY NOT BEHAVE SIMILARLY IN SNOWFLAKE. ***/ IDENTITY(621, 1) ORDER NOT NULL
	)
	COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "oracle",  "convertedOn": "07/16/2025",  "domain": "no-domain-provided" }}'
	;

Copy

2.6.CLOB および BLOB 列宣言¶

CLOB または BLOB として宣言された列は、VARCHAR に変更されます。

Oracle¶

CREATE TABLE T
(
 Col1 BLOB DEFAULT EMPTY_BLOB(),
Col5 CLOB DEFAULT EMPTY_CLOB()
);

Copy

Snowflake¶

CREATE OR REPLACE TABLE T
 (
  Col1 BINARY,
 Col5 VARCHAR
 )
 COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
 ;

Copy

2.7.制約名¶

警告

制約名はSnowflakeでは適用できないため、コードから削除されています。

Oracle¶

CREATE TABLE "CustomSchema"."BaseTable"(
 "PROPERTY" VARCHAR2(64) CONSTRAINT "MICROSOFT_NN_PROPERTY" NOT NULL ENABLE
  );

Copy

Snowflake¶

CREATE OR REPLACE TABLE "CustomSchema"."BaseTable" (
  "PROPERTY" VARCHAR(64) NOT NULL /*** SSC-FDM-0012 - CONSTRAINT NAME '"MICROSOFT_NN_PROPERTY"' IN NULL OR NOT NULL CONSTRAINT IS NOT SUPPORTED IN SNOWFLAKE ***/
   )
   COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
   ;

Copy

2.8.時刻を含むデフォルト列¶

日付型として宣言された列は、特定の日付型と一致するようにキャストされます。

Oracle¶

CREATE TABLE TABLE1
(
"COL1" VARCHAR(50) DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE TABLE1
(
 COL0 TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP,
 COL1 TIMESTAMP(6) DEFAULT CURRENT_TIME,
 COL2 TIMESTAMP(6) WITH LOCAL TIME ZONE DEFAULT '1900-01-01 12:00:00',
 COL3 TIMESTAMP(6) WITH TIME ZONE DEFAULT '1900-01-01 12:00:00',
 COL4 TIMESTAMP(6) WITHOUT TIME ZONE DEFAULT '1900-01-01 12:00:00',
 COL5 TIMESTAMP(6) DEFAULT TO_TIMESTAMP('01/01/1900 12:00:00.000000 AM', 'MM/DD/YYYY HH:MI:SS.FF6 AM')
 );

Copy

Snowflake¶

CREATE OR REPLACE TABLE TABLE1
 (
 "COL1" VARCHAR(50) DEFAULT TO_VARCHAR(CURRENT_TIMESTAMP(), 'YYYY-MM-DD HH:MI:SS')
 )
 COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
 ;

 --** SSC-FDM-0019 - SEMANTIC INFORMATION COULD NOT BE LOADED FOR TABLE1. CHECK IF THE NAME IS INVALID OR DUPLICATED. **
 CREATE OR REPLACE TABLE TABLE1
 (
  COL0 TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP() :: TIMESTAMP(6),
  COL1 TIMESTAMP(6) DEFAULT CURRENT_TIME() :: TIMESTAMP(6),
  COL2 TIMESTAMP_LTZ(6) DEFAULT '1900-01-01 12:00:00' :: TIMESTAMP_LTZ(6),
  COL3 TIMESTAMP_TZ(6) DEFAULT '1900-01-01 12:00:00' :: TIMESTAMP_TZ(6),
  COL4 TIMESTAMP(6) WITHOUT TIME ZONE DEFAULT '1900-01-01 12:00:00' :: TIMESTAMP(6) WITHOUT TIME ZONE,
  COL5 TIMESTAMP(6) DEFAULT TO_TIMESTAMP('01/01/1900 12:00:00.000000 AM', 'MM/DD/YYYY HH:MI:SS.FF6 AM') :: TIMESTAMP(6)
  )
 COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
 ;

Copy

2.10 AS SubQuery¶

Snowflakeで AS SubQuery を通じてテーブルを作成する場合、以下のプロパティと句はサポートされません。

[ immutable_table_clauses ]
[ blockchain_table_clauses ]
[ DEFAULT COLLATION collation_name ]
[ ON COMMIT { DROP | PRESERVE } DEFINITION ]
[ ON COMMIT { DELETE | PRESERVE } ROWS ]
[ physical_properties ]

Copy

Oracle¶

create table table1
-- NO DROP NO DELETE HASHING USING sha2_512 VERSION v1 -- blockchain_clause not yet supported
DEFAULT COLLATION somename
ON COMMIT DROP DEFINITION
ON COMMIT DELETE ROWS
COMPRESS
NOLOGGING
AS
   select
      *
   from
      table1;

Copy

Snowflake¶

CREATE OR REPLACE TABLE table1
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"oracle"}}'
-- NO DROP NO DELETE HASHING USING sha2_512 VERSION v1 -- blockchain_clause not yet supported
AS
   select
      *
   from
      table1;

Copy

既知の問題¶

Snowflakeでの動作が異なるため、テーブルのプロパティの一部を適応したり、コメントしたりする場合があります。

SnowConvert AI - Oracle - Create Table¶

説明¶

サンプルソースパターン¶

2.1.物理的プロパティとテーブルプロパティ¶

Oracle¶

Snowflake¶

2.2.制約と制約状態¶

Oracle¶

Snowflake¶

Oracle¶

Snowflake¶

2.3.外部キー¶

Oracle¶

Snowflake¶

2.4.仮想列¶

Oracle¶

Snowflake¶

2.5.アイデンティティ列¶

Oracle¶

Snowflake¶

2.6.CLOB および BLOB 列宣言¶

Oracle¶

Snowflake¶

2.7.制約名¶

Oracle¶

Snowflake¶

2.8.時刻を含むデフォルト列¶

Oracle¶

Snowflake¶

2.9 共有とメモ最適化オプション¶

Oracle¶

Snowflake¶

2.10 AS SubQuery¶

Oracle¶

Snowflake¶

既知の問題¶

関連 EWIs¶