{aa31aa} 本文大纲
对初学者和学习能力相对较差的人而言,深刻理解和熟练使用 Oracle 数据库是非常困难的一件事,因为 Oracle 从理论到实际都非常复杂。本人见过许多开发人员装 Oracle 装的垂头丧气,其中不乏已经工作了四五年的人,但从未见过工作了好多年还装不好 SQL Server 的情况。在我刚开始接触 Oracle 的时候,也曾被它的实例和表空间搞蒙蔽了好一阵子。
本文将要讲述的概念不包括 Oracle 体系结构里的概念。首先 Oracle 体系结构里的 SGA、PGA 等概念本人也不太理解,其次普通开发人员完全没必要搞懂 Oracle 的体系结构。本文主要讲述表空间、SCHEMA、用户、权限,这四个开发人员最常用,也最容易蒙蔽的概念。
表空间是 Oracle 数据库的逻辑划分,据库表、索引等所有的数据库对象都存放在指定的表空间中,但主要存放的是表,所以称作表空间。在物理上表空间由 1 个或者多个数据文件组成,而数据库在逻辑上由 1 个或者多个表空间组成,一个表空间只能属于一个数据库。Oracle 数据库中至少存在一个表空间,即 SYSTEM 表空间。建立 SYSTEM 表空间的目的是尽量将目的相同的表存放在一起,以提高使用效率,只应存放数据字典。
表空间分为 3 类:
表空间的主要作用就是空间资源分配,方便用户操作,有利于磁盘空间管理、I/O 性能、备份和恢复数据等。
SCHEMA(模式 / 方案)应该是开发人员最难理解的概念,因为它既无法直接创建,也无法直接删除,如果不注意,甚至都无法感知它的存在。相信大家看文档时都有见过
、
- scott.emp
之类的描述,不知道大家是否真的理解。反正我有很长时间都以为 scott 和 owner 指的是用户名,但实际上这里的 scott 和 owner 都是指 SCHEMA 名。
- owner.object
SCHEMA 是数据库中对象的集合,为了区分各个集合,我们需要给每个集合都取一个名字。也许是为了方便吧,常见的数据库如 Oracle、SQL Server 等,都会给用户一个缺省的 SCHEMA,且名称与用户名相同。Oracle 中虽然有 CREATE SCHEMA 语句,但是它并不是用来创建 SCHEMA 的,要创建 SCHEMA 只能通过创建用户的方式解决;这样一来用户与 SCHEMA 就一一对应了,所以即使把用户名当成 SCHEMA 名来用一般也不会有问题。
访问数据库对象的时候,如果没有指明该对象属于哪一个 SCHEMA,系统就会自动在对象名前面加上缺省的 SCHEMA 名。在数据库中一个对象的完整名称为 SCHEMA.OBJECT,而不是 USER.OBJECT。类似的如果创建对象时不指定该对象的 SCHEMA,系统也会为该对象指定用户的缺省 SCHEMA。不同的 SCHEMA 之间没有直接的关系,它们的表可以同名,也可以互相引用(但必须有权限),在没有操作别的 SCHEMA 的操作根权下,每个用户只能操作它自己的 SCHEMA 下的所有的对象。
要查询某个数据库,就得先登录那个数据库,登录数据库必然需要用户,这很好理解。要想访问某个数据库对象,也得先知道这个对象所属用户,尽管实际访问对象时用的是 SCHEMA。
有人用房子和主人来类比用户和 SCHEMA,我觉得这个类比不太恰当但的确有助于理解。每个人都有一套房子(估计大城市的异地打工仔大多还没有!),就好比每个用户都有一个缺省的 SCHEMA 一样;要想进入别人的房子就必须先通过房子的主人拿到钥匙,就好比要访问其它 SCHEMA 里的对象就得先取得其它用户的授权一样;房东的存在就好比 DBA 用户。
权限是相对用户来说的,它就相当于房子的钥匙,有了钥匙主人或客人才能进入房子,有的房子里的房间很多,于是钥匙也很多。如果你明知某个 SCHEMA 里面有那么一个表,结果你访问的时候却报 "表或视图不存在",那说明你没有访问这个表的权限。在 Oracle 里,没权限的操作并不总是报这个错,有可能是别的错或不报错,所以如果你对 Oracle 的权限没有一个系统性的认识,有些时候是很难识别具体错误的。
Oracle 中的权限看似繁多(100 多种),但可清晰的分为两类:
创建表空间的完整语法有好几十行,但对于一个普通的开发人员来讲,这简直太麻烦了,好在 Oracle 提供了大量的默认选项,以便快速完成的表空间的创建。
示例一:
- -- 创建一个大小为 20MB 的永久表空间,这也许是创建表空间最简单的语法
- CREATE TABLESPACE ts1 DATAFILE 'ts1.dbf' SIZE 20M;
示例二:
- -- 在指定位置,创建一个大小为 64MB,一次增长 8MB,最大 1024MB 的永久表空间
- CREATE TABLESPACE ts2
- DATAFILE 'F:\oracle\product\10.2.0\oradata\orcl\ts2.dbf' SIZE 64M
- AUTOEXTEND ON NEXT 8M
- MAXSIZE 1024M
- EXTENT MANAGEMENT LOCAL
- SEGMENT SPACE MANAGEMENT AUTO;
- -- 查询数据库中所有的表空间
- SELECT t.TABLESPACE_NAME,t.STATUS FROM SYS.DBA_TABLESPACES t;
- -- 查询数据库用户的表空间
- SELECT t.TABLESPACE_NAME,t.STATUS FROM SYS.USER_TABLESPACES t;
- -- 查询从控制文件中获取的表空间名称和编号
- SELECT * FROM V$TABLESPACE;
- -- 查询表空间和数据文件对应关系
- SELECT t.TABLESPACE_NAME,t.FILE_NAME,t.STATUS FROM SYS.DBA_DATA_FILES t;
- -- 查询临时数据文件以及所属的表空间
- SELECT * FROM DBA_TEMP_FILES;
- -- 查询从控制文件中获取的数据文件的基本信息,包括它所属的表空间名称、编号等
- SELECT * FROM V$DATAFILE;
- -- 查询所有临时数据文件的基本信息
- SELECT * FROM V$TEMPFILE;
- -- 查询当前数据库默认永久表空间和默认临时表空间
- SELECT t.property_name, t.property_value
- FROM sys.database_properties t
- WHERE t.property_name IN ('DEFAULT_PERMANENT_TABLESPACE','DEFAULT_TEMP_TABLESPACE');
- -- 修改当前数据库默认表空间为 ts1
- ALTER DATABASE DEFAULT TABLESPACE ts1;
- -- 修改当前数据库默认表空间为 ts2
- ALTER DATABASE DEFAULT TEMPORARY TABLESPACE ts2;
- -- 将 scott 用户的默认表空间修改为 ts1
- ALTER USER scott DEFAULT TABLESPACE ts1;
- -- 查询"USERS"表空间下所有的表,貌似只能查当前用户的表空间,dba 也查不了其它用户的
- SELECT * FROM sys.user_tables t WHERE t.tablespace_name='USERS'
- -- 将 t_staff_copy 表移动到 ts1 表空间下
- ALTER TABLE t_staff_copy MOVE TABLESPACE ts1;
查看表空间使用情况
SQL1:
- SELECT A.TABLESPACE_NAME AS TABLESPACE_NAME,
- ROUND(A.BYTES/(1024*1024*1024),2) AS "TOTAL(G)" ,
- ROUND(B.BYTES/(1024*1024*1024),2) AS "USED(G)" ,
- ROUND(C.BYTES/(1024*1024*1024),2) AS "FREE(G)" ,
- ROUND((B.BYTES * 100) / A.BYTES,2) AS "% USED" ,
- ROUND((C.BYTES * 100) / A.BYTES,2) AS "% FREE"
- FROM SYS.SM$TS_AVAIL A, SYS.SM$TS_USED B, SYS.SM$TS_FREE C
- WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME
- AND A.TABLESPACE_NAME = C.TABLESPACE_NAME;
SQL2:
- SELECT UPPER(F.TABLESPACE_NAME) AS "表空间名称",
- ROUND(D.AVAILB_BYTES ,2) AS "表空间大小(G)",
- ROUND(D.MAX_BYTES,2) AS "最终表空间大小(G)",
- ROUND((D.AVAILB_BYTES - F.USED_BYTES),2) AS "已使用空间(G)",
- TO_CHAR(ROUND((D.AVAILB_BYTES - F.USED_BYTES) / D.AVAILB_BYTES * 100,
- 2), '999.99') AS "使用比",
- ROUND(F.USED_BYTES, 6) AS "空闲空间(G)",
- F.MAX_BYTES AS "最大块(M)"
- FROM (
- SELECT TABLESPACE_NAME,
- ROUND(SUM(BYTES) / (1024 * 1024 * 1024), 6) USED_BYTES,
- ROUND(MAX(BYTES) / (1024 * 1024 * 1024), 6) MAX_BYTES
- FROM SYS.DBA_FREE_SPACE
- GROUP BY TABLESPACE_NAME) F,
- (SELECT DD.TABLESPACE_NAME,
- ROUND(SUM(DD.BYTES) / (1024 * 1024 * 1024), 6) AVAILB_BYTES,
- ROUND(SUM(DECODE(DD.MAXBYTES, 0, DD.BYTES, DD.MAXBYTES))/(1024*1024*1024),6) MAX_BYTES
- FROM SYS.DBA_DATA_FILES DD
- GROUP BY DD.TABLESPACE_NAME) D
- WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME
- ORDER BY 4 DESC;
查看表空间已经使用的百分比
- SELECT a.tablespace_name,a.bytes/1024/1024 "Sum MB",
- (a.bytes-b.bytes)/1024/1024 "used MB",
- b.bytes/1024/1024 "free MB",
- round(((a.bytes-b.bytes)/a.bytes)*100,2) "percent_used"
- FROM(SELECT tablespace_name,sum(bytes) bytes FROM dba_data_files GROUP BY tablespace_name) a,
- (SELECT tablespace_name,sum(bytes) bytes,max(bytes) largest FROM dba_free_space GROUP BY tablespace_name) b
- WHERE a.tablespace_name=b.tablespace_name ORDER BY ((a.bytes-b.bytes)/a.bytes) DESC;
"Sum MB" 表示表空间所有的数据文件总共在操作系统占用磁盘空间的大小
比如:test 表空间有 2 个数据文件,datafile1 为 300MB,datafile2 为 400MB,那么 test 表空间的
"Sum MB" 就是 700MB
"userd MB" 表示表空间已经使用了多少
"free MB" 表示表空间剩余多少
"percent_user" 表示已经使用的百分比
之所以需要调整表空间大多是因为存储空间不够用了,需要加大表空间,下面给出了 3 种加大表空间的方法。
方法一示例:
- -- 给表空间 ts1 增加数据文件 ts1.1.dbf
- ALTER TABLESPACE ts1 ADD DATAFILE 'F:\oracle\product\10.2.0\oradata\orcl\ts1.1.dbf' SIZE 10M;
- -- 或
- ALTER TABLESPACE ts1 ADD DATAFILE 'F:\oracle\product\10.2.0\oradata\orcl\ts1.2.dbf' SIZE 10M
- AUTOEXTEND ON NEXT 5M MAXSIZE 100M;
方法二示例:
- -- 允许已存在的数据文件自动增长
- ALTER DATABASE DATAFILE 'F:\oracle\product\10.2.0\oradata\orcl\ts1.1.dbf' AUTOEXTEND ON NEXT 5M MAXSIZE 100M;
方法三示例:
- -- 手动改变已存在数据文件的大小
- ALTER DATABASE DATAFILE 'F:\oracle\product\10.2.0\oradata\orcl\ts1.2.dbf' RESIZE 100M;
可通过 SQL 修改表空间的状态。
- ALTER TABLESPACE users READ ONLY; -- 将 users 表空间修改为只读
- ALTER TABLESPACE users READ WRITE; -- 将 users 表空间修改为读写
- ALTER TABLESPACE users OFFLINE; -- 将 users 表空间修改为脱机状态
- ALTER TABLESPACE users ONLINE; -- 将 users 表空间修改为联机状态
除了 SYSTEM 表空间外,数据库中的任何表空间可以删除。删除表空间时,ORACLE 仅仅是在控制文件和数据字典中删除与表空间和数据文件相关的信息。默认情况下,ORACLE 并不会在操作操作系统中删除相应的数据文件,因此在成功执行删除表空间的操作后,需要手动删除该表空间在操作系统中对应的数据文件。如果在删除表空间的同时要删除对应的数据文件,则必须显示的指定 INCLUDING CONTENTS AND DATAFILES 子句。注意: 当前的数据库级的默认表空间不能删除,用户级的可以删除
语法:
- DROP TABLESPACE tablespace_name
- [INCLUDING CONTENTS [{AND DATAFILES/KEEP DATAFILES}]
- [CASCADE CONSTRAINTS]];
示例:
- -- 删除表空间
- DROP TABLESPACE ts1;
- -- 删除表空间、对象及数据文件
- DROP TABLESPACE ts2 INCLUDING CONTENTS AND DATAFILES;
- -- 删除表空间、对象、数据文件及约束
- DROP TABLESPACE ts2 INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS;
创建用户的完整语法也相当的繁琐,同样 Oracle 也提供了大量的默认选项,以便更容易的创建用户。
示例:
- -- 创建一个用户 user1,并设置密码为 user1。注意:密码不能太简单,否则不能创建
- CREATE USER user1 IDENTIFIED BY user1;
- -- 创建一个用户 user2,同时指定默认表空间为 user2。注意:要先创建表空间 user2
- CREATE USER user2 IDENTIFIED BY user2 DEFAULT TABLESPACE user2;
- -- 创建一个用户 user3,同时指定临时表空间为 temp。但一般不需要建立临时表空间和撤销表空间,除非是把它们转移到其它磁盘以提高性能
- CREATE USER user3
- IDENTIFIED BY user3
- DEFAULT TABLESPACE user3
- TEMPORARY TABLESPACE temp;
修改用户密码的语法:
- ALTER USER user_name IDENTIFIED BY new_password;
示例:
- -- 把 user1 用户的密码改为 user2
- ALTER USER user1 IDENTIFIED BY user2;
删除用户的语法:
- DROP USER user_name [CASCADE];
示例:
- DROP USER user1; -- 删除 user1 用户
- DROP USER user2 CASCADE; -- 删除 user2 用户,并删除 user2 拥有数据库对象
对象权限可以控制到列,但查询和删除不能控制到列。
系统权限(进行 DDL 操作的权限):
- GRANT CONNECT TO user3; -- 用户最基本的权限,有这个权限之后就能登录了
- GRANT RESOURCE TO user3; -- 开发人员都需要的权限,创建对象的基本权限
- GRANT DBA TO user3; -- 最高权限,拥有这个权限的用户基本啥都能干
- -- 拥有下面三个授权之后,用户才能对表进行 CRUD 操作
- GRANT CREATE SESSION TO user3; -- 会话权限,有这个权限之后用户才能登录
- GRANT CREATE TABLE TO user3; -- 建表权限,有这个权限之后用户才能创建表
- GRANT UNLIMITED TABLESPACE TO user3; -- 不受限表空间权限,可以对数据库中所有表空间进行操作
对象权限(进行 DML 操作的权限):
- GRANT SELECT ON scott.t_staff TO user3; -- 把对 t_staff 表的查询权限授予 user3
- GRANT ALL ON scott.t_staff TO user3; -- 把对 t_staff 表的增删改查、索引、外键等权限授予 user3
- GRANT UPDATE(staff_name) ON scott.t_staff TO user3; -- 把对 t_staff 表中 staff_name 列的修改权限授予 user3
- GRANT INSERT(staff_name) ON scott.t_staff TO user3; -- 把对 t_staff 表中 staff_name 列的插入权限授予 user3
注意:查询和删除不能控制到列。
撤消权限:
- REVOKE CREATE SESSION FROM user3;
- REVOKE CREATE TABLE FROM user3;
- REVOKE SELECT ON scott.t_staff FROM user3;
- REVOKE ALL ON scott.t_staff FROM user3;
公开权限(比如公开 sys 帐户下的两个权限):
- GRANT CREATE SESSION TO PUBLIC;
- GRANT CREATE ANY TALBE TO PUBLIC;
限制用户:
- ALTER USER user3 ACCOUNT LOCK; -- 锁住用户
- ALTER USER user3 ACCOUNT UNLOCK; -- 解锁用户
- ALTER USER user3 PASSWORD EXPIRE; -- 强制用户口令过期
以下是一些查询系统 / 对象权限的表或视图:
- SELECT * FROM V$SESSION; -- 查询登录过系统的用户
- SELECT * FROM ALL_USERS; -- 查询系统的所有用户
- SELECT * FROM DBA_USERS; -- 查询系统的所有用户
- SELECT * FROM USER_USERS; -- 查询系统的所有用户
- SELECT * FROM ROLE_SYS_PRIVS; -- 角色拥有的系统权限
- SELECT * FROM ROLE_TAB_PRIVS; -- 角色拥有的对象权限
- SELECT * FROM USER_TAB_PRIVS_MADE; -- 查询授出去的对象权限(通常是属主自己查)
- SELECT * FROM USER_TAB_PRIVS_RECD; -- 用户拥有的对象权限
- SELECT * FROM USER_COL_PRIVS_MADE; -- 用户分配出去的列的对象权限
- SELECT * FROM USER_COL_PRIVS_RECDSERS; -- 用户拥有的关于列的对象权限
- SELECT * FROM USER_SYS_PRIVS; -- 用户拥有的系统权限
- SELECT * FROM USER_TAB_PRIVS; -- 用户拥有的对象权限
- SELECT * FROM USER_COL_PRIVS; -- 用户拥有的列权限
- SELECT * FROM USER_ROLE_PRIVS; -- 用户拥有的角色
本文主要介绍了 Oracle 中最基本的四个概念,并详细说明了表空间管理和用户管理的基本 SQL 操作,这些对开发人员来说都不太常用,但又必须了解,因为这些是基础中的基础。
来源: