Discover Insights From System Catalog Tables

You can query the  System catalog tables to discover important information on system objects such as databases, segments, tables, and indexes. These statistics can be important for monitoring your system to ensure it operates as intended. 
However, querying these tables can be challenging because generating insights about your system can involve writing complex queries that involve merging, grouping, and filtering multiple system catalog tables. 
Here, you can find examples of useful queries that you can use as templates for discovering insights about your system. 
Most of these example queries use the data_type_coverage table available with the Ocient Simulator. For details, see Ocient Simulator﻿. 
Segment System Catalog Tables
For specific column definitions of the system catalog tables referenced in these examples, see the Storage﻿ section of the System Catalog page.   
Segment Size
Check the size of segments and return insights about the segment storage allocation and the number of table rows contained in each segment. 
SQL
SELECT current_timestamp,
    sg.segment_type,
    t.name,
    (COUNT(*)) AS num_segments,
    (SUM(s.row_count)) AS total_rows,
    (AVG(s.row_count)) AS avg_rows_per_segment,
    (DOUBLE(SUM(s.segment_size)) / 1000000000) AS total_size_gb, 
    (MAX(s.segment_size / 1000000)) AS max_size, 
    (MIN(s.segment_size / 1000000)) AS min_size,
    (AVG(s.segment_size) / 1000000) AS avg_size_mb,
    (STDEV(DOUBLE(s.segment_size) / 1000000)) AS stdev_size_mb
FROM
    sys.segment_groups AS sg
    JOIN sys.tables t ON t.id = sg.table_id
    JOIN sys.segments s ON s.segment_group_id = sg.id
GROUP BY
    sg.segment_type, t.name
ORDER BY
    total_size_gb DESC, 
    total_rows DESC, 
    sg.segment_type, 
    t.name;
SELECT current_timestamp,
    sg.segment_type,
    t.name,
    (COUNT(*)) AS num_segments,
    (SUM(s.row_count)) AS total_rows,
    (AVG(s.row_count)) AS avg_rows_per_segment,
    (DOUBLE(SUM(s.segment_size)) / 1000000000) AS total_size_gb, 
    (MAX(s.segment_size / 1000000)) AS max_size, 
    (MIN(s.segment_size / 1000000)) AS min_size,
    (AVG(s.segment_size) / 1000000) AS avg_size_mb,
    (STDEV(DOUBLE(s.segment_size) / 1000000)) AS stdev_size_mb
FROM
    sys.segment_groups AS sg
    JOIN sys.tables t ON t.id = sg.table_id
    JOIN sys.segments s ON s.segment_group_id = sg.id
GROUP BY
    sg.segment_type, t.name
ORDER BY
    total_size_gb DESC, 
    total_rows DESC, 
    sg.segment_type, 
    t.name;
﻿
Output
Text
|  "current_timestamp()"  | "segment_type" |       "name"       | "num_segments" | "total_rows" | "avg_rows_per_segment" | "total_size_gb" | "max_size" | "min_size" |   "avg_size_mb"    |  "stdev_size_mb"  |
|-------------------------|----------------|--------------------|----------------|--------------|------------------------|-----------------|------------|------------|--------------------|-------------------|
| 2024-07-31 16:23:22.863 | TKT_SEGMENT    | data_type_coverage |              3 |       100000 |     33333.333333333336 |     0.189542692 |        113 |         37 | 63.180897333333334 | 43.73338035824336 |
|  "current_timestamp()"  | "segment_type" |       "name"       | "num_segments" | "total_rows" | "avg_rows_per_segment" | "total_size_gb" | "max_size" | "min_size" |   "avg_size_mb"    |  "stdev_size_mb"  |
|-------------------------|----------------|--------------------|----------------|--------------|------------------------|-----------------|------------|------------|--------------------|-------------------|
| 2024-07-31 16:23:22.863 | TKT_SEGMENT    | data_type_coverage |              3 |       100000 |     33333.333333333336 |     0.189542692 |        113 |         37 | 63.180897333333334 | 43.73338035824336 |
﻿
For information on the system catalog tables queried in this example, see sys.segments and sys.tables.
﻿Page and Segment Information﻿
Check the details for pages and segments in the system. 
SQL
WITH cte AS (
SELECT
	COALESCE(v.schema, t.schema) AS schema,
	COALESCE(v.name, t.name) AS name,
	stored_segments.status,
	stored_segments.kind,
	SUM(CASE WHEN segments.segment_type = 'PAGE' THEN 1 ELSE 0 END) AS pagecount,
	SUM(CASE WHEN segments.segment_type = 'TKT_SEGMENT' THEN 1 ELSE 0 END) AS segcount,
	SUM(CASE WHEN segments.segment_type = 'PAGE' THEN segments.row_count ELSE 0 END) AS page_rows,
	SUM(CASE WHEN segments.segment_type = 'TKT_SEGMENT' THEN segments.row_count ELSE 0 END) AS seg_rows,
	MIN(CASE WHEN segments.segment_type = 'PAGE' THEN sg.begin_time ELSE NULL END) AS minpagetime,
	MAX(CASE WHEN segments.segment_type = 'PAGE' THEN sg.end_time ELSE NULL END) AS maxpagetime,
	MIN(CASE WHEN segments.segment_type = 'TKT_SEGMENT' THEN sg.begin_time ELSE NULL END) AS minsegtime,
	MAX(CASE WHEN segments.segment_type = 'TKT_SEGMENT' THEN sg.end_time ELSE NULL END) AS maxsegtime
FROM
	sys.segments
JOIN sys.stored_segments ON
	segments.segment_group_id = stored_segments.segment_group_id
	AND segments.ida_offset = stored_segments.ida_offset
JOIN sys.segment_groups sg ON
	sg.id = segments.segment_group_id
JOIN sys.tables AS t ON
	t.id = sg.table_id
LEFT JOIN sys.views AS v ON
	v.global_dictionary_compression_table_id = t.id
WHERE
	stored_segments.end_osn = 'OSN_INFINITY'
GROUP BY
	1,
	2,
	stored_segments.status,
	stored_segments.kind )
SELECT
	schema,
	name,
	status,
	kind,
	pagecount,
	segcount,
	page_rows,
	seg_rows,
	page_rows + seg_rows AS tot_rows,
	CEILING(page_rows /(pagecount +.000001)) AS rows_per_page,
	CEILING(seg_rows /(segcount +.000001)) AS rows_per_seg,
	to_timestamp(minpagetime / 1000000000) AS minpagetime,
	to_timestamp(maxpagetime / 1000000000) AS maxpagetime,
	to_timestamp(minsegtime / 1000000000) AS minsegtime,
	to_timestamp(maxsegtime / 1000000000) AS maxsegtime,
	NOW()
FROM
	cte
ORDER BY
	schema,
	name;
WITH cte AS (
SELECT
	COALESCE(v.schema, t.schema) AS schema,
	COALESCE(v.name, t.name) AS name,
	stored_segments.status,
	stored_segments.kind,
	SUM(CASE WHEN segments.segment_type = 'PAGE' THEN 1 ELSE 0 END) AS pagecount,
	SUM(CASE WHEN segments.segment_type = 'TKT_SEGMENT' THEN 1 ELSE 0 END) AS segcount,
	SUM(CASE WHEN segments.segment_type = 'PAGE' THEN segments.row_count ELSE 0 END) AS page_rows,
	SUM(CASE WHEN segments.segment_type = 'TKT_SEGMENT' THEN segments.row_count ELSE 0 END) AS seg_rows,
	MIN(CASE WHEN segments.segment_type = 'PAGE' THEN sg.begin_time ELSE NULL END) AS minpagetime,
	MAX(CASE WHEN segments.segment_type = 'PAGE' THEN sg.end_time ELSE NULL END) AS maxpagetime,
	MIN(CASE WHEN segments.segment_type = 'TKT_SEGMENT' THEN sg.begin_time ELSE NULL END) AS minsegtime,
	MAX(CASE WHEN segments.segment_type = 'TKT_SEGMENT' THEN sg.end_time ELSE NULL END) AS maxsegtime
FROM
	sys.segments
JOIN sys.stored_segments ON
	segments.segment_group_id = stored_segments.segment_group_id
	AND segments.ida_offset = stored_segments.ida_offset
JOIN sys.segment_groups sg ON
	sg.id = segments.segment_group_id
JOIN sys.tables AS t ON
	t.id = sg.table_id
LEFT JOIN sys.views AS v ON
	v.global_dictionary_compression_table_id = t.id
WHERE
	stored_segments.end_osn = 'OSN_INFINITY'
GROUP BY
	1,
	2,
	stored_segments.status,
	stored_segments.kind )
SELECT
	schema,
	name,
	status,
	kind,
	pagecount,
	segcount,
	page_rows,
	seg_rows,
	page_rows + seg_rows AS tot_rows,
	CEILING(page_rows /(pagecount +.000001)) AS rows_per_page,
	CEILING(seg_rows /(segcount +.000001)) AS rows_per_seg,
	to_timestamp(minpagetime / 1000000000) AS minpagetime,
	to_timestamp(maxpagetime / 1000000000) AS maxpagetime,
	to_timestamp(minsegtime / 1000000000) AS minsegtime,
	to_timestamp(maxsegtime / 1000000000) AS maxsegtime,
	NOW()
FROM
	cte
ORDER BY
	schema,
	name;
﻿
Output
Text
| "schema" |       "name"       | "status" | "kind" | "pagecount" | "segcount" | "page_rows" | "seg_rows" | "tot_rows" | "rows_per_page" | "rows_per_seg" | "minpagetime" | "maxpagetime" |      "minsegtime"       |      "maxsegtime"       |  "current_timestamp()"  |
|----------|--------------------|----------|--------|-------------|------------|-------------|------------|------------|-----------------|----------------|---------------|---------------|-------------------------|-------------------------|-------------------------|
| loading  | data_type_coverage | INTACT   | DISK   |           0 |          3 |           0 |     100000 |     100000 |             0.0 |        33334.0 |               |               | 1970-01-01 00:00:00.000 | 1970-01-01 00:00:00.000 | 2024-08-07 21:41:36.800 |
| "schema" |       "name"       | "status" | "kind" | "pagecount" | "segcount" | "page_rows" | "seg_rows" | "tot_rows" | "rows_per_page" | "rows_per_seg" | "minpagetime" | "maxpagetime" |      "minsegtime"       |      "maxsegtime"       |  "current_timestamp()"  |
|----------|--------------------|----------|--------|-------------|------------|-------------|------------|------------|-----------------|----------------|---------------|---------------|-------------------------|-------------------------|-------------------------|
| loading  | data_type_coverage | INTACT   | DISK   |           0 |          3 |           0 |     100000 |     100000 |             0.0 |        33334.0 |               |               | 1970-01-01 00:00:00.000 | 1970-01-01 00:00:00.000 | 2024-08-07 21:41:36.800 |
﻿
Storage Spaces on a System
Check all storage spaces on your system. 
SQL
SELECT * 
FROM sys.storage_spaces;
SELECT * 
FROM sys.storage_spaces;
﻿
Output
Text
|                  "id"                  |       "name"       | "is_system_storage_space" | "block_size" | "total_width" | "parity_width" | "parity_type" | "parity_cycles" | "page_replication" |
|----------------------------------------|--------------------|---------------------------|--------------|---------------|----------------|---------------|-----------------|--------------------|
| ce9f9c2e-d3fe-4101-a2b2-6f7f2580327b   | ss0                | false                     |         4096 |             3 |              1 | XOR           |               1 |                  2 |
| "7277656e-6465-6c20-7761-732068657265" | systemstoragespace | true                      |         4096 |             3 |              2 | REPLICATION   |               1 |                  3 |
|                  "id"                  |       "name"       | "is_system_storage_space" | "block_size" | "total_width" | "parity_width" | "parity_type" | "parity_cycles" | "page_replication" |
|----------------------------------------|--------------------|---------------------------|--------------|---------------|----------------|---------------|-----------------|--------------------|
| ce9f9c2e-d3fe-4101-a2b2-6f7f2580327b   | ss0                | false                     |         4096 |             3 |              1 | XOR           |               1 |                  2 |
| "7277656e-6465-6c20-7761-732068657265" | systemstoragespace | true                      |         4096 |             3 |              2 | REPLICATION   |               1 |                  3 |
﻿
Query Management System Catalog Tables
For specific column definitions of the system catalog tables referenced in these examples, see the Monitoring﻿ section of the System Catalog page.   
Running Queries
Check all queries currently running on the system. 
The output of the sys.queries table depends on what database you are logged into and your system role. For details, see Query Visibility﻿.
SQL
SELECT
	queries.query_id,
    queries.initial_priority,
    queries.effective_priority,
	queries.user,
	queries.total_time,
	queries.status,
	queries.rows_returned,
	queries.bytes_returned
