一、体系结构
- 体系结构的定义
- 主要组件
  - 数据库
    - 物理结构
      - 主要文件：
      - 其他文件:
    - 逻辑结构
      - 表空间
  - 实例
    - 内存结构
    - 进程结构
      - 后台进程
二、用户管理
三、数据字典
- 定义
- 分类
  - 静态数据字典
  - 动态数据字典
扩展
总结

一、体系结构

体系结构的定义

体系结构是指数据库的组成、工作过程与原理，以及数据在数据库中的组织与管理机制

主要组件

Oracle服务器提供开放、全面和综合的信息管理，它由Oracle数据库和Oracle实例组成
数据库是一个数据的集合，该集合被视为一个逻辑单元
Oracle 实例是后台进程和内存结构的集合

数据库

Oracle 数据库由操作系统文件组成，这些文件为数据库信息提供实际物理存储区
Oracle 数据库包括逻辑结构和物理结构

物理结构

主要文件：

数据文件：
- 包含数据库的用户或应用程序数据，以及元数据和数据字典
重做日志文件：
- 用于进行数据库的实例恢复
- 如果数据库服务器发生崩溃，但未丢失任何数据文件，那么实例便可使用这些文件中的信息恢复数据库
控制文件：
- 包含与数据库本身相关的数据，即物理数据库结构信息
- 这些文件对数据库至关重要。没有这些文件，就无法打开数据文件以访问数据库中的数据
  其他文件:
参数文件：用于定义实例启动时的配置
口令文件：允许sysdba、sysoper 远程连接到实例并执行管理任务
归档日志文件：使用这些文件和数据库备份，可以恢复丢失的数据文件。也就是说，归档日志能够恢复还原的数据文件

逻辑结构
数据库的逻辑组件是从逻辑的角度分析数据库的组成

表空间

是数据库中最大的逻辑单位， SYSTEM 和SYSAUX 表空间是在创建数据库时创建的必须存在的表空间，还会有TEMP，USERS等
每个表空间是由一个或多个数据文件组成的，一个数据文件只能与一个表空间相关联
表空间的大小等于构成该表空间的所有数据文件大小之和
创建表空间的语法是：
```
create tablespace 表空间名字
datafile '文件路径和文件名' size 整数 [K|M]
[autoextend [off|on]];  -- 是否自动增长（默认不自动增长），off不增长，on增长
```
示例：
create tablespace abc datafile 'd:\abc.dbf' size 100m;

如文件路径只写文件名，数据文件位于（oracle安装地址） C:\app\Administrator\product\11.2.0\dbhome_1\database

实例

内存结构

系统全局区（ SGA ）

数据库信息存储于系统全局区，由多个数据库进程共享

程序全局区（PGA）

包含单个服务器进程所需的数据和控制信息
PGA是在用户进程连接到数据库并创建一个会话时自动分配的，保存每个与Oracle 数据库连接的用户进程所需的信息。当一个用户会话结束，PGA释放

用户全局区（UGA）
用户全局区（UGA）为用户进程存储会话状态
作为SGA或者PGA的一部分。具体位置取决于如何连接Oracle：
- 如果通过一个共享服务器连接，UGA包含在SAG中
- 如果通过一个专有服务器连接，UGA就包含在专有服务器的PGA中
  进程结构
  
  后台进程
  
  PMON：
  进程监控进程(process moniter)
  作用：用户连接中断后执行资源清理工作
  SMON：
  系统管理进程(system moniter)
  作用：
  实例启动时执行实例恢复
  整理数据文件的自由空间
  释放不再使用的临时段
  DBWR：
  数据写入进程(database write)
  作用：
  管理数据缓冲区
  把数据缓冲区的内容写入到文件
  LGWR：
  日志写入进程(log write)
  作用：
  负责将日志缓冲区的日志数据写入日志文件组
  CKPT：
  检查点进程(check point)
  作用：是一种确保缓冲区中修改过的数据块都被写入数据库文件的机制。
  二、用户管理
  默认用户
  
  创建用户
  语法
```
create user 用户名
identified by 密码
[default  tablespace 默认表空间名]
[temporary  tablespace 临时表空间名]
[password expire ]       --第一次登录需要修改密码
[account lock|unlock]    --unlock 启用用户,lock 禁用账户
```
  示例
```
create user martin              --用户名为martin
identified by martinpwd         --密码为martinpwd
default tablespace tbs_work     --默认表空间为tbs_work
temporary tablespace temp       --临时表空间为temp
password expire                 --第一次登录需要修改密码
account unlock;                 --启用用户
```
  权限管理create user 用户名
权限指的是执行特定命令或访问数据库对象的权利
权限有两种类型，系统权限和对象权限
- 系统权限允许用户执行某些数据库操作，如创建表就是一个系统权限
- 对象权限允许用户对数据库对象（如表、视图、序列等）执行特定操作
角色是一组相关权限的组合，可以将权限授予角色，再把角色授予用户，以简化权限管理

connect 可以连接数据库
resource:可以建表，存储过程，触发器等，改表，删表，增删改查数据，不能建立视图和用户
dba:管理员，有大多数系统权限

授权

grant 权限或者角色 [on 用户名.表名] to 用户
示例

-- 授予connet角色给martin
grant connect to martin; 
-- 同时授予connet，resource这2个角色给martin
grant connect,resource to martin; 
-- 允许用户martin插入、删除、更新和查询scott.emp表中的记录，也有删表等权限
grant all on scott.emp to martin;   
-- 允许用户martin插入、删除、更新和查询scott.emp表中的记录
grant select,insert,update,delete on scott.emp to martin;

撤销权限

revoke 权限或者角色 from 用户;
示例

-- 撤销connet，resource这2个角色
revoke connect,resource from martin; 
-- 撤销martin查询 scott.emp表的记录的权限
revoke select from scott.emp to martin;

三、数据字典

定义

数据字典是Oracle存储关键信息的表和视图的集合
数据字典是数据库的说明，包含数据库中所有对象的名称和属性

分类

数据字典表
- 是不可以直接被访问

数据字典视图

静态数据字典（静态性能视图），前缀是user dba all_，名是复数形式

动态数据字典（动态性能视图），前缀是v$，名是单数形式

静态数据字典

SQL> select table_name, tablespace_name from user_tables; -- 查看用户的表
SQL> select index_name from user_indexes; -- 查看用户的索引
SQL> select view_name from user_views; -- 查看用户的视图
SQL> select object_name from user_objects; -- 查看用户的对象
SQL> select * from user_users; -- 查看用户
SQL> select owner, object_name,object_type from all_objects; -- 查看所有对象

动态数据字典

会不断的进行更新，可以反映出当前实例和数据库的运行状况

DBA可以使用动态视图来监视和维护数据库

查看日志组状态信息：select * from v$log;
查看日志文件信息：select * from v$logfile;
查看控制文件的名称： select * from v$controlfile;
查看实例信息：select * from v$instance;
查看数据库信息：select * from v$database;

根据对象名称查询对象类型 select object_type from user_objects where object_name=’名字’

扩展

表空间管理

1）创建语法

create tablespace 表空间名字   -- 创建表空间
datafile ‘文件路径’ size 整数 [k|m]   -- 文件大小
[autoextend [off|on]];  -- 是否自动增长（默认不自动增长）

例子：
create tablespace abc datafile 'd:\abc.dbf' size 100m;

2）修改表空间的大小

alter database datafile 'dbf文件路径' resize 80m;

3）向表空间添加数据文件

alter tablespace 表空间名 
add datafile 'dbF文件路径' size 20m autoextend on;

4）改变表空间的读写状态

alter tablespace 表空间名 read write; --可读写
alter tablespace 表空间名 read only;  --只读

5）查看oracle下面所有的表空间

select * from Dba_Tablespaces

6）查看指定的表空间是否为自动扩展

select file_name,tablespace_name,autoextensible,increment_by 
from dba_data_files where tablespace_name = '表空间名';

7）如果不是自动扩展想修改为自动扩展的话需要操作

SQL> alter database datafile '/u01/app/oracle/oradata/XXX/XXXX01.dbf' autoextend on;
或者
SQL> alter database datafile '/u01/app/oracle/oradata/XXX/XXXX01.dbf' autoextend on next xxx maxsize xxxx;

8）关闭自动扩展

SQL> alter database datafile '/u01/app/oracle/oradata/XXX/XXXX01.dbf' autoextend off;

9）删除表空间（同时删除表空间的内容和数据文件）

drop tablespace tablespace_name including contents and datafiles;

用户管理

1）创建用户语法：

create user 用户名
identified by 密码
[default  tablespace 默认表空间名]
[temporary  tablespace 临时表空间名]
[password expire ]       --第一次登录需要修改密码
[account lock|unlock]    --锁定或者启用

示例：

create user martin              --用户名为martin
identified by martinpwd         --密码为martinpwd
default tablespace tbs_work     --默认表空间为tbs_work
temporary tablespace temp       --临时表空间为temp
password expire                 --第一次登录需要修改密码
account unlock;                 --启用用户

2）删除用户

drop user xx cascade -- 如果用户含有对象，必须写cascade

3）解除锁定

alter user 用户名 account unlock;

4）修改口令

alter user 用户名 identified by 密码;

5）查看SCOTT用户是否锁定，值为open是解锁状态，否则锁定状态

select account_status from dba_users where username='SCOTT';

locked是锁定的，expired 是过期的解锁： alter user 用户名 account unlock; 修改过期：update user$ set astatus = 0 where name = ‘用户名’; commit;

6）其他应用

1、通常我们遇到oracle用户密码输入多次错误，那么改用户就会变锁定，那么解决方法很简单，如下：
使用plsql工具sys用户登录（亦可dos命令输入：sqlplus / as sysdba ）解锁
解除锁定：alter user 用户名 account unlock;
2、那么遇到这种用户会被锁的情况，我们可以设置用户密码无限次尝试登录
用户不会被锁定：alter profile default limit failed_login_attempts unlimited;
3、另外，做项目运维的小伙伴经常遇到数据库用户密码过期的情况，要定期去修改密码很麻烦，那么我们可以设置用户
密码不过期：alter profile default limit password_life_time unlimited;
4、密码找回：
打开cmd窗口，输入 sqlplus / as sysdba ，
输入 alter user 用户名 account unlock;
alter user 用户名 identified by 新密码 ; — 修改密码

权限和数据字典

1.查询用户拥有的系统权限

SQL> select grantee,privilege from dba_sys_privs where grantee = 'scott';
（dba权限）注意用户名大写

2.查询用户拥有的对象权限

SQL> select grantee,table_name,privilege from dba_tab_privs where grantee = 'scott';
（dba权限）

3.查询用户拥有的角色

SQL> select grantee,granted_role from dba_role_privs where grantee ='scott';
（dba权限）

4.查看用户和默认表空间的关系

select username,default_tablespace from dba_users;
（dba权限）

5.Oracle查询用户表

select * from user_tables;    --查看当前用户能访问的表     
select * from dba_tables;     --管理员可以看到的数据库中所有的表（dba权限）    
select * from user_all_tables;--与select * from user_tables;有类似之处不过显示的键值对不一样    
select * from all_tables;     --显示用户有权限看到的所有的表，包括系统表

6.Oracle查询用户视图

select * from user_views;

7.查询所有函数和储存过程：

select * from user_source;

8.查询所有用户：

select * from all_users;  --查看能管理的所有用户    
select * from dba_users;  --查看数据库里面所有用户（dba权限）    
select * from user_users; --查看当前用户信息

9.查看当前用户连接：

select * from v$Session;
（dba权限）

10.查看用户角色

select * from user_role_privs;

11.查看当前用户权限：

select * from session_privs;

12.查看所有用户所拥有的角色

select * from dba_role_privs;
（dba权限）

13.查看所有角色

select * from dba_roles;
（dba权限）

14.查看某个角色，例如DBA角色有哪些权限

select * from role_sys_privs where role='DBA';
select * from role_sys_privs where role='RESOURCE';

15.查看数据库名

select name from v$database;
（dba权限）

16.查看所有表空间使用情况

select a.file_id "FileNo",    
a.tablespace_name "Tablespace_name",    
a.bytes "Bytes",    
a.bytes - sum(nvl(b.bytes, 0)) "Used",    
sum(nvl(b.bytes, 0)) "Free",    
sum(nvl(b.bytes, 0)) / a.bytes * 100 "%free"    
 from dba_data_files a, dba_free_space b     
 where a.file_id = b.file_id(+)    
 group by a.tablespace_name, a.file_id, a.bytes    
 order by a.tablespace_name;（dba权限）

常用数据类型

1、字符类型

• CHAR：一个定长字符串，当位数不足自动用空格填充来达到其最大长度。如非NULL的CHAR(12)总是包含12字节信息。CHAR字段最多可以存储2,000字节的信息。
• VARCHAR2：目前这也是VARCHAR 的同义词。这是一个变长字符串，与CHAR 类型不同，它不会用空格填充至最大长度。VARCHAR2(12)可能包含0～
12字节的信息。VARCHAR2最多可以存储4,000 字节的信息。
CHAR和VARCHAR2的比较
CHAR(4) “A“ 实际在数据库中存储为”A “
“ABCDE”超长报错
VARCHAR2(4) “A” 存储的还是“A”
“ABCDE”超长报错
汉字：每个汉字占多少字节，要看具体的编码方式，如UTF-8（1-3字节）、GB2312（2字节）、GBK（2字节）、GB18030（1、2、4字节）

2、数字类型

• NUMBER：该数据类型能存储精度最多达38位的数字。每个数存储在一个变长字段中，其长度在0～22字节之间。Oracle的NUMBER类型精度很高，远远高于许多编程语言中常规的FLOAT和DOUBLE类型。
NUMBER( p,s ) p表示精度（总长度） s表示小数位置且四舍五入
NUMBER(10,3) 10是总长度，3是小数位数的长度
123.456
123.4567 ：将存储为123.457
12345679.899 ：精度超长了，10是总长度，3是小数位，整数位为10-3=7位NUMBER(10)==NUMBER(10,0) java.lang.Integer
NUMBER(19)==NUMBER(19,0) java.lang.Long

3、日期类型

• DATE：一个7字节的定宽日期/时间数据类型。其中总包含7个属性，包括：世纪、世纪中哪一年、月份、月中的哪一天、小时、分钟和秒。
• TIMESTAMP：一个7 字节或12.字节的定宽日期/时间数据类型。它与DATE 数据类型不同，因为TIMESTAMP 可以包含小数秒（fractional second）；带小数秒的TIMESTAMP 在小数点右边最多可以保留9位。

4、二进制及大文本数据

• BLOB： (binary large object)在Oracle9i及以前的版本中，这种数据类型允许存储最多4GB的数据，在Oracle 10g及以后的版本中允许存储最多（4GB）×（数据库块大小）字节的数据。BLOB包含不需要进行字符集转换的“二进制“数据，如果要存储电子表格、字处理文档、图像文件等就很适合采用
• CLOB：（Character Large Object）在Oracle9i及以前的版本中，这种数据类型允许存储最多4GB的数据，在Oracle 10g及以后的版本中允许存储最多（4GB）×（数据库块大小）字节的数据。CLOB包含要进行字符集转换的信息。这种数据类型很适合存储纯文本信息。

表的管理

建表

create table 表名(
列名 数据类型 各种约束,
   ....
);

--建表
create table class(              -- 班级
classid number(2) primary key,   -- 班级编号
classname varchar(10) not null   -- 班级名称  
);
create table student(            -- 学生
studentid number(2) primary key, --学号，主键
name varchar(10) not null,       --姓名，非空
sex char(2) check(sex='男' or sex='女'),  --性别，检查约束
age number(2) not null,          --年龄
tel number(11) unique,           --电话，唯一键
address varchar(50) default '上海', --地址，默认值
classid number(2) references classinfo(classid) --班级编号，外键
);

查看表的结构

describe 表名;

改表

1.修改表的字段：

修改一个列的数据类型(一般限于修改长度，修改为一个不同类型时有诸多限制):
语法: alter table 表名 modify(列名数据类型);
eg1: alter table skate_test modify (author number(10,0) );
在修改列的长度时,只能改为比现有字段实际存的长度还要大,否则提示下面错误:
ORA-01441: 无法减小列长度, 因为一些值过大
eg2: alter table skate_test modify (author varchar2(10) )
在修改列的数据类型的时候,所修改的列必须为空,否则提示下面的错误:
ORA-01439: 要更改数据类型, 则要修改的列必须为空

