oracle將表配置為分割槽表
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');
|OWNER |SYNONYM_NAME |TABLE_OWNER|TABLE_NAME |DB_LINK|ORIGIN_CON_ID|
|-------|----------------------|-----------|----------------------|-------|-------------|
|PUBLIC |TABLE1 |USER |TABLE1 | |0 |
|USER_VIEW2 |TABLE4 |USER |TABLE4 | |0 |
|USER_VIEW2 |TABLE3 |USER |TABLE3 | |0 |
|USER_VIEW2 |TABLE2|USER |TABLE2| |0 |
|USER_VIEW2 |TABLE5 |USER |TABLE5 | |0 |
|USER_VIEW2 |TABLE7 |USER |TABLE7 | |0 |
|USER_VIEW3|TABLE5 |USER |TABLE5 | |0 |
|USER_VIEW3|TABLE3 |USER |TABLE3 | |0 |
|USER_VIEW3|TABLE7 |USER |TABLE7 | |0 |
|USER_VIEW3|TABLE2|USER |TABLE2| |0 |
|USER_VIEW3|TABLE6 |USER |TABLE6 | |0 |
|USER_VIEW3|TABLE1 |USER |TABLE1 | |0 |
|USER_VIEW3|TABLE4 |USER |TABLE4 | |0 |
|USER_VIEW4|TABLE6 |USER |TABLE6 | |0 |
|USER_VIEW4|TABLE1 |USER |TABLE1 | |0 |
|USER_VIEW4|TABLE4 |USER |TABLE4 | |0 |
|USER_VIEW4|TABLE5 |USER |TABLE5 | |0 |
|USER_VIEW4|TABLE7 |USER |TABLE7 | |0 |
|USER_VIEW4|TABLE2|USER |TABLE2| |0 |
|USER_VIEW4|TABLE3 |USER |TABLE3 | |0 |
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');
|LAST_ANALYZED |NUM_ROWS |TABLESPACE_NAME|TABLE_NAME |TABLESPACE_NAME|
|-----------------------|---------|---------------|----------------------|---------------|
|2023-06-20 22:04:01.000|16666006 |FDC_TBS |TABLE6 |FDC_TBS |
|2023-06-18 18:10:41.000|344764680|FDC_TBS |TABLE2|FDC_TBS |
|2023-06-16 22:09:09.000|9869239 |FDC_TBS |TABLE5 |FDC_TBS |
|2023-07-21 22:04:13.000|15422627 |FDC_TBS |TABLE7 |FDC_TBS |
|2023-08-02 22:13:05.000|312623234|FT_TBS |TABLE1 |FT_TBS |
|2023-06-14 22:05:23.000|6012594 |SORT_TBS |TABLE4 |SORT_TBS |
|2023-07-10 22:07:43.000|112458880|WAT_TBS |TABLE3 |WAT_TBS |
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');
|TABLESPACE_NAME|STATUS|INDEX_NAME |INDEX_TYPE|TABLE_OWNER|TABLE_NAME |
|---------------|------|-------------------------|----------|-----------|----------------------|
|FDC_TBS |VALID |PK_CHAMBER_DATA |NORMAL |USER |TABLE6 |
|FDC_TBS |VALID |PK_CHAMBER_DATA02 |NORMAL |USER |TABLE6 |
|FDC_TBS |VALID |TABLE2_UK|NORMAL |USER |TABLE2|
|FDC_TBS |VALID |TABLE5_UK |NORMAL |USER |TABLE5 |
|FDC_TBS |VALID |TABLE5_UK01 |NORMAL |USER |TABLE5 |
|FDC_TBS |VALID |TABLE5_UK02 |NORMAL |USER |TABLE5 |
|FDC_TBS |VALID |TABLE7_UK |NORMAL |USER |TABLE7 |
|FDC_TBS |VALID |TABLE7_UK01 |NORMAL |USER |TABLE7 |
|FT_TBS |VALID |UK_TABLE1_TEST |NORMAL |USER |TABLE1 |
|SORT_TBS |VALID |TABLE4_INDE2 |NORMAL |USER |TABLE4 |
|SORT_TBS |VALID |SYS_IL0000123198C00028$$ |LOB |USER |TABLE4 |
|SORT_TBS |VALID |SYS_IL0000123198C00027$$ |LOB |USER |TABLE4 |
|SORT_TBS |VALID |TABLE4_INDEX1 |NORMAL |USER |TABLE4 |
|WAT_TBS |VALID |UK_TABLE3 |NORMAL |USER |TABLE3 |
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');
|INDEX_TYPE|INDEX_NAME |TABLE_NAME |COLUMN_NAME |
|----------|-------------------------|----------------------|--------------|
|NORMAL |PK_CHAMBER_DATA02 |TABLE6 |EQUIPMENT |
|NORMAL |PK_CHAMBER_DATA02 |TABLE6 |STAGE |
|NORMAL |PK_CHAMBER_DATA02 |TABLE6 |LOT_NAME |
|NORMAL |TABLE2_UK|TABLE2|RUN_INFO_KEY |
|NORMAL |TABLE2_UK|TABLE2|INDICATOR_NAME|
|NORMAL |TABLE5_UK |TABLE5 |STARTTIME |
|NORMAL |TABLE5_UK |TABLE5 |ENDTIME |
|NORMAL |TABLE5_UK |TABLE5 |EQUIPMENT |
|NORMAL |TABLE5_UK |TABLE5 |CHAMBER |
|NORMAL |TABLE5_UK |TABLE5 |RECIPE |
|NORMAL |TABLE5_UK01 |TABLE5 |RUN_INFO_KEY |
|NORMAL |TABLE5_UK01 |TABLE5 |MATERIAL_NAME |
|NORMAL |TABLE5_UK02 |TABLE5 |EQUIPMENT_TYPE|
|NORMAL |TABLE7_UK |TABLE7 |RUN_INFO_KEY |
|NORMAL |TABLE7_UK |TABLE7 |WAFER_NAME |
|NORMAL |TABLE7_UK01 |TABLE7 |LOT_NAME |
|NORMAL |TABLE7_UK01 |TABLE7 |WAFER_NAME |
|NORMAL |UK_TABLE1_TEST |TABLE1 |PRODUCT |
|NORMAL |UK_TABLE1_TEST |TABLE1 |LOT |
|NORMAL |UK_TABLE1_TEST |TABLE1 |PACKAGE_LOT |
|NORMAL |UK_TABLE1_TEST |TABLE1 |TEST_LOT |
|NORMAL |UK_TABLE1_TEST |TABLE1 |MEAS_TIME |
|NORMAL |UK_TABLE1_TEST |TABLE1 |PART_ID |
|NORMAL |UK_TABLE1_TEST |TABLE1 |SITE_NUM |
|NORMAL |UK_TABLE1_TEST |TABLE1 |PARAMETER |
|NORMAL |UK_TABLE1_TEST |TABLE1 |PROGRAM |
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');
|OWNER|CONSTRAINT_NAME|CONSTRAINT_TYPE|TABLE_NAME |SEARCH_CONDITION |SEARCH_CONDITION_VC |
|-----|---------------|---------------|----------------------|----------------------------|----------------------------|
|USER |SYS_C009994 |C |TABLE3 |"PARAMETER" IS NOT NULL |"PARAMETER" IS NOT NULL |
|USER |SYS_C009995 |C |TABLE3 |"SHOT_ID" IS NOT NULL |"SHOT_ID" IS NOT NULL |
|USER |SYS_C009996 |C |TABLE3 |"SHOT_X" IS NOT NULL |"SHOT_X" IS NOT NULL |
|USER |SYS_C009997 |C |TABLE3 |"SHOT_Y" IS NOT NULL |"SHOT_Y" IS NOT NULL |
|USER |SYS_C009998 |C |TABLE3 |"WAT_WAFER_KEY" IS NOT NULL |"WAT_WAFER_KEY" IS NOT NULL |
|USER |SYS_C0054079 |C |TABLE5 |"RUN_INFO_KEY" IS NOT NULL |"RUN_INFO_KEY" IS NOT NULL |
|USER |SYS_C0054080 |C |TABLE5 |"STARTTIME" IS NOT NULL |"STARTTIME" IS NOT NULL |
|USER |SYS_C0054081 |C |TABLE5 |"ENDTIME" IS NOT NULL |"ENDTIME" IS NOT NULL |
|USER |SYS_C0054082 |C |TABLE5 |"EQUIPMENT_TYPE" IS NOT NULL|"EQUIPMENT_TYPE" IS NOT NULL|
|USER |SYS_C0054093 |C |TABLE7 |"UPDATE_TIME" IS NOT NULL |"UPDATE_TIME" IS NOT NULL |
|USER |SYS_C0054094 |C |TABLE2|"RUN_INFO_KEY" IS NOT NULL |"RUN_INFO_KEY" IS NOT NULL |
|USER |SYS_C0054095 |C |TABLE2|"INDICATOR_NAME" IS NOT NULL|"INDICATOR_NAME" IS NOT NULL|
|USER |SYS_C0054096 |C |TABLE2|"UPDATE_TIME" IS NOT NULL |"UPDATE_TIME" IS NOT NULL |
|USER |SYS_C0054083 |C |TABLE5 |"EQUIPMENT" IS NOT NULL |"EQUIPMENT" IS NOT NULL |
|USER |SYS_C0054084 |C |TABLE5 |"CHAMBER" IS NOT NULL |"CHAMBER" IS NOT NULL |
|USER |SYS_C0054085 |C |TABLE5 |"RECIPE" IS NOT NULL |"RECIPE" IS NOT NULL |
|USER |SYS_C0054086 |C |TABLE5 |"PROGCLASS" IS NOT NULL |"PROGCLASS" IS NOT NULL |
|USER |SYS_C0054087 |C |TABLE5 |"PROGGROUP" IS NOT NULL |"PROGGROUP" IS NOT NULL |
|USER |SYS_C0054088 |C |TABLE5 |"PROGNAME" IS NOT NULL |"PROGNAME" IS NOT NULL |
|USER |SYS_C0054089 |C |TABLE5 |"STRATEGY" IS NOT NULL |"STRATEGY" IS NOT NULL |
|USER |SYS_C0054090 |C |TABLE5 |"STRATEGYUUID" IS NOT NULL |"STRATEGYUUID" IS NOT NULL |
|USER |SYS_C0054091 |C |TABLE5 |"UPDATE_TIME" IS NOT NULL |"UPDATE_TIME" IS NOT NULL |
|USER |SYS_C0054092 |C |TABLE7 |"RUN_INFO_KEY" IS NOT NULL |"RUN_INFO_KEY" IS NOT NULL |
|USER |SYS_C00225234 |C |TABLE1 |"FAB" IS NOT NULL |"FAB" IS NOT NULL |
|USER |SYS_C00225235 |C |TABLE1 |"PRODUCT" IS NOT NULL |"PRODUCT" IS NOT NULL |
|USER |SYS_C00225236 |C |TABLE1 |"LOT" IS NOT NULL |"LOT" IS NOT NULL |
|USER |SYS_C00225237 |C |TABLE1 |"PACKAGE_LOT" IS NOT NULL |"PACKAGE_LOT" IS NOT NULL |
|USER |SYS_C00225238 |C |TABLE1 |"TEST_LOT" IS NOT NULL |"TEST_LOT" IS NOT NULL |
|USER |SYS_C00225239 |C |TABLE1 |"MEAS_TIME" IS NOT NULL |"MEAS_TIME" IS NOT NULL |
|USER |SYS_C00225240 |C |TABLE1 |"PART_ID" IS NOT NULL |"PART_ID" IS NOT NULL |
|USER |SYS_C00225241 |C |TABLE1 |"SITE_NUM" IS NOT NULL |"SITE_NUM" IS NOT NULL |
|USER |SYS_C00225242 |C |TABLE1 |"PARAMETER" IS NOT NULL |"PARAMETER" IS NOT NULL |
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$$');
|TABLE_NAME |COLUMN_NAME |SEGMENT_NAME |TABLESPACE_NAME|INDEX_NAME |
|--------------|----------------|-------------------------|---------------|------------------------|
|TABLE4|INITIAL_MAP_DATA|SYS_LOB0000123198C00027$$|SORT_TBS |SYS_IL0000123198C00027$$|
|TABLE4|MAP_DATA |SYS_LOB0000123198C00028$$|SORT_TBS |SYS_IL0000123198C00028$$|
其他：匯出作業異常處理。
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")
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;
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;
rename TABLE1_PART2 to TABLE1;
alter index TABLE1_UK_PART2 rename to TABLE1_UK;
索引正常，不需要重建。
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
索引：TABLE1_UK01
索引：TABLE1_UK02
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,
"STARTTIME" TIMESTAMP (6) NOT NULL ENABLE,
"ENDTIME" TIMESTAMP (6) NOT NULL ENABLE,
"EQUIPMENT_TYPE" VARCHAR2(256) NOT NULL ENABLE,
"EQUIPMENT" VARCHAR2(256) NOT NULL ENABLE,
"CHAMBER" VARCHAR2(256) NOT NULL ENABLE,
"RECIPE" VARCHAR2(256) NOT NULL ENABLE,
"PHYSICAL_RECIPE" VARCHAR2(256),
"LOGICAL_RECIPE" VARCHAR2(256),
"PROGCLASS" VARCHAR2(256) NOT NULL ENABLE,
"PROGGROUP" VARCHAR2(256) NOT NULL ENABLE,
"PROGNAME" VARCHAR2(256) NOT NULL ENABLE,
"STRATEGY" VARCHAR2(256) NOT NULL ENABLE,
"STRATEGYUUID" VARCHAR2(256) NOT NULL ENABLE,
"MODEL" VARCHAR2(256),
"CONTROLJOBID" VARCHAR2(256),
"PROCESSJOBID" VARCHAR2(256),
"PROCESSJOBNAME" VARCHAR2(256),
"UPDATE_TIME" TIMESTAMP (6) NOT NULL ENABLE,
"MATERIAL_NAME" VARCHAR2(256)
) 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_UK02','USER') from dual;
CREATE UNIQUE INDEX "USER"."TABLE1_UK" ON "USER"."TABLE1" ("STARTTIME", "ENDTIME", "EQUIPMENT", "CHAMBER", "RECIPE")
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" ("RUN_INFO_KEY", "MATERIAL_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"
CREATE INDEX "USER"."TABLE1_UK02" ON "USER"."TABLE1" ("EQUIPMENT_TYPE")
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; --13138977
--用表總資料量除以id最大值，預估分割槽間隔。=10839465/13138977 =0.8 每個id，
--間隔2000000一個分割槽，約200w行資料。
CREATE TABLE "USER"."TABLE1_PART2"
( "RUN_INFO_KEY" NUMBER NOT NULL ENABLE,
"STARTTIME" TIMESTAMP (6) NOT NULL ENABLE,
"ENDTIME" TIMESTAMP (6) NOT NULL ENABLE,
"EQUIPMENT_TYPE" VARCHAR2(256) NOT NULL ENABLE,
"EQUIPMENT" VARCHAR2(256) NOT NULL ENABLE,
"CHAMBER" VARCHAR2(256) NOT NULL ENABLE,
"RECIPE" VARCHAR2(256) NOT NULL ENABLE,
"PHYSICAL_RECIPE" VARCHAR2(256),
"LOGICAL_RECIPE" VARCHAR2(256),
"PROGCLASS" VARCHAR2(256) NOT NULL ENABLE,
"PROGGROUP" VARCHAR2(256) NOT NULL ENABLE,
"PROGNAME" VARCHAR2(256) NOT NULL ENABLE,
"STRATEGY" VARCHAR2(256) NOT NULL ENABLE,
"STRATEGYUUID" VARCHAR2(256) NOT NULL ENABLE,
"MODEL" VARCHAR2(256),
"CONTROLJOBID" VARCHAR2(256),
"PROCESSJOBID" VARCHAR2(256),
"PROCESSJOBNAME" VARCHAR2(256),
"UPDATE_TIME" TIMESTAMP (6) NOT NULL ENABLE,
"MATERIAL_NAME" VARCHAR2(256)
) 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 新表索引建立語句。注意表名、索引名、本地索引
----唯 一 索引必須包含分割槽鍵，該表無法切換為分割槽表。
解決方法：
原表刪除 TABLE1_UK 索引
配置好分割槽交換後
原表是否可以建立TABLE1_UK索引，但是必須包含 ("RUN_INFO_KEY","STARTTIME", "ENDTIME", "EQUIPMENT", "CHAMBER", "RECIPE")
需業務上確認是否支援。
drop index "USER"."TABLE1_UK";
# CREATE UNIQUE INDEX "USER"."TABLE1_UK_PART2" ON "USER"."TABLE1_PART2" ("STARTTIME", "ENDTIME", "EQUIPMENT", "CHAMBER", "RECIPE")
# 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" ("RUN_INFO_KEY", "MATERIAL_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;
CREATE INDEX "USER"."TABLE1_UK02_PART2" ON "USER"."TABLE1_PART2" ("EQUIPMENT_TYPE")
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;
表：TABLE1
索引：TABLE1_UK
索引：TABLE1_UK01
索引：TABLE1_UK02
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;
alter index TABLE1_UK02 rename to TABLE1_UK02_OLD;
rename TABLE1_PART2 to TABLE1;
--alter index TABLE1_UK_PART2 rename to TABLE1_UK;
alter index TABLE1_UK01_PART2 rename to TABLE1_UK01;
alter index TABLE1_UK02_PART2 rename to TABLE1_UK02;
CREATE UNIQUE INDEX "USER"."TABLE1_UK" ON "USER"."TABLE1" ("RUN_INFO_KEY","STARTTIME", "ENDTIME", "EQUIPMENT", "CHAMBER", "RECIPE")
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;
索引正常，不需要重建。
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
索引：UK_TABLE1_TEST
set long 10000 pages 10000
select dbms_metadata.get_ddl('TABLE','TABLE1','USER') from dual;
CREATE TABLE "USER"."TABLE1"
( "FAB" VARCHAR2(12) NOT NULL ENABLE,
"PRODUCT" VARCHAR2(40) NOT NULL ENABLE,
"LOT" VARCHAR2(40) NOT NULL ENABLE,
"PACKAGE_LOT" VARCHAR2(12) NOT NULL ENABLE,
"TEST_LOT" VARCHAR2(60) NOT NULL ENABLE,
"FT_TYPE" VARCHAR2(10),
"TESTER_ID" VARCHAR2(12),
"BEGINING_TIME" TIMESTAMP (6),
"MEAS_TIME" TIMESTAMP (6) NOT NULL ENABLE,
"BIN_NAME" VARCHAR2(3),
"PASS_FLAG" VARCHAR2(12),
"PART_ID" VARCHAR2(30) NOT NULL ENABLE,
"SITE_NUM" VARCHAR2(3) NOT NULL ENABLE,
"PARAMETER" VARCHAR2(60) NOT NULL ENABLE,
"VALUE" FLOAT(126),
"UPDATE_TIME" TIMESTAMP (6),
"DEVICE_NAME" VARCHAR2(40),
"PROGRAM" VARCHAR2(100),
"FT_CATE" VARCHAR2(10),
"UNIT" VARCHAR2(10),
"LIMITL" NUMBER,
"LIMITU" NUMBER,
"HANDLER" VARCHAR2(40)
) 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 "FT_TBS"
3.2 原表索引建立語句。
set long 10000 pages 10000
select dbms_metadata.get_ddl('INDEX','UK_TABLE1_TEST','USER') from dual;
CREATE UNIQUE INDEX "USER"."UK_TABLE1_TEST" ON "USER"."TABLE1" ("PRODUCT", "LOT", "PACKAGE_LOT", "TEST_LOT", "MEAS_TIME", "PART_ID", "SITE_NUM", "PARAMETER", "PROGRAM")
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 "FT_TBS"
3.3 新表建立語句。注意表名
CREATE TABLE "USER"."TABLE1_part2"
( "FAB" VARCHAR2(12) NOT NULL ENABLE,
"PRODUCT" VARCHAR2(40) NOT NULL ENABLE,
"LOT" VARCHAR2(40) NOT NULL ENABLE,
"PACKAGE_LOT" VARCHAR2(12) NOT NULL ENABLE,
"TEST_LOT" VARCHAR2(60) NOT NULL ENABLE,
"FT_TYPE" VARCHAR2(10),
"TESTER_ID" VARCHAR2(12),
"BEGINING_TIME" TIMESTAMP (6),
"MEAS_TIME" TIMESTAMP (6) NOT NULL ENABLE,
"BIN_NAME" VARCHAR2(3),
"PASS_FLAG" VARCHAR2(12),
"PART_ID" VARCHAR2(30) NOT NULL ENABLE,
"SITE_NUM" VARCHAR2(3) NOT NULL ENABLE,
"PARAMETER" VARCHAR2(60) NOT NULL ENABLE,
"VALUE" FLOAT(126),
"UPDATE_TIME" TIMESTAMP (6),
"DEVICE_NAME" VARCHAR2(40),
"PROGRAM" VARCHAR2(100),
"FT_CATE" VARCHAR2(10),
"UNIT" VARCHAR2(10),
"LIMITL" NUMBER,
"LIMITU" NUMBER,
"HANDLER" VARCHAR2(40)
) 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 "FT_TBS"
partition by range("MEAS_TIME") 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 "FT_TBS"
)
3.4 新表索引建立語句。注意表名、索引名、本地索引
CREATE UNIQUE INDEX "USER"."UK_TABLE1_TEST_par2" ON "USER"."TABLE1_part2" ("PRODUCT", "LOT", "PACKAGE_LOT", "TEST_LOT", "MEAS_TIME", "PART_ID", "SITE_NUM", "PARAMETER", "PROGRAM")
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 "FT_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;>
### ************************
alter table USER.TABLE1_part2 exchange partition P1 with table USER.TABLE1 including indexes without validation;
瞬間執行成功。原表表 TABLE1 還在，空的。
5、重新命名錶名：
rename TABLE1 to TABLE1_OLD;
alter index UK_TABLE1_TEST rename to UK_TABLE1_TEST_OLD;
rename TABLE1_part2 to TABLE1;
alter index UK_TABLE1_TEST_par2 rename to UK_TABLE1_TEST;
索引正常，不需要重建。
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 原表建立語句。
表：TABLE2
索引：TABLE2_INDE2
索引：TABLE2_INDEX1
set long 10000 pages 10000
select dbms_metadata.get_ddl('TABLE','TABLE2','USER') from dual;
CREATE TABLE "USER"."TABLE2"
( "SORT_WAFER_KEY" NUMBER,
"FAB" VARCHAR2(12),
"PRODUCT" VARCHAR2(40),
"LOT" VARCHAR2(40),
"WAFER" VARCHAR2(40),
"WAFER_NO" VARCHAR2(2),
"SORT_TYPE" VARCHAR2(20),
"PARAMETER" VARCHAR2(70),
"MEAS_TIME" DATE,
"START_MEAS_TIME" DATE,
"END_MEAS_TIME" DATE,
"SORT_TEST_PROG" VARCHAR2(60),
"REWORK_COUNT" NUMBER,
"LATEST_FLAG" CHAR(1),
"GROSS_DIE_COUNT" NUMBER,
"TOTAL_DIE_COUNT" NUMBER,
"GOOD_DIE_COUNT" NUMBER,
"YIELD" FLOAT(126),
"MEAS_EQP" VARCHAR2(40),
"PROBE_CARD" VARCHAR2(30),
"TEST_OPERATOR" VARCHAR2(20),
"DIE_MAX_X" NUMBER(3,0),
"DIE_MIN_X" NUMBER(3,0),
"DIE_MAX_Y" NUMBER(3,0),
"DIE_MIN_Y" NUMBER(3,0),
"ORIGINAL_WAFER_NOTCH" CHAR(1),
"INITIAL_MAP_DATA" CLOB,
"MAP_DATA" CLOB,
"AVG_VALUE" FLOAT(126),
"STD_VALUE" FLOAT(126),
"MIN_VALUE" FLOAT(126),
"Q1_VALUE" FLOAT(126),
"MED_VALUE" FLOAT(126),
"Q3_VALUE" FLOAT(126),
"MAX_VALUE" FLOAT(126),
"USL" FLOAT(126),
"LSL" FLOAT(126),
"UPDATE_TIME" DATE,
"P1" FLOAT(126),
"P5" FLOAT(126),
"P10" FLOAT(126),
"P50" FLOAT(126),
"P90" FLOAT(126),
"P95" FLOAT(126),
"P99" FLOAT(126),
"CP_VERSION" VARCHAR2(60),
"CHECKSUM" VARCHAR2(40)
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS NOLOGGING
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 "SORT_TBS"
LOB ("INITIAL_MAP_DATA") STORE AS BASICFILE "SYS_LOB0000123198C00027$$"(
TABLESPACE "SORT_TBS" ENABLE STORAGE IN ROW CHUNK 8192 RETENTION
NOCACHE 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))
LOB ("MAP_DATA") STORE AS BASICFILE (
TABLESPACE "SORT_TBS" ENABLE STORAGE IN ROW CHUNK 8192 RETENTION
NOCACHE 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))
3.2 原表索引建立語句。
set long 10000 pages 10000
select dbms_metadata.get_ddl('INDEX','TABLE2_INDEX1','USER') from dual;
CREATE INDEX "USER"."TABLE2_INDE2" ON "USER"."TABLE2" ("UPDATE_T
IME", "LOT", "WAFER", "SORT_TYPE", "PARAMETER")
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 "SORT_TBS"
CREATE INDEX "USER"."TABLE2_INDEX1" ON "USER"."TABLE2" ("LOT", "
WAFER", "SORT_TYPE", "MEAS_TIME", "PARAMETER")
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 "SORT_TBS"
3.3 新表建立語句。注意表名、表空間
CREATE TABLE "USER"."TABLE2_PART2"
( "SORT_WAFER_KEY" NUMBER,
"FAB" VARCHAR2(12),
"PRODUCT" VARCHAR2(40),
"LOT" VARCHAR2(40),
"WAFER" VARCHAR2(40),
"WAFER_NO" VARCHAR2(2),
"SORT_TYPE" VARCHAR2(20),
"PARAMETER" VARCHAR2(70),
"MEAS_TIME" DATE,
"START_MEAS_TIME" DATE,
"END_MEAS_TIME" DATE,
"SORT_TEST_PROG" VARCHAR2(60),
"REWORK_COUNT" NUMBER,
"LATEST_FLAG" CHAR(1),
"GROSS_DIE_COUNT" NUMBER,
"TOTAL_DIE_COUNT" NUMBER,
"GOOD_DIE_COUNT" NUMBER,
"YIELD" FLOAT(126),
"MEAS_EQP" VARCHAR2(40),
"PROBE_CARD" VARCHAR2(30),
"TEST_OPERATOR" VARCHAR2(20),
"DIE_MAX_X" NUMBER(3,0),
"DIE_MIN_X" NUMBER(3,0),
"DIE_MAX_Y" NUMBER(3,0),
"DIE_MIN_Y" NUMBER(3,0),
"ORIGINAL_WAFER_NOTCH" CHAR(1),
"INITIAL_MAP_DATA" CLOB,
"MAP_DATA" CLOB,
"AVG_VALUE" FLOAT(126),
"STD_VALUE" FLOAT(126),
"MIN_VALUE" FLOAT(126),
"Q1_VALUE" FLOAT(126),
"MED_VALUE" FLOAT(126),
"Q3_VALUE" FLOAT(126),
"MAX_VALUE" FLOAT(126),
"USL" FLOAT(126),
"LSL" FLOAT(126),
"UPDATE_TIME" DATE,
"P1" FLOAT(126),
"P5" FLOAT(126),
"P10" FLOAT(126),
"P50" FLOAT(126),
"P90" FLOAT(126),
"P95" FLOAT(126),
"P99" FLOAT(126),
"CP_VERSION" VARCHAR2(60),
"CHECKSUM" VARCHAR2(40)
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS NOLOGGING
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 "SORT_TBS"
LOB ("INITIAL_MAP_DATA") STORE AS BASICFILE "SYS_LOB0000123198C00027$$"(
TABLESPACE "SORT_TBS" ENABLE STORAGE IN ROW CHUNK 8192 RETENTION
NOCACHE 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))
LOB ("MAP_DATA") STORE AS BASICFILE (
TABLESPACE "SORT_TBS" ENABLE STORAGE IN ROW CHUNK 8192 RETENTION
NOCACHE 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))
partition by range("MEAS_TIME") 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 "SORT_TBS"
)
3.4 新表索引建立語句。注意表名、索引名、本地索引、表空間
CREATE INDEX "USER"."TABLE2_INDE2_PART2" ON "USER"."TABLE2_PART2" ("UPDATE_TIME", "LOT", "WAFER", "SORT_TYPE", "PARAMETER")
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 "SORT_TBS"
local;
CREATE INDEX "USER"."TABLE2_INDEX1_PART2" ON "USER"."TABLE2_PART2" ("LOT", "WAFER", "SORT_TYPE", "MEAS_TIME", "PARAMETER")
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 "SORT_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;>
### ************************
表：TABLE2
索引：TABLE2_INDE2
索引：TABLE2_INDEX1
alter table USER.TABLE2_PART2 exchange partition P1 with table USER.TABLE2 including indexes without validation;
瞬間執行成功。原表表 FT_FAIL_RAW 還在，空的。
5、重新命名錶名：
rename TABLE2 to TABLE2_OLD;
alter index TABLE2_INDE2 rename to TABLE2_INDE2_OLD;
alter index TABLE2_INDEX1 rename to TABLE2_INDEX1_OLD;
rename TABLE2_PART2 to TABLE2;
alter index TABLE2_INDE2_PART2 rename to TABLE2_INDE2;
alter index TABLE2_INDEX1_PART2 rename to TABLE2_INDEX1;
索引正常，不需要重建。
6、收集統計資訊：
exec dbms_stats.gather_table_stats('USER','TABLE2',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 原表建立語句。
表：TABLE3
索引：UK_TABLE3
set long 10000 pages 10000
select dbms_metadata.get_ddl('TABLE','TABLE3','USER') from dual;
CREATE TABLE "USER"."TABLE3"
( "WAT_WAFER_KEY" NUMBER NOT NULL ENABLE,
"PARAMETER" VARCHAR2(60) NOT NULL ENABLE,
"SHOT_ID" NUMBER NOT NULL ENABLE,
"WAT_SITE_NAME" VARCHAR2(10),
"SHOT_X" NUMBER NOT NULL ENABLE,
"SHOT_Y" NUMBER NOT NULL ENABLE,
"SHOT_VALUE" FLOAT(126),
"UPDATE_TIME" DATE DEFAULT SYSDATE
) 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 "WAT_TBS"
3.2 原表索引建立語句。
set long 10000 pages 10000
select dbms_metadata.get_ddl('INDEX','UK_TABLE3','USER') from dual;
CREATE UNIQUE INDEX "USER"."UK_TABLE3" ON "USER"."TABLE3" ("WAT_WAFER_KEY","PARAMETER", "SHOT_ID")
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 "WAT_TBS"
3.3 新表建立語句。注意表名
分割槽鍵型別為number id
SELECT max(WAT_WAFER_KEY) FROM TABLE3; --292980
--用表總資料量除以id最大值，預估分割槽間隔。=113951203/292980 =389 每個id，
--間隔5000一個分割槽，約200w行資料。
CREATE TABLE "USER"."TABLE3_PART2"
( "WAT_WAFER_KEY" NUMBER NOT NULL ENABLE,
"PARAMETER" VARCHAR2(60) NOT NULL ENABLE,
"SHOT_ID" NUMBER NOT NULL ENABLE,
"WAT_SITE_NAME" VARCHAR2(10),
"SHOT_X" NUMBER NOT NULL ENABLE,
"SHOT_Y" NUMBER NOT NULL ENABLE,
"SHOT_VALUE" FLOAT(126),
"UPDATE_TIME" DATE DEFAULT SYSDATE
) 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 "WAT_TBS"
partition by range("WAT_WAFER_KEY")
( partition "P1" values less than(295000) TABLESPACE "WAT_TBS" ,
partition "P2" values less than(300000) TABLESPACE "WAT_TBS" ,
partition "P3" values less than(305000) TABLESPACE "WAT_TBS" ,
partition "P4" values less than(310000) TABLESPACE "WAT_TBS" ,
partition "P5" values less than(315000) TABLESPACE "WAT_TBS" ,
partition "P6" values less than(320000) TABLESPACE "WAT_TBS" ,
partition "P7" values less than(325000) TABLESPACE "WAT_TBS" ,
partition "P8" values less than(330000) TABLESPACE "WAT_TBS" ,
partition "P9" values less than(335000) TABLESPACE "WAT_TBS" ,
partition "P10" values less than(340000) TABLESPACE "WAT_TBS" ,
partition "P11" values less than(345000) TABLESPACE "WAT_TBS" ,
partition "P12" values less than(350000) TABLESPACE "WAT_TBS" ,
partition "P13" values less than(355000) TABLESPACE "WAT_TBS" ,
partition "P14" values less than(360000) TABLESPACE "WAT_TBS" ,
partition "P15" values less than(365000) TABLESPACE "WAT_TBS" ,
partition "P16" values less than(370000) TABLESPACE "WAT_TBS" ,
partition "P17" values less than(375000) TABLESPACE "WAT_TBS" ,
partition "P18" values less than(380000) TABLESPACE "WAT_TBS" ,
partition "P19" values less than(385000) TABLESPACE "WAT_TBS" ,
partition "P20" values less than(390000) TABLESPACE "WAT_TBS" ,
partition "P21" values less than(395000) TABLESPACE "WAT_TBS" ,
partition "P22" values less than(400000) TABLESPACE "WAT_TBS" ,
partition "P23" values less than(405000) TABLESPACE "WAT_TBS" ,
partition "P24" values less than(410000) TABLESPACE "WAT_TBS" ,
partition "P25" values less than(415000) TABLESPACE "WAT_TBS" ,
partition "P26" values less than(420000) TABLESPACE "WAT_TBS" ,
partition "P27" values less than(425000) TABLESPACE "WAT_TBS" ,
partition "P28" values less than(430000) TABLESPACE "WAT_TBS" ,
partition "P29" values less than(435000) TABLESPACE "WAT_TBS" ,
partition "P30" values less than(440000) TABLESPACE "WAT_TBS" ,
partition "P31" values less than(445000) TABLESPACE "WAT_TBS" ,
partition "P32" values less than(450000) TABLESPACE "WAT_TBS" ,
partition "P33" values less than(455000) TABLESPACE "WAT_TBS" ,
partition "P34" values less than(460000) TABLESPACE "WAT_TBS" ,
partition "P35" values less than(465000) TABLESPACE "WAT_TBS" ,
partition "P36" values less than(470000) TABLESPACE "WAT_TBS" ,
PARTITION "PMAX" VALUES LESS THAN (MAXVALUE) TABLESPACE "WAT_TBS" );
3.4 新表索引建立語句。注意表名、索引名、本地索引
CREATE UNIQUE INDEX "USER"."UK_TABLE3_PART2" ON "USER"."TABLE3_PART2" ("WAT_WAFER_KEY","PARAMETER", "SHOT_ID")
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 "WAT_TBS"
local;
表：TABLE3
索引：UK_TABLE3
4、交換分割槽
alter table USER.TABLE3_PART2 exchange partition P1 with table USER.TABLE3 including indexes without validation;
5、重新命名錶名：
rename TABLE3 to TABLE3_OLD;
alter index UK_TABLE3 rename to UK_TABLE3_OLD;
rename TABLE3_PART2 to TABLE3;
alter index UK_TABLE3_PART2 rename to UK_TABLE3;
索引正常，不需要重建。
6、收集統計資訊：
exec dbms_stats.gather_table_stats('USER','TABLE3',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;
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/70031606/viewspace-2990826/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- oracle分割槽表和分割槽表exchangeOracle
- oracle分割槽表和非分割槽表exchangeOracle
- Oracle 12.2之後ALTER TABLE .. MODIFY轉換非分割槽表為分割槽表Oracle
- oracle 分割槽表move和包含分割槽表的lob moveOracle
- oracle 普通表-分割槽表改造流程Oracle
- [oracle] expdp 匯出分割槽表的分割槽Oracle
- ORACLE分割槽表梳理系列Oracle
- Oracle分割槽表基礎運維-04列表分割槽Oracle運維
- Oracle分割槽表基礎運維-07增加分割槽(3列表分割槽)Oracle運維
- Oracle分割槽表基礎運維-06分割槽表索引Oracle運維索引
- Oracle分割槽表基礎運維-01分割槽表分類Oracle運維
- oracle 線上重新定義，普通表改變分割槽表，分割槽表可以更改型別、分割槽欄位等Oracle型別
- 線上重定義方式將普通表修改為分割槽表
- Oracle分割槽表基礎運維-09刪除分割槽Oracle運維
- Oracle分割槽表基礎運維-05組合分割槽Oracle運維
- Oracle分割槽表基礎運維-02範圍分割槽Oracle運維
- Oracle分割槽表基礎運維-03HASH分割槽Oracle運維
- 對oracle分割槽表的理解整理Oracle
- Oracle SQL調優之分割槽表OracleSQL
- Oracle分割槽表基礎運維-07增加分割槽(2 HASH分割槽)Oracle運維
- Oracle 12C新特性-線上把非分割槽錶轉為分割槽表Oracle
- Oracle分割槽表基礎運維-07增加分割槽(1範圍分割槽)Oracle運維
- ORACLE刪除-表分割槽和資料Oracle
- 增加表分割槽時，為local分割槽索引指定不同表空間的方法索引
- PG的非分割槽表線上轉分割槽表
- LightDB 22.4 新特性之相容Oracle線上轉換普通表為分割槽表Oracle
- 【MYSQL】 分割槽表MySql
- 移動分割槽表和分割槽索引的表空間索引
- oracle分割槽表的分類及測試Oracle
- oracle 19C新特性——混合分割槽表Oracle
- oracle 更改分割槽表資料 ora-14402Oracle
- PostgreSQL/LightDB 分割槽表之分割槽裁剪SQL
- Oracle分割槽表基礎運維-07增加分割槽(4 RANGE_HASH)Oracle運維
- Oracle分割槽表基礎運維-07增加分割槽(5RANGE_LIST)Oracle運維
- Oracle分割槽表基礎運維-07增加分割槽(6RANGE_RANGE)Oracle運維
- Oracle drop分割槽表單個分割槽無法透過閃回恢復Oracle
- MySQL 分割槽表探索MySql
- 分割槽表-實戰