【数据库学习笔记】Oracle_01_sql*plus,存储结构,用户管理,常用数据类型

Oracle_01

A.简介

1.Oracle服务

在成功安装Oracle后，会有7个服务

a.OracleDBConsoleorcl

Oracle数据库控制台服务，orcl是Oracle的实例标识,默认的实例为orcl

在运行Enterprise Manager(企业管理器OEM)的时候，需要启动这个服务

b.OracleOraDb11g_home1TNSListener

监听器服务，服务只有在数据库需要远程访问的时候才需要

(非必须启动，下面会有详细详解)

c.OracleServiceORCL

数据库服务(数据库实例)，是Oracle核心服务该服务

是数据库启动的基础， 只有该服务启动

Oracle数据库才能正常启动。(必须启动)

d.OracleOraDb11g_home1ClrAgent

e.OracleMTSRecoveryService

f.OracleJobSchedulerORCL

g.Oracle ORCL VSS Writer Service

常用的启动顺序

OracleOracle_homeTNSListener

OracleServiceORCL

OracleDBConsoleORCL

关闭时次序相反

2.实例和数据库

a.实例

一组Oracle 后台进程/线程以及一个共享内存区

这些内存由同一个计算机上运行的线程/进程所共享

实例用于管理和控制数据库，是和数据库交互的媒介

b.数据库

基于磁盘的数据文件，控制文件，日志文件，参数文件等

组成的物理文件集合，为实例提供数据

数据库可以由多个实例装载和打开

而实例在其整个生存期中最多能装载和打开一个数据库

3.服务启动方式

a.方式1

在window系统：计算机--管理--服务和应用程序--服务

只用sql*plus的话，启动OracleServiceORCL即可

要是使用PL/SQL Developer等第三方工具的话

OracleOraDb11g_home1TNSListener服务也要开启

OracleDBConsoleorcl是进入基于web的EM必须开启的

其余服务很少用

b.方式2

以管理员身份，通过命令行的方式，oracle启动流程

1)windows

lsnrctl start（启动监听）

oradim -startup -sid 数据库实例名

2)linux

lsnctl start

sqlplus sys/change_on_install as sysdba

startup

c.方式3

数据库实例服务启动情况下，使用sqlplus以管理员身份管理数据库

实例的启动分为3个部分，启动实例 ，加载数据库 ，打开数据库

1)startup nomount

创建一个Oracle实例，不加载数据库，也不打开任何数据文件

常用于创建新数据库或重建控制文件时候

2)startup mount

该命令创建实例并且安装数据库，但没有打开数据库

Oracle系统读取控制文件中关于数据文件和redo log文件的内容

但并不打开这些文件，这种打开方式常在数据库维护操作时使用

如对数据文件的更名、改变redo log以及打开归档方式

执行数据库的full database recovery

3)startup open

先执行nomount，然后执行mount

再打开包括Redo log文件在内的所有数据库文件

这种方式下可访问数据库中的数据。是常规的启动模式

4)startup

后面不带任何参数，表示以open方式打开数据库

4.关闭数据库

a.shutdown normal

正常方式关闭数据库，没有时间限制。通常使用这种方式

执行后，会阻止新用户的链接，等待当前连接用户断开连接

所有用户断开连接后立即关闭数据库

b.shutdown immediate

立即关闭数据库。立即中断当前用户连接

立即中断当前活动事务，立刻关闭数据库

B.sql*plus

1.概述

是oracle自带工具软件，主要用于执行sql语句,pl\sql块

使用方式：

a.在开始->程序->oracle oradb_home10g->application development->sql*plus

b.在运行栏中输入: sqlplus即可

2.常用命令

a.help

b.desc[ribe]

查询指定对象的数据结构

c.spool

查询内容输出到指定文件中

备注：spool on至spool off之间所有的内容被写入指定文件中

没有使用spool off或者spool out命令关闭输出，则输出文件中不会添加

查看e:\b.sql文件

d.show

显示SQL*Plus系统变量的值或环境变量的值

e.edit

编辑指定的sql脚本或sql缓冲区最近sql语句

回车后，自动打开目标文件，若不存在，则有如下提示

然后在文件中编辑sql语句即可

f.save

把sql缓冲区最近一条语句保存到指定文件中

查看路径下的文件

g.get

把文件内容放进sql缓冲区

h.start/@

执行sql文件

3.其他常用命令

a.conn[ect]

用法: conn 用户名/密码@网络服务名 [as sysdba/sysoper]

特权用户身份连接时，必须带上 as sysdba 或是 as sysoper

切换用户

使用超级用户

b.disc[onnect] 

断开与当前数据库的连接

c.passw[ord]

