Oracle实战笔记（第四天）
导读
今天的主要内容是：两个管理员用户sys&system、数据库的逻辑备份和逻辑恢复、数据字典、表空间&数据文件。
一、Oracle数据库管理员的职责（了解）
数据库管理员（dba）是对数据库数据进行维护和管理的工作者，一般有以下职责/工作：
- 安装和升级Oracle数据库。
- 建库、表空间、表、视图、索引......
- 指定并实施数据的备份和恢复计划。
- 数据库权限管理、调优、故障排除。
- 对于高级dba，要求能参与项目开发，会编写sql语句、存储过程、触发器、规划、约束、包等内容。
二、Oracle的两个管理员用户：sys&system
Oracle中的数据库管理员用户主要是：sys和system。但这两个用户是有区别的：
1、最重要的区别，存储的数据的重要性不同
sys：是oracle权限最高的用户：所有的oracle的数据字典的基表和视图都存放在sys用户中，这些基表和视图对于oracle的运行是至关重要的，由数据库自己维护，任何用户都不能手动更改。 sys用户拥有sysdba（系统数据库管理员）、sysoper（系统操作员）、dba（数据库管理员）三个角色或权限。
system：用户用于存放次一级的内部数据，如oracle的一些特性或工具的管理信息。system用户拥有sysoper权限、dba角色权限或系统权限，比sys。
2、其次的区别：权限的不同
sys：用户具有“SYSDBA”或者“SYSOPER”系统权限，所以必须用"as sysdba"或“as sysoper”登录，不能用normal。
注意：可能你使用普通用户 as sysdba也能登录成功，比如SQL> conn scott/tiger as sysdba 也能登录成功，但这不代表scott具有sysdba的权限。甚至有时候你随便输入一个不存在的账号密码然后输入 as sysdba也能登录成功，这是因为Oracle登录除了口令认证外还能使用操作系统认证，这就是外部认证方式，验证时oracle会对数据库配置文件sqlnet.ora进行读取，这文件就是oracle登录需要验证读取的文件。该文件的位置为：D:\oracle\product\10.2.0\db_1\network\ADMIN\sqlnet.ora，该文件中的SQLNET.AUTHENTICATION_SERVICES= (NTS)，参数值：NTS就是指定使用操作系统认证方式。不输入用户名密码登录成功还有重要一点：安装oracle时，oracle会自动创建一个操作系统数据库管理员组“ora_dba”，将当前操作系统用户添加至ora_dba组中，才可登录成功。所以如果你访问的是本地Oracle，那么你的电脑本身就已经相当于是sys用户了，就好比在自己家，当个皇帝肿么了的感觉。
system：拥有sysoper和dba角色权限。正常登录即可。
3、sysdba、sysoper和dba三个角色的区别
首先权限大小不同，sysdba权限最大，sysoper次之，dba最小。
sysdba和sysoper的权限对比图如下：
而dba的权限相对就更小一些了：上面两种特权用户sys和sysoper都能启动和关闭数据库，而dba用户只有在启动数据库后才能执行各种操作。
三、数据库的逻辑备份
逻辑备份是指使用工具export将数据对象的结构和数据导出到文件的过程，逻辑恢复是指当数据库对象被破坏而使用工具import利用备份的文件把数据对象导入到数据库的过程，逻辑备份和恢复只能在open即数据库打开的情况下进行。（简单来说备份就是将数据库对象存到磁盘中，恢复就是将备份的数据导入数据库）
1、导出(3种)
导出具体分为三种：导出表、导出方案、导出数据库三种方式。
导出使用exp命令来完成的，该命令常用的选项有：
- userid: 用于指定执行导出操作的用户名、口令、连接字符串
- tables: 用户指定执行导出操作的表
- owner: 用于指定执行导出操作的方案
- full=y: 用户指定执行导出操作的数据库
- inctype:用于指定执行导出操作的增量类型 ，也叫增量备份(当第一次备份完之后,第二次备份只会备份新的数据,老的数据不会进行备份)
- rows: 用于指定执行导出操作是否要导出表数据
- file: 用于指定导出文件名路径
注意：使用导出操作的口令不能在sqlplus工具中运行（但是需要先连接数据库），而是在exp.exe程序中执行导出操作。该文件一般位于H:\app\Administrator\product\11.2.0\dbhome_1\BIN，H盘是我的Oracle安装盘，你可以直接点击exe进行操作界面，获取你可以使用cmd，进入界面，下面是使用cmd的导出过程：
2、导出之导出表
- 导出自己的表
exp userid=用户名/密码@数据库实例名 tables=(表名) file=导出表的路径
- 导出其他用户的表
exp userid=用户名/密码@数据库实例名 tables=(方案名.表名) file=导出表的路径
- 导出多张表
exp userid=用户名/密码@数据库实例名 tables=(表1名,表2名,表3名) file=导出表的路径 - 只导出表的结构(当表中的数据特别大时,只导出表的结构)
exp userid=scott/tiger@orcl tables=(emp) file=d:/test.dmp rows=n
- 当表的数据量非常大时,使用"直接导出的方式"来处理这种大表,速度比常规的方法要快
exp userid=scott/tiger@orcl tables=(emp) file=d:\aaa.dmp direct=y
3、导出之导出方案
导出方案是指导出一个方案或者多个方案中的所有对象(表、索引、约束..)和数据,并存放到文件中。
- 导出自己的方案
exp userid=scott/tiger@orcl owner=scott file=d:\scott.dm
- 导出他人的方案
exp userid=system/manager@orcl owner=(scott) file=D:\scott.dmp
- 导出多个方案
exp userid=system/manager@orcl owner=(system,scott) file=d:\test.dmp
4、导出之导出数据库
- 导出数据库是指使用export工具导出数据库中的所有对象及数据,要求用户必须具有sysdba或是exp_full_database的权限。
exp userid=system/manager@orcl full=y inctype=complete file=orcl.dmp
四、数据库的逻辑恢复
导入就是使用工具import将文件中的对象和数据导入到数据库中,导入的文件必须对应导出的文件。
下面是导入关键字imp的常用口令：
- userid：用于指定要执行导入操作的用户名、密码、连接字符串(也就是数据库实例名)
- tables：用于指定要执行导入操作的表
- fromuser：用于指定源用户
- touser：用于指定目标用户
- file：用于指定导入文件名
- full=y ：用于指定导入整个文件
- inctype：用于指定执行导入操作的增量类型
- rows：用于指定是否要导入表行(数据)
- ignore：如果表存在,则只导入数据
1、导入表
-
imp userid=scott/tiger@orcl tables=(emp) file=d:\emp.dmp;
- 导入表到其他用户
imp userid=system/manager@orcl tables=(emp) file=d:\test.dmp touser=scott
- 只导入表的结构
imp userid=scott/tiger@orcl tables=(emp) file=d:\emp.dmp rows=n
- 只导入数据
imp userid=scott/tiger@orcl tables=(emp) file=d:\emp.dmp ignore=y
2、导入方案
- 导入自身的方案
imp userid=scott/tiger@orcl file=d:\scott.dmp
- 导入到其他方案
imp userid=system/manager@orcl file=d:\scott.dmp fromuser=system touser=scott
3、导入数据库
-
imp userid=system/manager full=y file=d:\xxx.emp
五、数据字典
1、概念
数据字典是Oracle数据库中最重要的组成部分，它提供了数据库的一些系统信息（静态信息）。数据字典是描述数据的信息集合，是对系统中使用的所有数据元素的定义的集合。
动态性能记载了例程启动后的相关信息（动态信息）。当启动oracle server时，系统会自动建立动态性能视图；当停止oracle serve时，系统会删除动态性能视图。oracle的所有动态性能视图都以v-$开始的，并且oracle为每个动态性能视图都提供了相应的同义词，并且同义词是以v$开始的，例如v-$da tafile的同义词为v$da tafile；动态性能视图的所有者为sys，一般情况下，有dab或是特权用户来查询动态性能视图。
2、数据字典视图
数据字典的所有者是sys用户，所以用户只能在数据字典上执行查询操作，而其他操作都是系统自动完成的。Oracle中的数据字典包括数据字典基表和数据字典视图，其中基表存储数据库的基本信息，普通用户不能直接访问；而数据字典视图是基于数据字典基表所建立的视图，普通用户可以通过查询数据字典视图查询系统信息，数据字典视图主要包括user_xxx、all_xxx、dba_xxx三种类型。
- user_tables：用于返回用户对应方案的所有表。
--显示用户所有表名 select table_name from user_tables
- all_tables：返回当前客户可以访问的所有表。
- dba_tables：返回所有方案拥有的数据库表，但这种查询需要具有dba角色权限或具有select any table的系统权限。
- 在建立用户时，Oracle会把用户的信息存到数据库的数据字典中；当给用户授予权限或角色时，Oracle也会将权限或角色的信息存放到数据字典中。所以，通过查询数据字典可以查询用户信息、权限和角色等信息：
- dba_users：可以显示所有数据库用户的详尽信息。
--查询数据库中的所有用户名,密码 select username,password from dba_user;
- dba_sys_privs：可以显示用户具有的系统权限。
- dba_tab_privs：可以显示用户具有的对象权限。
--查询用户SCOTT的对象权限 select * from dba_tab_privs where grantee='SCOTT';
- dba_col_privs：可以显示用户具有的列权限。
- dba_role_privs：可以显示用户具有的角色。
--查询scott拥有的角色信息(用户名大写) select * from dba_role_privs where grantee='SCOTT';
- dba_rules：系统角色
--数据库中的角色信息 select * from dba_roles;
- dba_sys_privs：系统权限
--查询角色为"CONNECT" 的系统权限 select * from dba_sys_privs where grantee='CONNECT';
- 扩展：
--显示当前用户可以访问的所有数据字典视图 select * from dict where comments like'%grant%'; --显示当前数据库全称 select * from global_name;
六、表空间和数据文件
表空间是数据库的逻辑组成部分。从物理上讲，数据是存放在数据文件中的，而从逻辑上讲，数据是存放在表空间中的，一个表空间有若干个数据文件组成。把oracle数据库看作一个实在房间，表空间可以看作这个房间的空间，是可以自由分配，在这空间里面可以堆放多个箱子（箱子可以看作数据库文件），箱子里面再装物件（物件看作表）。用户指定表空间也就是你希望把属于这个用户的表放在那个房间（表空间）里面。
1、数据库的逻辑结构
Oracle中的逻辑结构包括：表空间、段、区、块。
数据库是由表空间构成，而表空间由段构成，段又由区构成，最后区又由Oracle块构成。这样分级是为了提高数据库的效率。数据库就好比一个国家，为了方便管理，我们将国家分为省，省又分为市，依次类推。Oracle也是如此，表空间的设计大大提高了数据库的效率，表空间能：1）控制数据库占用的磁盘空间大小；2）dba可以将不同的数据类型部署到不同的位置，这样有利于提高I/O性能，同时利于备份和恢复等操作。
2、表空间的操作
- 创建表空间：使用create tablespace来创建（操作用户需要具有dba权限或create tablespace的系统权限）。在创建数据库后为了方便管理表，我们通常要建立自己的表空间。
--创建一个名为sp01，数据文件大小为20m，段大小为128k的表空间，存储位置为：d:\test\sp01.dbf
create tablespace sp01 datafile 'd:\test\sp01.dbf' size 20m uniform size 128k;
- 使用表空间
--将新表存放到表中间sp01中 create table myemp(empno number(4),ename vachar2(10),sal number) tablespace sp01;
- 改变表空间的状态
当建立表空间的时，表空间处于联机状态（online）。联机状态下允许进行访问和读写等操作，但在某些时候如系统维护或数据维护时需要改变表空间的状态（需要特权用户进行操作）：
1）脱机状态offline
alter tablespace 表空间名 offline2）联机状态
alter tablespace 表空间名 online3）只读状态
alter tablespace 表空间名 read only
- 删除表空间
drop tablespace 表空间名 including contents and datafiles;
contents和datafiles分别表示删除表时将所有数据库对象和数据库文件也删除。 - 扩展表空间
表空间是由数据文件组成的，表空间的实际大小就是数据文件占用的大小，那么如果文件大小超过了表空间大小初始值那么继续插入数据就会报错，所以需要进行表空间扩展，一般有三种方法：
1）增加数据文件
2）增加文件的大小（单个文件一般不要超过500m）alter tablespace 表空间名 add datafile 'd:\test\sp02.dbf' size 20m;
3）设置文件的自动增长alter tablespace 表空间名 datafile 'd:\test\sp01.dbf' resize 50m;
--空间不足时自动增加10m，最大空间500m alter tablespace 空间名 datafile 'd:\test\sp01.dbf' autoextend on next 10m maxsize 500m;
- 移动数据文件
有时磁盘的损坏导致数据文件无法再继续使用，为了能够继续使用需要将这些数据文件副本进行转移到其他磁盘，然后进行数据恢复。
下面以移动数据文件sp01.dbf进行说明：
1）确定数据文件所在的表空间
select tablespace_name from dba_data_files where filename='d:\test\sp01.dbf'
2）使表空间脱机：为了保证数据一致性。
3）使数据文件移动到指定的目录alter tablespace sp01 offline;
host move 'd:\test\sp01.dbf' 'c:\test\sp01.dbf'
4）执行alter tablespace 命令
在物理迁移后，为了使用户能访问到该数据文件，需要对数据文件进行逻辑上的修改：alter tablespace sp01 rename 'd:\test\sp01.dbf' to 'c:\test\sp01.dbf'
5）如果需要访问该表空间，需要将表空间联机
alter tablespace sp01 online
