Oracle表空间、用户详解

新建连接

工具选择：
我们一般会选择一个工具来连接本地的Oracle，而我这里选择的是Navicat 15 —— 。

---

开启服务：
为了Navicat能连接上Oracle，我们需要本地开启Oracle服务（因为Oracle很占内存，所以一般不会设置自动启动）。

wins键——服务——开启服务

只要把这几个启动了，就可以连接了（我也不清楚哪几个是必须的，如果还是连接不了，就把所有的Oracle服务都启动就没问题了）。

---

新建连接:

• 我们先登录 system 用户，因为它拥有普通dba角色权限

  Oracle用户权限说明： http://www.kaotop.com/it/171606.html

• 注意1： 如果你之前已经建立了一个Oracle连接，然后你想直接修改连接的用户来登录其他用户，那么其会报错：

所以，请新建一个连接。
1

• 注意2： 有些小伙伴就要问了，凭什么非要用 System 登录呢，是看不起我们的 SCOTT 或者 SH 吗？
  因为它们没有权限呀：

  select * from user_sys_privs;
  1

  

  Oracle数据库权限一览表： https://blog.csdn.net/oYinHeZhiGuang/article/details/104718403

  如果直接用 SCOTT 创建表空间或者用户，会报错：ORA-01031: 权限不足

三者关系

先来说明一下表空间、用户和表的关系，其关系如下：

一个表空间下面可以有多个用户，而一个用户下面可以有多张表。

• 表空间：数据库数据的物理存储空间
  那些后缀名为 .DBF 的文件就是表空间
  
• 用户：可以通过用户操作数据库（前提是该用户有相应权限）
  创建用户必须为其指定表空间，如果没有显性指定表空间，则默认指定为 USERS 表空间
• 表：数据记录的集合
  

通过对这三者的关系分析，可知道创建流程：创建表空间 → 创建用户 → 创建表。

表空间

表空间与数据库的关系：

---

创建表空间

创建表空间： 创建一个初始大小为20MB，每次扩容4M，最大容量为40MB的表空间。

注意： 在创建表空间的时候，一定要指定数据文件路径 。

-- 创建表空间 HIPAY
create tablespace hipay
-- 指定表空间属下的数据文件，初始化容量为 20MB
datafile 'D:\Applications\orcl\oradata\orcl\TEST.dbf' size 20m
-- 设置数据文件为自动扩容，每次扩容为4MB，最大容量为40MB
autoextend on next 4m maxsize 40m
1
2
3
4
5
6

• Oracle大小写敏感，且默认会将表空间名、用户名、表名、字段名转换为大写
• 如果不想名称转换为大写，可以在创建的时候，给名称加一个 "" 包起来，查询的时候用''包起来
• 指定表空间的数据文件，可以指定已经存在的数据文件，也可以指定一个不存在的，然后Oracle会自动创建一个
• 在指定表空间的数据文件的时候，可以指定多个数据文件

查看表空间：

SELECT
	t.tablespace_name,-- 表空间
	t.file_name,-- 文件名
	t.autoextensible,-- 是否自增
	t.bytes / 1024 / 1024 "SIZE(M)",-- 初始值
	t.increment_by * 8 / 1024 "NEXT(M)",-- 步长 1blok = 8KB
	t.maxbytes / 1024 / 1024 "MAXSIZE(M)" -- 最大值
	
FROM
	dba_data_files t 
WHERE
	t.tablespace_name = 'HIPAY';
1
2
3
4
5
6
7
8
9
10
11
12

创建表空间官方文档： https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/CREATE-TABLESPACE.html

修改表空间和数据文件

修改数据文件容量

• 修改数据文件大小：
  修改数据文件初始大小为 10MB

  ALTER database datafile 'D:\Applications\orcl\oradata\orcl\TEST.dbf' resize 10m;
  1

• 修改数据文件自动扩容：
  每次扩容10MB，最大容量为500MB，可以把500m换成 unlimited，即无上限

  ALTER database datafile 'D:\Applications\orcl\oradata\orcl\TEST.dbf' autoextend ON NEXT 10m maxsize 500m;
  1

---

新增表空间的数据文件

给 HIPAY 新增一个容量为 20MB 的数据文件。

ALTER tablespace "HIPAY" ADD datafile 'D:\Applications\orcl\oradata\orcl\TEST01.dbf' SIZE 20M;
1

---

重命名数据文件

ALTER tablespace HIPAY RENAME datafile 'D:\Applications\orcl\oradata\orcl\TEST01.dbf' TO 'D:\Applications\orcl\oradata\orcl\TEST02.dbf';
1

---

修改表空间状态

