当前位置:Gxlcms > 数据库问题 > oracle06

oracle06

时间:2021-07-01 10:21:17 帮助过:14人阅读

 - 了解

1.1. Oracle数据库和Oracle实例

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

两者的解释如下:

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

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

 

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

技术分享

1.2. Oracle常见的存储文件

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

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

技术分享

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

技术分享

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

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

技术分享

 

 

2. 表空间(Tablespace)的管理

2.1. 表空间的概念

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

 技术分享

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

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

技术分享

表空间的属性特点:

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

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

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

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

官方数据存储结构图:

技术分享

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

技术分享

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

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

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

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

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

 

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

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

 

数据操作的过程:

技术分享

【小结】

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

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

 

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

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

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

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

 

临时表空间和UNDO表空间的异同:(了解)

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

不同之处: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预定义有很多用户,用于不同的用途。这些用户大都默认是禁用的(如scotthr等),但有两个最重要的用户是默认开启的,这两个用户就是SYSSYSTEM

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

拥有 DBA 角色权限

拥有 ADMIN OPTION  的所有权限

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

n 拥有数据字典

l SYSTEM 帐户

拥有 DBA 角色权限.

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

Syssystem账户的区别:

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 权限和角色

每个表空间的配额.

 

3.4. 配置角色和权限

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

技术分享

结果报错。

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

 

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

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

技术分享

常见权限:

技术分享

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

我们再建立一张表看看:

技术分享

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

再添加建表的权限:

技术分享

技术分享

再次测试建表:

技术分享

再添加一条数据看看:

技术分享

竟然又没有权限!。。。

 

结论:这样一个个添加权限非常的麻烦!

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

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

技术分享

需要分配 unlimited tablespaces 权限

 

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

普通用户就选择:connectResource角色即可。

管理员用户选择:connectResourcedba角色。

 

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

 

再次登录、各种操作测试,均正常了!

【提示】

如果遇到这个错误:

 技术分享

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

 技术分享

技术分享

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

1.创建用户—2.赋权限(connectresourece角色)

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

几个概念:

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

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

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

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

提示:

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

3.6. 跨域访问对象

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

 技术分享

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

技术分享

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

Oracle用户的权限分为两种:

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

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

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

技术分享

--赋予权限的Sql语句:

技术分享

--itcast用户登录测试:

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

技术分享

注意:

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

 

1. 视图VIEW

问题:

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

 

1.1. 视图的概念和作用

概念:

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表起个别名:

技术分享

重点:

 

  1. 多表关联查询(oracle的语法,左外,右外 自连接)
  2. 子查询:anyall的面试题,子查询和多表查询的选择(面试)
  3. 分页:rownum+子查询!!!
  4. rowid:删除重复数据(面试)
  5. 两个新语法:批量插入(insert into table select ...) 复制表(create table tablename as select ....
  6. deletetruncate的区别(面试),高水位,如何消除高水位(truncatemove
  7. Oracle的事务和mysql的事务的不同(oracledml时隐式开启,必须手动提交(不建议隐式提交))
  8. 约束的使用(外键是否要增加)(面试)
  9. 序列:创建(create sequence 序列名字)和插入数据的使用。
  10. 表空间-了解
  11. 创建用户:创建用户+赋予角色(connect,resource,注意:unlimited tablespace权限如果没有加上)
  12. 用户和方案的关系
  13. 跨域访问
  14. 视图,
  15. 同义词

 

 

oracle06

标签:组织   单位   物理   account   序列   字段   word   空间名   永久   

人气教程排行