USER資料庫相關表：配置需求：

TABLE1 FT_TBS meas_time

TABLE2 FDC_TBS run_info_key

TABLE3 WAT_TBS Wafer_key

TABLE4 SORT_TBS meas_time

TABLE5 FDC_TBS run_info_key

TABLE6 FDC_TBS start_time

TABLE7 FDC_TBS run_info_key

1、備份：測試時使用2個併發，耗時23h42min，正式實施時啟用20併發，時間預估3h。

expdp USERNAME/USERNAME directory=bkdump dumpfile=tables_%U.dmp filesize=20480M logfile=tables_2023-7-27.log tables=USER.TABLE1,USER.TABLE2,USER.TABLE3,USER.TABLE4,USER.TABLE5,USER.TABLE6,USER.TABLE7 parallel=20 cluster=N compression=all job_name=my_job <exclude=statistics>

若出現異常：匯入恢復。耗時15h，同時需要注意tmp表空間大小。

impdp \"/as sysdba\" directory=dpdata1 dumpfile=tables_%U.dmp logfile=imp_tab_0729.log tables=USER.TABLE1,USER.TABLE2,USER.TABLE3,USER.TABLE4,USER.TABLE5,USER.TABLE6,USER.TABLE7 parallel=20 job_name=my_job2

2、原表資訊

2.1 表許可權授權配置資訊：交換分割槽後需要重新授權

SELECT 'grant select on '||t.OWNER ||'.'||t.TABLE_NAME||' to '||t.GRANTEE||';' ,t.* FROM dba_tab_privs t WHERE t.table_name in

('TABLE1','TABLE2','TABLE3','TABLE4','TABLE5','TABLE6','TABLE7');

grant select on USER.TABLE2 to USER_VIEW1

grant select on USER.TABLE5 to USER_VIEW1

grant select on USER.TABLE7 to USER_VIEW1

grant select on USER.TABLE1 to USER_VIEW1

grant select on USER.TABLE4 to USER_VIEW1

grant select on USER.TABLE3 to USER_VIEW1

grant select on USER.TABLE2 to USER_VIEW2;

grant select on USER.TABLE5 to USER_VIEW2;

grant select on USER.TABLE7 to USER_VIEW2;

grant select on USER.TABLE1 to USER_VIEW2;

grant select on USER.TABLE4 to USER_VIEW2;

grant select on USER.TABLE3 to USER_VIEW2;

grant select on USER.TABLE6 to USER_VIEW3;

grant select on USER.TABLE2 to USER_VIEW3;

grant select on USER.TABLE5 to USER_VIEW3;

grant select on USER.TABLE7 to USER_VIEW3;

grant select on USER.TABLE1 to USER_VIEW3;

grant select on USER.TABLE4 to USER_VIEW3;

grant select on USER.TABLE3 to USER_VIEW3;

grant select on USER.TABLE6 to USER_VIEW4;

grant select on USER.TABLE2 to USER_VIEW4;

grant select on USER.TABLE5 to USER_VIEW4;

grant select on USER.TABLE7 to USER_VIEW4;

grant select on USER.TABLE1 to USER_VIEW4;

grant select on USER.TABLE4 to USER_VIEW4;

grant select on USER.TABLE3 to USER_VIEW4;

2.2 同義詞配置資訊：交換分割槽後不需要重新建立同義詞，但需要確認同義詞是否與原來保持一致。

SELECT * FROM DBa_SYNONYMS WHERE table_owner='USER' AND table_name in

('TABLE1','TABLE2','TABLE3','TABLE4','TABLE5','TABLE6','TABLE7');

|-------|----------------------|-----------|----------------------|-------|-------------|

2.3 確認為非分割槽表：

SELECT * FROM USER_PART_TABLES WHERE table_name in

('TABLE1','TABLE2','TABLE3','TABLE4','TABLE5','TABLE6','TABLE7');

SELECT t.LAST_ANALYZED ,t.NUM_ROWS ,t.* FROM user_tab_partitions t WHERE table_name in

('TABLE1','TABLE2','TABLE3','TABLE4','TABLE5','TABLE6','TABLE7');

分割槽鍵

SELECT * FROM USER_PART_KEY_COLUMNS WHERE name in

('TABLE1','TABLE2','TABLE3','TABLE4','TABLE5','TABLE6','TABLE7');

2.4 確認表空間相關資訊：

SELECT t.LAST_ANALYZED ,t.NUM_ROWS,t.TABLESPACE_NAME ,t.*

FROM user_tables t WHERE table_name IN ('TABLE1','TABLE2','TABLE3',

'TABLE4','TABLE5','TABLE6','TABLE7');