FROM
	sys.queries
WHERE
	queries.sql NOT LIKE '%sys.queries%';

SELECT
	q.query_id,
	q.user,
	q.sql,
	q.status,
	"database_name",
	q.bytes_returned
FROM
	sys.queries q;
SELECT
	queries.query_id,
    queries.initial_priority,
    queries.effective_priority,
	queries.user,
	queries.total_time,
	queries.status,
	queries.rows_returned,
	queries.bytes_returned
FROM
	sys.queries
WHERE
	queries.sql NOT LIKE '%sys.queries%';

SELECT
	q.query_id,
	q.user,
	q.sql,
	q.status,
	"database_name",
	q.bytes_returned
FROM
	sys.queries q;
﻿
Output
Text
|               "query_id"               |    "user"    |      "sql"       | "status" | "database_name" | "bytes_returned" |
|----------------------------------------|--------------|------------------|----------|-----------------|------------------|
| "66b27dde-17df-403a-82d7-65e34b6fd07f" | admin@system |  "example_query" | RUNNING  | test            |                0 |

|               "query_id"               |    "user"    |      "sql"       | "status" | "database_name" | "bytes_returned" |
|----------------------------------------|--------------|------------------|----------|-----------------|------------------|
| "66b27dde-17df-403a-82d7-65e34b6fd07f" | admin@system |  "example_query" | RUNNING  | test            |                0 |
﻿
﻿Recent Queries ﻿
Check the most recent queries completed by the system. 
The output of the sys.completed_queries table depends on what database you are logged into and your system role. For details, see Query Visibility﻿.
SQL
SELECT user,
    timestamp_start,
    total_time,
    state,
    reason,
    sql
FROM sys.completed_queries
ORDER BY timestamp_start DESC
LIMIT 10;
SELECT user,
    timestamp_start,
    total_time,
    state,
    reason,
    sql
FROM sys.completed_queries
ORDER BY timestamp_start DESC
LIMIT 10;
﻿
Output
Text
|    "user"    |       "time_start"        | "total_time" | "state" |                                                             "reason"                                                              |                                                                 "sql"                                                                 |             |                                       |                                                                                                                                    |                                                                                                                                    |                                                                                                                                                                                                                                |          |            |         |                                    |          |            |         |                                                                    |
|--------------|---------------------------|--------------|---------|-----------------------------------------------------------------------------------------------------------------------------------|---------------------------------------------------------------------------------------------------------------------------------------|-------------|---------------------------------------|------------------------------------------------------------------------------------------------------------------------------------|------------------------------------------------------------------------------------------------------------------------------------|--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|----------|------------|---------|------------------------------------|----------|------------|---------|--------------------------------------------------------------------|
| admin@system | "2024-08-06T21:51:29.284" |           84 | "00000" | The operation completed successfully                                                                                              | "with the_user_query_to_add_a_limit_to as (select user                                                                                |  time_start |  total_time                           |  state                                                                                                                             |  reason                                                                                                                            |  sql from sys.completed_queries order by time_start desc limit 100) select * from the_user_query_to_add_a_limit_to limit 200"                                                                                                  |          |            |         |                                    |          |            |         |                                                                    |
| admin@system | "2024-08-06T21:45:33.952" |           58 | "00000" | The operation completed successfully                                                                                              | "with the_user_query_to_add_a_limit_to as (select q.query_id                                                                          |  q.user     |  q.sql                                |  q.status                                                                                                                          |  ""database_name""                                                                                                                 |  q.bytes_returned from sys.queries q) select * from the_user_query_to_add_a_limit_to limit 200"                                                                                                                                |          |            |         |                                    |          |            |         |                                                                    |
| admin@system | "2024-08-06T21:41:04.521" |          205 | "00000" | The operation completed successfully                                                                                              | "with the_user_query_to_add_a_limit_to as (select sg.segment_type                                                                     |  ss.kind    |  ss.status                            |  t.name                                                                                                                            |  (ss.end_osn = 'OSN_INFINITY') active                                                                                              |  count(distinct ss.storage_id) num_segments from sys.stored_segments ss join sys.segment_groups sg on sg.id = ss.segment_group_id join sys.tables t on t.id = sg.table_id where ss.status <> 'INTACT' group by sg.segment_type |  ss.kind |  ss.status |  t.name |  active order by num_segments desc |  ss.kind |  ss.status |  t.name |  active) select * from the_user_query_to_add_a_limit_to limit 200" |
| admin@system | "2024-08-06T21:38:19.740" |           90 | "00000" | The operation completed successfully                                                                                              | with the_user_query_to_add_a_limit_to as (select * from sys.stored_segments) select * from the_user_query_to_add_a_limit_to limit 200 |             |                                       |                                                                                                                                    |                                                                                                                                    |                                                                                                                                                                                                                                |          |            |         |                                    |          |            |         |                                                                    |
| admin@system | "2024-08-06T21:37:35.181" |            5 | "42703" | The reference to referenced column is not valid (The column 'ss.kind' is being referenced incorrectly. Maybe a missing GROUP BY?) | "with the_user_query_to_add_a_limit_to as (select ss.kind                                                                             |  ss.status  |  (ss.end_osn = 'OSN_INFINITY') active |  count(distinct ss.storage_id) num_segments from sys.stored_segments ss) select * from the_user_query_to_add_a_limit_to limit 200" |                                                                                                                                    |                                                                                                                                                                                                                                |          |            |         |                                    |          |            |         |                                                                    |
| admin@system | "2024-08-06T21:37:33.710" |            5 | "42703" | The reference to referenced column is not valid (The column 'ss.kind' is being referenced incorrectly. Maybe a missing GROUP BY?) | "with the_user_query_to_add_a_limit_to as (select ss.kind                                                                             |  ss.status  |  (ss.end_osn = 'OSN_INFINITY') active |  count(distinct ss.storage_id) num_segments from sys.stored_segments ss) select * from the_user_query_to_add_a_limit_to limit 200" |                                                                                                                                    |                                                                                                                                                                                                                                |          |            |         |                                    |          |            |         |                                                                    |
| admin@system | "2024-08-06T21:36:03.692" |           11 | "42703" | The reference to referenced column is not valid (The column 'ss.kind' is being referenced incorrectly. Maybe a missing GROUP BY?) | "with the_user_query_to_add_a_limit_to as (select ss.kind                                                                             |  ss.status  |  (ss.end_osn = 'OSN_INFINITY') active |  count(distinct ss.storage_id) num_segments from sys.stored_segments ss) select * from the_user_query_to_add_a_limit_to limit 200" |                                                                                                                                    |                                                                                                                                                                                                                                |          |            |         |                                    |          |            |         |                                                                    |
| admin@system | "2024-08-06T21:35:51.301" |            4 | "42703" | The reference to column 't.name' is not valid                                                                                     | "with the_user_query_to_add_a_limit_to as (select ss.kind                                                                             |  ss.status  |  t.name                               |  (ss.end_osn = 'OSN_INFINITY') active                                                                                              |  count(distinct ss.storage_id) num_segments from sys.stored_segments ss) select * from the_user_query_to_add_a_limit_to limit 200" |                                                                                                                                                                                                                                |          |            |         |                                    |          |            |         |                                                                    |
| admin@system | "2024-08-06T21:35:10.331" |            5 | "42703" | The reference to column 'sg.segment_type' is not valid                                                                            | "with the_user_query_to_add_a_limit_to as (select sg.segment_type                                                                     |  ss.kind    |  ss.status                            |  t.name                                                                                                                            |  (ss.end_osn = 'OSN_INFINITY') active                                                                                              |  count(distinct ss.storage_id) num_segments from sys.stored_segments ss) select * from the_user_query_to_add_a_limit_to limit 200"                                                                                             |          |            |         |                                    |          |            |         |                                                                    |
| admin@system | "2024-08-06T21:34:42.970" |          208 | "00000" | The operation completed successfully                                                                                              | "with the_user_query_to_add_a_limit_to as (select sg.segment_type                                                                     |  ss.kind    |  ss.status                            |  t.name                                                                                                                            |  (ss.end_osn = 'OSN_INFINITY') active                                                                                              |  count(distinct ss.storage_id) num_segments from sys.stored_segments ss join sys.segment_groups sg on sg.id = ss.segment_group_id join sys.tables t on t.id = sg.table_id where ss.status <> 'INTACT' group by sg.segment_type |  ss.kind |  ss.status |  t.name |  active order by num_segments desc |  ss.kind |  ss.status |  t.name |  active) select * from the_user_query_to_add_a_limit_to limit 200" |
|    "user"    |       "time_start"        | "total_time" | "state" |                                                             "reason"                                                              |                                                                 "sql"                                                                 |             |                                       |                                                                                                                                    |                                                                                                                                    |                                                                                                                                                                                                                                |          |            |         |                                    |          |            |         |                                                                    |
|--------------|---------------------------|--------------|---------|-----------------------------------------------------------------------------------------------------------------------------------|---------------------------------------------------------------------------------------------------------------------------------------|-------------|---------------------------------------|------------------------------------------------------------------------------------------------------------------------------------|------------------------------------------------------------------------------------------------------------------------------------|--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|----------|------------|---------|------------------------------------|----------|------------|---------|--------------------------------------------------------------------|
| admin@system | "2024-08-06T21:51:29.284" |           84 | "00000" | The operation completed successfully                                                                                              | "with the_user_query_to_add_a_limit_to as (select user                                                                                |  time_start |  total_time                           |  state                                                                                                                             |  reason                                                                                                                            |  sql from sys.completed_queries order by time_start desc limit 100) select * from the_user_query_to_add_a_limit_to limit 200"                                                                                                  |          |            |         |                                    |          |            |         |                                                                    |
| admin@system | "2024-08-06T21:45:33.952" |           58 | "00000" | The operation completed successfully                                                                                              | "with the_user_query_to_add_a_limit_to as (select q.query_id                                                                          |  q.user     |  q.sql                                |  q.status                                                                                                                          |  ""database_name""                                                                                                                 |  q.bytes_returned from sys.queries q) select * from the_user_query_to_add_a_limit_to limit 200"                                                                                                                                |          |            |         |                                    |          |            |         |                                                                    |
| admin@system | "2024-08-06T21:41:04.521" |          205 | "00000" | The operation completed successfully                                                                                              | "with the_user_query_to_add_a_limit_to as (select sg.segment_type                                                                     |  ss.kind    |  ss.status                            |  t.name                                                                                                                            |  (ss.end_osn = 'OSN_INFINITY') active                                                                                              |  count(distinct ss.storage_id) num_segments from sys.stored_segments ss join sys.segment_groups sg on sg.id = ss.segment_group_id join sys.tables t on t.id = sg.table_id where ss.status <> 'INTACT' group by sg.segment_type |  ss.kind |  ss.status |  t.name |  active order by num_segments desc |  ss.kind |  ss.status |  t.name |  active) select * from the_user_query_to_add_a_limit_to limit 200" |
| admin@system | "2024-08-06T21:38:19.740" |           90 | "00000" | The operation completed successfully                                                                                              | with the_user_query_to_add_a_limit_to as (select * from sys.stored_segments) select * from the_user_query_to_add_a_limit_to limit 200 |             |                                       |                                                                                                                                    |                                                                                                                                    |                                                                                                                                                                                                                                |          |            |         |                                    |          |            |         |                                                                    |
| admin@system | "2024-08-06T21:37:35.181" |            5 | "42703" | The reference to referenced column is not valid (The column 'ss.kind' is being referenced incorrectly. Maybe a missing GROUP BY?) | "with the_user_query_to_add_a_limit_to as (select ss.kind                                                                             |  ss.status  |  (ss.end_osn = 'OSN_INFINITY') active |  count(distinct ss.storage_id) num_segments from sys.stored_segments ss) select * from the_user_query_to_add_a_limit_to limit 200" |                                                                                                                                    |                                                                                                                                                                                                                                |          |            |         |                                    |          |            |         |                                                                    |
| admin@system | "2024-08-06T21:37:33.710" |            5 | "42703" | The reference to referenced column is not valid (The column 'ss.kind' is being referenced incorrectly. Maybe a missing GROUP BY?) | "with the_user_query_to_add_a_limit_to as (select ss.kind                                                                             |  ss.status  |  (ss.end_osn = 'OSN_INFINITY') active |  count(distinct ss.storage_id) num_segments from sys.stored_segments ss) select * from the_user_query_to_add_a_limit_to limit 200" |                                                                                                                                    |                                                                                                                                                                                                                                |          |            |         |                                    |          |            |         |                                                                    |
| admin@system | "2024-08-06T21:36:03.692" |           11 | "42703" | The reference to referenced column is not valid (The column 'ss.kind' is being referenced incorrectly. Maybe a missing GROUP BY?) | "with the_user_query_to_add_a_limit_to as (select ss.kind                                                                             |  ss.status  |  (ss.end_osn = 'OSN_INFINITY') active |  count(distinct ss.storage_id) num_segments from sys.stored_segments ss) select * from the_user_query_to_add_a_limit_to limit 200" |                                                                                                                                    |                                                                                                                                                                                                                                |          |            |         |                                    |          |            |         |                                                                    |
| admin@system | "2024-08-06T21:35:51.301" |            4 | "42703" | The reference to column 't.name' is not valid                                                                                     | "with the_user_query_to_add_a_limit_to as (select ss.kind                                                                             |  ss.status  |  t.name                               |  (ss.end_osn = 'OSN_INFINITY') active                                                                                              |  count(distinct ss.storage_id) num_segments from sys.stored_segments ss) select * from the_user_query_to_add_a_limit_to limit 200" |                                                                                                                                                                                                                                |          |            |         |                                    |          |            |         |                                                                    |
| admin@system | "2024-08-06T21:35:10.331" |            5 | "42703" | The reference to column 'sg.segment_type' is not valid                                                                            | "with the_user_query_to_add_a_limit_to as (select sg.segment_type                                                                     |  ss.kind    |  ss.status                            |  t.name                                                                                                                            |  (ss.end_osn = 'OSN_INFINITY') active                                                                                              |  count(distinct ss.storage_id) num_segments from sys.stored_segments ss) select * from the_user_query_to_add_a_limit_to limit 200"                                                                                             |          |            |         |                                    |          |            |         |                                                                    |
| admin@system | "2024-08-06T21:34:42.970" |          208 | "00000" | The operation completed successfully                                                                                              | "with the_user_query_to_add_a_limit_to as (select sg.segment_type                                                                     |  ss.kind    |  ss.status                            |  t.name                                                                                                                            |  (ss.end_osn = 'OSN_INFINITY') active                                                                                              |  count(distinct ss.storage_id) num_segments from sys.stored_segments ss join sys.segment_groups sg on sg.id = ss.segment_group_id join sys.tables t on t.id = sg.table_id where ss.status <> 'INTACT' group by sg.segment_type |  ss.kind |  ss.status |  t.name |  active order by num_segments desc |  ss.kind |  ss.status |  t.name |  active) select * from the_user_query_to_add_a_limit_to limit 200" |
﻿
Database and Table System Catalog Tables
For specific column definitions of the system catalog tables used in these examples, see the Databases﻿ section of the System Catalog page.   
﻿Database Sizes﻿
Check the total size of all databases on your system. 
SQL
SELECT TO_CHAR(SUM(sp.size), '999,999,999,999,999') AS totsize, 
    d.name
    FROM sys.segment_parts AS sp
    JOIN sys.segment_groups AS sg
        ON sp.segment_group_id = sg.id
    JOIN sys.tables AS t
        ON sg.table_id = t.id
    JOIN sys.databases AS d
        ON t.database_id = d.id
 GROUP BY d.name
 ORDER BY 1 DESC;
SELECT TO_CHAR(SUM(sp.size), '999,999,999,999,999') AS totsize, 
    d.name
    FROM sys.segment_parts AS sp
    JOIN sys.segment_groups AS sg
        ON sp.segment_group_id = sg.id
    JOIN sys.tables AS t
        ON sg.table_id = t.id
    JOIN sys.databases AS d
        ON t.database_id = d.id
 GROUP BY d.name
 ORDER BY 1 DESC;
﻿
Output
Text
| "totsize" | "name" |
|-----------|--------|
| 204276004 | test   |
| "totsize" | "name" |
|-----------|--------|
| 204276004 | test   |
﻿
Table Sizes
Check the total size of all tables on the system. 
SQL
WITH 
cte AS (
    SELECT
	    sg.id AS segment_group_id,
	    sp.name AS segment_name,
	    sp.part_type AS partition_type,
	    sp.size AS segment_size,
	    sg.segment_type,
	    sg.status,
	    t.schema,
	    t.name
    FROM sys.segment_parts AS sp
    INNER JOIN sys.segment_groups AS sg ON
	    sp.segment_group_id = sg.id
    INNER JOIN sys.tables AS t ON
	    sg.table_id = t.id ),
ctefinal AS ( 
    SELECT schema, name, SUM(segment_size) AS total_table_size 
    FROM cte 
    GROUP BY schema, name )
SELECT SCHEMA, name, total_table_size, SUM(total_table_size) 
    OVER( ORDER BY total_table_size DESC ) AS running_total
    FROM ctefinal 
    ORDER BY total_table_size DESC;
WITH 
cte AS (
    SELECT
	    sg.id AS segment_group_id,
	    sp.name AS segment_name,
	    sp.part_type AS partition_type,
	    sp.size AS segment_size,
	    sg.segment_type,
	    sg.status,
	    t.schema,
	    t.name
    FROM sys.segment_parts AS sp
    INNER JOIN sys.segment_groups AS sg ON
	    sp.segment_group_id = sg.id
    INNER JOIN sys.tables AS t ON
	    sg.table_id = t.id ),
ctefinal AS ( 
    SELECT schema, name, SUM(segment_size) AS total_table_size 
    FROM cte 
    GROUP BY schema, name )
SELECT SCHEMA, name, total_table_size, SUM(total_table_size) 
    OVER( ORDER BY total_table_size DESC ) AS running_total
    FROM ctefinal 
    ORDER BY total_table_size DESC;
﻿
Output
Text
| "schema" |       "name"       | "total_table_size" | "running_total" |
|----------|--------------------|--------------------|-----------------|
| loading  | data_type_coverage |          204276004 |       204276004 |
| "schema" |       "name"       | "total_table_size" | "running_total" |
|----------|--------------------|--------------------|-----------------|
| loading  | data_type_coverage |          204276004 |       204276004 |
﻿
﻿Table Statistics﻿
This query provides a breakdown of table statistics and partition information. ﻿
SQL
WITH 
cte1 AS (
	SELECT
		sg.id AS segment_group_id,
		sp.name AS segment_name,
		sp.part_type AS partition_type,
		sp.size AS segment_size,
		sg.segment_type,
		sg.status,
		t.schema,
		t.name
	FROM sys.segment_parts AS sp
	INNER JOIN sys.segment_groups AS sg ON
		sp.segment_group_id = sg.id
	INNER JOIN sys.tables AS t ON
		sg.table_id = t.id ),
cte AS (
	SELECT
		COUNT(*) AS num_segments,
		SUM(segment_size) AS partition_size,
		partition_type,
		SUM(SUM(segment_size)) OVER(PARTITION BY schema, name) AS total_table_size,
		SUM(COUNT(*)) OVER(PARTITION BY schema, name) AS total_table_segments,
		schema,
		name
	FROM cte1
	GROUP BY
		partition_type,
		schema,
		name )
SELECT
    name,
    num_segments,
    partition_size,
    partition_type,
    CAST(FLOAT(partition_size)/(total_table_size + 1) * 100 
        AS DECIMAL(6,3)) AS percent_of_total,
    total_table_size,
    total_table_segments
FROM cte
ORDER BY
    name,
    partition_size DESC;
WITH 
cte1 AS (
	SELECT
		sg.id AS segment_group_id,
		sp.name AS segment_name,
		sp.part_type AS partition_type,
		sp.size AS segment_size,
		sg.segment_type,
		sg.status,
		t.schema,
		t.name
	FROM sys.segment_parts AS sp
	INNER JOIN sys.segment_groups AS sg ON
		sp.segment_group_id = sg.id
	INNER JOIN sys.tables AS t ON
		sg.table_id = t.id ),
cte AS (
	SELECT
		COUNT(*) AS num_segments,
		SUM(segment_size) AS partition_size,
		partition_type,
		SUM(SUM(segment_size)) OVER(PARTITION BY schema, name) AS total_table_size,
		SUM(COUNT(*)) OVER(PARTITION BY schema, name) AS total_table_segments,
		schema,
		name
	FROM cte1
	GROUP BY
		partition_type,
		schema,
		name )
SELECT
    name,
    num_segments,
    partition_size,
    partition_type,
    CAST(FLOAT(partition_size)/(total_table_size + 1) * 100 
        AS DECIMAL(6,3)) AS percent_of_total,
    total_table_size,
    total_table_segments
FROM cte
ORDER BY
    name,
    partition_size DESC;
﻿
Output
Text
|       "name"       | "num_segments" | "partition_size" |    "partition_type"    | "percent_of_total" | "total_table_size" | "total_table_segments" |
|--------------------|----------------|------------------|------------------------|--------------------|--------------------|------------------------|
| data_type_coverage |              3 |        113397760 | parity_data            |             59.827 |          189542692 |                     42 |
| data_type_coverage |              3 |         75599872 | data                   |             39.885 |          189542692 |                     42 |
| data_type_coverage |              3 |           172032 | skip_lists             |              0.091 |          189542692 |                     42 |
| data_type_coverage |              3 |           172032 | copy_skip_lists        |              0.091 |          189542692 |                     42 |
| data_type_coverage |              3 |            90112 | parity_stats           |              0.048 |          189542692 |                     42 |
| data_type_coverage |              3 |            59210 | stats                  |              0.031 |          189542692 |                     42 |
| data_type_coverage |              3 |            12288 | table_of_contents      |              0.006 |          189542692 |                     42 |
| data_type_coverage |              3 |            12288 | parity_index           |              0.006 |          189542692 |                     42 |
| data_type_coverage |              3 |            12288 | copy_table_of_contents |              0.006 |          189542692 |                     42 |
| data_type_coverage |              3 |             8192 | index                  |              0.004 |          189542692 |                     42 |
| data_type_coverage |              3 |             4096 | parity_summary_stats   |              0.002 |          189542692 |                     42 |
| data_type_coverage |              3 |              906 | column_metadata        |              0.000 |          189542692 |                     42 |
| data_type_coverage |              3 |              906 | copy_column_metadata   |              0.000 |          189542692 |                     42 |
| data_type_coverage |              3 |              710 | summary_stats          |              0.000 |          189542692 |                     42 |
|       "name"       | "num_segments" | "partition_size" |    "partition_type"    | "percent_of_total" | "total_table_size" | "total_table_segments" |
|--------------------|----------------|------------------|------------------------|--------------------|--------------------|------------------------|
| data_type_coverage |              3 |        113397760 | parity_data            |             59.827 |          189542692 |                     42 |
| data_type_coverage |              3 |         75599872 | data                   |             39.885 |          189542692 |                     42 |
| data_type_coverage |              3 |           172032 | skip_lists             |              0.091 |          189542692 |                     42 |
| data_type_coverage |              3 |           172032 | copy_skip_lists        |              0.091 |          189542692 |                     42 |
| data_type_coverage |              3 |            90112 | parity_stats           |              0.048 |          189542692 |                     42 |
| data_type_coverage |              3 |            59210 | stats                  |              0.031 |          189542692 |                     42 |
| data_type_coverage |              3 |            12288 | table_of_contents      |              0.006 |          189542692 |                     42 |
| data_type_coverage |              3 |            12288 | parity_index           |              0.006 |          189542692 |                     42 |
| data_type_coverage |              3 |            12288 | copy_table_of_contents |              0.006 |          189542692 |                     42 |
| data_type_coverage |              3 |             8192 | index                  |              0.004 |          189542692 |                     42 |
| data_type_coverage |              3 |             4096 | parity_summary_stats   |              0.002 |          189542692 |                     42 |
| data_type_coverage |              3 |              906 | column_metadata        |              0.000 |          189542692 |                     42 |
| data_type_coverage |              3 |              906 | copy_column_metadata   |              0.000 |          189542692 |                     42 |
| data_type_coverage |              3 |              710 | summary_stats          |              0.000 |          189542692 |                     42 |
﻿
For information on the system catalog tables queried in this example, see sys.segment_groups and sys.segment_parts.
﻿Compression﻿
Check the information on the compression for each column in the specified table. 
SQL
SELECT r."table",
    c.NAME,
	r.is_rle_enabled,
	r.is_ne_enabled,
	r.raw_size,
	r.compressed_size,
	r.is_deltadelta_enabled,
	r.num_deltadelta_blocks,
	r.num_rle_blocks,
	r.num_nerle_blocks,
	r.num_uncompressed_blocks,
	r.num_total_blocks
