ORACLE 创建表空间、创建用户

创建临时表空间

CREATE   TEMPORARY  TABLESPACE test_temp TEMPFILE  ' C:\oracle\product\10.1.0\oradata\orcl\test_temp01.dbf '  SIZE 32M AUTOEXTEND  ON  NEXT  32M MAXSIZE 2048M EXTENT MANAGEMENT LOCAL;

创建用户表空间

CREATE  TABLESPACE test_data LOGGING DATAFILE  ' C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\TEST_DATA01.DBF '   SIZE 32M  AUTOEXTEND  ON   NEXT  32M MAXSIZE 2048M EXTENT MANAGEMENT LOCAL;

创建用户并制定表空间

CREATE   USER  test_user IDENTIFIED  BY  passwordDEFAULT  TABLESPACE TEST_DATATEMPORARY  TABLESPACE TEST_TEMP;

给用户授予权限

GRANT   　　 CREATE  SESSION,  CREATE   ANY   TABLE ,  CREATE   ANY   VIEW  , CREATE   ANY   INDEX ,  CREATE   ANY  PROCEDURE , 　　 ALTER   ANY   TABLE ,  ALTER   ANY   PROCEDURE , 　　 DROP   ANY   TABLE ,  DROP   ANY   VIEW ,  DROP   ANY   INDEX ,  DROP   ANY   PROCEDURE , 　　 SELECT   ANY   TABLE ,  INSERT   ANY   TABLE ,  UPDATE   ANY   TABLE ,  DELETE   ANY   TABLE  　　 TO  test_user ;

将role这个角色授与test_user ，也就是说，使test_user 这个用户可以管理和使用role所拥有的资源

GRANT  role  TO  test_user ;

-----------------------------------------------查看用户权限---------------------------------------------------------

查看所有用户

SELECT   *   FROM  DBA_USERS;SELECT   *   FROM  ALL_USERS;SELECT   *   FROM  USER_USERS;

查看用户系统权限

SELECT   *   FROM  DBA_SYS_PRIVS;SELECT   *   FROM  USER_SYS_PRIVS;

查看用户对象或角色权限

SELECT   *   FROM  DBA_TAB_PRIVS;SELECT   *   FROM  ALL_TAB_PRIVS;SELECT   *   FROM  USER_TAB_PRIVS; 

查看所有角色

SELECT   *   FROM  DBA_ROLES;

查看用户或角色所拥有的角色

SELECT   *   FROM  DBA_ROLE_PRIVS;SELECT   *   FROM  USER_ROLE_PRIVS;