07. Oracle企业管理

• Oracle企业管理方式

1. 通过OEM(Oracle Enterprise Manager)Oracle企业管理界面进行管理,首先要启动相关服务,在浏览器输入https://hostname:port/em进行登录管理
2. 使用sql语句直接进行创建管理
3. 创建表空间和数据文件,创建用户并授权,创建表及约束条件,创建表与表之间的关系

---

• 表空间

1. 在表空间默认不区分大小写,所有的最终都会转换成大写进行处理
2. 表空间:
   表空间名:TEST0402
   创建方式:小文件方式
3. 数据文件:
   路径:自行选择
   数据文件名:一般为 表空间名.DBF
   初始大小:此处设置为100M
   数据文件满后自动拓展:设置自动拓展幅度(值通常为初始文件的10%)
   最大文件大小:无限制

 CREATE SMALLFILE TABLESPACE "TEST0402"
 DATAFILE 'D:\APP\ORACLE\PRODUCT\11.2.0\DBHOME_2\ORADATA\ORCL\TEST0402.DBF' SIZE 100M
 AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED 
 LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO

4. 在SQL窗口中执行即可

image.png

5. 创建完成,可通过以下sql查看基本信息

 select * from dba_tablespaces t where t.tablespace_name = 'TEST0402';--表空间
 select * from dba_data_files t where t.tablespace_name = 'TEST0402';--数据文件
 select * from dba_free_space t where t.tablespace_name = 'TEST0402';--未使用的表空间

image.png

6. 创建临时表空间(数据库本身存在临时表空间,因有时无法满足需求,故可以手动创建,主要用途是提供临时的运算空间，当运算完成之后系统会自动清理)

 CREATE TEMPORARY TABLESPACE "TEMP0402"
 TEMPFILE 'D:\APP\ORACLE\PRODUCT\11.2.0\DBHOME_2\ORADATA\ORCL\TEMP0402.DBF' SIZE 50M
 AUTOEXTEND ON NEXT 5M MAXSIZE 2048M 
 EXTENT MANAGEMENT LOCAL;

image.png

7. 删除表空间

DROP 表空间名 INCLUDING CONTENTS AND DATAFILES;

---

• 用户

1. 系统安装完成时,只有sys和system两个用户,为安全和使用考虑,需要为表空间创建用户
2. 一个表空间允许多个用户访问,而一个用户通常只能访问一个表空间

创建用户

1. 创建用户并指定默认表空间和临时表空间(修改用ALTER语句即可)
   其中:
   用户名:USER0402
   密码:AAaa1234
   默认表空间:TEST0402
   临时表空间:TEMP0402

CREATE USER USER0402 IDENTIFIED BY AAaa1234
DEFAULT TABLESPACE TEST0402
TEMPORARY TABLESPACE TEMP0402;

2. 查看新建的用户

select t.* from dba_users t where t.username = 'USER0402';--用户
select t.* from all_users t where t.username = 'USER0402';--用户
select t.* from user_users t;--当前用户

image.png

3. 删除用户

DROP USER 用户名
若用户包含对象,则先删除用户所有的对象,再删除用户,如下:
DROP USER 用户名 CASCADE

---

• 角色

1. oracle标准角色:
   DBA: 拥有全部特权,是系统最高权限,只有DBA才可以创建数据库结构,超级系统管理员
   RESOURCE:拥有Resource权限的用户只可以创建实体,不可以创建数据库结构,普通用户
   CONNECT:拥有Connect权限的用户只可以登录Oracle,不可以创建实体,不可以创建数据库结构,临时用户
2. 创建角色
   角色名:ROLE0403

CREATE ROLE ROLE0403;

3. 查看新建的角色

select t.* from dba_roles t where t.role = 'ROLE0403';--所有角色

image.png

4. 删除角色

DROP ROLE 角色名;

---

• 权限

1. 权限分为系统权限和对象权限,系统权限即系统级别的操作,对象权限又称为表级权限,即对表的操作
2. 如果角色拥有管理选项,则此用户可将自己的权限赋给其他的用户
3. 权限可直接赋予用户,也可赋予角色,再将角色赋予用户(推荐),同时多个权限或角色,中间用逗号分割
4. 查看系统所有权限

select t.* from system_privilege_map t;--所有权限

image.png

5. 赋予角色系统权限
   其中:将查询所有表的权限赋给了角色ROLE0403

GRANT SELECT ANY TABLE TO ROLE0403;
select t.* from role_sys_privs t where t.role = 'ROLE0403';--角色拥有的系统权限

image.png

6. 赋予角色对象权限
   其中:将查询表INCFIL的权限赋予了角色ROLE0403

GRANT SELECT ON INCFIL TO ROLE0403;
select t.* from role_tab_privs t where t.role = 'ROLE0403';--角色拥有的对象权限

image.png

