[Oracle] 逻辑结构实验与总结

ORACLE 专栏收录该内容
5 篇文章 0 订阅

一. 逻辑体系结构图



  

二. 逻辑结构图组成介绍



  从上表可以看出,一个数据库是由多个表空间(tablespace)组成,一个表空间又由多个段(segment)组成,一个段又由多个区(extent)组成,一个区则由多个块(block)组成。

  一个数据库中,UNDO和SYSTEM表空间是必须存在的。

  举个例子:

  话说张三是大坝县的一个农民,每年秋收的稻谷都必须放到县里的粮仓里统一管理。跟他一同去的还有李四、王五等以种稻谷为主的农民。

  刚开始的时候县里粮仓给他分配了一块5㎡的地方(block块)给他存放。后来不够用了,他又向管粮仓的领导申请了5㎡。谁知道今年大丰收,这10㎡的地方根本就不够他用,下次又申请了5㎡。这三两天就要了几次地方放稻谷,领导觉得烦了,这样下去岂不把我忙死?于是领导批准张三可以一次性申请90㎡(extent区),这样的话张三就可以少去烦领导要地方放稻谷了。可是随着张三的地一年一年地增多,收获的稻子也一年比一年多,90㎡也不够他用了,于是他再向领导申请,但这次他轻松多了,只去了一次,90㎡就到手了。。。但是很快,张三的业务发展迅速,已经不止种稻谷了,还种了苹果!他又用同样的方式申请了一块90㎡的地方放苹果,再下一年业务增加了种植菠萝。。。就这样一直发展,张三靠起家了,他这次真正的大丰收,县里的粮仓给他分配的地方又不够用了,张三就直接把整个粮仓(segment段)买了下来,正好储备所有的农作物。但第二年,张三已经发展到养殖业了,于是直接在旁边自己建了几个仓库,并且给这些仓库(包括之前的粮仓)起了个名字,张生仓库(tablespace)

  而李四、王五也几乎同时,也跟张三一样发家致富,各自也建了自己的仓库,李生仓库(tablespace1)、王生仓库(tablespace2)。。。

  他们几十个发家致富的农民的仓库共同组成了大坝县的粮食仓库(database)


三. 实践出真知 ([x] - 2015/12/30)



实验一:创建测试表TEST_A,插入数据并查看数据块分布情况。

实验过程:

-- 1.创建一个有1000条记录的表
CREATE TABLE TEST_A AS SELECT * FROM dba_objects do WHERE rownum<=1000;

-- 2.查看表中的数据块存储位置
SELECT a.ROWID ,b.file_name ,dbms_rowid.rowid_block_number(a.rowid) block_number,  
  dbms_rowid.rowid_row_number(a.rowid) row_number  
    FROM test_a a ,dba_data_files b  
  WHERE b.file_id=dbms_rowid.rowid_to_absolute_fno(a.rowid,user,'TEST_A');  

-- 3.查看每个数据块分布情况(每个实验都用到此SQL)
SELECT dbms_rowid.rowid_block_number(a.rowid) as block_number,count(*) AS record FROM test_a a ,dba_data_files b  
  WHERE b.file_id=dbms_rowid.rowid_to_absolute_fno(a.rowid,user,'TEST_A')
  GROUP BY rollup(dbms_rowid.rowid_block_number(a.rowid));

数据块位置结果(样本):


....

数据块分布情况统计:

-----数据块-----   ----数据行数---

         

实验结论:当我们在数据库中创建表时,系统会自动分配连续的N块数据块存放数据,由于块大小固定,插入数据时,每个块里面有大致相当的数据量,因为具体每行数据字节数都会不一样,所以块存储的数据量大致一样。


实验二:在实验一基础上删除数据再重新查询数据在块中的分布情况

实验过程:

-- 4.删除表TEST_A在数据块931中的数据
DELETE FROM TEST_A a WHERE 
dbms_rowid.rowid_block_number(a.rowid)=931;

删除后数据块分布情况:

-----数据块-----   ----数据行数---

     


插入数据,再观察块中数据量分布情况:

-- 5.插入数据,再观察数据分布情况
INSERT INTO TEST_A SELECT * FROM dba_objects do WHERE rownum<=80;

-----数据块-----   ----数据行数---

   


实验结论: 删除表数据时,之前数据库向系统申请的数据块空间并不会一同删除。而当我们再次插入数据的时候,会优先填补之前为此表分配过的块,如空间不够再扩展。

实验三: 创建表TEST_B,跟TEST_A一样的数据。

实验过程:

-- 1.创建一个有1000条记录的表
CREATE TABLE TEST_B AS SELECT * FROM dba_objects do WHERE rownum<=1000;
-- 2.查看每个数据块分布情况
SELECT  dbms_rowid.rowid_block_number(a.rowid) as block_number,count(*) AS record FROM test_b a ,dba_data_files b  
  WHERE b.file_id=dbms_rowid.rowid_to_absolute_fno(a.rowid,user,'TEST_B')
  GROUP BY rollup(dbms_rowid.rowid_block_number(a.rowid));

-----数据块-----   ----数据行数---

       

实验总结:

1. 创建新表会分配新块。

2. 留意TEST_A,创建后终结块号为943,而在创建TEST_B的时候开始块号已经是947,中间的一段944~946去了哪?答案体现在对区(EXTEND)的理解上了,区是系统分配的最少单位(虽然块是最小存储单位),所以可以推测944~946块也是分配给了TEST_A。


四. 总结



  对于我们在数据库里新建数据库(database),在数据库中建立多个表空间(tablespace),在每个表空间内建表。例如我们可以分配多个用户,在user1用户下建立table1,table2,user2下建立table3,table4,user1、user2就等于不同的表空间,table1、table2是建立在表空间下的不同段(segment),而每张表的每个数据就是块(block),一列数据可看做一个区(extent),区满了之后不断扩展就组成了表。

 

本文只作为总结用,如有错误请更正。
  • 0
    点赞
  • 0
    评论
  • 0
    收藏
  • 打赏
    打赏
  • 扫一扫,分享海报

实验一 SQL*PLUS练习 【实验目的】 (1) 了解Oracle的工作环境和基本使用方法。 (2) 练习标准SQL的数据操作,查询命令及其查询优化。 (3) 学会使用高级SQL命令,排序、分组、自连接查询等。 (4) 学会使用SQL*PLUS命令显示报表,存储到文件等。 【实验内容】 一、 准备使用SQL*PLUS 1. 进入SQL*PLUS 2. 退出SQL*PLUS 3. 显示表结构命令DESCRIBE SQL>DESCRIBE emp 使用DESCRIBE(缩写DESC)可以列出指定表的基本结构,包括各字段的字段名以及类型、长度、是否非空等信息。 4. 使用SQL*PLUS显示数据库中EMP表的内容 输入下面的查询语句: SQL>SELECT * FROM emp; 按下回车键执行查询 5. 执行命令文件 START或@命令将指定文件调入SQL缓冲区中,并执行文件内容。 SQL>@ 文件名(文件后缀缺省为.SQL)或 SQL>START 文件名 文件中每条SQL语句顺序装入缓冲区并执行。 二、 数据库命令——有关表、视图等的操作 1. 创建表employee 例1 定义一个人事信息管理系统中存放职工基本信息的一张表。可输入如下命令: SQL>CREATE TABLE employee (empno number(6) PRIMARY KEY, /* 职工编号 name varchar2(10) NOT NULL, /* 姓名 deptno number(2) DEFAULT 10, /* 部门号 salary number(7,2) CHECK(salarycreate table emp2 as select * from emp where 1=2; 在命令的where子句中给出1=2,表示条件不可能成立,因而只能复制表结构,而不能复制任何数据到新表中去。另外,还可以复制一个表的部分列定义或部分列定义及其数据。 三、 Oracle数据库数据查询 1、单表查询 2、多表查询 四、 SQL*PLUS常用命令 表1 常用报表格式化名命令 命令 定义 Btitle 为报表的每一页设置底端标题 Column 设置列的标题和格式 Compute 让SQL*PLUS计算各种值 Remark 将某些字标记为注释 Set linesize 设置报表的行宽字符数 Set newpage 设置报表各页之间的行数 Spool 使SQL*PLUS将输出写入文件中 Start 使SQL*PLUS执行一个sql文件 Ttitle 设置报表每页的头标题 Break 让SQL*PLUS进行分组操作 例3 建立一个批命令文件对查询到的数据以报表的形式输出并将其保存到指定的文件中。 处理方法:利用SQL*PLUS语言工具(也可以使用其他文本编辑器)建立批命令的.SQL文件。在“SQL>”提示符下,使用EDIT命令在”E:\”中建立SCGB.SQL文件。 SCGB.SQL文件中的命令组如下: SQL>EDIT E:\ SCGB.SQL SET echo off SET pagesize 30 SET linesize 75 TTITLE’2008年4月10号’CE’公司职员基本情况登记表’R’Page:’ FORMAT 99- >SQL.PNO SKIP 1 CE’===========================’ BTITLE COL 60 ’制标单位’ TAB 3 ‘人事部’ COLUMN empno heading ‘职工|编号’ COLUMN ename format a10 heading ‘姓 名’ COLUMN job heading ‘工 种’ COLUMN sal format $99,990 heading 工 资’ COLUMN comm Like sal heading ‘奖 金’ COLUMN deptno format 9999 heading ‘部门|编号’ COLUMN hiredate heading ‘参加工作时间’ SPOOL e:\sjbb /*在E盘中建立格式报表输出文件,默认属性为LST BREAK on deptno skip 1 COMPUTE sum of sal comm on deptno SELECT empno,ename,job,hiredate,sal,comm,deptno from emp ORDER BY deptno,sal; SPOOL off /*终止SPOOL功能,关闭其文件。注意,此命令不可省,否则将建立空文件。 五、 实验内容 1、以cs+学号为用户名创建用户,并授予用户创建数据对象的权限。 2、复制emp表,复制表名为emp_学号,然后将emp表中工资低于$2000 的职工插入到复制的表中。 3、对复制的emp表插入一行只包含有职工号,职工名,工资部门号四个数据 项值的记录。 4、在复制的emp表中将雇员ALLEN提升为经理,工资增至$2500, 奖(佣 )金增加40%。 5、删除复制的emp表中工资低于500的记录行。 6、列出10号部门中既不是经理,也不是秘书的职工的所有信息。 7、查找出部门所在地是CHICAGO的部门的职工姓名、工资和工种。 8、统计各部门中各工种的人数、工资总和及奖金总和。 9、查找出工资比其所在部门平均工资高的职工姓名、工种工资情况。 实验3 Oracle数据库开发环境下PL/SQL编程 【实验目的】 (1)掌握 PL/SQL 的基本使用方法。 (2)在SQL*PLUS环境下运行PL/SQL的简单程序。 (3)应用 PL/SQL 解决实际问题 【实验内容步骤】 PL/SQL块中的可执行部分是由一系列语句组成的(包括对数据库进行操作的SQL语句,PL/SQL语言的各种流程控制语句等)。在块中对数据库查询,增、删、改等对数据的操作是由SQL命令完成的。在PL/SQL块中,可以使用SQL的数据查询命令,数据操纵命令和事务控制命令。可使用全部SQL函数。PL/SQL中的SQL语句,可使用SQL的比较操作等运算符。但不能使用数据定义语句。 在PL/SQL块中使用SELECT语句时注意几点: (1)SELECT语句必须含有INTO子句。 (2)INTO子句后的变量个数和位置及数据类型必须和SELECT命令后的字段名表相同。 (3)INTO子句后可以是简单类型变量或组合类型变量。 (4)SELECT语句中的WHERE条件可以包含PL/SQL块中定义的变量及表达式,但变量名不要同数据库表列名相同。 (5)在未使用显式游标的情况下,使用SELECT语句必须保证只有一条记录返回,否则会产生异常情况。 [例3-1] 问题:编写一个过程,求和运算。 SET SERVEROUTPUT ON; DECLARE a number:=1; BEGIN a:=a+5; DBMS_OUTPUT.PUT_LINE('和为:'||TO_CHAR(a)); END; / 【例3-2】:使用%TYPE声明变量,输出制定表中的相关信息。 DECLARE my_name student.sname%TYPE; BEGIN SELECT sname INTO my_name FROM student WHERE no=’01203001’; DBMS_OUTPUT.PUT_LINE(my_name); END; / 【例3-3】问题:编写一个过程,可以输入一个雇员名,如果该雇员的工资低于2000,就给该员工工资增加10%。 declare v_sal emp.sal%type; begin select sal into v_sal from emp where ename=spName; if v_sal :NEW.sal THEN DBMS_OUTPUT.PUT_LINE('工资减少'); ELSIF :OLD.sal < :NEW.sal THEN DBMS_OUTPUT.PUT_LINE('工资增加'); ELSE DBMS_OUTPUT.PUT_LINE('工资未作任何变动'); END IF; DBMS_OUTPUT.PUT_LINE('更新前工资 :' || :OLD.sal); DBMS_OUTPUT.PUT_LINE('更新后工资 :' || :NEW.sal); END; / --执行UPDATE查看效果 UPDATE emp SET sal = 3000 WHERE empno = '7788'; 6、需要对在表上进行DML操作的用户进行安全检查,看是否具有合适的特权。 Create table foo(a number); Create trigger biud_foo Before insert or update or delete On foo Begin If user not in (‘DONNY’) then Raise_application_error(-20001, ‘You don’t have access to modify this table.’); End if; End; / 即使SYS,SYSTEM用户也不能修改foo表。 2、 利用PL/SQL编写程序实现下列触发器 1)、编写一个数据库触发器,当任何时候某个部门从dept表中删除时,该触发器将从emp表中删除该部门的所有雇员。(要求:emp表、dept表均为复制后的表) 2)、创建一个触发器,当客户下完订单后,自动统计该订单的所有图书的价格总额。 3)、创建一个触发器,禁止客户在非工作时间(早上8:00前,晚上17:00后)下订单。 五、实验心得
©️2022 CSDN 皮肤主题:精致技术 设计师:CSDN官方博客 返回首页

打赏作者

Jan丶X

你的鼓励将是我创作的最大动力

¥2 ¥4 ¥6 ¥10 ¥20
输入1-500的整数
余额支付 (余额:-- )
扫码支付
扫码支付:¥2
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值