FROM sys.columns c,
	( SELECT t.name AS "table",
		t.id,
		cci.is_deltadelta_enabled,
		cci.is_rle_enabled,
		cci.is_ne_enabled,
		cci.raw_size,
		cci.compressed_size,
		cci.num_deltadelta_blocks,
		cci.num_rle_blocks,
		cci.num_nerle_blocks,
		cci.num_uncompressed_blocks,
		cci.num_total_blocks,
		cci.ordinal
	    FROM sys.tables AS t,
		    sys.columns_compression_info AS cci
		WHERE t.id = cci.table_id
		    AND t.name = 'chicago_docsis_arr_idx' ) AS r
WHERE r.id = c.table_id
    AND r.ordinal = c.ordinal;
SELECT r."table",
    c.NAME,
	r.is_rle_enabled,
	r.is_ne_enabled,
	r.raw_size,
	r.compressed_size,
	r.is_deltadelta_enabled,
	r.num_deltadelta_blocks,
	r.num_rle_blocks,
	r.num_nerle_blocks,
	r.num_uncompressed_blocks,
	r.num_total_blocks
FROM sys.columns c,
	( SELECT t.name AS "table",
		t.id,
		cci.is_deltadelta_enabled,
		cci.is_rle_enabled,
		cci.is_ne_enabled,
		cci.raw_size,
		cci.compressed_size,
		cci.num_deltadelta_blocks,
		cci.num_rle_blocks,
		cci.num_nerle_blocks,
		cci.num_uncompressed_blocks,
		cci.num_total_blocks,
		cci.ordinal
	    FROM sys.tables AS t,
		    sys.columns_compression_info AS cci
		WHERE t.id = cci.table_id
		    AND t.name = 'chicago_docsis_arr_idx' ) AS r
WHERE r.id = c.table_id
    AND r.ordinal = c.ordinal;
﻿
Output
Text
|      "table"       |    "name"     | "is_rle_enabled" | "is_ne_enabled" | "raw_size" | "compressed_size" | "is_deltadelta_enabled" | "num_deltadelta_blocks" | "num_rle_blocks" | "num_nerle_blocks" | "num_uncompressed_blocks" | "num_total_blocks" |
|--------------------|---------------|------------------|-----------------|------------|-------------------|-------------------------|-------------------------|------------------|--------------------|---------------------------|--------------------|
| data_type_coverage | col_double    | true             | true            |     900000 |            905216 | false                   |                       0 |                0 |                  0 |                       220 |                220 |
| data_type_coverage | col_int       | true             | true            |     500000 |            507904 | true                    |                       0 |                0 |                  0 |                       123 |                123 |
| data_type_coverage | col_tinyint   | true             | true            |     200000 |            204800 | true                    |                       0 |                0 |                  0 |                        49 |                 49 |
| data_type_coverage | col_date      | true             | true            |     500000 |            294912 | true                    |                      71 |                0 |                  0 |                         0 |                 71 |
| data_type_coverage | col_decimal   | true             | true            |    1100000 |           1105920 | false                   |                       0 |                0 |                  0 |                       269 |                269 |
| data_type_coverage | col_ip        | true             | true            |    1700000 |           1712128 | false                   |                       0 |                0 |                  0 |                       417 |                417 |
| data_type_coverage | col_point     | true             | true            |    1700000 |           1712128 | false                   |                       0 |                0 |                  0 |                       417 |                417 |
| data_type_coverage | col_timestamp | true             | true            |     900000 |            905216 | true                    |                       0 |                0 |                  0 |                       220 |                220 |
| data_type_coverage | col_bigint    | true             | true            |     900000 |            905216 | true                    |                       0 |                0 |                  0 |                       220 |                220 |
| data_type_coverage | col_binary    | true             | true            |     300000 |            307200 | false                   |                       0 |                0 |                  0 |                        74 |                 74 |
| data_type_coverage | col_boolean   | true             | true            |     200000 |            106496 | true                    |                      19 |                0 |                  0 |                         6 |                 25 |
| data_type_coverage | col_ipv4      | true             | true            |     500000 |            507904 | true                    |                       0 |                0 |                  0 |                       123 |                123 |
| data_type_coverage | col_time      | true             | true            |     900000 |            905216 | true                    |                       0 |                0 |                  0 |                       220 |                220 |
| data_type_coverage | col_float     | true             | true            |     500000 |            507904 | false                   |                       0 |                0 |                  0 |                       123 |                123 |
| data_type_coverage | col_uuid      | true             | true            |    1700000 |           1712128 | false                   |                       0 |                0 |                  0 |                       417 |                417 |
| data_type_coverage | col_smallint  | true             | true            |     300000 |            307200 | true                    |                       0 |                0 |                  0 |                        74 |                 74 |
|      "table"       |    "name"     | "is_rle_enabled" | "is_ne_enabled" | "raw_size" | "compressed_size" | "is_deltadelta_enabled" | "num_deltadelta_blocks" | "num_rle_blocks" | "num_nerle_blocks" | "num_uncompressed_blocks" | "num_total_blocks" |
|--------------------|---------------|------------------|-----------------|------------|-------------------|-------------------------|-------------------------|------------------|--------------------|---------------------------|--------------------|
| data_type_coverage | col_double    | true             | true            |     900000 |            905216 | false                   |                       0 |                0 |                  0 |                       220 |                220 |
| data_type_coverage | col_int       | true             | true            |     500000 |            507904 | true                    |                       0 |                0 |                  0 |                       123 |                123 |
| data_type_coverage | col_tinyint   | true             | true            |     200000 |            204800 | true                    |                       0 |                0 |                  0 |                        49 |                 49 |
| data_type_coverage | col_date      | true             | true            |     500000 |            294912 | true                    |                      71 |                0 |                  0 |                         0 |                 71 |
| data_type_coverage | col_decimal   | true             | true            |    1100000 |           1105920 | false                   |                       0 |                0 |                  0 |                       269 |                269 |
| data_type_coverage | col_ip        | true             | true            |    1700000 |           1712128 | false                   |                       0 |                0 |                  0 |                       417 |                417 |
| data_type_coverage | col_point     | true             | true            |    1700000 |           1712128 | false                   |                       0 |                0 |                  0 |                       417 |                417 |
| data_type_coverage | col_timestamp | true             | true            |     900000 |            905216 | true                    |                       0 |                0 |                  0 |                       220 |                220 |
| data_type_coverage | col_bigint    | true             | true            |     900000 |            905216 | true                    |                       0 |                0 |                  0 |                       220 |                220 |
| data_type_coverage | col_binary    | true             | true            |     300000 |            307200 | false                   |                       0 |                0 |                  0 |                        74 |                 74 |
| data_type_coverage | col_boolean   | true             | true            |     200000 |            106496 | true                    |                      19 |                0 |                  0 |                         6 |                 25 |
| data_type_coverage | col_ipv4      | true             | true            |     500000 |            507904 | true                    |                       0 |                0 |                  0 |                       123 |                123 |
| data_type_coverage | col_time      | true             | true            |     900000 |            905216 | true                    |                       0 |                0 |                  0 |                       220 |                220 |
| data_type_coverage | col_float     | true             | true            |     500000 |            507904 | false                   |                       0 |                0 |                  0 |                       123 |                123 |
| data_type_coverage | col_uuid      | true             | true            |    1700000 |           1712128 | false                   |                       0 |                0 |                  0 |                       417 |                417 |
| data_type_coverage | col_smallint  | true             | true            |     300000 |            307200 | true                    |                       0 |                0 |                  0 |                        74 |                 74 |
﻿
For information on the system catalog table queried in this example, see sys.columns_compression_info.
﻿Compression for the Entire Table ﻿
This query summarizes the total compression of the specified table. In the example, replace the table name and schema name accordingly. 
SQL
SELECT SUM(raw_size),
    SUM(compressed_size)
FROM (
    SELECT t.name,
        c.name AS colname,
        co.raw_size,
        co.compressed_size,
        SUM(co.compressed_size) OVER(PARTITION BY t.schema, t.name) AS table_size,
        100 -((100 * co.compressed_size) / co.raw_size) AS col_compress_percent,
        DECIMAL(
           FLOAT(co.compressed_size) * 100 / SUM(co.compressed_size) 
           OVER(PARTITION BY t.schema, t.name),
           3,
           1
           ) AS percent_of_table
    FROM (
        SELECT table_id,
            ordinal,
            raw_size,
            compressed_size
        FROM sys.columns_compression_info
        UNION ALL
        SELECT table_id,
            ordinal,
            raw_size,
            compressed_size
        FROM sys.vl_columns_compression_info
      ) AS co
    JOIN sys.tables AS t ON co.table_id = t.id
    JOIN sys.columns AS c ON c.table_id = t.id
        AND co.ordinal = c.ordinal
    WHERE t.name = 'data_type_coverage'
        AND t.schema = 'loading'
    ORDER BY co.ordinal
    ) AS abc;
SELECT SUM(raw_size),
    SUM(compressed_size)
FROM (
    SELECT t.name,
        c.name AS colname,
        co.raw_size,
        co.compressed_size,
        SUM(co.compressed_size) OVER(PARTITION BY t.schema, t.name) AS table_size,
        100 -((100 * co.compressed_size) / co.raw_size) AS col_compress_percent,
        DECIMAL(
           FLOAT(co.compressed_size) * 100 / SUM(co.compressed_size) 
           OVER(PARTITION BY t.schema, t.name),
           3,
           1
           ) AS percent_of_table
    FROM (
        SELECT table_id,
            ordinal,
            raw_size,
            compressed_size
        FROM sys.columns_compression_info
        UNION ALL
        SELECT table_id,
            ordinal,
            raw_size,
            compressed_size
        FROM sys.vl_columns_compression_info
      ) AS co
    JOIN sys.tables AS t ON co.table_id = t.id
    JOIN sys.columns AS c ON c.table_id = t.id
        AND co.ordinal = c.ordinal
    WHERE t.name = 'data_type_coverage'
        AND t.schema = 'loading'
    ORDER BY co.ordinal
    ) AS abc;
﻿
Output
Text
| "sum(raw_size)" | "sum(compressed_size)" |
|-----------------|------------------------|
|        73788914 |               73592306 |   
| "sum(raw_size)" | "sum(compressed_size)" |
|-----------------|------------------------|
|        73788914 |               73592306 |   
﻿
For information on the system catalog table queried in this example, see sys.columns_compression_info.
﻿Index Size﻿
Check the total size of each index across the different segment groups. 
In this example, the table has three secondary indexes added before loading data. 
SQL
SELECT p.name AS uuid,
    p.part_type,
    i.name AS index_name,
    SUM(p.size) AS sum_size
FROM (
    SELECT *
    FROM sys.segment_parts
    WHERE part_type LIKE 'index'
    ) AS p
    INNER JOIN sys.indexes i ON p.name = i.id
GROUP BY 1,2,3
ORDER BY 1,2;
SELECT p.name AS uuid,
    p.part_type,
    i.name AS index_name,
    SUM(p.size) AS sum_size
FROM (
    SELECT *
    FROM sys.segment_parts
    WHERE part_type LIKE 'index'
    ) AS p
    INNER JOIN sys.indexes i ON p.name = i.id
GROUP BY 1,2,3
ORDER BY 1,2;
﻿
Output
Text
|                 "uuid"                 | "part_type"  | "index_name" | "sum_size" |
|----------------------------------------|--------------|--------------|------------|
| "669cbcdf-772f-4c97-b250-0cc9e573cd7b" | index        | idx01        |    2527232 |
| cccf6f5e-96a8-4621-b947-f318d120f6a0   | index        | idx03        |     180224 |
| d75ae71a-207e-4723-a86c-ecffc798c7d0   | index        | idx02        |    3190784 |
|                 "uuid"                 | "part_type"  | "index_name" | "sum_size" |
|----------------------------------------|--------------|--------------|------------|
| "669cbcdf-772f-4c97-b250-0cc9e573cd7b" | index        | idx01        |    2527232 |
| cccf6f5e-96a8-4621-b947-f318d120f6a0   | index        | idx03        |     180224 |
| d75ae71a-207e-4723-a86c-ecffc798c7d0   | index        | idx02        |    3190784 |
﻿
For information on the system catalog tables queried in this example, see sys.indexes and sys.segment_parts.
Column Sizes
This query finds the size of all columns for the specified table. Before running this query on your system, replace table_name in the example. 
SQL
WITH cte AS (
    SELECT
	    table_id,
	    ordinal,
	    compressed_size
    FROM
	    sys.columns_compression_info
    UNION ALL
    SELECT
	    table_id,
	    ordinal,
	    compressed_size
    FROM
	    sys.vl_columns_compression_info )
SELECT
	( SELECT t.name
	    FROM sys.tables AS t
	    WHERE t.id = cte.table_id ) AS table_name,
	ordinal,
	compressed_size,
	( SELECT name
	    FROM sys.columns AS c
	    WHERE c.table_id = cte.table_id
		    AND c.ordinal = cte.ordinal ) AS name,
	DOUBLE(compressed_size) / SUM(compressed_size) OVER() AS percent_of_total
FROM cte
WHERE
	table_id IN (
	    SELECT id
	    FROM sys.tables
	    WHERE name LIKE 'table_name' )
ORDER BY compressed_size;
WITH cte AS (
    SELECT
	    table_id,
	    ordinal,
	    compressed_size
    FROM
	    sys.columns_compression_info
    UNION ALL
    SELECT
	    table_id,
	    ordinal,
	    compressed_size
    FROM
	    sys.vl_columns_compression_info )
SELECT
	( SELECT t.name
	    FROM sys.tables AS t
	    WHERE t.id = cte.table_id ) AS table_name,
	ordinal,
	compressed_size,
	( SELECT name
	    FROM sys.columns AS c
	    WHERE c.table_id = cte.table_id
		    AND c.ordinal = cte.ordinal ) AS name,
	DOUBLE(compressed_size) / SUM(compressed_size) OVER() AS percent_of_total
FROM cte
WHERE
	table_id IN (
	    SELECT id
	    FROM sys.tables
	    WHERE name LIKE 'table_name' )
ORDER BY compressed_size;
﻿
Output
Text
|    "table_name"    | "ordinal" | "compressed_size" |     "name"     |  "percent_of_total"   |
|--------------------|-----------|-------------------|----------------|-----------------------|
| data_type_coverage |        25 |              8192 |                |    0.0001082748327179 |
| data_type_coverage |        21 |              8192 |                |    0.0001082748327179 |
| data_type_coverage |         2 |            102400 | col_boolean    | 0.0013534354089736837 |
| data_type_coverage |        18 |            204800 | col_tinyint    | 0.0027068708179473675 |
| data_type_coverage |        19 |            266240 |                |  0.003518932063331578 |
| data_type_coverage |         4 |            294912 | col_date       | 0.0038978939778442096 |
| data_type_coverage |        12 |            307200 | col_smallint   |  0.004060306226921052 |
| data_type_coverage |         1 |            307200 | col_binary     |  0.004060306226921052 |
| data_type_coverage |        10 |            507904 | col_ipv4       | 0.0067130396285094715 |
| data_type_coverage |         7 |            507904 | col_float      | 0.0067130396285094715 |
| data_type_coverage |         8 |            507904 | col_int        | 0.0067130396285094715 |
| data_type_coverage |         3 |            699370 | col_char       |  0.009243673066151615 |
| data_type_coverage |        23 |            783147 | col_varbinary  |  0.010350965627260875 |
| data_type_coverage |         6 |            905216 | col_double     |  0.011964369015327365 |
| data_type_coverage |        17 |            905216 | col_timestamp  |  0.011964369015327365 |
| data_type_coverage |        16 |            905216 | col_time       |  0.011964369015327365 |
| data_type_coverage |         0 |            905216 | col_bigint     |  0.011964369015327365 |
| data_type_coverage |         5 |           1105920 | col_decimal    |  0.014617102416915785 |
| data_type_coverage |        13 |           1712128 | col_point      |  0.022629440038039995 |
| data_type_coverage |        22 |           1712128 | col_uuid       |  0.022629440038039995 |
| data_type_coverage |        11 |           1712128 | col_ip         |  0.022629440038039995 |
| data_type_coverage |        20 |           1784388 |                |   0.02358451076706771 |
| data_type_coverage |        24 |           1788957 | col_varchar    |  0.023644899891907562 |
| data_type_coverage |        14 |           8500000 | col_linestring |   0.11234571265894837 |
| data_type_coverage |         9 |          21477958 | col_int_array  |   0.28387723505517193 |
| data_type_coverage |        15 |          27739482 | col_polygon    |    0.3666366910682436 |
|    "table_name"    | "ordinal" | "compressed_size" |     "name"     |  "percent_of_total"   |
|--------------------|-----------|-------------------|----------------|-----------------------|
| data_type_coverage |        25 |              8192 |                |    0.0001082748327179 |
| data_type_coverage |        21 |              8192 |                |    0.0001082748327179 |
| data_type_coverage |         2 |            102400 | col_boolean    | 0.0013534354089736837 |
| data_type_coverage |        18 |            204800 | col_tinyint    | 0.0027068708179473675 |
| data_type_coverage |        19 |            266240 |                |  0.003518932063331578 |
| data_type_coverage |         4 |            294912 | col_date       | 0.0038978939778442096 |
| data_type_coverage |        12 |            307200 | col_smallint   |  0.004060306226921052 |
| data_type_coverage |         1 |            307200 | col_binary     |  0.004060306226921052 |
| data_type_coverage |        10 |            507904 | col_ipv4       | 0.0067130396285094715 |
| data_type_coverage |         7 |            507904 | col_float      | 0.0067130396285094715 |
| data_type_coverage |         8 |            507904 | col_int        | 0.0067130396285094715 |
| data_type_coverage |         3 |            699370 | col_char       |  0.009243673066151615 |
| data_type_coverage |        23 |            783147 | col_varbinary  |  0.010350965627260875 |
| data_type_coverage |         6 |            905216 | col_double     |  0.011964369015327365 |
| data_type_coverage |        17 |            905216 | col_timestamp  |  0.011964369015327365 |
| data_type_coverage |        16 |            905216 | col_time       |  0.011964369015327365 |
| data_type_coverage |         0 |            905216 | col_bigint     |  0.011964369015327365 |
| data_type_coverage |         5 |           1105920 | col_decimal    |  0.014617102416915785 |
| data_type_coverage |        13 |           1712128 | col_point      |  0.022629440038039995 |
| data_type_coverage |        22 |           1712128 | col_uuid       |  0.022629440038039995 |
| data_type_coverage |        11 |           1712128 | col_ip         |  0.022629440038039995 |
| data_type_coverage |        20 |           1784388 |                |   0.02358451076706771 |
| data_type_coverage |        24 |           1788957 | col_varchar    |  0.023644899891907562 |
| data_type_coverage |        14 |           8500000 | col_linestring |   0.11234571265894837 |
| data_type_coverage |         9 |          21477958 | col_int_array  |   0.28387723505517193 |
| data_type_coverage |        15 |          27739482 | col_polygon    |    0.3666366910682436 |
﻿
This output is based on a query using the table data_type_coverage. 
Column Cardinality
This query finds the cardinality of each column in the specified table. Knowing the cardinality is useful for determining which compression scheme to use for a column. 
SQL
SELECT t.schema,
    t.name,
    c.name,
    c.ordinal,
    cc.cardinality
FROM sys.column_cardinalities AS cc
    LEFT JOIN sys.tables AS t ON cc.table_id = t.id
    LEFT JOIN sys.columns AS c ON cc.column_id = c.id
WHERE t.name LIKE 'data_type_coverage'
ORDER BY c.ordinal;
SELECT t.schema,
    t.name,
    c.name,
    c.ordinal,
    cc.cardinality
FROM sys.column_cardinalities AS cc
    LEFT JOIN sys.tables AS t ON cc.table_id = t.id
    LEFT JOIN sys.columns AS c ON cc.column_id = c.id
WHERE t.name LIKE 'data_type_coverage'
ORDER BY c.ordinal;
﻿
Output
Text

| "schema" |       "name"       |    "name_1"    | "ordinal" | "cardinality" |
|----------|--------------------|----------------|-----------|---------------|
| loading  | data_type_coverage | col_bigint     |         0 |         98551 |
| loading  | data_type_coverage | col_binary     |         1 |         48048 |
| loading  | data_type_coverage | col_boolean    |         2 |             2 |
| loading  | data_type_coverage | col_char       |         3 |         95206 |
| loading  | data_type_coverage | col_date       |         4 |         29634 |
| loading  | data_type_coverage | col_decimal    |         5 |         20059 |
| loading  | data_type_coverage | col_double     |         6 |         53546 |
| loading  | data_type_coverage | col_float      |         7 |         75680 |
| loading  | data_type_coverage | col_int        |         8 |         99319 |
| loading  | data_type_coverage | col_int_array  |         9 |       5000855 |
| loading  | data_type_coverage | col_int_array  |         9 |         99899 |
| loading  | data_type_coverage | col_ipv4       |        10 |         99908 |
| loading  | data_type_coverage | col_ip         |        11 |         99875 |
| loading  | data_type_coverage | col_smallint   |        12 |         51227 |
| loading  | data_type_coverage | col_point      |        13 |        100000 |
| loading  | data_type_coverage | col_linestring |        14 |        100000 |
| loading  | data_type_coverage | col_polygon    |        15 |         99153 |
| loading  | data_type_coverage | col_time       |        16 |         99902 |
| loading  | data_type_coverage | col_timestamp  |        17 |         99829 |
| loading  | data_type_coverage | col_tinyint    |        18 |           256 |
| loading  | data_type_coverage | col_uuid       |        22 |         99892 |
| loading  | data_type_coverage | col_varbinary  |        23 |         68834 |
| loading  | data_type_coverage | col_varchar    |        24 |         93258 |
| loading  | data_type_coverage |                |           |         10410 |
| loading  | data_type_coverage |                |           |             2 |
| loading  | data_type_coverage |                |           |             1 |
| loading  | data_type_coverage |                |           |         93333 |
| loading  | data_type_coverage |                |           |             1 |

| "schema" |       "name"       |    "name_1"    | "ordinal" | "cardinality" |
|----------|--------------------|----------------|-----------|---------------|
| loading  | data_type_coverage | col_bigint     |         0 |         98551 |
| loading  | data_type_coverage | col_binary     |         1 |         48048 |
| loading  | data_type_coverage | col_boolean    |         2 |             2 |
| loading  | data_type_coverage | col_char       |         3 |         95206 |
| loading  | data_type_coverage | col_date       |         4 |         29634 |
| loading  | data_type_coverage | col_decimal    |         5 |         20059 |
| loading  | data_type_coverage | col_double     |         6 |         53546 |
| loading  | data_type_coverage | col_float      |         7 |         75680 |
| loading  | data_type_coverage | col_int        |         8 |         99319 |
| loading  | data_type_coverage | col_int_array  |         9 |       5000855 |
| loading  | data_type_coverage | col_int_array  |         9 |         99899 |
| loading  | data_type_coverage | col_ipv4       |        10 |         99908 |
| loading  | data_type_coverage | col_ip         |        11 |         99875 |
| loading  | data_type_coverage | col_smallint   |        12 |         51227 |
| loading  | data_type_coverage | col_point      |        13 |        100000 |
| loading  | data_type_coverage | col_linestring |        14 |        100000 |
| loading  | data_type_coverage | col_polygon    |        15 |         99153 |
| loading  | data_type_coverage | col_time       |        16 |         99902 |
| loading  | data_type_coverage | col_timestamp  |        17 |         99829 |
| loading  | data_type_coverage | col_tinyint    |        18 |           256 |
| loading  | data_type_coverage | col_uuid       |        22 |         99892 |
| loading  | data_type_coverage | col_varbinary  |        23 |         68834 |
| loading  | data_type_coverage | col_varchar    |        24 |         93258 |
| loading  | data_type_coverage |                |           |         10410 |
| loading  | data_type_coverage |                |           |             2 |
| loading  | data_type_coverage |                |           |             1 |
| loading  | data_type_coverage |                |           |         93333 |
| loading  | data_type_coverage |                |           |             1 |
﻿
For information on the system catalog table queried in this example, see sys.column_cardinalities.
Cluster and Node System Catalog Tables
For specific column definitions of the system catalog tables referenced in these examples, see the System section of the System Catalog page.   
Nodes Present on a System
Check the status of all nodes in the system.
SQL
SELECT name,
    status
FROM sys.nodes; 
SELECT name,
    status
FROM sys.nodes; 
﻿
Output
Text
|   "name"    | "status" |
|-------------|----------|
| foundation2 | ACCEPTED |
| sql         | ACCEPTED |
| loader      | ACCEPTED |
| foundation1 | ACCEPTED |
| foundation0 | ACCEPTED |

