oracle初步学习笔记

oracle数据库的体系

oracle服务器组成：

• oracle实例
• oracle数据库组成
• 关键文件-
• 用户进程
• 服务进程等。

它是一个数据库管理系统。

---

oracle实例：是一种访问数据库的机制，它由内存结构和一些后台进程组成。

实例的后台进程中有5个是必须的，只要这5个后台进程中的一个未能启动，则该实例都将自动关闭。

这5个后台进程是：SMON、PMON、DBWR、LGWR、CKPT。

每个实例只能操作其对应的一个数据库。但是反过来是不成立的，因为一个数据库可以同时被几个实例操作（在oracle集群中）。

---

oracle数据库：是数据的一个集合。

包括3类操作系统文件：

1. 控制文件（cntrol file）：包含了维护和校验数据库一致性所需的信息。
2. 重做日志文件（redo log file）：包含了当系统崩溃后进行恢复所需记录的变化信息。
3. 数据文件（data file）：包含了数据库的真正数据。

---

其他的关键文件：

初始化参数文件（parameter files）
密码文件（password files）
归档重做日志文件（archived redo log files）：是重做日志文件的脱机备份。

===================

数据库管理员用户：

当创建了一个数据库之后，系统会自动地创建两个超级用户 sys 和 system，也被称为数据库管理员用户。被授予数据库管理员的角色（DBA role）。

在进行数据库维护和管理时需要以这两个用户之一的身份登录。

sys拥有数据库中数据字典，连接数据库时，应该使用 SYSDBA 或 SYSOPER 的权限来连接。否则系统会报错。

system拥有由oracle工具所使用的附加的内部表和视图。连接数据库时，可以不使用 SYSDBA 或 SYSOPER 的权限来连接。

格式化显示：
set charwidth XX; 设置字符型字段的显示宽度
set numwidh XX; 设置number型字段的显示宽度

oracle 实例管理

初始化参数文件的管理和维护，以各种不同的方式启动或关闭oracle实例，对oracle实例所出现的问题进行诊断和维护。

---

初始化参数文件：在实例启动时oracle将读入该文件的每个参数项，并使用这些参数来配置oracle实例。

在oracle数据库中有两种类型的参数：
显式：在初始化参数文件中有一个参数项。
隐式：在初始化参数文件中没有参数项，但使用oracle的默认值。

有两种不同类型的初始化参数文件：
静态参数文件（PFILE）：该文件为正文文件。文件名一般为 initSID.ora
动态服务器参数文件（SPFILE）：该文件为二进制文件。文件名一般为 spfileSID.ora，这里的 SID 为实例名。

初始化参数文件的内容包括：

1. 实例名和与该实例相关的数据库名。
2. 控制文件名字和位置。
3. 系统全局区的配置，如 shared pool的配置。
4. 还原段（回滚段）的配置。
5. 该实例所能同时起动的进程数。
6. 标准数据块的大小。
7. 是否允许DBA远程登录等。

---

静态参数文件：该文件可使用编辑器进行编辑，oracle只在实例启动时读这一文件。
该文件在 /database/initXE.ora 目录下，指向了真正的文件地址。/dbs/init.ora

---

动态服务器参数文件：是一个二进制文件，他总是保存在服务器上而且是由oracle服务器自动维护的。

引入该文件的目的：为了能在不需要关闭和启动数据库的情况下可以修改实例或数据库的配置。它提供了自我调优的能力。
该文件在/dbs/SPFILEXE.ora

---

在oracle数据库中有3种类型的常见数据库诊断文件：

1. 报警文件：包括数据库日常操作信息。
2. 后台进程追踪文件：
3. 前台进程追踪文件：

数据字典

数据字典：是由oracle服务器创建和维护的一组只读的系统表，数据库字典中存放了有关数据库和数据库对象的信息。

oracle服务器就是依赖这些信息来管理和维护oracle数据库的。

数据字典分为两大类：

• 基表
• 数据字典视图

---

数据字典中所存的信息：

1. 数据库的逻辑结构和物理结构，如表空间和数据文件的信息。
2. 所有数据库对象定义的信息。这些对象包括：表、索引、视图、序列号、同义词、过程、函数、软件包、触发器等。
3. 所有数据库对象的磁盘空间分配信息，如对象所分配的磁盘空间和当前正在使用的磁盘空间。
4. oracle用户名。
5. 每个用户所授予的权限和角色。
6. 完整性约束的信息。
7. 列的默认值。
8. 审计信息等。

