06-Oracle表空间与用户管理（表空间，用户，备份与恢复，导入导出数据）

CSDN专家-赖老师（软件之家）

已于 2023-03-11 20:56:52 修改

阅读量766

点赞数

分类专栏： Oracle学习笔记文章标签： Oracle表空间 Oracle用户管理 Oracle授权 Oracle导入导出

于 2023-03-08 16:17:54 首次发布

本文链接：https://blog.csdn.net/software7503/article/details/129405021

版权

Oracle学习笔记专栏收录该内容

13 篇文章 2 订阅

订阅专栏

本讲主要内容：

1.表空间管理：表空间的作用，创建，修改，删除及管理；

2.用户管理：创建用户，修改用户，删除用户，修改密码，解锁；

3.用户权限管理：授权，撤销授权

4.数据库的备份与恢复

5.数据库导入导出

表空间

• 数据库由若干表空间组成

• 表空间的集合包含：

表段，存放表数据

索引段，存放索引数据

临时段，排序

回滚段，事务读一致性、回滚

• 查看有哪些表空间

Select * from v$tablespace

• 查看有哪些数据文件

Select * from v$datafile

表空间管理

• 表空间和数据文件的作用：

使用户数据和数据字典 ( 系统表数据 ) 数据相分离。减少字典对象和模式对象在同一个数据文件中的竞争

使不同应用程序的数据相分离。使用户对数据文件的管理更清晰，各个程序的表空间的操作互不影响，即使单个表空间处于脱机也不影响其他的表空间正常运行

将回滚段从用户数据中分离出来，防止了单个磁盘的故障造成的数据的不可恢复

•表空间可以分为两大类：

系统表空间：

• 在创建数据库时一同创建的，用于存放数据字典对象，并包含了系统回滚段。

• 可以存储用户数据，但 oracle 建议一般不用于存放用户数据

非系统表空间：

• 永久表空间：

– 用于存储用户永久数据

• 临时表空间 (temporary tablespace) ：

– 用于存储临时数据，如用户数据进行排序时产生的数据；使用临时表空间在很大程度上提高了用户进行大量数据的排序性能

• 还原表空间 (undo tablepspace) ：

– 提供对回滚段的自动管理

•管理表空间主要包括以下操作：

创建表空间

• Create (temporary|undo) tablespace

更改表空间的存储参数

更改表 Alter tablespace/manage tablespace

空间的状态

删除表空间

• drop tablepace/manage tablespace

查看表空间的信息

• 对表空间中的区的分配和使用有两种管理方式：

数据字典管理：

• 使用数据字典来跟踪表空间的使用情况

本地管理（ 9i 后默认的方式，也是推荐的方式）：

• 使用位图跟踪表空间的使用情况，位图存储于头文件中

•创建表空间

Extend management 子句：区的管理方式

• 本地管理 ( local ) 还是字典管理 ( dictionary )

• 如果是 local 需要指定是自动管理 ( autoallocate ) 表空间大小还是一个指定大小 ( uniform size ) 的区来管理

Segment space management 子句 : 段空间的管理方式

• 自动管理 (auto) 或使用空闲列表进行管理 (manual)

• 用户还需要指定是否启动重做日志文件纪录表空间的数据更改情况（ Logging / nologging ）

datafile 子句 :

• 至少指定一个属于该表空间的数据文件

•创建表空间

CREATE TABLESPACE "APP" 
    LOGGING 
    DATAFILE  'C:\ORACLE\ORADATA\EXAMPLE\APP1.DBF'  SIZE 5M REUSE 
    AUTOEXTEND    ON NEXT  512K MAXSIZE  10M

•创建本地管理的临时表空间

CREATE  temporary TABLESPACE "APPTEMP" 
      TEMPFILE  'C:\ORACLE\ORADATA\EXAMPLE\APPTEMP.DBF'  SIZE 5M 
EXTENT MANAGEMENT  LOCAL
Uniform size 1 M

•创建本地管理的还原表空间

CREATE  undo TABLESPACE “roolback1" 
      DATAFILE  'C:\ORACLE\ORADATA\EXAMPLE\roolback1.DBF'  SIZE 5M

表空间的状态

• 联机：

用户可以正常访问此表空间的数据

又可以将联机状态的表空间设置为只读或可读写：

• 脱机：

此状态的表空间或数据暂时不可用，用户不能访问

用于部分的停止数据库、修复数据库文件、改物理文件名、移动物理文件

表空间脱机

ALTER TABLESPACE "APP" OFFLINE Normal

恢复表空间为联机状态

