Oracle数据库基础与管理深入解析

王小约

于 2024-09-09 16:06:21 发布

阅读量863

点赞数 3

本文链接：https://blog.csdn.net/weixin_31776191/article/details/142094121

版权

本文还有配套的精品资源，点击获取

简介：在本课程的第三天，我们将深入探讨Oracle数据库的核心技术和概念，包括SQL基础、数据类型、表结构与约束、索引、视图、PL/SQL编程、事务与并发控制、备份与恢复、性能优化以及安全性等内容。通过实例学习，旨在让学生掌握Oracle数据库的日常操作和管理，为未来学习高级特性打下坚实的基础。 ORACLE第3天

1. SQL基础与操作

1.1 SQL简介

SQL（Structured Query Language）是用于管理和操作关系型数据库的标准语言。它包括数据查询、数据操纵（如插入、更新和删除）、数据定义（创建、修改数据库结构）以及数据控制（如权限控制）等方面的功能。

1.2 数据操作语言（DML）

数据操作语言用于对数据库中的数据进行增删改的操作。包括以下命令： - INSERT：向表中添加数据。 - UPDATE：更新表中的数据。 - DELETE：从表中删除数据。

1.3 数据查询语言（DQL）

数据查询语言用于从数据库中检索数据。查询的基础是SELECT语句，它可与FROM、WHERE、GROUP BY、HAVING、ORDER BY等子句结合使用，实现复杂的数据检索和排序功能。

-- 示例：查询员工表中所有员工的姓名和工资
SELECT name, salary FROM employees;

1.4 数据定义语言（DDL）

数据定义语言用于创建、修改或删除数据库对象。主要的DDL命令包括： - CREATE：用于创建数据库对象，如表、视图等。 - ALTER：用于修改现有数据库对象的结构。 - DROP：用于删除整个数据库对象。

-- 示例：创建一个新的表
CREATE TABLE new_table (
  id INT PRIMARY KEY,
  name VARCHAR(100),
  salary DECIMAL(10, 2)
);

通过上述章节，我们将了解SQL的基础知识，学习如何使用DML、DQL和DDL来操作数据库。这些操作是进行数据库管理工作的核心，对任何数据库相关的工作都至关重要。接下来的内容将会深入探讨数据库表的结构和数据类型，以及如何高效地使用索引和视图。

2. Oracle支持的数据类型与表结构

Oracle数据库系统为用户提供了丰富多样的数据类型，从而能够存储不同性质和用途的数据。本章将深入探讨Oracle支持的数据类型以及如何定义和管理表结构。

2.1 Oracle支持的数据类型

Oracle提供多种数据类型以满足不同类型数据的存储需求。我们可以将它们大致分为两大类：基本数据类型和复杂数据类型。

2.1.1 字符类型、数值类型与日期类型

字符类型、数值类型和日期类型是Oracle中最常用的基本数据类型。

字符类型 ： Oracle中的字符类型包括CHAR、VARCHAR2、NCHAR、NVARCHAR2等。CHAR和NCHAR类型是固定长度的，如果存储的数据长度小于定义长度，Oracle会在其后填充空格以达到定义长度。VARCHAR2和NVARCHAR2则为变长类型，根据实际存储的数据长度来分配空间，有效节省存储空间。
数值类型 ： NUMERIC和NUMBER数据类型用于存储数值。NUMERIC类型精确存储固定位数的数值，包含小数位数，非常适合需要精确计算的金融数据。NUMBER类型则可以存储非常大的数值和小数点，具有可变精度。
日期类型 ： DATE类型用于存储日期和时间信息，能够处理的时间范围从公元前4712年1月1日到公元9999年12月31日。TIMESTAMP是更精确的时间戳类型，它可以存储日期和时间到小数点后多位，非常适合需要高精度时间处理的应用。

2.1.2 大对象类型与复杂类型

对于需要存储大文本、图片或二进制数据，Oracle提供了大对象类型。