|   "name"    | "status" |
|-------------|----------|
| foundation2 | ACCEPTED |
| sql         | ACCEPTED |
| loader      | ACCEPTED |
| foundation1 | ACCEPTED |
| foundation0 | ACCEPTED |
﻿
Nodes Roles on a System
Check the roles of each node on the system.
SQL
SELECT
	n.name, n.status,
	STRING_AGG(DISTINCT c.name, ', ' ORDER BY c.name) in_clusters,
	STRING_AGG(DISTINCT sr.service_role_type, ', ' ORDER BY sr.service_role_type) with_roles
FROM
	sys.nodes n
	LEFT JOIN sys.node_clusters nc ON nc.node_id = n.id
	LEFT JOIN sys.clusters c ON c.id = nc.cluster_id
	LEFT JOIN sys.service_roles sr ON sr.node_id = n.id
GROUP BY
	n.name, n.status
ORDER BY
	n.name;
SELECT
	n.name, n.status,
	STRING_AGG(DISTINCT c.name, ', ' ORDER BY c.name) in_clusters,
	STRING_AGG(DISTINCT sr.service_role_type, ', ' ORDER BY sr.service_role_type) with_roles
FROM
	sys.nodes n
	LEFT JOIN sys.node_clusters nc ON nc.node_id = n.id
	LEFT JOIN sys.clusters c ON c.id = nc.cluster_id
	LEFT JOIN sys.service_roles sr ON sr.node_id = n.id
GROUP BY
	n.name, n.status
ORDER BY
	n.name;
﻿
Output
Text
|   "name"    | "status" |    "in_clusters"    |      "with_roles"       |                |             |              |
|-------------|----------|---------------------|-------------------------|----------------|-------------|--------------|
| foundation0 | ACCEPTED | "foundation_cluster |  foundation_cluster-vm" | "health        |  lts        |  operatorvm" |
| foundation1 | ACCEPTED | "foundation_cluster |  foundation_cluster-vm" | "health        |  lts        |  operatorvm" |
| foundation2 | ACCEPTED | "foundation_cluster |  foundation_cluster-vm" | "health        |  lts        |  operatorvm" |
| loader      | ACCEPTED |                     | "health                 |  streamloader" |             |              |
| sql         | ACCEPTED | initial-vm          | "admin                  |  health        |  operatorvm |  sql"        |



|   "name"    | "status" |    "in_clusters"    |      "with_roles"       |                |             |              |
|-------------|----------|---------------------|-------------------------|----------------|-------------|--------------|
| foundation0 | ACCEPTED | "foundation_cluster |  foundation_cluster-vm" | "health        |  lts        |  operatorvm" |
| foundation1 | ACCEPTED | "foundation_cluster |  foundation_cluster-vm" | "health        |  lts        |  operatorvm" |
| foundation2 | ACCEPTED | "foundation_cluster |  foundation_cluster-vm" | "health        |  lts        |  operatorvm" |
| loader      | ACCEPTED |                     | "health                 |  streamloader" |             |              |
| sql         | ACCEPTED | initial-vm          | "admin                  |  health        |  operatorvm |  sql"        |


﻿
Node Status
Check the operational status, software versions, and assigned roles of your nodes. Possible status values include ACTIVE, STARTING, STOPPING, ERROR, UNKNOWN, or UNREACHABLE.
SQL
SELECT name,
    operational_status,
    software_version,
    ARRAY_AGG(service_role_type)
FROM sys.node_status AS ns
    LEFT JOIN sys.nodes AS n ON ns.node_id = id
    LEFT JOIN sys.service_roles AS sr ON sr.node_id = n.id
GROUP BY name,
    operational_status,
    software_version;
SELECT name,
    operational_status,
    software_version,
    ARRAY_AGG(service_role_type)
FROM sys.node_status AS ns
    LEFT JOIN sys.nodes AS n ON ns.node_id = id
    LEFT JOIN sys.service_roles AS sr ON sr.node_id = n.id
GROUP BY name,
    operational_status,
    software_version;
﻿
Output
Text
|    name     | operational_status | software_version | array_agg(service_role_type:) |
|-------------|--------------------|------------------|-------------------------------|
| loader      | Active             |           24.0.0 | ['health','streamloader']     |
| foundation2 | Active             |           24.0.0 | ['health','lts','operatorvm'] |
|    name     | operational_status | software_version | array_agg(service_role_type:) |
|-------------|--------------------|------------------|-------------------------------|
| loader      | Active             |           24.0.0 | ['health','streamloader']     |
| foundation2 | Active             |           24.0.0 | ['health','lts','operatorvm'] |
﻿
Foundation Nodes on a Specific Cluster
Check for all Foundation Nodes on your system clusters.  
SQL
SELECT n.name,
    r.name
FROM sys.nodes AS n,
    ( SELECT *
        FROM sys.clusters AS c,
            sys.node_clusters AS nc
        WHERE c.cluster_type = 'Foundation'
            AND c.id = nc.cluster_id ) AS r
WHERE r.node_id = n.id;
SELECT n.name,
    r.name
FROM sys.nodes AS n,
    ( SELECT *
        FROM sys.clusters AS c,
            sys.node_clusters AS nc
        WHERE c.cluster_type = 'Foundation'
            AND c.id = nc.cluster_id ) AS r
WHERE r.node_id = n.id;
﻿
Output
Text
| foundation2 | foundation_cluster |
|-------------|--------------------|
| foundation0 | foundation_cluster |
| foundation1 | foundation_cluster |
| foundation2 | foundation_cluster |
|-------------|--------------------|
| foundation0 | foundation_cluster |
| foundation1 | foundation_cluster |
﻿
Drive Status
Check the status of all drives assigned to nodes in the system. 
SQL
SELECT
    n.name AS node_name,
    s.node_id,
    s.id AS serial_number,
    s.pci_address,
    s.device_status,
    s.device_model
FROM
    sys.nodes AS n
JOIN sys.storage_device_status AS s
    ON n.id = s.node_id; 
SELECT
    n.name AS node_name,
    s.node_id,
    s.id AS serial_number,
    s.pci_address,
    s.device_status,
    s.device_model
FROM
    sys.nodes AS n
JOIN sys.storage_device_status AS s
    ON n.id = s.node_id; 
﻿
Output
Text
| "node_name" |               "node_id"                |            "serial_number"             |                      "pci_address"                       | "device_status" | "device_model" |
|-------------|----------------------------------------|----------------------------------------|----------------------------------------------------------|-----------------|----------------|
| foundation1 | "6a96b307-57bc-477f-952c-e6cab38a7922" | "72062649-396c-4424-9da5-c90cf0589e00" | /var/opt/ocient/72062649-396c-4424-9da5-c90cf0589e00.dat | ""              | NON-NVME DRIVE |
| foundation1 | "6a96b307-57bc-477f-952c-e6cab38a7922" | ee2f0013-c7b7-4750-833e-3d717af85001   | /var/opt/ocient/ee2f0013-c7b7-4750-833e-3d717af85001.dat | ""              | NON-NVME DRIVE |
| loader      | "83f0ecf9-9a31-4a56-85b9-7eb610447185" | ff3186fb-502f-417c-bcab-8b7aa3de8800   | /var/opt/ocient/ff3186fb-502f-417c-bcab-8b7aa3de8800.dat | ""              | NON-NVME DRIVE |
| loader      | "83f0ecf9-9a31-4a56-85b9-7eb610447185" | "11ff09e2-a6c1-4f76-ae96-3a97eaf02a01" | /var/opt/ocient/11ff09e2-a6c1-4f76-ae96-3a97eaf02a01.dat | ""              | NON-NVME DRIVE |
| foundation0 | "9330a0b3-b3b7-4503-949c-043b196c0cc4" | "6156962f-fcf6-4299-bbd7-2618fc6f1d00" | /var/opt/ocient/6156962f-fcf6-4299-bbd7-2618fc6f1d00.dat | ""              | NON-NVME DRIVE |
| foundation0 | "9330a0b3-b3b7-4503-949c-043b196c0cc4" | e6a4b24f-0d49-4704-b7ce-18af163c0701   | /var/opt/ocient/e6a4b24f-0d49-4704-b7ce-18af163c0701.dat | ""              | NON-NVME DRIVE |
| sql         | "1198a86f-549d-4736-ac31-1ba54a2b02e7" | "37203a6c-9deb-47cf-8194-95c4eecd7300" | /var/opt/ocient/37203a6c-9deb-47cf-8194-95c4eecd7300.dat | ""              | NON-NVME DRIVE |
| sql         | "1198a86f-549d-4736-ac31-1ba54a2b02e7" | "0921d62f-a31c-4681-9a56-82cb1450a401" | /var/opt/ocient/0921d62f-a31c-4681-9a56-82cb1450a401.dat | ""              | NON-NVME DRIVE |
| foundation2 | babadc9c-f2a3-4512-ab9a-bab3fd88a544   | "842881df-b71c-4cfc-847b-19517e8b2800" | /var/opt/ocient/842881df-b71c-4cfc-847b-19517e8b2800.dat | ""              | NON-NVME DRIVE |
| foundation2 | babadc9c-f2a3-4512-ab9a-bab3fd88a544   | fc0e490a-a295-491d-b6a6-8b3c153a7301   | /var/opt/ocient/fc0e490a-a295-491d-b6a6-8b3c153a7301.dat | ""              | NON-NVME DRIVE |
| "node_name" |               "node_id"                |            "serial_number"             |                      "pci_address"                       | "device_status" | "device_model" |
|-------------|----------------------------------------|----------------------------------------|----------------------------------------------------------|-----------------|----------------|
| foundation1 | "6a96b307-57bc-477f-952c-e6cab38a7922" | "72062649-396c-4424-9da5-c90cf0589e00" | /var/opt/ocient/72062649-396c-4424-9da5-c90cf0589e00.dat | ""              | NON-NVME DRIVE |
| foundation1 | "6a96b307-57bc-477f-952c-e6cab38a7922" | ee2f0013-c7b7-4750-833e-3d717af85001   | /var/opt/ocient/ee2f0013-c7b7-4750-833e-3d717af85001.dat | ""              | NON-NVME DRIVE |
| loader      | "83f0ecf9-9a31-4a56-85b9-7eb610447185" | ff3186fb-502f-417c-bcab-8b7aa3de8800   | /var/opt/ocient/ff3186fb-502f-417c-bcab-8b7aa3de8800.dat | ""              | NON-NVME DRIVE |
| loader      | "83f0ecf9-9a31-4a56-85b9-7eb610447185" | "11ff09e2-a6c1-4f76-ae96-3a97eaf02a01" | /var/opt/ocient/11ff09e2-a6c1-4f76-ae96-3a97eaf02a01.dat | ""              | NON-NVME DRIVE |
| foundation0 | "9330a0b3-b3b7-4503-949c-043b196c0cc4" | "6156962f-fcf6-4299-bbd7-2618fc6f1d00" | /var/opt/ocient/6156962f-fcf6-4299-bbd7-2618fc6f1d00.dat | ""              | NON-NVME DRIVE |
| foundation0 | "9330a0b3-b3b7-4503-949c-043b196c0cc4" | e6a4b24f-0d49-4704-b7ce-18af163c0701   | /var/opt/ocient/e6a4b24f-0d49-4704-b7ce-18af163c0701.dat | ""              | NON-NVME DRIVE |
| sql         | "1198a86f-549d-4736-ac31-1ba54a2b02e7" | "37203a6c-9deb-47cf-8194-95c4eecd7300" | /var/opt/ocient/37203a6c-9deb-47cf-8194-95c4eecd7300.dat | ""              | NON-NVME DRIVE |
| sql         | "1198a86f-549d-4736-ac31-1ba54a2b02e7" | "0921d62f-a31c-4681-9a56-82cb1450a401" | /var/opt/ocient/0921d62f-a31c-4681-9a56-82cb1450a401.dat | ""              | NON-NVME DRIVE |
| foundation2 | babadc9c-f2a3-4512-ab9a-bab3fd88a544   | "842881df-b71c-4cfc-847b-19517e8b2800" | /var/opt/ocient/842881df-b71c-4cfc-847b-19517e8b2800.dat | ""              | NON-NVME DRIVE |
| foundation2 | babadc9c-f2a3-4512-ab9a-bab3fd88a544   | fc0e490a-a295-491d-b6a6-8b3c153a7301   | /var/opt/ocient/fc0e490a-a295-491d-b6a6-8b3c153a7301.dat | ""              | NON-NVME DRIVE |
﻿
Roles, Privileges, and Service Class System Catalog Tables
For specific column definitions of the system catalog tables referenced in these examples, see the User Management section of the System Catalog page.   
Group Assignment for Users
Check for all users on the system and their corresponding assigned group. 
SQL
SELECT g.name AS group_name,
    r.user_name
