ALTER TABLE

説明

このステートメントは、既存のテーブルの構造を変更するために使用されます。これには、テーブルとテーブル属性の変更、列の追加、列と属性の変更、列の削除などが含まれます。

構文

ALTER TABLE table_name alter_table_actions;
| ALTER TABLE EXTERNAL table_name alter_table_actions;
| ALTER TABLE table_name alter_column_group_option;
| ALTER TABLE EXTERNAL table_name ADD PARTITION '(' add_external_table_partition_actions ')' LOCATION STRING_VALUE;
| ALTER TABLE EXTERNAL table_name DROP PARTITION LOCATION STRING_VALUE;

alter_table_actions:
    alter_table_action
    | alter_table_actions ',' alter_table_action
    | exclude_alter_table_action

exclude_alter_table_action:
    alter_partition_option
    | modify_partition_info
    | auto_split_range_partition_option

alter_table_action:
    table_option_list_space_seperated
    | SET table_option_list_space_seperated
    | opt_alter_compress_option
    | alter_column_option
    | alter_tablegroup_option
    | RENAME relation_factor
    | RENAME TO relation_factor
    | alter_index_option
    | DROP CONSTRAINT constraint_name
    | enable_option ALL TRIGGERS
    | REFRESH
    | enable_macro_block_bloom_filter
    | DYNAMIC_PARTITION_POLICY [=] (dynamic_partition_policy_list)
    | SET INTERVAL(expr)

dynamic_partition_policy_list:
    dynamic_partition_policy_option [, dynamic_partition_policy_option ...]

dynamic_partition_policy_option:
    ENABLE = {true | false}
    | PRECREATE_TIME = {'-1' | '0' | 'n {hour | day | week | month | year}'}
    | EXPIRE_TIME = {'-1' | '0' | 'n {hour | day | week | month | year}'}

alter_partition_option:
    DROP PARTITION drop_partition_name_list
    | DROP PARTITION drop_partition_name_list UPDATE GLOBAL INDEXES
    | RENAME PARTITION relation_name TO relation_name
    | add_range_or_list_partition
    | SPLIT PARTITION relation_factor split_actions
    | TRUNCATE PARTITION name_list
    | TRUNCATE PARTITION name_list UPDATE GLOBAL INDEXES
    | MODIFY PARTITION relation_factor add_range_or_list_subpartition
    | RENAME SUBPARTITION relation_name TO relation_name
    | DROP SUBPARTITION drop_partition_name_list
    | DROP SUBPARTITION drop_partition_name_list UPDATE GLOBAL INDEXES
    | TRUNCATE SUBPARTITION name_list
    | TRUNCATE SUBPARTITION name_list UPDATE GLOBAL INDEXES
    | EXCHANGE {PARTITION partition_name
               | SUBPARTITION subpartition_name} WITH TABLE origin_table_name INCLUDING INDEXES WITHOUT VALIDATION

alter_column_group_option:
    ADD COLUMN GROUP '(' column_group_list ')' alter_column_group_delayed_desc
    | DROP COLUMN GROUP '(' column_group_list ')' alter_column_group_delayed_desc

modify_partition_info:
    MODIFY hash_partition_option
    | MODIFY list_partition_option
    | MODIFY range_partition_option

auto_split_range_partition_option:
    PARTITION BY RANGE '(' ')'  opt_auto_split_tablet_size_option
    | PARTITION BY RANGE '(' column_name_list ')' subpartition_option opt_auto_split_range_partition_info
    | MODIFY PARTITION BY RANGE '(' ')'  opt_auto_split_tablet_size_option
    | MODIFY PARTITION BY RANGE '(' column_name_list ')' subpartition_option auto_split_tablet_size_option
    | MODIFY PARTITION BY RANGE '(' column_name_list ')' subpartition_option auto_split_tablet_size_option opt_range_partition_list

