oracle命名空间和用户的基本操作

一、命名空间

1.创建

create tablespace QT datafile 
  '/home/oracle/QT.dbf' size 200m autoextend on next 10m maxsize unlimited
logging
online
permanent
extent management local autoallocate
blocksize 8k;

2.删除

DROP TABLESPACE QT INCLUDING CONTENTS AND DATAFILES;

二、用户

1.创建

create user QT identified by 123456 default tablespace QT;

2.赋权

1）赋权

grant connect,resource to QT;
grant create any table to QT;
grant delete any table to QT;
grant insert any table to QT;
grant select any table to QT;
grant update any table to QT;
grant unlimited tablespace to QT;
grant create any sequence to QT;
grant create any view to QT;
grant execute any procedure to QT;

2）查看权限

select * from user_sys_privs;//查看当前用户所有权限

select * from user_tab_privs;//查看所用用户对表的权限

3）收回权限

基本语法同grant,关键字为revoke

4）角色

　　角色即权限的集合，可以把一个角色授予给用户

create role myrole;//创建角色

grant create session to myrole;//将创建session的权限授予myrole

grant myrole to zhangsan;//授予zhangsan用户myrole的角色

drop role myrole;删除角色

3.删除

drop user QT cascade;