|-----------------------|---------|---------------|----------------------|---------------|

2.5 檢視索引資訊

SELECT t.TABLESPACE_NAME ,t.STATUS ,t.* FROM USER_INDEXES t WHERE t.table_name IN

('TABLE1','TABLE2','TABLE3','TABLE4','TABLE5','TABLE6','TABLE7');

|---------------|------|-------------------------|----------|-----------|----------------------|

TABLE3 -- 1個索引

TABLE4 -- 4個索引

TABLE6 -- 2個索引

TABLE2 -- 1個索引

TABLE5 -- 3個索引

TABLE7 -- 2個索引

TABLE1 -- 1個索引

分割槽後檢視索引資訊：

SELECT t.PARTITION_NAME,t.HIGH_VALUE,t.TABLESPACE_NAME,STATUS,t.* FROM USER_IND_PARTITIONS t where t.INDEX_NAME in ('PK_CHAMBER_DATA','PK_CHAMBER_DATA02','TABLE2_UK','TABLE5_UK','TABLE5_UK01','TABLE5_UK02','TABLE7_UK','TABLE7_UK01','UK_TABLE1_TEST','TABLE4_INDE2','SYS_IL0000123198C00028','SYS_IL0000123198C00027','TABLE4_INDEX1','UK_TABLE3');

2.6 索引相信資訊：

SELECT idx.index_type,idx_col.*

FROM USER_IND_COLUMNS idx_col,USER_INDEXES idx

WHERE idx_col.INDEX_NAME =idx.INDEX_NAME

AND idx_col.table_name in

('TABLE1','TABLE2','TABLE3','TABLE4','TABLE5','TABLE6','TABLE7');

|----------|-------------------------|----------------------|--------------|

2.7 檢視錶其他約束資訊：存在表欄位非空約束，無其他外來鍵約束

SELECT au.*,cu.POSITION

FROM user_cons_columns cu, user_constraints au

WHERE cu.CONSTRAINT_NAME =au.constraint_name

AND cu.TABLE_NAME in

('TABLE1','TABLE2','TABLE3','TABLE4','TABLE5','TABLE6','TABLE7');

|-----|---------------|---------------|----------------------|----------------------------|----------------------------|

2.8 LOB欄位分析 TABLE4 表存在2個lob欄位。 1.2TB 左右。

SELECT * FROM user_lobs WHERE table_name in

('TABLE1','TABLE2','TABLE3','TABLE4','TABLE5','TABLE6','TABLE7');

SELECT * FROM user_segments WHERE segment_name='SYS_LOB0000023100C00026$$';

SELECT sum(bytes/1024/1024/1024) FROM user_segments WHERE segment_name in ('SYS_LOB0000123198C00027$$','SYS_LOB0000123198C00028$$');

|--------------|----------------|-------------------------|---------------|------------------------|

其他：匯出作業異常處理。

set linesize 300

col OPERATION for a10

col TTACHED_SESSIONS for a10

col JOB_MODE for a10

col STATE for a12

select owner_name, job_name, operation, job_mode,

state, attached_sessions

from dba_datapump_jobs

where job_name like 'MY_JOB%'

order by 1, 2;

col OWNER.OBJECT for a20

select o.status, o.object_id, o.object_type,

o.owner||'.'||object_name "OWNER.OBJECT"

from dba_objects o, dba_datapump_jobs j

where o.owner=j.owner_name and

o.object_name=j.job_name and

j.job_name like 'MY_JOB%'

order by 4, 2;

drop table USERNAME.MY_JOB1;

---------------------------------------------------------

資訊確認

##############################################################################################################################

--確認授權

SELECT 'grant select on '||t.OWNER ||'.'||t.TABLE_NAME||' to '||t.GRANTEE||';' ,t.* FROM dba_tab_privs t WHERE t.table_name in

('TABLE1','TABLE2','TABLE3','TABLE4','TABLE5','TABLE6','TABLE7');

--確認同義詞

SELECT * FROM DBa_SYNONYMS WHERE table_owner='EDA' AND table_name in

('TABLE1','TABLE2','TABLE3','TABLE4','TABLE5','TABLE6','TABLE7');

##############################################################################################################################

--**************確認為分割槽表--**************

SELECT * FROM USER_PART_TABLES WHERE table_name in

('TABLE1','TABLE2','TABLE3','TABLE4','TABLE5','TABLE6','TABLE7');

--**************確認分割槽表資訊--**************

SELECT t.LAST_ANALYZED ,t.NUM_ROWS ,t.* FROM user_tab_partitions t WHERE table_name in

