|
Column |
Datatype |
NULL |
Description |
|
TABLESPACE_NAME |
VARCHAR2(30) |
|
Tablespace name |
|
USED_SPACE |
NUMBER |
|
Total space consumed by the tablespace,in database blocks |
|
TABLESPACE_SIZE |
NUMBER |
|
Total size of the tablespace,in database blocks |
|
USED_PERCENT |
NUMBER |
|
Percentage of used space,as a function of the maximum possible tablespace size |
从官网的说明来看，通过视图DBA_TABLESPACE_USAGE_METRICS可以很方便的查看各类型表空间的使用情况，包括永久、临时和undo表空间。但是，通过这个视图查询到的结果，和传统的SQL脚步查询到的结果不一致，而且相差很大。
select text from dba_views where view_name='DBA_TABLESPACE_USAGE_METRICS';
TEXT
-----------------------------------------------------------------
SELECT t.name, sum(f.allocated_space), sum(f.file_maxsize),
(sum(f.allocated_space)/sum(f.file_maxsize))*100
FROM sys.ts$ t, v$filespace_usage f
WHERE
t.online$ != 3 and
t.bitmapped <> 0 and
t.contents$ = 0 and
bitand(t.flags, 16) <> 16 and
t.ts# = f.tablespace_id
GROUP BY t.name, f.tablespace_id
union
SELECT t.name, sum(f.allocated_space), sum(f.file_maxsize),
(sum(f.allocated_space)/sum(f.file_maxsize))*100
FROM sys.ts$ t, v$filespace_usage f
WHERE
t.online$ != 3 and
t.bitmapped <> 0 and
t.contents$ <> 0 and
f.flag = 6 and
t.ts# = f.tablespace_id
GROUP BY t.name, f.tablespace_id
union
SELECT t.name, sum(f.allocated_space), sum(f.file_maxsize),
(sum(f.allocated_space)/sum(f.file_maxsize))*100
FROM sys.ts$ t, gv$filespace_usage f, gv$parameter param
WHERE
t.online$ != 3 and
t.bitmapped <> 0 and
f.inst_id = param.inst_id and
param.name = 'undo_tablespace' and
t.name = param.value and
f.flag = 6 and <------在12C之前，UNDO表空间在GV $ FILESPACE_USAGE中将其数据文件标记为值6，
t.ts# = f.tablespace_id
GROUP BY t.name, f.tablespace_id
可以看出，DBA_TABLESPACE_USAGE_METRICS查询到的关键数据出自v$filespace_usage视图。
|
Column |
Datatype |
Description |
|
TABLESPACE_ID |
ID of the tablespace to which the file belongs |
|
|
RFNO |
NUMBER |
Relative file number of the file |
|
ALLOCATED_SPACE |
NUMBER |
Total allocated space in the file |
|
FILE_SIZE |
NUMBER |
Current file size |
|
FILE_MAXSIZE |
NUMBER |
Maximum file size |
|
CHANGESCN_BASE |
NUMBER |
SCN base of the last change to the file |
|
CHANGESCN_WRAP |
NUMBER |
SCN wrap of the last change to the file |
|
FLAG |
NUMBER |
Flags for file attributes |
根据以上分析，可以得出以下结论：
1、DBA_TABLESPACE_USAGE_METRICS的USED_SPACE是已经分配的空间，对应 V$FILESPACE_USAGE 的ALLOCATED_SPACE字段。
2、DBA_TABLESPACE_USAGE_METRICS的 TABLESPACE_SIZE对应V$FILESPACE_USAGE的 FILE_MAXSIZE字段（而不是FILE_SIZE）。
注意：这里对应的是最大值。如果数据文件是自动增长的，那么，对于8k的block，这里的最大值就是32G，
也就是通过DBA_TABLESPACE_USAGE_METRICS视图查询显示的4194302个blocks。
4、对于自动扩展的表空间，DBA_TABLESPACE_USAGE_METRICS视图查询的结果就不准确了，还要使用传统的方法查询。
5. 统计UNDO表空间时 ，在12c之前UNDO表空间在GV$FILESPACE_USAGE中将其数据文件标记为值6，并且它是以此方式唯一标识的表空间。
但是在12.1时，添加undo文件时如果在创建过程中，指定存储路径该数据文件仍标记为6，但不指定路径时，根据MOS错误28821847，这些文件标记为14。
因此，即使DBA_TABLESPACE_USAGE_METRICS报告UNDO表空间，它也仅报告第一个数据文件的信息：
6. 在12.2中 所有新创建的undo 数据文件都标记为6，但是12.2中DBA_TABLESPACE_USAGE_METRICS，不在记录undo信息
7. 因此,最好查询DBA_DATA_FILES和DBA_FREE_SPACE来监视UNDO使用，因为DBA_TABLESPACE_USAGE_METRICS无法在12.1.0.x中提供可靠的数据，并且在12.2.0.x中绝对不提供数据。 Oracle可能会更改视图定义以反映UNDO表空间文件的实际标志设置，但是就目前而言，最好查询已知包含相关且准确的数据的视图（DBA_DATA_FILES，DBA_FREE_SPACE）。
8.在19c中测试 所有新创建的undo 数据文件都标记为10，更改为当前undo表空间时，flag 变改为14
#19C 中的 DBA_TABLESPACE_USAGE_METRICS
CREATE FORCE VIEW "SYS"."DBA_TABLESPACE_USAGE_METRICS" ("TABLESPACE_NAME", "USED_SPACE", "TABLESPACE_SIZE", "USED_PERCENT") AS
SELECT t.name,
tstat.kttetsused,
tstat.kttetsmsize,
(tstat.kttetsused / tstat.kttetsmsize) * 100
FROM sys.ts$ t, x$kttets tstat
WHERE
t.online$ != 3 and
t.bitmapped <> 0 and
t.contents$ = 0 and
bitand(t.flags, 16) <> 16 and
t.ts# = tstat.kttetstsn
union
SELECT t.name, sum(f.allocated_space), sum(f.file_maxsize),
(sum(f.allocated_space)/sum(f.file_maxsize))*100
FROM sys.ts$ t, v$filespace_usage f
WHERE
t.online$ != 3 and
t.bitmapped <> 0 and
t.contents$ <> 0 and
f.flag = 6 and
t.ts# = f.tablespace_id
GROUP BY t.name, f.tablespace_id, t.ts#
union
SELECT t.name, sum(f.allocated_space), sum(f.file_maxsize),
(sum(f.allocated_space)/sum(f.file_maxsize))*100
FROM sys.ts$ t, gv$filespace_usage f, gv$parameter param
WHERE
t.online$ != 3 and
t.bitmapped <> 0 and
f.inst_id = param.inst_id and
param.name = 'undo_tablespace' and
t.name = param.value and
f.flag = 6 and <---19c的undo 表空间数据文件的flag为14 ,
t.ts# = f.tablespace_id
GROUP BY t.name, f.tablespace_id, t.ts#
<---19c的undo 表空间数据文件的flag为14 , 新创建的undo 数据文件 flag 为10，
当吏用以下命令改改当前undo表空间时，alter system set undo_tablespace='UNDOTEST'; 注意大写。
UNDO表空间数据文件的flag改为14
# 自定义DBA_TABLESPACE_USAGE_METRICS_2 过程：
CREATE FORCE VIEW "SYS"."DBA_TABLESPACE_USAGE_METRICS_2" ("TABLESPACE_NAME", "USED_SPACE", "TABLESPACE_SIZE", "USED_PERCENT") AS
SELECT t.name,
tstat.kttetsused,
tstat.kttetsmsize,
(tstat.kttetsused / tstat.kttetsmsize) * 100
FROM sys.ts$ t, x$kttets tstat
WHERE
t.online$ != 3 and
t.bitmapped <> 0 and
t.contents$ = 0 and
bitand(t.flags, 16) <> 16 and
t.ts# = tstat.kttetstsn
union
SELECT t.name, sum(f.allocated_space), sum(f.file_maxsize),
(sum(f.allocated_space)/sum(f.file_maxsize))*100
FROM sys.ts$ t, v$filespace_usage f
WHERE
t.online$ != 3 and
t.bitmapped <> 0 and
t.contents$ <> 0 and
f.flag = 6 and
t.ts# = f.tablespace_id
GROUP BY t.name, f.tablespace_id, t.ts#
union
SELECT t.name, sum(f.allocated_space), sum(f.file_maxsize),
(sum(f.allocated_space)/sum(f.file_maxsize))*100
FROM sys.ts$ t, gv$filespace_usage f, gv$parameter param
WHERE
t.online$ != 3 and
t.bitmapped <> 0 and
f.inst_id = param.inst_id and
param.name = 'undo_tablespace' and
t.name = param.value and
f.flag = 14 and
t.ts# = f.tablespace_id
GROUP BY t.name, f.tablespace_id, t.ts#
#PDB中 每个PDB都要单独创建