表分区建立以及删除.sql

最新推荐文章于 2022-12-12 16:36:50 发布
最新推荐文章于 2022-12-12 16:36:50 发布
阅读量2.9k 收藏
点赞数
分类专栏： oracle
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/boenwan/article/details/50903322
版权 
---sms_bengine
---分区名命名方法:PART_TYYYYMM  ,分区列：view_time
---最大分区名:PART_T201100
---有maxvalues不能新增分区
select count(1) from sms_bengine partition(PART_T201100);
---PART_T201100
alter table sms_bengine drop partition PART_T201100 UPDATE GLOBAL INDEXES;
alter table sms_bengine rename partition PART_T201112 to PART_T201012;

----------------------------------------------------------------
set serveroutput on size 10000
declare
 l_str varchar(1000);
begin
    for mm in 1..12 loop
--   for n in 0..2 loop
     select 'alter table sms_bengine add PARTITION PART_T2011'||lpad(mm,2,'0')||' VALUES LESS THAN (TIMESTAMP '||chr(39)||'2011-'||lpad(mm+1,2,'0')||'-01 00:00:00'||chr(39)||') TABLESPACE ECHNHAND_DAT;' into l_str from dual;
     dbms_output.put_line(replace(l_str,'2011-13','2012-01'));
--   end loop;
    end loop;
end;
/
------------------------------------------------------------------
alter table sms_bengine add PARTITION PART_T201101 VALUES LESS THAN (TIMESTAMP '2011-02-01 00:00:00') TABLESPACE ECHNHAND_DAT;
alter table sms_bengine add PARTITION PART_T201102 VALUES LESS THAN (TIMESTAMP '2011-03-01 00:00:00') TABLESPACE ECHNHAND_DAT;
alter table sms_bengine add PARTITION PART_T201103 VALUES LESS THAN (TIMESTAMP '2011-04-01 00:00:00') TABLESPACE ECHNHAND_DAT;
alter table sms_bengine add PARTITION PART_T201104 VALUES LESS THAN (TIMESTAMP '2011-05-01 00:00:00') TABLESPACE ECHNHAND_DAT;
alter table sms_bengine add PARTITION PART_T201105 VALUES LESS THAN (TIMESTAMP '2011-06-01 00:00:00') TABLESPACE ECHNHAND_DAT;
alter table sms_bengine add PARTITION PART_T201106 VALUES LESS THAN (TIMESTAMP '2011-07-01 00:00:00') TABLESPACE ECHNHAND_DAT;
alter table sms_bengine add PARTITION PART_T201107 VALUES LESS THAN (TIMESTAMP '2011-08-01 00:00:00') TABLESPACE ECHNHAND_DAT;
alter table sms_bengine add PARTITION PART_T201108 VALUES LESS THAN (TIMESTAMP '2011-09-01 00:00:00') TABLESPACE ECHNHAND_DAT;
alter table sms_bengine add PARTITION PART_T201109 VALUES LESS THAN (TIMESTAMP '2011-10-01 00:00:00') TABLESPACE ECHNHAND_DAT;
alter table sms_bengine add PARTITION PART_T201110 VALUES LESS THAN (TIMESTAMP '2011-11-01 00:00:00') TABLESPACE ECHNHAND_DAT;
alter table sms_bengine add PARTITION PART_T201111 VALUES LESS THAN (TIMESTAMP '2011-12-01 00:00:00') TABLESPACE ECHNHAND_DAT;
alter table sms_bengine add PARTITION PART_T201212 VALUES LESS THAN (TIMESTAMP '2012-01-01 00:00:00') TABLESPACE ECHNHAND_DAT;

---默认分区
alter table sms_bengine add PARTITION PART_T201300 VALUES LESS THAN (MAXVALUE) TABLESPACE ECHNHAND_DAT;