('TABLE1','TABLE2','TABLE3','TABLE4','TABLE5','TABLE6','TABLE7');

--確認分割槽鍵

SELECT * FROM USER_PART_KEY_COLUMNS WHERE name in

('TABLE1','TABLE2','TABLE3','TABLE4','TABLE5','TABLE6','TABLE7');

##############################################################################################################################

--####確認表資訊

SELECT t.LAST_ANALYZED ,t.NUM_ROWS,t.TABLESPACE_NAME ,t.*

FROM user_tables t WHERE table_name IN ('TABLE1','TABLE2','TABLE3',

'TABLE4','TABLE5','TABLE6','TABLE7');

--####確認索引資訊

SELECT t.TABLESPACE_NAME ,t.STATUS ,t.* FROM USER_INDEXES t WHERE t.table_name IN

('TABLE1','TABLE2','TABLE3','TABLE4','TABLE5','TABLE6','TABLE7');

##############################################################################################################################

--**************確認分割槽索引資訊--**************

SELECT t.PARTITION_NAME,t.HIGH_VALUE,t.TABLESPACE_NAME,STATUS,t.* FROM USER_IND_PARTITIONS t where t.INDEX_NAME in

('PK_CHAMBER_DATA','PK_CHAMBER_DATA02','TABLE2_UK','TABLE5_UK','TABLE5_UK01',

'TABLE5_UK02','TABLE7_UK','TABLE7_UK01','UK_TABLE1_TEST','TABLE4_INDE2',

'SYS_IL0000123198C00028','SYS_IL0000123198C00027','TABLE4_INDEX1','UK_TABLE3');

--####確認索引的相信資訊

SELECT idx.index_type,idx_col.*

FROM USER_IND_COLUMNS idx_col,USER_INDEXES idx

WHERE idx_col.INDEX_NAME =idx.INDEX_NAME

AND idx_col.table_name in

('TABLE1','TABLE2','TABLE3','TABLE4','TABLE5','TABLE6','TABLE7');

##############################################################################################################################

--確認相關約束

SELECT au.*,cu.POSITION

FROM user_cons_columns cu, user_constraints au

WHERE cu.CONSTRAINT_NAME =au.constraint_name

AND cu.TABLE_NAME in

('TABLE1','TABLE2','TABLE3','TABLE4','TABLE5','TABLE6','TABLE7');

---確認lob資訊

SELECT * FROM user_lobs WHERE table_name in

('TABLE1','TABLE2','TABLE3','TABLE4','TABLE5','TABLE6','TABLE7');

SELECT * FROM user_segments WHERE segment_name='SYS_LOB0000023100C00026$$';

SELECT sum(bytes/1024/1024/1024) FROM user_segments WHERE segment_name in ('SYS_LOB0000123198C00027$$','SYS_LOB0000123198C00028$$');

##############################################################################################################################

3、建立臨時表作為分割槽交換表。

3.1 原表建立語句。

表：TABLE1

索引：PK_CHAMBER_DATA

索引：PK_CHAMBER_DATA02

set long 10000 pages 10000

select dbms_metadata.get_ddl('TABLE','TABLE1','USER') from dual;

CREATE TABLE "USER"."TABLE1"

( "EQUIPMENT_TYPE" VARCHAR2(256),

"EQUIPMENT" VARCHAR2(256),

"LOT_NAME" VARCHAR2(40),

"WAFER_NAME" VARCHAR2(40),

"MATERIAL_NAME" VARCHAR2(256),

"STARTTIME" DATE,

"STOPTIME" DATE,

"CHAMBER" VARCHAR2(256),

"RECIPE" VARCHAR2(256),

"STAGE" VARCHAR2(40),

"OP_NAME" VARCHAR2(40),

"PRODUCT" VARCHAR2(40),

"ROUTE" VARCHAR2(40),

"UPDATE_TIME" DATE

) SEGMENT CREATION IMMEDIATE

PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255

NOCOMPRESS 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 "FDC_TBS"

3.2 原表索引建立語句。

set long 10000 pages 10000

select dbms_metadata.get_ddl('INDEX','PK_CHAMBER_DATA02','USER') from dual;

CREATE INDEX "USER"."PK_CHAMBER_DATA" ON "USER"."TABLE1" ("LOT_NAME","WAFER_NAME", "EQUIPMENT", "STAGE", "OP_NAME", "STARTTIME", "STOPTIME", "CHAMBER", "PRODUCT", "ROUTE")

PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS

STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 MAXSIZE UNLIMITED

BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

TABLESPACE "FDC_TBS"

