oracle表空间创建及管理

浏览数：26 / 时间：2015年06月12日

一、数据文件和数据库逻辑存储结构：

一个表空间包含一个或多个数据文件,一个表空间包含一个或多个段,一个段包含一个或多个区,一个区包含一个或多个连续的数据库块,一个数据库块包含一个或多个操作系统块.
段是对象存储基本单元,段中的区总是在同一个表空间中,可以跨数据文件.
区是空间分配的基本单元,连续的数据块,不能跨数据文件.
块是I/O 的基本单元,不能跨数据文件.

技术分享

表空间的分类
永久表空间存放永久性数据，如表，索引等。
临时表空间不能存放永久性对象，用于保存数据库排序，分组时产生的临时数据。
UNDO表空间保存数据修改前的镜象。

表空间的管理方式：
字典管理：全库所有的空间分配都放在数据字典中。容易引起字典争用，而导致性能问题。
本地管理：空间分配不放在数据字典，而在每个数据文件头部的第3到第8个块的位图块，来管理空间分配。

创建表空间：

--1、创建临时表空间
create temporary tablespace lxj_temp
tempfile ‘I:\testData\lxj_temp.dbf‘
size 100M
autoextend on   --默认为off
next 50M maxsize 2048M;

--2、创建数据表空间
create tablespace lxj_data
datafile ‘I:\testData\lxj_data.dbf‘
size 500M
autoextend on   --默认为off
next 100M maxsize 5000M;

create tablespace test123
datafile ‘I:\testData\test123.dbf‘
size 20M;

表空间创建时，不加参数的默认值：

SQL> select tablespace_name, contents,logging,force_logging,extent_management,allocation_type,segment_space_management,retention,bigfile,encrypted from dba_tablespaces where tablespace_name=‘TEST123‘;
TABLESPACE_NAME CONTENTS LOGGING FORCE_LOGGING EXTENT_MANAGEMENT ALLOCATION_TYPE SEGMENT_SPACE_MANAGEMENT RETENTION BIGFILE ENCRYPTED
------------------------------ --------- --------- ------------- ----------------- --------------- ------------------------ ----------- ------- ---------
TEST123 PERMANENT LOGGING NO LOCAL SYSTEM AUTO NOT APPLY NO NO

4、添加用户，指定默认表空间及授权：

SQL> create user lxj identified by rusky
default tablespace lxj_data
temporary tablespace lxj_tmp;

SQL> grant connect,resource,dba to lxj;

二、表空间基本信息查询
主要是这几张表及视图：dba_tablespaces,user_tablespaces,v$tablespaces,dba_data_files,dba_temp_files,v$tempfile,dba_users,
查看默认表空间：
select * from database_properties WHERE PROPERTY_NAME = ‘DEFAULT_PERMANENT_TABLESPACE‘;
如果创建用户时，不指定表空间，则使用默认的USERS表空间。

查看默认的TEMP表空间：
数据库级别
select property_name,property_value from database_properties where property_name=‘DEFAULT_TEMP_TABLESPACE‘;

用户级别
SELECT USERNAME, DEFAULT_TABLESPACE FROM DBA_USERS;

--包含数据库中所有表空间的描述信息
SELECT * FROM DBA_TABLESPACES

--包含当前用户的表空间的描叙信息
SELECT * FROM USER_TABLESPACES

--包含从控制文件中获取的表空间名称和编号信息
SELECT * FROM V$TABLESPACE;

查看数据文件
--包含数据文件以及所属的表空间的描述信息,查看数据文件是否是自动增长。
SELECT * FROM DBA_DATA_FILES

--包含临时数据文件以及所属的表空间的描述信息
SELECT * FROM DBA_TEMP_FILES

--包含所有临时数据文件的基本信息
SELECT * FROM V$TEMPFILE

查看表空间使用情况：

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;

计算表空间使用情况(考虑了数据文件自动增长情况)

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;

在ORACLE 9i数据库中，创建数据库用户时，如果没有指定默认的永久性表空间，则系统使用SYSTME表空间分别作为该用户的默认永久表空间，默认的临时表空间为TEMP。在ORACLE 10/11g中，如果不指定默认永久性表空间，则是USERS.默认的临时表空间为TEMP，当然前提是你没有修改过默认永久表空间值或指定用户的默认永久性表空间。ORACLE允许使用自定义的表空间作为默认永久性表空间，你可以用下面SQL

查看数据库的默认永久表空间和默认临时表空间

SQL>SELECT * FROM database_properties
WHERE PROPERTY_NAME = ‘DEFAULT_PERMANENT_TABLESPACE‘;

