Oracle之tablespace简单操作

①创建表空间

CREATE TABLESPACE tbs_02 
   DATAFILE 'diskb:tbs_f5.dat' SIZE 100M
   AUTOEXTEND ON NEXT 100M MAXSIZE 2000M;

 

②修改表空间

alter database datafile '/vol01/app/oracle/oradata/rms/sysaux01.dbf' autoextend on next 100M maxsize 3000M;

 

 ③添加表空间文件

alter tablespace SYSAUX add datafile '/vol01/app/oracle/oradata/rms/sysaux02.dbf' 
size 100M autoextend on next 10M maxsize 2000M;

 

④添加分区

1，

create tablespace g200910 datafile 'D:\oracle\product\10.1.0\oradata\BMPS2\g200910.dbf' size 100m 
      autoextend on next 100m maxsize 3000m;
create tablespace g200911 datafile 'D:\oracle\product\10.1.0\oradata\BMPS2\g200911.dbf' size 100m 
      autoextend on next 100m maxsize 3000m;

 

 2，

-- Create table
create table BMPS_HIS_RECEIVE_GPSINFO
(
  SEQUENCE      NUMBER(32) not null,
  MCUID         NUMBER(10) not null,
  DEVICENO      VARCHAR2(8) not null,
  SIMNUM        VARCHAR2(16) not null,
  BLUETOOTHID   VARCHAR2(12),
  CMDTYPE       NUMBER(3) not null,
  CMDSTATUS     NUMBER(3) not null,
  VEHICLESTATUS VARCHAR2(20),
  LONGITUDE     NUMBER(10) not null,
  LATITUDE      NUMBER(10) not null,
  ANGLE         NUMBER(5) not null,
  VELOCITY      NUMBER(5) not null,
  FLAG          NUMBER(3) not null,
  LOCATESTATE   NUMBER(3) not null,
  POSITIONTIME  DATE not null,
  RECEIVETIME   DATE not null,
  SPEEDOMETER   NUMBER(10),
  RESERVE       NUMBER(5)
)
partition by range(POSITIONTIME)
(
　　partition g200910 values less than (date'2009-11-01') tablespace g200910,
　　partition g200911 values less than (date'2009-12-01') tablespace g200911,
　　partition g200912 values less than (date'2010-01-01') tablespace g200912,
　　partition g201001 values less than (date'2010-02-01') tablespace g201001,
　　partition g201002 values less than (date'2010-03-01') tablespace g201002,
　　partition g201003 values less than (date'2010-04-01') tablespace g201003,
　　partition g201004 values less than (date'2010-05-01') tablespace g201004,
　　partition g201005 values less than (date'2010-06-01') tablespace g201005,
　　partition g201006 values less than (date'2010-07-01') tablespace g201006,
　　partition g201007 values less than (date'2010-08-01') tablespace g201007,
　　partition g201008 values less than (date'2010-09-01') tablespace g201008,
　　partition g201009 values less than (date'2010-10-01') tablespace g201009,
　　partition g201010 values less than (date'2010-11-01') tablespace g201010,
　　partition g201011 values less than (date'2010-12-01') tablespace g201011,
　　partition g201012 values less than (date'2011-01-01') tablespace g201012,
　　partition g201101 values less than (date'2011-02-01') tablespace g201101,
　　partition g201102 values less than (date'2011-03-01') tablespace g201102,
　　partition g201103 values less than (date'2011-04-01') tablespace g201103,
　　partition g201104 values less than (date'2011-05-01') tablespace g201104,
　　partition g201105 values less than (date'2011-06-01') tablespace g201105,
　　partition g201106 values less than (date'2011-07-01') tablespace g201106,
　　partition g201107 values less than (date'2011-08-01') tablespace g201107,
　　partition g201108 values less than (date'2011-09-01') tablespace g201108,
　　partition g201109 values less than (date'2011-10-01') tablespace g201109,
　　partition g201110 values less than (date'2011-11-01') tablespace g201110,
　　partition g201111 values less than (date'2011-12-01') tablespace g201111,
　　partition g201112 values less than (date'2012-01-01') tablespace g201112
)
tablespace BMPSSPACE
  pctfree 10
  pctused 40
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );
-- Add comments to the columns 
comment on column BMPS_HIS_RECEIVE_GPSINFO.SPEEDOMETER
  is '里程数';
comment on column BMPS_HIS_RECEIVE_GPSINFO.RESERVE
  is '保留字';
-- Create/Recreate primary, unique and foreign key constraints 
alter table BMPS_HIS_RECEIVE_GPSINFO
  add constraint BMPS_HIS_RECEIVE_GPSINFO primary key (SEQUENCE)
  using index 
  tablespace BMPSSPACE
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );
-- Create/Recreate indexes 
create index INDEX1 on BMPS_HIS_RECEIVE_GPSINFO (MCUID)
  tablespace BMPSSPACE
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );