16.3.1 Metadata Table Dictionary

-- List all tables, including their types
SELECT name, type, colcount
  FROM m$sys_tables
 ORDER BY name;

-- List Tag tables only (type = 6)
SELECT name FROM m$sys_tables WHERE type = 6;

-- List columns of a specific table
SELECT c.name AS col_name, c.type AS col_type, c.length
  FROM m$sys_columns c
  JOIN m$sys_tables  t
    ON c.database_id = t.database_id
   AND c.tablespace_id = t.tablespace_id
   AND c.table_id = t.id
 WHERE t.name = 'SENSOR_TAG'
 ORDER BY c.id;

-- List indexes of a specific table
SELECT i.name AS idx_name, i.type AS idx_type, i.colcount
  FROM m$sys_indexes i
  JOIN m$sys_tables  t
    ON i.database_id = t.database_id
   AND i.tablespace_id = t.tablespace_id
   AND i.table_id = t.id
 WHERE t.name = 'SENSOR_TAG';

-- Check the columns that make up an index
SELECT ic.name AS col_name, ic.index_type
  FROM m$sys_index_columns ic
  JOIN m$sys_indexes i ON ic.index_id = i.id
  JOIN m$sys_tables  t ON i.table_id = t.id
 WHERE t.name = 'SENSOR_TAG';

-- Check tablespace disk paths
SELECT ts.name AS tbs_name, d.path, d.io_thread_count
  FROM m$sys_tablespace_disks d
  JOIN m$sys_tablespaces ts ON d.tablespace_id = ts.id;

-- List users
SELECT user_id, name, pwd_policy_level, valid_before
  FROM m$sys_users;

-- List retention policies
SELECT * FROM m$retention;