Tip
2013.01.10 18:13

online reorg script

조회 수 10630 추천 수 0 댓글 0

예전에 사용한 online reorg script 입니다.

본인 입맛에 맞게 변경하여 사용하시기 바랍니다.

 

PROMPT
PROMPT select dbms_metadata.get_ddl('TABLE','TABLE','ABC') from dual;
PROMPT select dbms_metadata.get_ddl('INDEX','TABLE_I02','ABC') from dual;
PROMPT CREATE IMSI table script
 
  CREATE TABLE "ABC"."TABLE_IMSI"
   (    "IC_CODE" CHAR(3) NOT NULL ENABLE,
        "WORK_DATE" CHAR(8) NOT NULL ENABLE,
        "WORK_NO" CHAR(4) NOT NULL ENABLE,
        "SER_NO" NUMBER(8,0) NOT NULL ENABLE,
        "INOUT_DIV" CHAR(1),
        "VIOLATE_CODE" VARCHAR2(2),
        "OBU_NO" CHAR(16),
        "ECARD_NO" CHAR(16),
        "OUT_PROC_DATETIME" VARCHAR2(14),
        "OUT_FARE_TYPE" NUMBER(1,0),
        "TRD_CLASS" CHAR(1),
        "OBU_CLASS" CHAR(1),
        "IN_IC_CODE" CHAR(3),
        "PROC_TYPE" CHAR(2),
        "CAR_NO" VARCHAR2(16),
        "INQ_NAME" VARCHAR2(60),
        "ZIP_CODE" VARCHAR2(6),
        "TEL" VARCHAR2(20),
        "ADDR" VARCHAR2(100),
        "PROC_CODE" VARCHAR2(2),
        "STATUS_CODE" VARCHAR2(2),
        "COL_SEQ" NUMBER(11,0) DEFAULT 0,
        "COL_WILL_AMT" NUMBER(6,0) DEFAULT 0 NOT NULL ENABLE,
        "COL_WILL_EXTRA_AMT" NUMBER(6,0) DEFAULT 0 NOT NULL ENABLE,
        "COL_AMT" NUMBER(6,0) DEFAULT 0 NOT NULL ENABLE,
        "COL_EXTRA_AMT" NUMBER(6,0) DEFAULT 0 NOT NULL ENABLE,
        "COL_DIV" CHAR(1),
        "COL_DATE" VARCHAR2(8),
        "COL_IC_CODE" VARCHAR2(3),
        "MGR_NO" CHAR(4),
        "MGR_SER_NO" CHAR(12),
        "NOTICE_NO" VARCHAR2(20),
        "REMARK" VARCHAR2(100),
        "PROC_DATE" CHAR(8),
        "FEE_AMT" NUMBER(11,0) DEFAULT 0,
        "DOCU_NO" VARCHAR2(100),
        "SEIZURE_DATE" VARCHAR2(8),
        "SEIZURE_CAN_DATE" VARCHAR2(8),
        "SEIZURE_FLAG" CHAR(1) DEFAULT 0,
        "ECARD_GUBUN" VARCHAR2(1) DEFAULT '0',
        "FREE_DC_GUBUN" VARCHAR2(1),
        "PROC_KIND" VARCHAR2(2),
         CONSTRAINT "TABLE_P01_IMSI" PRIMARY KEY
("WORK_DATE", "IC_CODE", "WORK_NO", "SER_NO")
  USING INDEX PCTFREE 5 INITRANS 2 MAXTRANS 255
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "TCS_INX_ES"  ENABLE
   ) PCTFREE 5 PCTUSED 90 INITRANS 2 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "TCS_DATA_ES"

PROMPT
PROMPT press enter key to next execute..
PAUSE
PROMPT ADD CONSTRAINTS
PROMPT

 
PROMPT
PROMPT press enter key to next execute..
PAUSE
PROMPT  
PROMPT ONLINE REORG(dbms_redifinition)
PROMPT

PROMPT CHECKING whether PACKAGE USING TO TABLE
exec DBMS_REDEFINITION.can_redef_table ('ABC','TABLE');


PROMPT
PROMPT press enter key to next execute..
PAUSE
PROMPT  EXECUTE DBMS_REDEFINITION.start_redef_table..
PROMPT

exec DBMS_REDEFINITION.start_redef_table ('ABC','TABLE','TABLE_IMSI');


PROMPT
PROMPT press enter key to next execute..
PAUSE


PROMPT CREATE INDEX ON IMSI TABLE
PROMPT CREATE INDEX


 CREATE INDEX "ABC"."TABLE_I01_IMSI" ON "ABC"."TABLE" ("NOTICE_NO" DESC)
  PCTFREE 5 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 7 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "TCS_INX_ES"

  CREATE INDEX "ABC"."TABLE_I02_IMSI" ON "ABC"."TABLE" ("CAR_NO")
  PCTFREE 10 INITRANS 2 MAXTRANS 255
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "TCS_INX_ES"

  CREATE INDEX "ABC"."TABLE_I03_IMSI" ON "ABC"."TABLE" ("PROC_DATE")
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "TCS_INX_ES"


PROMPT
PROMPT press enter key to next execute..
PAUSE

PROMPT
PROMPT TABLE SYNC (dbms_redefinition.sync_interim_table)
PROMPT

exec dbms_redefinition.sync_interim_table('ABC','TABLE','TABLE_IMSI');

 
PROMPT press enter key to next execute..
PAUSE
PROMPT EXECUTE dbms_redefinition.finish_redef_table..
PROMPT

exec dbms_redefinition.finish_redef_table('ABC','TABLE','TABLE_IMSI');

PROMPT
PROMPT
PROMPT press enter key to next execute..
PAUSE

PROMPT
PROMPT index(constraint) rename
PROMPT

--alter index ABC.HIGHCDUSE_M_P01 rename to HIGHCDUSE_M_P01_bk;
--alter index ABC.HIGHCDUSE_M_P01_IMSI rename to HIGHCDUSE_M_P01;
alter index TABLE_P01 rename to TABLE_P01_BK; 
alter index TABLE_P01_IMSI rename to TABLE_P01;

alter index TABLE_P02 rename to TABLE_P02_BK; 
alter index TABLE_P02_IMSI rename to TABLE_P02;

alter index TABLE_P03 rename to TABLE_P03_BK; 
alter index TABLE_P03_IMSI rename to TABLE_P03;

alter table ABC.TABLE_IMSI rename constraint TABLE_P01 to TABLE_P01_BK;
alter table ABC.TABLE rename constraint TABLE_P01_IMSI to TABLE_P01;

 

 

PROMPT
PROMPT ALTER PUBLIC SYNONYM TEST COMPILE;

 

PROMPT press enter key to next execute..
PAUSE

PROMPT CHECK REORG TABLE
PROMPT INDEX INFOMATION
PROMPT WHEN YOU NEED TO ABORT_REDEF_TABLE
PROMPT #######################################
PROMPT
PROMPT
PROMPT exec dbms_redefinition.abort_redef_table('DAM', 'BIG_TABLE', 'BIG_TABLE_')
PROMPT
PROMPT
PROMPT########################################

 


List of Articles
번호 분류 제목 글쓴이 날짜 조회 수
공지 Q&A Oracle관련 게시물만 Sean 2014.04.09 84839
159 Q&A 오라클 복구 질문 희달이 2015.05.08 1148
158 Q&A import후 simple_size 0일때 고구마 2014.01.13 3194
157 Tip 통계복원하기 고구마 2013.05.13 8032
156 Tip 유저별 Segment 개수 및 사이즈 조회 쿼리, n2bfree 2013.03.12 10942
» Tip online reorg script 담벼락 2013.01.10 10630
154 TM 11G new feature SQL performance analyzer 1 file Sean 2013.01.09 14284
153 TM MEMORY TARGET 1 도로시 2013.01.03 19588
152 TM check whether crs auto start is enable or disable 담벼락 2012.10.12 26273
151 TM ARDCI file 이현정 2012.10.09 12520
150 TM (10gR2)Full UNDO tablespace Tech1 2012.04.04 23918
149 TM audit 관련 2 이현정 2012.03.29 26507
148 TM 테이블과 인덱스의 관계 및 인덱스 종류 및 생성 방법. Tech1 2012.03.28 25512
147 TM windows에서 Opatch 진행시 oci.dll 에러 조치 1 이현정 2012.03.26 37418
146 Q&A [답변] 테이블 수정, 삭제 및 LOB에 대해 알려주세요 Tech1 2012.03.26 10644
145 Q&A ORACLE_BASE, ORACLE_HOME 각각의 위치에 대한 설명 부탁드립니다 Tech1 2012.03.20 10850
144 TM 테이블 생성방법 및 각종옵션 Tech1 2012.03.20 20710
143 TM upgrade 시 sorting 문제 담벼락 2012.03.16 16458
142 Tip 오라클11g에서 SE1,SE,EE제품의 옵션비교입니다. file perfstat 2012.03.07 27010
141 Tip 오라클 업그레이드 메뉴얼 1 file perfstat 2012.02.06 11894
Board Pagination Prev 1 2 3 4 5 6 7 8 Next
/ 8