CREATE INDEX "USER"."PK_CHAMBER_DATA02" ON "USER"."TABLE1" ("STARTTIME", "EQUIPMENT", "STAGE", "LOT_NAME")

PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS

STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 MAXSIZE UNLIMITED

BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

TABLESPACE "FDC_TBS"

3.3 新表建立語句。注意表名、表空間

CREATE TABLE "USER"."TABLE1_PART2"

( "EQUIPMENT_TYPE" VARCHAR2(256),

"EQUIPMENT" VARCHAR2(256),

"LOT_NAME" VARCHAR2(40),

"WAFER_NAME" VARCHAR2(40),

"MATERIAL_NAME" VARCHAR2(256),

"STARTTIME" DATE,

"STOPTIME" DATE,

"CHAMBER" VARCHAR2(256),

"RECIPE" VARCHAR2(256),

"STAGE" VARCHAR2(40),

"OP_NAME" VARCHAR2(40),

"PRODUCT" VARCHAR2(40),

"ROUTE" VARCHAR2(40),

"UPDATE_TIME" DATE

) SEGMENT CREATION IMMEDIATE

PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255

NOCOMPRESS 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 "FDC_TBS"

partition by range("STARTTIME") interval(numtoyminterval(1,'MONTH'))

(

partition "P1" values less than(to_date('2023-09-1 00:00:00','SYYYY-MM-DD HH24:MI:SS','NLS_CALENDAR=GREGORIAN')) TABLESPACE "FDC_TBS"

)

3.4 新表索引建立語句。注意表名、索引名、本地索引、表空間

CREATE INDEX "USER"."PK_CHAMBER_DATA_PART2" ON "USER"."TABLE1_PART2" ("LOT_NAME","WAFER_NAME", "EQUIPMENT", "STAGE", "OP_NAME", "STARTTIME", "STOPTIME", "CHAMBER", "PRODUCT", "ROUTE")

PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS

STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 MAXSIZE UNLIMITED

BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

TABLESPACE "FDC_TBS"

local;

CREATE INDEX "USER"."PK_CHAMBER_DATA02_PART2" ON "USER"."TABLE1_PART2" ("STARTTIME", "EQUIPMENT", "STAGE", "LOT_NAME")

PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS

STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 MAXSIZE UNLIMITED

BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

TABLESPACE "FDC_TBS"

local;

4、交換分割槽

###<測試環境temp清理：

###select file_name from dba_temp_files;

###select * from v$sort_usage;

###select se.sid,se.serial#,s.sql_text from v$sort_usage su,v$parameter p,v$session se,v$sql s

###where p.name='db_block_size' and su.session_addr=se.saddr and s.hash_value=su.sqlhash and s.address=su.sqladdr

###order by se.username,se.sid;

###alter system kill session '4,62469' immediate;

###1、建立 temp2

###create temporary tablespace temp2 tempfile '/oradata/temptbs/atemp01.dbf' size 20480M autoextend on;

###2、修改預設temp表空間為temp2

###alter database default temporary tablespace temp2;

###3、刪除原表空間TEMP

###drop tablespace TEMP1 including contents and datafiles;

###4、重新建立原表空間TEMP

###create temporary tablespace TEMP tempfile '+DATA/USER/TEMPFILE/temp01.dbf' size 2048M autoextend on;

###5、修改預設temp表空間為TEMP

###alter database default temporary tablespace TEMP;

###6、刪除temp2

###drop tablespace temp2 including contents and datafiles;

###7、擴容 TEMP

###alter tablespace temp add tempfile '+DATA/USER/TEMPFILE/temp02.dbf' size 2048M autoextend on;

###alter tablespace temp add tempfile '+DATA/USER/TEMPFILE/temp03.dbf' size 2048M autoextend on;>

### ************************

表：TABLE1

索引：PK_CHAMBER_DATA

索引：PK_CHAMBER_DATA02

alter table USER.TABLE1_PART2 exchange partition P1 with table USER.TABLE1 including indexes without validation;

瞬間執行成功。原表表 FT_FAIL_RAW 還在，空的。

5、重新命名錶名：

rename TABLE1 to TABLE1_OLD;

alter index PK_CHAMBER_DATA rename to PK_CHAMBER_DATA_OLD;

alter index PK_CHAMBER_DATA02 rename to PK_CHAMBER_DATA02_OLD;

rename TABLE1_PART2 to TABLE1;

alter index PK_CHAMBER_DATA_PART2 rename to PK_CHAMBER_DATA;

alter index PK_CHAMBER_DATA02_PART2 rename to PK_CHAMBER_DATA02;