2.增加一个列：

语法：alter table 表名 add(列名数据类型);
eg1： alter table skate_test add(author number(38,0) not null);

3.更改列名：

语法：alter table 表名 rename column 当前列名 to 新列名;
eg1：alter table skate_test rename column author to authorer_new;

4.删除一个列：

语法：alter table 表名 drop column 列名;
eg1： alter table skate_test drop column author;

5.修改表名：

语法：alter table 当前表名 rename to 新表名;
eg1： alter table skate_test rename to test_sakte

6.给表字段加注释：

语法：comment on column 表名.列名 is '注释内容'; --修改表的列的注释
eg1：comment on column movo_new.test_sakte is '注释内容';

7.给表加注释：

语法：comment on table 表名 is '注释内容'; --修改表的注释
eg1：comment on table movo_new is '注释内容';

8.添加约束

//建测试表  
create table dept(                      --部门表
deptno number(3) primary key,  
dname varchar2(10),  
loc varchar2(13)   
);  
create table employee_info(              --emplpoyee表
empno number(3),  
deptno number(3),  
ename varchar2(10),  
sex char(1),  
phone number(11),  
address varchar2(50),  
introduce varchar2(100)  
);  
--   
//0.重命名  
  //0.1 表:rename dept to dt;            --将dept重命名为dt；
           rename dt to dept;            --将dt改回dept表
  //0.2 列:alter table dept rename column loc to location;  
           alter table dept rename column location to loc;  
//1.添加约束 
  //1.1 primary key  
      alter table employee_info add constraint pk_emp_info primary key(empno);  
  //1.2 foreign key  
      alter table employee_info add constraint fk_emp_info foreign key(deptno)  
      references dept(deptno);  
  //1.3 check  
      alter table employee_info add constraint ck_emp_info check  
      (sex in ('F','M'));  
  //1.4 not null  
      alter table employee_info modify phone constraint not_null_emp_info not null;  
  //1.5 unique  
      alter table employee_info add constraint uq_emp_info unique(phone);  
  //1.6 default  
      alter table employee_info modify sex char(2) default 'M';  
//2.添加列  
   alter table employee_info add id varchar2(18);  
   alter table employee_info add hiredate date default sysdate not null;  
//3.删除列  
   alter table employee_info drop column introduce;  
//3.修改列  
  //3.1 修改列的长度  
      alter table dept modify loc varchar2(50);  
  //3.2 修改列的精度  
      alter table employee_info modify empno number(2);  
  //3.3 修改列的数据类型  
      alter table employee_info modify sex char(2);  
  //3.4 修改默认值  
      alter table employee_info modify hiredate default sysdate+1;  
//4.禁用约束  
  alter table employee_info disable constraint uq_emp_info;  
//5.启用约束  
  alter table employee_info enable constraint uq_emp_info;  
//6.延迟约束  
  alter table employee_info drop constraint fk_emp_info;  
  alter table employee_info add constraint fk_emp_info foreign key(deptno)  
        references dept(deptno)  
  deferrable initially deferred;  
//7.向表中添加注释  
  comment on table employee_info is 'information of employees';  
//8.向列添加注释  
  comment on column employee_info.ename is 'the name of employees';  
  comment on column dept.dname is 'the name of department';  
//9.清除表中所有数据  
  truncate table employee_info;  
//10.删除表  
  drop table employee_info;

删表

drop table 表名;

注意： oracle的增删改查基本和mysql一样，但是多行插入Oracle只有insert-select语句，没有union和多个()

序列

Oracle没有自增列，但是可以通过序列来完成自动增长的功能。

创建序列

create sequence 序列名
[start with 开始的数]
[increment by 增长的数]
[minvalue 最小值|nominvalue]
[maxvalue 最大值|nomaxvalue]
[cycle|nocycle]  --循环|不循环
[cache 缓存的数|nocache]
[order|noorder]  --排序|不排序

修改的例子（不能改初始值）

alter sequence master_seq
increment by 5
maxvalue 100000
nocycle
nocache;