CLOB（字符大对象） ： CLOB类型用于存储大量的文本数据，可存储的数据量高达4GB。适用于存储文档、书籍等大文本信息。
BLOB（二进制大对象） ： BLOB类型用于存储非文本的二进制数据，比如图片、视频文件等。存储空间同样可高达4GB。
复杂类型 ：在Oracle中，复杂类型还包括对象类型和XMLType。对象类型允许用户定义一个新的数据类型，它由多个基础数据类型的属性组成。XMLType可以用来存储和管理XML数据，提供XML相关的功能，如类型检查、索引和查询优化。

2.2 表结构定义与约束

表是数据库中最基本的存储数据的单位。表结构定义是数据库设计中的核心部分，表中的数据被约束条件所控制，保证数据的完整性和准确性。

2.2.1 表的创建、修改与删除

表的创建、修改与删除操作是数据库管理的常见任务。

表的创建 ：创建表通常使用CREATE TABLE语句，并且可以定义表的列以及其数据类型。例如：

CREATE TABLE employees (
  employee_id NUMBER(6) NOT NULL,
  first_name VARCHAR2(20),
  last_name VARCHAR2(25) CONSTRAINT emp_last_name_nn NOT NULL,
  email VARCHAR2(25) CONSTRAINT emp_email_uk UNIQUE,
  phone_number VARCHAR2(20),
  hire_date DATE NOT NULL,
  job_id VARCHAR2(10) NOT NULL,
  salary NUMBER(8, 2),
  commission_pct NUMBER(2, 2),
  manager_id NUMBER(6),
  department_id NUMBER(4)
);

在这个例子中，我们定义了一个名为 employees 的表，包含多个字段，如 employee_id 、 first_name 等，并为其中的一些字段设置了约束，如 NOT NULL 和 UNIQUE 。

表的修改 ：修改表结构使用ALTER TABLE语句。可以添加或删除列，修改列的数据类型，添加约束等操作。例如：

ALTER TABLE employees ADD (email VARCHAR2(25));

这条命令会在 employees 表中添加一个名为 email 的列，数据类型为 VARCHAR2(25) 。

表的删除 ：删除表使用DROP TABLE语句。这会永久移除表及其数据。例如：

DROP TABLE employees;

2.2.2 约束的种类及应用

约束用来限制表中的数据，确保数据的准确性和可靠性。Oracle支持以下种类的约束：

主键约束（PRIMARY KEY） ：主键约束用于唯一标识表中的每一行，并且不允许有重复值或NULL值。通常用于关联表和查询操作。
唯一约束（UNIQUE） ：保证一列或列的组合中的所有值都是唯一的。但是可以有一个NULL值。
非空约束（NOT NULL） ：确保列中不能有NULL值。这种约束经常用于强制某列必须有数据。
外键约束（FOREIGN KEY） ：维持两个表之间数据的完整性。它是指向父表的主键的列或列组合。当删除或更新父表中的数据时，子表中相关的数据也会受到影响。
检查约束（CHECK） ：用于限制列中的值必须满足一定的条件，比如范围限制或值的列表。

通过这些约束，数据库管理员可以构建出一个更加健壮和一致的数据库系统。这些约束不仅保证了数据的质量，同时也为数据库的维护提供了便利。

在下一章节中，我们将深入探讨索引与视图的应用，这些是进一步优化数据库性能与管理数据的关键组成部分。

3. 索引与视图的应用

索引和视图是提高数据库性能和维护数据抽象的两种重要技术。它们允许数据库管理员和开发人员以更高效和安全的方式访问和操作数据。

3.1 多种索引类型及其应用

在数据库管理中，索引是组织数据库表中数据的一种方式，它允许快速查找、访问表中的特定数据。索引类型的选择依赖于数据访问模式和查询优化的需求。

3.1.1 B-tree索引、位图索引与函数索引

