1. 数据存储结构及管理

1.1. 表中数据的存储

1.2. Block的内容与结构

1.3. 表空间（tablespace）

1.3.1. 常见表空间及视图查询

在创建数据库时，会伴随产生如下表空间

SYSTEM 表空间：存储系统数据、数据字典，必须保持 ONLINE。

SYSAUX 表空间：存储系统数据，作为SYSTEM表空间的辅助表空间，存放一些其他的 metadata 组件，如?OEM,Streams?等会默认存放在 SYSAUX 表空间里。

TEMP 表空间：存储临时数据，临时段。

UNDO 表空间（UNDOTBS1）：存储UNDO数据，UNDO段。

USERS：用户的默认表空间。

相关视图

1）dba_tablespaces：查询表空间信息

SQL> select tablespace_name, status, contents, logging, extent_management, allocation_type, segment_space_management from dba_tablespaces;

2）dba_data_files：查询数据文件

SQL> select tablespace_name, file_name, status from dba_data_files;
SQL> select name from v$tempfile;

1.3.2. 创建表空间

SQL> create tablespace mytbs01 datafile ‘/u01/app/oracle/oradata/ORCL/mytbs01.dbf‘ size 100M;
SQL> create table t1(id number(4), name char(10)) tablespace mytbs01;    -- 创建表时指定表空间
SQL> create index idx1 on t1(id) tablespace mytbs01;    -- 创建索引时指定表空间

1.3.3. 查看用户对象所在的表空间

SQL> select table_name, tablespace_name from user_tables;    -- 查看用户表及对应存储的表空间
SQL> select index_name, tablespace_name from user_indexes;    -- 查看用户索引及对应存储的表空间

1.3.4. 区管理方式（extent_management）

1）字典管理表空间（dictionary managed），在创建时需要指定存储参数：

initial：为段分配的第一个区的大小

next：为段分配的第二个区的大小

pctincrease：从第三个区开始，每个区在前一个区的基础上增加50%

minextents：在创建段时，一开始分配的区的数量

maxextents：一个段最多的区的数量

2）本地管理表空间（local managed）：所有区的大小都相同

创建表空间的时候指定区管理方式：

SQL> create tablespace mytbs02 
     datefile ‘/u01/app/oracle/oradata/ORCL/mytbs02.dbf‘ size 50M
     extent management local
     autoallocate;
SQL> create tablespace mytbs03
     datafile ‘/u01/app/oracle/oradata/ORCL/mytbs03.dbf‘ size 100M
     extent management local
     uniform size 10M；

3）数据字典中查询表空间的区管理方式

SQL> select tablespace_name, extent_management from dba_tablespaces;

1.3.5. 段管理方式（segment_space_management）

1）manual：在每个段的头部有一个freelist保存空闲的块号，块号需要占用空间，事务要对freelist加锁，执行效率相对较低。

2）auto：在每个段的头部有一个bitmap，采用0和1表示块的状态。

创建表空间的时候指定段管理方式

SQL> create tablespace mytbs04
     datafile ‘/u01/app/oracle/oradata/ORCL/mytbs04.dbf‘ size 100M
     extent management local
     uniform size 10M
     segment space management auto;

1.3.6. bigfile 表空间

只有一个数据文件，具有4G个数据块，要求区管理方式为 local，段管理方式为 auto。

1）创建 bigfile 表空间

SQL> create bigfile tablespace mytbs05 datafile ‘/u01/app/oracle/oradata/ORCL/mytbs05.dbf‘ size 10M;

2）查询 bigfile 表空间

SQL> select bigfile, tablespace_name, extent_management, segment_space_management from dba_tablespaces;

1.3.7. TEMP，临时表空间

1）创建 TEMP 表空间

SQL> create temporary tablespace mytemptbs01 tempfile ‘/u01/app/oracle/oradata/ORCL/mytemptbs01.dbf‘ size 100M;

2）为数据块指定新的默认临时表空间

SQL> alter database default temporary tablespace mytemptbs01;