ALTER TABLESPACE "APP" ONLINE

•更改表空间的状态

SYSTEM 表空间和临时表空间不能被脱机

包含活动的回滚段的表空间也不能被脱机

Normal

• 清除此表空间的所有数据文件在 SGA 中分配的空间，该方式为默认方式，且再联机的时候不需要执行介质恢复

Temporary

• 脱机前需要设置检查点，则再联机的时候需要执行介质恢复

Immediate

• 脱机前不需要设置检查点，则再联机的时候需要执行介质恢复

• 如果数据库运行在 noarchivelog 模式下，则不能立即脱机

• For recover

将数据库中进行 point-in-time 恢复的表空间设置为脱机

•设置表空间为只读，只读表空间不能写数据，可删除数据

ALTER TABLESPACE "APP" READ ONLY

可将只读表空间写到 CD 中，然后修改表空间数据文件的位置

•设置表空间为读/写

ALTER TABLESPACE "APP" READ WRITE

•删除表空间

DROP TABLESPACE “APP” INCLUDING CONTENTS AND DATAFILES
带INCLUDING CONTENTS可删除表空间的对象，不删文件
带AND DATAFILE删除数据文件

•改变表空间数据文件大小

ALTER DATABASE DATAFILE ‘d:\oracle\oradata\study\myapp01.dbf’ 
Resize 50m       --改变大小
ALTER TABLESPACE ‘MYAPP’
		ADD DATAFILE ‘d:\oracle\oradata\study\mypp02.dbf’ size 10m;

•改变表空间数据文件

准备好数据文件

表空间离线

修改数据文件

举例
表空间离线
	Alter tablespace ‘myapp’ offline
修改数据文件名称,在操作系统下改
修改表空间数据文件
	alter tablespace myapp
	rename datafile ’d:\oracle\oradata\study\myapp01.dbf’
	to ’d:\oracle\oradata\study\myapp02.dbf’

脚本创建表空间

CREATE TABLESPACE "STORE" 
    LOGGING 
    DATAFILE 'D:\ORACLE\ORADATA\STUDY\STORE.ora' SIZE 5M EXTENT 
    MANAGEMENT LOCAL

创建用户

• Oracle 是一个多用户的数据库系统，每个试图使用 Oracle 的用户都必须得到一个合法的用户名和口令。

•

• 用户也叫方案，是一组逻辑对象的所有者；

•

• 创建用户时需要指定：

验证方法：连接数据库的合法密码

默认和临时表空间：当用户创建对象时，如果没有特殊指定另一个表空间，将使用默认表空间。临时表空间用来创建临时段。

• 语法：

Create user 用户名 Identified by 密码

default tablespace 默认表空间名称

quota nk on 表空间名字

password expire

Account {lock | unlock}

参数说明：

• quota nk 限制用户在表空间上的使用限额，单位为 KB 或 MB ，如为 unlimited 说明使用表空间不受限制

• password expire 说明用户的密码已经到期失效，登陆的时候要强制用户修改密码

• Account 指锁定或解除用户帐号

举例

•创建用户

CREATE USER "ST" IDENTIFIED BY "123" DEFAULT TABLESPACE "STORE" 
    ACCOUNT UNLOCK;
GRANT "CONNECT" TO "ST";
GRANT "RESOURCE" TO "ST";



修改用户的默认表空间
ALTER USER kong default tablespace=store
修改用户在表空间上的空间使用限额
ALTER USER kong quote 10m ON STORE
修改用户密码
Alter user test identified by test11;

启动两个SQL PLUS：A、B表示

A>conn sys/change_on_install as sysdba
A>create user test identified by test123 default tablespace store;
B>conn test/test123   --出错，没有create session的权限
A>grant create session to test; --授权成功
B>conn test/test123	--连接成功
B>create table ab(a number(5), b varchar2(20));  --权限不足
A>grant create table to test;  --赋给操作权限
B>create table ab(a number(5), b varchar2(20)); --没有表空间store的权限
A>grant unlimited tablespace to test;  --赋给用户表空间资源的操作权限
B>create table ab(a number(5), b varchar2(20)); --创建成功
B>select * from scott.dept;  --表或试图不存在，因为没权限
B>grant select any table to test;  --赋给用户可以查询任何表空间的表的数据
B>select * from scott.dept;  --OK
B>create user test1 identified by test1 --权限不足
A>grant create user to test with admin option; --给test赋给创建用户的权限，	并且test可以将创建用户的权限赋给其他用户
A>grant create session to test with admin option;
B>create user test1 identified by test1  --创建成功
B>conn test1/test1  	--连接失败，没有权限
B>grant create session to test1  --test用户将create session权限赋给test1
B>conn test1/test1	--连接成功

