1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
|
CLEAR COLUMNS COMPUTES
SET DEFINE OFF
col pdbname for a20
col CON_ID HEADING '容器ID' format 999999
col PDBNAME HEADING 'PDB名称' format a15
col TS# HEADING '表空间号' format 999999
col TS_NAME HEADING '表空间名称' format a22
col TYPE HEADING '表空间类型' format a10
col TS_SIZE_M HEADING '总大小(MB)' format 999,999,999
col FREE_SIZE_M HEADING '空闲(MB)' format 999,999,999
col USED_SIZE_M HEADING '已用(MB)' format 999,999,999
col USED_PER HEADING '使用率%' format 999.99
col MAX_SIZE_G HEADING '最大扩展(GB)' format 999,999
col USED_PER_MAX HEADING '扩展利用率%' format 999.99
col BLOCK_SIZE HEADING '块大小' format 99999
col LOGGING HEADING '日志模式' format a10
col TS_DF_COUNT HEADING '文件数量' format 999999
SELECT CON_ID,
PDBNAME,
TS#,
TS_NAME,
type,
TS_SIZE_M,
FREE_SIZE_M,
USED_SIZE_M,
USED_PER,
MAX_SIZE_G,
USED_PER_MAX,
BLOCK_SIZE,
LOGGING,
TS_DF_COUNT
FROM (WITH wt1 AS (SELECT ts.CON_ID,
(SELECT np.NAME
FROM V$CONTAINERS np
WHERE np.CON_ID = tS.con_id) PDBNAME,
(SELECT A.TS#
FROM V$TABLESPACE A
WHERE A.NAME = UPPER(tS.TABLESPACE_NAME)
AND a.CON_ID = tS.con_id) TS#,
ts.TABLESPACE_NAME,
df.all_bytes,
decode(df.TYPE,
'D',
nvl(fs.FREESIZ, 0),
'T',
df.all_bytes - nvl(fs.FREESIZ, 0)) FREESIZ,
df.MAXSIZ,
ts.BLOCK_SIZE,
ts.LOGGING,
ts.FORCE_LOGGING,
ts.CONTENTS,
ts.EXTENT_MANAGEMENT,
ts.SEGMENT_SPACE_MANAGEMENT,
ts.RETENTION,
ts.DEF_TAB_COMPRESSION,
df.ts_df_count
FROM cdb_tablespaces ts,
(SELECT d.CON_ID,
'D' TYPE,
TABLESPACE_NAME,
COUNT(*) ts_df_count,
SUM(BYTES) all_bytes,
SUM(decode(MAXBYTES, 0, BYTES, MAXBYTES)) MAXSIZ
FROM cdb_data_files d
GROUP BY d.CON_ID,
TABLESPACE_NAME
UNION ALL
SELECT d.CON_ID,
'T',
TABLESPACE_NAME,
COUNT(*) ts_df_count,
SUM(BYTES) all_bytes,
SUM(decode(MAXBYTES, 0, BYTES, MAXBYTES))
FROM cdb_temp_files d
GROUP BY d.CON_ID,
TABLESPACE_NAME) df,
(SELECT d.CON_ID,
TABLESPACE_NAME,
SUM(BYTES) FREESIZ
FROM cdb_free_space d
GROUP BY d.CON_ID,
TABLESPACE_NAME
UNION ALL
SELECT d.CON_ID,
tablespace_name,
SUM(d.BLOCK_SIZE * a.BLOCKS) bytes
FROM gv$sort_usage a,
cdb_tablespaces d
WHERE a.tablespace = d.tablespace_name
AND a.CON_ID = d.CON_ID
GROUP BY d.CON_ID,
tablespace_name) fs
WHERE ts.TABLESPACE_NAME = df.TABLESPACE_NAME
AND ts.CON_ID = df.CON_ID
AND ts.TABLESPACE_NAME = fs.TABLESPACE_NAME(+)
AND ts.CON_ID = fs.CON_ID(+))
SELECT T.CON_ID,
(CASE
WHEN T.PDBNAME = LAG(T.PDBNAME, 1)
OVER(PARTITION BY T.PDBNAME ORDER BY TS#) THEN
NULL
ELSE
T.PDBNAME
END) PDBNAME,
TS#,
t.TABLESPACE_NAME TS_Name,
t.CONTENTS type,
round(t.all_bytes / 1024 / 1024) ts_size_M,
round(t.freesiz / 1024 / 1024) Free_Size_M,
round((t.all_bytes - t.FREESIZ) / 1024 / 1024) Used_Size_M,
round((t.all_bytes - t.FREESIZ) * 100 / t.all_bytes, 3) Used_per,
round(MAXSIZ / 1024 / 1024 / 1024, 3) MAX_Size_g,
round(decode(MAXSIZ,
0,
to_number(NULL),
(t.all_bytes - FREESIZ)) * 100 / MAXSIZ,
3) USED_per_MAX,
round(t.BLOCK_SIZE) BLOCK_SIZE,
t.LOGGING,
t.ts_df_count
FROM wt1 t
UNION ALL
SELECT DISTINCT T.CON_ID,
'' PDBNAME,
to_number('') TS#,
'ALL TS:' TS_Name,
'' type,
round(SUM(t.all_bytes) / 1024 / 1024, 3) ts_size_M,
round(SUM(t.freesiz) / 1024 / 1024) Free_Size_m,
round(SUM(t.all_bytes - t.FREESIZ) / 1024 / 1024) Used_Size_M,
round(SUM(t.all_bytes - t.FREESIZ) * 100 /
SUM(t.all_bytes),
3) Used_per,
round(SUM(MAXSIZ) / 1024 / 1024 / 1024) MAX_Size,
to_number('') "USED,% of MAX Size",
to_number('') BLOCK_SIZE,
'' LOGGING,
to_number('') ts_df_count
FROM wt1 t
GROUP BY rollup(CON_ID,PDBNAME)
)
ORDER BY CON_ID,type,TS# ;
|