B-tree索引是最常用的索引类型，适用于数据的快速查找、排序和范围查询。它维护了一个有序的键值列表，并通过平衡树结构确保了高效的插入、删除和查找操作。

CREATE INDEX idx_student_name ON students(name);

上述SQL语句创建了一个名为 idx_student_name 的B-tree索引，用于提高查询学生姓名的性能。

位图索引通常用于包含有限数量的低基数列。与B-tree索引不同，位图索引是紧凑的，并且在执行多个条件查询时非常高效。

CREATE BITMAP INDEX idx_gender ON students(gender);

这个语句创建了一个位图索引 idx_gender ，用于提高基于性别筛选学生的查询速度。

函数索引是使用函数或者表达式计算列的值，这些值被存储并用于索引。当查询包含基于该函数或表达式的列时，函数索引可以加快查询速度。

CREATE INDEX idx_length_name ON students(LENGTH(name));

该代码创建了一个函数索引 idx_length_name ，它对学生的 name 列应用 LENGTH 函数，并使用结果作为索引键。

3.1.2 索引的创建、优化与管理

创建索引后，必须对其进行优化以维持性能。索引管理包括添加、删除和修改索引，以及监控其性能和维护统计信息。

ALTER INDEX idx_student_name REBUILD;

这个命令用来重建 idx_student_name 索引，以优化其性能和空间利用率。

索引可能会成为插入、更新和删除操作的瓶颈，因为数据库需要更新索引来保持一致性。因此，对索引进行优化涉及到定期评估索引的使用情况和对索引进行重新组织或重建。

3.2 视图的创建和应用

视图是数据库中一个非常有用的特性，它允许用户从一个或多个表中创建虚拟表。视图可以用来简化复杂的查询、隐藏敏感信息、封装数据逻辑以及实现数据的独立性。

3.2.1 视图的概念与分类

视图的概念类似于虚拟表，它包含了基于一个或多个表的查询结果。视图可以分为简单视图和复杂视图。简单视图是基于单个表的查询，而复杂视图是基于多个表的连接或其他复杂操作的查询。

3.2.2 视图的创建、修改与删除

创建视图的基本语法如下：

CREATE VIEW student_view AS
SELECT name, gender, age
FROM students
WHERE age > 18;

这个SQL命令创建了一个名为 student_view 的视图，它仅展示了年龄大于18岁的学生信息。

当需要更新视图结构时，可以使用 CREATE OR REPLACE VIEW 语句来修改视图定义。

CREATE OR REPLACE VIEW student_view AS
SELECT name, gender, age, address
FROM students;

这个例子增加了 address 字段到 student_view 视图中。

最后，如果一个视图不再需要，可以使用 DROP VIEW 语句来删除它。

DROP VIEW student_view;

删除 student_view 视图的命令。

通过本章节的介绍，我们已经对索引与视图的应用有了更深入的理解。接下来，我们将探讨PL/SQL编程与事务管理，在这一章节中，我们将了解如何通过编程来处理更复杂的业务逻辑，以及如何管理数据库事务，确保数据的一致性和完整性。

4. PL/SQL编程与事务管理

4.1 PL/SQL编程基础

4.1.1 PL/SQL基本语法

PL/SQL（Procedural Language/SQL）是Oracle数据库提供的过程式编程语言，用于存储和执行复杂的SQL语句。PL/SQL结合了SQL的非过程特性和过程式语言的流程控制和数据处理能力。以下是一些PL/SQL编程的基本语法规则：

匿名块与命名块 ：PL/SQL程序可以是匿名块（无名称），直接在SQL*Plus或SQL Developer中运行，也可以是有名称的存储过程或函数。
变量与数据类型 ：在PL/SQL中，可以声明变量，并为其指定数据类型，如 NUMBER、VARCHAR2、DATE 等。
控制结构 ：PL/SQL使用标准的过程式编程控制结构，包括条件语句（如 IF-THEN-ELSE）和循环语句（如 LOOP、WHILE、FOR）。
异常处理 ：异常处理机制用于处理程序执行期间遇到的错误。PL/SQL定义了预定义异常以及用户定义异常。
代码块组织 ：PL/SQL代码块由三个主要部分组成：声明部分、执行部分和异常处理部分。

