学步园

创建表空间代码如下

--创建生产库表空间
CREATE TABLESPACE FE DATAFILE
  '/opt/oracle/product/10.2.0/db_1/dbs/FE.dbf' SIZE 2048 m AUTOEXTEND ON NEXT 5 m MAXSIZE 20480
                                                                                               m
LOGGING
ONLINE
PERMANENT
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8 k
SEGMENT SPACE MANAGEMENT MANUAL;

--创建索引表空间
CREATE TABLESPACE FEIDX DATAFILE 
  '/opt/oracle/oradata/DEV/FEIDX.dbf' SIZE 2048M AUTOEXTEND ON NEXT 4M MAXSIZE UNLIMITED
LOGGING
ONLINE
PERMANENT
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO;

--创建全文索引表空间
CREATE TABLESPACE fefulltxt DATAFILE
  '/opt/oracle/oradata/DEV/FEFULLTXT.dbf' SIZE 2048 m AUTOEXTEND ON NEXT 4 m MAXSIZE UNLIMITED
LOGGING
ONLINE
PERMANENT
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8 k
SEGMENT SPACE MANAGEMENT AUTO;

上面的 创建索引表空间和 创建全文索引表空间我在创建表空间的时候删除了 因为是copy 同事的东西所以就放上来了

创建用户并且指定 表空间等操作

--创建fe用户并分配表空间、角色、权限、授权程序
CREATE USER fe
  IDENTIFIED BY VALUES '0A8713C2E93BC510'
  DEFAULT TABLESPACE fe
  TEMPORARY TABLESPACE temp
  PROFILE DEFAULT
  ACCOUNT UNLOCK;
  -- 3 Roles for CYTS 
GRANT   ctxapp TO fe WITH ADMIN OPTION;
GRANT   CONNECT TO fe WITH ADMIN OPTION;
GRANT   RESOURCE TO fe WITH ADMIN OPTION;
ALTER   USER cyts DEFAULT ROLE RESOURCE;
  -- 5 System Privileges for CYTS 
GRANT   CREATE VIEW TO fe;
GRANT   CREATE MATERIALIZED VIEW TO fe;
GRANT   CREATE SESSION TO fe;
GRANT   CREATE TRIGGER TO fe;
GRANT   UNLIMITED TABLESPACE TO fe WITH ADMIN OPTION;
  -- 3 Tablespace Quotas for CYTS 
ALTER   USER cyts QUOTA UNLIMITED ON feidx;
ALTER   USER cyts QUOTA UNLIMITED ON fe;
ALTER   USER cyts QUOTA UNLIMITED ON fefulltxt;
  -- 2 Object Privileges for CYTS 
GRANT     EXECUTE ON  ctxsys.ctx_ddl TO fe;

--修改密码
ALTER USER FE IDENTIFIED BY "FE";

公司中的实际例子

CREATE TABLESPACE turbocms2009 DATAFILE 'D:\oracle\product\10.2.0\oradata\ecoalchina\TURBOCMS2009.DBF' SIZE 2048 m AUTOEXTEND ON NEXT 5 m MAXSIZE 20480 m
LOGGING
ONLINE
PERMANENT
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8 k
SEGMENT SPACE MANAGEMENT MANUAL;



--创建 turbocms2009 用户并分配表空间、角色、权限、授权程序
CREATE USER turbocms2009
  IDENTIFIED BY VALUES '0A8713C2E93BC510'
  DEFAULT TABLESPACE turbocms2009
  TEMPORARY TABLESPACE temp
  PROFILE DEFAULT
  ACCOUNT UNLOCK;
  -- 3 Roles for turbocms2009 
GRANT   ctxapp TO turbocms2009 WITH ADMIN OPTION;
GRANT   CONNECT TO turbocms2009 WITH ADMIN OPTION;
GRANT   RESOURCE TO turbocms2009 WITH ADMIN OPTION;
ALTER   USER turbocms2009 DEFAULT ROLE RESOURCE;
  -- 5 System Privileges for turbocms2009 
GRANT   CREATE VIEW TO turbocms2009;
GRANT   CREATE MATERIALIZED VIEW TO turbocms2009;
GRANT   CREATE SESSION TO turbocms2009;
GRANT   CREATE TRIGGER TO turbocms2009;
GRANT   UNLIMITED TABLESPACE TO turbocms2009 WITH ADMIN OPTION;
  -- 3 Tablespace Quotas for turbocms2009 
ALTER   USER turbocms2009 QUOTA UNLIMITED ON turbocms2009;



--修改密码
ALTER USER turbocms2009 IDENTIFIED BY "turbocms2009";