首页 > 代码库 > oracle06

oracle06

2024-10-13 01:55:02 215人阅读

1. Oracle的体系结构 - 了解

1.1. Oracle数据库和Oracle实例

Oracle 服务器软件部分由两大部分组成, Oracle 数据库和 Oracle 实例。

两者的解释如下：

l Oracle 数据库(物理概念): 位于硬盘上实际存放数据的文件和相应的程序文件, 这些文件组织在一起, 成为一个逻辑整体, 即为 Oracle 数据库. 因此在 Oracle 看来, “数据库” 是指硬盘上文件的逻辑集合, 必须要与内存里实例合作, 才能对外提供数据管理服务。

l Oracle 实例（逻辑概念）: 位于物理内存里的数据结构. 它由一个共享的内存池和多个后台进程所组成, 共享的内存池可以被所有进程访问. 用户如果要存取数据库(也就是硬盘上的文件) 里的数据, 必须通过实例才能实现, 不能直接读取硬盘上的文件。实例的唯一标识也称之为SID（OSID）。

一个实例只能对应一个数据库，一个数据库可以有多个实例（RAC集群），但大多数情况下, 一个数据库上只有一个实例对其进行操作。我们就是通过连接到实例来操作数据库的。

技术分享

1.2. Oracle常见的存储文件

常见的存储文件主要为三类：

l 数据文件。存储数据用的。例：表

技术分享

l 控制文件。记录数据文件存放的位置。例：数据库名称、数据文件名称及位置。

技术分享

l 日志文件。记录数据信息变化的。例：因故障问题造成一些数据没有及时写入到数据文件，可以使用日志文件恢复

(Oracle日志回滚:如果你的数据被delete掉并且提交了,数据还是可以恢复的,可以通过日志来恢复的)。

技术分享

2. 表空间(Tablespace)的管理

2.1. 表空间的概念

ORACLE是属于文件存储。ORACLE中的数据是存放在一个个数据文件中，数据文件存放在磁盘中。

技术分享

如果说数据文件是物理概念，那么表空间就是逻辑概念，Oracle通过表空间来对数据文件中的数据进行CRUD。

表空间是一种逻辑结构，是Oracle最大的逻辑单元，可以理解为：所有的数据都存储在表空间中。

技术分享

表空间的属性特点：

l 一个数据库可以包含多个表空间，一个表空间只能属于一个数据库。

l 一个表空间可以由一个或多个数据文件组成，一个数据文件只能属于一个表空间。

l 表空间可以划分成更细的逻辑存储单元。（了解）

2.2. 数据库的存储结构--了解

官方数据存储结构图：

技术分享

各对象之间的存储对应关系图：

技术分享

l 所有的数据库对象都存储在表空间中，而表空间被数据库服务管理。

l 一个表空间可以对应N个数据文件，表空间是逻辑概念，而数据文件是物理概念。

l 方案（SCHEMA模式）是表、视图、索引等数据库对象的逻辑集合，它通过数据库服务来间接管理这些对象。

l 一个用户（user）创建时会同时创建一个同名的方案（schema），即，你甚至可以认为用户和方案是同一个东西（事实上不是，用户主要是做权限等相关管理的）。当用户登录后，就立刻拥有了该同名方案下所有对象。

l 方案（用户）和表空间没有什么必然关系，一个方案拥有一个默认的表空间，但同时可以使用多个表空间来存储它的对象。一个表空间可以为不同的方案来存储其所属对象

【补充阅读】下面有个很形象的比喻，是从网上摘的，不妨一看：

我们可以把database看做是一个大仓库，仓库分了很多很多的房间，schema就是其中的房间，一个schema代表一个房间，table可以看做是每个schema中的床，table被放入每个房间中，不能放置在房间之外，那岂不是晚上睡觉无家可归了，然后床上可以放置很多物品，就好比table上可以放置很多列和行一样，数据库中存储数据的基本单元是table，显示中每个仓库放置物品的基本单位就是床，user就是每个schema的主人，（所以schema包含的是object，而不是user），user和schema是一一对应的，每个user在没有特别指定下只能使用自己schema的东西，如果一个user想使用其他schema的东西，那就要看那个schema的user有没有给你这个权限了，或者看这个仓库的老大（DBA）有没有给你这个权限了。换句话说，如果你是某个仓库的主人，那么这个仓库的使用权和仓库中的所有东西都是你的，你有完全的操作权，可以扔掉不用东西从每个房间，也可以防止一些有用的东西到某个房间，你还可以给每个user分配具体的权限，也就是他到某一个房间能做些什么，是只能看（read-only),还是可以像主人一样有所有控制权（R/W），这个就要看这个user所对应的角色Role了。