一个简单的PL/SQL匿名块示例：

DECLARE
  v_counter NUMBER := 0;
BEGIN
  WHILE v_counter < 5 LOOP
    dbms_output.put_line('Counter Value: ' || v_counter);
    v_counter := v_counter + 1;
  END LOOP;
EXCEPTION
  WHEN OTHERS THEN
    dbms_output.put_line('An error occurred!');
END;

在上述代码中，声明了变量 v_counter 并初始化为0。在执行部分，使用 WHILE 循环打印计数器的值，直到达到5。如果在执行期间出现任何异常，异常处理部分将捕获并输出错误信息。

4.1.2 存储过程和函数的编写

存储过程 是一组为了完成特定功能的SQL语句集，它可以被赋予一个名称，并通过该名称被调用。它与PL/SQL匿名块的主要区别是它可以在数据库中被存储和命名。

创建存储过程的基本语法如下：

CREATE OR REPLACE PROCEDURE procedure_name (parameters)
AS
  -- Declaration section
BEGIN
  -- Executable section
  [EXCEPTION
    -- Exception handling section]
END procedure_name;

函数也类似存储过程，但必须返回一个值。创建函数的基本语法如下：

CREATE OR REPLACE FUNCTION function_name (parameters) RETURN return_type
IS
  -- Declaration section
BEGIN
  -- Executable section, including a return statement
  [EXCEPTION
    -- Exception handling section]
END function_name;

为了编写有效的存储过程和函数，需要了解如何参数化输入输出，处理事务以及如何在数据库中进行递归调用。正确的存储过程和函数不仅能够提高代码的可维护性，还能提升执行效率。

接下来，我们将探讨事务管理与并发控制，这是数据库编程中确保数据一致性和完整性的重要组成部分。

5. 数据库备份恢复与性能优化

5.1 数据库备份与恢复技术

5.1.1 物理备份与逻辑备份

数据库备份是数据库管理员（DBA）日常维护工作的核心部分，其目的是为了确保数据的完整性和可用性。在Oracle数据库中，备份可以分为物理备份和逻辑备份两大类。

物理备份 指的是复制数据库文件的过程，这些文件包括数据文件、控制文件、重做日志文件等。物理备份可以是热备份或冷备份。热备份在数据库运行时进行，对生产环境影响较小；冷备份则需要在数据库关闭后进行，操作简单，但对服务的可用性有一定影响。

备份类型示例代码：

-- 冷备份
ALTER TABLESPACE system BEGIN BACKUP;
ALTER TABLESPACE users BEGIN BACKUP;
-- 复制文件至备份目录
-- ALTER TABLESPACE system END BACKUP;
-- ALTER TABLESPACE users END BACKUP;

-- 热备份
ALTER TABLESPACE example BEGIN BACKUP;
ALTER TABLESPACE example END BACKUP;

以上命令用于在热备份中标识表空间的开始和结束，使得在备份期间发生的数据变动可以被正确地记录。

逻辑备份 则是指使用Oracle提供的数据泵工具（Data Pump）或传统导出工具（EXP/IMP）导出和导入数据库对象（如表、视图、存储过程等）的过程。逻辑备份对系统资源要求较低，易于管理和维护，但是恢复速度往往较慢，尤其是大型数据库系统。

逻辑备份示例代码：

# 使用数据泵导入
expdp system/password@db_name DIRECTORY=backup_dir DUMPFILE=expdp.dmp LOGFILE=expdp.log SCHEMAS=example

5.1.2 点对点恢复与不完全恢复

在面对数据丢失或损坏的情况下，恢复技术显得尤为重要。点对点恢复与不完全恢复是两种常见的恢复方法。