---sms_mo
---分区名命名方法:PART_TYYYYMM  ,分区列：up_time
---最大分区名:PART_T201100
---有maxvalues不能新增分区
select count(1) from sms_mo partition(PART_T201100);
---PART_T201100
alter table sms_mo drop partition PART_T201100 UPDATE GLOBAL INDEXES;
alter table sms_mo rename partition PART_T201112 to PART_T201012;

----------------------------------------------------------------
set serveroutput on size 10000
declare
 l_str varchar(1000);
begin
    for mm in 1..12 loop
--   for n in 0..2 loop
     select 'alter table sms_mo add PARTITION PART_T2011'||lpad(mm,2,'0')||' VALUES LESS THAN (TIMESTAMP '||chr(39)||'2011-'||lpad(mm+1,2,'0')||'-01 00:00:00'||chr(39)||') TABLESPACE ECHNHAND_DAT;' into l_str from dual;
     dbms_output.put_line(replace(l_str,'2011-13','2012-01'));
--   end loop;
    end loop;
end;
/
------------------------------------------------------------------
alter table sms_mo add PARTITION PART_T201101 VALUES LESS THAN (TIMESTAMP '2011-02-01 00:00:00') TABLESPACE ECHNHAND_DAT;
alter table sms_mo add PARTITION PART_T201102 VALUES LESS THAN (TIMESTAMP '2011-03-01 00:00:00') TABLESPACE ECHNHAND_DAT;
alter table sms_mo add PARTITION PART_T201103 VALUES LESS THAN (TIMESTAMP '2011-04-01 00:00:00') TABLESPACE ECHNHAND_DAT;
alter table sms_mo add PARTITION PART_T201104 VALUES LESS THAN (TIMESTAMP '2011-05-01 00:00:00') TABLESPACE ECHNHAND_DAT;
alter table sms_mo add PARTITION PART_T201105 VALUES LESS THAN (TIMESTAMP '2011-06-01 00:00:00') TABLESPACE ECHNHAND_DAT;
alter table sms_mo add PARTITION PART_T201106 VALUES LESS THAN (TIMESTAMP '2011-07-01 00:00:00') TABLESPACE ECHNHAND_DAT;
alter table sms_mo add PARTITION PART_T201107 VALUES LESS THAN (TIMESTAMP '2011-08-01 00:00:00') TABLESPACE ECHNHAND_DAT;
alter table sms_mo add PARTITION PART_T201108 VALUES LESS THAN (TIMESTAMP '2011-09-01 00:00:00') TABLESPACE ECHNHAND_DAT;
alter table sms_mo add PARTITION PART_T201109 VALUES LESS THAN (TIMESTAMP '2011-10-01 00:00:00') TABLESPACE ECHNHAND_DAT;
alter table sms_mo add PARTITION PART_T201110 VALUES LESS THAN (TIMESTAMP '2011-11-01 00:00:00') TABLESPACE ECHNHAND_DAT;
alter table sms_mo add PARTITION PART_T201111 VALUES LESS THAN (TIMESTAMP '2011-12-01 00:00:00') TABLESPACE ECHNHAND_DAT;
alter table sms_mo add PARTITION PART_T201212 VALUES LESS THAN (TIMESTAMP '2012-01-01 00:00:00') TABLESPACE ECHNHAND_DAT;

---默认分区
alter table sms_mo add PARTITION PART_T201300 VALUES LESS THAN (MAXVALUE) TABLESPACE ECHNHAND_DAT;

---sms_mt
---分区名命名方法:PART_TYYYYMM  ,分区列：down_time
---最大分区名:PART_T201100
---有maxvalues不能新增分区
select count(1) from sms_mt partition(PART_T201100);
---PART_T201100
alter table sms_mt drop partition PART_T201100 UPDATE GLOBAL INDEXES;
alter table sms_mt rename partition PART_T201112 to PART_T201012;

----------------------------------------------------------------
set serveroutput on size 10000
declare
 l_str varchar(1000);
begin
    for mm in 1..12 loop