add_external_table_partition_actions:
    add_external_table_partition_action
    | add_external_table_partition_actions ',' add_external_table_partition_action
    | /* empty */

opt_auto_split_tablet_size_option:
    | AUTO_SPLIT_TABLET_SIZE '(' integer_value ')'
    | /* empty */

構文説明

split_partition_option

• SPLIT PARTITION partition_name AT (value) [INTO (PARTITION split_partition_name1, PARTITION [split_partition_name2])]：この構文を使用してパーティションを分割する場合、指定された value を境界として、ソースパーティションを2つのパーティションに分割します。INTO 句を使用して、分割されたパーティションに名前を設定することもできます。

• SPLIT PARTITION partition_name INTO (PARTITION split_partition_name VALUES LESS THAN (value) [, PARTITION split_partition_name VALUES LESS THAN (value) ...], PARTITION split_partition_name)：この構文を使用してパーティションを分割する場合、1つのパーティションを複数のパーティションに分割できます。パーティション分割で定義される value の範囲は、ソースパーティションの value の範囲と同じである必要があり、value は昇順で定義する必要があります（最後のパーティション分割の定義は記入できず、その value はソースパーティションと同じになります）。

手動でパーティションを分割する方法の詳細については、手動でのパーティション分割を参照してください。

dynamic_partition_policy_option

• ENABLE = {true | false}：動的パーティション管理を有効にするかどうかを示します。取り得る値は以下のとおりです：

  • true：デフォルト値で、動的パーティション管理を有効にします。
  • false：動的パーティション管理を無効にします。

• PRECREATE_TIME = {'-1' | '0' | 'n {hour | day | week | month | year}'}：事前作成時間を示します。動的パーティション管理を1回スケジュールするごとに、パーティションを事前作成し、最大パーティションの上限 > now() + precreate_time となるようにします。取り得る値は以下のとおりです：

  • -1：デフォルト値で、パーティションを事前作成しません。
  • 0：現在のパーティションのみを事前作成します。
  • n {hour | day | week | month | year}：対応する時間範囲のパーティションを事前作成します。例えば、3 hour は3時間以内のパーティションを事前作成することを意味します。

  説明

  • 複数のパーティションを事前作成する必要がある場合、パーティション境界の間隔は TIME_UNIT です。
  • 最初に事前作成されるパーティションの境界は、既存の最大パーティション境界から TIME_UNIT で切り上げた値となります。

• EXPIRE_TIME = {'-1' | '0' | 'n {hour | day | week | month | year}'}：オプションで、パーティションの有効期限を示します。動的パーティション管理を1回スケジュールするごとに、パーティションの上限 < now() - expire_time のすべての期限切れパーティションを削除します。変更可能で、取り得る値は以下のとおりです：

  • -1：デフォルト値で、パーティションは永遠に期限切れになりません。
  • 0：現在のパーティションを除く、それ以前のすべてのパーティションが期限切れになります。
  • n {hour | day | week | month | year}：パーティションの有効期限を示します。例えば、1 day はパーティションの有効期限が1日であることを意味します。

動的パーティションテーブルの変更に関する詳細は、動的パーティションテーブルの変更を参照してください。

例：

ALTER TABLE tbl2 SET DYNAMIC_PARTITION_POLICY(
    ENABLE = true,
    PRECREATE_TIME = '1 day',
    EXPIRE_TIME = '-1'
);

例

• テーブル tbl1 の列 col1 のデータ型を変更します。

  obclient> CREATE TABLE tbl1(col1 VARCHAR(3));
  
  obclient> ALTER TABLE tbl1 MODIFY col1 CHAR(10);
  
  obclient> DESCRIBE tbl1;
  

• テーブル tbl1 の列 col1 の名前を col2 に変更します。

  obclient> ALTER TABLE tbl1 RENAME COLUMN col1 TO col2;
  
  obclient> DESCRIBE tbl1;
  