所有数据库管理员和用户只能通过访问数据字典（视图）来得到数据库的相关信息。

数据字典视图分为3大类：

• USER_*--- 用户所拥有的对象的信息，即用户自己创建的对象信息。
• ALL_*--- 用户可以访问的对象的信息，即用户自己创建的对象信息加上其他用户创建的对象信息。但要该用户有权访问的对象信息。
• DBA_*--- 整个数据库中对象的信息。

其中的*可以是 tables、indexs、objects等。

---

所有的数据字典视图都是静态视图，即当数据库发生变化时，oracle服务器并不及时的刷新这些视图中的信息。

数据字典视图包含的信息：

1. 对象的属主
2. 对象所拥有的权限
3. 对象的创建时间
4. 对象存储参数的设置
5. 对象存储空间的使用情况等。

---

使用数据字典视图：

查看所有的数据字典：

select * from dictionary;
select * from v$fiexd_table;

查看某个数据字典中某一列的含义：

select * from dict_columns where table_name='DBA_TABLES'
and column_name='INITIAL_EXTENT';

查看当前数据库的信息：

select name, created, log_mode, open_mode, from v$database;

查看数据库运行的主机的相关信息：

select host_name, instance_name, version from v$instance;

查看数据库系统的版本信息：

select * from v$version;

查看控制文件名字：

select * from v$controlfile;

查看当前用户下的所有表：

select table_name from user_tables;

select * from user_catalog;
user_catalog有一个别名cat

---

动态性能表（视图）：动态性能视图是一组虚表，在oracle数据库运行期间，这些虚表存在于内存中。
他们的名字以v$开头。

利用动态性能视图可以获得类似如下的信息：

1. 会话活动的信息。
2. 对象打开或关闭的信息。
3. 对象在线或离线的信息等。

---

控制文件：该文件是一个比价小的二进制文件，它记载了物理数据库的当前状态，每一个控制文件只属于一个数据库。

但为了防止控制文件的丢失，一个数据库一般有不只一个控制文件，这些控制文件的内容完全一样。

控制文件中所存的内阁：

1. 数据库名字。
2. 数据库标识符。
3. 数据库创建的时间戳。
4. 联机重做日志文件的名字和准确的位置。
5. 当前日志的序列号。
6. 校验点信息。
7. 日志的历史信息。
8. 归档日志文件的准确位置和状态。
9. 数据文件的名字和准确位置。
10. 表空间的信息。
11. 备份的准确位置和状态。

对控制文件的备份：

alter database backup controlfile to 'D:\backup\control.bak';

表空间和数据文件管理

oracle的逻辑结构

实体——关系模型（E-R模型）。

oracle将数据逻辑地存放在表空间里，而物理地存放在数据文件里。

表空间（tablespace）在任何一个时刻只能属于一个数据库，一个数据库一般都有多个表空间，每个表空间由一个或多个
操作系统的数据文件组成，但是一个操作系统的数据文件只能属于一个表空间。

在oracle数据库中，每个数据文件可以而且只能属于一个表空间和一个数据库。
数据文件（data file）：实际上是存储模式数据对象的一个容器（仓库）。

在oracle数据库中一般有两类表空间：

1. 系统表空间（system ）：
   该表空间是与数据库一起建立的，存有数据字典，包含了系统还原（回滚）段。
   在系统表空间中不应该存放任何的用户数据。

2. 非系统表空间（non-system）：
   由数据库管理员创建，存储一些单独的段（用户的数据段、索引段、还原段、临时段等）。

---

创建一个非系统表空间：

create tablespace tablespace_name
  [datafile 子句]      // 组成所要创建表空间的文件说明
  [minimum extent 正整数[K|M]]
  [blocksize 正整数[K]]
  [logging | nologging]
  [default]
  [online | offline]
  [permanent | temporary]
  [区段管理子句]
  [段管理子句]

---

表空间中的磁盘空间管理方法：数据字典管理、本地管理的表空间（oracle 9i 之后是默认的，推荐使用）。

创建本地管理的索引表空间：