--   for n in 0..2 loop
     select 'alter table sms_mt add PARTITION PART_T2011'||lpad(mm,2,'0')||' VALUES LESS THAN (TIMESTAMP '||chr(39)||'2011-'||lpad(mm+1,2,'0')||'-01 00:00:00'||chr(39)||') TABLESPACE ECHNHAND_DAT;' into l_str from dual;
     dbms_output.put_line(replace(l_str,'2011-13','2012-01'));
--   end loop;
    end loop;
end;
/
------------------------------------------------------------------
alter table sms_mt add PARTITION PART_T201101 VALUES LESS THAN (TIMESTAMP '2011-02-01 00:00:00') TABLESPACE ECHNHAND_DAT;
alter table sms_mt add PARTITION PART_T201102 VALUES LESS THAN (TIMESTAMP '2011-03-01 00:00:00') TABLESPACE ECHNHAND_DAT;
alter table sms_mt add PARTITION PART_T201103 VALUES LESS THAN (TIMESTAMP '2011-04-01 00:00:00') TABLESPACE ECHNHAND_DAT;
alter table sms_mt add PARTITION PART_T201104 VALUES LESS THAN (TIMESTAMP '2011-05-01 00:00:00') TABLESPACE ECHNHAND_DAT;
alter table sms_mt add PARTITION PART_T201105 VALUES LESS THAN (TIMESTAMP '2011-06-01 00:00:00') TABLESPACE ECHNHAND_DAT;
alter table sms_mt add PARTITION PART_T201106 VALUES LESS THAN (TIMESTAMP '2011-07-01 00:00:00') TABLESPACE ECHNHAND_DAT;
alter table sms_mt add PARTITION PART_T201107 VALUES LESS THAN (TIMESTAMP '2011-08-01 00:00:00') TABLESPACE ECHNHAND_DAT;
alter table sms_mt add PARTITION PART_T201108 VALUES LESS THAN (TIMESTAMP '2011-09-01 00:00:00') TABLESPACE ECHNHAND_DAT;
alter table sms_mt add PARTITION PART_T201109 VALUES LESS THAN (TIMESTAMP '2011-10-01 00:00:00') TABLESPACE ECHNHAND_DAT;
alter table sms_mt add PARTITION PART_T201110 VALUES LESS THAN (TIMESTAMP '2011-11-01 00:00:00') TABLESPACE ECHNHAND_DAT;
alter table sms_mt add PARTITION PART_T201111 VALUES LESS THAN (TIMESTAMP '2011-12-01 00:00:00') TABLESPACE ECHNHAND_DAT;
alter table sms_mt add PARTITION PART_T201212 VALUES LESS THAN (TIMESTAMP '2012-01-01 00:00:00') TABLESPACE ECHNHAND_DAT;

---默认分区
alter table sms_mt add PARTITION PART_T201200 VALUES LESS THAN (MAXVALUE) TABLESPACE ECHNHAND_DAT;

---分区索引
---原索引IDX_ACCESSLOG_SUBURL，基于create_date做的全局索引失效。


---wap_log
---分区名命名方法:PART_TYYYYMM  ,分区列：view_time
---最大分区名:PART_T201100
---有maxvalues不能新增分区
select count(1) from wap_log partition(PART_T201100);
---PART_T201100
alter table wap_log drop partition PART_T201100 UPDATE GLOBAL INDEXES;
alter table wap_log rename partition PART_T201112 to PART_T201012;

----------------------------------------------------------------
set serveroutput on size 10000
declare
 l_str varchar(1000);
begin
    for mm in 1..12 loop
--   for n in 0..2 loop
     select 'alter table wap_log add PARTITION PART_T2011'||lpad(mm,2,'0')||' VALUES LESS THAN (TIMESTAMP '||chr(39)||'2011-'||lpad(mm+1,2,'0')||'-01 00:00:00'||chr(39)||') TABLESPACE ECHNHAND_DAT;' into l_str from dual;
     dbms_output.put_line(replace(l_str,'2011-13','2012-01'));