• 列の追加と削除。

  • テーブル tbl2 を作成します。

    obclient> CREATE TABLE tbl2 (col1 NUMBER(30) PRIMARY KEY,col2 VARCHAR(50));
    

  • テーブル tbl2 に列 col3 を追加します。

    obclient> ALTER TABLE tbl2 ADD col3 NUMBER(30);
    
    obclient> DESCRIBE tbl2;
    

  • テーブル tbl2 の列 col3 を削除します。

    obclient> ALTER TABLE tbl2 DROP COLUMN col3;
    
    obclient> DESCRIBE tbl2;
    

  • テーブル tbl2 に一意インデックスを作成します。

    obclient> CREATE TABLE tbl2 (col1 NUMBER(30) PRIMARY KEY,col2 VARCHAR(50), col3 INT);
    
    obclient> ALTER TABLE tbl2 ADD CONSTRAINT constraint_TBL2 UNIQUE (col2, col3);
    
    obclient [SYS]> DESC tbl2;
    
    obclient> INSERT INTO tbl2 VALUES('1','2','2');
    
    obclient> INSERT INTO tbl2 VALUES('2','2','2');
    
    obclient> INSERT INTO tbl2 VALUES('2','3','2');
    

• テーブル ref_t2 に外部キーを追加し、DELETE 操作が子テーブルと一致する行の親テーブルのキー値に影響を与えた場合に SET NULL 操作を実行します。

  obclient> CREATE TABLE ref_t1(c1 INT PRIMARY KEY,C2 INT);
  
  obclient> CREATE TABLE ref_t2(c1 INT PRIMARY KEY,C2 INT);
  
  obclient> ALTER TABLE ref_t2 ADD CONSTRAINT fk1 FOREIGN KEY (c2) REFERENCES ref_t1(c1) ON DELETE SET NULL;
  

• 非テンプレートのサブパーティションテーブル tbl3 のパーティション p1 にサブパーティション p1_r4 を追加します。

  obclient> ALTER TABLE tbl3 MODIFY PARTITION p1 ADD SUBPARTITION p1_r4 VALUES LESS THAN(2022);
  

• 非テンプレートのサブパーティションテーブル tbl3 のサブパーティション p3_r3 を削除します。

  obclient> ALTER TABLE tbl3 DROP SUBPARTITION p2_r3;
  

• 非テンプレートのサブパーティションテーブル tbl3 にパーティション p4 を追加します。パーティションの定義とその下のサブパーティションの定義を同時に指定する必要があります。

  obclient> ALTER TABLE tbl3 ADD PARTITION p4 VALUES LESS THAN (400)
  (
    SUBPARTITION p4_r1 VALUES LESS THAN (2019),
    SUBPARTITION p4_r2 VALUES LESS THAN (2020),
    SUBPARTITION p4_r3 VALUES LESS THAN (2021)
  );
  

• テンプレートのサブパーティションテーブル tbl4 にパーティション p3 を追加します。パーティションの定義のみを指定する必要があり、サブパーティションの定義はテンプレートに従って自動的に埋め込まれます。

  obclient> CREATE TABLE tbl4(col1 INT, col2 INT, PRIMARY KEY(col1,col2))
       PARTITION BY RANGE(col1)
       SUBPARTITION BY RANGE(col2)
       SUBPARTITION TEMPLATE
       (
       SUBPARTITION p0 VALUES LESS THAN (50),
       SUBPARTITION p1 VALUES LESS THAN (100)
       )
       (
       PARTITION p0 VALUES LESS THAN (100),
       PARTITION p1 VALUES LESS THAN (200),
       PARTITION p2 VALUES LESS THAN (300)
       );
  
  obclient> ALTER TABLE tbl4 ADD PARTITION p3 VALUES LESS THAN (400);
  

