oracle创建表空间、用户、授权

275 次访问 2020-08-04

东东

创建用户
create user dd134 identified by 123456;

修改用户密码
alter user dd134 identified by 123456;

oracle12c容器模式，cdb用户需要C##开头

查看用户表空间
select username,default_tablespace from dba_users;
创建表空间，表空间自增长，初始200M，每次自动扩展100M，最大扩展5G
create tablespace ddspace datafile ‘/opt/oracle/ddspace.dbf’ size 200M AUTOEXTEND ON NEXT 100M MAXSIZE 5G;
修改用户默认表空间
alter user dd134 default tablespace ddspace;
用户授权
grant connect,resource to dd134;
授予创建视图权限
grant create any view to dd134;

查询用户表空间限额
select * from dba_ts_quotas;

增加用户的表空间限额
alter user dd quota 500M on ddspace;

修改表空间限额-无限大
alter user dd134 quota unlimited on users;

跨用户授权

将dd1下xq4表的所有权限授予dd2

GRANT ALL ON dd.xq4 to dd2;

将dd1下xq4表的查询权限授予dd2

GRANT SELECT ON dd.xq4 to dd2;

11g授予resource角色会自动隐含授予unlimited tablespace权限，该权限可以操作任何表空间
12c则不会授予unlimited tablespace，需要分配表空间限额

删除用户
drop user dd134 cascade

–删除空的表空间，但是不包含物理文件
drop tablespace tablespace_name;
–删除非空表空间，但是不包含物理文件
drop tablespace tablespace_name including contents;
–删除空表空间，包含物理文件
drop tablespace tablespace_name including datafiles;
–删除非空表空间，包含物理文件
drop tablespace tablespace_name including contents and datafiles;
–如果其他表空间中的表有外键等约束关联到了本表空间中的表的字段，就要加上CASCADE CONSTRAINTS
drop tablespace tablespace_name including contents and datafiles CASCADE CONSTRAINTS;

回收角色（权限）
revoke connect,resource from dd134;

查询表空间文件
select * from dba_data_files;
表空间修改为自增长，每次扩展50M，无限大空间
alter database datafile ‘/opt/oracle/ddspace.dbf’ AUTOEXTEND ON NEXT 50M MAXSIZE UNLIMITED

权限、角色
connect
resource
create materialized 物化视图
create any view
execute on dbms_aqadm 高级队列

查询所有用户
select * from dba_users;

查询当前用户的角色
select * from user_role_privs;
查询所有用户的角色
select * from dba_role_privs;

查询当前用户系统权限
select * from user_sys_privs;

查看用户对象权限
select * from dba_tab_privs;
select * from all_tab_privs;
select * from user_tab_privs;

查询表空间使用率

SELECT a.tablespace_name,
       total,
       free,
       (total - free) "used",
       total / (1024 * 1024 * 1024) "total(G)",
       free / (1024 * 1024 * 1024) "free(G)",
       (total - free) / (1024 * 1024 * 1024) "used(G)",
       round((total - free) / total, 4) * 100 "used %"
  FROM (SELECT tablespace_name, SUM(bytes) free
          FROM dba_free_space
         GROUP BY tablespace_name) a,
       (SELECT tablespace_name, SUM(bytes) total
          FROM dba_data_files
         GROUP BY tablespace_name) b
 WHERE a.tablespace_name = b.tablespace_name

问题

ORA-01950: no privileges on tablespace ‘USERS’

现象：oracle 12c，虽然给用户指定了默认表空间，但写数据时出现 ORA-01950: no privileges on tablespace ‘USERS’ 报错。
原因：12c版本用户授予resource权限，并不会附带quota unlimited，11g及之前版本会附带。
解决方案：设置用户的表空间配额quota unlimited。

alter user DD quota unlimited on USERS;