工作中碰到一批表是按照月分做的分表:
需要参照12年为13年创建按月分表和按月分视图。
这里有两种方法实现:
方法1: plsql方式:
首先建立表:hch_temp_template
create table HCH_TEMP_TEMPLATE
(
OWNER VARCHAR2(200),
TABLENAME VARCHAR2(200),
TABDESC VARCHAR2(4000) ---直接输入表的定义:
);
for i in 2..12
loop
insert into hch_temp_template(Owner,Tablename,TABDESC,Partname) ---批量生成2到12月份的表,
select owner,replace(tablename,'201301','2013'||to_char(i,'FM09')),
replace(tabdesc,'201301','2013'||to_char(i,'FM09')),partname from hch_temp_template
where tablename =vtable_name;
commit;
end loop;
插入表里面之后,直接select 出来执行即可。
方法2: shell脚本:
建表脚本:
-- Create table
create table OSS_LOGIN_USER_DAY_201212
(
RECDATE NUMBER(8) not null,
USERNUMBER VARCHAR2(32) not null,
MODULEID NUMBER(5) default 0 not null,
CHANNELID NUMBER(5) default 0 not null,
OPERID NUMBER(10) default 0 not null,
CONTENTID NUMBER(10) default 0 not null,
SERVICEID NUMBER(5) default 0 not null,
OPERTYPE NUMBER(10) default 0 not null,
PROVCODE NUMBER(5) default 0 not null,
AREACODE NUMBER(5) default 0 not null,
SERVICEITEM VARCHAR2(20) not null,
ORDERTYPE NUMBER(5) default 0 not null,
CARDTYPE NUMBER(5) default 0 not null,
BINDTYPEID NUMBER(10) default 0 not null,
TOTALCOUNT NUMBER(10) default 0 not null,
EXTCOUNT1 NUMBER(10) default 0 not null,
EXTCOUNT2 NUMBER(10) default 0 not null,
CREATETIME DATE default sysdate not null,
MODIFYTIME DATE default sysdate not null
)
partition by range (PROVCODE)
(
partition LOGIN_USER_DAY_201212_00 values less than (1)
tablespace OSS03_DATA
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 80K
next 1M
minextents 1
maxextents unlimited
),
partition LOGIN_USER_DAY_201212_01 values less than (2)
tablespace OSS03_DATA
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 80K
next 1M
minextents 1
maxextents unlimited
),
partition LOGIN_USER_DAY_201212_02 values less than (3)
tablespace OSS03_DATA
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 80K
next 1M
minextents 1
maxextents unlimited
),
partition LOGIN_USER_DAY_201212_03 values less than (4)
tablespace OSS03_DATA
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 80K
next 1M
minextents 1
maxextents unlimited
),
partition LOGIN_USER_DAY_201212_04 values less than (5)
tablespace OSS03_DATA
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 80K
next 1M
minextents 1
maxextents unlimited
),
partition LOGIN_USER_DAY_201212_05 values less than (6)
tablespace OSS03_DATA
pctfree 10
ini