数据操作的过程：

技术分享

【小结】

表空间:属于一种逻辑结构。记录物理文件的逻辑单位。是Oracle最大的逻辑单位.

也就是说,我们所有的数据都存储在表空间中.

2.3. 常见的表空间分类—了解

l （永久）数据表空间 , 主要用来永久存储正式的数据文件。

l 临时数据表空间,主要用来存储临时数据的，比如数据的排序、分组等产生的临时数据，不能存放永久性对象。

l UNDO表空间，保存数据修改前的镜象。

临时表空间和UNDO表空间的异同：（了解）

l 相同之处：两者都不会永久保存数据。

l 不同之处：UNDO表空间用于存放UNDO数据,当执行DML操作时,oracle会将这些操作的旧数据写入到UNDO段,以保证可以回滚和事务隔离读取等，主要用于数据的修改等；而临时表空间主要用来做查询和存放一些缓冲区数据。

2.4. Oracle对表空间的管理方式—了解

字典管理：全库所有的空间分配都放在数据字典中。容易引起字典争用，而导致性能问题。

本地管理：空间分配不放在数据字典，而在每个数据文件头部的第3到第8个块的位图块，来管理空间分配。oracle公司推荐使用本地管理表空间。

2.5. 表空间的创建

注：表空间的创建一般是由DBA来操作完成的，而且需要管理员权限（我们一般用sys）。

三种表空间中，UNDO表空间通常是由Oracle自动化管理的，而另外两种表空间则一般需要手动创建。

【常用参数语法】：

--创建永久数据表空间
CREATE TABLESPACE TABLESPACE_NAME
[DATAFILE DATAFILE1，[DATAFILE 2]…]
[LOGGING | NOLOGGING]
[ONLINE|OFFLINE]
[EXTENT_MANAGEMENT_CLAUSE]

参数说明：
?TABLESPACE_NAME，表空间名称随意，但最好遵循一定的规范，如tbl_itcast18_dat、tbl_itcast18_tmp等。
?DATAFILE，表空间的类型
?DATAFILE1 数据文件需要有如下格式：文件名 SIZE 初始文件大小 [AUTOEXTEND OFF| ON] [MAXSIZE|NEXT SIZE MAXSIZE SIZE]
?文件名是数据文件的路径名，可以是绝对路径，也可以是相对路径，如“路径\xxx.dbf”，注意路径必须先建立好。
?初始化文件大小，是数据文件刚建立起来的时候所占物理磁盘空间的大小；
?AUTOEXTEND，是否自动扩展数据文件的大小，OFF表示关闭自动扩展，数据文件只能是初始大小，ON表示开启自动扩展，当数据文件超过初始大小的时候，会自动增大。默认值为OFF。
?如果设置自动扩展，则需要设置最大值MAXSIZE，如设置2000m，当然也可以设置为UNLIMITED，表示无限表空间。如果要指定每次扩展的大小，可以使用NEXT SIZE MAXSIZE SIZE语法，表示每次扩展多少尺寸，最大能扩展到多大（大小上限）。
?[LOGGING | NOLOGGING]该子句用来声明这个表空间上所有的用户对象的日志属性，即当操作包括表，索引，分区，物化视图，物化视图上的索引，分区等是否记录日志。缺省值为LOGGING。
?[ONLINE|OFFLINE]表空间的状态，ONLINE表示表空间创建后立即有效，OFFLINE表示表空间创建后暂时无效，即不能使用，只有设置为ONLINE后才有效，默认值为ONLINE。
?EXTENT_MANAGEMENT_CLAUSE表空间如何管理范围，推荐设置为本地管理，值为EXTENT MANAGEMENT LOCAL，如果不指定该值，则ORACLE会根据初始化时内部的其他参数进行自动设置一个默认值，生产环境下建议指定该值为本地管理。

