Oracle 数据库系统笔记（一）

Oracle 数据库系统

0. 历史

image.png

四人帮创始人(左起为Ed Oates，Bruce Scott，Bob Miner，Larry Ellison)此 Scott 就是彼 Scott, Scott/tiger, tiger 为他家一只 cat 的名字.他更准确的说是 Oracle 的第一位雇员.用汇编语言写成的第一版(2.0)甲骨文系统在 1978 年正式推出.2009年 IBM 收购 SUN 失败一周后, Oracle 成功收购 SUN, Oracle 从纯软厂商走向软硬结合的唯一能和 IBM 全面抗衡的公司.

几个概念:

  • RDBMS: Relational Database Management System

一.体系结构

Oracle 服务器实例

由内存和后台进程组成的用于访问数据库的方法,由 SID 标识,默认为创建数据库时的全局数据库名，通过实例访问数据库，实例是一个共享存储区和一组与文件中的数据交互的进程。
启动数据库三部曲及对应模式:

  • NOMOUNT: 启动实例
  • MOUNT: 装载数据库
  • OPEN: 打开数据库
    命令启动:
    STARTUP [NOMOUNT | MOUNT | OPEN ] [RESTRICT] [PFILE = 初始化参数文件];
  • FORCE 强制重启,相当于 SHUTDOWN ABORT 后 startup
  • 模式切换: alter database mount|open

1.物理结构

  • Oracle 物理结构是一个或多个数据文件的集合.
D:\app\oradata\orcl.png

数据文件(*.DBF)

  • 所在数据字典: V$DATAFILE
  • 用于保存数据库的所有数据
  • 特征:
    • 一个数据文件仅与一个数据库联系
    • 一个数据文件只能从属于一个表空间
    • 一个表空间可以包含几个数据文件
    • 数据库容量越界时数据文件可以自动扩展
数据文件目录
数据文件目录
  • 创建数据文件:
-- 向 ORCL 数据库的 USERS 表空间添加一个10M的 user02 数据文件.
 alter tablespace users 
add datafile 'D:\app\oradata\orcl\user02.dbf' size 10M;
  • 删除数据文件:
-- 删除 TEMP 表空间的 temp02 数据文件.
alter tablespace temp
drop tempfile
'D:\app\oracle\orcl\temp02.dbf';

控制文件(*.CTL)

  • 一个由 Oracle 进程读写的二进制文件, 记录数据库的物理结构(数据库名,创建时间, 表空间名,数据文件和日志文件名称等), 用于标识数据库和日志文件
  • 相关数据字典视图: V$controlfile

重做日志文件(*.log)

  • 用于保存用户对数据库进行的变更操作
  • 数据字典视图: V$log, V$logfile

2.逻辑结构

Oracle 数据库逻辑结构由表空间(tablespace), 段(segment), 区间(extent), 数据块(data block) 组成.

表空间(tablespace)

数据库中的基本逻辑结构,一系列数据文件的集合,一个数据库可以有多个表空间,一个表空间可以有多个数据文件,一个数据文件只能从属于一个表空间

  • 系统用户表空间数据字典:dba_tablespaces

  • 普通用户表空间数据字典:user_tablespaces

  • Oracle 中的6个默认表空间:


    image
    image

  • 重看 tablespace 的总空间, 剩余空间, 使用率

select df.tablespace_name "表空间名",totalspace "总空间M",freespace "剩余空间M",round((1-freespace/totalspace)*100,2) "使用率%"
from
(select tablespace_name,round(sum(bytes)/1024/1024) totalspace from dba_data_files group by tablespace_name) df,
(select tablespace_name,round(sum(bytes)/1024/1024) freespace from dba_free_space group by tablespace_name) fs
where df.tablespace_name=fs.tablespace_name order by df.tablespace_name ;
image.png
  • 查看一个表空间的 extent, block 的数量
-- file_id 为数据文件号, block_id, 区起始数据块号
select tablespace_name,file_id,extent_id,block_id,blocks 
from dba_extents 
where tablespace_name='LEO1' order by extent_id;
  • 创建表空间
create tablespace <tablespace_name> 
datafile <filename1> [, <filename2, ...]
size <filesize> --指定数据文件大小
[autoextend [on|off]] next <filesize> -- 指定数据文件扩展方式:自动/非自动扩展
[maxsize <maxsize>|unlimited] -- 指定数据文件为自动扩展方式时的最大值或无限制
[permanent | temperary] --指定表空间的类型为永久/临时
扩大数据库的3种方式
  • 在表空间增加数据文件:
-- 为 system 表空间增加一个 30M 数据文件 
alter tablespace system 
add datafile 'test01.dbf'
size 30M;
  • 增加数据文件的大小:
    alter database datafile 'filename.dbf' autoextend on next 20m maxsize 1000M
  • 增加表空间:
    create tablespace users datafile 'filename.dbf'
  • 删除表空间
    drop tablespace tpname including contents and datafiles
  • 设置读写状态
    alter tablespace tpname read only
  • 设置离线|在线
    alter tablespace tpname offline|online
  • 删除表空间
-- 可选择同时删除其内容和数据文件
drop tablespace <tablespace_name>
[including contents [and datafile]]

表分区

段(Segment)

由一组连续/不连续的区间组成的逻辑存储单元,代表特定数据类型的数据存储结构

根据段的存储对象类型,分成以下4种:

  • 数据段(data segment)
    • 用于存储表或簇的数据
  • 索引段(index segment)
  • 回滚段(rollback segment)
  • 临时段(temporary segment)

常见情况为表, 索引都是一个段

select segment_name, segment_type
from user_segments;
段空间管理
  • MSSM(手动)
  • ASSM(自动)

区间(extent)

区间是数据库存储空间中分配的一个逻辑单位,由一组连续的数据块组成,区间存储于段中,是磁盘空间分配的最小单位

  • 区间的分配
  • 区间的大小及数量

数据块(data block)

  • 数据块是 Oracle 管理数据库存储空间的最小数据储存单位,一个数据块对应一定数量的数据库空间,标准数据块大小由 DB_BLOCK_SIZE 指定.
  • 数据块既是逻辑单位,也是物理单位

相互关系:

  • 数据库由若干个表空间组成
  • 表空间由一个或多个数据文件组成
  • 表空间存放段
  • 段由区间组成
  • 区间由数据块组成
  • 块是数据库中最小的分配单元也是数据库使用的最小 I/O 单元

操作系统的块(每个簇字节数):

image.png
image.png

3.内存结构

分为 SGA, PGA

  • SGA(System Global Area):一组由 Oracle 分配的共享的内存结构,包括:数据库缓冲存储区, 共享池,重做日志缓冲区,Java 存储区, 大型存储区
  • 查询 sga 各组件大小
select * from v$sga;
-- 或者
show parameter sga;
  • PGA(Program Global Area): 一块包含一个服务进程的数据和控制信息的内存区域,用于处理 SQL 语句和容纳会话信息
  • 查询数据库实例参数:
    show parameter instance name
  • 查询数据库名字:
    show parameter db_name
    select name from v$database
  • 显示 SGA 参数大小及当前实际大小
    show sga
    select name, current_size from v$buffer_pool

二.用户管理

登录

sqlplus /nolog
conn u_name/u_password@domain [as sysdba|sysoper|normal]

系统用户

  • sys ：sys 必须以系统管理员或系统操作员身份登陆:conn sys/password as sysdba
  • system : conn system/password
  • sysman

普通用户

  • scott：Oracle 数据库创始人之一名字，默认被锁，默认密码：tiger
    解锁命令：alter user username sccount unlock
    修改用户密码(记得原密码): password
    修改用户密码(忘记原密码): alter user u_name identified by new_password;

查看用户

  • show user 查看当前登陆用户
  • desc dba_users 查看用户数据字典
  • select username from dba_users

创建用户

创建用户:

create user <usr_name> identified by <usr_password>
default tablespace <tb_name>
quota unlimited on <tb_name>
  • docker 下的 oracle 可能没有 scott ,可以通过脚本创建一个
  • vim /u01/app/oracle/product/11.2.0/xe/rdbms/admin/scott.sql
Rem Copyright (c) 1990 by Oracle Corporation
Rem NAME
REM    UTLSAMPL.SQL
Rem  FUNCTION
Rem  NOTES
Rem  MODIFIED
Rem gdudey   06/28/95 -  Modified for desktop seed database
Rem glumpkin   10/21/92 -  Renamed from SQLBLD.SQL
Rem blinden   07/27/92 -  Added primary and foreign keys to EMP and DEPT
Rem rlim   04/29/91 -      change char to varchar2
Rem mmoore   04/08/91 -      use unlimited tablespace priv
Rem pritto   04/04/91 -      change SYSDATE to 13-JUL-87
Rem   Mendels 12/07/90 - bug 30123;add to_date calls so language independent
Rem
rem
rem $Header: utlsampl.sql 7020100.1 94/09/23 22:14:24 cli Generic<base> $ sqlbld.sql
rem
SET TERMOUT OFF
SET ECHO OFF
rem CONGDON    Invoked in RDBMS at build time. 29-DEC-1988
rem OATES:     Created: 16-Feb-83
GRANT CONNECT,RESOURCE,UNLIMITED TABLESPACE TO SCOTT IDENTIFIED BY TIGER;
ALTER USER SCOTT DEFAULT TABLESPACE USERS;
ALTER USER SCOTT TEMPORARY TABLESPACE TEMP;
CONNECT SCOTT/TIGER
DROP TABLE DEPT;
CREATE TABLE DEPT
       (DEPTNO NUMBER(2) CONSTRAINT PK_DEPT PRIMARY KEY,
DNAME VARCHAR2(14) ,
LOC VARCHAR2(13) ) ;
DROP TABLE EMP;
CREATE TABLE EMP
       (EMPNO NUMBER(4) CONSTRAINT PK_EMP PRIMARY KEY,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7,2),
COMM NUMBER(7,2),
DEPTNO NUMBER(2) CONSTRAINT FK_DEPTNO REFERENCES DEPT);
INSERT INTO DEPT VALUES
(10,'ACCOUNTING','NEW YORK');
INSERT INTO DEPT VALUES (20,'RESEARCH','DALLAS');
INSERT INTO DEPT VALUES
(30,'SALES','CHICAGO');
INSERT INTO DEPT VALUES
(40,'OPERATIONS','BOSTON');
INSERT INTO EMP VALUES
(7369,'SMITH','CLERK',7902,to_date('17-12-1980','dd-mm-yyyy'),800,NULL,20);
INSERT INTO EMP VALUES
(7499,'ALLEN','SALESMAN',7698,to_date('20-2-1981','dd-mm-yyyy'),1600,300,30);
INSERT INTO EMP VALUES
(7521,'WARD','SALESMAN',7698,to_date('22-2-1981','dd-mm-yyyy'),1250,500,30);
INSERT INTO EMP VALUES
(7566,'JONES','MANAGER',7839,to_date('2-4-1981','dd-mm-yyyy'),2975,NULL,20);
INSERT INTO EMP VALUES
(7654,'MARTIN','SALESMAN',7698,to_date('28-9-1981','dd-mm-yyyy'),1250,1400,30);
INSERT INTO EMP VALUES
(7698,'BLAKE','MANAGER',7839,to_date('1-5-1981','dd-mm-yyyy'),2850,NULL,30);
INSERT INTO EMP VALUES
(7782,'CLARK','MANAGER',7839,to_date('9-6-1981','dd-mm-yyyy'),2450,NULL,10);
INSERT INTO EMP VALUES
(7788,'SCOTT','ANALYST',7566,to_date('13-JUL-87')-85,3000,NULL,20);
INSERT INTO EMP VALUES
(7839,'KING','PRESIDENT',NULL,to_date('17-11-1981','dd-mm-yyyy'),5000,NULL,10);
INSERT INTO EMP VALUES
(7844,'TURNER','SALESMAN',7698,to_date('8-9-1981','dd-mm-yyyy'),1500,0,30);
INSERT INTO EMP VALUES
(7876,'ADAMS','CLERK',7788,to_date('13-JUL-87')-51,1100,NULL,20);
INSERT INTO EMP VALUES
(7900,'JAMES','CLERK',7698,to_date('3-12-1981','dd-mm-yyyy'),950,NULL,30);
INSERT INTO EMP VALUES
(7902,'FORD','ANALYST',7566,to_date('3-12-1981','dd-mm-yyyy'),3000,NULL,20);
INSERT INTO EMP VALUES
(7934,'MILLER','CLERK',7782,to_date('23-1-1982','dd-mm-yyyy'),1300,NULL,10);
DROP TABLE BONUS;
CREATE TABLE BONUS
(
ENAME VARCHAR2(10) ,
JOB VARCHAR2(9)  ,
SAL NUMBER,
COMM NUMBER
) ;
DROP TABLE SALGRADE;
CREATE TABLE SALGRADE
      ( GRADE NUMBER,
LOSAL NUMBER,
HISAL NUMBER );
INSERT INTO SALGRADE VALUES (1,700,1200);
INSERT INTO SALGRADE VALUES (2,1201,1400);
INSERT INTO SALGRADE VALUES (3,1401,2000);
INSERT INTO SALGRADE VALUES (4,2001,3000);
INSERT INTO SALGRADE VALUES (5,3001,9999);
COMMIT;
SET TERMOUT ON
SET ECHO ON

