Oracle 服务器组成

image-20220519150021800

image-20220519150232801

区（Extent）

是一组连续的数据块。在Oracle数据库中，分配空间就是以数据区为单位的。当一个段创建或需要附加空间时，系统会为之分配新的数据区。

  • MinExtents定义了段中能包含的最小区间数量，创建段时，它所包含的区间数量只能为这个数。
  • MaxExtents定义了段中区间数的最大数。

段（Segment）

段是由一个或多个连续或不连续的区组成的逻辑存储单元。 数据库模式对象在逻辑上是以段来占据表空间的大小。段代表特定数据类型的数据存储结构。

段的类型

  • 数据段
  • 索引段
  • 临时段
  • 回退段

数据段

数据段用来存储表或簇的数据，可以细分为普通表数据段、分区表数据段、簇数据段和索引表数据段四种。

image-20220519150624597

索引段

用来存放索引信息，主要包括存储ROWID和索引键值索引段与其相应的表段经常会被同时访问，为了减少硬盘访问的冲突，索引段与表段可以放到处于不同物理位置的表空间中 。

临时段

当用户进行排序查询时，如果在指定的内存无法完成排序，Oracle将自动从用户默认的临时表空间中指派临时段进行排序。会话结束，数据将从临时段中自动删除。

回退段

用于存放数据修改之前的值（包括数据修改之前的位置和值），利用回滚段恢复被回滚事务对数据库所做的修改，或者为事务提供读一致性保证。

image-20220519150736635

表空间（Tablespace）

  • 表空间概念是Oracle数据库的一个主要逻辑组织结构，Oracle数据库在逻辑上可以划分为一系列的逻辑空间，每一个逻辑空间就可以称为一个表空间。
  • 一个数据库由有一个或多个表空间构成，不同表空间用于存放不同应用的数据。
  • 一个表空间对应一个或多个数据文件，数据文件大小决定了表空间的大小。
  • 一个数据文件只能从属于一个表空间。
  • 一个数据库对象只能存储在一个表空间中（分区表和分区索引除外），但可以存储在该表空间所对应的一个或多个数据文件中。

数据库、表空间、数据文件、数据库对象之间的关系

SYSTEM 表空间

每个Oracle数据库必须具有一个默认系统表空间，即SYSTEM表空间，该表空间是在创建数据库时自动创建的。

SYSTEM表空间主要用于存储下列信息

数据库的数据字典：

数据字典里存有用户信息、用户的权限信息、所有数据对象信息、表的约束条件、统计分析数据库的视图等。

PL/SQL程序的源代码和解释代码，包括存储过程、函数、包、触发器等。开发者不应把模式对象（如表等）存放在SYSTEM表空间，以免影响数据库的稳定性与执行效率。

SYSAUX表空间

是随着数据库的创建而创建的，它充当SYSTEM的辅助表空间，主要存储除数据字典以外的其他对象。SYSAUX表空间也是存储数据的默认位置。

  • 临时表空间（Temp tablespace）

相对于其他表空间而言，临时表空间主要用于存储Oracle数据库运行期间所产生的临时数据。

  • 撤销表空间（Undo Tablespace)

用于保存Oracle数据库回滚信息，Undo表空间由回滚段构成。

用户表空间（Users Tablespace)

用户表空间，用于存放永久性用户对象的数据和私有信息。

数据库物理存储结构

image-20220519151438637

Oracle数据库物理存储结构的构成

image-20220519151450508

数据文件的内容

  • 用于保存数据库中数据的文件(扩展名为DBF)
  • 系统数据、数据字典数据、临时数据、索引数据、应用数据等都物理地存储在数据文件中。

控制文件的性质

  • 控制文件是一个很小的二进制文件(扩展名为CTL)
  • 在加载数据库时，实例必须首先通过初始化参数文件找到数据库的控制文件。
  • 在数据库运行期间，控制文件始终在不断更新，以便记录数据文件和重做日志文件的变化。
  • 每个数据库至少拥有一个控制文件。一个数据库也可以同时拥有多个控制文件,Oracle可以同时护多个控制文件，这些文件之间是镜像的。

控制文件内容：

数据库名称和标识

数据库创建的时间

表空间名称

数据文件和重做日志文件的名称和位置

当前重做日志文件的序号

最近检查点

回退段的开始和结束

重做日志的归档信息

备份信息

数据库恢复所需要的同步信息

重做日志文件概述

  • 重做日志文件：保存了用户对数据库所作的更新操作（插入、删除或修改），包含的主要信息是事务的开始和结束、事务中每项操作的对象和类型、更新操作前后的数据值等。
  • 在数据库恢复时，可以从日志文件读取原始记录。

重做日志文件的工作过程

  • 每个数据库至少需要两个重做日志文件，采用循环写的方式进行工作。当一个重做日志文件写满后，进程LGWR就会移到下一个日志组，称为日志切换，同时信息会写到控制文件中。
  • 重做日志文件工作流程

image-20220519152203986

重做日志文件归档过程

image-20220519152252593

实例（ Oracle instance）概述

数据库与实例的关系

  • 数据库是Oracle用于保存数据的一系列物理结构和逻辑结构
  • 用户直接与实例交互，由实例访问数据库。
  • 每个数据库至少有一个与之对应的实例
  • Oracle instance由后台进程和共享内存组成

Oracle内存结构

系统全局区 SGA（System Global Area）

SGA区是由Oracle分配的共享内存结构，包含一个数据库实例共享的数据和控制信息。当多个用户同时连接同一个实例时，SGA区数据供多个用户共享，所以SGA区又称为共享全局区。

SGA 包含的内存结构有：

  • 数据高速缓冲区（Database Buffer Cache）

  • 共享池（Shared Pool）

  • 重做日志缓冲区（Redo Log Buffer）

  • 其他结构 (例如锁管理、统计数据）

  • 在SGA，有几个可配置的可选内存结构:流池

    （Stream POOL）

    大池（Large Pool）

    Java池（Java Pool）

    数据高速缓冲区

    功能数据缓冲区存储的是从数据文件中检索出来的数据拷贝。应用程序要访问的数据必须从磁盘的数据文件读到数据缓冲区中处理。在数据缓冲区中被修改后的数据由DBWR写到硬盘的数据文件中永久保存。提高获取和更新数据的性能

    image-20220519152717146

  • “脏”缓存块（Dirty Buffers）

保存的是已经被修改过的数据

  • 空闲缓存块（Free Buffers）

不包含任何数据，它们等待后台进程或服务器进程向其中写入数据。

  • 命中缓存块(Pinned Buffers)

正被使用，或者被显式声明为保留的缓存块

重做日志缓冲区

功能

用于缓存在用户对数据进行修改的操作过程中生成的重做记录。当重做日志缓冲区被添满时，由LGWR把重做日志缓冲区的内容写到磁盘的重做日志文件中。

image-20220519153653172

共享池

功能

用于缓存与SQL或PL/SQL语句、数据字典、资源锁以及其他控制结构相关的数据

组成

库缓存

  • 库缓存用于缓存已经解释并执行过的SQL语句和PL/SQL程序代码，以提高SQL或PL/SQL程序的执行效率。
  • 包括SQL工作区和PL/SQL工作区

数据字典

  • 缓存区数据字典缓存区保存最常用的数据字典信息

程序全局区PGA（Program Global Area）

是一块包含服务进程数据以及控制信息的内存区域。 是私有内存区域，不能共享。

Oracle学习：

登录：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22


 / as sysdba;         超级用户（sysdba）

 scott/tiger

conn / as sysdba;  以DBA身份连接数据库

select distinct owner from all_objects; 查看当前系统用户

select username from dba_users; 查看当前所有用户

alter user system identified by unary123; //修改帐号的密码

grant dba,sysdba to unary;         给与unary用户DBA权限

GRANT SELECT ON SCOTT.EMP TO unary; //把EMP这个表所有权给unary，用户赋予表查询权限

CONN登录
SQL> conn sys（用户名）/manager（密码）@orcl（实例名）as sysdba;  连接数据库

SQL> conn system/2008Bc1223@orcl as sysdba;

oracle 创建表时 指定表表空间

1
create table tablename(id int) tablespace tablespacename;

解释：在表空间“tablespacename”上创建表“tablename”。以上语句就实现了为表指定表空间

修改表

oracle 查询表空间所有表、及表所有的表空间

查询表空间所有表：

1
select table_name from all_tables where TABLESPACE_NAME='表空间';

表空间名字一定要大写。

查询表所在的表空间

1
select * from user_tables where table_name=‘表名';

一个数据库由多个表空间构成，表空间又是由段构成，而段又是由区构成，而区又是由块构成，这样构成的可以提高数据的效率；

实际上表空间又是由多个数据文件构成，当表空间不够使用时，可以增加多个数据文件来增大表空间

增大表空间语句

alter tablespace 表空间名字

add filedata ‘d:\db2.dbf’ size 200m ;

表空间脱机：alter tablespace 表空间 offline;

oracle 查看用户所在表空间

  • 查看所有表空间
1
select *  from user_tablespaces
  • 查看数据库里面所有用户，前提是你是有dba权限的帐号，如sys,system
1
select username from dba_users;
  • 查看你能管理的所有用户！
1
2
select * from all_users

  • 查看当前用户信息
1
select * from user_users;
  • 查看表所属的变空间
1
select table_name 表名 ,tablespace_name 所使用表空间 from user_tables;
  • 给已存在的用户指定一个表空间
1
2
alter user username default tablespace userspace;

  • 创建用户的时候指定表空间
1
create user username identified by userpassword default tablespace userspace;
  • 查看当前用户所在表空间
1
select username,default_tablespace from user_users;--查看用户所拥有的角色
1
2
3
4
5
6
 
-- user_tab_columns 自定义的字段
SELECT column_name FROM user_tab_columns where table_name = upper('表名') 
 
-- user_tab_cols 包含oracle创建的隐藏字段
SELECT column_name FROM user_tab_cols where table_name = upper('表名')

  • oracle 查看表结构

    1
    desc DEPARTMENT;

    image-20220311111821198

  • 显示创建表的语句

1
2
SQL> SET LONG 9999
SQL> SELECT DBMS_METADATA.GET_DDL('TABLE','NCHAR_TST') FROM DUAL;

image-20220311113348761

1、在SQL PLUS 中创建一个名永久表空间：表空间名为首字母+02，各参数自定。

1
2
3
create tablespace jwz
 2   datafile 'D:\Oracle\jwz01' size 10m
 3  extent management local uniform size 1m;

img

2、在SQL PLUS 中用ALTER TABLESPACE 修改表空间属性（内容同学们自定）。示例如下(第一条语句是改表空间名，第二条是向表空间增加数据文件）：

1
2
```sql
alter tablespace jwz rename to jwz02
1
2
3
4
5
6
7
8
9
10
11
12
13

![img](https://gitee.com/jwz--jwz/cloud-img/raw/master/image/202203102126201.jpg)

 3、在SQL PLUS 中创建一个名临时表空间：表空间名为姓名首字母+temp+02，各参数自定

![img](https://gitee.com/jwz--jwz/cloud-img/raw/master/image/202203102126118.jpg)

4、删除表空间 ：将1.2中创建的表空间删除 

```sql
SQL> drop tablespace jwztemp02 including contents
  2  ;

image-20220310212859291

创建表指令：

在OEM中创建如下学院表(department)。

字段名 代码 类型 约束
学院编号 deptno Varchar(10) 主键
学院名 deptname char(10) 非空

在SQL*PLUS中创建班级表(class)。

字段名 代码 类型 约束
班级编号 classno char(5) 主键
班级名 classname char(10) 非空
学院编号 deptno Varchar2(10) 与学院表中deptno外键关联 
1
2
3
4
5
6
SQL> create table class （
  2  classno char(5) not null   primary key,
  3  classname char(10) not null,
  4  deptno varchar2(10) ,
  5  constraint fk_dept foreign key(deptno) references department(detno)
  6  );

image-20220318103956023

在SQL*PLUS中创建课程表(course) 。

字段名 代码 类型 约束
课程编码 cno char(5) 主键
课程名 cname char(20) 非空
先行课 cpno Char(5)
学分 ccredit Number(3,1)

image-20220325103754594

在OEM中创建学生表(student)：且将数据分散到四个区当中。

字段名 代码 类型 约束
学号 sno char(12) 主键
姓名 sname char(12) 非空
性别 ssex char(2) 只取男、女，默认值为女
出生日期 sbirthday Date
入学成绩 sscore number(5,1)
班级编号 classno char(5) 与班级表中classno外键关联

image-20220325103522342

1
2
3
4
5
6
7
8
9
10
SQL> create table student (
  2  sno char(12) primary key ,
  3  sname char(12) not null,
  4  ssex char(2) default '女' check(ssex in ('男','女')),
  5  sbirthday Date ,
  6  sscore number(5,1),
  7  classno char(5),
  8  constraint fk_class foreign key(classno) references class(classno));

表已创建。

SQL> SET LONG 9999

SQL> SELECT DBMS_METADATA.GET_DDL(‘TABLE’,’NCHAR_TST’) FROM DUAL;

1
2
3
4
SQL> create profile profilejwz limit
  2  cpu_per_call 100
  3  idle_time 5
  4  failed_login_attempts 3;

image-20220325111533237

1
2
3
4
5
6
7
8
alter "profilejwz" limit connetc_time 200
 2  sessions_per_user 2
 3  peivate_sga 512k
 4  password_life_time 200
 5  password_reuse_max 5
 6  password_reuse_time unlimited
 7  password_lock_time 1
 8  ;

image-20220325113129549

1
2
3
4
5
SQL> create tablespace jwz82
  2  datafile 'D:\Oracle\jwz82.DBF'
  3  size 100M
  4  autoextend on next 1024M
  5  maxsize 10240M;

备份数据库：

image-20220408111405808

image-20220408111347584

image-20220408121949511

image-20220408121918470

任务2：逻辑备份工具Expdp模式方式备份 ：备份SCOTT模式下的所有表。

2.1利用Expdp导出SCOTT模式下的所有表。

1
C:\Users\wz>expdp scott/tiger directory = duapdir dumpfile =scott.dmp logfile=scott.log schemas=scott

image-20220408122502259

image-20220408122533497

删除表语句：

1
drop table scott.EMP;

image-20220408122958124

image-20220408123125187

数据处理

1
desc course;
1
insert into course values('001','数据库原理','',4);

image-20220408113551043

恢复数据库表

1
D:\Oracle\backup>impdp scott/tiger directory = duapdir dumpfile =emp.dmp tables =emp

利用Impdp导入scott.emp表。

image-20220408123548653

查看scott.emp表是否存在

1
select emp.ename from scott.emp where empno =7902;

image-20220408123830981

将SC表中最低的成绩改为59分。

img

1
select min(GRADE) from sc ;
1
update sc set GRADE='59' where grade in (select min(GRADE) from SC );

image-20220422111052456

显示选修了与学号为“100”的学生相同的某门课程且成绩相同的学生的学号、姓名。（注意：只要有一门课程与“100”同学相同且分数相同的都是查询对象）。

image-20220422112500265

image-20220422112512010

先进行内连接

找出符合的学号

1
select SC1.sno  from sc sc1 ,sc sc2 where sc2.sno = '1' and sc1.cno =sc2.cno and sc1.grade =sc2.grade;

再进行外连接：

1
select sno,sname from "student"  where sno in (select SC1.sno  from sc sc1 ,sc sc2 where sc2.sno = '1' and sc1.cno =sc2.cno and sc1.grade =sc2.grade);

显示个人平均成绩在85分以上的学生姓名。

1
select  *  from "student" s inner join （select sno ,avg(grade) avg  from  sc     group by sno) a on s.sno = a.sno where avg > 85 ;

image-20220422131120327

image-20220422131148976

PL/SQL基本语法:

声明部分 - 此部分是以关键字DECLARE开头。这是一个可选部分，并定义了程序中要使用的所有变量，游标，子程序和其他元素。

可执行命令部分 - 此部分包含在关键字BEGIN和END之间，这是一个强制性部分。它由程序的可执行PL/SQL语句组成。它应该有至少一个可执行代码行，它可以只是一个NULL命令，表示不执行任何操作。

异常处理部分 - 此部分以关键字EXCEPTION开头。这是一个可选部分，它包含处理程序中错误的异常。

每个PL/SQL语句以分号(;)结尾。 使用BEGIN和END可以将PL/SQL块嵌套在其他PL/SQL块中。

语法:

PL/SQL基本循环语句 - PL/SQL教程™ (yiibai.com)

Loop:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
set SERVEROUTPUT ON  SIZE 100000;
declare 
x number := 10;
BEGIN 
       LOOP
                  dbms_output.put_line(x);
                  x :=x+10;
                  If x>50 then
                  exit;
                  END IF;
       END LOOP;
       dbms_output.put_line('After Exit x is :'|| x);
 END;


image-20220329093406942

利用简单循环求1~100之间的偶数：

1
2
3
4
5
6
7
8
9
10
11
12
13
DECLARE
    v_counter BINARY_INTEGER :=1;
    v_sum NUMBER :=0;
   BEGIN 
           LOOP
                    IF mod(v_counter,2) =0 then
                    v_sum := v_sum+v_counter;
            END IF;
            v_couner :=v_counter +1;
            exit when v_counter>100;
            end loop;
            DBMS_OUTPUT.PUT_LINE(v_sum);
            END;

image-20220401113735642