索引正常，不需要重建。

6、收集統計資訊：

exec dbms_stats.gather_table_stats('USER','TABLE1',degree=>20)

PS:異常時排查2個表之間差異的列。

conn USER/"password"

select col#,name from sys.col$ where obj# in (select object_id from user_objects where object_name='FT_RAW_PART2');

select col#,name from sys.col$ where obj# in (select object_id from user_objects where object_name='FT_RAW');

select a.column_id "COLUMN_ID", a.table_name "TABLE_NAME",

a.column_name, a.data_type, a.data_length

from user_tab_columns a, user_tab_columns b

where a.column_id (+) = b.column_id

and (a.data_type != b.data_type

or a.data_length != b.data_length)

and a.table_name = upper('FT_RAW_PART2')

and b.table_name = upper('FT_RAW')

union

select b.column_id "COLUMN_ID", b.table_name "TABLE_NAME",

b.column_name, b.data_type, b.data_length

from user_tab_columns a, user_tab_columns b

where b.column_id (+) = a.column_id

and (a.data_type != b.data_type

or a.data_length != b.data_length)

and a.table_name = upper('FT_RAW_PART2')

and b.table_name = upper('FT_RAW')

order by table_name, column_id;

SQL> select col#, name

2 from sys.col$

3 where obj# in

4 (select object_id from user_objects where object_name = '<table>');

----------------------------------------

3、建立臨時表作為分割槽交換表。

3.1 原表建立語句。

表：TABLE1

索引：TABLE1_UK

索引：TABLE1_UK01

set long 10000 pages 10000

select dbms_metadata.get_ddl('TABLE','TABLE1','USER') from dual;

CREATE TABLE "USER"."TABLE1"

( "RUN_INFO_KEY" NUMBER NOT NULL ENABLE,

"LOT_NAME" VARCHAR2(256),

"WAFER_NAME" VARCHAR2(256),

"AUX1" VARCHAR2(256),

"AUX2" VARCHAR2(256),

"TECHNOLOGY" VARCHAR2(256),

"FAB" VARCHAR2(12),

"PRODUCT" VARCHAR2(256),

"PROCESS" VARCHAR2(256),

"STAGE" VARCHAR2(256),

"ROUTE" VARCHAR2(256),

"STEP" VARCHAR2(256),

"CARRIER" VARCHAR2(256),

"SLOTNUMBER" NUMBER,

"PRODUCTFAMILY" VARCHAR2(256),

"LOTTYPE" VARCHAR2(256),

"SUBROUTE" VARCHAR2(256),

"INPUTLOADPORT" NUMBER,

"RETICLENAME" VARCHAR2(256),

"UPDATE_TIME" TIMESTAMP (6) NOT NULL ENABLE

) SEGMENT CREATION IMMEDIATE

PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255

NOCOMPRESS 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 "FDC_TBS"

3.2 原表索引建立語句。

set long 10000 pages 10000

select dbms_metadata.get_ddl('INDEX','TABLE1_UK01','USER') from dual;

CREATE UNIQUE INDEX "USER"."TABLE1_UK" ON "USER"."TABLE1" ("RUN_INFO_KEY", "WAFER_NAME")

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 FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

TABLESPACE "FDC_TBS"

CREATE INDEX "USER"."TABLE1_UK01" ON "USER"."TABLE1" ("LOT_NAME", "WAFER_NAME")

PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS

STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 MAXSIZE UNLIMITED

BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

TABLESPACE "FDC_TBS"

3.3 新表建立語句。注意表名

分割槽鍵型別為number id

SELECT max(RUN_INFO_KEY) FROM TABLE1; --13139935

--用表總資料量除以id最大值，預估分割槽間隔。=15881518/13139935 =1.2 每個id，

--間隔2000000一個分割槽，約200w行資料。

CREATE TABLE "USER"."TABLE1_PART2"

( "RUN_INFO_KEY" NUMBER NOT NULL ENABLE,

"LOT_NAME" VARCHAR2(256),

"WAFER_NAME" VARCHAR2(256),

"AUX1" VARCHAR2(256),

"AUX2" VARCHAR2(256),

"TECHNOLOGY" VARCHAR2(256),

"FAB" VARCHAR2(12),

"PRODUCT" VARCHAR2(256),

"PROCESS" VARCHAR2(256),

"STAGE" VARCHAR2(256),

"ROUTE" VARCHAR2(256),

"STEP" VARCHAR2(256),

"CARRIER" VARCHAR2(256),

"SLOTNUMBER" NUMBER,

"PRODUCTFAMILY" VARCHAR2(256),

"LOTTYPE" VARCHAR2(256),

"SUBROUTE" VARCHAR2(256),

"INPUTLOADPORT" NUMBER,

"RETICLENAME" VARCHAR2(256),

"UPDATE_TIME" TIMESTAMP (6) NOT NULL ENABLE

) SEGMENT CREATION IMMEDIATE

PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255

NOCOMPRESS 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 "FDC_TBS"

partition by range("RUN_INFO_KEY")

( partition "P1" values less than(16000000) TABLESPACE "FDC_TBS" ,

partition "P2" values less than(18000000) TABLESPACE "FDC_TBS" ,

partition "P3" values less than(20000000) TABLESPACE "FDC_TBS" ,

partition "P4" values less than(22000000) TABLESPACE "FDC_TBS" ,

partition "P5" values less than(24000000) TABLESPACE "FDC_TBS" ,

partition "P6" values less than(26000000) TABLESPACE "FDC_TBS" ,

partition "P7" values less than(28000000) TABLESPACE "FDC_TBS" ,

partition "P8" values less than(30000000) TABLESPACE "FDC_TBS" ,

partition "P9" values less than(32000000) TABLESPACE "FDC_TBS" ,

partition "P10" values less than(34000000) TABLESPACE "FDC_TBS" ,

partition "P11" values less than(36000000) TABLESPACE "FDC_TBS" ,

partition "P12" values less than(38000000) TABLESPACE "FDC_TBS" ,

partition "P13" values less than(40000000) TABLESPACE "FDC_TBS" ,

partition "P14" values less than(42000000) TABLESPACE "FDC_TBS" ,

partition "P15" values less than(44000000) TABLESPACE "FDC_TBS" ,

partition "P16" values less than(46000000) TABLESPACE "FDC_TBS" ,

partition "P17" values less than(48000000) TABLESPACE "FDC_TBS" ,

partition "P18" values less than(50000000) TABLESPACE "FDC_TBS" ,

partition "P19" values less than(52000000) TABLESPACE "FDC_TBS" ,

partition "P20" values less than(54000000) TABLESPACE "FDC_TBS" ,

partition "P21" values less than(56000000) TABLESPACE "FDC_TBS" ,

partition "P22" values less than(58000000) TABLESPACE "FDC_TBS" ,

partition "P23" values less than(60000000) TABLESPACE "FDC_TBS" ,

partition "P24" values less than(62000000) TABLESPACE "FDC_TBS" ,

partition "P25" values less than(64000000) TABLESPACE "FDC_TBS" ,

partition "P26" values less than(66000000) TABLESPACE "FDC_TBS" ,

partition "P27" values less than(68000000) TABLESPACE "FDC_TBS" ,

partition "P28" values less than(70000000) TABLESPACE "FDC_TBS" ,

partition "P29" values less than(72000000) TABLESPACE "FDC_TBS" ,

partition "P30" values less than(74000000) TABLESPACE "FDC_TBS" ,

partition "P31" values less than(76000000) TABLESPACE "FDC_TBS" ,

partition "P32" values less than(78000000) TABLESPACE "FDC_TBS" ,

partition "P33" values less than(80000000) TABLESPACE "FDC_TBS" ,

partition "P34" values less than(82000000) TABLESPACE "FDC_TBS" ,

partition "P35" values less than(84000000) TABLESPACE "FDC_TBS" ,

partition "P36" values less than(86000000) TABLESPACE "FDC_TBS" ,

PARTITION "PMAX" VALUES LESS THAN (MAXVALUE) TABLESPACE "FDC_TBS" );

3.4 新表索引建立語句。注意表名、索引名、本地索引

CREATE UNIQUE INDEX "USER"."TABLE1_UK_PART2" ON "USER"."TABLE1_PART2" ("RUN_INFO_KEY", "WAFER_NAME")

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 FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

TABLESPACE "FDC_TBS"

LOCAL;

CREATE INDEX "USER"."TABLE1_UK01_PART2" ON "USER"."TABLE1_PART2" ("LOT_NAME", "WAFER_NAME")

PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS

STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 MAXSIZE UNLIMITED

BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

TABLESPACE "FDC_TBS"

local;

表：TABLE1

索引：TABLE1_UK

索引：TABLE1_UK01

4、交換分割槽

alter table USER.TABLE1_PART2 exchange partition P1 with table USER.TABLE1 including indexes without validation;

5、重新命名錶名：

rename TABLE1 to TABLE1_OLD;

alter index TABLE1_UK rename to TABLE1_UK_OLD;

alter index TABLE1_UK01 rename to TABLE1_UK01_OLD;

rename TABLE1_PART2 to TABLE1;

alter index TABLE1_UK_PART2 rename to TABLE1_UK;

alter index TABLE1_UK01_PART2 rename to TABLE1_UK01;

索引正常，不需要重建。

6、收集統計資訊：

exec dbms_stats.gather_table_stats('USER','TABLE1',degree=>20)

PS:如需手工分裂最大分割槽，則按以下操作。注意，PMAX所有資料必須全部劃分到一個大的分割槽，且表空間不能更改。

檢視索引狀態。

SELECT PARTITION_NAME,HIGH_VALUE,TABLESPACE_NAME,STATUS

FROM USER_IND_PARTITIONS WHERE INDEX_NAME ='DEFECT_IMAGE_UK' ORDER BY 1;

檢視統計資訊。

SELECT t.LAST_ANALYZED ,t.NUM_ROWS ,t.* FROM user_tab_partitions t WHERE table_name='DEFECT_IMAGE';

##############################################################################################################################

EXEC dbms_stats.gather_table_stats('USER','DEFECT_IMAGE',partname=>'PMAX',degree=>6)

EXEC dbms_stats.gather_table_stats('USER','DEFECT_IMAGE',degree=>6)

##############################################################################################################################

ALTER TABLE USER.DEFECT_IMAGE split PARTITION pmax at(2025000) INTO (PARTITION p93 tablespace DEFECT_TBS_DATA_07,PARTITION pmax);

ALTER TABLE USER.DEFECT_IMAGE split PARTITION pmax at(2050000) INTO (PARTITION p94 tablespace DEFECT_TBS_DATA_08,PARTITION pmax);

ALTER TABLE USER.DEFECT_IMAGE split PARTITION pmax at(2075000) INTO (PARTITION p95 tablespace DEFECT_TBS_DATA_09,PARTITION pmax);

ALTER TABLE USER.DEFECT_IMAGE split PARTITION pmax at(2100000) INTO (PARTITION p96 tablespace DEFECT_TBS_DATA_10,PARTITION pmax);

ALTER TABLE USER.DEFECT_IMAGE split PARTITION pmax at(2125000) INTO (PARTITION p97 tablespace DEFECT_TBS_DATA_11,PARTITION pmax);

ALTER TABLE USER.DEFECT_IMAGE split PARTITION pmax at(2150000) INTO (PARTITION p98 tablespace DEFECT_TBS_DATA_12,PARTITION pmax);

重建索引。

ALTER INDEX USER.DEFECT_IMAGE_UK REBUILD PARTITION P93 TABLESPACE DEFECT_TBS_IDX_07 ONLINE PARALLEL 6;

ALTER INDEX USER.DEFECT_IMAGE_UK REBUILD PARTITION P94 TABLESPACE DEFECT_TBS_IDX_08 ONLINE PARALLEL 6;

ALTER INDEX USER.DEFECT_IMAGE_UK REBUILD PARTITION P95 TABLESPACE DEFECT_TBS_IDX_09 ONLINE PARALLEL 6;

ALTER INDEX USER.DEFECT_IMAGE_UK REBUILD PARTITION P96 TABLESPACE DEFECT_TBS_IDX_10 ONLINE PARALLEL 6;

ALTER INDEX USER.DEFECT_IMAGE_UK REBUILD PARTITION P97 TABLESPACE DEFECT_TBS_IDX_11 ONLINE PARALLEL 6;

-------------

3、建立臨時表作為分割槽交換表。

3.1 原表建立語句。

表：TABLE1

索引：TABLE1_UK

set long 10000 pages 10000

select dbms_metadata.get_ddl('TABLE','TABLE1','USER') from dual;

CREATE TABLE "USER"."TABLE1"

( "RUN_INFO_KEY" NUMBER NOT NULL ENABLE,

"INDICATOR_NAME" VARCHAR2(256) NOT NULL ENABLE,

"VALUE" FLOAT(126),

"LSL" FLOAT(126),

"HSL" FLOAT(126),

"LPL" FLOAT(126),

"HPL" FLOAT(126),

"LOL" FLOAT(126),

"HOL" FLOAT(126),

"UPDATE_TIME" TIMESTAMP (6) NOT NULL ENABLE

) SEGMENT CREATION IMMEDIATE

PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255

NOCOMPRESS 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 "FDC_TBS"

3.2 原表索引建立語句。

set long 10000 pages 10000

select dbms_metadata.get_ddl('INDEX','TABLE1_UK','USER') from dual;

CREATE UNIQUE INDEX "USER"."TABLE1_UK" ON "USER"."FDC_RUN_INDICA