• テーブル tbl5 の並列度を 3 に変更します。

  obclient> CREATE TABLE tbl5(col1 int primary key, col2 int) PARALLEL 5;
  
  obclient> ALTER TABLE tbl5 PARALLEL 3;
  

  または：

  obclient> CREATE TABLE tbl5(col1 int primary key, col2 int) PARALLEL 5;
  
  obclient> ALTER /*+ parallel(3) */ TABLE tbl5;
  

• 外部キー制約の状態を変更します。

  obclient> CREATE TABLE MMS_GROUPUSER (
    "ID" VARCHAR2(254 BYTE) NOT NULL,
    "GROUPID" VARCHAR2(254 BYTE),
    "USERID" VARCHAR2(254 BYTE),
    CONSTRAINT "PK_MMS_GROUPUSER" PRIMARY KEY ("ID"),
    CONSTRAINT "FK_MMS_GROUPUSER_02" FOREIGN KEY ("GROUPID") REFERENCES MMS_GROUPUSER ("ID") ON DELETE CASCADE DISABLE
  );
  
  obclient> SELECT CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME,STATUS FROM user_constraints WHERE CONSTRAINT_NAME LIKE 'FK_MMS_GROUPUSE%';
  
  obclient> ALTER TABLE MMS_GROUPUSER ENABLE CONSTRAINT FK_MMS_GROUPUSER_02;
  
  obclient> SELECT CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME,STATUS FROM user_constraints WHERE CONSTRAINT_NAME LIKE 'FK_MMS_GROUPUSE%';
  

• パーティションテーブル tbl6 のパーティション M202001 および M202002 のすべてのデータを削除します。

  obclient> CREATE TABLE tbl6 (log_id number NOT NULL,log_value varchar2(50),log_date date NOT NULL DEFAULT sysdate)
  PARTITION BY RANGE(log_date)
  (
      PARTITION M202001 VALUES LESS THAN(TO_DATE('2020/02/01','YYYY/MM/DD'))
      , PARTITION M202002 VALUES LESS THAN(TO_DATE('2020/03/01','YYYY/MM/DD'))
      , PARTITION M202003 VALUES LESS THAN(TO_DATE('2020/04/01','YYYY/MM/DD'))
      , PARTITION M202004 VALUES LESS THAN(TO_DATE('2020/05/01','YYYY/MM/DD'))
      , PARTITION M202005 VALUES LESS THAN(TO_DATE('2020/06/01','YYYY/MM/DD'))
      , PARTITION MMAX VALUES LESS THAN (MAXVALUE)
  );
  
  obclient> ALTER TABLE tbl6 TRUNCATE PARTITION M202001, M202002 UPDATE GLOBAL INDEXES;
  

• テーブル tbl7 の CHECK 制約 tbl7_equal_check1 を削除します。

  obclient> CREATE TABLE tbl7 (col1 INT, col2 INT, col3 INT,CONSTRAINT tbl7_equal_check1 CHECK(col2 = col3 * 2) ENABLE VALIDATE);
  
  obclient> SELECT CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME,STATUS FROM user_constraints WHERE TABLE_NAME LIKE 'TBL%';
  
  obclient> ALTER TABLE tbl7 DROP CONSTRAINT tbl7_equal_check1;
  
  obclient> SELECT CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME,STATUS FROM user_constraints WHERE TABLE_NAME LIKE 'TBL%';
  

• テーブル tbl8 をテーブルグループ tblgroup1 からテーブルグループ tblgroup2 に移動します。

  obclient> SHOW TABLEGROUPS;
  
  obclient> ALTER TABLE tbl8 SET TABLEGROUP tblgroup2;
  
  obclient> SHOW TABLEGROUPS;
  

• テーブル primary_table に外部キー制約 cons_fk1 を追加します。

  obclient> CREATE TABLE primary_table (id NUMBER PRIMARY KEY, names VARCHAR(100) NOT NULL, foreign_col NUMBER);
  
  obclient> CREATE TABLE reference_table (id NUMBER PRIMARY key, comments VARCHAR2(100) NOT NULL);
  
  obclient> ALTER TABLE primary_table ADD CONSTRAINT cons_fk1 FOREIGN KEY(foreign_col) REFERENCES reference_table(id);
  

