oracle 表空间-用户-授权-表创建

一、创建表空间

创建表空间为：MDMS_TS_File ，数据文件为：MDMS_TS_File.DBF ，指定存放路径为：'/oradata1/ ，大小为：2000M

CREATE TABLESPACE MDMS_TS_File DATAFILE '/oradata1/MDMS_TS_File.DBF' SIZE 2000M AUTOEXTEND ON NOLOGGING;

二、创建用户

CREATE USER viva1 IDENTIFIED BY viva1 DEFAULT TABLESPACE MDMS_TS_File TEMPORARY TABLESPACE TEMP;

三、授权

GRANT CREATE USER,DROP USER,ALTER USER ,CREATE ANY VIEW ,DROP ANY VIEW,EXP_FULL_DATABASE,IMP_FULL_DATABASE,CONNECT,RESOURCE,CREATE SESSION  TO viva1; GRANT ALTER TABLESPACE TO VIVA1;

四、创建表

create table zjsm_test1( OriginTime varchar(50), DeviceId number(15), ProgranId number(15), Title varchar2(50), callsign varchar2(50), channelNumber number(15), channeltype varchar2(50), Duration number(15) )

tablespace MDMS_TS_File   pctfree 10   initrans 1   maxtrans 255   storage   (     initial 2M     next 2M     minextents 1     maxextents unlimited   );

五、分区表创建

-- Create table
create table zjsm_test1(
OriginTime varchar(50),
DeviceId number(15),
ProgranId number(15),
Title varchar2(50),
callsign varchar2(50),
channelNumber number(15),
channeltype varchar2(50),
Duration number(15)
)

--创建分区表空间
partition by range (CSTART_TIME)
(
  partition MDMS_P_SEC_20120601_1 values less than (TO_DATE(' 2012-06-01 14:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace MDMS_TS_File
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 64K
      next 1M
      minextents 1
      maxextents unlimited
    ),
  partition MDMS_P_SEC_20120601_2 values less than (TO_DATE(' 2012-06-02 02:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace MDMS_TS_File
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 64K
      next 1M
      minextents 1
      maxextents unlimited
    ),
  partition P_DEFAULT values less than (MAXVALUE)
    tablespace MDMS_TS_OTHER
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 128K
      next 128K
      minextents 1
      maxextents unlimited
    )

 );