对象权限

数据控制语言为用户提供权限控制命令

用于权限控制的命令有：

GRANT 授予权限

REVOKE 撤销已授予的权限

SQL> GRANT SELECT, UPDATE ON EMP  TO user;

SQL> GRANT SELECT ON EMP TO user WITH GRANT OPTION;

SQL> GRANT UPDATE(SAL, HIREDATE)  ON EMP TO user;

SQL> REVOKE SELECT, UPDATE ON EMP FROM user;

查看用户的权限

• 查看当前用户的角色

SQL>select * from user_role_privs;
查看当前用户的系统权限和表级权限
SQL>select * from user_sys_privs;
SQL>select * from user_tab_privs;

select * from session_privs; //用户连上后

•查看角色权限

select * from dba_sys_privs where grantee in ('CONNECT','RESOURCE');

select * from role_sys_privs where role='角色名';

常用字典信息

•表空间信息表

DBA_TABLESPACE

V$TABLESPACE

•数据文件信息表

DBA_DATA_FILES

V$DATAFILE

•临时文件信息

DBA_TEMP_FILES

V$TEMPFILE

•查看用户及其默认的表空间

Select username,default_tablespace from dba_users

备份与恢复简介

• 备份是数据库中数据的副本，它可以保护数据在出现意外损失时最大限度的恢复

Oracle数据库的备份包括以下两种类型：

故障类型

导致数据库操作中止的故障包括四种类型：

数据库备份

• 在数据库中提供了两种备份数据的方法：物理备份和逻辑备份

• 逻辑备份：备份表、视图、触发器、模式对象。。。

• 物理备份

冷备份

• 停止服务，拷贝物理文件

热备份

• 数据库正常运行情况下备份，数据库需要运行在归档模式

导出和导入实用程序

• 导出和导入实用程序用于实施数据库的逻辑备份和恢复

• 导出实用程序将数据库中的对象定义和数据备份到一个操作系统二进制文件中

• 导入实用程序读取二进制导出文件并将对象和数据载入数据库中

•

• 导入命令

IMP

• 导出命令

EXP

• 在导出自身模式下的对象时不需要什么特殊权限

• 但希望对其他模式下的对象进行操作的时候就必须必须具备 EXP_FULL_DATABASE 、 IMP_FULL_DATABASE 的角色。

• 可以通过 OEM 中提供的工具进行导入 / 导出的操作

• 导出和导入实用程序的特点有：

可以按时间保存表结构和数据

允许导出指定的表，并重新导入到新的数据库中

可以把数据库迁移到另外一台异构服务器上

在两个不同版本的 Oracle 数据库之间传输数据

在联机状态下进行备份和恢复

可以重新组织表的存储结构，减少链接及磁盘碎片

使用以下三种方法调用导出和导入实用程序：

导出和导入数据库对象的四种模式是：

•导出实用程序有以下常用命令参数：

参数	说明
USERID	确定执行导出实用程序的用户名和口令
BUFFER	确定导出数据时所使用的缓冲区大小，其大小用字节表示
FILE	指定导出的二进制文件名称，默认的扩展名是.dmp
FULL	指定是否以全部数据库方式导出，只有授权用户才可使用此参数
OWNER	要导出的数据库用户列表
HELP	指定是否显示帮助消息和参数说明
ROWS	确定是否要导出表中的数据
TABLES	按表方式导出时，指定需导出的表和分区的名称
PARFILE	指定传递给导出实用程序的参数文件名
TABLESPACES	按表空间方式导出时，指定要导出的表空间名

导入实用程序

•导入实用程序有如下常用命令参数：

参数	说明
USERID	指定执行导入的用户名和密码
BUFFER	指定用来读取数据的缓冲区大小，以字节为单位
COMMIT	指定是否在每个数组（其大小由BUFFER参数设置）插入后进行提交
FILE	指定要导入的二进制文件名
FROMUSER	指定要从导出转储文件中导入的用户模式
TOUSER	指定要将对象导入的用户名。FROMUSER与TOUSER可以不同
FULL	指定是否要导入整个导出转储文件
TABLES	指定要导入的表的列表
ROWS	指定是否要导入表中的行
PARFILE	指定传递给导入实用程序的参数文件名，此文件可以包含这里列出的所有参数
IGNORE	导入时是否忽略遇到的错误，默认为N
TABLESPACES	按表空间方式导入，列出要导入的表空间名