表空间
什么是表空间：Oracle中的用户都有属于自己的默认的表空间，在一段内存空间中存储的大部分是表，所以称为表空间。
用户的表空间
1,系统用户的表空间
2,普通用户的表空间
为什么要给普通用户创建属于自己的表空间那？
项目中很可能与其他项目使用同一个数据库，
多个用户在使用同一个数据库的时候有可能访问同一个数据库文件，
就会造成资源争用问题，给不同的用户指定不同的表空间，就可以让他们
使用不同的数据文件，解决争用问题。
用户有一个缺省的表空间，但是该用户还可以使用其他的表空间，如果我们在创建对象时不指定表空间，则对象存储在缺省表空间中，要想让对象存储在其他表空间中，我们需要在创建对象时指定该对象的表空间
一、创建表空间语法及给用户表空间
表空间分类图
前提打开cmd输入,进入sqlplus和连接数据库及登录sys。
登录sys：
sqlplus sys/123456@ZhengJiaAo:1521/ORCL as sysdba
0.查看管理员用户使用的表空间名称列表
status状态（只读READ ONLY / 读写ONLINE）
SQL> select tablespace_name,status from dba_tablespaces;
SYSTEM ONLINE #系统表空间，是永久系统表空间，用于存储SYS用户的表、视图、存储过程对象。
SYSAUX ONLINE #作为EXAMPLE的辅助表空间
UNDOTBS1 ONLINE #存储撤销信息的undo表空间
TEMP ONLINE #临时表空间，用户存储SQL语句处理的表示索引信息
USERS ONLINE #永久表空间，存储数据库用户创建的数据库对象
EXAMPLE ONLINE #安装Oracle数据库示例的表空间
1.创建临时表空间: duke_temp
先创建好路径 I:\Orcl\tablespace
CREATE TEMPORARY TABLESPACE duke_temp /* 临时表空间名称:duke_temp */
tempfile 'I:\Orcl\tablespace\duke_temp.dbf' /* 临时表空间路径 *//*--oracle文件路径*/
size 50m /* 空间初始大小：50M*/
autoextend ON /* 开启自动扩展 */
NEXT 50m /* 空间满了一次扩展50M*/
maxsize 6G /* 空间最终大小6G,可设为 unlimited 大小不受限制*/
extent management local;
表空间已创建。
SQL> select tablespace_name,status from dba_tablespaces;
TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
USERS ONLINE
EXAMPLE ONLINE
EBANK_TEMP ONLINE
ECIF_DATA ONLINE
ECIF_INDEX ONLINE
EIP_DATA ONLINE
YANG ONLINE
TABLESPACE_NAME STATUS
------------------------------ ---------
DUKE_TEMP ONLINE /*新创建的临时表空间*/
已选择12行。
2.创建数据表空间: duke_data
CREATE TABLESPACE duke_data /* 数据表空间名称:duke_data */
datafile 'I:\Orcl\tablespace\duke_data.dbf' /* 数据表空间路径 */
size 50m /* 表空间初始大小：50M */
autoextend ON /* 开启自动扩展*/
NEXT 50m /* 表空间满了后一次扩展50M */
maxsize 10G /* 空间最终大小10G,可设为 unlimited 大小不受限制*/
logging extent management local;
表空间已创建。
SQL> select tablespace_name,status from dba_tablespaces;
TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
USERS ONLINE
EXAMPLE ONLINE
EBANK_TEMP ONLINE
ECIF_DATA ONLINE
ECIF_INDEX ONLINE
EIP_DATA ONLINE
YANG ONLINE
TABLESPACE_NAME STATUS
------------------------------ ---------
DUKE_TEMP ONLINE /*新创建的临时表空间*/
DUKE_DATA ONLINE /*新创建的数据表空间*/
已选择13行。
3.创建用户并指定表空间
CREATE USER duke /* 用户名 */
IDENTIFIED BY duke /* 密码 */
DEFAULT TABLESPACE duke_data /* 表空间 */ /*给用户指定默认表空间*/
TEMPORARY TABLESPACE duke_temp /* 临时表空间 */ /*默认临时表空间*/
;
用户已创建。
4.修改用户默认和临时表空间
为了方便以后数据的备份和恢复，仅仅将有价值的数据做备份可以提高效率和节省空间。
修改用户默认表空间：
ALTER USER duke DEFAULT TABLESPACE USERS; /*duke 用户名，USERS 默认（DEFAULT）表空间*/
用户已更改。
修改用户临时表空间：
ALTER USER duke temporary TABLESPACE TEMP; /*修改临时（temporary）表空间*/
注意：普通用户没有修改默认表空间的权限，但是可以通过授权来实现普通用户也能够修改默认表空间。
SQL> show user;
USER 为 "SYS"
SQL> select DEFAULT_TABLESPACE,TEMPORARY_TABLESPACE from dba_users where username='SYSTEM';
DEFAULT_TABLESPACE TEMPORARY_TABLESPACE
------------------------------ ------------------------------
SYSTEM TEMP
5.删除空间表
----删除非空表空间，包含物理文件
DROP TABLESPACE duke_data INCLUDING CONTENTS AND DATAFILES;
表空间已经删除。
删除表空间后，原先指向该表空间的用户仍然默认的空间位置，
需要通过alter user 命令将用户的表空间指向一个有效的表空间。
删除表空间方法总结：
--删除空的表空间，但是不包含物理文件
drop tablespace duke_temp
--删除非空表空间，但是不包含物理文件
drop tablespace duke_temp including contents;
--删除空表空间，包含物理文件
drop tablespace duke_temp including datafiles;
--删除非空表空间，包含物理文件
DROP TABLESPACE duke_data INCLUDING CONTENTS AND DATAFILES;
--如果其他表空间中的表有外键等约束关联到了本表空间中的表的字段，就要加上CASCADE CONSTRAINTS
drop tablespace duke_temp including contents and datafiles CASCADE CONSTRAINTS;
--说明： 删除了duke，只是删除了该duke下的schema objects，是不会删除相应的表空间（tablespace）的。
drop user duke cascade； --duke用户
6.给用户授权登录和撤销权限
新创建的用户是不能登录的，需授权才能登录oracle数据库，授予两个系统内置角色权限，connect和resource。
conncet角色使用户可以登录; resource角色使得用户可以程序开发，如创建表，触发器等。
授权命令：
GRANT CONNECT,RESOURCE TO duke; /* duke用户名 --授权为登录oracle权限，具有创建，修改等基本权限*/
grant dba to duke; /*授权dba最高权限*/
登录用户dike
SQL> connect duke/duke
SQL> show user;
USER is "DUKE"
SQL>
7.查看表空间使用情况
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
8.查看普通用户属于哪个表空间
SQL> select username,default_tablespace from dba_users where username='SCOTT'; /*用户名*/
USERNAME DEFAULT_TABLESPACE
------------------------------ ------------------------------
SCOTT USERS
9.查看所有表空间
SQL> select * from v$tablespace; /*包含从控制文件中获取的表空间名称和编号信息*/
TS# NAME INC BIG FLA ENC
---------- ------------------------------ --- --- --- ---
0 SYSTEM YES NO YES
1 SYSAUX YES NO YES
2 UNDOTBS1 YES NO YES
4 USERS YES NO YES
3 TEMP NO NO YES
6 EXAMPLE YES NO YES
8 EBANK_TEMP NO NO YES
9 ECIF_DATA YES NO YES
10 ECIF_INDEX YES NO YES
11 EIP_DATA YES NO YES
13 YANG YES NO YES
TS# NAME INC BIG FLA ENC
---------- ------------------------------ --- --- --- ---
14 DUKE_TEMP NO NO YES
15 DUKE_DATA YES NO YES
已选择13行。
10.查看表空间下所有用户
SQL> select distinct s.owner from dba_segments s where s.tablespace_name ='USERS'; /*USERS表空间下所有用户*/
OWNER
------------------------------
SCOTT
SYSTEM
OE
11.查看当前用户的表属于哪个表空间
SQL> select table_name,tablespace_name from user_all_tables; /*查看当前用户的表属于哪些表空间*/
TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
MLOG$ SYSTEM
SLOG$ SYSTEM
RGROUP$ SYSTEM
RGCHILD$ SYSTEM
SYS_IOT_OVER_5140 SYSAUX
RULE_SET_PR$
SYS_IOT_OVER_5146 SYSAUX
RULE_SET_IOT$
SYS_IOT_OVER_5150 SYSAUX
RULE_SET_ROP$
SYS_IOT_OVER_5387 SYSAUX
12.查看用户的默认表空间和临时表空间
查看SYSTEM用户的默认表空间和临时表空间
SQL> select DEFAULT_TABLESPACE,TEMPORARY_TABLESPACE from dba_users where username='SYSTEM';
DEFAULT_TABLESPACE TEMPORARY_TABLESPACE
------------------------------ ------------------------------
SYSTEM TEMP
默认表空间
用户在登陆后创建数据库对象时，如果没有指定表空间，那么这些数据就会存储到默认表空间。
13.系统表空间dba_tablespaces和用户表空间user_tablespaces
普通用户不能查看系统表空间dba_tablespaces，只能查看用户表空间user_tablespaces
SQL> connect duke/duke
已连接。
SQL> show user;
USER 为 "DUKE"
SQL> select tablespace_name from dba_tablespaces;
select tablespace_name from dba_tablespaces
*
第 1 行出现错误:
ORA-00942: 表或视图不存在
SQL> select tablespace_name from user_tablespaces;
TABLESPACE_NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
TEMP
USERS
EXAMPLE
EBANK_TEMP
ECIF_DATA
ECIF_INDEX
EIP_DATA
YANG
TABLESPACE_NAME
------------------------------
DUKE_TEMP
DUKE_DATA
已选择13行。
14.查看表空间数据文件的存放路径：
表空间数据文件都存放在永久性表空间中
SQL> desc dba_data_files; /*数据字典dba_data_file用于存放数据文件的属性。*/
名称 是否为空? 类型
----------------------------------------- -------- ----------------------------
FILE_NAME VARCHAR2(513)
FILE_ID NUMBER
TABLESPACE_NAME VARCHAR2(30)
BYTES NUMBER
BLOCKS NUMBER
STATUS VARCHAR2(9)
RELATIVE_FNO NUMBER
AUTOEXTENSIBLE VARCHAR2(3)
MAXBYTES NUMBER
MAXBLOCKS NUMBER
INCREMENT_BY NUMBER
USER_BYTES NUMBER
USER_BLOCKS NUMBER
ONLINE_STATUS VARCHAR2(7)
SQL> select file_name from dba_data_files where tablespace_name='DUKE_DATA';
FILE_NAME
--------------------------------------------------------------------------------
I:\ORCL\TABLESPACE\DUKE_DATA.DBF /*文件存放的位置*/
15.修改表空间的状态(联机/脱机、只读/读写)
设置表空间的联机ONLINE或脱机状态OFFLINE：表空间的默认状态是联机状态，如果表空间是脱机状态，不能够使用这个表空间。
ALTER TABLESPACE tablespace_name ONLINE|OFFLINE;
例如：DUKE_DATA表空间脱机，查看表空间状态
SQL> ALTER TABLESPACE duke_data OFFLINE; /*DUKE_DATA表空间脱机,需要使用大些字母*/
表空间已更改。
SQL> select STATUS from dba_tablespaces where tablespace_name='DUKE_DATA'; /*查看表空间状态*/
STATUS
---------
OFFLINE
设置表空间的只读|可读写状态：表空间默认为可读写状态read write
ALTER TABLESPACE duke_data read only|read write;
注意：如果表空间状态中拥有可read，那么表空间就必须是联机状态ONLINE的。
SQL> ALTER TABLESPACE duke_data read only;
ALTER TABLESPACE duke_data read only
*
第 1 行出现错误:
ORA-01539: 表空间 'DUKE_DATA' 未联机
表空间联机后更改状态为只读
SQL> ALTER TABLESPACE duke_data ONLINE;
表空间已更改。
SQL> select STATUS from dba_tablespaces where tablespace_name='DUKE_DATA';/*查案状态*/
STATUS
---------
ONLINE
SQL> ALTER TABLESPACE duke_data read only;
表空间已更改。
select tablespace_name,status from dba_tablespaces where tablespace_name='DUKE_DATA'; /*查看DUKE_DATA表空间状态，读写（ONLINE）,DUKE_DATA要大写*/
16.修改表空间的数据文件
注意：如果表空间添加数据文件，那么表空间就必须是联机状态ONLINE的。
增加表空间中的数据文件 xx.dbf
SQL> select STATUS from dba_tablespaces where tablespace_name='DUKE_DATA'; /*查看状态是否联机*/
STATUS
---------
ONLINE
SQL> ALTER TABLESPACE duke_data ADD DATAFILE'I:\Orcl\tablespace\duke_data2.dbf' SIZE 50m; /*增加数据文件*/
表空间已更改。
SQL> select file_name from dba_data_fileS where tablespace_name='DUKE_DATA';
/*查看数据文件及文件位置*/
FILE_NAME
--------------------------------------------------------------------------------
I:\ORCL\TABLESPACE\DUKE_DATA.DBF
I:\ORCL\TABLESPACE\DUKE_DATA2.DBF
注意：如果发现某个表空间存储空间不足时，可以为表空间添加新的数据文件，扩展表空间大小
SQL> ALTER TABLESPACE DUKE_DATA ADD DATAFILE 'I:\ORCL\TABLESPACE\DUKE_DATA03.dbf' SIZE 2G AUTOEXTEND OFF; /*方法一：增加新的大数据文件，达到增大表空间的目的*/
表空间已更改。
SQL> ALTER TABLESPACE DUKE_DATA /*方法二：增加数据文件*/
2 ADD DATAFILE 'I:\ORCL\TABLESPACE\DUKE_DATA04.dbf'
3 SIZE 100M
4 AUTOEXTEND ON
5 NEXT 10M
6 MAXSIZE 20480M;
表空间已更改。
注意：在添加新的数据文件时，如果同名的操作系统已经存在，ALTER TABLESPACE语句将失败。如果要覆盖同名的操作系统文件时，则必须在后面显示的指定REUSE子句。
- 调整数据文件的大小
SQL> ALTER DATABASE DATAFILE 'I:\ORCL\TABLESPACE\DUKE_DATA04.dbf' RESIZE 500M;
数据库已更改。
- 删除数据文件
注意：不能够删除表空间中的第一个数据文件，如果将第一个数据文件删除的话，相当于删除了整个表空间。
SQL> ALTER TABLESPACE DUKE_DATA DROP DATAFILE'I:\ORCL\TABLESPACE\DUKE_DATA04.dbf';
/*删除方式一*/
表空间已更改。
数据文件图
SQL> alter tablespace DUKE_DATA drop datafile 'I:\ORCL\TABLESPACE\DUKE_DATA03.dbf';
/*删除方式二*/
表空间已更改。
查看删除文件后，duke_data表空间的数据文件
SQL> select file_name from dba_data_fileS where tablespace_name='DUKE_DATA';
FILE_NAME
--------------------------------------------------------------------------------
I:\ORCL\TABLESPACE\DUKE_DATA.DBF
I:\ORCL\TABLESPACE\DUKE_DATA2.DBF
17.删除表空间
基本语法：
DROP TABLESPAC tablespace_name [INCLUDING CONTENTS];
注意：如果你希望在删除表空间的同时将表空间中的数据文件一同删除时，需要加上[INCLUDING CONTENTS]
drop tablespace DUKE_DATA including contents;
/*删除表空间*/
表空间已更改。
select file_name from dba_data_fileS where tablespace_name='DUKE_DATA';
/*查看表空间*/
没有此DUKE_DATA表空间。
18.用户表空间限额
表空间存储限制是用户在某一个表空间中可以使用的存储空间总数。
在创建或修改用户时，可以由参数quota指出。若用户在向表空间存储数据时，超出了此限额，则会产生错误。
错误信息：ORA-01536:space quota exceeded for tablespace tablespacename..’。
可以通过查询字典dba_ts_quotas查看表空间限额信息。
查看用户的表空间配额
查看所有的用户表空间配额情况
SQL> SELECT * FROM DBA_TS_QUOTAS
2 ;
TABLESPACE_NAME USERNAME BYTES
------------------------------ ------------------------------ ----------
MAX_BYTES BLOCKS MAX_BLOCKS DRO
---------- ---------- ---------- ---
SYSAUX APPQOSSYS 0
-1 0 -1 NO
SYSAUX FLOWS_FILES 0
-1 0 -1 NO
SYSAUX SYSMAN 160038912
-1 19536 -1 NO
TABLESPACE_NAME USERNAME BYTES
------------------------------ ------------------------------ ----------
MAX_BYTES BLOCKS MAX_BLOCKS DRO
---------- ---------- ---------- ---
SYSAUX OLAPSYS 4718592
-1 576 -1 NO
查看当前用户表空间的配额情况
#查看当前用户表空间的配额情况
SELECT * FROM USER_TS_QUOTAS;
SQL> DESC DBA_TS_QUOTAS;
名称 是否为空? 类型
----------------------------------------- -------- ----------------------------
TABLESPACE_NAME NOT NULL VARCHAR2(30)
USERNAME NOT NULL VARCHAR2(30)
BYTES NUMBER
MAX_BYTES NUMBER
BLOCKS NUMBER
MAX_BLOCKS NUMBER
DROPPED VARCHAR2(3)
注意：若MAX_BYTES=-1表示没有配额限制
三、oralce 数据库设置表空间的只读/读写模式
意义：主要是为了确保数据完整性。
如:数据备份与还原操作、历史数据的完整性保护。可以将表空间设置只读模式。
只读：是表空间中不能进行INSERT（插入）、UPDATE（修改）、DELETE（删除）等操作。
需要dba权限的用户登录才可以进行一下操作：
1.打开cmd输入,进入sqlplus和连接数据库及登录sys。
sqlplus sys/123456@ZhengJiaAo:1521/ORCL as sysdba
SQL> select tablespace_name,status from dba_tablespaces; //查看所有表空间状态，读写（ONLINE）
TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
USERS ONLINE
EXAMPLE ONLINE
EBANK_TEMP ONLINE
ECIF_DATA ONLINE
ECIF_INDEX ONLINE
EIP_DATA ONLINE
YANG ONLINE
已选择11行。
2.更改表空间状态：只读（READ ONLY）。
SQL> alter tablespace users read only;
表空间已更改。
SQL> select tablespace_name,status from dba_tablespaces;
TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
USERS READ ONLY
EXAMPLE ONLINE
EBANK_TEMP ONLINE
ECIF_DATA ONLINE
ECIF_INDEX ONLINE
EIP_DATA ONLINE
YANG ONLINE
已选择11行。
3.更改表空间状态为：读写（ONLINE）。
SQL> alter tablespace users read write;
表空间已更改。
SQL> select tablespace_name,status from dba_tablespaces;
TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
USERS ONLINE
EXAMPLE ONLINE
EBANK_TEMP ONLINE
ECIF_DATA ONLINE
ECIF_INDEX ONLINE
EIP_DATA ONLINE
YANG ONLINE
已选择11行。
四、Schema和用户
Oracle的方案（Schema）和用户（User）的区别
Schema定义：
A schema is a collection of database objects (used by a user).
Schema objects are the logical structures that directly refer to the database’s data.
A user is a name defined in the database that can connect to and access objects。
Schemas and users help database administrators manage database security
模式是数据库对象（由用户使用）的集合。
Schema对象是直接引用数据库数据的逻辑结构。
用户是数据库中定义的可以连接和访问对象的名称。
模式和用户帮助数据库管理员管理数据库安全性
从定义中我们可以看出schema为数据库对象的集合，为了区分各个集合，我们需要给这个集合起个名字，这些名字就是我们在企业管理器的方案下看到的许多类似用户名的节点，这些类似用户名的节点其实就是一个schema，schema里面包含了各种对象如tables, views, sequences, stored procedures, synonyms, indexes, clusters, and database links.
1.一个用户一般对应一个schema：
一个用户一般对应一个schema，该用户的schema名等于用户名，并作为该用户缺省schema。这也就是在企业管理器的方案下看到schema名都为数据库用户名的原因。
数据库Schema有两种含义:一种是概念上的Schema，指的是一组DDL语句集，该语句集完整地描述了数据库的结构。还有一种是物理上的 Schema，指的是数据库中的一个名字空间，它包含一组表、视图和存储过程等命名对象。物理Schema可以通过标准SQL语句来创建、更新和修改。例 如以下SQL语句创建了两个物理Schema：
create schema SCHEMA_A;
create table SCHEMA_A.CUSTOMERS(ID int not null,……);
create schema SCHEMA_B;
create table SCHEMA_B.CUSTOMERS(ID int not null,……);
简单的说：就是一个数据库用户所拥有的数据库的对象。
比如scott用户建立了表，索引，视图，存储过程等对象，那么这些对象就构成了schema scott
Oracle数据库中不能新创建一个schema，要想创建一个schema，只能通过创建一个用户的方法解决(Oracle中虽然有create schema语句，但是它并不是用来创建一个schema的)，在创建一个用户的同时为这个用户创建一个与用户名同名的schem并作为该用户的缺省shcema.
即schema的个数同user的个数相同，而且schema名字同user名字一一对应并且相同，所有我们可以称schema为user的别名，虽然这样说并不准确，但是更容易理解一些。
2.一个用户还可以使用其他的schema:
访问一个表时，没有指明该表属于哪一个schema中的，系统就会自动给我们在表上加上缺省的sheman名.
访问scott用户下的emp表，通过select * from emp; 其实，这sql语句的完整写法为select * from scott.emp;
在数据库中一个对象的完整名称为schema.object，而不属user.object.
3.oracle中的schema就是指一个用户下所有对象的集合:
schema本身不能理解成一个对象，oracle并没有提供创建schema的语法，schema也并不是在创建user时就创建，而是在该用户下创建第一个对象之后schema也随之产生，只要user下存在对象，schema就一定存在，user下如果不存在对象，schema也不存在；这一点类似于temp tablespace group，另外也可以通过oem来观察，如果创建一个新用户，该用户下如果没有对象则schema不存在，如果创建一个对象则和用户同名的schema也随之产生。
Schema和用户的关系图
网友评论