oracle 创建用户，表空间，赋权

1、先创建表空间

--查看表空间数据文件存放位置

 SELECT * FROM DBA_DATA_FILES;

--根据查看到的实际位置修改临时数据文件及数据文件的位置

--创建临时表空间

CREATE TEMPORARY TABLESPACE soademo_tmp
TEMPFILE '/soaapp/db/app/soadb/oradata/db11g/soademo_tmp.dbf'
SIZE 32M
AUTOEXTEND ON
NEXT 32M MAXSIZE 2048M
EXTENT MANAGEMENT LOCAL;

-- //创建数据表空间
-- DROP TABLESPACE soademo_data;
CREATE TABLESPACE soademo_data
LOGGING
DATAFILE '/soaapp/db/app/soadb/oradata/db11g/soademo_data.dbf'
SIZE 32M
AUTOEXTEND ON
NEXT 32M MAXSIZE 2048M
EXTENT MANAGEMENT LOCAL;

2、创建用户并指定表空间

CREATE USER soademo IDENTIFIED BY soademo
    DEFAULT TABLESPACE soademo_data
    TEMPORARY TABLESPACE soademo_tmp;

3、一般情况赋予如下权利

   grant connect, resource to soademo identified by soademo;

4、如果要拥有与dba的权限，如下赋值

 --GRANT/REVOKE SYSTEM PRIVILEGES

 GRANT SELECT ANY TABLE TO soademo;
    GRANT ALTER ANY MATERIALIZED VIEW TO soademo;
    GRANT ALTER ANY PROCEDURE TO soademo;
    GRANT ALTER ANY SEQUENCE TO soademo;
    GRANT ALTER ANY TABLE TO soademo;
    GRANT ALTER ANY TRIGGER TO soademo;
    GRANT ALTER SESSION TO soademo;
    GRANT ALTER SYSTEM TO soademo;
    GRANT ALTER TABLESPACE TO soademo;
    GRANT ANALYZE ANY TO soademo;
    GRANT COMMENT ANY TABLE TO soademo;
    GRANT CREATE ANY DIRECTORY TO soademo;
    GRANT CREATE ANY INDEX TO soademo;
    GRANT CREATE ANY MATERIALIZED VIEW TO soademo WITH ADMIN OPTION;
    GRANT CREATE ANY SEQUENCE TO soademo;
    GRANT CREATE ANY SYNONYM TO soademo WITH ADMIN OPTION;
    GRANT CREATE ANY TABLE TO soademo;
    GRANT CREATE ANY TRIGGER TO soademo;
    GRANT CREATE MATERIALIZED VIEW TO soademo WITH ADMIN OPTION;
    GRANT CREATE PROCEDURE TO soademo WITH ADMIN OPTION;
    GRANT CREATE PUBLIC DATABASE LINK TO soademo WITH ADMIN OPTION;
    GRANT CREATE PUBLIC SYNONYM TO soademo WITH ADMIN OPTION;
    GRANT CREATE ROLE TO soademo;
    GRANT CREATE SESSION TO soademo;
    GRANT CREATE SYNONYM TO soademo;
    GRANT CREATE TABLE TO soademo;
    GRANT CREATE TRIGGER TO soademo;
    GRANT CREATE VIEW TO soademo WITH ADMIN OPTION;
    GRANT DEBUG ANY PROCEDURE TO soademo;
    GRANT DEBUG CONNECT SESSION TO soademo;
    GRANT DELETE ANY TABLE TO soademo;
    GRANT DEQUEUE ANY QUEUE TO soademo;
    GRANT DROP ANY INDEX TO soademo;
    GRANT DROP ANY MATERIALIZED VIEW TO soademo;
    GRANT DROP ANY PROCEDURE TO soademo WITH ADMIN OPTION;
    GRANT DROP ANY SEQUENCE TO soademo;
    GRANT DROP ANY SYNONYM TO soademo WITH ADMIN OPTION;
    GRANT DROP ANY TABLE TO soademo;
    GRANT DROP ANY TRIGGER TO soademo;
    GRANT DROP ANY VIEW TO soademo WITH ADMIN OPTION;
    GRANT DROP PUBLIC DATABASE LINK TO soademo;
    GRANT DROP PUBLIC SYNONYM TO soademo WITH ADMIN OPTION;
    GRANT ENQUEUE ANY QUEUE TO soademo;
    GRANT EXECUTE ANY PROCEDURE TO soademo WITH ADMIN OPTION;
    GRANT GLOBAL QUERY REWRITE TO soademo;
    GRANT INSERT ANY TABLE TO soademo;
    GRANT LOCK ANY TABLE TO soademo;
    GRANT MANAGE ANY QUEUE TO soademo;
    GRANT ON COMMIT REFRESH TO soademo;
    GRANT SELECT ANY DICTIONARY TO soademo WITH ADMIN OPTION;
    GRANT SELECT ANY SEQUENCE TO soademo;
    GRANT SELECT ANY TABLE TO soademo WITH ADMIN OPTION;
    GRANT UNLIMITED TABLESPACE TO soademo;
    GRANT UPDATE ANY TABLE TO soademo;