Oracle基础

2019-05-07

Oracle常用命令

查看当前连接用户

show user;

查看全局数据库名与SID

SELECT * FROM global_name;
SELECT instance_name FROM v$instance;

清空屏幕

cl scr ;

更改用户登录

conn 用户名/密码 [ AS SYSDBA ]

注意：如果是超级管理员（SYS），必须写上 AS SYSDBA

表空间

​ 表空间是Oracle最大的逻辑存储结构，用于存储用户在数据库中创建的所有内容。它与物理上的一个或多个数据文件相对应，每个Oracle数据库都拥有多个表空间。(oracle只有一个数据库，表空间相当于mysql的数据库)

在安装Oracle时，Oracle系统会自动创建一系列表空间。可以通过数据字典dba_tablespaces查看表空间的信息。

SELECT tablespace_name FROM dba_tablespaces ; 

表 空 间	说 明
system	系统表空间，用于存储系统的数据字典、系统的管理信息和用户数据表等
sysaux	辅助系统表空间。用于减少系统表空间的负荷，提高系统的作业效率。该表空间由Oracle系统内部自动维护，一般不用于存储用户数据
temp	临时表空间。用于存储临时的短期活动的数据，例如SQL排序时产生的临时数据。数据库中的所有用户都使用temp作为默认的临时表空间。临时表空间本身是永久存在的，只是保存在临时表空间中的段是临时的。临时段在实例关闭时被释放
undotbs1	撤消表空间。用于回退未提交的事务数据。 在撤消表空间中，除了回退段以外，不能建立任何其他类型的段。所以，用户不可以在撤消表空间中创建任何数据库对象
users	用户表空间。用于存储永久性用户对象和私有信息
example	实例表空间。用于存储演示的实例

查看用户默认表空间

SELECT username,default_tablespace FROM dba_users;

查看指定表空间存储的所有表信息

SELECT table_name FROM dba_tables WHERE tablespace_name= '表空间名称' ; //注意：表空间名称必须大写

创建表空间

create tablespace 表空间名称 datafile '数据文件路径及名称' size 数据文件大小

create tablespace mytablespace datafile 'D:\APP\ADMINISTRATOR\ORADATA\ORCL\mytablespace.dbf' 
size 100m autoextend on next 50m 

删除表空间

drop tablespace 表空间名称 [including contents and datafiles];

说明：including contents and datafiles表示删除表空间时把表空间的数据文件也删除。如果不加这个参数：那么只删除了表空间，而数据文件还存在的，即磁盘空间没有释放。

用户管理

创建用户

创建新用户由DBA执行，其他身份创建用户必须要有create user系统权限。

create user 用户名 identified by 密码
      [default tablespace 默认表空间名称]
      [quota 表空间配额数值[K|M]|unlimited on默认表空间名称]
      [password expire]
      
create user user1 identified by 123456 default tablespace orcltable quota 100m on orcltable; 
create user user1 identified by 123456 default tablespace orcltable quota unlimited on orcltable; 

注意事项：

初始建立的用户没有任何权限。为了使用户可以连接到数据库，必须授予create session权限。

grant create session to developer ;

如果建立用户时不指定default tablespace子句，Oracle会将USERS表空间作为用户的默认表空间。

如果建立用户时没有为表空间指定quota子句，则用户在表空间上的配额为0，不能在表空间上建立数据库对象。

password expire表示该用户密码过期需要重设密码,当用这个新建立的密码过期用户首次登录后，系统会提示重新输入新密码，不然会拒绝登陆，重设新密码后就可以登陆，该用户的密码就是新设的密码，相当于首次登陆修改密码选项。

修改密码

普通用户可以修改自身密码，DBA可以修改任何用户的密码。

alter user 用户名 identified by 新密码

alter user developer identified by 123456 ; 

修改表空间配额

alter user 用户名 quota 配额大小 on 表空间名称

alter user developer quota 200m on orcltable ;

锁定用户账户

alter user 用户名 account lock ; 

alter user developer account lock ;

解锁用户账户

