Oracle 数据库系统

• 使用版本：Oracle 11gR2
• 安装:https://oracle-base.com/articles/11g/articles-11g#db11g_release_2_installations
• 卸载:https://oracle-base.com/articles/misc/manual-oracle-uninstall
• 启动:net start orcalserviceorcl

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

一.体系结构

D:\app\oradata\orcl.png

数据文件(*.DBF)

• 所在数据字典: V$DATAFILE
• 用于保存数据库的所有数据
• 特征:
  • 一个数据文件仅与一个数据库联系
  • 一个数据文件只能从属于一个表空间
  • 一个表空间可以包含几个数据文件
  • 数据库容量越界时数据文件可以自动扩展

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)--自定义约束
);