Tablespace
Tablespace
Table of Contents
1 概念
表空间，是Oralce 数据库中的一个逻辑概念。我们可以理解表空间是存储表、索引的逻辑空间。 虽然表空间是逻辑概念，但是实际上也是有其对应的物理存储的。 表空间是由一个或者多个数据文件组成。大文件表空间只有一个数据文件。小文件表空间中最多可以添加多少个数据文件是由rowid的 计算方式决定的。关于相关信息请参见ORACLE_ROWID
2 相关参数
2.1 db_block_size
此参数是数据块大小。
2.2 db_files
此参数限制了数据库中数据文件个数总和的最大值.当然这个值也不是无限设置的，最大值也受到ROWID影响。原因请参见: ORACLE_ROWID
3 相关视图
3.1 DBA_DATA_FILES
3.2 DBA_FREE_SPACE
3.3 V$DATAFILES
3.4 dba_hist_tbspc_space_usage
3.5 dba_free_space
4 分类
表空间的分类，根据不同的标准会有不同的类别.
- 根据数据会话结束后数据仍保留，分为永久表空间与临时表空间
- 根据数据段类型，分为：数据(数据段与索引段，大对象等)表空间,临时表空间，UNDO表空间
- 根据表空间由多少个文件组成，分为：大文件表空间（只有一个文件），小文件表空间（多个文件组成一个表空间，单个表空间最多1023个数据文件）
- 根据管理方式分为: 字典管理和本地管理。
4.1 永久表空间
4.1.1 小文件表空间
4.1.2 大文件表空间
4.1.3 resumalbe
当我们在建立一个大表，或往表里面插入大量数据时，如果中途因为表空间剩余空间不足并且没有开启自动扩展的话，
会话中中止并退出操作，如果操作了很长时间，要从头再来，是非常痛苦的。
这种情况往往出现在我们还无法预估将要插入的数据量时，除了个一个极大的表空间或自动扩展来解决之外。
Oracle还为我们提供了一个很好的功能：resumable
在 resumable开启的情况下，如果Oracle执行某一个SQL申请不到空间了，会话会暂停,开始等待有空间可用，（等待 时间可以由TIMEOUT来控制），等你把空间的问题解决了，Oracle会继续刚才的操作。
想要使用这个功能，需要拥有resumable权限。
具体的语法:ALTER SESSION{ ENABLE RESUMABLE [ TIMEOUT integer ] [ NAME string ]| DISABLE RESUMABLE}
相关数据字典：USER_RESUMABLE and DBA_RESUMABLE
如果在SQL中不指定NAME，Oracle自动生成的NAME= ‘Userusername(userid), Session sessionid, Instance instanceid’.
- 回顾9i中的功能
权限设置 首先一个用户要设置resumable session，必须具有resumable的权限。
grant resumable to study;
resumable权限也被包含在其他role里面，比如dba，当一个用户拥有dba role时，就自动拥有了resumable privilege.
开启与关闭
-- ENALBE resumable session alter session enable resumable; --开启 alter session disable resumable; --关闭
超时设置 resumable session的默认超时时间为7200秒，也就是2小时。我们可以通过如下的来调整timeout的时间，比如
alter session enable resumable timeout 1800;
监控 我们可以通过dba_resumable和alert日志来监控resumable session的状态。
alter session enable resumable timeout 10; select user_id,SESSION_ID, STATUS, START_TIME, SUSPEND_TIME,SQL_TEXT, ERROR_NUMBER,ERROR_MSG from dba_resumable;
dbms_reumable包 Oracle也提供了一个dbms_resumable package来设置获取或异常终止一个resumable session的操作。
SQL> alter session enable resumable; -- 设置其他会话resumable_time exec dbms_resumable.set_session_timeout(49,1800); -- 查看指定会话的resumable_time select dbms_resumable.get_session_timeout(49) from dual; -- 终止指定会话中已经挂起的会话 exec dbms_resumable.abort(49);
执行此命令，被挂起的会话会出现错误： ORA-01013: 用户请求取消当前的操作
- 10g中的增强
在10g中，Oracle一样支持9i的上述方法，同时做了增强。
增加了一个resumable_timeout的参数
该参数可以在system和session level级均可以修改.对 db,每个instance可以单独设置.而9i中只能在session一级中设置。比如：
SQL> show parameter resumable_timeout; NAME TYPE VALUE ------------------- -------------- ------- resumable_timeout integer 0 SQL> alter system set resumable_timeout=60;
- 对分布式事务的支持 在9i中，resumable特性不支持分布式事务。 10g中，这一限制已经去除。但是在一个分布式事务中，用户开启或者关闭resumable 特性或者变更resumable_timeout参数， 只对当前实例有影响；如果resumable 特性在远端的实例上开启，那么远端的会话会被暂停。
增加了一个监控的视图
在10g中，我们也可以通过DBA_OUTSTANDING_ALERTS 来查看当前正牌挂起状态的session.比如：
select object_name,object_type,reason,message_type,message_level from dba_outstanding_alerts where message_group='Space';
- resumable操作在其他utiliy中的使用
在exp/imp,expdp/impdp,sql*loader工具，同样也支持resumable操作,10G 之后是默认开启的. 具体可查看相关命令的帮助文档。
4.2 DONE 临时表空间
临时表空间主要用途是在数据库进行数据排序、管理索引、访问视图等操作时提供临时的运算空间，当运算完成之后系统会自动清理。
其中的排序操作并不一定是在临时表空间中完成的，首先是在PGA中的sort_area 中进行的，PGA 中 sort_area_size 大小不够时，将会把数据放入临时表空间里进行排序，同时如果有异常情况的话，也会被放入临时表空间。由于临时表空间是存储在磁盘上的，而sort_area 是内存区域，这两者在操作上的效率差异非常大。所以我们需要尽量避免在临时表空间中发生排序操作。
临时数据只对会话有效，会话结束后数据自动清除。临时段保留不释放，会话间的临时数据不共享。 注意这里的释放，仅仅是将这些空间标记为空闲，并可重用，真正占用的磁盘空间并没有释放。 所以 Temp 表空间可能会越来越大。
如下操作都会申请临时段: create index, order by ,group by , union, intersect,minus,使用临时表,enable constrints 等。
这里需要注意一点，临时表创建时，临时段并不分配，只有第一条数据插入时或者使用CTAS(create temporary table as select ……)创建临时表时，临时段才会分配。
4.2.1 相关视图
与临时表空间相关的常用视图如下:
- DBA_TEMP_FILES
下面来看下视图相关字段信息
Table 1: DBA_TEMP_FILES 字段名 字段类型 说明 FILE_NAME VARCHAR2(513) 组成临时表空间的文件 FILE_ID NUMBER 全局文件ID TABLESPACE_NAME NOT NULL VARCHAR2(30) 临时表空间的名字 BYTES NUMBER 文件大小(单位byte) BLOCKS NUMBER 该文件被划分为多少个数据块（块大小由db_block_size决定) STATUS VARCHAR2(7) 文件状态 RELATIVE_FNO NUMBER 表空间内文件的相对文件号 AUTOEXTENSIBLE VARCHAR2(3) 文件是否自动扩展 MAXBYTES NUMBER 文件最大扩展到的大小(单位byte) MAXBLOCKS NUMBER 文件最多被划分为多少个数据块 INCREMENT_BY NUMBER 单次扩展量 USER_BYTES NUMBER 可用的文件大小，单位byte USER_BLOCKS NUMBER 可用的文件大小，以块计算(受db_block_size影响) - DBA_TEMP_FREE_SPACE
是Oracle 11G 才开发出来的一个视图。该视图主要检查表空间中剩余可用空间。从该视图的创建语句来看，是由gv$temp_space_header与gv$sort_segment 和基表ts$关联查询所得。关于其中两个视图的内容，在后面视图分析中会涉及。
基表 我们可以通过视图的创建语句查看到基表。下面是视图的创建语句。
CREATE OR REPLACE FORCE VIEW "SYS"."DBA_TEMP_FREE_SPACE" ("TABLESPACE_NAME", "TABLESPACE_SIZE", "ALLOCATED_SPACE", "FREE_SPACE") AS SELECT tsh.tablespace_name, tsh.total_bytes/tsh.inst_count, tsh.bytes_used/tsh.inst_count, (tsh.bytes_free/tsh.inst_count) + (nvl(ss.free_blocks, 0) * ts$.blocksize) FROM (SELECT tablespace_name, sum(bytes_used + bytes_free) total_bytes, sum(bytes_used) bytes_used, sum(bytes_free) bytes_free, count(distinct inst_id) inst_count FROM gv$temp_space_header GROUP BY tablespace_name) tsh, (SELECT tablespace_name, sum(free_blocks) free_blocks FROM gv$sort_segment GROUP BY tablespace_name) ss, ts$ WHERE ts$.name = tsh.tablespace_name and tsh.tablespace_name = ss.tablespace_name (+);字段说明
Table 2: DBA_TEMP_FREE_SPACE 字段名 类型 说明 TABLESPACE_NAME NOT NULL VARCHAR2(30) 表空间名字 TABLESPACE_SIZE NUMBER 表空间大小 ALLOCATED_SPACE NUMBER 历史分配出来的最间分配最大值 FREE_SPACE NUMBER 剩余可用临时表空间 - note
- 这里需要注意，allocated_space 是曾经或者当前已使用的临时表空间最大值.临时表空间创建后，磁盘上文件大小显示为创建表空间时指定的文件大小（或者是添加文件时指定的文件大小),但是临时表空间文件在文件系统上并没有真正分配空间，这种文件被称为 稀疏文件 。当真正需要空间临时存放数据时，才会从操作系统上真正的分配空间，按需分配。allocated_space 记录的就是这个按需分配的历史最大值。free_space 指的是当前表空间最大可用空间,包含可扩展空间及未分配空间。
- V$SORT_SEGMENT
要了解这些相关的视图，需要了解几个帮助我们运维的几个视图：
该视图是发生在临时表空间排序操作涉及到的临时段的统计信息。只有发生在临时表空间的排序操作才会更新该视图。
字段说明
Table 3: V$SORT_SEGMENT 字段名 数据类型 说明 TABLESPACE_NAME VARCHAR2(31) 排序段所属临时表空间 SEGMENT_FILE NUMBER 第一个扩展（区）所属文件 SEGMENT_BLOCK NUMBER 第一个区的块编号 EXTENT_SIZE NUMBER 区大小(单位block) CURRENT_USERS NUMBER 当前使用临时表空间的用户数 TOTAL_EXTENTS NUMBER 排序段中包含多少个extent TOTAL_BLOCKS NUMBER 段中包含多少个块 USED_EXTENTS NUMBER 段中已分配的用于排序的extents USED_BLOCKS NUMBER 段中已分配的用于排序的块数量 FREE_EXTENTS NUMBER 段中未分配的extents FREE_BLOCKS NUMBER 段中未分配的blocks ADDED_EXTENTS NUMBER 段中已分配的extents EXTENT_HITS NUMBER sort buffer 中找到 未使用的extent的次数 FREED_EXTENTS NUMBER 释放的extents的个(次)数 FREE_REQUESTS NUMBER 请求释放extent的次数 MAX_SIZE NUMBER 曾经用过的最多的extent个数 MAX_BLOCKS NUMBER 曾经用过的最多的块数 MAX_USED_SIZE NUMBER 当前所有排序操作使用的extent数 MAX_USED_BLOCKS NUMBER 当前所有排序操作使用的块数 MAX_SORT_SIZE NUMBER 当前单个排序所使用的区(extent)个数 MAX_SORT_BLOCKS NUMBER 当前单个排序所使用的块数 RELATIVE_FNO NUMBER 表空间内相对文件号 TS# NUMBER 排序段的段头所在文件的相对文件号 CON_ID NUMBER CONTAINER ID.值 可能是： 0：表示该数据所属实例为非CDBS 或者属于整个CDB 1: 属于root的数据 n: container id . IS_LOCAL_TEMP NUMBER 1==>YES 0==>NO.
- V$SORT_USAGE
在单实例中，我们一般会用的都是v$视图。实际上V$* 视图也是从gv$中进行查询的。比如v$sort_usage.看一看视图的创建语句
SQL> select view_definition from v$fixed_view_definition where view_name='V$SORT_USAGE'; select USERNAME , "USER" , SESSION_ADDR , SESSION_NUM , SQLADDR , SQLHASH, SQL_ID, TABLESPACE , CONTENTS , SEGTYPE , SEGFILE# , SEGBLK# ,EXTENTS , BLOCKS , SEGRFNO#, TS#, CON_ID , SQL_ID_TEMPSEG from GV$SORT_USAGE where inst_id = USERENV('Instance')由此可以看出，v$sort_usage 视图是根据gv$sort_usage 查询得来。再来看看gv$的创建语句:
-- Oracle 11G select x$ktsso.inst_id, username, username, ktssoses, ktssosno, prev_sql_addr,prev_hash_value , prev_sql_id, ktssotsn, decode(ktssocnt, 0, 'PERMANENT', 1, 'TEMPORARY') , decode(ktssosegt, 1, 'SORT', 2, 'HASH', 3, 'DATA', 4, 'INDEX', 5, 'LOB_DATA', 6, 'LOB_INDEX' , 'UNDEFINED') , ktssofno, ktssobno, ktssoexts, ktssoblks,ktssorfno from x$ktsso, v$session where ktssoses = v$session.saddr and ktssosno= v$session.serial# / -- Oracle 12C select so.inst_id, username, username, ktssoses, ktssosno, prev_sql_addr, prev_hash_value ,prev_sql_id, ts.name, decode(ts.contents$, 0, 'PERMANENT', 1, 'TEMPORARY') , decode(ktssosegt, 1, 'SORT', 2, 'HASH', 3, 'DATA', 4, 'INDEX', 5, 'LOB_DATA', 6, 'LOB_INDEX' , 'UNDEFINED') , ktssofno, ktssobno, ktssoexts, ktssoblks, ktssorfno, ktssotsnum, so.con_id, ktssosqlid from x$ktsso so, v$session, ts$ ts where so.ktssotsnum = ts.ts# and ktssoses = v$session.saddr and ktssosno = v$session.serial#由此可以看出，排序段的数据来源于视图: v$session 与基表 x\(ktsso 和基表ts\). 同时，11g 中的 表空间名及存储数据类型的取值已发生变化.原来分别是x$ktsso.ktssotns 及x\(ktsso.ktssocnt. 12C 中分别改 为ts\).name,ts$.contents#.
字段说明
Table 4: V$SORT_UAGE 字段名 数据类型 说明 版本 USERNAME VARCHAR2(30) 请求临时空间的用户 USER VARCHAR2(30) 已废弃。保留以向后兼容 SESSION_ADDR RAW(4 \VERT 8) 会话内存地址 SESSION_NUM NUMBER 会话序列号 SQLADDR RAW(4 \VERT 8) v$session.prev_sql_id 对应的SQL语句的内存地址 SQLHASH NUMBER v$session.prev_sql_id 对应的SQL的HASH值 SQL_ID VARCHAR2(13) v$session.prev_sql_id TABLESPACE VARCHAR2(31) 表空间名 CONTENTS VARCHAR2(9) decode(ts$.contents$, 0, 'PERMANENT', 1, 'TEMPORARY') SEGTYPE VARCHAR2(9) decode(x$ktsso.ktssosegt, 1, 'SORT', 2, 'HASH', 3, 'DATA', 4, 'INDEX' , 5, 'LOB_DATA', 6, 'LOB_INDEX' , 'UNDEFINED' SEGFILE# NUMBER 段所在文件的文件号 SEGBLK# NUMBER EXTENTS NUMBER BLOCKS NUMBER SEGRFNO# NUMBER TS# NUMBER 12C CON_ID NUMBER 12C SQL_ID_TEMPSEG VARCHAR2(13) 12C 从以上字段的解释，可以看出，v$sort_usage中的sqladdr,sqlhash,sql_id 都与v$session中的prev_sql_id 相关,这样查询出来的内容都不是当前占用临时段的 会话,从11.2.0.2以后 Oracle 研发将基表进行改善。增加了KTSSOSQLID字段，代表了当前使用临时段的SQL_ID. 但是到现在最新的12C 仍没有调整相关的视图。 因此可以使用如下SQL 代替：
select k.inst_id "INST_ID", ktssoses "SADDR", sid, ktssosno "SERIAL#", username "USERNAME", osuser "OSUSER", ktssosqlid "SQL_ID", ktssotsn "TABLESPACE", decode(ktssocnt, 0, 'PERMANENT', 1, 'TEMPORARY') "CONTENTS", decode(ktssosegt, 1, 'SORT', 2, 'HASH', 3, 'DATA', 4, 'INDEX', 5, 'LOB_DATA', 6, 'LOB_INDEX' , 'UNDEFINED') "SEGTYPE", ktssofno "SEGFILE#", ktssobno "SEGBLK#", ktssoexts "EXTENTS", ktssoblks "BLOCKS", round(ktssoblks*p.value/1024/1024, 2) "SIZE_MB", ktssorfno "SEGRFNO#" from x$ktsso k, v$session s, (select value from v$parameter where name='db_block_size') p where ktssoses = s.saddr and ktssosno = s.serial#;
- V$TEMPSEG_USAGE
此视图中的内容，为临时段的使用情况. 在12C中已废弃。因为这个视图其实就是v$sort_usage.在这里就不再过多详述。 先来看下视图的创建语句：
-- 11G SQL> select dbms_metadata.get_ddl('SYNONYM','V$TEMPSEG_USAGE','PUBLIC') FROM DUAL; CREATE OR REPLACE PUBLIC SYNONYM "V$TEMPSEG_USAGE" FOR "SYS"."V_$SORT_USAGE" SQL> SELECT dbms_metadata.get_ddl('VIEW','V_$SORT_USAGE','SYS') from dual; CREATE OR REPLACE FORCE VIEW "SYS"."V_$SORT_USAGE" ("USERNAME", "USER", "SESSION_ADDR" , "SESSION_NUM", "SQLADDR", "SQLHASH", "SQL_ID", "TABLESPACE", "CONTENTS", "SEGTYPE" , "SEGFILE#", "SEGBLK#", "EXTENTS", "BLOCKS", "SEGRFNO#") AS select "USERNAME","USER","SESSION_ADDR","SESSION_NUM","SQLADDR","SQLHASH","SQL_ID" ,"TABLESPACE","CONTENTS","SEGTYPE","SEGFILE#","SEGBLK#","EXTENTS","BLOCKS","SEGRFNO#" from v$sort_usage - V$TEMP_EXTENT_POOL
此视图也是v$sort_usage的同义词。 记录了临时空间缓存与使用的状态。假如临时空间缓存缓慢可能导致该实例hang住。
- 基表
此视图的基表是x$ktstfc。
-- 12C SQL> SELECT OWNER,OBJECT_TYPE FROM DBA_OBJECTS WHERE OBJECT_NAME='V$TEMP_EXTENT_POOL'; OWNER OBJECT_TYPE ------------------------------ ------------------- PUBLIC SYNONYM SQL> SET LINES 32767 PAGES 50000 SQL> COL OWNER FOR A8 SQL> COL SYNONYM_NAME FOR A18 SQL> COL TABLE_OWNER FOR A30 SQL> COL TABLE_NAME FOR A13 SQL> COL DB_LINK FOR A10 SQL> select * from dba_synonyms where synonym_name='V$TEMP_EXTENT_POOL'; OWNER SYNONYM_NAME TABLE_OW TABLE_NAME DB_LINK ORIGIN_CON_ID -------- ------------------ -------- ------------------------------ ---------- ------------- PUBLIC V$TEMP_EXTENT_POOL SYS V_$TEMP_EXTENT_POOL 1 -- 看到这里应该反应过来，v$* 是V_$*的同义词,v_$*是v$*的视图。Oracle 在$ORACLE_HOME/rdbms/admin/catspace.sql 中包含 了此类对象的创建语句。遇到此情况，我们可以通过V$fixed_view_definition 查询视图名为gv$*的创建语句。得出的才是该 视图出现的根源。 SQL> set heading off SQL> select view_definition from v$fixed_view_definition where view_name='GV$TEMP_EXTENT_POOL'; select /*+ ordered use_nl(fc) */ fc.inst_id, ts.name, fc.ktstfctfno, fc.ktstfcec, fc.ktstfceu , fc.ktstfcbc, fc.ktstfcbu, fc.ktstfcbc*ts.blocksize, fc.ktstfcbu*ts.blocksize, fc.ktstfcfno, fc.con_id from ts$ ts, x$ktstfc fc where ts.contents$ = 1 and ts.bitmapped <> 0 and ts.online$ = 1 and ts.ts# = fc.ktstfctsn -- 11G select /*+ ordered use_nl(fc) */ fc.inst_id, ts.name, fc.ktstfctfno, fc.ktstfcec, fc.ktstfceu , fc.ktstfcbc, fc.ktstfcbu, fc.ktstfcbc*ts.blocksize, fc.ktstfcbu*ts.blocksize, fc.ktstfcfno from ts$ ts, x$ktstfc fc where ts.contents$ = 1 and ts.bitmapped <> 0 and ts.online$ = 1 and ts.ts# = fc.ktstfctsn
从上面创建语句来看，11G 与12C 之间区别就在于增加了一个字段:con_id （container_id)
- 字段说明
Table 5: v$temp_extent_pool 字段名 数据类型 说明 版本 INST_ID NUMBER 实例编号 TABLESPACE_NAME NOT NULL VARCHAR2(30) 表空间名 FILE_ID NUMBER 文件绝对编号 EXTENTS_CACHED NUMBER 真实的被cache的extents EXTENTS_USED NUMBER 真实的被使用的extents BLOCKS_CACHED NUMBER 缓存的块数量 BLOCKS_USED NUMBER 正在被使用的块数量 BYTES_CACHED NUMBER 缓存的数据量 BYTES_USED NUMBER 正在被使用的数据量 RELATIVE_FNO NUMBER 相对文件号 CON_ID NUMBER Container id 0: 没有CDB或者数据属于整个CDB 1: 属于root n: container id - V$TEMP_SPACE_HEADER
此视图显示的是 本地管理 模式的临时表空间中，每个文件的在文件头中记录的已使用空间和剩余空间的信息。
在这里列出此视图，一方面有兴趣分析临时段数据块的人知道可以从此视图查询头部信息。另一方面需要提示的是：
- note
此视图bytes_used 字段并不是真实分配的临时数据量大小。此视图显示的是tempfile的初始化大小，与当前数据库使用了多少 临时段无关。想要取得准确的临时段的使用量(占用临时段的真实数据量)，需要去查询V$sort_segment,v$sort_usage及 v$tempseg_usage视图。
- 基表
此视图的基表是: x$ktfthc. 视图V$temp_space_header 是在gv$temp_space_header 基础上查询当前实例数据得到的。所以我们来看gv$temp_space_header
select view_definition from v$fixed_view_definition where view_name='GV$TEMP_SPACE_HEADER'; VIEW_DEFINITION -------------------------------------------------------------------------------------------------- select /*+ ordered use_nl(hc) */ hc.inst_id, ts.name, hc.ktfthctfno, (hc.ktfthcsz - hc.ktfthcfree)*ts.blocksize , (hc.ktfthcsz - hc.ktfthcfree), hc.ktfthcfree*ts.blocksize, hc.ktfthcfree, hc.ktfthcfno from ts$ ts, x$ktfthc hc where ts.contents$ = 1 and ts.bitmapped <> 0 and ts.online$ = 1 and ts.ts# = hc.ktfthctsn and hc.ktfthccval = 0
字段
Table 6: gv$temp_space_header 视图字段 字段类型 说明 版本 INST_ID NUMBER 实例编号 TABLESPACE_NAME NOT NULL VARCHAR2(30) 表空间 FILE_ID NUMBER 文件编号 BYTES_USED NUMBER 初始化文件大小 BLOCKS_USED NUMBER 初始化文件块数 BYTES_FREE NUMBER 可用空间 BLOCKS_FREE NUMBER 可用块数 RELATIVE_FNO NUMBER 相对文件编号 CON_ID container id 12C
4.2.2 存储内容
临时表空间主要存储会话持续期间的临时数据及排序数据。主要存储内容如下：
- 未完成的排序数据 (intermediate sort results)
- 临时表及临时索引 (Temporary tables and temporary indexes)
- 临时大对象 (Temporary LOBS)
- 临时B－trees (Temporary B-trees)
4.2.3 临时表空间组
oracle从10G 开始提供了临时表空间组。这样一个用户可以同时使用多个临时表空间。
临时表空间组是不能显示创建的，没有 create tablespace group 这样的语句，在创建临时表空间的时候可以加相应关键词指定临时表空间组,后台会自动创建。当该组内所有临时表空间成员全部被删除时，组也会自动删除。
create temporary tablespace temp1 datafile '+temptbs/temp01.dbf' size 10G autoextend off management local uniform size 1M tablespace group tempgroup;
4.2.4 排序段分配
数据库启动后，第一个需要进行排序操作的语句需要进行排序时（或者第一个临时表需要存储数据时)，会向临时表空间请求 sort segment,当排序操作结束后，所分配的extents 并不会完全释放，只会标记状态为 available 状态。直到数据库关闭。
同一个实例中的所有排序操作，都会共享该 sort segemnt . 排序所占用的临时段，可以通过v$sort_segment来查看。
4.2.5 运维操作
以下示例都是最基本的示例。
- 创建
CREATE TABLESPACE &tablespace_name TEMPFILE '&file_name' size &file_size autoextend off;
- 添加文件
ALTER TABLESPACE &TABLESPACE_NAME ADD TEMPFILE '&file_name' size &file_size autoextend off;
- 删除文件
ALTER TABLESPACE &tablespace_name DROP TEMPFILE '&file_name'; ALTER TABLESPACE &tablespace_name DROP TEMPFILE &file_id;
- 调整文件大小
ALTER DATABASE TEMPFILE '&file_name' RESIZE &file_size; ALTER DATABASE TEMPFILE &file_id RESIZE &file_size;
- 收缩碎片
从Oracle Database11g 版本1 开始，可使用ALTER TABLESPACESHRINK SPACE 命令收缩临时表空间，也可以使用ALTER TABLESPACE SHRINK TEMPFILE 命令收缩临时文件。对于这两个命令，可以指定可选的KEEP 子句，该子句定义了表空间/临时文件可收缩到的下限。 如果忽略KEEP 子句，则只要满足其它存储属性，数据库就会尽可能尝试收缩表空间/临时文件（所有当前使用的区的总空间）。此操作需联机执行。但是，如果所分配的当前使用的一些区超出了收缩估计值，系统将等待这些区被释放以完成收缩操作。 ALTER DATABASETEMPFILE RESIZE 命令通常会因ORA-03297 而失败，因为临时文件包含的已用数据超过了所需的RESIZE 值。 与ALTER TABLESPACE SHRINK 相反，ALTER DATABASE 命令不会在排序区分配后尝试取消分配。
-- 收缩表空间 ALTER TABLESPACE &tbs_name SHRINK SPACE; -- 收缩临时文件 ALTER TABLESPACE SHRINK TEMPFILE '&file_name';
- 手动触发清理临时段
alter session set events 'immediate trace name DROP_SEGMENTS level ts#+1';
ts# 指的是临时表空间的表空间编号。
- 重命名临时表空间文件
-- OFFILNE 临时表空间文件 ALTER DATABASE TEMPFILE '&file_name' OFFLINE; -- 操作系统上移动或者重命名文件 mv '$original_file' '&target_file' -- 数据库中重命名文件 ALTER DATABASE RENAME FILE '&original_file' to '&target_file'; -- 将文件ONLINE ALTER DATABASE TEMPFILE '&target_file' ONLINE;
- 设置默认临时表空间
alter database default temporary tablespace temp;
- 删除表空间
DROP TABLESPACE &tbs_name;
- 查看临时表空间使用率
从Oracle 11G 开始，可以通过dba_temp_free_space 查看剩余可用空间。但是在11G 之前,我们可以通过gv$temp_space_header 视图 进行计算。语句可参照dba_temp_free_space 视图进行调整。
select * from dba_temp_free_space
- 查看排序段大小
col "Name" for a10 col "Size (M)" for a14 col "HWM (M)" for a14 col "HWM % " for a8 col "Using (M)" for a12 col "Using %" for a8 SELECT a.tablespace_name "Name", TO_CHAR(NVL(a.bytes / 1024 / 1024, 0), '99,999,990.90') "Size (M)", TO_CHAR(NVL(t.hwm, 0) / 1024 / 1024, '99999999.99') "HWM (M)", TO_CHAR(NVL(t.hwm / a.bytes * 100, 0), '990.00') "HWM % ", TO_CHAR(NVL(t.bytes / 1024 / 1024, 0), '99999999.99') "Using (M)", TO_CHAR(NVL(t.bytes / a.bytes * 100, 0), '990.00') "Using %" FROM (select tablespace_name, sum(decode(AUTOEXTENSIBLE,'YES',MAXBYTES,bytes)) bytes from dba_temp_files group by tablespace_name) a, (select tablespace_name, sum(bytes_cached) hwm, sum(bytes_used) bytes from v$temp_extent_pool group by tablespace_name) t WHERE a.tablespace_name = t.tablespace_name(+); - 查看占用排序段的会话
col username for a8 col sid for 9999999 col serial# for 9999999 col event for a40 col osuser for a10 col tablespace for a10 col sql_text for a40 SELECT a.username, a.sid,a.serial#, a.event,a.osuser, b.tablespace, b.blocks, c.sql_text FROM v$tempseg_usage b left join v$session a on a.saddr = b.session_addr left join v$sqlarea c on c.address= a.sql_address AND c.hash_value = a.sql_hash_value where a.sid=6077 ORDER BY b.tablespace, b.blocks;
4.3 TODO UNDO表空间
4.3.1 undo 分类
system undo in-memory undo LOBS undo
4.3.2 Automatic UNDO Retention
Automatic UNDO Retention是10g的新特性，在10G R2 和之后的版本的数据库，这个特性是默认启用的。
其自动调整的规则如下： 当UNDO表空间不会自动扩展时，tuned_undoretention的值是根据UNDO表空间大小的百分比 来计算的，特别是UNDO表空间又比较大的时候,在一些情况下会将tuned_undoretention的值 会被自动调整得特别大。
这个问题，从10GR2至11GR2 一直都存在。在12C 中，至今未发现。有待验证。
针对这种情况，我们可以通过以下三种方法来解决:
开启自动扩展，但是最大大小设置为当前大小
ALTER DATABASE DATAFILE '<datafile_flename>' AUTOEXTEND ON MAXSIZE <current_size>;
操作后，undo retention 将取(MAXQUERYLEN secs + 300) 和UNDO_RETENTION的最大值。
关闭自动调优UNDO_RETENTION
alter system set "_undo_autotune" = false sid='*' scope=both;
设置此参数后，undo rentention 将以参数undo_retention的值为准。
设置隐含参数
alter system set "_smu_debug_mode"=33554432 sid='*' scope=both;
设置此参数后，undo_retention的计算规则为：(MAXQUERYLEN secs + 300) 和UNDO_RETENTION 两者之间取最大值使用。
4.3.3 undo segment
oracle 将事务的dml操作进行收集和记录,这些被记录的数据就是undo data. Undo Data 在oracle 数据库中的作用:
- 回滚一个活动会话(Roll back an active transaction )
- 恢复已被终止的会话(Recover a terminated transaction)
- 提供数据一致性读(Provide read consistency)
- 为逻辑上的闪回操作提供支持(Perform some logical flashback operations)
Undo data 被保存在undo表空间中. Oracle提供一种全自动的机制来管理undo段和undo表空间中的空间,这种机制被称为: automatic undo management mode(undo自动管理模式)
- Undo Segments and Transactions
When a transaction starts, the database binds (assigns) the transaction to an undo segment, and therefore to a transaction table, in the current undo tablespace. In rare circumstances, if the database instance does not have a designated undo tablespace, then the transaction binds to the system undo segment. Multiple active transactions can write concurrently to the same undo segment or to different segments. For example, transactions T1 and T2 can both write to undo segment U1, or T1 can write to U1 while T2 writes to undo segment U2. Conceptually, the extents in an undo segment form a ring. Transactions write to one undo extent, and then to the next extent in the ring, and so on in cyclical fashion. Figure 12-20 shows two transactions, T1 and T2, which begin writing in the third extent (E3) of an undo segment and continue writing to the fourth extent (E4). Figure 12-20 Ring of Allocated Extents in an Undo Segment
4.3.4 Undo 段与会话的关系(Undo Segments and Transactions)
When a transaction starts, the database binds (assigns) the transaction to an undo segment, and therefore to a transaction table, in the current undo tablespace. In rare circumstances, if the database instance does not have a designated undo tablespace, then the transaction binds to the system undo segment. 当一个会话开始时,会与一个回滚段建立固定关系,并因此被记录到当前undo表空间的transaction table中.在一些特殊的环境中,比如数据库没有指定明确的undo表空间,会话会绑定到system undo表空间中. transaction table: The data structure within an undo segment that holds the transaction identifiers of the transactions using the undo segment. 会话表,指的是一个undo段的结构,在这个Undo段中保存着所有使用Undo段的会话的标识. Multiple active transactions can write concurrently to the same undo segment or to different segments. For example, transactions T1 and T2 can both write to undo segment U1, or T1 can write to U1 while T2 writes to undo segment U2. 多个活动会话,可同时向同一个undo段中写入数据,也可以向不同的undo段写入数据. Conceptually, the extents in an undo segment form a ring. Transactions write to one undo extent, and then to the next extent in the ring, and so on in cyclical fashion. Figure 12-20 shows two transactions, T1 and T2, which begin writing in the third extent (E3) of an undo segment and continue writing to the fourth extent (E4). 从概念上来讲,undo段中的区(extents)形成一个环形.会话首先向undo段中的一个extent写数据,然后向下一个,以循环的模式逐一向下一个extent中写入数据.如图:
Figure 12-20 Ring of Allocated Extents in an Undo Segment
Description of "Figure 12-20 Ring of Allocated Extents in an Undo Segment "
At any given time, a transaction writes sequentially to only one extent in an undo segment, known as the current extent for the transaction. Multiple active transactions can write simultaneously to the same current extent or to different current extents. Figure 12-20 shows transactions T1 and T2 writing simultaneously to extent E3. Within an undo extent, a data block contains data for only one transaction. As the current undo extent fills, the first transaction needing space checks the availability of the next allocated extent in the ring. If the next extent does not contain data from an active transaction, then this extent becomes the current extent. Now all transactions that need space can write to the new current extent. In Figure 12-21, when E4 is full, T1 and T2 continue writing to E1, overwriting the nonactive undo data in E1. Description of "Figure 12-20 Ring of Allocated Extents in an Undo Segment "
At any given time, a transaction writes sequentially to only one extent in an undo segment, known as the current extent for the transaction. Multiple active transactions can write simultaneously to the same current extent or to different current extents. Figure 12-20 shows transactions T1 and T2 writing simultaneously to extent E3. Within an undo extent, a data block contains data for only one transaction. As the current undo extent fills, the first transaction needing space checks the availability of the next allocated extent in the ring. If the next extent does not contain data from an active transaction, then this extent becomes the current extent. Now all transactions that need space can write to the new current extent. In Figure 12-21, when E4 is full, T1 and T2 continue writing to E1, overwriting the nonactive undo data in E1. –查看会话的UNDO使用量 SELECT 'ps -ef | grep '||p.spid, s.username, s.sid, s.serial#, s.username, s.osuser, s.machine, s.program, s.event, sq.sql_text, t.ubafil "UBA filenum", t.ubablk "UBA block number", t.used_ublk, t.used_ublk*a.value/(1024*1024) undo_size_m, t.start_time, t.status, t.start_scnb, t.xidusn rollid, t.name rollname FROM v$session s,v$transaction t,v$rollname r,v$sql sq,v$process p,v$parameter a WHERE s.saddr = t.ses_addr AND s.sql_id = sq.sql_id AND t.xidusn = r.usn AND s.paddr = p.addr AND a.name = 'db_block_size' AND s.event like 'PX%';
col username for a8 col module for a20 col sess_status for a10 col event for a30 col segment_name for a23
select s.sid,s.serial#,s.username,s.module,s.sql_id,s.state as sess_status,s.event, v.usn,segment_name,r.status as undoseg_status, v.rssize/1024/1024 mb From dba_rollback_segs r, v$rollstat v,v$transaction t,v$session s Where r.segment_id = v.usn and v.usn=t.xidusn and t.addr=s.taddr order by segment_name ;
Oracle的一些内部事件允许设置debug_mode，以便允许在AUM模式下，手工进行回滚段的处理： alter session set "_smu_debug_mode"=4; alter rollback segment "_SYSSMU7$" ONLINE; 或者使用隐含参数offline_rollback_segments进行指定回滚段处理： _offline_rollback_segments drop rollback segment ‘xxx’
1494886.1 –Troubleshooting Database Transaction Recovery
除了极少数undo坏块,undo文件丢失外，大部分undo异常是因为redo未被正常进行前滚，从而导致undo回滚异常数据库无法open，解决此类问题，一般需要结合redo异常处理技巧在其中，一般undo异常处理思路 1.切换undo_management= MANUAL尝试启动数据库，如果不成功进入2 2.设置10513 等event尝试启动数据库，如果不成功进入3 3.使用_offline_rollback_segments/_corrupted_rollback_segments屏蔽回滚段 4.如果依然不能open数据库,考虑使用bbed工具提交事务，修改回滚段状态等操作 5.如果依然还不能open数据库，考虑使用dul
4.3.5 undo 回滚
在生产系统上偶尔会遇到undo表空间不足，遇到ORA-1555错误后会话退出，或者没有经验的DBA kill 掉执行时间过长的会话， 都有可能会大事务回滚或者死事务回滚。
预估回滚时长
declare
l_start number;
l_end number;
begin
select ktuxesiz
into l_start
from x$ktuxe
where KTUXEUSN = 10
and KTUXESLT = 39; ---------这里根据实际数字来填写
dbms_lock.sleep(60); ---------可以缩小这个时间，但是太小，可能会导致误差较大
select ktuxesiz
into l_end
from x$ktuxe
where KTUXEUSN = 10
and KTUXESLT = 39; ---------这里根据实际数字来填写
dbms_output.put_line('time cost Day:' ||
round(l_end / (l_start - l_end) / 60, 2));
end;
/
4.3.6 如何调整合理的UNDO
4.3.7 UNDO 相关参数
4.3.8 UNDO 损坏后的处理方法
4.4 参数
undo_retention: undo 记录的保留时长. 但是这个参数需要另外一个选项(noguarantee/ Guarantee)相配合,才能决定是否严格按照undo_retention参数的设置来保留undo记录。
下面是指定guarantee的命令:
方式1: 创建表空间时指定 CREATE UNDO TABLESPACE UNDOTBS01 DATAFILE ‘E:/oracle/product/10.2.0/oradata/keymen/UNDOTBS01.DBF’ SIZE 500M AUTOEXTEND ON RETENTION GUARANTEE; -----> 指定为guarantee的方式 方式2: 后期修改调整 ALTER DATABASE UNDOTBS1 RETENTION GUARANTEE; ----->指定为guarantee的方式 ALTER DATABASE UNDOTBS1 RETENTION NOGUARANTEE;-----> 指定为noguarantee的方式
guarantee 与noguarantee有什么区别呢？ undo_retention的默认值 是900秒.
在noguarantee的情况下，ORACLE并不能保证能够将undo信息存储900秒，如果undo表 空间不足，那么ORACLE将忽略undo_retention的设置，直接覆盖掉以前的undo,这个时候 有可能会产生ORA-01555错误。如果undo表空间空间足够，那么undo将会保存很长一段时 间，直到undo表空间达到maxsize，这个时候才会覆盖undo信息，而且ORACLE会从最初的 undo信息开始覆盖。
在有guarantee的情况下，ORACLE将会保证undo信息能够保存到undo_retention设定的值 之后才被覆盖，如果这个时候同时执行了很多事物，将undo表空间耗完了,那么那个事物 会失败，会报ORA-30036 错误，所以使用guarantee一定要慎用，如果非要使用guarantee 那么尽量将undo 表空间设大 一点。
4.5 系统表空间
4.5.1 SYSTEM
4.5.2 SYSAUX
- 清理SYSAUX
检查SYSAUX数据组成
COL Item FOR A30 COL Schema FOR A30 SELECT occupant_name "Item", space_usage_kbytes / 1024 "Space Used (MB)", schema_name "Schema", move_procedure "Move Procedure" FROM v$sysaux_occupants ORDER BY 2 desc ;
查询示例:
一般来说，出现比较多的情况是AWR和audit审计信息占用较多空间。审计信息保存在AUD$表中，可以truncate 该表， 也可以使用DBMS_AUDIT_MGMT.move_dbaudit_tables 存储过程将该表迁移到其他表空间。
而AWR的数据，在11G 中由于存在BUG，不能触发自动分区，由于没有新的分区生成，会有一个分区的长期不过期不在清理 范围内，该分区有部分数据过期，但是没有自动清理。 而且没有迁移数据的存储过程，我们只能自己手动每张表逐一处理。
清理AWR数据
紧急情况下，可以找到应保留的最小snap_id , 将该snap_id之前的数据清除掉。不紧张的情况下，可以通过调整 隐含参数_swrf_test_action = 72 ，手动触发Oracle将awr相关表进行分区，待到历史分区过期后，oracle 会自动清理历史分区数据。
诚然, oracle 本身也提供了清理AWR数据的存储过程DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE， 但是由于效率低下，并且不能自动回收空间，大部分DBA不太喜欢使用，而是采用自己编写的脚本。
检查实际保留时间与设定时间的差异
col ash for a30 col SNAP for a30 col RETENTION for a30 select sysdate - a.sample_time ash, sysdate - s.begin_interval_time snap, c.RETENTION from sys.wrm$_wr_control c, ( select db.dbid, min(w.sample_time) sample_time from sys.v_$database db, sys.Wrh$_active_session_history w where w.dbid = db.dbid group by db.dbid ) a, ( select db.dbid, min(r.begin_interval_time) begin_interval_time from sys.v_$database db, sys.wrm$_snapshot r where r.dbid = db.dbid group by db.dbid ) s where a.dbid = s.dbid and c.dbid = a.dbid;
查询实际应保留的最小snap_id
SELECT MIN(SNAP_ID),MAX(SNAP_ID) FROM DBA_HIST_SNAPSHOT;
检查历史数据最小snap_id
SELECT MIN(SNAP_ID),MAX(SNAP_ID) FROM DBA_HIST_ACTIVE_SESS_HISTORY;
停止AWR采集
停止 AWR 信息采集有两种方法，一种是 调整statistic_level 为basic。另外一种是调整awr信息采集频率为0.
alter system set statistics_level='BASIC' scope=both;
exec dbms_workload_repository.modify_snapshot_settings(interval => 0);
生成备份、删除、回插数据的SQL
select distinct 'create table '||segment_name||'27 as select * from '||segment_name||' where snap_id>=&min_snap_id;'||chr(10)|| 'truncate table '||segment_name||';'||chr(10)|| 'insert into '||segment_name||' select * from '||segment_name||'27;'||chr(10)|| 'drop table '||segment_name||'27;' from dba_segments where segment_name like 'WRH$%' and segment_type like 'TABLE%' and segment_name not in ('WRH$_WAITCLASSMETRIC_HISTORY', 'WRH$_LATCH_MISSES_SUMMARY_BL', 'WRH$_ACTIVE_SESSION_HISTORY_BL');修复索引
select a.table_owner, a.table_name, d.partitioned as tab_partitioned, decode(a.partitioned,'NO',a.owner, decode(b.subpartition_count,0, b.index_owner, --分区 c.index_owner --子分区 )) as index_owner, decode(a.partitioned,'NO',a.index_name, decode(b.subpartition_count,0, b.index_name, --分区 c.index_name --子分区 )) as index_name, b.partition_name, c.subpartition_name, a.status as index_status, b.status as ind_partition_status, c.status as ind_subpartition_status, decode(a.partitioned, 'NO', decode(d.partitioned,'YES', -- 表分区，索引未分区 decode(a.index_type,'UNIQUE', 'alter index ' ||a.owner || '.' || a.index_name || ' rebuild ', -- 唯一索引不进行分区重建 'DROP INDEX '|| a.owner || '.' || a.index_name ||';'||CHR(10)|| 'CREATE INDEX '||a.owner||'.'||a.index_name||' on '|| a.table_owner||'.'||a.table_name||'('||f.cols||') local '), -- 将分区表中的全局索引自动重建为分区索引 'alter index ' ||a.owner || '.' || a.index_name || ' rebuild '), -- 全表 decode(b.subpartition_count,0, 'alter index ' ||b.index_owner || '.' || b.index_name || ' rebuild partition ' || b.partition_name, --分区 c.index_owner || '.' || c.index_name || ' rebuild subpartition ' || c.subpartition_name --子分区 )) || ' online parallel '|| e.value/2||';'||chr(10)|| 'alter index '||a.owner || '.' || a.index_name ||' noparallel;' as sql_rebuild from dba_indexes a left join dba_ind_partitions b on a.owner = b.index_owner and a.index_name = b.index_name left join dba_ind_subpartitions c on b.index_owner = c.index_owner and b.index_name = c.index_name and b.partition_name = c.partition_name left join (SELECT DISTINCT INDEX_OWNER,INDEX_NAME,TABLE_OWNER, TABLE_NAME, LISTAGG(COLUMN_NAME, ',') WITHIN GROUP(ORDER BY COLUMN_POSITION) OVER(PARTITION BY INDEX_OWNER, INDEX_NAME, TABLE_OWNER, TABLE_NAME) as cols FROM DBA_IND_COLUMNS) f on a.table_owner = f.table_owner and a.table_name = f.table_name and a.owner = f.index_owner and a.index_name = f.index_name ,dba_tables d ,v$parameter e where (a.status = 'UNUSABLE' OR b.status = 'UNUSABLE' or c.status = 'UNUSABLE') and d.owner = a.table_owner and d.table_name = a.table_name and e.name='cpu_count';恢复数据采集 根据每4步操作，做相应的恢复
alter system set statistics_level='TYPICAL' scope=both;
或者
exec dbms_workload_repository.modify_snapshot_settings(interval => 60);
- 调整SYSAUX表空间大小 方法参见resize数据文件.
- 清理审计数据 truncate sys.aud$;
5 管理方式
ORACLE通过表空间为数据库提供使用空间。由于区(extent)是ORACLE创建对象时的最小分配单元, 所以表空间的管理实际上就是基于extent的空间管理。
我们知道，表空间是由物理文件组成的。逻辑上表空间是由segment组成的，segment由extent组成， extent(一般翻译为区，也有直译为扩展),extent由block组成。即 tablespace <— segment <— extent <— block
表空间的管理实际上就是为extent/segment/block的管理.
5.1 extent
Oracle 表空间管理，实际就是extent的管理。对于extent,oracle不同的版本管理方式可能是不一样的。 在8i之前采用数据字典管理表空间(DMT) ，通过uet$和fet$进行管理。可是从8i开始引入了本地管理表空间方式（LMT).
字典管理
字典(dictionary)管理方式,是以基表来记录、控制区的分配与回收等信息。9i 之前，Oracle使用的是这种方式，按当前版本 (12c),这种管理方式已经很古老了。知道这种管理方式,算是对历史的了解。这里不做过多的研究。
这种方式存在严重的性能问题:
- 速度慢
- 单线程，易争用
- 产生redo和undo信息
本地管理
这种管理方式，是将表空间中extent的分配和回收相关信息保存在数据文件中。表空间为每个数据文件维护一个位图结构,用 于记录表空间的区分情况。使用这种方式,消除或者减少了原来字典管理方式的弊端,不产生redo和undo, 所以速度要快很多。
oracle 在每个数据文件的头部加入了一个位图区域。该文件的第一个段中，前三个块用于存储位图信息：
下面有两种说法，到底哪种说法是正确的？
第一个段的第一个区的第一个块是firstlevelbitmapblock 第二个块是secondlevelbitmapblock 第三个块才是段头块 这两个块是用来管理freeblock
- 第一部分: First Level Bitmap Block
- 第二部分: Second Level Bitmap Block
- 第三部分: segment header
为什么这里都是按部分来说而不是块呢？那是因为每一部分可能包含多个数据块。
其实区的管理，就是区的分区的相关的问题：是否自动分配及每次分配多少的问题。
extent management local {autoallocate|uniform size n K/M}
5.2 segment
对于段来说，管理方式有两种：MSSM(Manual Segment Space Management)和ASSM(Automatic Segment Space Management).
在9i之前采用的是mssm手动段空间管理技术，采用了是在数据段头加入free list进行管理，可是往往出现性能问题 （如 buffer busy wai），以此到9i开始引入了assm自动段管理方式。
在11g中存在延迟段，且在第一次分配区间的时候，在11.2.0.4版本中1-2数据块为数据文件头部信息，3-7为extend位图信息， 8-10为段位图信息。
5.3 block
oracle 数据块，有数据块，索引块，Undo 块，临时块等与表空间相对应。
5.3.1 rowid
5.3.2 ora_rowscn
ORA_ROWSCN伪列是Oracle10g引入的，可以查询表中记录最后变更的SCN。这个新的伪列在某些环境下会非常有用，比如执行乐观锁定， 或者增量数据抽取的时候。但是，默认情况下，每行记录的ORA_ROWSCN是基于Block的(norowdependencies)，除非在建表的时候执 行开启行级跟踪(createtable … rowdependencies).
- norowdependencies
- 此时的ora_rawscn 取自data block header的SCN，那么这时候，对于同一个block里的row而言，他们的ora_rowscn 是一样的
- rowdependencies
- 每行row 保存一个ora_rowscn. 这样对于同一个block里的row，会有多个ora_rowscn 值。
通过dump block，可以发现每个 row 会多出一个dscn的信息，该信息就是用来保存ora_rowscn的.
6 物理结构
7 表空间运维
7.1 创建
7.2 删除
7.3 重命名表空间
7.4 重建UNDO表空间
查看默认UNDO表空间
show parameter undo_tablespace
创建新的UNDO表空间
create undo tablespace UNDOTBS2 datafile '/oradata/orcl/undotbs201.dbf' size 1G autoextend on;
将新undo表空间设置为默认表空间
alter system set undo_tablespace=UNDOTBS2 scope=both;
检查是否有会话仍在使用原有undo表空间
select SEGMENT_NAME ,STATUS ,TABLESPACE_NAME from dba_rollback_segs where status='ONLINE';
删除原undo表空间
在原undo表空间中无在线undo段时，才可以删除undo表空间。
drop tablespace UNDOTBS1 including contents and datafiles;
7.5 重建临时表空间
7.6 重命名数据文件
10G、11G 与12C 之后，数据文件的重命名方式有了很大的变化。12C 之后只需要一条命令。
10G/11G 一种方法是表空间offline后, rename 数据文件。示例如下:
-- offline 表空间 alter tablespace test offline; -- 移动文件 -- 修改控制文件 alter database rename file '/u01/oracle/oradata/test01.dbf' to '/home/oracle/oradata/test.dbf'; 或者 alter tablespace test rename datafile '/u01/oracle/oradata/test01.dbf' to'/home/oracle/oradata/test.dbf'; -- online 表空间 alter tablespace test online;
示例如下:
SQL> archive log list Database log mode No Archive Mode Automatic archival Disabled Archive destination USE_DB_RECOVERY_FILE_DEST Oldest online log sequence 31 Current log sequence 33 SQL> alter database datafile '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/repdata1.db' rename to '/u01/app/oracle/data/wttest/db601/repdata1.dbf'; alter database datafile '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/repdata1.db' rename to '/u01/app/oracle/data/wttest/db601/repdata1.dbf' * ERROR at line 1: ORA-01916: keyword ONLINE, OFFLINE, RESIZE, AUTOEXTEND or END/DROP expected SQL> alter tablespace repdata offline; Tablespace altered. SQL> ho mv /u01/app/oracle/product/11.2.0/dbhome_1/dbs/repdata1.db /u01/app/oracle/data/wttest/db601/repdata1.dbf SQL> alter tablespace repdata online;^A^C SQL> alter database rename file '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/repdata1.db' to '/u01/app/oracle/data/wttest/db601/repdata1.dbf'; Database altered. SQL> alter tablespace repdata online; Tablespace altered.在归档模式下，也可以针对某个文件进行offline操作后rename,然后再online.
而对于ASM环境，则需要通过RMAN来实现:
rman> report schema; copy datafile '+DATA/orcl/datafile/user01.dbf' to '+DATA/orcl/datafile/user_01.dbf' run{ sql 'alter tablespace users offline'; set newname for datafile 4 to '+DATA/orcl/datafile/user01.dbf' ; restore datafile 4; switch datafile 4; recover datafile 4; sql 'alter tablespace users online'; }12C
12C 修改数据文件位置 ，只需要一个SQL:
alter database move datafile '/u01/app/oracle/oradata/test01.dbf' to '/u01/app/oracle/oradata/test.dbf';
7.7 添加数据文件
7.8 删除数据文件
7.9 RESIZE数据文件
alter database datafile '&file_name' resize $target_size;
使用下面语句，拼接出数据文件resize 语句,由于是从dba_extents 视图中查询，速度较慢。需要耐心等待。
select 'alter database datafile ' || file_id || ' resize ' ||
ceil(used_blocks * b.value / (1024 * 1024 * 1024)) || 'G;'
from (select /*+ rule */
distinct file_id,
last_value(block_id + blocks) over(partition by file_id order by block_id rows between unbounded preceding and unbounded following) as used_blocks
from dba_extents
where tablespace_name = '&TBS_NAME') a,
v$parameter b
where b.name = 'db_block_size';
并且计算出允许resize的最小值，从原理上来讲，这样统计出来的，应该是绝对可以用的。但是偶尔还会遇到错误
ORA-03297: file contains used data beyond requested RESIZE value
推测，可能是dba_extents 的数据更新不是绝对准确或者不及时。
7.10 OFFLINE 表空间
7.11 ONLINE 表空间
7.12 设置只读
8 脚本
8.0.1 检查表空间中对象大小
col owner for a8 col segment_name for a32 col partition_name for a32 col segment_type for a18 col size_gb for 9999999.99 col tablespace_name for a30 set linesize 300 pagesize 990 select owner,segment_name,partition_name,segment_type,sum(bytes)/(1024*1024*1024) size_gb,tablespace_name from dba_segments where -- owner = '' -- segment_name = '&segment_name' and -- segment_type = '&segment_type' AND tablespace_name = '&tbs_name' group by owner,segment_name,partition_name,segment_type,tablespace_name having sum(bytes)/(1024*1024*1024)>1 order by 5 desc;
8.0.2 检查用户数据在每个表空间的分布
col username for a10
col tablespace_name for a20
col default_tbs for a20
col total for 9999.99
col free for 9999.99
col user_ocupied for 9999.99
set linesize 200
set pagesize 10000
select b.username ,
c.tablespace_name,
decode(b.default_tablespace, e.tablespace_name, 'YES', 'NO') default_tbs,
d.total ,
c.free ,
round(sum(e.bytes) / (1024 * 1024 * 1024), 2) user_ocupied
from (select tablespace_name,
round(sum(bytes) / (1024 * 1024 * 1024), 2) free
from dba_free_space
group by tablespace_name) c,
(select tablespace_name,
round(sum(bytes) / (1024 * 1024 * 1024), 2) total
from dba_data_files
group by tablespace_name) d,
dba_segments e
left join dba_users b on e.owner = b.username
where c.tablespace_name = d.tablespace_name
and e.tablespace_name = d.tablespace_name
and b.username = 'SYS'
group by b.username,
decode(b.default_tablespace, e.tablespace_name, 'YES', 'NO'),
c.tablespace_name,
d.total,
c.free;
8.0.3 检查普通表空间使用率
set linesize 500 pagesize 999 head on
col tablespace_name for a21
col MAXEXTSIZE for 9999999.99
col MAXDFsize for 9999999.99
col used_gb for 999999.99
col free_gb for 999999.99
col pct_free for 999.99
col pct_used for 999.99
COL FSFI FOR 999.99
SELECT a.Tablespace_Name,
round(a.MAXEXT,2) MAXEXTSIZE,
ROUND(A.MAXDFS,2) MAXDFsize,
Round((greatest(a.maxdfs,a.MAXEXT) - (b.free_gb+a.MAXEXT-a.maxdfs)) , 2) Used_Gb,
round(a.maxext-a.maxdfs+b.free_gb, 2) Free_Gb,
round((a.maxdfs-b.free_gb)*100/a.maxdfs,2) as pct_used,
round((greatest(a.maxdfs,a.MAXEXT) - (b.free_gb+a.MAXEXT-a.maxdfs))*100/greatest(a.maxdfs,a.MAXEXT),2) ext_Pct_Used
--,round(b.fsfi,2) fsfi
FROM (SELECT Tablespace_Name,
Sum(Decode(Autoextensible, 'YES', Maxbytes, Bytes))/(1024*1024*1024) MAXEXT,
sum(bytes)/(1024*1024*1024) MAXDFS
FROM Dba_Data_Files
GROUP BY Tablespace_Name) a
left join (SELECT Tablespace_Name,
Sum(Bytes) / (1024 * 1024 * 1024) Free_Gb,
sqrt(max(blocks) / sum(blocks)) *
(100 / sqrt(sqrt(count(blocks)))) FSFI
FROM Dba_Free_Space
GROUP BY Tablespace_Name) b on a.Tablespace_Name =b.Tablespace_Name
ORDER BY Pct_Used desc;
8.0.4 查看表空间每天增长量
此统计依赖于AWR统计信息保留时长。默认保留7天或者8天（受版本的影响）
-- 11G 查询表空间每天空间增长量
with tmp as
(select rtime,
sum(tablespace_usedsize)/ (1024*1024*1024) tablespace_usedsize_gb,
sum(tablespace_size)/ (1024*1024*1024) tablespace_size_gb
from (select rtime,
e.tablespace_id,
(e.tablespace_usedsize) * (f.block_size) tablespace_usedsize,
(e.tablespace_size) * (f.block_size) tablespace_size
from dba_hist_tbspc_space_usage e,
dba_tablespaces f,
v$tablespace g
where e.tablespace_id = g.TS#
and f.tablespace_name = g.NAME
and f.contents not in ('TEMPORARY', 'UNDO'))
group by rtime)
select tmp.rtime,
tablespace_usedsize_Gb,
tablespace_size_gb,
(tablespace_usedsize_gb - LAG(tablespace_usedsize_gb, 1, NULL)
OVER(ORDER BY tmp.rtime)) AS DIFF_GB
from tmp,
(select max(rtime) rtime from tmp group by substr(rtime, 1, 10)) t2
where t2.rtime = tmp.rtime;
-- 12C 查询表空间每天空间增长量，
SELECT a.snap_id,
a.con_id,
e.name pdbname,
c.tablespace_name ts_name,
to_char(to_date(a.rtime, 'mm/dd/yyyy hh24:mi:ss'), 'yyyy-mm-dd hh24:mi') rtime,
round(a.tablespace_size * c.block_size / 1024 / 1024, 2) ts_size_mb,
round(a.tablespace_usedsize * c.block_size / 1024 / 1024, 2) ts_used_mb,
round((a.tablespace_size - a.tablespace_usedsize) * c.block_size / 1024 / 1024,
2) ts_free_mb,
round(a.tablespace_usedsize / a.tablespace_size * 100, 2) pct_used
FROM cdb_hist_tbspc_space_usage a,
(SELECT tablespace_id,
nb.con_id,
substr(rtime, 1, 10) rtime,
max(snap_id) snap_id
FROM dba_hist_tbspc_space_usage nb
group by tablespace_id, nb.con_id,substr(rtime, 1, 10)) b,
cdb_tablespaces c,
v$tablespace d,
V$CONTAINERS e
where a.snap_id = b.snap_id
and a.tablespace_id = b.tablespace_id
and a.con_id=b.con_id
and a.con_id=c.con_id
and a.con_id=d.con_id
and a.con_id=e.con_id
and a.tablespace_id=d.TS#
and d.NAME=c.tablespace_name
and to_date(a.rtime, 'mm/dd/yyyy hh24:mi:ss') >=sysdate-30
order by a.CON_ID,a.tablespace_id,to_date(a.rtime, 'mm/dd/yyyy hh24:mi:ss') desc;
9 调整文件路径
9.1 不停库
**
- 主要步骤： *
- 1、offline表空间：alter tablespace tablespace_name offline;*
- 2、复制数据文件到新的目录； *
- 3、rename修改表空间，并修改控制文件； *
- 4、online表空间； *
**
offline表空间zerone，使表空间zerone离线
alter tablespace zerone offline;
- 复制数据文件到新的目录
创建目录并授权
mkdir -p $target_path chown -R oracle:oinstall $target_path
复制数据文件
cp $original_file $target_file chown oracle:oinstall $target_file
rename修改表空间数据文件为新的位置，并修改控制文件
alter tablespace zerone rename datafile '&original_' to '&target_';
online表空间
alter tablespace &tablespace_name online;
9.2 停库
*
- 主要步骤：
- 1、关闭数据库；
- 2、复制数据文件到新的位置；
- 3、启动数据库到mount状态；
- 4、通过SQL修改数据文件位置；
- 5、打开数据库；
*
关闭数据库 关闭数据库尽量不要使用shutdown abort。
shutdown immediate;
复制数据文件到新的位置； 在复制之前检查确认路径是不是存在。如果不存在就先创建再授权，然后再复制 。
cp $original_file $target_file
启动数据库到mount状态
startup mount
通过SQL修改文件位置
alter database rename file '&original_file' to '&target_file';
可以通过以下脚本生成批量SQL:
set pagesize 999 set linesize 999 select 'alter database rename file '||''''||member||''''||' to '||chr(39)||replace(member,'&&original_path','&&target_path')||''';' from v$logfile where member like '&&orginal_path%'; select 'alter database rename file '||''''||name||''''||' to '||chr(39)||replace(name,'&&original_path','&&target_path')||''';' from v$datafile where name like '&&original_path%' select 'alter database rename file '||''''||name||''''||' to '||chr(39)||replace(name,'&&original_path','&&target_path')||''';' from v$tempfile where name like '&&original_path%'
打开数据库；
alter database open;
10 空间管理
Database Adminitrators' Guid –> 19 Managing Space for Schema Objects
Created: 2020-12-30 Wed 23:10
===================
天行健，君子以自强不息
地势坤，君子以厚德载物
===================