点对点恢复 （Point-in-time Recovery, PITR）允许DBA将数据库恢复到过去的一个特定时间点，这通常用于处理用户误删除数据或数据损坏的情况。通过指定特定的归档日志和时间点，可以实现精确的数据恢复。

不完全恢复 则是在某些情况下，DBA只需要恢复到数据丢失之前的一个时间点，而不必等到所有的归档日志都应用完成。这种方法适用于灾难恢复等场景，可以减少恢复时间，并允许DBA评估损失并采取相应措施。

PITR示例步骤：

-- 关闭数据库
SHUTDOWN IMMEDIATE;
-- 备份当前的控制文件
ALTER DATABASE BACKUP CONTROLFILE TO '/path/to/controlfile.bak';
-- 启动数据库至mount状态
STARTUP MOUNT;
-- 恢复数据文件
RESTORE DATABASE UNTIL TIME 'YYYY-MM-DD:HH24:MI:SS';
-- 应用归档日志
RECOVER DATABASE UNTIL TIME 'YYYY-MM-DD:HH24:MI:SS';
-- 打开数据库
ALTER DATABASE OPEN RESETLOGS;

不完全恢复示例步骤：

-- 关闭数据库
SHUTDOWN IMMEDIATE;
-- 启动数据库至mount状态
STARTUP MOUNT;
-- 恢复数据文件
RESTORE DATABASE UNTIL SEQUENCE 12345;
-- 应用部分归档日志（取决于丢失的时间点）
RECOVER DATABASE UNTIL CANCEL;
-- 打开数据库
ALTER DATABASE OPEN RESETLOGS;

5.2 性能优化策略

5.2.1 SQL优化方法与工具

数据库的性能优化是保障系统运行效率的关键。SQL语句作为数据库的核心，其执行效率直接影响整个系统的性能。

使用 执行计划（EXPLAIN PLAN） 可以查看SQL语句的执行细节，这是诊断和优化SQL的有力工具。通过分析执行计划，DBA能够识别出执行效率低下的SQL语句，并对其进行重写或调整索引。

EXPLAIN PLAN FOR
SELECT * FROM employees WHERE department_id = 10;

执行完毕后，可以查询PLAN_TABLE来获取执行计划信息：

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

另外，使用 Oracle SQL Tuning Advisor 对特定SQL进行性能优化建议，是自动化优化过程的常见方式。

BEGIN
  DBMS_SQLTUNEADVISOR.REPORT_TUNE_TABLE (
    TABLE_NAME => 'SQL_TUNE_SET',
    SCENARIO => 'Tuning the SQL statements in the SQL Tuning Set',
    TASK_NAME => 'My_Tuning_Task',
    DESCRIPTION => 'A tune task for a SQL Tuning Set');
END;

5.2.2 数据库级别的性能调整

数据库级别的性能调整通常包括内存管理和存储配置优化。

内存管理优化 包括合理配置SGA（System Global Area）和PGA（Program Global Area）来提升缓冲区缓存的命中率和减少物理I/O操作。合理分配内存大小对于数据库性能至关重要。

ALTER SYSTEM SET sga_target = 2G SCOPE=BOTH;
ALTER SYSTEM SETpga_aggregate_target = 1G SCOPE=BOTH;

存储配置优化 涉及调整数据库文件的存储参数，例如自动扩展、多路径I/O等，确保数据读写的高效率。

ALTER DATABASE DATAFILE '/u01/app/oracle/oradata/orcl/users01.dbf' AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED;

除了对内存和存储的调整，监控数据库性能，定期执行性能分析报告，以及及时更新和打补丁数据库软件也是保持数据库性能的关键策略。

数据库性能优化是一个持续的过程，需要定期审视系统运行状况，并对可能出现的性能瓶颈做出响应。通过以上方法和工具，DBA能够有效提升数据库的性能，确保数据处理的高效率和准确性。

6. 数据库安全性管理与高级特性