--创建临时数据表空间
CREATE TEMPORARY TABLESPACE TABLESPACE_NAME
TEMPFILE DATAFILE1，[DATAFILE 2]…
EXTENT_MANAGEMENT_CLAUSE
参数说明：
?DATAFILE1数据文件的格式语法：文件名 SIZE 初始文件大小，注意临时数据表空间的数据文件一般不需要指定最大值，Oracle对其采用了贪吃算法策略，因此，该表空间会自动逐渐增大。当然你也可以手动指定。
?其他参数见永久数据表空间的。
?临时表空间默认是不记日志的。

【最简语法】

Create tablespace 表空间名称
表空间类型
‘物理文件全路径’
Size 初始文件大小

【示例】

永久数据表空间和临时数据表空间的建立。

--建立一个数据表空间。
CREATE  TABLESPACE tbl_itcast_dat
DATAFILE
‘D:\Applications\Oracle\mydata\itcast_dat01.dbf‘
SIZE 50m
AUTOEXTEND ON 
NEXT 5m 
MAXSIZE 2000m
EXTENT MANAGEMENT LOCAL

--创建临时数据表空间。
CREATE TEMPORARY TABLESPACE  tbl_itcast_tmp
TEMPFILE 
‘D:\Applications\Oracle\mydata\itcast__tmp.dbf‘
SIZE 20m
EXTENT MANAGEMENT LOCAL

解释：

技术分享

【提示】：

文件路径（data目录）必须提前存在，否则：

技术分享

注意：实际企业开发中，不要用最简化的方式来进行表空间的创建。

【参考示例1】

--创建数据表空间
CREATE TABLESPACE TBS_CSP_BS_DAT 
DATAFILE ‘/dev/rlv_dat001‘ SIZE 2000M REUSE AUTOEXTEND OFF,
LOGGING
ONLINE
PERMANENT
EXTENT MANAGEMENT LOCAL;
--创建临时数据表空间
CREATE TEMPORARY TABLESPACE TBS_CSP_BS_TMP
TEMPFILE ‘/dev/rlv_dat009‘ SIZE 2000M REUSE AUTOEXTEND OFF
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 10M;

注：PERMANENT是显式的指定创建的是永久的表空间，用来存放永久对象。默认值。

【参考示例2】

--创建数据表空间
create tablespace tbs_user_data
logging
datafile ‘D:\oracle\oradata\Oracle9i\user_data.dbf‘
size 50m
autoextend on
next 50m maxsize 20480m
extent management local;
--创建临时数据表空间
create temporary tbs_user_temp  
tempfile ‘D:\oracle\oradata\Oracle9i\user_temp.dbf‘ 
size 50m  
autoextend on  
next 50m maxsize 20480m  
extent management local;

2.6. 删除表空间

语法：

技术分享

【示例】

--删除表空间以及下面所有数据和数据文件（全删，寸草不生）
DROP TABLESPACE tbl_itcast_tmp INCLUDING CONTENTS AND DATAFILES;
提示：如果不加后面的一堆，则，只是将表空间进行了逻辑删除（Oracle无法管理使用这个表空间了，但数据文件还存在）。

2.7. 表空间的一个应用

【示例】

建立表的时候指定表空间。

技术分享

企业开发中，一定不要用默认的表空间，一定使用要指定表空间。

最简的一个建表脚本：

技术分享

注意:

写建表的语句的时候,可以指定存储的表空间,但不建议指定表空间的参数。

3. 用户和权限

3.1. 用户角色权限的关系

预备知识：

技术分享

3.2. 预定义用户（账户）

Oracle预定义有很多用户，用于不同的用途。这些用户大都默认是禁用的（如scott，hr等），但有两个最重要的用户是默认开启的，这两个用户就是SYS和SYSTEM。

l SYS 帐户(数据库拥有者):

n 拥有 DBA 角色权限

n 拥有 ADMIN OPTION 的所有权限

n 拥有 startup, shutdown, 以及若干维护命令

n 拥有数据字典

l SYSTEM 帐户

n 拥有 DBA 角色权限.

注意：这些帐户通常不用于常规操作。

Sys和system账户的区别：

l sys用户是数据库的拥有者，是系统内置的、权限最大的超级管理员帐号。

l system用户只是拥有DBA角色权限的一个管理员帐号，其实它还是归属于普通用户。

3.3. 操作用户

l 创建用户的语句

create user 用户名

identified by 密码(不要加引号)

default tablespace 默认表空间名 quota 5M on 默认表空间名

[temporary tablespace 临时表空间名]

[profile 配置文件名] //配置文件

[default role 角色名] //默认角色

[password expire] //密码失效

//如果设置失效，那么第一次登录的时候，会提醒你更改密码。

[account lock] //账号锁定（停用）

l 修改用户

alter user 用户名 identified by 密码 quota 10M on 表空间名

alter user 用户名 account lock/unlock

l 删除用户

drop user 用户名 [cascade].如果要删除的用户中有模式对象，必须使用cascade.

【示例】最简方式创建一个用户

切换到sys用户下：

技术分享

注：未指定的参数都采用默认值。

【示例】借助工具创建一个用户

技术分享

创建用户的时候指定的表空间，会成为以后在该用户下建立对象（表）的默认存储表空间。

技术分享

-- 语句：Create the user 
/*创建用户并指定表空间  */
create user itcast19
  identified by itcast19
  default tablespace TBL_ITCAST19_DAT
  temporary tablespace TBL_ITCAST19_TMP; --上锁解锁改密码等

注意：

一般企业开发中，建表要手动指定表空间，可以让不同模块、不同功能的对象存储在不同的数据文件中，可以提高性能。

【示例】删除用户

技术分享

--删除用户及其下面所有的对象

drop user itcasttest cascade;

提示，每个数据库用户帐户具备：

l 一个唯一的用户名

l 一个验证方法

l 一个默认的表空间

l 一个临时表空间

l 权限和角色

l 每个表空间的配额.

3.4. 配置角色和权限

使用上面创建的用户登录测试：

技术分享

结果报错。

提示说：该用户没有创建会话的权限，登录被拒绝。

那该如何赋权呢？赋什么权限呢？

Oracle内置有大量的权限属性：

技术分享

常见权限：

技术分享

我们可以将create session权限赋权给新建的用户.新建的用户就可以登录了.

我们再建立一张表看看:

技术分享

结果又提示是权限不足。= =...

再添加建表的权限：

技术分享

再次测试建表：

技术分享

再添加一条数据看看：

技术分享

竟然又没有权限！。。。

结论：这样一个个添加权限非常的麻烦！

是否可以使用比较简单的方式将普通用户的权限赋予给一个用户呢？

可以！通过预定义内置角色就可以实现。

技术分享

需要分配 unlimited tablespaces 权限

如何选择预定义的角色呢？

普通用户就选择：connect和Resource角色即可。

管理员用户选择：connect和Resource、dba角色。

/*给用户授予权限  */
grant connect,resource to username;

再次登录、各种操作测试，均正常了！

【提示】

如果遇到这个错误：

技术分享

说明当前用户没有操作该表空间的权限，需要手动加入这个权限：

技术分享

梳理回顾建立一个普通用户的过程：

1.创建用户—2.赋权限（connect和resourece角色）

3.5. Oracle用户(user)和方案(schema)

几个概念：

l 方案就是属于某一用户的所有对象（表、视图等）的集合.

l 用户名和方案名往往是通用的.

l 一个用户只能关联一个方案.

l 创建用户时系统会自动创建一个同名方案（schema）

提示：

Scott用户的方案名也是scott，因此，后面我们将这两个概念放在一起用，即我们可以说，某表是scott用户下的对象，也可以说是scott方案下的对象。

3.6. 跨域访问对象

跨域访问也称之为跨用户访问、跨方案访问，访问的方式为：用户名.对象名，

技术分享

如在itcast用户下访问scott用户下的emp表的数据：

技术分享

原因：itcast用户没有对scott用户的对象访问权限。

Oracle用户的权限分为两种:

l 系统权限(System Privilege): 允许用户执行对于数据库的特定行为，例如：创建表、创建用户等

l 对象权限(Object Privilege): 允许用户访问和操作一个特定的对象，例如：对其他方案下的表的查询

【示例】需求：itcast用户要读取scott用户中emp表的数据。

技术分享