SQL>SELECT * FROM database_properties
WHERE PROPERTY_NAME =‘DEFAULT_PERMANENT_TABLESPACE‘

你可以使用ALTER DATABASE DEFAULT TABLESPACE语句可以设置数据库的默认永久性表空间，这样建立用户时，默认将使用指定的表空间。
数据库级别：
永久表空间
SQL>ALTER DATABASE DEFAULT TABLESPACE USER;
临时表空间
SQL>ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP;
用户级别

SQL>ALTER USER USERNAM DEFAULT TABLESPACE NEW_TABLESPACE_NAME;
查看用户对应的默认表空间
SELECT USERNAME, DEFAULT_TABLESPACE FROM DBA_USERS;

默认表空间不能删除，除非将默认表空间指向其他表空间之后才可以删除。
如果用户的默认表空间指向其他的表空间，当这个表空间被drop 之后，用户的默认表空间会自动指向DB的默认表空间。
删除test表空间：drop tablespace test;

三、表空间数据文件调整

添加数据文件：
ALTER TABLESPACE TBS_TR_IND
ADD DATAFILE ‘/oradata/rTBS_TR_IND_002.dbf‘
SIZE 32G
AUTOEXTEND OFF;

SQL> ALTER TABLESPACE TBS_EDS_DAT
ADD DATAFILE ‘G:\datafile\TBS_EDS_DAT01.DBF‘
SIZE 100M
AUTOEXTEND ON
NEXT 10M
MAXSIZE 20480M;

SQL> ALTER TABLESPACE temp01
ADD TMPFILE ‘D:\ORACLEDATA\temp01_02.dbf‘ SIZE 10M REUSE;

重置数据文件的大小
ALTER DATABASE DATAFILE ‘/database/oracle/oradata/gsp/tbs_dm_data_002.dbf‘
RESIZE 500M;

删除数据文件
ALTER TABLESPACE TEST
DROP DATAFILE ‘/database/oracle/oradata/gsp/tbs_dm_data_002.dbf‘;

删除test表空间：drop tablespace test;--只是逻辑删除
在添加新的数据文件时，如果同名的操作系统已经存在，ALTER TABLESPACE语句将失败。如果要覆盖同名的操作系统文件时，则必须在后面显示的指定REUSE子句。
SQL> create tablespace test
2 datafile ‘I:\testData\test.dbf‘
3 size 20M;
create tablespace test
datafile ‘I:\testData\test.dbf‘
size 20M
ORA-01119: 创建数据库文件 ‘I:\testData\test.dbf‘ 时出错
ORA-27038: 所创建的文件已存在
OSD-04010: 指定了 <create> 选项, 但文件已经存在

SQL> create tablespace test
2 datafile ‘I:\testData\test.dbf‘
3 size 20M
4 reuse; --加reuse参数，否则报错。
Tablespace created

移动数据文件：
SQL> alter database rename file ‘I:\testData\test.dbf‘ to ‘G:\testData\test.dbf‘;
alter database rename file ‘I:\testData\test.dbf‘ to ‘G:\testData\test.dbf‘
ORA-01511: 重命名日志/数据文件时出错
ORA-01141: 重命名数据文件 7 时出错 - 未找到新文件 ‘G:\testData\test.dbf‘
ORA-01110: 数据文件 7: ‘I:\TESTDATA\TEST.DBF‘
ORA-27041: 无法打开文件
OSD-04002: 无法打开文件
O/S-Error: (OS 2) 系统找不到指定的文件。

必须先把G:\testData\test.dbf 复制或剪切到新的位置，才能移动，否则报错。
SQL> alter database rename file ‘I:\testData\test.dbf‘ to ‘G:\testData\test.dbf‘;
Database altered

将相应的数据文件 online
SQL> alter database datafile ‘G:\testData\test.dbf‘ online;
Database altered

移动表空间：
SQL> alter tablespace test offline;
Tablespace altered
把数据文件 copy 到新位置
SQL> alter tablespace test rename datafile ‘G:\testData\test.dbf‘ to ‘I:\testData\test.dbf‘; ---如果不加路径，可以重命名表空间：alter tablespace test rename to test1;
Tablespace altered

SQL>alter tablespace test online;

更改表空间状态：
表空间状态有下面几种状态：online、offline、read only、read write。
语法：alter tablespace 表空间名状态名；
查看表空间及数据文件状态：

