oracle 基本操作

--创建临时表空间
CREATE TEMPORARY TABLESPACE A01_temp
TEMPFILE 'D:\oracle\product\10.2.0\oradata\lhl\DB\A01_temp.dbf '
SIZE 32M
AUTOEXTEND ON
NEXT 32M MAXSIZE 2048M
EXTENT MANAGEMENT LOCAL;

--创建用户表空间
CREATE TABLESPACE A01_data
LOGGING
DATAFILE 'D:\oracle\product\10.2.0\oradata\lhl\DB\A01_data.DBF '
SIZE 32M
AUTOEXTEND ON
NEXT 32M MAXSIZE 2048M
EXTENT MANAGEMENT LOCAL;

--创建用户并制定表空间
CREATE USER A01 IDENTIFIED BY "1"
DEFAULT TABLESPACE A01_data
TEMPORARY TABLESPACE A01_temp;

--给用户授予(角色)权限 CONNECT,resource,DBA
GRANT CONNECT,resource TO A01;

drop USER A01

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


查看所有用户
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;