--赋予权限的Sql语句：

技术分享

--itcast用户登录测试：

再次查询scott用户的emp表，可以查询

技术分享

注意：

赋权的时候，只能是自己拥有的权限、或者该权限是可以传递的，才可以将其赋予别人。

1. 视图VIEW

问题：

Itcast用户现在只需要查询10部门的员工数据就行了，scott也不想将所有数据都开放给itcast用户。

1.1. 视图的概念和作用

概念：

l 视图是一种虚表.

l 视图建立在已有表的基础上, 视图赖已建立的这些表称为基表。

l 向视图提供数据内容的语句为 SELECT 语句, 可以将视图理解为存储起来的 SELECT 语句.

l 视图向用户提供基表数据的另一种表现形式

作用：

技术分享

1.2. 语法

语法：create VIEW 视图名称 as select···（查询语句）

提示：

子查询可以是任意复杂的 SELECT 语句。

语法详细分析：

技术分享

纠正：默认值不是只读。

1.3. 操作视图

视图只能创建、替换和删除，不能修改。

1.3.1. 创建视图

【示例】创建10号部门的视图

--sql语句建立视图
CREATE VIEW v_emp10
AS
SELECT * FROM emp WHERE deptno=10;

提示：如果提示权限不足而导致无法添加视图，则需要添加权限，一般为学习方便，我们会直接添加dba角色权限。

--切换到sys用户下，为scott添加dba权限：

技术分享

1.3.2. 查询视图

【示例】查询视图

技术分享

【示例】视图的真实内容查看

技术分享

结论：可以看出，视图的本质就是sql语句。

1.3.3. 替换视图

视图没有修改功能。

【示例】要将视图改为可以查询10号部门的员工信息且工资要大于2000：

CREATE OR REPLACE VIEW v_emp10 
AS
SELECT * FROM emp WHERE deptno=10 AND sal >2000;

提示：

平时我们在编写建立视图的语句时候，一般直接把replace加上，即直接CREATE OR REPLACE。（没有就创建，有就替换）

1.3.4. 删除视图

【示例】删除10号部门的这个视图

DROP VIEW v_emp10;
SELECT * FROM v_emp10;

1.4. 几个参数说明

--先创建视图再创建表:一般用的不多，一般我们都是先有表再创建视图。
CREATE OR REPLACE FORCE VIEW v_test2015
AS
SELECT * FROM test2015;

SELECT * FROM v_test2015

--视图默认情况下和表一样，拥有表类似的功能，可以crud
SELECT t.*,ROWID FROM v_emp10 t;

SELECT * FROM emp;

CREATE OR REPLACE VIEW v_emp10 
AS
SELECT * FROM emp WHERE deptno=10
WITH CHECK OPTION;--数据的增加和修改，必须满足子查询的条件

--一般视图，我们主要用来查询的，一般不维护它。
CREATE OR REPLACE VIEW v_emp10 
AS
SELECT * FROM emp WHERE deptno=10
WITH READ ONLY;

1.5. 只读视图

一般情况下，视图主要用来提供查询的，并不希望用户去修改它，因此，我们可以创建只读视图。

创建只读视图只需要添加with read only 选项即可，这样就可以屏蔽对视图的DML操作。

【示例】将已有的视图修改为只读视图

CREATE OR REPLACE VIEW v_emp_dept10
AS
SELECT * FROM emp WHERE deptno=10 AND sal >2000
WITH READ ONLY ;

友情提示：

其实，很多大的系统中，比如银行，某些客户会告诉你，这个表存这个数据，那个表存哪个数据，但实际上，可能不是真正的表，而是视图，而且还是只读的。

为什么给视图？原因是：

如果是存钱的表，那么放开给你，是不是非常危险。如果业务需要确实是需要更改这个表的数据呢?一般来调用存储过程(一般有提供,有一定特定功能,还能记录日志)来改表，为了安全！不能直接改表。

1.6. 跨域访问视图

【示例】只放开scott下的emp表的部分数据给itcast14用户查询，开放的数据要求为：20号部门的员工，字段只显示员工号和姓名，且要求这两个字段的标题显示为中文。（要求本例使用工具来操作）

--在scott下创建视图（视图名称参考为：v_emp_dept20）