SQL> SELECT TABLESPACE_NAME, CONTENTS, STATUS FROM DBA_TABLESPACES;
TABLESPACE_NAME CONTENTS STATUS
------------------------------ --------- ---------
SYSTEM PERMANENT ONLINE
SYSAUX PERMANENT ONLINE
UNDOTBS1 UNDO ONLINE
TEMP TEMPORARY ONLINE
USERS PERMANENT ONLINE
EXAMPLE PERMANENT ONLINE
LXJ_TEMP TEMPORARY ONLINE
LXJ_DATA PERMANENT ONLINE
TEST PERMANENT ONLINE
9 rows selected

SQL> SELECT FILE#, STATUS, ENABLED FROM V$DATAFILE;
FILE# STATUS ENABLED
---------- ------- ----------
1 SYSTEM READ WRITE
2 ONLINE READ WRITE
3 ONLINE READ WRITE
4 ONLINE READ WRITE
5 ONLINE READ WRITE
6 ONLINE READ WRITE
7 ONLINE READ WRITE
7 rows selected

修改表空间状态语法：
语法：alter tablespace 表空间名状态名；

表空间脱机
SQL>ALTER TABLESPACE TBS_DM_DAT OFFLINE IMMEDIATE;

设置脱机状态，可以使用下面4个参数来控制脱机方式
NORMAL 该参数表示将表空间以正常方式切换到脱机状态，在进入脱机状态过程中，ORACLE会执行一次检查点，将SGA区中与该表空间相关的脏缓存块写入数据文件中，然后再关闭表空间的所有数据文件。如果在这过程中没有发生任何错误，则可以使用NORMAL参数，这也是默认的方式。

TEMPORARY 该参数将表空间以临时方式切换到脱机状态。这时ORACLE在执行检查点时并不会检查各个数据文件的状态，即使某些数据文件处于不可用状态，ORACLE也会忽略这些错误。这样将表空间设置为联机状态时，可能需要进行数据恢复。

IMMEDIATE 该参数将表空间以立即方式切换到脱机状态，这时ORACLE不会执行检查点，也不会检查数据文件是否可用。而是直接将属于表空间的数据文件设置为脱机状态。下一次将表空间恢复为联机状态时必须进行数据库恢复。

FOR RECOVER 该参数将表空间以用于恢复方式切换到脱机状态，如果要对表空间进行基于时间的恢复，可以使用这个参数将表空间切换到脱机状态。

如果数据库运行在非归档模式下(NOARCHIVELOG),由于无法保留恢复表空间所需要的重做数据，所以不能将表空间以立即方式切换到脱机状态。如果表空间脱机了，则查询表空间下的表，会报错误：ORA-00376 此时无法读取文件以及 ORA-01110：数据文件x......

注意：脱机（offline）一般用于数据库的联机备份，数据恢复等维护操作。有些表空间不能OFFLINE，如：SYTEM,UNDO等

1. SYTEM 不能offline，也不能read only

2. 当前的UNDO表空空间，不能offline,也不能read only

3. 当前的临时表空间不能offline,也不能read only

4. SYSAUX可以offline 不能read only

SQL> ALTER TABLESPACE SYSTEM OFFLINE;

ALTER TABLESPACE SYSTEM OFFLINE

ORA-01541: system tablespace cannot be brought offline; shut down if necessary

SQL> ALTER TABLESPACE SYSTEM OFFLINE;

ALTER TABLESPACE SYSTEM OFFLINE

ORA-01541: system tablespace cannot be brought offline; shut down if necessary

表空间开启或关闭(off)自动扩展：
SQL> alter database datafile ‘G:\TESTDATA\TEST.DBF‘ autoextend on;

表空间配额管理：
表空间不足与用户配额不足是两种不同的概念。表空间的大小是指实际的用户表空间的大小，而配额大小指的是用户指定使用表空间的的大小。两者的解决方式亦不相同
管理用户表空间配额
1: --查看所有用户表空间的配额情况
2: SELECT * FROM DBA_TS_QUOTAS
3:
4: --查看当前用户表空间的配额情况
5: SELECT * FROM USER_TS_QUOTAS
用户表空间限额的创建与更改：
1.创建用户时，指定限额
eg：
CREATE USER TEST IDENTIFIED BY TEST

DEFAULT TABLESPACE TS_TEST

TEMPORARY TABLESPACE TEMP

QUOTA 3M ON TS_TEST

PASSWORD EXPIRE;

2.更改用户的表空间限额：

A：不对用户做表空间限额控制:

查看是否没有表空间限额限制

B：取消限额

这种方式是全局性的.

SQL> GRANT UNLIMITED TABLESPACE TO SCOTT;

或者针对特定的表空间的.

SQL>ALTER USER SCOTT QUOTA UNIMITED ON TBS_EDS_DAT;