oracle将表配置为分区表

来源：这里教程网时间：2026-03-03 19:01:50 作者：

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;

编辑推荐：

返回列表

oracle将表配置为分区表

编辑推荐：

相关推荐

excel表格怎样在数字前输入0

excel表格里怎么替换数据

excel表格里面如何插入圆形

excel表格里怎么添加表格数据透视表

excel表格中怎么添加线边框

excel表格里怎样设置条件自动排序

excel表格例怎样导入数据库

Excel表格如何设置数据排序

excel表格里怎么选择性粘贴快捷键

excel表格怎么插入分隔符号

excel表格怎么设置多行多列数据内容排序

Excel表格如何设置行和列相互转换

excel表格连接公式怎么使用

excel表格怎样链接到cad中

雷神推出 MIX PRO II 迷你主机：基于 Ultra 200H，玻璃上盖 + ARGB 灯效

制造商 Musnap 推出彩色墨水屏电纸书 Ocean C：支持手写笔、第三方安卓应用

最新软件资讯

热文推荐

天极热推