FROM sys.groups AS g,
    ( SELECT u.user_name,
        ug.group_id
        FROM sys.users AS u,
            sys.user_groups AS ug
        WHERE u.id = ug.user_id ) AS r
ORDER BY g.name ASC;
SELECT g.name AS group_name,
    r.user_name
FROM sys.groups AS g,
    ( SELECT u.user_name,
        ug.group_id
        FROM sys.users AS u,
            sys.user_groups AS ug
        WHERE u.id = ug.user_id ) AS r
ORDER BY g.name ASC;
﻿
Output
Text
| group_name | user_name |
|------------|-----------|
| DBA        | Humphrey  |
| group_name | user_name |
|------------|-----------|
| DBA        | Humphrey  |
﻿
User Roles
Check for all users on the system and their assigned role. 
SQL
SELECT rl.name AS role_name,
    r.user_name
FROM sys.roles AS rl,
    ( SELECT u.user_name,
        ur.role_id
        FROM sys.users AS u,
            sys.user_roles AS ur
        WHERE u.id = ur.user_id ) AS r
ORDER BY rl.name ASC;
SELECT rl.name AS role_name,
    r.user_name
FROM sys.roles AS rl,
    ( SELECT u.user_name,
        ur.role_id
        FROM sys.users AS u,
            sys.user_roles AS ur
        WHERE u.id = ur.user_id ) AS r
ORDER BY rl.name ASC;
﻿
Output
Text
|       role_name        | user_name |
|------------------------|-----------|
| system administrator   | Humphrey  |
| system analyst         | Grimey    |
| database administrator | Charles   |
| database analyst       | Edna      |
|       role_name        | user_name |
|------------------------|-----------|
| system administrator   | Humphrey  |
| system analyst         | Grimey    |
| database administrator | Charles   |
| database analyst       | Edna      |
﻿
User Privileges
Check the privileges for the specific table name. Before running on your system, replace username in the example. 
SQL
SELECT t.schema AS db_name,
    t.name AS table_name,
    r.privilege
FROM sys.tables AS t,
    ( SELECT object_type,
        object_id,
        privilege
        FROM sys.PRIVILEGES 
        WHERE grantee = 'username' ) AS r
WHERE r.object_id = t.id
ORDER BY t.schema, t.name ASC;
SELECT t.schema AS db_name,
    t.name AS table_name,
    r.privilege
FROM sys.tables AS t,
    ( SELECT object_type,
        object_id,
        privilege
        FROM sys.PRIVILEGES 
        WHERE grantee = 'username' ) AS r
WHERE r.object_id = t.id
ORDER BY t.schema, t.name ASC;
﻿
Output
Text
|  db_name |     table_name     |  privilege  |
|----------|--------------------|-------------|
| loading  | data_type_coverage | VIEW        |
| loading  | data_type_coverage | SELECT      |
|  db_name |     table_name     |  privilege  |
|----------|--------------------|-------------|
| loading  | data_type_coverage | VIEW        |
| loading  | data_type_coverage | SELECT      |
﻿
﻿Privileges for the Specific Table﻿
Check the granted privileges across all database objects of the specific type, such as GROUP, TABLE, or DATABASE.  In this example, the query checks for TABLE privileges. 
SQL
SELECT p.grantee,
    t.schema,
    t.name,
    p.privilege
FROM sys.tables AS t,
    sys.privileges AS p
WHERE object_type = 'TABLE'
    AND p.object_id = t.id;
SELECT p.grantee,
    t.schema,
    t.name,
    p.privilege
FROM sys.tables AS t,
    sys.privileges AS p
WHERE object_type = 'TABLE'
    AND p.object_id = t.id;
﻿
Output
Text

| admin@system | admin@system |   example_table    | DELETE  |
|--------------|--------------|--------------------|---------|
| admin@system | admin@system | example_table      | DROP    |
| admin@system | admin@system | example_table      | SYSAUTH |
| admin@system | admin@system | example_table      | LOAD    |
| admin@system | admin@system | example_table      | INSERT  |
| admin@system | admin@system | example_table      | ALTER   |
| admin@system | admin@system | example_table      | SELECT  |
| admin@system | admin@system | example_table      | VIEW    |

| admin@system | admin@system |   example_table    | DELETE  |
|--------------|--------------|--------------------|---------|
| admin@system | admin@system | example_table      | DROP    |
| admin@system | admin@system | example_table      | SYSAUTH |
| admin@system | admin@system | example_table      | LOAD    |
| admin@system | admin@system | example_table      | INSERT  |
| admin@system | admin@system | example_table      | ALTER   |
| admin@system | admin@system | example_table      | SELECT  |
| admin@system | admin@system | example_table      | VIEW    |
﻿
﻿Groups Assigned to Service Classes﻿
Check for the service class assignment for all groups. 
SQL
SELECT g.name,
    sc.name
FROM sys.groups AS g,
    sys.service_classes AS sc
WHERE g.service_class_id = sc.id;
SELECT g.name,
    sc.name
FROM sys.groups AS g,
    sys.service_classes AS sc
WHERE g.service_class_id = sc.id;
﻿
Output
Text
|  group  |    sc_name    |
|---------|---------------|
| analyst | high_priority |
| dbadmin | low_priority  |
|  group  |    sc_name    |
|---------|---------------|
| analyst | high_priority |
| dbadmin | low_priority  |
﻿
Service Class Settings
Check for the settings for the specified service class. Before running on your system, replace the high priority service class name in the example. 
For details about service class settings, see Workload Management and Service Classes﻿.
SQL
SELECT * 
FROM sys.groups AS g, 
    sys.service_classes AS sc
WHERE g.service_class_id = sc.id
    AND sc.name = 'high_priority'; 
SELECT * 
FROM sys.groups AS g, 
    sys.service_classes AS sc
WHERE g.service_class_id = sc.id
    AND sc.name = 'high_priority'; 
﻿
Output
Text
| id                                   | name    | database_id                          | service_class_id                     | id_1                                 | database_id_1                        | name_1        | max_temp_disk_usage | max_elapsed_time | max_concurrent_queries | max_rows_returned | scheduling_priority | cache_max_bytes | cache_max_time | max_elapsed_time_for_caching | max_columns_in_result_set | priority_adjustment_factor | priority_adjustment_time | min_priority | max_priority | statement_text | statement_text_matcher_type | half_parallelism | load_balance_shuffle | parallelism | memory_optimal_strategy |
|--------------------------------------|---------|--------------------------------------|--------------------------------------|--------------------------------------|--------------------------------------|---------------|---------------------|------------------|------------------------|-------------------|---------------------|-----------------|----------------|------------------------------|---------------------------|----------------------------|--------------------------|--------------|--------------|----------------|-----------------------------|------------------|----------------------|-------------|-------------------------|
| e9c92a1b-32a8-4f88-ab72-485ff8b24f53 | analyst | e80010d7-6f26-438c-8461-11af309ed8a3 | 83b76f87-6634-4cc9-8252-9300caeefdf1 | 83b76f87-6634-4cc9-8252-9300caeefdf1 | e80010d7-6f26-438c-8461-11af309ed8a3 | high_priority | 80                  | 100              | 10                     | 100               | 5                   | 1,000           | 25             | 50                           | -1                        | 0                          | 0                        | 0            | -1           |                |                             |                  |                      |             | false                   |
| id                                   | name    | database_id                          | service_class_id                     | id_1                                 | database_id_1                        | name_1        | max_temp_disk_usage | max_elapsed_time | max_concurrent_queries | max_rows_returned | scheduling_priority | cache_max_bytes | cache_max_time | max_elapsed_time_for_caching | max_columns_in_result_set | priority_adjustment_factor | priority_adjustment_time | min_priority | max_priority | statement_text | statement_text_matcher_type | half_parallelism | load_balance_shuffle | parallelism | memory_optimal_strategy |
|--------------------------------------|---------|--------------------------------------|--------------------------------------|--------------------------------------|--------------------------------------|---------------|---------------------|------------------|------------------------|-------------------|---------------------|-----------------|----------------|------------------------------|---------------------------|----------------------------|--------------------------|--------------|--------------|----------------|-----------------------------|------------------|----------------------|-------------|-------------------------|
| e9c92a1b-32a8-4f88-ab72-485ff8b24f53 | analyst | e80010d7-6f26-438c-8461-11af309ed8a3 | 83b76f87-6634-4cc9-8252-9300caeefdf1 | 83b76f87-6634-4cc9-8252-9300caeefdf1 | e80010d7-6f26-438c-8461-11af309ed8a3 | high_priority | 80                  | 100              | 10                     | 100               | 5                   | 1,000           | 25             | 50                           | -1                        | 0                          | 0                        | 0            | -1           |                |                             |                  |                      |             | false                   |
﻿
﻿Service Classes for the Specific User﻿
Check for the service class of the specific user. Before running on your system, replace the jmack@test username in this example with the specific username that you want to query. 
SQL
SELECT r2.name,
    sc.name,
    r2.user_name
FROM sys.service_classes AS sc,
    ( SELECT g.name,
        g.service_class_id,
        r.user_name
        FROM sys.groups AS g,
            ( SELECT u.user_name,
                ug.group_id
                FROM sys.users AS u,
                    sys.user_groups AS ug
                WHERE u.id = ug.user_id
                    AND u.user_name = 'jmack@test' ) AS r ) AS r2;
SELECT r2.name,
    sc.name,
    r2.user_name
FROM sys.service_classes AS sc,
    ( SELECT g.name,
        g.service_class_id,
        r.user_name
        FROM sys.groups AS g,
            ( SELECT u.user_name,
                ug.group_id
                FROM sys.users AS u,
                    sys.user_groups AS ug
                WHERE u.id = ug.user_id
                    AND u.user_name = 'jmack@test' ) AS r ) AS r2;