7. 赋予用户系统权限
   其中:将查询所有字典的权限赋予用户USER0402

GRANT SELECT ANY DICTIONARY TO USER0402;

image.png

8. 赋予用户对象权限
   其中:将查询表INCVID的权限赋予了角色USER0402

GRANT SELECT ON INCVID TO USER0402;
select t.* from dba_tab_privs t where t.grantee = 'USER0402';--所有用户拥有的对象权限

image.png

9. 赋予用户角色
   其中:将角色ROLE0403赋给用户USER0402
   用户拥有角色后,即拥有其权限

GRANT ROLE0403 TO USER0402;

image.png

10. 撤销权限

REVOKE 角色/权限 FROM 用户名/角色

11. 权限相关SQL

--所有权限
select t.* from system_privilege_map t;--所有权限
--系统权限
select t.* from dba_sys_privs t where t.grantee = 'USER0402';--所有用户的系统权限
select t.* from user_sys_privs t;--当前用户的系统权限
--对象权限
select t.* from dba_tab_privs t where t.grantee = 'USER0402';--所有用户拥有的对象权限
select t.* from all_tab_privs t;--用户拥有的对象权限
select t.* from user_tab_privs t;--用户拥有的对象权限
--权限分配
select t.* from user_tab_privs_made t;--用户授出的对象权限
select t.* from user_tab_privs_recd t;--用户接受的对象权限
select t.* from user_col_privs_made t;--用户授出的对列的对象权限
select t.* from user_col_privs_recd t;--用户接受的对列的对象权限
--用户角色关系
select t.* from dba_role_privs t where t.grantee = 'USER0402';--所有用户拥有的角色
select t.* from user_role_privs t;--当前用户拥有的角色
--角色权限关系
select t.* from role_sys_privs t where t.role = 'ROLE0403';--角色拥有的系统权限
select t.* from role_tab_privs t where t.role = 'ROLE0403';--角色拥有的对象权限

---

• 表

1. 表是数据库中的最基本的对象,用于存储数据,没有表的数据库没有任何意义
2. 设计合理的表能大幅提升数据库查询性能,数据组织合理,便于开发数据库代码,节省数据存储空间
3. 表的设计,一般按照数据库设计范式进行

创建表:

1. 其中:
   表名:STUDENT
   所属用户:USER0402
   列名和数据类型为()内容
   所属表空间:TEST0402

CREATE TABLE USER0402.STUDENT
(
  SID CHAR(6),
  SNAME VARCHAR2(10),
  SSEX CHAR(1),
  SAGE INTEGER,
  SPHONE VARCHAR2(12)
)
TABLESPACE TEST0402;

2. 查看创建结果

select t.*,t.rowid from user0402.student t;

image.png

3. 数据类型

数据类型	释义
VARCHAR2(n)	1. n为必填参数,指定可变字符串的长度 2. n的最大值是4000,最小值是1
NVARCHAR2(n)	1. 其基本用法以及范围同varchar2类型 2. 此可变字符串,依据所选国家的字符集
NUMBER(p,s)	1. 精度为p,数值范围为s的数值 2. 精度p的范围从1到38,数值范围s的范围是从-84到127 3. 有效位为从左边第一个不为0的数开始,到右边四舍五入位结束 4. s表示取值范围,从小数点为0点,向右(正数)表示小数保留位数,向左(负数)表示整数保留四舍五入的开始位 5. p表示有效位的最大位数
NUMBER	等同于NUMBER(5)
LONG	可变长度的字符数据,长度最大2G个字节
DATE	有效日期范围从公元前4712年1月1日到公元后4712年12月31日
RAW(n)	1. n为必填参数,指定原始二进制数据的最大长度 2. n的最大值为2000字节
LONG RAW	可变长度的原始二进制数据,最大长度为2G字节
CHAR(n)	1. 固定长度的字符数据,最大长度为n 2. n的最大值为2000,最小值为1
NCHAR(n)	与CHAR类型用法一致,且根据Unicode标准定义
CLOB	字符大型对象,可容纳单字节字符,不支持宽度不等的字符集,最大为4G字节
NCLOB	与CLOB用法一致,且可存储国家字符集
BLOB	二进制大型对象,最大4G字节
BFILE	包含一个大型二进制文件的定位器,存储在数据库外面,使得可以以字符流I/O访问存在数据库服务器上的外部LOB,最大为4G字节

---

• 约束

约束即对列值的条件,但约束在一定程度上会降低数据库性能
非空约束:

1. 非空约束即not null,不允许列的值为空(有default值例外)
2. 其中:为表USER0402.STUDENT的SNAME列添加非空约束

ALTER TABLE USER0402.STUDENT MODIFY SNAME NOT NULL;
select t.* from dba_constraints t where t.table_name like '%STUDENT%';--查看所有约束

image.png

主键约束:

