主キー作成・削除コマンド取得

いきさつ

たまに何故か必要になる主キーの削除と戻し作業。なんででしょうね・・・。とりあえず、コマンドを保存しておきましょう。

取得SQL

------------------------------------------------------------------
-- Copyright(c) 2015-2016 pakkin. All Rights Reserved.
-- [改訂履歴]
-- 2015.08.22 作成
-- 2015.09.25 統計収集オプション追加、最大列数32まで可能に
-- 2016.07.19 LISTAGGコマンドに変更、マテリアライズド・ビューPK除外
-- 
-- [項目]
-- ADDPK_CMDLINE : 主キー追加コマンド
-- DELPK_CMDLINE : 主キー削除コマンド
-- 
-- [注意事項]
-- 降順指定には対応していません。
------------------------------------------------------------------
  select 'ALTER TABLE ' || rpad(cons.TABLE_NAME,30)
      || ' ADD CONSTRAINT ' || rpad(cons.CONSTRAINT_NAME,30) || ' PRIMARY KEY('
      || LISTAGG(cols.COLUMN_NAME, ',') WITHIN GROUP (order by cols.COLUMN_POSITION asc)
      || ') USING INDEX COMPUTE STATISTICS ;' as ADDPK_CMDLINE
       , 'ALTER TABLE ' || rpad(cons.TABLE_NAME,30) || ' DROP PRIMARY KEY;'  as DELPK_CMDLINE
    from USER_CONSTRAINTS  cons
       , USER_IND_COLUMNS  cols
   where cons.TABLE_NAME      = cols.TABLE_NAME
     and cons.INDEX_NAME      = cols.INDEX_NAME
     and cons.CONSTRAINT_TYPE = 'P'
--   and cons.TABLE_NAME in ('TABLE1','TABLE2')
     and cons.CONSTRAINT_NAME not like 'BIN$%' --ごみ箱除外
     and cons.CONSTRAINT_NAME not like 'SYS@_C@_SNAP$_%' escape '@' -- マテリアライズド・ビューPK除外
group by cons.TABLE_NAME, cons.CONSTRAINT_NAME
order by cons.TABLE_NAME, cons.CONSTRAINT_NAME
;

取得SQL(フルオプション)

------------------------------------------------------------------
-- Copyright(c) 2015 pakkin. All Rights Reserved.
-- [改訂履歴]
-- 2015.09.15 作成
-- 
-- [項目]
-- ADDPK_CMDLINE : 主キー追加コマンド(FULL)
-- DELPK_CMDLINE : 主キー削除コマンド
-- 
-- [注意事項]
-- CLOB型で返ります
-- 
-- [補足]
-- 省略したい文字は置換で削って下さい。
------------------------------------------------------------------
EXECUTE  DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'PRETTY',        FALSE);
EXECUTE  DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SQLTERMINATOR', TRUE);
  select substr(DBMS_METADATA.GET_DDL(OBJECT_TYPE=>'CONSTRAINT',NAME=>cons.CONSTRAINT_NAME),4) as ADDPK_CMDLINE
       , 'ALTER TABLE ' || rpad(cons.TABLE_NAME,30) || ' DROP PRIMARY KEY;'                    as DELPK_CMDLINE
    from USER_CONSTRAINTS       cons
   where cons.CONSTRAINT_TYPE = 'P'
--   and cons.TABLE_NAME in ('TABLE1','TABLE2')
     and cons.CONSTRAINT_NAME not like 'BIN$%' --ごみ箱除外(エラー回避)
order by cons.TABLE_NAME, cons.CONSTRAINT_NAME
;