Teradata 常用查询

查询数据库空间大小

Query table space: SELECT SUM(currentperm)/1024/1024/1024 AS GBFROM dbc.tablesizeWHERE tablename = 'table1' AND databasename = 'db1'Query database size: SELECT SUM(currentperm)/1024/1024/1024 AS SpaceUsed, SUM(maxperm)/1024/1024/1024 AS MaxSpaceFROM dbc.diskspaceWHERE databasename = 'db1'List all tables space in a database: SELECT tablename, SUM(currentperm)/1024/1024/1024 AS GBFROM dbc.tablesizeWHERE databasename = 'db1'GROUP BY 1ORDER BY 2

 

Query table space: SELECT SUM(currentperm)/1024/1024/1024 AS GBFROM dbc.tablesizeWHERE tablename = 'table1' AND databasename = 'db1'Query database size: SELECT SUM(currentperm)/1024/1024/1024 AS SpaceUsed, SUM(maxperm)/1024/1024/1024 AS MaxSpaceFROM dbc.diskspaceWHERE databasename = 'db1'List all tables space in a database: SELECT tablename, SUM(currentperm)/1024/1024/1024 AS GBFROM dbc.tablesizeWHERE databasename = 'db1'GROUP BY 1ORDER BY 2

 

Query table space: SELECT SUM(currentperm)/1024/1024/1024 AS GBFROM dbc.tablesizeWHERE tablename = 'table1' AND databasename = 'db1'Query database size: SELECT SUM(currentperm)/1024/1024/1024 AS SpaceUsed, SUM(maxperm)/1024/1024/1024 AS MaxSpaceFROM dbc.diskspaceWHERE databasename = 'db1'List all tables space in a database: SELECT tablename, SUM(currentperm)/1024/1024/1024 AS GBFROM dbc.tablesizeWHERE databasename = 'db1'GROUP BY 1ORDER BY 2

 

Query table space: SELECT SUM(currentperm)/1024/1024/1024 AS GBFROM dbc.tablesizeWHERE tablename = 'table1' AND databasename = 'db1'Query database size: SELECT SUM(currentperm)/1024/1024/1024 AS SpaceUsed, SUM(maxperm)/1024/1024/1024 AS MaxSpaceFROM dbc.diskspaceWHERE databasename = 'db1'List all tables space in a database: SELECT tablename, SUM(currentperm)/1024/1024/1024 AS GBFROM dbc.tablesizeWHERE databasename = 'db1'GROUP BY 1ORDER BY 2

 

Query table space: SELECT SUM(currentperm)/1024/1024/1024 AS GBFROM dbc.tablesizeWHERE tablename = 'table1' AND databasename = 'db1'Query database size: SELECT SUM(currentperm)/1024/1024/1024 AS SpaceUsed, SUM(maxperm)/1024/1024/1024 AS MaxSpaceFROM dbc.diskspaceWHERE databasename = 'db1'List all tables space in a database: SELECT tablename, SUM(currentperm)/1024/1024/1024 AS GBFROM dbc.tablesizeWHERE databasename = 'db1'GROUP BY 1ORDER BY 2

 

Query table space: SELECT SUM(currentperm)/1024/1024/1024 AS GBFROM dbc.tablesizeWHERE tablename = 'table1' AND databasename = 'db1'Query database size: SELECT SUM(currentperm)/1024/1024/1024 AS SpaceUsed, SUM(maxperm)/1024/1024/1024 AS MaxSpaceFROM dbc.diskspaceWHERE databasename = 'db1'List all tables space in a database: SELECT tablename, SUM(currentperm)/1024/1024/1024 AS GBFROM dbc.tablesizeWHERE databasename = 'db1'GROUP BY 1ORDER BY 2

 

SELECT databasename,tablename, SUM(columnlength) + 22 AS RowSize---- Assuming a 22 Byte Row Overhead, not sure if that has changed in TD12--FROM dbc.COLUMNSWHERE databasename = 'GDW_TABLES'AND tablename = 'DW_LSTG_ITEM_COLD'GROUP BY 1,2;