create tablespace test_index
  datafile 'D:\oracle\xe\test_index.dbf'  // 数据文件名
  size 50M  // 数据文件大小
  extent management local  // 使用本笃管理的表空间
  uniform size 1M  // EXTENT 的大小

查看创建的表空间：

SELECT tablespace_name, block_size, segment_space_management
  FROM dba_tablespaces
WHERE tablespace_name LIKE 'test%';

---

还原表空间（undo）：

用来自动地管理还原（回滚）数据。

还原表空间是用来存储还原段的。在还原表空间中不能包含任何其他的对象。还原表空间中的区段是由本地管理的，

在创建还原表空间时的SQL只能使用 datafile。

create undo tablespace test_undo
  datafile 'E:\oracle\app\oracle\oradata\XE\test_undo.dbf'
  size 10M;

---

临时表空间（temporary）：

作为排序操作使用的。当在SQL语句中使用的 order by、 group by子句时。

临时表空间可以由多个用户共享，在其中不能包含任何的永久的对象。

临时表空间中的排序段是在实例启动后当有第一个排序操作时创建的。

当创建临时表空间时，必须使用标准数据块。

-- 创建临时表空间
create temporary tablespace test_temp
  tempfile 'E:\oracle\app\oracle\oradata\XE\test_temp.dbf'
  size 10M;

---

表空间的联机/脱机状态：（online/offline）

一个表空间的正常状态是联机状态（online），此时用户可访问该表空间中的数据。

有的时候需要将被某些表空间置为脱机状态来进行数据库的维护，维护工作包括：

1. 在数据库处于打开状态下移动数据文件。
2. 在数据库处于打开状态下恢复一个数据文件或一个表空间。
3. 执行对表空间的脱机备份。
4. 使数据库的一部分不可以被访问。

不是所有的表空间都可以被置为脱机状态，以下的表空间不可以：

1. 系统表空间
2. 上面有活动的还原/回滚段的表空间。
3. 默认临时表空间。

查看对应表空间的状态：

SELECT tablespace_name, status, contents
  FROM dba_tablespaces
WHERE tablespace_name LIKE 'test%';

将表空间设置为脱机状态：

alter tablespace tablespace_name offline;

将表空间设置为联机状态：

alter tablespace tablespace_name online;

---

只读表空间（read-only）：

讲一个表空间的状态改为只读或可读可写时，该表空间必须处于联机状态。

设为只读：

alter tablespace tablespace_name read only;

设为可读可写：

alter tablespace tablespace_name read write;

---

表空间的存储设置：

alter tablespace tablespace_name 存储参数;

更小的存储结构

oracle中不同类型的段：

• 表（table）：

• 分区表（table partition）：

• 索引（index）：

• 簇（cluster）：

• 索引分区：

• 索引表

• 临时段：

• 还原段：

• 大对象段：

• 嵌套表：

• 自举段：

管理还原数据

管理表

oracle数据库常见的几种存储用户数据的方法：

1. 表
2. 分区表
3. 簇
4. 索引表

oracle的内置数据类型：

1. 标量数据类型

• varchar2、nvarchar2 -- 变长字符型数据，最大值4000字符
• char、nchar -- 定长字符型数据，最大值2000字符
• date、timesatmp
• number(p, s)
• blob
• clob

2. 集合数据类型

3. 关系数据类型

---

ROWID :

挡在oracle数据库的表中插入一行数据时，oracle就会自动地在这一行数据上加上一个 ROWID。
每一行数据都有唯一的 ROWID，oracle就是利用ROWID来定位数据的。

---

创建表：

oracle推荐在创建表时的一些规则：

1. 将不同的表放在不同的表空间中。
2. 使用本地管理表空间以避免碎片。
3. 在表中使用若干的标准extent尺寸，以减少表空间的碎片。

-- 创建用户
create user lifan identified by 111111;
-- 授权
grant dba to lifan;
-- 创建表位于user表空间
CREATE TABLE lifan.product(
  pcode NUMBER(8),
  pname VARCHAR2(30),
  pdesc VARCHAR2(100),
  pprice NUMBER(6, 2)
)TABLESPACE users;

---

创建临时表：

CREATE GLOBAL TEMPORARY TABLE lifan.emp_temp
  ON COMMIT PRESERVE ROWS AS
  SELECT * FROM lifan.emp WHERE job NOT LIKE 'SAL%';