• 离线状态：

  ALTER tablespace HIPAY OFFLINE;
  1

• 在线状态：

  ALTER tablespace HIPAY ONLINE;
  1

  oracle表空间offline的三种方式区别： https://blog.51cto.com/jiujian/1033891

• 只读状态：

  ALTER tablespace HIPAY read ONLY;
  1

• 可读写状态：

  ALTER tablespace HIPAY read write;
  1

注意：离线状态不可设置只读，只有在只读状态才可修改为可读写，因为默认可读写。

---

修改数据文件状态

• 离线状态：
  设置数据文件为离线状态需要先设置表空间为离线状态

  ALTER tablespace HIPAY OFFLINE;
  ALTER database datafile 'D:\Applications\orcl\oradata\orcl\TEST.dbf' OFFLINE;
  1
  2

• 在线状态：

  ALTER database datafile 'D:\Applications\orcl\oradata\orcl\TEST.dbf' ONLINE;
  1

• 删除数据文件：

  ALTER database datafile 'D:\Applications\orcl\oradata\orcl\TEST.dbf' OFFLINE DROP;
  1

  

删除表空间

• 脱机（表空间为空）

  DROP tablespace hipay;
  1

• 脱机（表空间有数据）

  DROP tablespace hipay including contents;
  1

• 完全删除（表空间与数据文件）

  DROP tablespace hipay including contents and datafiles;
  1

查询

• 查询数据文件

  SELECT
  	* 
  FROM
  	dba_data_files;
  1
  2
  3
  4

• 查询表空间

  SELECT
  	* 
  FROM
  	dba_tablespaces;
  SELECT
  	* 
  FROM
  	dba_free_space;
  1
  2
  3
  4
  5
  6
  7
  8

• 查看表空间的使用情况

  WITH temp_data_files AS ( SELECT ddf.tablespace_name, sum( bytes ) total FROM dba_data_files ddf GROUP BY ddf.tablespace_name ),
  temp_free_space AS ( SELECT dfs.tablespace_name, sum( bytes ) free FROM dba_free_space dfs GROUP BY dfs.tablespace_name ) SELECT
  dt.tablespace_name 表空间名称,
  dt.contents 类型,
  ( tdf.total / 1024 / 1024 ) "总大小(M)",
  ( tfs.free / 1024 / 1024 ) "空闲(M)",
  round( ( tdf.total - tfs.free ) / 1024 / 1024, 2 ) "已使用(M)",
  round( ( tdf.total - tfs.free ) / tdf.total * 100, 2 ) "占比(%)" 
  FROM
  	dba_tablespaces dt,
  	temp_data_files tdf,
  	temp_free_space tfs 
  	
  WHERE
  	tdf.tablespace_name = dt.tablespace_name 
  	AND tfs.tablespace_name = dt.tablespace_name;
  1
  2
  3
  4
  5
  6
  7
  8
  9
  10
  11
  12
  13
  14
  15
  16

  

用户

创建

用户的创建依赖于表空间，所以在创建用户的时候需要指定表空间（默认为 USERS）：

-- 创建test用户，密码为123456
CREATE USER test IDENTIFIED BY 123456
-- 默认表空间
DEFAULT tablespace hipay
-- 临时表空间
temporary tablespace temp
-- 表空间额度
quota unlimited ON hipay;

-- 授权（可以登录）
GRANT CREATE SESSION TO test;

## 查询
1
2
3
4
5
6
7
8
9
10
11
12
13

删除

DROP USER test cascade;
1

查询

查看用户：

SELECT
	* 
FROM
	dba_users t 
WHERE
	t.username = 'TEST';
1
2
3
4
5
6

---

查看当前用户的的权限：

SELECT
	* 
FROM
	user_role_privs;
	
SELECT
	* 
FROM
	session_privs
1
2
3
4
5
6
7
8
9

---

查看当前用户的系统权限和表级权限：

SELECT
	* 
FROM
	user_sys_privs;

SELECT
	* 
FROM
	user_tab_privs;
1
2
3
4
5
6
7
8
9

---

查看所有用户：

SELECT
	* 
FROM
	dba_users;
SELECT
	* 
FROM
	all_users;
SELECT
	* 
FROM
	user_users;
1
2
3
4
5
6
7
8
9
10
11
12

修改

修改密码：

ALTER USER test IDENTIFIED BY 234556;
1

设置权限：

GRANT CONNECT,
RESOURCE TO test;
1
2

撤销权限：

REVOKE CONNECT,
RESOURCE FROM test;
1
2

Oracle数据库权限一览表： https://blog.csdn.net/oYinHeZhiGuang/article/details/104718403