三.权限管理

系统权限

grant privilege [,privilege...] to uname

角色分配

数据库预定义角色：

  • connect：登录执行基本函数
  • resource：建立用户自己的数据对象
  • dba：所有系统权限
    grant connect,resource to <uname>;

回收权限

四.Table:表管理

  • dba_tables:数据库中所有的表
  • user_tables:当前登录用户拥有的表对象
  • all_tables:当前登录用户可以访问的表对象
-- 删除表数据：delete & truncate
delete table tname;  --可撤销删除数据，可带条件，但效率低，空间不会及时释放
trancate table tname; --清除所有数据，保留表结构，效率高，释放空间，不可撤销
drop table tname; -- 删除表结构， 不可撤销

数据类型

数据类型 最大长度 说明
char(size) 2000字节 固定长度,默认1字节
varchar2(size) 4000字节 可变长字符串
number(precision, scale) 2000 包含小数位的数值类型

视图

视图是一个由 select 语句定义内容的作为查看数据便捷方式的虚拟表.

  • 创建一个视图:
create [or replace] [force | noforce] -- force 不考虑基表是否存在
view v_name
as
<select statement>
[with check option | -- 使用视图进行插入或修改操作是必须满足 select 语句的查询条件,默认不检查
with read only]  --视图只能用于查询数据,默认可修改

序列

Oracle 没有 MySQL 的自增, 但可以用可自定义更强大的序列替代实现.

-- 创建序列, 
create sequence seq_empid
start with 100
increment by 2;

select seq_empid.nextval from dual;

六.完整性约束

  • 主键约束: primary key
  • 非空约束: not null
  • 唯一性约束: unique
  • 检查约束: check
  • 外键约束: foreign key
    ** 示例 **
create table t_orders(
oid char(8)  primary key,--主键
uiid char(6) references t_user(uiid),--外键--列级约束
uiid1 char(6) constraint fk_uiid2 references t_user(uiid),--有约束名的外键
uiid2 char(6) ,
constraint fk_uiid3 foreign key(uiid2)  references t_user(uiid),--表级有约束名的外键
customer varchar2(20) not null,--只能做列级约束
odate date default sysdate,--默认值
dddd char(8) unique,--唯一，不能重复
onum number(2) check(onum>0)--自定义约束
);
最后编辑于 ：
©著作权归作者所有,转载或内容合作请联系作者
  • 人面猴
    序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
    沈念sama阅读 203,324评论 5赞 476
  • 死咒
    序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
    沈念sama阅读 85,303评论 2赞 381
  • 救了他两次的神仙让他今天三更去死
    文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
    开封第一讲书人阅读 150,192评论 0赞 337
  • 道士缉凶录：失踪的卖姜人
    文/不坏的土叔 我叫张陵，是天一观的道长。 经常有香客问我，道长，这世上最难降的妖魔是什么？ 我笑而不...
    开封第一讲书人阅读 54,555评论 1赞 273
  • 港岛之恋（遗憾婚礼）
    正文 为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
    茶点故事阅读 63,569评论 5赞 365
  • 恶毒庶女顶嫁案：这布局不是一般人想出来的
    文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...
    开封第一讲书人阅读 48,566评论 1赞 281
  • 城市分裂传说
    那天，我揣着相机与录音，去河边找鬼。 笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。 我是一名探鬼主播，决...
    沈念sama阅读 37,927评论 3赞 395
  • 双鸳鸯连环套：你想象不到人心有多黑
    文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
    开封第一讲书人阅读 36,583评论 0赞 257
  • 万荣杀人案实录
    序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
    沈念sama阅读 40,827评论 1赞 297
  • 护林员之死
    正文 独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 35,590评论 2赞 320
  • 白月光启示录
    正文 我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 37,669评论 1赞 329
  • 活死人
    序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
    沈念sama阅读 33,365评论 4赞 318
  • 日本核电站爆炸内幕
    正文 年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
    茶点故事阅读 38,941评论 3赞 307
  • 男人毒药：我在死后第九天来索命
    文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 29,928评论 0赞 19
  • 一桩弑父案，背后竟有这般阴谋
    文/苍兰香墨 我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 31,159评论 1赞 259
  • 情欲美人皮
    我被黑心中介骗来泰国打工， 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。 一个月前我还...
    沈念sama阅读 42,880评论 2赞 349
  • 代替公主和亲
    正文 我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
    茶点故事阅读 42,399评论 2赞 342

推荐阅读更多精彩内容