查看表空间信息
SELECT * FROM V$TABLESPACE
查看表空间数据文件路径信息
SELECT * FROM DBA_DATA_FILES;
查看表空间的属性信息
SELECT * FROM DBA_TABLESPACES
查看表空间组及其所属的表空间的信息
SELECT * FROM DBA_TABLESPACE_GROUPS
查看表空间里面的表的组成
SELECT SEGMENT_NAME,SEGMENT_TYPE,TABLESPACE_NAME FROM DBA_SEGMENTS WHERE TABLESPACE_NAME=‘USERS‘
查看表空间中空闲区间的信息
SELECT TABLESPACE_NAME,BYTES,BLOCKS FROM DBA_FREE_SPACE;
SYS AS SYSDBA@ORCL>SELECT D.TABLESPACE_NAME,trunc(D.BYTES/2014)||‘M‘,D.BLOCKS,E.FILE_NAME FROM DBA_FREE_SPACE D,DBA_DATA_FILES E WHERE D.TABLESPACE_NAME=E.TABLESPACE_NAME;
创建表空间(本地管理表空间)
CREATE SMALLFILE TABLESPACE "RSC" DATAFILE ‘/u01/app/oracle/oradata/ORCL/RSC.DBF‘ SIZE 100M REUSE AUTOEXTEND ON NEXT 100M MAXSIZE 32767M LOGGING EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO
AUTOALLOCATE //指定表空间由系统管理
UNIFORM //手动管理区间大小
大文件表空间：
大文件表空间由惟一的，非常巨大的数据文件组成，普通小文件表空间可以包含多个数据文件，但大文件表空间只能包含一个数据文件
撤销表空间
SYS AS SYSDBA@ORCL>CREATE UNDO TABLESPACE UNDOTBS01 DATAFILE ‘/u01/app/oracle/oradata/ORCL/UNDOTBS01.DBF‘ SIZE 2M REUSE;
使用ALTER TABLESPACE 语句重命名表空间
ALTER TABLESPACE UNDOTBS01 RENAME TO UNDOTBS001;
查看使用后的表空间
SELECT * FROM V$TABLESPACE;
向本地管理表空间中增加数据文件
ALTER TABLESPACE RSC ADD DATAFILE ‘/u01/app/oracle/oradata/ORCL/RSC011.DBF‘ SIZE 2M REUSE;
++++++++++++++++++++++++++++++++++++++++++++++++
ALTER TABLESPACE 命令重命名数据文件的步骤：
1、使表空间脱机。
2、使用操作系统命令移动或复制文件。
3、执行ALTER TABLESPACE RENAME DATAFILE 命令。
4、使表空间联机。
5、必要时使用操作系统命令删除原来的数据文件。
下面是例子：
1、alter tablespace tBS_T02 offline normal ;----表空间脱机
2、linux执行：cp TBS_T02.dbf ./test/------linux下修改数据文件或者重命名
3、alter tablespace TBS_T02 rename datafile ‘/opt/oracle/oradata/ora36/TBS_T02.dbf‘ to ‘/opt/oracle/oradata/ora36/test/TBS_T02.dbf‘;------执行修改命令，第一路径是原路径，第二个是将要改变的数据
4、alter tablespace TBS_T02 online;--------表空间联机
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
修改大文件表空间的属性
ALTER TABLESPACE bigtbs RESIZE 4G;
向临时表空间中添加临时文件
ALTER TABLESPACE TEMP ADD DATAFILE ‘/u01/app/oracle/oradata/ORCL/temp01.DBF‘ SIZE 20M REUSE;
查看表空间的状态
脱机和联机
在这些情况下将表空间设置为脱机状态
1.将数据库的一部分设置为不可访问，而其他部分可以访问
2.执行脱机表空间备份
3.在升级或维护应用程序时，将应用程序及其使用的表临时设置为不可访问
4.重命令或重新分配表空间
系统表空间（SYSTEM）,撤消表空间(UNDO),临时表空间(TEMPORARY)不可以设置为脱机状态
ALTER TABLESPACE RSC OFFLINE; //脱机
ALTER TABLESPACE RSC ONLINE; //联机
查看表空间状态
SELECT TABLESPACE_NAME,CONTENTS,STATUS FROM DBA_TABLESPACES;
设置只读表空间:
ALTER TABLESPACE RSC READ ONLY;
查看表空间状态:
SELECT TABLESPACE_NAME, STATUS FROM DBA_TABLESPACES;
设置表空间读写：
ALTER TABLESPACE RSC READ WRITE;
删除表空间:
DROP TABLESPACE RSC;
删除表空间的同时，删除包含的段和数据文件
DROP TABLESPACE RSC INCLUDING CONTENTS AND DATAFILES;
查看表空间的名称：
SELECT * FROM V$TABLESPACE;
统计表空间的使用情况
DBA_DATA_FILES:用于查询所有数据文件的信息
DBA_FREE_SPACE:用于查询表空间的空闲区间信息
DBA_TABLESPACES:用于查询所有表空间的信息
统计所有表空间的总空间大小
SELECT TABLESPACE_NAME,SUM(BYTES)/1024/1024 BYTES_M FROM DBA_DATA_FILES GROUP BY TABLESPACE_NAME;
统计所有表空间空闲空间的大小
SELECT a.tablespace_name, SUM(b.bytes)/1024/1024 bytes_M
FROM DBA_DATA_FILES a,DBA_FREE_SPACE b
WHERE a.tablespace_name=b.tablespace_name (+) AND a.file_id=b.file_id (+)
GROUP BY a.tablespace_name;
统计表空间的使用情况:
SELECT c.tablespace_name "表空间", ROUND(a.bytes/1024/1024,2) "表空间大小" ,ROUND((a.bytes-b.bytes)/1048576,2)"已使用表空间" ,ROUND(b.bytes/1048576,2) "剩余空间",ROUND
(b.bytes/a.bytes*100,2)||‘%‘"剩余百分比"
FROM
(SELECT tablespace_name,SUM(bytes) bytes FROM DBA_DATA_FILES GROUP BY tablespace_name) a,
(SELECT a.tablespace_name,NVL(SUM(b.bytes),0) bytes FROM DBA_DATA_FILES a, DBA_FREE_SPACE b
WHERE a.tablespace_name=b.tablespace_name (+) AND a.file_id=b.file_id (+) GROUP BY a.tablespace_name) b,DBA_TABLESPACES c
WHERE a.tablespace_name=b.tablespace_name (+) AND a.tablespace_name=c.tablespace_name ORDER BY ROUND(b.bytes/1024/1024,2);
在CREATE TABLESPACE语句中创建临时表空间组
CREATE TEMPORARY TABLESPACE TEMP_SPC
TEMPFILE ‘/u01/app/oracle/oradata/ORCL/TEMP03.DBF‘
SIZE 20M
TABLESPACE GROUP TEMP_GRP;
查看所有的临时表空间的空间使用情况 ：
SELECT * FROM DBA_TEMP_FREE_SPACE;
查看所有临时表空间的数据文件的属性信息：
select * from dba_temp_files;
select * from v$tempfile;
要为临时表空间扩容，使用下面的语句
ALTER TABLESPACE &tablespace_name ADD TEMPFILE ‘/oradata/temp01.dbf‘ SIZE 2G;
查看临时表空间组：
SELECT * FROM DBA_TABLESPACE_GROUPS;
将已经创建好的临时表空间加入到临时表空间组里面：
ALTER TABLESPACE TEMP_SPC01 TABLESPACE GROUP TEMP_GRP;
使用ALTER TABLESPACE语句创建临时表空间组名称：
ALTER TABLESPACE TEMP_SPC01 TABLESPACE GROUP NEW_TEMP_GROUP;
将表空间从临时表空间组中移出
ALTER TABLESPACE TEMP_SPC01 TABLESPACE GROUP ‘‘;
将临时表空间分配给指定用户
创建用户时分配临时表空间
CREATE USER LEE IDENTIFIED BY 123
DEFAULT TABLESPACE USERS
TEMPORARY TABLESPACE NEW_TEMP_GROUP;
给指定用户分配临时表空间
ALTER USER RSC TEMPORARY TABLESPACE TEMP_GRP;
查看用户临时表空间信息
SELECT USERNAME,DEFAULT_TABLESPACE,TEMPORARY_TABLESPACE FROM DBA_USERS WHERE USERNAME=‘RSC‘;
设置数据库的默认临时表空间组
ALTER DATABASE ORCL DEFAULT TEMPORARY TABLESPACE NEW_TEMP_GROUP;
收缩临时表空间
SYS AS SYSDBA@ORCL>SELECT TABLESPACE_NAME,BYTES/1024/1024 FROM DBA_TEMP_FILES;
TABLESPACE_NAME BYTES/1024/1024
------------------------------ ---------------
TEMP 29
ALTER TABLESPACE TEMP SHRINK SPACE KEEP 25M;
SYS AS SYSDBA@ORCL>SELECT TABLESPACE_NAME,BYTES/1024/1024 FROM DBA_TEMP_FILES;
TABLESPACE_NAME BYTES/1024/1024
------------------------------ ---------------
TEMP 26
监控临时表空间
查看临时表空间使用情况：
SELECT TU.TABLESPACE_NAME AS "TABLESPACE_NAME",
TT.TOTAL - TU.USED AS "FREE(G)",
TT.TOTAL AS "TOTAL(G)",
ROUND(NVL(TU.USED, 0) / TT.TOTAL * 100, 3) AS "USED(%)",
ROUND(NVL(TT.TOTAL - TU.USED, 0) * 100 / TT.TOTAL, 3) AS "FREE(%)"
FROM (SELECT TABLESPACE_NAME,
SUM(BYTES_USED) / 1024 / 1024 / 1024 USED
FROM GV_$TEMP_SPACE_HEADER
GROUP BY TABLESPACE_NAME) TU ,
(SELECT TABLESPACE_NAME,
SUM(BYTES) / 1024 / 1024 / 1024 AS TOTAL
FROM DBA_TEMP_FILES
GROUP BY TABLESPACE_NAME) TT
WHERE TU.TABLESPACE_NAME = TT.TABLESPACE_NAME;
COL TEMP_FILE FOR A60;
SELECT ROUND((F.BYTES_FREE + F.BYTES_USED)/1024/1024/1024, 2) AS "TOTAL(GB)",
ROUND(((F.BYTES_FREE + F.BYTES_USED) - NVL(P.BYTES_USED, 0))/1024/1024/1024,2) AS "FREE(GB)",
D.FILE_NAME AS "TEMP_FILE",
ROUND(NVL(P.BYTES_USED, 0)/1024/1024/1024, 2) AS "USED(GB)" ,
ROUND((F.BYTES_USED + F.BYTES_FREE)/1024/1024/1024, 2) AS "TOTAL(GB)",
ROUND(((F.BYTES_USED + F.BYTES_FREE) - NVL(P.BYTES_USED, 0))/1024/1024/1024, 2) AS "FREE(GB)" ,
ROUND(NVL(P.BYTES_USED, 0)/1024/1024/1024, 2) AS "USED(GB)"
FROM SYS.V_$TEMP_SPACE_HEADER F ,DBA_TEMP_FILES D ,SYS.V_$TEMP_EXTENT_POOL P
WHERE F.TABLESPACE_NAME(+) = D.TABLESPACE_NAME
AND F.FILE_ID(+) = D.FILE_ID
AND P.FILE_ID(+) = D.FILE_ID;
查看临时表空间对应的临时文件的使用情况
SELECT TABLESPACE_NAME AS TABLESPACE_NAME ,
BYTES_USED/1024/1024/1024 AS TABLESAPCE_USED ,
BYTES_FREE/1024/1024/1024 AS TABLESAPCE_FREE
FROM V$TEMP_SPACE_HEADER
ORDER BY 1 DESC;
查找消耗临时表空间资源比较多的SQL语句
SELECT se.username,
se.sid,
su.extents,
su.blocks * to_number(rtrim(p.value)) as Space,
tablespace,
segtype,
sql_text
FROM v$sort_usage su, v$parameter p, v$session se, v$sql s
WHERE p.name = ‘db_block_size‘
AND su.session_addr = se.saddr
AND s.hash_value = su.sqlhash
AND s.address = su.sqladdr
ORDER BY se.username, se.sid;
创建回滚段：
CREATE [PUBLIC] ROLLBACK SEGMENT rollback_segment
[TABLESPACE tablespace]
[STORAGE ([INITIAL integer[K|M]] [NEXT integer[K|M]]
[MINEXTENTS integer]
[MAXTENTS {integer|UNLIMITED}]
[OPTIMAL {integer[K|M]|NULL}]) ]
注：
回滚段可以在创建时指明PRIVATE或PUBLIC，一旦创建将不能修改。
MINEXTENTS 必须大于等于2
PCTINCREASE必须是0
OPTIMAL如果要指定，必须大于等于回滚段的初始大小（由MINEXTENTS指定）
建议：
一般情况下，INITIAL=NEXT
设置OPTIMAL参数来节约空间的使用
不要设置MAXEXTENTS为UNLIMITED
回滚段应创建在一个特定的回滚段表空间内
例：
CREATE ROLLBACK SEGMENT rbs01
TABLESPACE rbs
STORAGE ( INITIAL 100K NEXT 100K MINEXTENTS 10
MAXEXTENTS 500 OPTIMAL 1000K);
使回滚段在线
当回滚段创建后，回滚段是离线的，不能被数据库使用，为了使回滚段被事务利用，必须将回滚段在线。可以用以下命令使回滚段在线：
ALTER ROLLBACK SEGMENT rollback_segment ONLINE;
例：
ALTER ROLLBACK SEGMENT rbs01 ONLINE；
为了使回滚段在数据库启动时自动在线，可以在数据库的参数文件中列出回滚段的名字。例如在参数文件中加入以下一行：
ROLLBACK_SEGMENT=(rbs01,rbs02)
回滚段的种类：
系统回滚段:
只用于存放系统表空间中对象的前影像
非系统回滚段：(私有回滚段和公有回滚段)
数据库至少要有一个非系统回滚段
私有回滚段:在参数文件中ROLLBACK SEGMENTS中列出
公有回滚段：在OPS(ORACLE 并行服务器)中出现
DEFERED回滚段：该回滚段在表空间离线(OFFLINE)时由系统自动创建，当表空间再次在线（ONLINE）时由系统自动删除，用于存放表空间离线时产生的回滚信息。
事务可以用以下的语句申请指定的回滚段：
SET TRANSTRACTION USE ROLLBACK SEGMENT rollback_segment
查看回滚信息
SELECT SEGMENT_NAME,TABLESPACE_NAME,BYTES,NEXT_EXTENT FROM DBA_SEGMENTS WHERE SEGMENT_TYPE=‘ROLLBACK‘;
SELECT * FROM V$ROLLNAME;
SELECT * FROM V$ROLLSTAT;
查看回滚段的当前工作情况：
SELECT S.USN,N.NAME,S.EXTENTS,S.OPTSIZE,S.HWMSIZE,S.STATUS FROM V$ROLLSTAT S, V$ROLLNAME N WHERE S.USN=N.USN;
设置回滚段的管理模式：
查看现在的管理模式：
SHOW PARAMETER UNDO_MANAGEMENT
修改回滚段的管理模式：
ALTER SYSTEM SET UNDO_MANAGEMENT=MANUAL SCOPE=SPFILE; //启动实例后才能生效
用户创建回滚段需要将回滚段设置为手动才可以修改：
查看表空间段的管理方式:
SELECT TABLESPACE_NAME,SEGMENT_SPACE_MANAGEMENT FROM DBA_TABLESPACES;
创建回滚段：
CREATE ROLLBACK SEGMENT ORCLRS01 TABLESPACE RSC STORAGE(INITAL 5M NEXT 2M MAXEXTENTS UNLIMITED)
修改回滚段的状态：
ALTER ROLLBACK SEGMENT <回滚段名称> <回滚段状态>
ALTER ROLLBACK SEGMENT ORCLRS01 ONLINE;
查看回滚段状态：
SYS AS SYSDBA@ORCL>SELECT NAME,STATUS FROM V$ROLLNAME,V$ROLLSTAT WHERE V$ROLLSTAT.USN=V$ROLLNAME.USN;
NAME STATUS
------------------------------ ---------------
SYSTEM ONLINE
手动收缩回滚段：
ALTER ROLLBACK SEGMENT ORCLRS01 SHRINK TO 1M;
删除回滚段：
1.ALTER ROLLBACK SEGMENT ORCLRS01 OFFLINE;
2.DROP ROLLBACK SEGMENT ORCLRS01;
3.SELECT * FROM V$ROLLNAME;
原文：http://rscpass.blog.51cto.com/771159/1612741