1. 主键约束即primary key,用来标识行的唯一性
2. 设置为主键的列将会自动被创建索引
3. 主键不允许为空值
4. 主键约束可以定义在一个列上,也可以定义在多个列的组合上,但一个表中只包括一个主键
5. 其中:为表USER0402.STUDENT添加主键名为STUDENT_SID,定义在表的SID列上

ALTER TABLE USER0402.STUDENT ADD CONSTRAINT STUDENT_SID PRIMARY KEY(SID);
select t.* from dba_constraints t where t.table_name like '%STUDENT%';--所有约束

image.png

6. 主键默认创建索引,查看索引

select t.* from dba_indexes t where t.table_name like '%STUDENT%';

image.png

唯一约束:

1. 唯一约束即unique,保证每一行的唯一性
2. 同主键一样,唯一约束也是通过索引实现的,故若所约束字段不存在索引,将自动创建索引
3. 唯一约束可存在null值,且可存在多个null值,在查表时,因btree索引不包括null值,故判断null值会进行全表扫描
4. 一个表可存在多个唯一约束
5. 其中:为表USER0402.STUDENT添加名为STUDENT_SPHONE的唯一约束,定义在表的SPHONE列上

ALTER TABLE USER0402.STUDENT ADD CONSTRAINT STUDENT_SPHONE UNIQUE(SPHONE);
select t.* from dba_constraints t where t.table_name like '%STUDENT%';--所有约束

image.png

6. 查看约束

select t.* from dba_indexes t where t.table_name like '%STUDENT%';

image.png

外键约束:
外键约束定义在具有父子关系的子表中，外键约束使得子表中的列对应父表的主键列，用以维护数据库的完整性。不过出于性能和后期的业务系统的扩展的考虑，很多时候，外键约束仅出现在数据库的设计中，实际会放在业务程序中进行处理

1. 外键约束的子表中的列和对应父表中的列数据类型必须相同，列名可以不同
2. 对应的父表的列必须存在主键约束()PRIMARY KEY)或唯一约束(UNIQUE)
3. 外键约束列允许NULL值
4. 有外键删除的表,在删除记录时,除非进行强制删除或级联删除,否则不允许删除
5. 其中:为表USER0402.SCORE添加外键约束,名为STUDENT_SCORE_SID,添加列为SID,关联父列为USER0402.STUDENT的SID列

方式一:删除的时候,级联删除子表所有匹配的行
ALTER TABLE USER0402.SCORE ADD CONSTRAINT STUDENT_SCORE_SID FOREIGN KEY(SID) REFERENCES USER0402.STUDENT(SID) ON DELETE CASCADE; 
方式二:删除父表中的对应行，会将对应子表中的所有匹配行的外键约束列置为NULL
ALTER TABLE USER0402.SCORE ADD CONSTRAINT STUDENT_SCORE_SID FOREIGN KEY(SID) REFERENCES USER0402.STUDENT(SID) ON DELETE SET NULL; 

image.png

检查约束:

1. 检查约束可用来实施一些简单的规则,比如列值必须在某个范围内
2. 检查的规则必须是一个结果为true或false 的表达式
3. 其中:为表USER0402.STUDENT添加名为STUDENT_SSEX的检查约束,定义在SSEX字段上,检查范围为男女

ALTER TABLE USER0402.STUDENT ADD CONSTRAINT STUDENT_SSEX CHECK(SSEX IN('Male','Female'));

image.png

约束状态:
因在进行老数据割接时,为保证源数据的完整性且能成功导入,有时需关闭约束的校验,保证数据能全部正确的导入当前库,导入结束后再打开约束

1. 数据库约束有两类状态
   启用/禁用(enable/disable):是否对新变更的数据启用约束验证
   验证/非验证(validate/novalidate):是否对表中已存在的数据进行约束验证
2. 这两类四种状态可以随意组合，默认是 enable validate
3. 组合以及含义如下:
   enable validate:默认的约束组合状态,无法添加违反约束的数据行,数据表中也不能存在违反约束的数据行
   enable novalidate:无法添加违反约束的数据行,但对已存在的违反约束的数据行不做验证
   disable validate:可以添加违反约束的数据行,但对已存在的违反约束的数据行会做约束验证(这是一种相互矛盾的约束组合,只不过是语法上支持这种组合罢了,结果是DML操作失败)
   disable novalidate:可以添加违法约束的数据行,对已存在的违反约束的数据行也不做验证
4. 例如:将表USER0402.STUDENT的SNAME字段的非空约束改为禁用非验证状态

ALTER TABLE USER0402.STUDENT MODIFY CONSTRAINT SYS_C0011125 DISABLE NOVALIDATE;

image.png

5. 数据导入结束后,再将其状态改为启用非验证,确保新添加的数据满足要求,且不会验证已有数据

ALTER TABLE USER0402.STUDENT MODIFY CONSTRAINT SYS_C0011125 ENABLE NOVALIDATE;

image.png

---