Oracle表空间

最新推荐文章于 2022-09-25 14:14:25 发布

authorzhh

最新推荐文章于 2022-09-25 14:14:25 发布

阅读量647

点赞数

本文链接：https://blog.csdn.net/authorzhh/article/details/8102478

版权

一、准备表空间

create tablespace ts_infecard01 datafile 'D:/oracle/product/ts_infecard01.dbf' size 200m autoextend on next 50M  maxsize unlimited;
create tablespace ts_infecard02 datafile 'D:/oracle/product/ts_infecard02.dbf' size 200m autoextend on next 50M  maxsize unlimited;
create tablespace ts_infecard03 datafile 'D:/oracle/product/ts_infecard03.dbf' size 200m autoextend on next 50M  maxsize unlimited;
create tablespace ts_infecard04 datafile 'D:/oracle/product/ts_infecard04.dbf' size 200m autoextend on next 50M  maxsize unlimited;
create tablespace ts_infecard05 datafile 'D:/oracle/product/ts_infecard05.dbf' size 200m autoextend on next 50M  maxsize unlimited;
create tablespace ts_infecard06 datafile 'D:/oracle/product/ts_infecard06.dbf' size 200m autoextend on next 50M  maxsize unlimited;
create tablespace ts_infecard07 datafile 'D:/oracle/product/ts_infecard07.dbf' size 200m autoextend on next 50M  maxsize unlimited;
create tablespace ts_infecard08 datafile 'D:/oracle/product/ts_infecard08.dbf' size 200m autoextend on next 50M  maxsize unlimited;
create tablespace ts_infecard09 datafile 'D:/oracle/product/ts_infecard09.dbf' size 200m autoextend on next 50M  maxsize unlimited;
create tablespace ts_infecard10 datafile 'D:/oracle/product/ts_infecard10.dbf' size 200m autoextend on next 50M  maxsize unlimited;
create tablespace ts_infecard11 datafile 'D:/oracle/product/ts_infecard11.dbf' size 200m autoextend on next 50M  maxsize unlimited;
create tablespace ts_infecard12 datafile 'D:/oracle/product/ts_infecard12.dbf' size 200m autoextend on next 50M  maxsize unlimited;
create tablespace ts_infecard13 datafile 'D:/oracle/product/ts_infecard13.dbf' size 200m autoextend on next 50M  maxsize unlimited;
create tablespace ts_infecard14 datafile 'D:/oracle/product/ts_infecard14.dbf' size 200m autoextend on next 50M  maxsize unlimited;
create tablespace ts_infecard15 datafile 'D:/oracle/product/ts_infecard15.dbf' size 200m autoextend on next 50M  maxsize unlimited;

二、建表

-- Create table
create table EVT_INFECTIONCARD2
(
  CARD_ID         VARCHAR2(20) not null,
  APANAGECODE     VARCHAR2(8),
  CARD_CODE       VARCHAR2(20),
  PATIENT_NAME    VARCHAR2(40),
  PARENT_NAME     VARCHAR2(40),
  SEX1            VARCHAR2(1) not null,
  SEX2            VARCHAR2(1),
  TELP            VARCHAR2(30),
  ID              VARCHAR2(18),
  UNIT            VARCHAR2(100),
  ADDR            VARCHAR2(100),
  ADDRCODE        VARCHAR2(8) not null,
  AREATYPE        VARCHAR2(1) not null,
  START_DATE      DATE,
  DIAGNOSEDATE    DATE,
  DEADDATE        DATE,
  CASETYPE        VARCHAR2(1),
  INPUTDOCTOR     VARCHAR2(16),
  DISEASE_ID1     NUMBER(4),
  DISEASE_ID2     NUMBER(4),
  VALID_DATE2     DATE,
  VALID_DATE1     DATE,
  USERID          VARCHAR2(20),
  NOTES           VARCHAR2(200),
  STATUS          VARCHAR2(1),
  BIRTHDAY_DATE   DATE,
  FILLTIME        DATE,
  BASE_ID         NUMBER(20),
  FLAG            NUMBER(1),
  INTIME          DATE,
  VALID_TIME5     DATE,
  VALID_TIME4     DATE,
  VALID_TIME3     DATE,
  DELETETIME      DATE,
  RPTORGCODE      VARCHAR2(30) not null,
  AFTER_FILL_DATE DATE,
  CONTACTFLAG     VARCHAR2(1),
  CURRMGMTORG     VARCHAR2(30),
  DATASOURCE      VARCHAR2(3),
  MGMTSTATUS      VARCHAR2(2),
  CASETYPE2       VARCHAR2(2),
  E_RCT           DATE,
  E_LMT           DATE,
  GROUP_ID        NUMBER(4)
)

