应用场景83：修改大文件表空间的属性：

ALTER TABLESPACE bigtbs RESIZE 4G;

应用场景84：设置表空间的状态：

ALTER TABLESPACE OrclTBS01 OFFLINE;

SELECT TABLESPACE_NAME,CONTENTS,STATUS FROM DBA_TABLESPACES;

ALTER TABLESPACE OrclTBS01 ONLINE;

应用场景85：设置只读表空间

ALTER TABLESPACE OrclTBS01 OFFLINE;

SELECT TABLESPACE_NAME,CONTENTS,STATUS FROM DBA_TABLESPACES;

ALTER TABLESPACE OrclTBS01 ONLINE;

应用场景86：删除表空间

DROP TABLESPACE OrclTBS03;

DROP TABLESPACE OrclTBS01 INCLUDING CONTENTS;

DROP TABLESPACE OrclTBS01 INCLUDING CONTENTS AND DATAFILES;

应用场景87：统计表空间的使用情况

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);

应用场景88：在CREATE TABLESPACE 语句中创建临时表空间组

CREATE TEMPORARY TABLESPACE temp_spc
TEMPFILE 'D:\app\Administrator\oradata\orcl\temp03.dbf'
SIZE 20M
TABLESPACE GROUP temp_grp;

应用场景89:使用ALTER TABLESPACE 语句创建临时表空间组

ALTER TABLESPACE temp_spc TABLESPACE GROUP new_temp_group

应用场景90：在Enterprise Manager中查看和添加临时表空间组

应用场景91：使用视图DBA_TABLESPACE_GROUPS查看临时表空间信息

SELECT * FROM DBA_TABLESPACE_GROUPS;

应用场景92：将表空间从临时表空间组中移出

ALTER TABLESPACE temp_spc TABLESPACE GROUP '';

应用场景93：表空间组分配给指定用户

CREATE USER LEE IDENTIFIED BY leepass
DEFAULT TABLESPACE Users
TEMPORARY TABLESPACE NEW_TEMP_GROUP;

ALTER USER LEE TEMPORARY TABLESPACE NEW_TEMP_GROUP;

SELECT USERNAME, DEFAULT_TABLESPACE, TEMPORARY_TABLESPACE
FROM DBA_USERS WHERE USERNAME='LEE';

ALTER DATABASE ORCL DEFAULT TEMPORARY TABLESPACE NEW_TEMP_GROUP;

应用场景94：查看回滚段信息：

SELECT SEGMENT_NAME, TABLESPACE_NAME, BYTES, NEXT_EXTENT
FROM DBA_SEGMENTS WHERE SEGMENT_TYPE='ROLLBACK';

COL NAME FOR A12
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;

应用场景95：设置回滚段的管理模式

SHOW PARAMETER UNDO_MANAGEMENT

ALTER SYSTEM SET UNDO_MANAGEMENT=MANUAL
SCOPE=SPFILE;

应用场景96：合理规划和创建回滚段

CREATE ROLLBACK SEGMENT OrclRs01
TABLESPACE UndoTBS1
STORAGE (INITIAL 5M
NEXT 2M
MAXEXTENTS UNLIMITED);

应用场景97：修改回滚段的属性：

ALTER ROLLBACK SEGMENT OrclRs01 ONLINE;

SELECT NAME, STATUS FROM V$ROLLNAME, V$ROLLSTAT
WHERE V$ROLLSTAT.USN=V$ROLLNAME.USN;

ALTER ROLLBACK SEGMENT OrclRs01 SHRINK TO 1M;

应用场景98：删除回滚段

ALTER ROLLBACK SEGMENT OrclRs01 OFFLINE;
DROP ROLLBACK SEGMENT OrclRs01;