目的
データディクショナリビューを利用した確認
コマンド
| 内容 | ディクショナリビュー | 実行例 |
|---|---|---|
| DB・ユーザの一覧と割当領域を確認 | Databases | select DatabaseName, PermSpace,DBKind from dbc.databases order by 3,1 ; |
| DBに含まれる表を確認 | TablesV | select DatabaseName(char(20)), TableName(char(20)), TableKind, LastAlterTimeStamp, BlockCompression from dbc.TablesV where DatabaseName = 'SYSDBA' order by 2; |
| 各DBの容量と使用量を確認 | DiskSpace | select DatabaseName, sum(MaxPerm), sum(CurrentPerm), case sum(MaxPerm) when 0 then 0 else sum(CurrentPerm) * 100.00 / sum(MaxPerm) end as usage from dbc.DiskSpace group by DatabaseName order by DatabaseName; |
| DBに含まれるテーブル毎の容量を確認 | TableSize | select TableName, sum(CurrentPerm) from dbc.TableSize where DatabaseName = 'SYSDBA' group by TableName order by TableName; |
| 統計情報の状態を確認 | TableStatsV | select TableName(char(15)), ColumnName(char(15)), StatsSource, ValidStats, DBSVersion, IndexNumber, RowCount, CreateTimeStamp from dbc.TableStatsV where DatabaseName = 'SYSDBA'; |
実行例
DB・ユーザの一覧と割当領域を確認(Databases)
select databaseName, PermSpace,DBKind from dbc.databases order by 3,1 ;
DatabaseName PermSpace DBKind
------------------------------ -------------------------- ------
dbcmngr 100,000,000 D
SQLJ 6,400,000 D
SYSBAR 320,000,000 D
SYSJDBC 16,000,000 D
SYSLIB 320,000,000 D
SYSSPATIAL 48,000,000 D
SYSUDTLIB 256,000,000 D
SYSUIF 6,400,000 D
TDQCD 15,360,000 D
TDStats 16,000,000 D
TD_SERVER_DB 1,024,000,000 D
TD_SYSFNLIB 12,894,208 D
TD_SYSGPL 6,400,000 D
TD_SYSXML 6,400,000 D
All 0 U
console 50,000 U
Crashdumps 0 U
DBC 15,786,351,028 U
Default 0 U
External_AP 160,000,000 U
EXTUSER 0 U
LockLogShredder 4,800,000 U
PUBLIC 0 U
sora 2,500,000 U
SysAdmin 160,000,000 U
SYSDBA 9,996,000,000 U
SystemFe 6,400,000,000 U
Sys_Calendar 25,600,000 U
TDMaps 4,800,000,000 U
TDPUSER 0 U
tdwm 100,000,000 U
viewpoint 10,000,000 U
DBに含まれる表を確認
select DatabaseName(char(20)), TableName(char(20)), TableKind, LastAlterTimeStamp, BlockCompression from dbc.TablesV where DatabaseName = 'SYSDBA' order by 2;
DataBaseName TableName TableKind LastAlterTimeStamp BlockCompression
-------------------- -------------------- --------- ------------------- ----------------
SYSDBA emp T 2020-07-24 10:56:11 DEFAULT
SYSDBA t1 T 2020-08-22 10:08:19 DEFAULT
SYSDBA t2 T 2020-08-30 11:37:53 DEFAULT
SYSDBA t3 T 2020-08-30 10:48:11 DEFAULT
SYSDBA t4 T 2020-08-30 10:48:21 DEFAULT
SYSDBA t5 T 2020-08-30 12:28:32 DEFAULT
各DBの容量と使用量を確認
dbc.DiskSpaceはAMP毎の容量であるため、DB名でグルーピングして合計を確認
select
DatabaseName, sum(MaxPerm), sum(CurrentPerm),
case sum(MaxPerm) when 0 then 0 else sum(CurrentPerm) * 100.00 / sum(MaxPerm) end as usage
from dbc.DiskSpace group by DatabaseName order by DatabaseName;
select
DatabaseName, sum(MaxPerm), sum(CurrentPerm),
case sum(MaxPerm) when 0 then 0 else sum(CurrentPerm) * 100.00 / sum(MaxPerm) end as usage
from dbc.DiskSpace group by DatabaseName order by DatabaseName;
*** Query completed. 32 rows found. 4 columns returned.
*** Total elapsed time was 1 second.
DatabaseName Sum(MaxPerm) Sum(CurrentPerm) usage
------------------------------ -------------------------- -------------------------- -----------------
All 0 0 .00
console 50,000 0 .00
Crashdumps 0 0 .00
DBC 15,786,351,028 113,971,200 .72
dbcmngr 100,000,000 16,384 .02
Default 0 0 .00
External_AP 160,000,000 0 .00
EXTUSER 0 0 .00
LockLogShredder 4,800,000 319,488 6.66
PUBLIC 0 0 .00
sora 2,500,000 0 .00
SQLJ 6,400,000 172,032 2.69
SysAdmin 160,000,000 548,864 .34
SYSBAR 320,000,000 24,576 .01
SYSDBA 9,997,500,000 963,018,752 9.63
SYSJDBC 16,000,000 0 .00
SYSLIB 320,000,000 10,526,720 3.29
SYSSPATIAL 48,000,000 1,363,968 2.84
SystemFe 6,400,000,000 2,629,632 .04
SYSUDTLIB 256,000,000 974,848 .38
SYSUIF 6,400,000 65,536 1.02
Sys_Calendar 25,600,000 1,622,016 6.34
TDMaps 4,800,000,000 7,733,248 .16
TDPUSER 0 0 .00
TDQCD 15,360,000 253,952 1.65
TDStats 16,000,000 1,097,728 6.86
tdwm 100,000,000 1,122,304 1.12
TD_SERVER_DB 1,024,000,000 286,720 .03
TD_SYSFNLIB 12,894,208 4,562,944 35.39
TD_SYSGPL 6,400,000 0 .00
TD_SYSXML 6,400,000 491,520 7.68
viewpoint 10,000,000 0 .00
DBに含まれるテーブル毎の容量を確認
dbc.TableSizeはAMP毎の容量であるため、テーブル名でグルーピング。
select TableName, sum(CurrentPerm) from dbc.TableSize where DatabaseName = 'SYSDBA' group by TableName order by TableName;
TableName Sum(CurrentPerm)
------------------------------ --------------------------
emp 8,192
t1 100,294,656
t2 92,307,456
t3 469,843,968
t4 99,164,160
t5 201,400,320
統計情報の状態を確認
select TableName(char(15)), ColumnName(char(15)), StatsSource, ValidStats, DBSVersion, IndexNumber, RowCount, CreateTimeStamp from dbc.TableStatsV where DatabaseName = 'SYSDBA';
TableName ColumnName StatsSource ValidStats DBSVersion IndexNumber RowCount CreateTimeStamp
--------------- --------------- ----------- ---------- -------------------------------- ----------- -------------------- -------------------
t1 c6,c7 S T 16.20.32.23 ? 4,194,309 2020-08-29 12:14:06
t1 ? S T 16.20.32.23 ? 4,194,309 2020-08-29 12:11:34
t1 c1 S T 16.20.32.23 1 4,194,309 2020-08-29 12:11:34
t1 c3 S T 16.20.32.23 ? 4,194,309 2020-08-29 12:12:23
t1 c4,c5 S T 16.20.32.23 ? 4,194,309 2020-08-29 12:13:21
t1 c2 S T 16.20.32.23 ? 4,194,309 2020-08-29 12:11:56
参考
データ ディクショナリの概要 • Teradata Vantage™ データ ディクショナリ • リーダー • Teradataドキュメント
ビューの参照 • Teradata Vantage™ データ ディクショナリ • リーダー • Teradataドキュメント