Oracle 水平分表

最新推荐文章于 2024-04-14 16:20:52 发布

爱码社长

最新推荐文章于 2024-04-14 16:20:52 发布

阅读量199

点赞数

文章标签： oracle 数学建模数据库

本文链接：https://blog.csdn.net/mars131458/article/details/130829378

版权

以PIECES_MTEST为例：

--以创建时间分表
CREATE TABLE "CQCBZ"."PIECES_MTEST" (
  "PIECES_CODE" VARCHAR2(32 BYTE) VISIBLE NOT NULL,
  "EXECUTION_BATCH" VARCHAR2(32 BYTE) VISIBLE NOT NULL,
  "PLATE_CODE" VARCHAR2(32 BYTE) VISIBLE,
  "BUNDLE_CODE" VARCHAR2(32 BYTE) VISIBLE,
  "CREATE_DATE" DATE VISIBLE
)
 partition by range (CREATE_DATE)   
(   
--PIECES_MTEST01为分片名称
 partition PIECES_MTEST01 values less than(TO_DATE('2022-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace PIECES_MTEST_DB_SPACE,   
 partition PIECES_MTEST02 values less than(TO_DATE('2022-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace PIECES_MTEST_DB_SPACE,   
 partition PIECES_MTEST03 values less than(TO_DATE('2022-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace PIECES_MTEST_DB_SPACE,
 partition PIECES_MTEST04 values less than(TO_DATE('2022-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace PIECES_MTEST_DB_SPACE,
 partition PIECES_MTEST05 values less than(TO_DATE('2022-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace PIECES_MTEST_DB_SPACE,
 partition PIECES_MTEST06 values less than(TO_DATE('2022-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace PIECES_MTEST_DB_SPACE,
 partition PIECES_MTEST07 values less than(TO_DATE('2022-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace PIECES_MTEST_DB_SPACE,
 partition PIECES_MTEST08 values less than(TO_DATE('2022-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace PIECES_MTEST_DB_SPACE,
 partition PIECES_MTEST09 values less than(TO_DATE('2022-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace PIECES_MTEST_DB_SPACE,
 partition PIECES_MTEST10 values less than(TO_DATE('2022-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace PIECES_MTEST_DB_SPACE,
 partition PIECES_MTEST11 values less than(TO_DATE('2022-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace PIECES_MTEST_DB_SPACE,
 partition PIECES_MTEST12 values less than(TO_DATE('2023-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace PIECES_MTEST_DB_SPACE,
 partition PIECES_MTEST13 values less than(MAXVALUE) tablespace PIECES_MTEST_DB_SPACE   
 )
;



 --创建表空间
create tablespace PIECES_MTEST_DB_SPACE
logging
datafile '/opt/oracle/oradata/ORCLCDB/ORCLPDB1/PIECES_MTEST_DB_SPACE.dbf'
size 50m
autoextend on
next 50m maxsize 20480m
extent management local;

--向新表中导入数据
insert into PIECES_MTEST select* from PIECES_467