数据库逻辑存储结构管理（5）-存储-表空间段区块

查看表空间信息
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;