﻿
Output
Text
|  group  |  service_class  |  user_name  |
|---------|-----------------|-------------|
| analyst | Default         | jmack@test  |
| analyst | high_priority   | jmack@test  |
|  group  |  service_class  |  user_name  |
|---------|-----------------|-------------|
| analyst | Default         | jmack@test  |
| analyst | high_priority   | jmack@test  |
﻿
Data Pipelines
Pipeline Events 
While your pipeline is running, the pipeline generates events in the sys.pipeline_events system catalog table to mark significant checkpoints when something has occurred. In this example, you run the CREATE PIPELINE and START PIPELINE SQL statements on the pipeline named my_pipeline and let the pipeline complete. As expected, the CREATED, STARTED, and COMPLETED events appear. For details about the lists of files, see the sys.pipeline_files system catalog table.
Pipeline events include messages from many different tasks. These events are the background processes that execute across different Loader Nodes during pipeline operation. For details about tasks, you can query the sys.tasks and sys.subtasks system catalog tables.
SQL
SELECT * 
FROM sys.pipeline_events 
ORDER BY event_timestamp;
SELECT * 
FROM sys.pipeline_events 
ORDER BY event_timestamp;
﻿
Output
SQL
+--------------------------------------+--------------------------------------+--------------------------------------+------------------------+-----------------------------------------------------------------------+----------------------------+
| pipeline_id                          | task_id                              | user_id                              | event_type             | event_message                                                         | event_timestamp            |
|--------------------------------------+--------------------------------------+--------------------------------------+------------------------+-----------------------------------------------------------------------+----------------------------|
| d8848467-9262-4bd6-84a7-1380c95f8b8b |                                      | dde90d1b-bfcf-4b48-a251-b9ea812a7b26 | CREATED                | Created pipeline my_pipeline                                          | 2024-02-07 18:04:32.822690 |
| d8848467-9262-4bd6-84a7-1380c95f8b8b |                                      | dde90d1b-bfcf-4b48-a251-b9ea812a7b26 | STARTED                | Started processing pipeline my_pipeline                               | 2024-02-07 18:39:52.682276 |
| d8848467-9262-4bd6-84a7-1380c95f8b8b |                                      |                                      | FILE_LISTING_STARTED   | File listing started for pipeline my_pipeline. This may take a while. | 2024-02-07 18:39:52.848000 |
| d8848467-9262-4bd6-84a7-1380c95f8b8b |                                      |                                      | FILE_LISTING_COMPLETED | File listing completed for pipeline my_pipeline. 1 files were listed. | 2024-02-07 18:39:52.863000 |
| d8848467-9262-4bd6-84a7-1380c95f8b8b | 61007c85-6744-42e7-9401-66e1a594e990 |                                      | EXTRACTION_STARTED     | Extraction started                                                    | 2024-02-07 18:39:58.367000 |
| d8848467-9262-4bd6-84a7-1380c95f8b8b | 61007c85-6744-42e7-9401-66e1a594e990 |                                      | EXTRACTION_COMPLETED   | Extraction completed                                                  | 2024-02-07 18:39:59.417000 |
| d8848467-9262-4bd6-84a7-1380c95f8b8b | 49c17f79-0108-4b05-aae5-9ae9a13abaca |                                      | COMPLETED              | Completed processing pipeline my_pipeline                             | 2024-02-07 18:40:03.808543 |
+--------------------------------------+--------------------------------------+--------------------------------------+------------------------+-----------------------------------------------------------------------+----------------------------+
Fetched 7 rows
+--------------------------------------+--------------------------------------+--------------------------------------+------------------------+-----------------------------------------------------------------------+----------------------------+
| pipeline_id                          | task_id                              | user_id                              | event_type             | event_message                                                         | event_timestamp            |
|--------------------------------------+--------------------------------------+--------------------------------------+------------------------+-----------------------------------------------------------------------+----------------------------|
| d8848467-9262-4bd6-84a7-1380c95f8b8b |                                      | dde90d1b-bfcf-4b48-a251-b9ea812a7b26 | CREATED                | Created pipeline my_pipeline                                          | 2024-02-07 18:04:32.822690 |
| d8848467-9262-4bd6-84a7-1380c95f8b8b |                                      | dde90d1b-bfcf-4b48-a251-b9ea812a7b26 | STARTED                | Started processing pipeline my_pipeline                               | 2024-02-07 18:39:52.682276 |
| d8848467-9262-4bd6-84a7-1380c95f8b8b |                                      |                                      | FILE_LISTING_STARTED   | File listing started for pipeline my_pipeline. This may take a while. | 2024-02-07 18:39:52.848000 |
| d8848467-9262-4bd6-84a7-1380c95f8b8b |                                      |                                      | FILE_LISTING_COMPLETED | File listing completed for pipeline my_pipeline. 1 files were listed. | 2024-02-07 18:39:52.863000 |
| d8848467-9262-4bd6-84a7-1380c95f8b8b | 61007c85-6744-42e7-9401-66e1a594e990 |                                      | EXTRACTION_STARTED     | Extraction started                                                    | 2024-02-07 18:39:58.367000 |
| d8848467-9262-4bd6-84a7-1380c95f8b8b | 61007c85-6744-42e7-9401-66e1a594e990 |                                      | EXTRACTION_COMPLETED   | Extraction completed                                                  | 2024-02-07 18:39:59.417000 |
| d8848467-9262-4bd6-84a7-1380c95f8b8b | 49c17f79-0108-4b05-aae5-9ae9a13abaca |                                      | COMPLETED              | Completed processing pipeline my_pipeline                             | 2024-02-07 18:40:03.808543 |
+--------------------------------------+--------------------------------------+--------------------------------------+------------------------+-----------------------------------------------------------------------+----------------------------+
Fetched 7 rows
﻿
Pipeline Metrics
Find metrics that measure the performance and activity of a pipeline in the sys.pipeline_metrics system catalog table. This table contains one row for each metric at a specified point in time. A common use of this data is to plot metric values over time to examine the behavior of a pipeline.
Metric Types
Metrics are either instantaneous or incremental:
Instantaneous metrics reflect the current value of a counter at the time of the metric collection. These values can increase or decrease based on the current state or rate of the metric.
Incremental metrics increment over time and reflect the cumulative value for a specified counter.
Example
The first snapshot of a metric appears when it is initialized. Afterward, the metric is updated every 10 seconds. The updated_at column shows the metric collection time.
For example, if you are interested in the number of the transformed record bytes, you can run this query. The value increases over time as the pipeline runs.
SQL
SELECT * 
FROM sys.pipeline_metrics 
WHERE name = 'count.record.bytes.transformed' 
ORDER BY updated_at;
SELECT * 
FROM sys.pipeline_metrics 
WHERE name = 'count.record.bytes.transformed' 
ORDER BY updated_at;
﻿
Output
SQL
+--------------------------------------+--------------------------------------+----------------+--------------+--------------------------------+-------------+----------------------------+
| pipeline_id                          | extractor_task_id                    | partition_id   | sink_index   | name                           |       value | updated_at                 |
|--------------------------------------+--------------------------------------+----------------+--------------+--------------------------------+-------------+----------------------------|
| 0f51f1c0-f251-4407-8393-0f9bcbf18f28 | 034cf49d-364b-42b5-ad0a-37b7c237f16e |                |              | count.record.bytes.transformed |  7774214710 | 2024-02-07 22:35:27.546000 |
| 0f51f1c0-f251-4407-8393-0f9bcbf18f28 | 034cf49d-364b-42b5-ad0a-37b7c237f16e |                |              | count.record.bytes.transformed | 16529202619 | 2024-02-07 22:35:37.546000 |
| 0f51f1c0-f251-4407-8393-0f9bcbf18f28 | 034cf49d-364b-42b5-ad0a-37b7c237f16e |                |              | count.record.bytes.transformed | 25077088214 | 2024-02-07 22:35:47.546000 |
| 0f51f1c0-f251-4407-8393-0f9bcbf18f28 | 034cf49d-364b-42b5-ad0a-37b7c237f16e |                |              | count.record.bytes.transformed | 33425410618 | 2024-02-07 22:35:57.546000 |
| 0f51f1c0-f251-4407-8393-0f9bcbf18f28 | 034cf49d-364b-42b5-ad0a-37b7c237f16e |                |              | count.record.bytes.transformed | 41818859671 | 2024-02-07 22:36:07.546000 |
+--------------------------------------+--------------------------------------+----------------+--------------+--------------------------------+-------------+----------------------------+
Fetched 5 rows
+--------------------------------------+--------------------------------------+----------------+--------------+--------------------------------+-------------+----------------------------+
| pipeline_id                          | extractor_task_id                    | partition_id   | sink_index   | name                           |       value | updated_at                 |
|--------------------------------------+--------------------------------------+----------------+--------------+--------------------------------+-------------+----------------------------|
| 0f51f1c0-f251-4407-8393-0f9bcbf18f28 | 034cf49d-364b-42b5-ad0a-37b7c237f16e |                |              | count.record.bytes.transformed |  7774214710 | 2024-02-07 22:35:27.546000 |
| 0f51f1c0-f251-4407-8393-0f9bcbf18f28 | 034cf49d-364b-42b5-ad0a-37b7c237f16e |                |              | count.record.bytes.transformed | 16529202619 | 2024-02-07 22:35:37.546000 |
| 0f51f1c0-f251-4407-8393-0f9bcbf18f28 | 034cf49d-364b-42b5-ad0a-37b7c237f16e |                |              | count.record.bytes.transformed | 25077088214 | 2024-02-07 22:35:47.546000 |
| 0f51f1c0-f251-4407-8393-0f9bcbf18f28 | 034cf49d-364b-42b5-ad0a-37b7c237f16e |                |              | count.record.bytes.transformed | 33425410618 | 2024-02-07 22:35:57.546000 |
| 0f51f1c0-f251-4407-8393-0f9bcbf18f28 | 034cf49d-364b-42b5-ad0a-37b7c237f16e |                |              | count.record.bytes.transformed | 41818859671 | 2024-02-07 22:36:07.546000 |
+--------------------------------------+--------------------------------------+----------------+--------------+--------------------------------+-------------+----------------------------+
Fetched 5 rows
﻿
Metric Scope
Each metric has a scope that defines its uniqueness. Be careful not to aggregate metrics across different scopes.
The scope columns are pipeline_id, extractor_task_id, partition_id, and sink_index. If the value of any of these columns is NULL, the scope applies to all values in that dimension.
For example, because the partition_id and sink_index columns are NULL for the metric count.record.bytes.transformed, its scope applies to the pipeline indicated by the pipeline_id and extractor_task_id across all sinks and partitions.
An extractor_task_id is the internal identifier used when the Ocient System executes a pipeline process on a Loader Node. A pipeline might have many of these processes across time and loaders.
Example
Retrieve the most recent snapshot for each metric and aggregate the values to produce a single value for a pipeline.
SQL
SELECT pipeline_id, name, SUM(most_recent_value) AS total_value 
FROM (
    SELECT DISTINCT * FROM (
        SELECT pipeline_id, extractor_task_id, partition_id, name,
        first_value (value) OVER 
            (PARTITION BY pipeline_id, extractor_task_id, partition_id, name 
             ORDER BY updated_at DESC) AS most_recent_value
        FROM sys.pipeline_metrics
        WHERE name LIKE 'count%'
    )
) 
GROUP BY pipeline_id, name;
SELECT pipeline_id, name, SUM(most_recent_value) AS total_value 
FROM (
    SELECT DISTINCT * FROM (
        SELECT pipeline_id, extractor_task_id, partition_id, name,
        first_value (value) OVER 
            (PARTITION BY pipeline_id, extractor_task_id, partition_id, name 
             ORDER BY updated_at DESC) AS most_recent_value
        FROM sys.pipeline_metrics
        WHERE name LIKE 'count%'
    )
) 
GROUP BY pipeline_id, name;
﻿
Output
SQL
+--------------------------------------+--------------------------------+---------------+
| pipeline_id                          | name                           |   total_value |
|--------------------------------------+--------------------------------+---------------|
| 0c7b32f1-37ca-43f7-96b6-69c266c978a5 | count.file.total               |            64 |
| 0c7b32f1-37ca-43f7-96b6-69c266c978a5 | count.record.bytes.transformed |      33644961 |
| 0c7b32f1-37ca-43f7-96b6-69c266c978a5 | count.file.processed           |            64 |
| 0c7b32f1-37ca-43f7-96b6-69c266c978a5 | count.record.sent              |        100000 |
| 0c7b32f1-37ca-43f7-96b6-69c266c978a5 | count.record.durable           |        100000 |
+--------------------------------------+--------------------------------+---------------+
Fetched 5 rows
+--------------------------------------+--------------------------------+---------------+
| pipeline_id                          | name                           |   total_value |
|--------------------------------------+--------------------------------+---------------|
| 0c7b32f1-37ca-43f7-96b6-69c266c978a5 | count.file.total               |            64 |
| 0c7b32f1-37ca-43f7-96b6-69c266c978a5 | count.record.bytes.transformed |      33644961 |
| 0c7b32f1-37ca-43f7-96b6-69c266c978a5 | count.file.processed           |            64 |
| 0c7b32f1-37ca-43f7-96b6-69c266c978a5 | count.record.sent              |        100000 |
| 0c7b32f1-37ca-43f7-96b6-69c266c978a5 | count.record.durable           |        100000 |
+--------------------------------------+--------------------------------+---------------+
Fetched 5 rows
﻿
This output contains a single row for each metric for each pipeline. If you run a pipeline with 64 files and 100,000 records, the output might look like this. This table explains each column in the output.
Related Links
﻿System Catalog 
﻿Data Integrity and Storage﻿ 
﻿Query Analysis﻿ 
﻿Database, Tables, Views, and Indexes﻿ 
﻿Cluster and Node Management﻿ 
﻿Users, Groups, and Service Classes﻿ 
﻿
Information Schema
Connection Driver Reference
Discover Insights From System Catalog Tables

Segment System Catalog Tables

Segment Size

﻿Page and Segment Information﻿

Storage Spaces on a System

Query Management System Catalog Tables

Running Queries

﻿Recent Queries ﻿

Database and Table System Catalog Tables

﻿Database Sizes﻿

Table Sizes

﻿Table Statistics﻿

﻿Compression﻿

﻿Compression for the Entire Table ﻿

﻿Index Size﻿

Column Sizes

Column Cardinality

Cluster and Node System Catalog Tables

Nodes Present on a System

Nodes Roles on a System

Node Status

Foundation Nodes on a Specific Cluster

Drive Status

Roles, Privileges, and Service Class System Catalog Tables

Group Assignment for Users

User Roles

User Privileges

﻿Privileges for the Specific Table﻿

﻿Groups Assigned to Service Classes﻿

Service Class Settings

﻿Service Classes for the Specific User﻿

Data Pipelines

Pipeline Events

Pipeline Metrics

Related Links

Page and Segment Information

Recent Queries

Database Sizes

Table Statistics

Compression

Compression for the Entire Table

Index Size

Privileges for the Specific Table

Groups Assigned to Service Classes

Service Classes for the Specific User