3）为用户指定临时表空间

SQL> alter user scott temporary tablespace mytemptbs01;

4）表空间组：多个临时表空间

SQL> alter tablespace mytemptbs01 tablespace group g1;
SQL> alter tablespace temp1 tablespace group g1;
SQL> alter database default temporary tablespace g1;

1.3.8. OMF

全称是Oracle Managed Files，即Oracle文件管理，使用OMF可以简化管理员的管理工作，不用指定文件的名字、大小、路径，其名字，大小，路径由oracle 自动分配。在删除不再使用的日志、数据、控制文件时，OMF也可以自动删除其对应的OS文件，对于未采用OMF来创建的表空间，在删除表空间之后，其数据文件并没有删除，v$datafile视图中被删除

OMF支持下列文件的自动管理：表空间、日志文件(联机）、控制文件，前提是需要为这些类型文件设定相关参数。

1）数据文件管理参数 db_create_file_dest：Oracle创建数据文件、临时文件时，在未明确指定路径的情况下的缺省路径，当db_create_online_log_dest_n未指定时，也作为联机日志文件和控制文件的缺省路径。

SQL> show parameter db_create_file_dest    -- 查看db_create_file_dest参数
SQL> alter system set db_create_file_dest=‘/u01/app/oracle/oradata/ORCL‘;    -- 设置db_create_file_dest参数
SQL> create tablespace ts5;    -- 此时创建表空间将使用OMF自动进行文件管理
SQL> create tablespace ts6 datafile size 10M;    -- 创建表空间的同时也可以指定大小

2）为重做日志文件指定路径 DB_CREATE_ONLINE_LOG_DEST_n

3）指定快速恢复区的路径 DB_RECOVERY_FILE_DEST

1.3.9. 表空间的扩展

数据库的几种扩展方法：

a. 创建新的表空间

b. 向已经存在的表空间中添加数据文件

c. 增加数据文件的容量

d. 让数据文件自动扩展

1）向表空间中增加文件

SQL> create tablespace ts4
     datafile ‘/u01/app/oracle/oradata/ORCL/ts4_1.dbf‘ size 20M, ‘/u01/app/oracle/oradata/ORCL/ts4_2.dbf‘ size 30M;    -- 创建表空间ts4
SQL> alter tablespace ts4
     add datafile ‘/u01/app/oracle/oradata/ORCL/ts4_3.dbf‘ size 20M;

2）手工扩展数据文件

SQL> select file_name, BYTES/1024/1024 as MB from dba_data_files;    -- 查看数据文件的大小
SQL> alter database datafile ‘/u01/app/oracle/oradata/ORCL/ts4.dbf‘ resize 50M;

3）允许数据文件自动扩展

SQL> create tablespace ts8
     datafile ‘/u01/app/oracle/oradata/ORCL/ts41.dbf‘ size 10M autoextend on next 20M maxsize 1G,
     ‘/u01/app/oracle/oradata/ORCL/ts42.dbf‘ size 30M autoextend off;    -- 创建表空间时指定数据文件自动扩展
SQL> alter database datafile ‘/u01/app/oracle/oradata/ORCL/ts51.dbf‘ autoextend on next 20M maxsize 2G;    -- 调整数据文件实现自动扩展
SQL> select file_name, autoextensible, increment_by, maxbytes/1024/1024 as max_mb from dba_data_files;

1.3.10. 表空间的删除与状态调整

1）删除表空间

SQL> drop tablespace ts1;
SQL> drop tablespace ts1 including contents;    -- 如果表空间中非空，不加including contents则无法删除
SQL> drop tablespace ts1 including contents and datafiles;    -- 删除表空间同时删除数据文件

2）改变表空间状态

SQL> alter tablespace ts5 offline;
SQL> alter tablespace ts5 online;
SQL> alter tablespace ts5 read only;
SQL> alter tablespace ts5 read write;

1.3.11. 在线移动数据文件

SQL> alter database move datafile ‘/u02/orcl/t822.dbf‘ to ‘/u01/app/oradata/ORCL/t82.dbf‘;    -- 重新分配数据文件

?

2. 用户及权限管理

2.1. 数据库用户账户

每个数据库用户账户都有：

1）唯一的用户名

2）一个认证方法

3）一个默认的表空间

4）一个用户profile

5）一个初始的consumer组

6）一个账户状态

?

一个schema：

1）是用户拥有的数据库对象的一个集合

2）具有和用户账户相同的名称

2.2. 预定义的管理账户

1）SYS：拥有数据字典和自动工作负载存储库（AWR），用于启动和关闭数据库实例

2）SYSTEM：拥有额外的用于管理的表和视图

3）SYSBACKUP：便于RMAN（Oracle Recovery Manager）的备份恢复操作

4）SYSDG：便于Oracle Data Gurad的操作

5）SYSKM：便于Transparent Data Encryption wallet的操作

2.3. 用于数据库管理的权限

1）SYSDBA：标准数据库操作，如：启动和关闭数据库实例，创建服务器参数文件（spfile），改变日志归档模式，允许被授权人查看用户数据。

2）SYSOPER：标准数据库操作，如：启动和关闭数据库实例，创建服务器参数文件（spfile），改变日志归档模式。

3）SYSBACKUP：Oracle Recovery Manager (RMAN) 备份和恢复操作，通过RMAN或SQLPlus

4）SYSDG：Data Guard操作，通过使用Data Guard Broker或是DGMGRL命令行接口

5）SYSKM：管理Manage Transparent Data Encryption wallet操作

2.4. 用户认证的方法

1）密码：用户定义的用户尝试登录数据库时必须提供的密码。

2）外部：通过数据库外部的方法进行身份验证。

3）全局：通过使用基于LDAP的目录服务来标识用户。

管理员身份验证

a. 操作系统验证：针对SYS用户的本地登录 oracle用户 --> SYS

b. 口令文件验证：针对SYS用户的远程登陆，位于$ORACLE_HOME/dbs 目录，名为orapworcl

? ? 重新创建口令文件：$ orapwd file="orapworcl" password="Qwer_1234"

2.5. 用户管理相关视图

1）dba_users

SQL> select username, password, account_status, lock_date, expiry_date from dba_users;    -- 查询用户信息

2）v$pwfile_users

SQL> select username, sysdba, sysoper, sysasm, sysbackup, syskm, sysdg, account_status from v$pwfile_users;    -- 查询所有的特权用户

3）dba_ts_quotas

SQL> select username, tablespace_name, max_bytes from dba_ts_quotas;    -- 查询用户对表空间的使用配额

4）v$session

SQL> select username, sid, serial# from v$session where username is not null;    -- 查询会话

2.6. 表空间配额的指定

SQL> grant unlimited tablespace to scott;
SQL> alter user scott quota 10M on t3;

2.7. 用户账号创建、调整及删除

SQL> create user jack identified by "1234";
SQL> create user john identified by "1234"
     default tablespace users
     temporary tablespace temp
     quota 20M on ts3
     account lock
     password expire;    -- 创建用户，指定用户使用的临时表空间、默认表空间账户可用性
SQL> alter user john
     default tablespace ts3
     temporary tablespace temp1;     -- 切换用户使用的表空间
SQL> alter user john 
     quota unlimited on ts3;    -- 调整john在ts3上的配额限制
SQL> alter user john account unlock;    -- 账户解锁
SQL> alter user john password expire;    -- 令用户密码过期
SQL> drop user john cascade;    -- 删除用户，级联删除

2.8. 用户权限管理

系统权限：用户在数据库范围内能够进行某种操作的权限

—— 例如：create session、create table、create procedure、create view等

对象权限：用户能够访问某个特定数据库对象的权限

2.8.1. 系统权限

1）为用户指定系统权限：

SQL> grant create table, create session to john;
SQL> grant create table, create session to john with admin option;

2）回收系统权限：

SQL> revoke create table from john;

3）与系统权限有关的数据字典视图

system_privilege_map：查询当前数据库所定义的系统权限

dba_sys_privs：查询任何一个用户所拥有的系统权限

SQL> select privilege from dba_sys_privs where grantee=‘SCOTT‘;

user_sys_privs：查询当前用户所拥有的系统权限

session_privs：当前用户登录之后在当前会话中所具有的系统权限

SQL> select * from session_privs;

2.8.2. 对象权限

对象权限：用户能够访问某个特定数据库对象的权限

例如：针对表的对象权限：select、insert、update、delete、references

? ? ?针对存储过程的对象权限：execute

1）为用户指定对象权限

SQL> grant select on scott.dept to john;
SQL> grant select on scott.dept to john with grant option;

2）回收对象权限

SQL> revoke select on scott.dept from john;

3）与对象权限有关的数据字典视图

dba_tab_privs：查询任何用户具有的对象权限

SQL> select owner, table_name, privilege, grantor from dba_tab_privs where grantee=‘john‘;

user_tab_privs：查询当前用户所具有的对象权限

SQL> select owner, table_name, privilege, grantor from user_tab_privs;

dba_col_privs 及 user_col_privs：查询列级权限

SQL> grant update(dname) on scott.dept to john;    -- 赋予列级权限

2.9. 角色

1）角色的创建

SQL> create role r1;

2）SYS用户将系统权限指定给角色

SQL> grant create session, create table to r1;

3）普通用户例如scott，将对象角色指定给角色

SQL> grant select, update on dept to r1;

4）将角色r1指定给用户

SQL> grant r1 to user1;

5）与角色相关的视图

dba_roles：查询当前数据库中所有的角色

role_sys_privs：查询一个角色所具有的系统权限

SQL> select privilege from role_sys_privs where role=‘R1‘;

role_tab_privs：查询一个角色所具有的对象权限

SQL> select owner, table_name, privilege from role_tab_privs where role=‘R1‘;

user_role_privs：当前用户所具有的角色

6）特殊的角色

CONNECT：仅具有创建session的权限。

RESOURCE：仅具有创建TABLE、CLUSTER，INDEXTYPE，OPERATOR，PROCEDEURE，SEQUENCE,TABLE，TRIGGER，TYPE的权限。同时，当把ORACLE resource角色授予一个user的时候，不但会授予ORACLE resource角色本身的权限，而且还有unlimited tablespace权限，但是，当把resource授予一个role时，就不会授予unlimited tablespace权限。（对于新用户常用的赋权操作即赋予CONNECT和RESOURCE两个角色）

SQL> select privilege from role_sys_privs where role=‘RESOURCE‘;    -- 查看RESOURCE角色的权限
SQL> create user u2 identified by "Qwer_1234";
SQL> grant connect, resource to u2;    -- 为u2赋予CONNECT与RESOURCE角色

DBA：对应的是对Oracle实例里对象的操作权限的集合。

2.10. profile

一次只为用户分配一个配置文件。profile能够控制资源消耗、管理账户状态和控制密码过期时间

相关参数：

failed_login_attemps：指定用户登录失败的次数

password_lock_time：锁定的天数

password_life_time：口令的有效期（天数）

password_grace_time：口令的宽限期（天数）

password_reuse_time：密码重用时间，天数

password_reuse_max：密码重用，次数

idle_time：会话的空闲时间，超时将自动断开

connect_time：会话能够持续的时间

cpu_per_session：用户在一个会话内能够消耗的cpu时间，1/100秒

session_per_user：一个用户最多的并发会话数量

logical_reads_per_session：一个会话最多能够读取多少个数据块

1）创建profile

SQL> create profile p1 limit 
     failed_login_attempts 3
     password_lock_time 1;
SQL> create profile p2 limit 
     password_reuse_time 1
     password_reuse_max 3;
SQL> create profile p5 limit
     cpu_per_session  100
     logical_reads_per_session 500;

2）调整用户profile

SQL> alter user u1 profile p1;