PARTITION BY RANGE(INTIME)
(  
PARTITION tbts01 VALUES LESS THAN(TO_DATE('2006-01-01 00:00:00','yyyy-mm-dd hh24:mi:ss')) TABLESPACE ts_infecard01,
PARTITION tbts02 VALUES LESS THAN(TO_DATE('2007-01-01 00:00:00','yyyy-mm-dd hh24:mi:ss')) TABLESPACE ts_infecard02,
PARTITION tbts03 VALUES LESS THAN(TO_DATE('2008-01-01 00:00:00','yyyy-mm-dd hh24:mi:ss')) TABLESPACE ts_infecard03,
PARTITION tbts04 VALUES LESS THAN(TO_DATE('2008-07-01 00:00:00','yyyy-mm-dd hh24:mi:ss')) TABLESPACE ts_infecard04,
PARTITION tbts05 VALUES LESS THAN(TO_DATE('2009-01-01 00:00:00','yyyy-mm-dd hh24:mi:ss')) TABLESPACE ts_infecard05,
PARTITION tbts06 VALUES LESS THAN(TO_DATE('2009-07-01 00:00:00','yyyy-mm-dd hh24:mi:ss')) TABLESPACE ts_infecard06,
PARTITION tbts07 VALUES LESS THAN(TO_DATE('2010-01-01 00:00:00','yyyy-mm-dd hh24:mi:ss')) TABLESPACE ts_infecard07,
PARTITION tbts08 VALUES LESS THAN(TO_DATE('2010-07-01 00:00:00','yyyy-mm-dd hh24:mi:ss')) TABLESPACE ts_infecard08,
PARTITION tbts09 VALUES LESS THAN(TO_DATE('2011-01-01 00:00:00','yyyy-mm-dd hh24:mi:ss')) TABLESPACE ts_infecard09,
PARTITION tbts10 VALUES LESS THAN(TO_DATE('2011-07-01 00:00:00','yyyy-mm-dd hh24:mi:ss')) TABLESPACE ts_infecard10,
PARTITION tbts11 VALUES LESS THAN(TO_DATE('2012-01-01 00:00:00','yyyy-mm-dd hh24:mi:ss')) TABLESPACE ts_infecard11,
PARTITION tbts12 VALUES LESS THAN(TO_DATE('2012-07-01 00:00:00','yyyy-mm-dd hh24:mi:ss')) TABLESPACE ts_infecard12,
PARTITION tbts13 VALUES LESS THAN(TO_DATE('2013-01-01 00:00:00','yyyy-mm-dd hh24:mi:ss')) TABLESPACE ts_infecard13,
PARTITION tbts14 VALUES LESS THAN(TO_DATE('2013-07-01 00:00:00','yyyy-mm-dd hh24:mi:ss')) TABLESPACE ts_infecard14,
PARTITION tbts15 VALUES LESS THAN(TO_DATE('2014-01-01 00:00:00','yyyy-mm-dd hh24:mi:ss')) TABLESPACE ts_infecard15
);

-- Create/Recreate primary, unique and foreign key constraints 
alter table EVT_INFECTIONCARD2
  add constraint PK_INFECTIONCARD2 primary key (CARD_ID)
  disable;
-- Create/Recreate indexes 
create index IDX_INTIMEED2 on EVT_INFECTIONCARD2 (INTIME)
  tablespace BHINFOSPACE
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 89M
    minextents 1
    maxextents unlimited
  );
-- Grant/Revoke object privileges 
grant select on EVT_INFECTIONCARD2 to ZJCH with grant option;

三、数据插入

insert into evt_infectioncard2
       (select Card_Id,Apanagecode,Card_Code,Patient_Name,Parent_Name,
               Sex1,Sex2,Telp,Id,Unit,Addr,Addrcode,Areatype,
                Start_Date,Diagnosedate,Deaddate,Casetype,
                Inputdoctor,Disease_Id1,Disease_Id2,Valid_Date2,
                Valid_Date1,Userid,Notes,Status,Birthday_Date,Filltime,
                Base_Id,Flag,Intime,Valid_Time5,Valid_Time4,Valid_Time3,
                Deletetime,Rptorgcode,After_Fill_Date,Contactflag,Currmgmtorg,
                Datasource,Mgmtstatus,Casetype2,Intime,
            case when valid_time5>intime then valid_time5
            when valid_time4>intime then valid_time4
            when valid_time3>intime then valid_time3
            when deletetime>intime then deletetime
            else intime end,
            Group_Id  from chinfectioncard
            where intime between to_date('2000-01-01 00:00:00','yyyy-MM-dd hh24:mi:ss') 
                           and to_date('2012-10-22 23:59:59','yyyy-MM-dd hh24:mi:ss') )

四、数据查询

select count(*) from evt_infectioncard2 t;
select count(*) from chinfectioncard;

select count(*) from evt_infectioncard2 partition(tbts01);
select count(*) from evt_infectioncard2 partition(tbts02);
select count(*) from evt_infectioncard2 partition(tbts03);
select count(*) from evt_infectioncard2 partition(tbts04);
select count(*) from evt_infectioncard2 partition(tbts05);
select count(*) from evt_infectioncard2 partition(tbts06);
select count(*) from evt_infectioncard2 partition(tbts07);
select count(*) from evt_infectioncard2 partition(tbts08);
select count(*) from evt_infectioncard2 partition(tbts09);
select count(*) from evt_infectioncard2 partition(tbts10);
select count(*) from evt_infectioncard2 partition(tbts11);
select count(*) from evt_infectioncard2 partition(tbts12);
select count(*) from evt_infectioncard2 partition(tbts13);
select count(*) from evt_infectioncard2 partition(tbts14);
select count(*) from evt_infectioncard2 partition(tbts15);