修改用户的密码，如果要想修改其它用户的密码，需要用sys/system登陆

d.show user

显示当前用户名

e.exit

断开与数据库的连接，同时会退出sql*plus

4.模式

也称作方案(Schema），是用户所拥有的数据库对象的集合

在Oracle数据库中，对象是以用户来组织的

用户与模式是一一对应的关系，并且两者名称相同

例如：SCOTT用户拥有的所有对象都属于SCOTT模式

而HR用户拥有的所有对象都属于HR模式

5.注意事项

在同一个模式中不能存在同名对象

但是不同模式中的对象名称则可以相同

用户可以直接访问自己模式对象

但如果要访问其它模式对象，则必须具有该对象的相应访问权限

例如用户HR要查看SCOTT模式中的EMP表时

则必须在EMP表上具有SELECT操作的权限

同时必须附加模式名作为前缀

6.查询当前用户下的所有表

a.select table_name from tabs;

b.select table_name from user_tables;

c.select tname from tab;

d.select * from dba_tables where owner=‘SCOTT';

语句4 必须dba身份登录，同时owner对应值必须大写

查看系统所有用户，必须以sysdba身份登录

select username,account_status from dba_users;

7.Enterprise manager console(企业管理器)

是通过web管理管理oracle数据库，一般默认端口是1158的

必须在OracleDBConsoleorcl和oracle服务启动下才行

sysdba或system权限

访问url地址：http://ip:1158/em

C.存储结构

1.概述

数据库存储数据的方式称为存储结构

Oracle分为逻辑存储结构和物理存储结构

逻辑存储结构用来描述Oracle内部组织和管理数据的方式

而物理结构用于展示Oracle在操作系统中的物理文件组成

逻辑存储结构是从逻辑分析数据库的组成

物理存储结构是实际的数据存储单元

就是操作系统上的数据库相关文件

逻辑存储结构是一种层次结构，是面向用户的。

2.表空间

是oracle数据库的最大逻辑划分区域

常用来存放数据表，索引等数据对象，任何对象在创建时必须指定表空间

表空间与数据文件相对应，一个表空间由一个或多个数据文件组成

一个数据文件只属于一个表空间

oracle数据的存储空间在逻辑上表现为表空间

而在物理上表现为数据文件

空间相当于文件夹，数据文件相当于文件夹中的文件

每个数据库至少包含一个表空间(system表空间)

表空间的大小相当于从属于它的数据文件大小的总和

创建数据库时候，Oracle系统自动创建多个默认的表空间

除了管理数据的表空间之外，还有管理Oracle内部数据(如数据字典)的表空间

从物理上看,数据库由控制文件、数据文件、重做日志文件等操作系统文件组成

Oracle11g默认的主要表空间：

a.SYSTEM表空间

存放oracle系统内部表和数据字典的数据

如表名，列名，用户名等。不赞成用户数据存在此

b.SYSAUX表空间

是SYSTEM表空间的辅助

降低负荷，存储数据字典外的其他数据

不存储用户数据，oracle系统内部自动维护

c.UNDO表空间

存储对数据表进行修改前的旧数据

一般名为UNDOTBS1的表空间，文件名UNDOTBS01.DBF

d.USERS表空间

Oracle建议用户使用的表空间

存储用户的各种数据对象，SCOTT的对象存储于此

e.除了默认的表空间外

用户应根据系统实际情况和对象类型创建多个自定义表空间

以区分用户数据和系统数据

不同系统的数据应存放不同盘符的各个表空间

用户的数据必须存放在数据文件中

而数据文件必须属于一个表空间

若不设置则默认的临时表空间为temp

默认永久性表空间是system

查看用户表空间 表对象

select  table_name,tablespace_name from user_tables;

3.创建表空间

a.创建临时表空间

查看路径

b.创建永久表空间

查看路径

D.用户管理

1.安全管理

可以分为3个步骤

a.首先，用户向数据库提供身份识别信息，即提供一个数据库账号

b.接下来用户还需要证明他们所给出的身份识别信息是有效的，这是通过输入密码来实现的，用户输入的密码经过数据库的核对确认用户提供的密码是否正确

c. 最后，假设密码是正确的，那么数据库认为身份识别信息是可信赖的。此时，数据库将会在基于身份识别信息的基础上确定用户所拥有的权限，即用户可以对数据库执行什么操作

因此，为了确保数据库的安全，首要的问题就是对用户进行管理

2.用户

是在数据库中定义的一个名称，更准确地说它是账户，习惯上称其为用户

当连接到Oracle数据库时，操作人员必须提供正确的用户名和密码

连接到数据库的用户所具有的权限是不同的，Oracle提供了一些特权用户

比如，SYSDBA或SYSOPER

这类用户主要用于执行数据库的维护操作，如启动、关闭等操作

SYSDBA和SYSOPER的区别在于：

SYSDBA不仅具备SYSOPER的所有权限，而且还可以建立数据库，执行不完全恢复

在Oracle 11g中，Oracle提供了默认的特权用户SYS

当以特权用户身份登录数据库时，必须带有AS SYSDBA或AS SYSOPER选项

3.忘记管理员密码

a.sqlplus/nolog

b.connect/as sysdba

c.alter user sys identified by sys

4.创建新用户

具有DBA权限的用户执行

5.用户授权

新用户是没有任何权限的, 需要dba授权

无法连接，需要授权

再次连接

6.用户权限

根据管理方式不同Oracle权限分：系统权限和对象权限

a.系统权限

系统权限是在系统级对数据库进行存取和使用的机制

比如是否能连接到数据库系统（SESSION权限）

执行DDL语句（如CREATE.ALTER、和DROP）

b.对象权限

对象权限是指某一个用户对其他用户的表、视图等对象的操作权限

对于某些模式对象，比如簇、索引、触发器、数据库链接等

没有相应的实体权限，由系统权限进行管理

7.角色

是一个独立的数据库实体，它包括一组权限

也就是说，角色是包括一个或者多个权限的集合，它并不被哪个用户所拥有

角色可以被授予任何用户，也可以从用户中将角色收回

Qracle提供了三种标准的角色（role）

a.CONNECT(连接角色)

临时用户，特别是那些不需要建表的用户，通常只赋予他们CONNECTrole

CONNECT是使用Oracle的简单权限

这种权限只有在对其他用户的表有访问权时

包括select、insert、update和delete等，才会变得有意义

只可以登录Oracle，不可以创建实体，不可以创建数据库结构

b.RESOURCE Role(资源角色)

更可靠和正式的数据库用户可以授予RESOURCE role

RESOURCE提供给用户另外的权限以创建他们自己的表、序列

过程（procedure）、触发器（trigger）、索引（index）和簇（cluster）

只可以创建实体，不可以创建数据库结构

c.DBA Role(数据库管理员角色)

拥有所有的系统权限

包括无限制的空间限额和给其他用户授予各种权限的能力。

SYSTEM由DBA用户拥有

下面介绍一些DBA经常使用的典型权限，只有DBA才可以创建数据库结构

在Oracle 11g中含有200多种系统特权

并且所有这些系统特权均在SYSTEM_PRIVILEGE_MAP数据目录视图中

8.系统权限授权语法

grant sys_privi | role to user | role | public [with admin option]

a.sys_privi：

表示Oracle系统权限，系统权限是一组约定的保留字

比如，若能够创建表，则为“CREATE TABLE”

b.role：角色

c.user：具体的用户名，或者是一些列的用户名

d.public：保留字，代表Oracle系统的所有用户

e.with admin option：表示可再将权限授予另外的用户

为用户admin授予开发系统权限

可以把这两个权限授予其他用户

9.收回权限

revoke sys_privi | role from user | role | public；

撤销admin用户的resource权限

10.对象权限

grant obj_privi | all column on schema.object to user | role | public [with grant option] | [with hierarchy option]

a.obj_privi：

表示对象的权限

可以是ALTER、EXECUTE、SELECT、UPDATE和INSERT等

b.  role：角色名

c.user：被授权的用户名

d.with admin option：表示被授权者可再将系统权限授予其他的用户

e. with hierarchy option：在对象的子对象（在视图上再建立视图）上授权给用户

 给用户admin授予select、insert表soctt.emp的权限

11.收回对象权限

revoke obj_privi | all on schema.object from user | role | public cascade constraints

obj_privi：表示对象的权限

public：保留字，代表Oracle系统的所有权限

cascade constraints：表示有关联关系权限也被撤销

从admin用户撤销scott.emp表的update和delete

12.修改用户

用户创建后，管理员可以对用户进行修改，包括修改口令

改变默认表空间、临时表空间等。

语法与创建用户的语法基本相似，只是把CREATE关键字替换成ALTER罢了

13.解锁被锁住的用户

Oracle默认安装完成后，为了安全起见，很多用户处于LOCKED状态

DBA可以对LOCKED状态的用户解除锁定

使用alter user命令解除被锁定的账户

14.删除用户

一般以dba的身份去删除某个用户

如果用其它用户去删除用户则需要具有 drop user的权限

 cascade：级联删除选项，如果用户包含数据库对象

则必须加cascade选项，此时连同该用户所拥有的对象一起删除

E.常用数据类型

1.字符类型

2.数字类型

3.时间，时间间隔类型

4.时间字段可取值范围

5.大对象类型