• テーブル tbl9 に主キー制約 tbl1_pk を追加します。

  obclient> CREATE TABLE tbl9 (col1 NUMBER, col2 INT,col3 VARCHAR2(100));
  
  obclient> ALTER TABLE tbl9 ADD CONSTRAINT tbl1_pk PRIMARY KEY (col1);
  

• テーブル tbl9 の主キーを col2 列に変更します。

  obclient> ALTER TABLE tbl9 MODIFY PRIMARY KEY(col2);
  

• テーブル tbl9 の主キーを削除します。

  obclient> ALTER TABLE tbl9 DROP PRIMARY KEY;
  

• パーティションとサブパーティションの名前を変更します。

  /* サブパーティションテーブル range_range_table を作成し、col1 に基づいてローカルインデックスを作成 */
  CREATE TABLE range_range_table(col1 INT, col2 INT, col3 INT)
      PARTITION BY RANGE(col1)
      SUBPARTITION BY RANGE(col2)
         (PARTITION p0 VALUES LESS THAN(100)
             (SUBPARTITION sp0 VALUES LESS THAN(100),
              SUBPARTITION sp1 VALUES LESS THAN(200)
             ),
          PARTITION p1 VALUES LESS THAN(200)
             (SUBPARTITION sp2 VALUES LESS THAN(100),
              SUBPARTITION sp3 VALUES LESS THAN(200),
              SUBPARTITION sp4 VALUES LESS THAN(300)
             )
          );
  
  CREATE INDEX local_idx_for_range_range_tb ON range_range_table (col1) LOCAL;
  
  /* パーティションの名前を変更しますが、この変更はローカルインデックスのパーティション名には反映されません */
  

  obclient> SELECT partition_name FROM SYS.USER_TAB_PARTITIONS WHERE table_name = 'RANGE_RANGE_TABLE';
  
  obclient> ALTER TABLE range_range_table RENAME PARTITION p0 TO p10;
  
  obclient> SELECT partition_name FROM SYS.USER_TAB_PARTITIONS WHERE table_name = 'RANGE_RANGE_TABLE';
  
  obclient> SELECT partition_name FROM SYS.USER_IND_PARTITIONS WHERE index_name = 'LOCAL_IDX_FOR_RANGE_RANGE_TB';
  
  /* サブパーティションの名前を変更しますが、この変更はローカルインデックスのパーティション名には反映されません */
  obclient> SELECT partition_name, subpartition_name FROM SYS.USER_TAB_SUBPARTITIONS WHERE table_name = 'RANGE_RANGE_TABLE';
  
  obclient> ALTER TABLE range_range_table RENAME SUBPARTITION sp0 TO sp10;
  
  obclient> SELECT partition_name, subpartition_name FROM SYS.USER_TAB_SUBPARTITIONS WHERE table_name = 'RANGE_RANGE_TABLE';
  
  obclient> SELECT partition_name, subpartition_name FROM SYS.USER_IND_SUBPARTITIONS WHERE index_name = 'LOCAL_IDX_FOR_RANGE_RANGE_TB';
  

• テーブルのカラムストア属性を変更します。

  1. 以下のSQLステートメントを使用してテーブル tbl1 を作成します。

     CREATE TABLE tbl1 (col1 INT PRIMARY KEY, col2 VARCHAR(50));
     

  2. テーブル tbl1 を行ストア・カラムストアの冗長テーブルに変更し、その後行ストア・カラムストアの冗長属性を削除します。

     ALTER TABLE tbl1 ADD COLUMN GROUP(all columns, each column);
     

     ALTER TABLE tbl1 DROP COLUMN GROUP(all columns, each column);
     

  3. テーブル tbl1 をカラムストアテーブルに変更し、その後カラムストア属性を削除します。

     ALTER TABLE tbl1 ADD COLUMN GROUP(each column);
     

     ALTER TABLE tbl1 DROP COLUMN GROUP(each column);
     