TOR_DATA" ("RUN_INFO_KEY", "INDICATOR_NAME")

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 FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

TABLESPACE "FDC_TBS"

3.3 新表建立語句。注意表名

分割槽鍵型別為number id

SELECT max(RUN_INFO_KEY) FROM TABLE1; --13133568

--用表總資料量除以id最大值，預估分割槽間隔。=375974477/13133568 =29 每個id，

--間隔50000一個分割槽，約200w行資料。

CREATE TABLE "USER"."TABLE1_PART2"

( "RUN_INFO_KEY" NUMBER NOT NULL ENABLE,

"INDICATOR_NAME" VARCHAR2(256) NOT NULL ENABLE,

"VALUE" FLOAT(126),

"LSL" FLOAT(126),

"HSL" FLOAT(126),

"LPL" FLOAT(126),

"HPL" FLOAT(126),

"LOL" FLOAT(126),

"HOL" FLOAT(126),

"UPDATE_TIME" TIMESTAMP (6) NOT NULL ENABLE

) SEGMENT CREATION IMMEDIATE

PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255

NOCOMPRESS 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 "FDC_TBS"

partition by range("RUN_INFO_KEY")

( partition "P1" values less than(13200000) TABLESPACE "FDC_TBS" ,

partition "P2" values less than(13250000) TABLESPACE "FDC_TBS" ,

partition "P3" values less than(13300000) TABLESPACE "FDC_TBS" ,

partition "P4" values less than(13350000) TABLESPACE "FDC_TBS" ,

partition "P5" values less than(13400000) TABLESPACE "FDC_TBS" ,

partition "P6" values less than(13450000) TABLESPACE "FDC_TBS" ,

partition "P7" values less than(13500000) TABLESPACE "FDC_TBS" ,

partition "P8" values less than(13550000) TABLESPACE "FDC_TBS" ,

partition "P9" values less than(13600000) TABLESPACE "FDC_TBS" ,

partition "P10" values less than(13650000) TABLESPACE "FDC_TBS" ,

partition "P11" values less than(13700000) TABLESPACE "FDC_TBS" ,

partition "P12" values less than(13750000) TABLESPACE "FDC_TBS" ,

partition "P13" values less than(13800000) TABLESPACE "FDC_TBS" ,

partition "P14" values less than(13850000) TABLESPACE "FDC_TBS" ,

partition "P15" values less than(13900000) TABLESPACE "FDC_TBS" ,

partition "P16" values less than(13950000) TABLESPACE "FDC_TBS" ,

partition "P17" values less than(14000000) TABLESPACE "FDC_TBS" ,

partition "P18" values less than(14050000) TABLESPACE "FDC_TBS" ,

partition "P19" values less than(14100000) TABLESPACE "FDC_TBS" ,

partition "P20" values less than(14150000) TABLESPACE "FDC_TBS" ,

partition "P21" values less than(14200000) TABLESPACE "FDC_TBS" ,

partition "P22" values less than(14250000) TABLESPACE "FDC_TBS" ,

partition "P23" values less than(14300000) TABLESPACE "FDC_TBS" ,

partition "P24" values less than(14350000) TABLESPACE "FDC_TBS" ,

partition "P25" values less than(14400000) TABLESPACE "FDC_TBS" ,

partition "P26" values less than(14450000) TABLESPACE "FDC_TBS" ,

partition "P27" values less than(14500000) TABLESPACE "FDC_TBS" ,

partition "P28" values less than(14550000) TABLESPACE "FDC_TBS" ,

partition "P29" values less than(14600000) TABLESPACE "FDC_TBS" ,

partition "P30" values less than(14650000) TABLESPACE "FDC_TBS" ,

partition "P31" values less than(14700000) TABLESPACE "FDC_TBS" ,

partition "P32" values less than(14750000) TABLESPACE "FDC_TBS" ,

partition "P33" values less than(14800000) TABLESPACE "FDC_TBS" ,

partition "P34" values less than(14850000) TABLESPACE "FDC_TBS" ,

partition "P35" values less than(14900000) TABLESPACE "FDC_TBS" ,

partition "P36" values less than(14950000) TABLESPACE "FDC_TBS" ,

PARTITION "PMAX" VALUES LESS THAN (MAXVALUE) TABLESPACE "FDC_TBS" )

3.4 新表索引建立語句。注意表名、索引名、本地索引

CREATE UNIQUE INDEX "USER"."TABLE1_UK_PART2" ON "USER"."TABLE1_PART2" ("RUN_INFO_KEY", "INDICATOR_NAME")