• ON COMMIT PRESERVE ROWS：数据在整个会话中可见。
• ON COMMIT DELETE ROWS： 数据行只有在事务中可见（默认）。

管理用户

创建用户：

create user user_name identified by passwrd;

删除用户：

DROP USER user_name;

---

用户的安全控制域：

当创建了一个oracle数据库的用户时，也就定义了一个用户的安全控制域（security domain）：

1. 安全检测机制
2. 用户的默认表空间
3. 用户排序所用的临时表空间
4. 表空间的配额
5. 账户上锁
6. 资源限制
7. 直接权限
8. 角色权限

---

概要文件：一组命了名的口令和资源限制，它是通过 create user 或 alter user 赋予用户的。

概要文件可以被开启和关闭。

使用概要文件的好处：
可以将用户按他们的安全控制和资源使用要求分成若干个组，然后按每一组按用户的需求创建一个概要文件，最后将这些概要文件分别赋予相关的用户。

创建概要文件：

create profile file_name limit
[SESSIONS_PER_USER]  // 最大会话数
[CPU_PER_SESSION] // 会话使用CPU的最大时间
[CPU_PER_CALL]  // 最大值
[CONNECT_TIME]  // 会话连接时间最大值
[IDLE_TIME]  // 会话没有互动时不能超过的最大值
[LOGICAL_READS_PER_SESSION]  // 会话可读数据块的最大值
[LOGICAL_READS_PER_CALL];  // 最大值

将文件赋予用户：

create user 
alert user

开启概要文件资源限制：

alert system ste resource_limit=true;

sysoper与sysdba的区别

管理权限

系统权限：

DBA 是数据库系统中级别最高的用户，DBA具有数据库系统中的一切系统权限，并拥有所有的系统资源。

可能用到的哟西额系统权限：

1. 有关用户的系统权限：
   CREATE USER
   ALERT USER
   DROP USER

2. 有关表的系统权限（在任何的模式中）
   SELECT ANY TABLE
   UPDATE ANY TABLE
   DELETE ANY TABLE
   CREATE ANY TABLE
   DROP ANY TABLE
   ALERT ANY TABLE
   CREAT TABLE

3. 有关表空间的系统权限
   CREATE TABLESPACE
   DROP TABLESPACE
   ALERT TABLESPACE
   UNLIMITED TABLESPACE // 使用全部表空间的权限

4. 有关索引的系统权限
   CREATE ANY INDEX
   DROP ANY INDEX
   ALERT ANY INDEX

5. 有关表会话的系统权限
   CREATE SESSION
   ALERT SESSION

6. 其他的系统权限
   CREATE VIEW
   CREATE SEQUENCE // 创建序列号
   CREATE PROCEDURE // 创建过程

授予用户权限：

GRANT 权限列表（用逗号隔开） TO user_name|用户列表;

回收系统权限：

REVOKE 系统权限列表|角色名列表  FROM user_name|relo_name|PUBLIC;

---

对象权限：

EXECUTE
ALTER
SELECT
UPDATE
INSERT
DELETE
INDEX
REFERENCES

授予权限：

GRANT ALL|对象的权限  [列名1， 列名2，....]
ON 对象名
TO [用户名|角色|PUBLIC];

回收权限：

REVOKE ALL|对象的权限  ON 对象名 FROM [用户名|角色|PUBLIC];

管理角色

角色（role）：一组命名的相关权限，这组权限可以通过这个名字属于用户或其他角色。
一个角色既可以包括系统权限，也可以包括对象权限。

每一个角色在系统中是唯一的，不能与现有的任何用户名和角色名重名。

---

角色的创建：

CREATE ROLE role_name;

角色授权：

创建默认角色：

ALTER USER user_name DEFAULT ROLE ALL;

禁止和激活角色：

禁止
SET ROLE ALL;
开启
SET ROLE ALL EXCEPT user_name;

收回角色：

REVOKE role_name, ...;

删除角色：

DROP ROLE role_name;

数据库的备份

数据库的全备份（最常用的备份方法）：备份所有数据文件和控制文件。

• 控制呢文件备份：

• 表空间备份：需要使用归档日志文件或重做日志文件来恢复

• 数据文件备份：需要使用归档日志文件或重做日志文件来恢复

---

冷备份：

最安全、最简单的备份方法。