--   end loop;
    end loop;
end;
/
------------------------------------------------------------------
alter table wap_log add PARTITION PART_T201101 VALUES LESS THAN (TIMESTAMP '2011-02-01 00:00:00') TABLESPACE ECHNHAND_DAT;
alter table wap_log add PARTITION PART_T201102 VALUES LESS THAN (TIMESTAMP '2011-03-01 00:00:00') TABLESPACE ECHNHAND_DAT;
alter table wap_log add PARTITION PART_T201103 VALUES LESS THAN (TIMESTAMP '2011-04-01 00:00:00') TABLESPACE ECHNHAND_DAT;
alter table wap_log add PARTITION PART_T201104 VALUES LESS THAN (TIMESTAMP '2011-05-01 00:00:00') TABLESPACE ECHNHAND_DAT;
alter table wap_log add PARTITION PART_T201105 VALUES LESS THAN (TIMESTAMP '2011-06-01 00:00:00') TABLESPACE ECHNHAND_DAT;
alter table wap_log add PARTITION PART_T201106 VALUES LESS THAN (TIMESTAMP '2011-07-01 00:00:00') TABLESPACE ECHNHAND_DAT;
alter table wap_log add PARTITION PART_T201107 VALUES LESS THAN (TIMESTAMP '2011-08-01 00:00:00') TABLESPACE ECHNHAND_DAT;
alter table wap_log add PARTITION PART_T201108 VALUES LESS THAN (TIMESTAMP '2011-09-01 00:00:00') TABLESPACE ECHNHAND_DAT;
alter table wap_log add PARTITION PART_T201109 VALUES LESS THAN (TIMESTAMP '2011-10-01 00:00:00') TABLESPACE ECHNHAND_DAT;
alter table wap_log add PARTITION PART_T201110 VALUES LESS THAN (TIMESTAMP '2011-11-01 00:00:00') TABLESPACE ECHNHAND_DAT;
alter table wap_log add PARTITION PART_T201111 VALUES LESS THAN (TIMESTAMP '2011-12-01 00:00:00') TABLESPACE ECHNHAND_DAT;
alter table wap_log add PARTITION PART_T201212 VALUES LESS THAN (TIMESTAMP '2012-01-01 00:00:00') TABLESPACE ECHNHAND_DAT;

---默认分区
alter table wap_log add PARTITION PART_T201300 VALUES LESS THAN (MAXVALUE) TABLESPACE ECHNHAND_DAT;