在现代信息技术环境中，确保数据库的安全性至关重要。同时，随着业务需求的增长，数据库的高级特性也成为了企业系统稳定运行的关键。本章节将探讨如何管理数据库的安全性，并对一些高级特性进行介绍。

6.1 数据库安全性管理

数据库安全性管理旨在防止未授权的访问、数据泄露、数据篡改及其他安全威胁。有效的安全性管理能保证企业数据资产的安全与隐私。

6.1.1 用户与角色的管理

用户是数据库操作的执行者，而角色则是一组权限的集合。在Oracle数据库中，用户和角色的管理是安全性管理的基础。

创建用户

在Oracle中创建新用户的基本语法如下：

CREATE USER username IDENTIFIED BY password;

其中 username 是新创建的用户名， password 是用户密码。

分配角色和权限

创建用户后，需要为用户分配角色和权限，以允许他们执行特定操作。

GRANT role_name TO username;

role_name 是你希望赋予用户的角色名称。

用户锁定与解锁

在用户不再需要访问数据库时，应当锁定用户账户，防止滥用。

ALTER USER username ACCOUNT LOCK;

当需要解锁时，使用下面的语句：

ALTER USER username ACCOUNT UNLOCK;

6.1.2 权限的分配与审计策略

权限分配是授权特定用户执行特定数据库操作的过程，而审计策略则是用来监控数据库活动的。

分配权限

可以使用 GRANT 语句分配权限，例如，给用户授予查询某个表的权限：

GRANT SELECT ON table_name TO username;

table_name 是被授予权限的表。

审计策略

审计是通过记录数据库活动来追踪潜在的恶意行为。以下是开启审计策略的示例：

AUDIT INSERT, UPDATE, DELETE ON table_name BY user_name;

6.2 数据库高级特性探索

随着技术的演进，现代数据库系统提供了许多高级特性，比如分布式数据库管理和高可用性解决方案。

6.2.1 分布式数据库与数据仓库基础

分布式数据库允许数据跨多个位置存储，同时保持逻辑上的统一。数据仓库则是用于决策支持的系统，侧重于数据存储和管理。

分布式数据库的优势

负载均衡：可将工作负载分散至多个服务器，避免单点瓶颈。
数据冗余：增加数据副本，提高数据的安全性与可用性。
地理分散：提升全球范围内的数据访问速度和可靠性。

数据仓库的设计原则

高度整合：确保数据的一致性和准确性。
可扩展性：支持大量的数据和多用户访问。
数据挖掘：提供分析工具帮助用户发现业务洞察。

6.2.2 数据库的高可用性解决方案

数据库的高可用性解决方案是确保在发生故障时仍能保持服务的连续性。以下是几种常见的高可用性策略。

数据库集群

集群技术通过将多个数据库节点组成一个集群来提供高可用性和扩展性。Oracle Real Application Clusters (RAC) 就是实现数据库集群的一种方式。

数据库备份和恢复

定期备份数据库数据，一旦发生故障，能够快速恢复至故障前的状态。

故障转移与自动切换

当主数据库发生故障时，可以将服务自动切换到备用数据库，以减少停机时间。

在本章中，我们探讨了数据库安全性和高级特性的基本概念和实施方法。在下一章，我们将继续探索更多高级主题，如数据库优化技术，提升企业级数据库系统的稳定性和性能。

本文还有配套的精品资源，点击获取

简介：在本课程的第三天，我们将深入探讨Oracle数据库的核心技术和概念，包括SQL基础、数据类型、表结构与约束、索引、视图、PL/SQL编程、事务与并发控制、备份与恢复、性能优化以及安全性等内容。通过实例学习，旨在让学生掌握Oracle数据库的日常操作和管理，为未来学习高级特性打下坚实的基础。

本文还有配套的精品资源，点击获取

王小约

关注

3
点赞
踩
7

收藏

觉得还不错? 一键收藏
0
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