alter user 用户名 account unlock ;

alter user developer account unlock ; 

修改用户默认表空间

alter user 用户名 default tablespace 表空间名称

alter user developer default tablespace users ; 

注意：修改默认表空间后，先前已创建的表仍然存储在原表空间中。如果再创建数据对象，则存储在新的表空间中。

删除用户

删除用户时，系统会将该用户账号以及用户模式信息从数据字典中删除。用户被删除后，其创建的所有数据库对象也被全部删除。如果用户当前正连接到数据库，则不能删除该用户。

drop user 用户名 ;

注意：安全的做法是为用户加锁，而不是删除用户。

权限和角色管理

权限是指执行特定的操作的权利。Oracle的权限可以分成两类：系统权限和对象权限。
注意：刚建立的用户没有任何权限，不能执行任何操作。

系统权限

​ 是指对整个Oracle系统的操作权限。例如当用户具有create table权限时，可以在其方案中创建表；当用户具有create any table权限时，可以在任何方案中创建表。

查看所有系统权限

conn system/password
SELECT * FROM system_privilege_map ; 

查看当前用户拥有的系统权限

SELECT * FROM session_privs ;

系统权限的授权

一般由DBA完成，若要以其他用户身份授权，必须在相应系统权限上具有with admin option功能。

grant 系统权限1,系统权限2…系统权限n  to 用户名 [ with admin option ]

grant create session , create table to developer with admin option ;
//with admin option选项表示用户可以将权限授予其他用户。

收回系统权限

一般由DBA完成，若要以其他用户身份授权，必须在相应系统权限上具有with admin option功能。

revoke 系统权限1,系统权限2…系统权限n  from 用户名 ;

revoke create session , create table from developer ; 

对象权限

​ 在oracle中，每个用户都对应一个方案，对象权限是指当前用户访问其他用户方案对象的权利。例如HR用户要访问SCOTT方案中的EMP表，必须在SCOTT的EMP表上具有对象权限。

角色管理

Oracle的权限非常繁多，为每个用户授权工作量非常大。为简化权限管理，Oracle提供了角色的概念。

角色是一组相关权限的组合。可以使用角色直接对用户授权。

获取数据库中全部角色信息

SELECT role FROM dba_roles;

几个基本角色

connect角色

具有一般应用开发人员需要的大部分权限

 ALTER SESSION --修改会话
 CREATE CLUSTER --建立聚簇
 CREATE DATABASE LINK --建立数据库链接
 CREATE SEQUENCE --建立序列
 CREATE SESSION --建立会话
 CREATE SYNONYM --建立同义词
 CREATE VIEW --建立视图

resource角色

具有应用开发人员所需要的其它权限。

 CREATE CLUSTER --建立聚簇
 CREATE PROCEDURE --建立过程
 CREATE SEQUENCE --建立序列
 CREATE TABLE --建表
 CREATE TRIGGER --建立触发器
 CREATE TYPE --建立类型

dba角色

具有所有系统权限，默认的dba用户为sys和system，它们可以将任何系统权限授予其他用户。但是要注意的是dba角色不具备sysdba和sysoper的特权，而sysdba特权自动具有dba角色所有的权限。

将角色授权给用户

一般开发工作用户只要授予connect和resource角色即可。

grant 角色名 to 用户名;

grant connect,resource to developer ; 

用户角色查询

SELECT granted_role FROM dba_role_privs where grantee= 'SCOTT';

建立自定义角色

​ 创建角色使用create role语句完成，一般由DBA执行，如果要以其他用户身份建立角色，需要该用户具有create role系统权限。

create role 角色名称 [ not identified ] 

create role public_role  not identified; 

not identified选项指定非验证方式，激活角色时无须口令。

自定义角色初始没有任何权限，为了使角色起作用，可以为其授予相应的权限。

删除角色

​ 删除角色使用drop role语句完成。一般由DBA执行，如果要以其他用户身份删除角色，需要该用户具有drop any role系统权限，或者在角色上具有with admin option选项。

drop role public_role ;