/* tb_uservisit */
-------------
-- Create table
create table TB_USERVISIT_NEW
(
  USER_PHONE   VARCHAR2(50),
  PAHT         VARCHAR2(200),
  NODEID       VARCHAR2(100),
  VISIT_TIME   DATE,
  TOKEN        VARCHAR2(200),
  USERID       VARCHAR2(200),
  AREACODE     VARCHAR2(20),
  REMARK       VARCHAR2(200),
  TYPE         VARCHAR2(20),
  RESNAME      VARCHAR2(200),
  RESTYPE      VARCHAR2(200),
  COLUMNID     VARCHAR2(200),
  COLUMNNAME   VARCHAR2(200),
  FLAG         VARCHAR2(100),
  CLIENTIP     VARCHAR2(200),
  TERMINALTYPE VARCHAR2(3)
)
partition by range (VISIT_TIME)
(
  partition PART_T201201 values less than (TO_DATE(' 2012-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace PORTAL_DAT
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 1M
      next 1M
      minextents 1
      maxextents unlimited
      pctincrease 0
    ),
  partition PART_T201202 values less than (TO_DATE(' 2012-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace PORTAL_DAT
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 1M
      next 1M
      minextents 1
      maxextents unlimited
      pctincrease 0
    ),
  partition PART_T201203 values less than (TO_DATE(' 2012-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace PORTAL_DAT
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 1M
      next 1M
      minextents 1
      maxextents unlimited
      pctincrease 0
    ),
  partition PART_T201204 values less than (TO_DATE(' 2012-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace PORTAL_DAT
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 1M
      next 1M
      minextents 1
      maxextents unlimited
      pctincrease 0
    ),
  partition PART_T201205 values less than (TO_DATE(' 2012-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace PORTAL_DAT
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 1M
      next 1M
      minextents 1
      maxextents unlimited
      pctincrease 0
    ),
partition PART_T201206 values less than (TO_DATE(' 2012-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace PORTAL_DAT
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 1M
      next 1M
      minextents 1
      maxextents unlimited
      pctincrease 0
    ),
  partition PART_T201207 values less than (TO_DATE(' 2012-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace PORTAL_DAT
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 1M
      next 1M
      minextents 1
      maxextents unlimited
      pctincrease 0
    ),
  partition PART_T201208 values less than (TO_DATE(' 2012-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace PORTAL_DAT
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 1M
      next 1M
      minextents 1
      maxextents unlimited
      pctincrease 0
    ),
  partition PART_T201209 values less than (TO_DATE(' 2012-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace PORTAL_DAT
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 1M
      next 1M
      minextents 1
      maxextents unlimited
      pctincrease 0
    ),
  partition PART_T201210 values less than (TO_DATE(' 2012-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace PORTAL_DAT
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 1M
      next 1M
      minextents 1
      maxextents unlimited
      pctincrease 0
    ),
  partition PART_T201211 values less than (TO_DATE(' 2012-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace PORTAL_DAT
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 1M
      next 1M
      minextents 1
      maxextents unlimited
      pctincrease 0
    ),
  partition PART_T201312 values less than (TO_DATE(' 2013-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace PORTAL_DAT
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 1M
      next 1M
      minextents 1
      maxextents unlimited
      pctincrease 0
    ),
  partition PART_T201300 values less than (MAXVALUE)
    tablespace PORTAL_DAT
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 1M
      next 1M
      minextents 1
      maxextents unlimited
      pctincrease 0
    )
);
-- Add comments to the table 
comment on table TB_USERVISIT_NEW
  is '用户访问日志';
-- Add comments to the columns 
comment on column TB_USERVISIT_NEW.USER_PHONE
  is '手机号码';
comment on column TB_USERVISIT_NEW.PAHT
  is '访问目录';
comment on column TB_USERVISIT_NEW.NODEID
  is '资源编码';
comment on column TB_USERVISIT_NEW.VISIT_TIME
  is '访问时间';
comment on column TB_USERVISIT_NEW.TOKEN
  is '用户Token';
comment on column TB_USERVISIT_NEW.USERID
  is '用户帐号';
comment on column TB_USERVISIT_NEW.AREACODE
  is '访问地市编码';
comment on column TB_USERVISIT_NEW.REMARK
  is '备注';
comment on column TB_USERVISIT_NEW.TYPE
  is '访问类型';
comment on column TB_USERVISIT_NEW.RESNAME
  is '资源名称';
comment on column TB_USERVISIT_NEW.RESTYPE
  is '资源分类';
comment on column TB_USERVISIT_NEW.COLUMNID
  is '归属叶子节点栏目ID';
comment on column TB_USERVISIT_NEW.COLUMNNAME
  is '归属叶子节点栏目名称';
comment on column TB_USERVISIT_NEW.FLAG
  is '0:手机号,1:邮箱,空:匿名';
comment on column TB_USERVISIT_NEW.CLIENTIP
  is '手机客户端IP';
comment on column TB_USERVISIT_NEW.TERMINALTYPE
  is ' 1.手机；2.电脑；3. 其它；';
-- creat_index
create index IDX_TB_USERVISIT_VISIT_TIME on TB_USERVISIT_NEW (VISIT_TIME)
  tablespace PORTAL_DAT
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
    pctincrease 0
  );
create index IDX_TB_USERVISIT_USER_PHONE on TB_USERVISIT_NEW (USER_PHONE)
  tablespace PORTAL_DAT
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
    pctincrease 0
  );
create index IDX_TB_USERVISIT_RESNAME on TB_USERVISIT_NEW (RESNAME)
  tablespace PORTAL_DAT
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
    pctincrease 0
  );
create index IDX_TB_USERVISIT_AREACODE on TB_USERVISIT_NEW (AREACODE)
  tablespace PORTAL_DAT
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
    pctincrease 0
  );
-- Grant/Revoke object privileges 


alter table TB_USERVISIT rename to TB_USERVISIT_HISTORY2012;
alter table TB_USERVISIT_NEW rename to TB_USERVISIT;
grant select on TB_USERVISIT to ICITYRPT;

------------

-- Create table
-- Create table
create table TW_CALL_SERVICE_LOG
(
  SERVICE_NAME    VARCHAR2(100),
  CALL_START_TIME DATE,
  CALL_END_TIME   DATE,
  PROCESS_TIME    NUMBER,
  LOGIN_NO        VARCHAR2(30),
  IN_PARA         VARCHAR2(4000),
  RET_CODE        VARCHAR2(100),
  RET_MESSAGE     VARCHAR2(4000)
)
partition by range (CALL_END_TIME)
(
  partition PART_T201301 values less than (TO_DATE(' 2013-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace PORTAL_DATA
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 1M
      next 1M
      minextents 1
      maxextents unlimited
      pctincrease 0
    ),
  partition PART_T201302 values less than (TO_DATE(' 2013-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace PORTAL_DATA
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 1M
      next 1M
      minextents 1
      maxextents unlimited
      pctincrease 0
    ),
  partition PART_T201303 values less than (TO_DATE(' 2013-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace PORTAL_DATA
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 1M
      next 1M
      minextents 1
      maxextents unlimited
      pctincrease 0
    ),
  partition PART_T201304 values less than (TO_DATE(' 2013-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace PORTAL_DATA
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 1M
      next 1M
      minextents 1
      maxextents unlimited
      pctincrease 0
    ),
  partition PART_T201305 values less than (TO_DATE(' 2013-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace PORTAL_DATA
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 1M
      next 1M
      minextents 1
      maxextents unlimited
      pctincrease 0
    ),
partition PART_T201306 values less than (TO_DATE(' 2013-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace PORTAL_DATA
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 1M
      next 1M
      minextents 1
      maxextents unlimited
      pctincrease 0
    ),
  partition PART_T201307 values less than (TO_DATE(' 2013-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace PORTAL_DATA
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 1M
      next 1M
      minextents 1
      maxextents unlimited
      pctincrease 0
    ),
  partition PART_T201308 values less than (TO_DATE(' 2013-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace PORTAL_DATA
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 1M
      next 1M
      minextents 1
      maxextents unlimited
      pctincrease 0
    ),
  partition PART_T201309 values less than (TO_DATE(' 2013-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace PORTAL_DATA
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 1M
      next 1M
      minextents 1
      maxextents unlimited
      pctincrease 0
    ),
  partition PART_T201310 values less than (TO_DATE(' 2013-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace PORTAL_DATA
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 1M
      next 1M
      minextents 1
      maxextents unlimited
      pctincrease 0
    ),
  partition PART_T201311 values less than (TO_DATE(' 2013-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace PORTAL_DATA
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 1M
      next 1M
      minextents 1
      maxextents unlimited
      pctincrease 0
    ),
  partition PART_T201412 values less than (TO_DATE(' 2014-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace PORTAL_DATA
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 1M
      next 1M
      minextents 1
      maxextents unlimited
      pctincrease 0
    ),
  partition PART_T201400 values less than (MAXVALUE)
    tablespace PORTAL_DATA
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 1M
      next 1M
      minextents 1
      maxextents unlimited
      pctincrease 0
    )
);

-- Add comments to the columns 
comment on column TW_CALL_SERVICE_LOG.SERVICE_NAME
  is '接口方法名称';
comment on column TW_CALL_SERVICE_LOG.CALL_START_TIME
  is '访问开始时间';
comment on column TW_CALL_SERVICE_LOG.CALL_END_TIME
  is '访问结束时间';
comment on column TW_CALL_SERVICE_LOG.PROCESS_TIME
  is '访问经历时间';
comment on column TW_CALL_SERVICE_LOG.LOGIN_NO
  is '登陆账号';
comment on column TW_CALL_SERVICE_LOG.IN_PARA
  is '输入参数';
comment on column TW_CALL_SERVICE_LOG.RET_CODE
  is '返回状态码';
comment on column TW_CALL_SERVICE_LOG.RET_MESSAGE
  is '返回状态描述';

create index IDX_SERVICE_LOG_SERVICE_NAME on TW_CALL_SERVICE_LOG (SERVICE_NAME) LOCAL
  tablespace PORTAL_DATA
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
    pctincrease 0
  );


create index IDX_SERVICE_LOG_LOGIN_NO on TW_CALL_SERVICE_LOG (LOGIN_NO) LOCAL
  tablespace PORTAL_DATA
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
    pctincrease 0
  );


rename tb_uservisit to tb_uservisit_bak1;

rename TB_USERVISIT_NEW to TB_USERVISIT;



create table tb_uservisit_3 as select * from tb_uservisit_bak1 where visit_time >sysdate-3;

alter table TB_USERVISIT exchange partition PART_T201207 with table tb_uservisit_3 ;
伯恩dadi
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
Hive_SQL 一次删除多个分区数据
Heng_bigdate_yan的博客
10-16 1万+
目录 1.删除语法 2.元数据及数据存储变化 3.示例 3.1 单个分区字段表 3.1.1 删除单个分区单个分区数据 3.1.2 删除单个分区字段多个分区数据 3.2多个分区字段表 3.2.1 删除多个分区字段 单个分区数据 3.2.2 删除多个分区字段 单个字段 多个分区范围数据 3.2.3删除多个分区字段 多个字段 多个分区范围数据 1.删除语法 ALTER TABLE table_name DROP [IF EXISTS] PARTITION partition...
【数据库SQL】分区的新建与删除（Oracle）
Donald_Knight的博客
05-26 6304
如果数据库中的一张表在建表的时候通过partitioned by()指定了表中的某字段为分区，那么它就是一张分区表。 向分区表中插入数据时，必须通过partition on()指明要把数据插入到哪个分区中，如果不指明分区则无法插入数据。 新建分区 给一张表新建分区的方法如下。 Oracle数据库上，新建分区的写法是这样的： alter table 表名 add partition 分区名 values (‘分区的值’) 示例： alter table DM_TABLE_M_V add partition P
删除MySQL表的SQL语句-DROP-TABLE-简介
铁锚的CSDN博客
12-10 1万+
辨析: 在SQL中, DROP 用于删除表结构等信息, 属于数据格式定义 DDL的范畴； 而 DELETE 用于删除数据，属于数据内容操作 DML 的范畴。 官方给出的 DROP 关键字删除表结构的语法如下: DROP [TEMPORARY] TABLE [IF EXISTS] tbl_name [, tbl_name] ... [RESTRICT | CASCADE] 当然...
Hive sql删除分区
天狗的日常
07-19 2512
Hive sql删除分区
一步步教你建立SQL数据库的表分区
09-10
分区存储提高了数据库的性能，被分区存储的数据物理上是多个文件，但逻辑上任然是一个表，对表的任何操作都跟没分区之前一样。插入、删除、查询、更新等操作的时候，数据库会自动为你找到对应的分区，然后执行操作。
mysql使用教程之分区表的使用方法(删除分区表)
12-15
2，方便维护，通过删除分区来删除老的数据。3，分区数据可以被分布到不同的物理位置，可以做分布式有效利用多个硬盘驱动器。 MySQL可以建立四种分区类型的分区： RANGE 分区：基于属于一个给定连续区间的列值，把...
SQLServer2005中的分区表和索引
08-18
1，建立分区表 2，查询分区 3，归档数据 4，添加分区 5，删除分区 6，查看元数据
经典SQL语句大全
12-20
10、说明：两张关联表，删除主表中已经在副表中没有的信息 delete from table1 where not exists ( select * from table2 where table1.field1=table2.field1 ) 11、说明：四表联查问题： select * from a left ...
SQL.Server.2008管理员必备指南.part4.rar（4/4）
05-04
　10.3.3 了解表的分区 283 　10.4 处理表 283 　10.4.1 创建表 284 　10.4.2 修改现有的表 288 　10.4.3 查看表的行和大小的信息 290 　10.4.4 显示表属性和权限 291 　10.4.5 显示表中当前的值 291 　10.4.6 复制...
数据库分区需要用到的SQL语句 查询表分区 删除表中的分区 手动增加表分区 查询表分区的名字以及对应的分区时间 查询对应的表分区
weixin_48230267的博客
12-12 1422
数据库分区
MySQL 清空分区表单个分区数据
demonson的专栏
11-04 2294
功能：指定清空之前某一天的数据，直接调用存储过程实现。清空test.t_001一天前的单个分区数据。
GP--大表分区管理(一)
唐僧洗头爱飘柔
04-01 1255
表分区使用背景： 表分区用以解决特别大的表的问题，比如事实表，解决办法就是将表分成很多小且更容易管理的部分。 在CREATE TABLE时使用PARTITION BY(以及可选的SUBPARTITION BY)子句来做分区。 在GPDB中对一张表做分区，实际上是创建了一张顶层(父级)表和多个低层(子级)表。 在内部， GPDB在顶级表与低级表之间创建了继承关系(类似于PostgreSQL中的继承/...
如何删除SqlServer分区函数
weixin_30547797的博客
01-11 1504
我们都知道，SQL server2008R2企业版以及一些其它的版本支持分区函数，当你在这些数据库备份后想在一些不支持分区函数的数据库做还原时，就会失败。 下面我们来解决这个问题。 1.备份数据库!备份数据库!备份数据库! （对数据库动任何刀子前先备份） 2.删除分区函数所涉及的表，因为在建表的时候我们做了关联，所以删除主要是切断这个关联以及相关的文件。 3.删除关联、删除分区文件 ...
删除分区表里某个分区的数据
热门推荐
wrh_csdn的博客
09-07 1万+
ALTER TABLE TBL_OPR_20_PERFORM_CNT TRUNCATE PARTITION P201704;
GP上的分区表
cuanqian4378的博客
01-20 1713
对于大数据量的表我们通常会对其按照一定规则进行分区存储，以便提高查询效率，下面简单对GP上分区表的创建、分区的增加和删除进行了整理。一、创建GP中表的分区都只能在创建表的时候定义，因此如果需要对表做分区一定要在create表的时...
批量删除分区表
心之所系的博客
04-24 4481
存储过程： DELIMITER $$ DROP PROCEDURE IF EXISTS delete_partitions$$ CREATE PROCEDURE delete_partitions(IN tabName VARCHAR(500),IN YearMonth VARCHAR(20) ) BEGIN declare parName VARCHAR(50); DECLARE s
删除分区数据
缘起缘灭4的博客
12-29 6599
全删除，删除数据以及该分区： ALTERTABLE yourTable DROP PARTITION partionName1; 清数据，只删除数据： ALTER TABLE yourTable TRUNCATE PARTITION partionName1;
pgsql建立分区表以及权限
最新发布
05-20
建立分区表： 1. 首先需要创建一个分区表的父表，例如： ```sql CREATE TABLE orders ( order_id serial primary key, customer text NOT NULL, order_date date NOT NULL, order_amount numeric NOT NULL ); ...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值