--将生成的脚本如下：
create or replace view v_emp_dept20 as
  select empno "编号",ename "姓名"
    from emp
   where deptno=20
WITH READ ONLY;

--scott下查询验证一下：
SELECT * FROM v_emp_dept20;

--将该视图赋予itcast用户：在scott用户下操作：
grant select on v_emp_dept20 to itcast;

--切换到itcast用户下进行查询验证：
Select * from scott.v_emp_dept20;

另外补充：

视图可以屏蔽筛选不同字段、字段名称等，因此，你看到的时候的字段也未必是真实表中存在的！

CREATE OR REPLACE VIEW v_emp10 
AS
SELECT empno 编号,ename empname FROM emp WHERE deptno=10
WITH READ ONLY;

技术分享

1.7. 视图小结

视图和表的区别：

视图是实体表的映射，视图和实体表区别就是于视图中没有真实的数据存在。

什么时候使用视图：

1，在开发中，有一些表结构是不希望过多的人去接触，就把实体表映射为一个视图。

2，在项目过程中，程序人员主要关注编码的性能、业务分析这方面。对于一些复杂的SQL语句，设计人员会提前把这些语句封装到一个视图中，供程序人员去调用

注意:在企业中，你查询的对象（表）他可能不是一张的表，可能是视图；你看到的视图的字段可能也不是真实的字段。

1. 同义词SYNONYM

问题：我们想伪装一下这个视图的名字，或者是嫌调用的这个对象名字太长，怎么办？

1.1. 同义词的概念和作用

同义词就是（对象的）别名，可以对表、视图等对象起个别名，然后通过别名就可以访问原来的对象了。

作用：

l 方便访问其它用户的对象

l 缩短对象名字的长度

1.2. 语法

1.3. 操作同义词

同义词只有创建和删除操作。

【需求】在itcast用户下为视图scott.v_emp_dept20创建一个同义词emp20;

技术分享

友情提示：

如果工作中,你遇到一张”表”来查询数据,那么它一定是表么?不一定,可能是视图,也可能是同义词.

另外，任何对象都能起别名。下面的例子对emp表起个别名：

技术分享

重点：

多表关联查询（oracle的语法，左外，右外自连接）
子查询：any和all的面试题，子查询和多表查询的选择（面试）
分页：rownum+子查询！！！
rowid：删除重复数据（面试）
两个新语法：批量插入(insert into table select ...) 复制表（create table tablename as select ....）
delete和truncate的区别（面试），高水位，如何消除高水位（truncate，move）
Oracle的事务和mysql的事务的不同（oracle在dml时隐式开启，必须手动提交（不建议隐式提交））
约束的使用（外键是否要增加）（面试）
序列：创建(create sequence 序列名字)和插入数据的使用。
表空间-了解
创建用户：创建用户+赋予角色（connect,resource，注意：unlimited tablespace权限如果没有加上）
用户和方案的关系
跨域访问
视图，
同义词

oracle06

声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉：投诉/举报工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。

联系
我们

首页 > 代码库 > oracle06

oracle06

1. Oracle的体系结构 - 了解

1.1. Oracle数据库和Oracle实例

1.2. Oracle常见的存储文件

2. 表空间(Tablespace)的管理

2.1. 表空间的概念

2.2. 数据库的存储结构--了解

2.3. 常见的表空间分类—了解

2.4. Oracle对表空间的管理方式—了解

2.5. 表空间的创建

2.6. 删除表空间

2.7. 表空间的一个应用

3. 用户和权限

3.1. 用户角色权限的关系

3.2. 预定义用户（账户）

3.3. 操作用户

3.4. 配置角色和权限

3.5. Oracle用户(user)和方案(schema)

3.6. 跨域访问对象

1. 视图VIEW

1.1. 视图的概念和作用

1.2. 语法

1.3. 操作视图

1.3.1. 创建视图

1.3.2. 查询视图

1.3.3. 替换视图

1.3.4. 删除视图

1.4. 几个参数说明

1.5. 只读视图

1.6. 跨域访问视图

1.7. 视图小结

1. 同义词SYNONYM

1.1. 同义词的概念和作用

1.2. 语法

1.3. 操作同义词

看完仍有疑问？有类似问题直接问程序猿