• テーブル内の列のSkip Index属性を変更します。

  1. 以下のSQLステートメントを使用してテーブル test_skidx を作成します。

     CREATE TABLE test_skidx(
         col1 NUMBER SKIP_INDEX(MIN_MAX, SUM),
         col2 FLOAT SKIP_INDEX(MIN_MAX),
         col3 VARCHAR2(1024) SKIP_INDEX(MIN_MAX),
         col4 CHAR(10)
       );
     

  2. テーブル test_skidx の列 col2 のSkip Index属性を SUM Skip Indexタイプに変更します。

     ALTER TABLE test_skidx MODIFY col2 FLOAT SKIP_INDEX(SUM);
     

  3. テーブル作成後に新しい列のSkip Index属性を追加します。テーブル test_skidx の列 col4 に MIN_MAX Skip Indexタイプを追加します。

     ALTER TABLE test_skidx MODIFY col4 CHAR(10) SKIP_INDEX(MIN_MAX);
     

  4. テーブル作成後に列のSkip Index属性を削除します。テーブル test_skidx の列 col1 のSkip Index属性を削除します。

     ALTER TABLE test_skidx MODIFY col1 NUMBER SKIP_INDEX();
     

• テーブル属性を変更するには、以下のコマンドを実行して永続化マクロブロックレベルのbloom filterを無効にします。対象はテーブル tb です。

  ALTER TABLE tb SET enable_macro_block_bloom_filter = False;
  

Oracleテナントの読み取り専用および読み書き可能なテーブル

Oracleテナントでは、CREATE TABLE を使用して READ ONLY および READ WRITE タイプのテーブルを作成できます。また、ALTER TABLE ステートメントを使用してテーブルの読み書き属性を変更することもできます。

操作手順：

1. 通常ユーザーを作成します：

   CREATE USER test1 IDENTIFIED BY "12345";
   

2. ユーザーに接続およびテーブル作成権限を付与します：

   GRANT CREATE SESSION TO test1;
   GRANT CREATE TABLE TO test1;
   

3. 通常ユーザーでOceanBaseデータベースに接続します：

   obclient -hxxx.xx.xxx.xxx -P2881 -utest1@oracle001  -A
   

4. 読み取り専用テーブルを作成します：

   CREATE TABLE tb_readonly1(id INT) READ ONLY;
   

5. 読み取り専用テーブルにデータを挿入する試み（失敗が予想されます）：

   INSERT INTO tb_readonly1 VALUES (1);
   -- 期待されるエラー：ORA-00600: internal error code, arguments: -5235, The table 'TEST1.TB_READONLY1' is read only so it cannot execute this statement
   

6. 読み書き可能なテーブルを作成します：

   CREATE TABLE tb_readwrite1(id INT) READ WRITE;
   

7. 読み書き可能なテーブルにデータを挿入します（成功が予想されます）：

   INSERT INTO tb_readwrite1 VALUES (99),(98);
   -- 期待される結果：Query OK, 2 rows affected (0.002 sec)
   --             Records: 2  Duplicates: 0  Warnings: 0
   

8. 読み書き可能なテーブルを読み取り専用テーブルに変換します：

   ALTER TABLE tb_readwrite1 READ ONLY;
   

9. 変換後の読み取り専用テーブルにデータを挿入する試み（失敗が予想されます）：

   INSERT INTO tb_readwrite1 VALUES (96),(97);
   -- 期待されるエラー：ORA-00600: internal error code, arguments: -5235, The table 'TEST1.TB_READWRITE1' is read only so it cannot execute this statement
   

関連ドキュメント

• テーブルの変更
• テーブルの作成
• テーブルのクリア
• テーブルの削除