删除序列

drop sequence 序列

使用序列

insert into 表名[(列名,...)] values(序列名.nextval,...)

查询序列的当前值

select 序列名.currval from dual;

注意：必须在他之前使用这个序列插入数据

函数

字符串函数：

字符串位置从1开始数
INITCAP (字符串)：首字母大写
LOWER (字符串)：转换为小写
UPPER (字符串)：转换为大写
LTRIM (字符串[, 子串])：左裁剪
RTRIM (字符串[, 子串])：右裁剪
TRANSLATE (字符串, 要翻译的字符, 翻译后的字符)：按字符翻译
replace(原始字符串，不想要的字符串，新字符串);替换指定字符串
instr(原始字符串，子字符串[，查找位置]);查找第一次出现的子串位置，找不到返回0.
substr(原始字符串，位置，长度);截取字符串
concat(字符串1，字符串2);连接两个字符串
||：表示连接的意思，可以用来连接两个字符串
+：表示求和的意思

数学函数：

abs(数)：绝对值
ceil(100.2)：向上取整，结果：101
floor(100.9)：向下取整结果：100
round(100.256，2):四舍五入到小数点后的2位，结果：100.26
trunc(100.256,2);截断到小数点的2位，结果：100.25

日期函数：

sysdate:系统时间
extract(YEAR | MONTH | DAY | HOUR | MINUTE | SECOND from 时间):提取日期的一部分MONTHS_BETWEEN（日期1，日期2）：日期1减去日期2得到的月份数
ADD_MONTHS（日期，数）：在指定日期上添加数月，得到新日期
NEXT_DAY（日期）：返回指定日期后的星期对应的新日期
LAST_DAY（日期）：返回指定日期所在的月的最后一天
ROUND（日期，‘日期的一部分，例如year’）:按指定格式对日期进行四舍五入
TRUNC（日期，‘日期的一部分，例如year’）:按指定格式对日期进行截断

注意：两个日期相减可以得到相差的天数

日期转换

1.默认日期格式：日-月-年，例子1-2月-16
2.to_char(日期，‘日期格式’);把一种类型转成字符
例子：to_char(时间列名，’yyyy-mm-dd’);
to_char(时间列名，’yyyy”年”mm”月”dd”日”‘)
3.to_date(‘字符串’，‘日期格式’);把字符串转换成日期类型
表示日期的字符：
yyyy:四位年，mm:01-12月，dd:1-31日，hh:1-12,hh24:0-23,mi:0-59分钟，ss:0-59秒

其他函数：

to_char(数，‘格式’)转换成字符串类型
TO_NUMBER（字符串）转换成数
nvl(列名，值);如果列的值是null的，显示值，否则显示列的原值

例子：select p.name ,nvl(p.name,’无名’) names from pet p

nvl2(列名，值1，值2）；如果列的值是null的，显示值2，否则显示值1

例子：select p.name ,nvl2(p.name,’有名’,’无名’) names from pet p

decode(列名，值1，值11，值2，值22，值33)：
如果列的值是值1，显示值11；
如果列的值是值2，显示值22；
否则，显示值33；

例子：select t.status,decode(t.status,’1’,’正常’,’2’,’有点不正常’,’非常不正常’) from PET t

总结

1.oracle监听和web登录的默认端口号分别是多少

监听：1521，登录：1158

2.关于Oracle用户的说明
SYS是oracle内置的超级用户，登录时必须以sysdba或sysoper角色登录系统
SYSTEM是oracle中默认的数据库管理员，拥有DBA角色权限，以normal身份登录
scott是测试用户，以normal身份登录
新建的用户，没有授予权限，不能登录oracle
删除用户时，当用户拥有模式对象则无法删除用户

3.Oracle常用的角色有哪些？
connect:连接
resource:资源，建表等
dba:数据库管理员，建用户等
4.创建用户的语法
create user 用户名
identified by 密码
[default tablespace 默认表空间名]
[temporary tablespace 临时表空间名]
[password expire ] —第一次登录改密码
[account lock|unlock] —账户锁定|解锁
5.Oracle授权的语法
grant 权限或者角色 [on 用户名2.表名] to 用户名;

Oracle体系结构和用户管理