对TMS.SD_CURRENT_PALLET_DAILY_LOG做分区表
1. 做分区之前的表的统计信息
/* Formatted on 2016/10/22 11:17:22 (QP5 v5.115.810.9015) */
SELECT OWNER, SEGMENT_NAME,TABLESPACE_NAME,SEGMENT_TYPE,BYTES / 1024 / 1024 / 1024 GB
FROM dba_segments
WHERE segment_name = 'SD_CURRENT_PALLET_DAILY_LOG'
OR SEGMENT_NAME = 'IDX_PALLET_LOG';
目前TABLESPACE使用情况：
select d.status, d.tablespace_name,
TO_CHAR(NVL(a.bytes / 1024 /1024, 0),'99G999G990') "size(M)",
TO_CHAR(NVL(a.bytes - NVL(f.bytes, 0),0)/1024/1024, '99G999G990D00') used,
TO_CHAR(NVL((a.bytes - NVL(f.bytes, 0)) / a.bytes * 100, 0), '990D00')||'%' pct
FROM sys.dba_tablespaces d,
(select tablespace_name, sum(bytes) bytes from dba_data_files group by
tablespace_name) a,
(select tablespace_name, sum(bytes) bytes from dba_free_space group by
tablespace_name) f
WHERE d.tablespace_name = a.tablespace_name(+)
AND d.tablespace_name = f.tablespace_name(+)
order by tablespace_name ;
输出如下：
2. Script
DROP TABLE TMS.SD_CURRENT_PALLET_DAILY_LOG CASCADE CONSTRAINTS;
CREATE TABLE TMS.SD_CURRENT_PALLET_DAILY_LOG
(
LOG_DATE DATE DEFAULT SYSDATE,
PALLET_ID VARCHAR2(30 BYTE) NOT NULL,
GRADE VARCHAR2(3 BYTE),
PLANT VARCHAR2(4 BYTE),
STLOC VARCHAR2(4 BYTE),
AREA VARCHAR2(10 BYTE),
BIN VARCHAR2(10 BYTE),
STATUS VARCHAR2(1 BYTE) DEFAULT 1,
PALLET_WEIGHT NUMBER(10,3),
RECEIPT_TIME DATE,
MVT VARCHAR2(7 BYTE),
WM_PALLET_ID VARCHAR2(30 BYTE),
MAT_DOC VARCHAR2(10 BYTE),
MES_TIME DATE,
CREATOR VARCHAR2(20 BYTE),
RECEIVETIME DATE,
PALLET_TYPE VARCHAR2(10 BYTE),
LOG_OPERATION_TIME DATE
)
TABLESPACE TMS_DATA
PCTUSED 0
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;
CREATE INDEX TMS.IDX_PALLET_LOG ON TMS.SD_CURRENT_PALLET_DAILY_LOG
(LOG_DATE)
LOGGING
TABLESPACE TMS_DATA
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
NOPARALLEL;
CREATE INDEX TMS.IDX_PALLET_LOG ON TMS.SD_CURRENT_PALLET_DAILY_LOG
(LOG_DATE)
LOGGING
TABLESPACE TMS_DATA
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
NOPARALLEL;
3. 新script
1.
CREATE TABLE TMS.SD_CURRENT_PALLET_DAILY_LOG_P
(
LOG_DATE DATE DEFAULT SYSDATE,
PALLET_ID VARCHAR2(30 BYTE) NOT NULL,
GRADE VARCHAR2(3 BYTE),
PLANT VARCHAR2(4 BYTE),
STLOC VARCHAR2(4 BYTE),
AREA VARCHAR2(10 BYTE),
BIN VARCHAR2(10 BYTE),
STATUS VARCHAR2(1 BYTE) DEFAULT 1,
PALLET_WEIGHT NUMBER(10,3),
RECEIPT_TIME DATE,
MVT VARCHAR2(7 BYTE),
WM_PALLET_ID VARCHAR2(30 BYTE),
MAT_DOC VARCHAR2(10 BYTE),
MES_TIME DATE,
CREATOR VARCHAR2(20 BYTE),
RECEIVETIME DATE,
PALLET_TYPE VARCHAR2(10 BYTE),
LOG_OPERATION_TIME DATE
)
TABLESPACE DWMDAT
PCTUSED 0
PCTFREE 0
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 10M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
LOGGING
PARTITION BY RANGE (LOG_DATE)
(
PARTITION P_20160829 VALUES LESS THAN (TO_DATE(' 2016-08-29 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
LOGGING
COMPRESS
TABLESPACE DWMDAT
PCTFREE 0
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 10M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
BUFFER_POOL DEFAULT
),
PARTITION P_20160905 VALUES LESS THAN (TO_DATE(' 2016-09-05 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
LOGGING
COMPRESS
TABLESPACE DWMDAT
PCTFREE 0
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 10M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
BUFFER_POOL DEFAULT
),
PARTITION P_20160912 VALUES LESS THAN (TO_DATE(' 2016-09-12 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
LOGGING
COMPRESS
TABLESPACE DWMDAT
PCTFREE 0
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 10M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
BUFFER_POOL DEFAULT
),
PARTITION P_20160919 VALUES LESS THAN (TO_DATE(' 2016-09-19 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
LOGGING
COMPRESS
TABLESPACE DWMDAT
PCTFREE 0
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 10M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
BUFFER_POOL DEFAULT
),
PARTITION P_20160926 VALUES LESS THAN (TO_DATE(' 2016-09-26 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
LOGGING
COMPRESS
TABLESPACE DWMDAT
PCTFREE 0
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 10M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
BUFFER_POOL DEFAULT
),
PARTITION P_20161003 VALUES LESS THAN (TO_DATE(' 2016-10-03 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
LOGGING
COMPRESS
TABLESPACE DWMDAT
PCTFREE 0
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 10M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
BUFFER_POOL DEFAULT
),
PARTITION P_20161010 VALUES LESS THAN (TO_DATE(' 2016-10-10 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
LOGGING
COMPRESS
TABLESPACE DWMDAT
PCTFREE 0
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 10M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
BUFFER_POOL DEFAULT
),
PARTITION P_20161017 VALUES LESS THAN (TO_DATE(' 2016-10-17 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
LOGGING
COMPRESS
TABLESPACE DWMDAT
PCTFREE 0
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 10M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
BUFFER_POOL DEFAULT
),
PARTITION P_20161024 VALUES LESS THAN (TO_DATE(' 2016-10-24 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
LOGGING
COMPRESS
TABLESPACE DWMDAT
PCTFREE 0
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 10M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
BUFFER_POOL DEFAULT
),
PARTITION P_20161031 VALUES LESS THAN (TO_DATE(' 2016-10-31 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
LOGGING
COMPRESS
TABLESPACE DWMDAT
PCTFREE 0
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 10M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
BUFFER_POOL DEFAULT
),
PARTITION P_20161107 VALUES LESS THAN (TO_DATE(' 2016-11-07 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
LOGGING
COMPRESS
TABLESPACE DWMDAT
PCTFREE 0
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 10M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
BUFFER_POOL DEFAULT
),
PARTITION P_20161114 VALUES LESS THAN (TO_DATE(' 2016-11-14 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
LOGGING
COMPRESS
TABLESPACE DWMDAT
PCTFREE 0
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 10M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
BUFFER_POOL DEFAULT
),
PARTITION P_20161121 VALUES LESS THAN (TO_DATE(' 2016-11-21 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
LOGGING
COMPRESS
TABLESPACE DWMDAT
PCTFREE 0
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 10M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
BUFFER_POOL DEFAULT
),
PARTITION P_20161128 VALUES LESS THAN (TO_DATE(' 2016-11-28 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
LOGGING
COMPRESS
TABLESPACE DWMDAT
PCTFREE 0
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 10M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
BUFFER_POOL DEFAULT
),
PARTITION P_20161205 VALUES LESS THAN (TO_DATE(' 2016-12-05 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
LOGGING
COMPRESS
TABLESPACE DWMDAT
PCTFREE 0
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 10M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
BUFFER_POOL DEFAULT
),
PARTITION P_20161212 VALUES LESS THAN (TO_DATE(' 2016-12-12 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
LOGGING
COMPRESS
TABLESPACE DWMDAT
PCTFREE 0
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 10M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
BUFFER_POOL DEFAULT
),
PARTITION P_20161219 VALUES LESS THAN (TO_DATE(' 2016-12-19 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
LOGGING
COMPRESS
TABLESPACE DWMDAT
PCTFREE 0
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 10M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
BUFFER_POOL DEFAULT
),
PARTITION P_20161226 VALUES LESS THAN (TO_DATE(' 2016-12-26 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
LOGGING
COMPRESS
TABLESPACE DWMDAT
PCTFREE 0
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 10M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
BUFFER_POOL DEFAULT
)
)
COMPRESS
NOCACHE
NOPARALLEL
MONITORING
ENABLE ROW MOVEMENT;
2.
alter table TMS.SD_CURRENT_PALLET_DAILY_LOG_P NOLOGGING;
ALTER SESSION ENABLE PARALLEL DML;
3.
INSERT /*+ append parallel(p,8) */
INTO TMS.SD_CURRENT_PALLET_DAILY_LOG_P P
SELECT /*+ parallel(n,8) */
*
FROM TMS.SD_CURRENT_PALLET_DAILY_LOG N
WHERE LOG_DATE >
TO_DATE ('2016-10-22 11:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
AND
LOG_DATE <=
TO_DATE(' 2016-10-22 11:00:00', 'YYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN');
select COUNT(*) FROM TMS.SD_CURRENT_PALLET_DAILY_LOG_P;
select COUNT(*) FROM TMS.SD_CURRENT_PALLET_DAILY_LOG;确保数据一致。
3.
drop index TMS.IDX_PALLET_LOG；--旧表的index
ALTER TABLE TMS.SD_CURRENT_PALLET_DAILY_LOG RENAME TO TMS.SD_CURRENT_PALLET_DAILY_LOG_OF;
ALTER TABLE TMS.SD_CURRENT_PALLET_DAILY_LOG_P RENMAE TO TMS.SD_CURRENT_PALLET_DAILY_LOG;
4.
CREATE INDEX TMS.IDX_PALLET_LOG ON TMS.SD_CURRENT_PALLET_DAILY_LOG
(LOG_DATE)
TABLESPACE DWMDAT
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 10M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
NOLOGGING
LOCAL(
PARTITION P_20160829
NOLOGGING
COMPRESS
TABLESPACE DWMDAT
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 10M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
BUFFER_POOL DEFAULT
),
PARTITION P_20160905
NOLOGGING
COMPRESS
TABLESPACE DWMDAT
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 10M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
BUFFER_POOL DEFAULT
),
PARTITION P_20160912
NOLOGGING
COMPRESS
TABLESPACE DWMDAT
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 10M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
BUFFER_POOL DEFAULT
),
PARTITION P_20160919
NOLOGGING
COMPRESS
TABLESPACE DWMDAT
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 10M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
BUFFER_POOL DEFAULT
),
PARTITION P_20160926
NOLOGGING
COMPRESS
TABLESPACE DWMDAT
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 10M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
BUFFER_POOL DEFAULT
),
PARTITION P_20161003
NOLOGGING
COMPRESS
TABLESPACE DWMDAT
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 10M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
BUFFER_POOL DEFAULT
),
PARTITION P_20161010
NOLOGGING
COMPRESS
TABLESPACE DWMDAT
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 10M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
BUFFER_POOL DEFAULT
),
PARTITION P_20161017
NOLOGGING
COMPRESS
TABLESPACE DWMDAT
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 10M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
BUFFER_POOL DEFAULT
),
PARTITION P_20161024
NOLOGGING
COMPRESS
TABLESPACE DWMDAT
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 10M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
BUFFER_POOL DEFAULT
),
PARTITION P_20161031
NOLOGGING
COMPRESS
TABLESPACE DWMDAT
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 10M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
BUFFER_POOL DEFAULT
),
PARTITION P_20161107
NOLOGGING
COMPRESS
TABLESPACE DWMDAT
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 10M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
BUFFER_POOL DEFAULT
),
PARTITION P_20161114
NOLOGGING
COMPRESS
TABLESPACE DWMDAT
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 10M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
BUFFER_POOL DEFAULT
),
PARTITION P_20161121
NOLOGGING
COMPRESS
TABLESPACE DWMDAT
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 10M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
BUFFER_POOL DEFAULT
),
PARTITION P_20161128
NOLOGGING
COMPRESS
TABLESPACE DWMDAT
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 10M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
BUFFER_POOL DEFAULT
),
PARTITION P_20161205
NOLOGGING
COMPRESS
TABLESPACE DWMDAT
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 10M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
BUFFER_POOL DEFAULT
),
PARTITION P_20161212
NOLOGGING
COMPRESS
TABLESPACE DWMDAT
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 10M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
BUFFER_POOL DEFAULT
),
PARTITION P_20161219
NOLOGGING
COMPRESS
TABLESPACE DWMDAT
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 10M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
BUFFER_POOL DEFAULT
),
PARTITION P_20161226
NOLOGGING
COMPRESS
TABLESPACE DWMDAT
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 10M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
BUFFER_POOL DEFAULT
)
)
COMPRESS
PARALLEL 6;
exec dbms_stats.gather_table_stats 查看表的统计信息。
alter table LOGGING NOPARALLEL;
ALTER INDEX LOGGING NOPARALLE;