Amazon Redshift will no longer support the creation of new Python UDFs starting Patch 198. Existing Python UDFs will continue to function until June 30, 2026. For more information, see the [ blog post ](https://aws.amazon.com/blogs/big-data/amazon-redshift-python-user-defined-functions-will-reach-end-of-support-after-june-30-2026/).
# SVV metadata views
SVV views are system views in Amazon Redshift that contain information about database objects. Note that although they store information such as user permissions or table names, they're not meant for joining with user-created relations.
**Note**
Amazon Redshift reports a WARNING, not an ERROR, if a database response fails for any reason. Amazon Redshift doesn't send ERROR messages when you're querying objects in a datashare.
**Topics**
+ [SVV\$1ACTIVE\$1CURSORS](r_SVV_ACTIVE_CURSORS.md)
+ [SVV\$1ALL\$1COLUMNS](r_SVV_ALL_COLUMNS.md)
+ [SVV\$1ALL\$1SCHEMAS](r_SVV_ALL_SCHEMAS.md)
+ [SVV\$1ALL\$1TABLES](r_SVV_ALL_TABLES.md)
+ [SVV\$1ALTER\$1TABLE\$1RECOMMENDATIONS](r_SVV_ALTER_TABLE_RECOMMENDATIONS.md)
+ [SVV\$1ATTACHED\$1MASKING\$1POLICY](r_SVV_ATTACHED_MASKING_POLICY.md)
+ [SVV\$1COLUMNS](r_SVV_COLUMNS.md)
+ [SVV\$1COLUMN\$1PRIVILEGES](r_SVV_COLUMN_PRIVILEGES.md)
+ [SVV\$1COPY\$1JOB\$1INTEGRATIONS](SVV_COPY_JOB_INTEGRATIONS.md)
+ [SVV\$1DATABASE\$1PRIVILEGES](r_SVV_DATABASE_PRIVILEGES.md)
+ [SVV\$1DATASHARE\$1PRIVILEGES](r_SVV_DATASHARE_PRIVILEGES.md)
+ [SVV\$1DATASHARES](r_SVV_DATASHARES.md)
+ [SVV\$1DATASHARE\$1CONSUMERS](r_SVV_DATASHARE_CONSUMERS.md)
+ [SVV\$1DATASHARE\$1OBJECTS](r_SVV_DATASHARE_OBJECTS.md)
+ [SVV\$1DEFAULT\$1PRIVILEGES](r_SVV_DEFAULT_PRIVILEGES.md)
+ [SVV\$1DISKUSAGE](r_SVV_DISKUSAGE.md)
+ [SVV\$1EXTERNAL\$1COLUMNS](r_SVV_EXTERNAL_COLUMNS.md)
+ [SVV\$1EXTERNAL\$1DATABASES](r_SVV_EXTERNAL_DATABASES.md)
+ [SVV\$1EXTERNAL\$1PARTITIONS](r_SVV_EXTERNAL_PARTITIONS.md)
+ [SVV\$1EXTERNAL\$1SCHEMAS](r_SVV_EXTERNAL_SCHEMAS.md)
+ [SVV\$1EXTERNAL\$1TABLES](r_SVV_EXTERNAL_TABLES.md)
+ [SVV\$1FUNCTION\$1PRIVILEGES](r_SVV_FUNCTION_PRIVILEGES.md)
+ [SVV\$1GEOGRAPHY\$1COLUMNS](r_SVV_GEOGRAPHY_COLUMNS.md)
+ [SVV\$1GEOMETRY\$1COLUMNS](r_SVV_GEOMETRY_COLUMNS.md)
+ [SVV\$1IAM\$1PRIVILEGES](r_SVV_IAM_PRIVILEGES.md)
+ [SVV\$1IDENTITY\$1PROVIDERS](r_SVV_IDENTITY_PROVIDERS.md)
+ [SVV\$1INTEGRATION](r_SVV_INTEGRATION.md)
+ [SVV\$1INTEGRATION\$1TABLE\$1MAPPING](r_SVV_INTEGRATION_TABLE_MAPPING.md)
+ [SVV\$1INTEGRATION\$1TABLE\$1STATE](r_SVV_INTEGRATION_TABLE_STATE.md)
+ [SVV\$1INTERLEAVED\$1COLUMNS](r_SVV_INTERLEAVED_COLUMNS.md)
+ [SVV\$1LANGUAGE\$1PRIVILEGES](r_SVV_LANUGAGE_PRIVILEGES.md)
+ [SVV\$1MASKING\$1POLICY](r_SVV_MASKING_POLICY.md)
+ [SVV\$1ML\$1MODEL\$1INFO](r_SVV_ML_MODEL_INFO.md)
+ [SVV\$1ML\$1MODEL\$1PRIVILEGES](r_SVV_ML_MODEL_PRIVILEGES.md)
+ [SVV\$1MV\$1DEPENDENCY](r_SVV_MV_DEPENDENCY.md)
+ [SVV\$1MV\$1INFO](r_SVV_MV_INFO.md)
+ [SVV\$1QUERY\$1INFLIGHT](r_SVV_QUERY_INFLIGHT.md)
+ [SVV\$1QUERY\$1STATE](r_SVV_QUERY_STATE.md)
+ [SVV\$1REDSHIFT\$1COLUMNS](r_SVV_REDSHIFT_COLUMNS.md)
+ [SVV\$1REDSHIFT\$1DATABASES](r_SVV_REDSHIFT_DATABASES.md)
+ [SVV\$1REDSHIFT\$1FUNCTIONS](r_SVV_REDSHIFT_FUNCTIONS.md)
+ [SVV\$1REDSHIFT\$1SCHEMA\$1QUOTA](r_SVV_REDSHIFT_SCHEMA_QUOTA.md)
+ [SVV\$1REDSHIFT\$1SCHEMAS](r_SVV_REDSHIFT_SCHEMAS.md)
+ [SVV\$1REDSHIFT\$1TABLES](r_SVV_REDSHIFT_TABLES.md)
+ [SVV\$1RELATION\$1PRIVILEGES](r_SVV_RELATION_PRIVILEGES.md)
+ [SVV\$1RLS\$1APPLIED\$1POLICY](r_SVV_RLS_APPLIED_POLICY.md)
+ [SVV\$1RLS\$1ATTACHED\$1POLICY](r_SVV_RLS_ATTACHED_POLICY.md)
+ [SVV\$1RLS\$1POLICY](r_SVV_RLS_POLICY.md)
+ [SVV\$1RLS\$1RELATION](r_SVV_RLS_RELATION.md)
+ [SVV\$1ROLE\$1GRANTS](r_SVV_ROLE_GRANTS.md)
+ [SVV\$1ROLES](r_SVV_ROLES.md)
+ [SVV\$1SCHEMA\$1PRIVILEGES](r_SVV_SCHEMA_PRIVILEGES.md)
+ [SVV\$1SCHEMA\$1QUOTA\$1STATE](r_SVV_SCHEMA_QUOTA_STATE.md)
+ [SVV\$1SYSTEM\$1PRIVILEGES](r_SVV_SYSTEM_PRIVILEGES.md)
+ [SVV\$1TABLE\$1INFO](r_SVV_TABLE_INFO.md)
+ [SVV\$1TABLES](r_SVV_TABLES.md)
+ [SVV\$1TRANSACTIONS](r_SVV_TRANSACTIONS.md)
+ [SVV\$1USER\$1GRANTS](r_SVV_USER_GRANTS.md)
+ [SVV\$1USER\$1INFO](r_SVV_USER_INFO.md)
+ [SVV\$1VACUUM\$1PROGRESS](r_SVV_VACUUM_PROGRESS.md)
+ [SVV\$1VACUUM\$1SUMMARY](r_SVV_VACUUM_SUMMARY.md)
# SVV\$1ACTIVE\$1CURSORS
SVV\$1ACTIVE\$1CURSORS displays details for currently open cursors. For more information, see [DECLARE](declare.md).
SVV\$1ACTIVE\$1CURSORS is visible to all users. Superusers can see all rows; regular users can see only their own data. For more information, see [Visibility of data in system tables and views](cm_chap_system-tables.md#c_visibility-of-data). A user can only view cursors opened by that user. A superuser can view all cursors.
## Table columns
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/r_SVV_ACTIVE_CURSORS.html)
# SVV\$1ALL\$1COLUMNS
Use SVV\$1ALL\$1COLUMNS to view a union of columns from Amazon Redshift tables as shown in SVV\$1REDSHIFT\$1COLUMNS and the consolidated list of all external columns from all external tables. For information about Amazon Redshift columns, see [SVV\$1REDSHIFT\$1COLUMNS](r_SVV_REDSHIFT_COLUMNS.md).
SVV\$1ALL\$1COLUMNS is visible to all users. Superusers can see all rows; regular users can see only their own data. For more information, see [Visibility of data in system tables and views](cm_chap_system-tables.md#c_visibility-of-data).
## Table columns
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/r_SVV_ALL_COLUMNS.html)
## Sample queries
The following example returns the output of SVV\$1ALL\$1COLUMNS.
```
SELECT *
FROM svv_all_columns
WHERE database_name = 'tickit_db'
AND TABLE_NAME = 'tickit_sales_redshift'
ORDER BY COLUMN_NAME,
SCHEMA_NAME
LIMIT 5;
database_name | schema_name | table_name | column_name | ordinal_position | column_default | is_nullable | data_type | character_maximum_length | numeric_precision | numeric_scale | remarks
--------------+-------------+-----------------------+-------------+------------------+----------------+-------------+-----------+--------------------------+-------------------+---------------+---------
tickit_db | public | tickit_sales_redshift | buyerid | 4 | | NO | integer | | 32 | 0 |
tickit_db | public | tickit_sales_redshift | commission | 9 | | YES | numeric | | 8 | 2 |
tickit_db | public | tickit_sales_redshift | dateid | 7 | | NO | smallint | | 16 | 0 |
tickit_db | public | tickit_sales_redshift | eventid | 5 | | NO | integer | | 32 | 0 |
tickit_db | public | tickit_sales_redshift | listid | 2 | | NO | integer | | 32 | 0 |
```
# SVV\$1ALL\$1SCHEMAS
Use SVV\$1ALL\$1SCHEMAS to view a union of Amazon Redshift schemas as shown in SVV\$1REDSHIFT\$1SCHEMAS and the consolidated list of all external schemas from all databases. For more information about Amazon Redshift schemas, see [SVV\$1REDSHIFT\$1SCHEMAS](r_SVV_REDSHIFT_SCHEMAS.md).
SVV\$1ALL\$1SCHEMAS is visible to all users. Superusers can see all rows; regular users can see only their own data. For more information, see [Visibility of data in system tables and views](cm_chap_system-tables.md#c_visibility-of-data).
## Table columns
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/r_SVV_ALL_SCHEMAS.html)
## Sample query
The following example returns the output of SVV\$1ALL\$1SCHEMAS.
```
SELECT *
FROM svv_all_schemas
WHERE database_name = 'tickit_db'
ORDER BY database_name,
SCHEMA_NAME;
database_name | schema_name | schema_owner | schema_type | schema_acl | source_database | schema_option
---------------+--------------------+--------------+-------------+------------+-----------------+---------------
tickit_db | public | 1 | shared | | |
```
# SVV\$1ALL\$1TABLES
Use SVV\$1ALL\$1TABLES to view a union of Amazon Redshift tables as shown in SVV\$1REDSHIFT\$1TABLES and the consolidated list of all external tables from all external schemas. For information about Amazon Redshift tables, see [SVV\$1REDSHIFT\$1TABLES](r_SVV_REDSHIFT_TABLES.md).
SVV\$1ALL\$1TABLES is visible to all users. Superusers can see all rows; regular users can see only their own data. For more information, see [Visibility of data in system tables and views](cm_chap_system-tables.md#c_visibility-of-data).
## Table columns
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/r_SVV_ALL_TABLES.html)
## Sample queries
The following example returns the output of SVV\$1ALL\$1TABLES.
```
SELECT *
FROM svv_all_tables
WHERE database_name = 'tickit_db'
ORDER BY TABLE_NAME,
SCHEMA_NAME
LIMIT 5;
database_name | schema_name | table_name | table_type | table_acl | remarks
---------------+-------------+--------------------------+------------+-----------+---------
tickit_db | public | tickit_category_redshift | TABLE | |
tickit_db | public | tickit_date_redshift | TABLE | |
tickit_db | public | tickit_event_redshift | TABLE | |
tickit_db | public | tickit_listing_redshift | TABLE | |
tickit_db | public | tickit_sales_redshift | TABLE | |
```
If the table\$1acl value is null, no access privileges have been explicitly granted to the corresponding table.
# SVV\$1ALTER\$1TABLE\$1RECOMMENDATIONS
Records the current Amazon Redshift Advisor recommendations for tables. This view shows recommendations for all tables, whether they are defined for automatic optimization or not. To view if a table is defined for automatic optimization, see [SVV\$1TABLE\$1INFO](r_SVV_TABLE_INFO.md). Entries appear only for tables visible in the current session's database. After a recommendation has been applied (either by Amazon Redshift or by you), it no longer appears in the view.
SVV\$1ALTER\$1TABLE\$1RECOMMENDATIONS is visible only to superusers. For more information, see [Visibility of data in system tables and views](cm_chap_system-tables.md#c_visibility-of-data).
## Table columns
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/r_SVV_ALTER_TABLE_RECOMMENDATIONS.html)
## Sample queries
In the following example, the rows in the result show recommendations for distribution key and sort key. The rows also show whether the recommendations are eligible for Amazon Redshift to automatically apply them.
```
select type, database, table_id, group_id, ddl, auto_eligible
from svv_alter_table_recommendations;
```
```
type | database | table_id | group_id | ddl | auto_eligible
diststyle | db0 | 117884 | 2 | ALTER TABLE "sch"."dp21235_tbl_1" ALTER DISTSTYLE KEY DISTKEY "c0" | f
diststyle | db0 | 117892 | 2 | ALTER TABLE "sch"."dp21235_tbl_1" ALTER DISTSTYLE KEY DISTKEY "c0" | f
diststyle | db0 | 117885 | 1 | ALTER TABLE "sch"."catalog_returns" ALTER DISTSTYLE KEY DISTKEY "cr_sold_date_sk", ALTER COMPOUND SORTKEY ("cr_sold_date_sk","cr_returned_time_sk") | t
sortkey | db0 | 117890 | -1 | ALTER TABLE "sch"."customer_addresses" ALTER COMPOUND SORTKEY ("ca_address_sk") | t
```
# SVV\$1ATTACHED\$1MASKING\$1POLICY
Use SVV\$1ATTACHED\$1MASKING\$1POLICY to view all the relations and roles/users with policies attached on the currently connected database.
Only superusers and users with the [https://docs.aws.amazon.com/redshift/latest/dg/r_roles-default.html](https://docs.aws.amazon.com/redshift/latest/dg/r_roles-default.html) role can view SVV\$1ATTACHED\$1MASKING\$1POLICY. Regular users will see 0 rows.
## Table columns
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/r_SVV_ATTACHED_MASKING_POLICY.html)
## Internal functions
SVV\$1ATTACHED\$1MASKING\$1POLICY supports the following internal functions:
### mask\$1get\$1policy\$1for\$1role\$1on\$1column
Get the highest priority policy that applies to a given column/role pair.
#### Syntax
```
mask_get_policy_for_role_on_column
(relschema,
relname,
colname,
rolename);
```
#### Parameters
*relschema*
The name of the schema the policy is in.
*relname*
The name of the table the policy is in.
*colname*
The name of the column the policy is attached to.
*rolename*
The name of the role the policy is attached to.
### mask\$1get\$1policy\$1for\$1user\$1on\$1column
Get the highest priority policy that applies to a given column/user pair.
#### Syntax
```
mask_get_policy_for_user_on_column
(relschema,
relname,
colname,
username);
```
#### Parameters
*relschema*
The name of the schema the policy is in.
*relname*
The name of the table the policy is in.
*colname*
The name of the column the policy is attached to.
*rolename*
The name of the user the policy is attached to.
# SVV\$1COLUMNS
Use SVV\$1COLUMNS to view catalog information about the columns of local and external tables and views, including [late-binding views](r_CREATE_VIEW.md#r_CREATE_VIEW_late-binding-views).
SVV\$1COLUMNS is visible to all users by default. To control access to your database's metadata, enable metadata security for your provisioned cluster or serverless workgroup. Metadata security lets you separate view permissions for object metadata by users and roles. For more information, see [Metadata security](t_metadata_security.md).
The SVV\$1COLUMNS view joins table metadata from the [System catalog tables](c_intro_catalog_views.md) (tables with a PG prefix) and the [SVV\$1EXTERNAL\$1COLUMNS](r_SVV_EXTERNAL_COLUMNS.md) system view. The system catalog tables describe Amazon Redshift database tables. SVV\$1EXTERNAL\$1COLUMNS describes external tables that are used with Amazon Redshift Spectrum.
All users can see all rows from the system catalog tables. Regular users can see column definitions from the SVV\$1EXTERNAL\$1COLUMNS view only for external tables to which they have been granted access. Although regular users can see table metadata in the system catalog tables, they can only select data from user-defined tables if they own the table or have been granted access.
## Table columns
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/r_SVV_COLUMNS.html)
# SVV\$1COLUMN\$1PRIVILEGES
Use SVV\$1COLUMN\$1PRIVILEGES to view the column permissions that are explicitly granted to users, roles, and groups in the current database.
SVV\$1COLUMN\$1PRIVILEGES is visible to the following users:
+ Superusers
+ Users with the ACCESS SYSTEM TABLE permission
Other users can only see identities they have access to or own.
## Table columns
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/r_SVV_COLUMN_PRIVILEGES.html)
## Sample query
The following example displays the result of the SVV\$1COLUMN\$1PRIVILEGES.
```
SELECT namespace_name,relation_name,COLUMN_NAME,privilege_type,identity_name,identity_type
FROM svv_column_privileges WHERE relation_name = 'lineitem';
namespace_name | relation_name | column_name | privilege_type | identity_name | identity_type
----------------+---------------+-------------+----------------+---------------+----------------
public | lineitem | l_orderkey | SELECT | reguser | user
public | lineitem | l_orderkey | SELECT | role1 | role
public | lineitem | l_partkey | SELECT | reguser | user
public | lineitem | l_partkey | SELECT | role1 | role
```
# SVV\$1COPY\$1JOB\$1INTEGRATIONS
Use SVV\$1COPY\$1JOB\$1INTEGRATIONS to view details of S3 event integrations.
This view contains the S3 event integrations that have been created.
SVV\$1COPY\$1JOB\$1INTEGRATIONS is visible to all users. Superusers can see all rows; regular users can see only their own data. For more information, see [Visibility of data in system tables and views](cm_chap_system-tables.md#c_visibility-of-data).
## Table columns
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/SVV_COPY_JOB_INTEGRATIONS.html)
The following example returns S3 integrations for the current database.
```
SELECT * FROM SVV_COPY_JOB_INTEGRATIONS WHERE db_name = pg_catalog.current_database();
```
# SVV\$1DATABASE\$1PRIVILEGES
Use SVV\$1DATABASE\$1PRIVILEGES to view the database permissions that are explicitly granted to users, roles, and groups in your Amazon Redshift cluster.
SVV\$1DATABASE\$1PRIVILEGES is visible to the following users:
+ Superusers
+ Users with the ACCESS SYSTEM TABLE permission
Other users can only see identities they have access to or own.
## Table columns
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/r_SVV_DATABASE_PRIVILEGES.html)
## Sample query
The following example displays the result of the SVV\$1DATABASE\$1PRIVILEGES.
```
SELECT database_name,privilege_type,identity_name,identity_type,admin_option FROM svv_database_privileges
WHERE database_name = 'test_db';
database_name | privilege_type | identity_name | identity_type | admin_option
---------------+----------------+---------------+---------------+--------------
test_db | CREATE | reguser | user | False
test_db | CREATE | role1 | role | False
test_db | TEMP | public | public | False
test_db | TEMP | role1 | role | False
```
# SVV\$1DATASHARE\$1PRIVILEGES
Use SVV\$1DATASHARE\$1PRIVILEGES to view the datashare permissions that are explicitly granted to users, roles, and groups in your Amazon Redshift cluster.
SVV\$1DATASHARE\$1PRIVILEGES is visible to the following users:
+ Superusers
+ Users with the ACCESS SYSTEM TABLE permission
Other users can only see identities they have access to or own.
## Table columns
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/r_SVV_DATASHARE_PRIVILEGES.html)
## Sample query
The following example displays the result of the SVV\$1DATASHARE\$1PRIVILEGES.
```
SELECT datashare_name,privilege_type,identity_name,identity_type,admin_option FROM svv_datashare_privileges
WHERE datashare_name = 'demo_share';
datashare_name | privilege_type | identity_name | identity_type | admin_option
----------------+----------------+----------------+---------------+--------------
demo_share | ALTER | superuser | user | False
demo_share | ALTER | reguser | user | False
```
# SVV\$1DATASHARES
Use SVV\$1DATASHARES to view a list of datashares created on the cluster, and datashares shared with the cluster.
SVV\$1DATASHARES is visible to the following users:
+ Superusers
+ Datashare owners
+ Users with ALTER or USAGE permissions on a datashare
Other users can't see any rows. For information on the ALTER and USAGE permissions, see [GRANT](r_GRANT.md).
## Table columns
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/r_SVV_DATASHARES.html)
## Usage notes
**Retrieving additional metadata** – Using the integer returned in the `share_owner` column, you can join with `usesysid` in [SVL\$1USER\$1INFO](r_SVL_USER_INFO.md) to get data about the datashare owner. This includes the name and additional properties.
## Sample query
The following example returns the output for SVV\$1DATASHARES.
```
SELECT share_owner, source_database, share_type, is_publicaccessible
FROM svv_datashares
WHERE share_name LIKE 'tickit_datashare%'
AND source_database = 'dev';
share_owner | source_database | share_type | is_publicaccessible
--------------+-----------------+-------------+----------------------
100 | dev | OUTBOUND | True
(1 rows)
```
The following example returns the output for SVV\$1DATASHARES for outbound datashares.
```
SELECT share_name, share_owner, btrim(source_database), btrim(consumer_database), share_type, is_publicaccessible, share_acl, btrim(producer_account), btrim(producer_namespace), btrim(managed_by) FROM svv_datashares WHERE share_type = 'OUTBOUND';
share_name | share_owner | source_database | consumer_database | share_type | is_publicaccessible | share_acl | producer_account| producer_namespace | managed_by
----------------+-------------+-----------------+-------------------+------------+---------------------+-----------+-----------------+--------------------------------------+------------
salesshare | 1 | dev | | OUTBOUND | True | | 123456789012 | 13b8833d-17c6-4f16-8fe4-1a018f5ed00d |
marketingshare | 1 | dev | | OUTBOUND | True | | 123456789012 | 13b8833d-17c6-4f16-8fe4-1a018f5ed00d |
```
The following example returns the output for SVV\$1DATASHARES for inbound datashares.
```
SELECT share_name, share_owner, btrim(source_database), btrim(consumer_database), share_type, is_publicaccessible, share_acl, btrim(producer_account), btrim(producer_namespace), btrim(managed_by) FROM svv_datashares WHERE share_type = 'INBOUND';
share_name | share_owner | source_database | consumer_database | share_type | is_publicaccessible | share_acl | producer_account | producer_namespace | managed_by
----------------+-------------+-----------------+-------------------+------------+---------------------+-----------+------------------+--------------------------------------+------------
salesshare | | | | INBOUND | False | | 123456789012 | 13b8833d-17c6-4f16-8fe4-1a018f5ed00d |
marketingshare | | | | INBOUND | False | | 123456789012 | 13b8833d-17c6-4f16-8fe4-1a018f5ed00d | ADX
```
# SVV\$1DATASHARE\$1CONSUMERS
Use SVV\$1DATASHARE\$1CONSUMERS to view a list of consumers for a datashare created on a cluster.
SVV\$1DATASHARE\$1CONSUMERS is visible to the following users:
+ Superusers
+ Datashare owners
+ Users with ALTER or USAGE permissions on a datashare
Other users can't see any rows. For information on the ALTER and USAGE permissions, see [GRANT](r_GRANT.md).
## Table columns
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/r_SVV_DATASHARE_CONSUMERS.html)
## Sample query
The following example returns the output for SVV\$1DATASHARE\$1CONSUMERS.
```
SELECT count(*)
FROM svv_datashare_consumers
WHERE share_name LIKE 'tickit_datashare%';
1
```
# SVV\$1DATASHARE\$1OBJECTS
Use SVV\$1DATASHARE\$1OBJECTS to view a list of objects in all datashares created on the cluster or shared with the cluster.
SVV\$1DATASHARE\$1OBJECTS is visible to all users. Superusers can see all rows; regular users can see only their own data. For more information, see [Visibility of data in system tables and views](cm_chap_system-tables.md#c_visibility-of-data).
For information about viewing a list of datashares, see [SVV\$1DATASHARES](https://docs.aws.amazon.com/redshift/latest/dg/r_SVV_DATASHARES.html).
## Table columns
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/r_SVV_DATASHARE_OBJECTS.html)
## Sample query
The following examples return the output for SVV\$1DATASHARE\$1OBJECTS.
```
SELECT share_type,
btrim(share_name)::varchar(16) AS share_name,
object_type,
object_name
FROM svv_datashare_objects
WHERE share_name LIKE 'tickit_datashare%'
AND object_name LIKE '%tickit%'
ORDER BY object_name
LIMIT 5;
share_type | share_name | object_type | object_name
------------+--------------------+-------------+---------------------------------
OUTBOUND | tickit_datashare | table | public.tickit_category_redshift
OUTBOUND | tickit_datashare | table | public.tickit_date_redshift
OUTBOUND | tickit_datashare | table | public.tickit_event_redshift
OUTBOUND | tickit_datashare | table | public.tickit_listing_redshift
OUTBOUND | tickit_datashare | table | public.tickit_sales_redshift
```
```
SELECT * FROM SVV_DATASHARE_OBJECTS WHERE share_name like 'sales%';
share_type | share_name | object_type | object_name | producer_account | producer_namespace | include_new
-----------+------------+-------------+--------------+------------------+--------------------------------------+-------------
OUTBOUND | salesshare | schema | public | 123456789012 | 13b8833d-17c6-4f16-8fe4-1a018f5ed00d | t
OUTBOUND | salesshare | table | public.sales | 123456789012 | 13b8833d-17c6-4f16-8fe4-1a018f5ed00d |
```
# SVV\$1DEFAULT\$1PRIVILEGES
Use SVV\$1DEFAULT\$1PRIVILEGES to view the default privileges that a user has access to in an Amazon Redshift cluster.
SVV\$1DEFAULT\$1PRIVILEGES is visible to the following users:
+ Superusers
+ Users with the ACCESS SYSTEM TABLE permission
Other users can only see default permissions granted to them.
## Table columns
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/r_SVV_DEFAULT_PRIVILEGES.html)
## Sample query
The following example returns the output for SVV\$1DEFAULT\$1PRIVILEGES.
```
SELECT * from svv_default_privileges;
schema_name | object_type | owner_id | owner_name | owner_type | privilege_type | grantee_id | grantee_name | grantee_type | admin_option
-------------+-------------------+--------- +------------+------------+----------------+------------+--------------+--------------+-------------+
public | RELATION | 106 | u1 | user | UPDATE | 107 | u2 | user | f |
public | RELATION | 106 | u1 | user | SELECT | 107 | u2 | user | f |
```
# SVV\$1DISKUSAGE
Amazon Redshift creates the SVV\$1DISKUSAGE system view by joining the STV\$1TBL\$1PERM and STV\$1BLOCKLIST tables. The SVV\$1DISKUSAGE view contains information about data allocation for the tables in a database.
Use aggregate queries with SVV\$1DISKUSAGE, as the following examples show, to determine the number of disk blocks allocated per database, table, slice, or column. Each data block uses 1 MB. You can also use [STV\$1PARTITIONS](r_STV_PARTITIONS.md) to view summary information about disk utilization.
SVV\$1DISKUSAGE is visible only to superusers. For more information, see [Visibility of data in system tables and views](cm_chap_system-tables.md#c_visibility-of-data).
**Note**
This view is only available when querying provisioned clusters.
## Table columns
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/r_SVV_DISKUSAGE.html)
## Sample queries
SVV\$1DISKUSAGE contains one row per allocated disk block, so a query that selects all the rows potentially returns a very large number of rows. We recommend using only aggregate queries with SVV\$1DISKUSAGE.
Return the highest number of blocks ever allocated to column 6 in the USERS table (the EMAIL column):
```
select db_id, trim(name) as tablename, max(blocknum)
from svv_diskusage
where name='users' and col=6
group by db_id, name;
db_id | tablename | max
--------+-----------+-----
175857 | users | 2
(1 row)
```
The following query returns similar results for all of the columns in a large 10-column table called SALESNEW. (The last three rows, for columns 10 through 12, are for the hidden metadata columns.)
```
select db_id, trim(name) as tablename, col, tbl, max(blocknum)
from svv_diskusage
where name='salesnew'
group by db_id, name, col, tbl
order by db_id, name, col, tbl;
db_id | tablename | col | tbl | max
--------+------------+-----+--------+-----
175857 | salesnew | 0 | 187605 | 154
175857 | salesnew | 1 | 187605 | 154
175857 | salesnew | 2 | 187605 | 154
175857 | salesnew | 3 | 187605 | 154
175857 | salesnew | 4 | 187605 | 154
175857 | salesnew | 5 | 187605 | 79
175857 | salesnew | 6 | 187605 | 79
175857 | salesnew | 7 | 187605 | 302
175857 | salesnew | 8 | 187605 | 302
175857 | salesnew | 9 | 187605 | 302
175857 | salesnew | 10 | 187605 | 3
175857 | salesnew | 11 | 187605 | 2
175857 | salesnew | 12 | 187605 | 296
(13 rows)
```
# SVV\$1EXTERNAL\$1COLUMNS
Use SVV\$1EXTERNAL\$1COLUMNS to view details for columns in external tables. Use SVV\$1EXTERNAL\$1COLUMNS also for cross-database queries to view details on all columns from the table on unconnected databases that users have access to.
SVV\$1EXTERNAL\$1COLUMNS is visible to all users. Superusers can see all rows; regular users can see only their own data. For more information, see [Visibility of data in system tables and views](cm_chap_system-tables.md#c_visibility-of-data).
## Table columns
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/r_SVV_EXTERNAL_COLUMNS.html)
# SVV\$1EXTERNAL\$1DATABASES
Use SVV\$1EXTERNAL\$1DATABASES to view details for external databases.
SVV\$1EXTERNAL\$1DATABASES is visible to all users. Superusers can see all rows; regular users can see only their own data. For more information, see [Visibility of data in system tables and views](cm_chap_system-tables.md#c_visibility-of-data).
## Table columns
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/r_SVV_EXTERNAL_DATABASES.html)
# SVV\$1EXTERNAL\$1PARTITIONS
Use SVV\$1EXTERNAL\$1PARTITIONS to view details for partitions in external tables.
SVV\$1EXTERNAL\$1PARTITIONS is visible to all users. Superusers can see all rows; regular users can see only their own data. For more information, see [Visibility of data in system tables and views](cm_chap_system-tables.md#c_visibility-of-data)..
## Table columns
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/r_SVV_EXTERNAL_PARTITIONS.html)
# SVV\$1EXTERNAL\$1SCHEMAS
Use SVV\$1EXTERNAL\$1SCHEMAS to view information about external schemas. For more information, see [CREATE EXTERNAL SCHEMA](r_CREATE_EXTERNAL_SCHEMA.md).
SVV\$1EXTERNAL\$1SCHEMAS is visible to all users. Superusers can see all rows; regular users can see only their own data. For more information, see [Visibility of data in system tables and views](cm_chap_system-tables.md#c_visibility-of-data).
## Table columns
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/r_SVV_EXTERNAL_SCHEMAS.html)
## Example
The following example shows details for external schemas.
```
select * from svv_external_schemas;
esoid | eskind | schemaname | esowner | databasename | esoptions
-------+--------+------------+---------+--------------+-------------------------------------------------------------
100133 | 1 | spectrum | 100 | redshift | {"IAM_ROLE":"arn:aws:iam::123456789012:role/mySpectrumRole"}
```
# SVV\$1EXTERNAL\$1TABLES
Use SVV\$1EXTERNAL\$1TABLES to view details for external tables; for more information, see [CREATE EXTERNAL SCHEMA](r_CREATE_EXTERNAL_SCHEMA.md). Use SVV\$1EXTERNAL\$1TABLES also for cross-database queries to view metadata on all tables on unconnected databases that users have access to.
SVV\$1EXTERNAL\$1TABLES is visible to all users. Superusers can see all rows; regular users can see only their own data. For more information, see [Visibility of data in system tables and views](cm_chap_system-tables.md#c_visibility-of-data).
## Table columns
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/r_SVV_EXTERNAL_TABLES.html)
## Example
The following example shows details svv\$1external\$1tables with a predicate on the external schema used by a federated query.
```
select schemaname, tablename from svv_external_tables where schemaname = 'apg_tpch';
schemaname | tablename
------------+-----------
apg_tpch | customer
apg_tpch | lineitem
apg_tpch | nation
apg_tpch | orders
apg_tpch | part
apg_tpch | partsupp
apg_tpch | region
apg_tpch | supplier
(8 rows)
```
# SVV\$1FUNCTION\$1PRIVILEGES
Use SVV\$1FUNCTION\$1PRIVILEGES to view the function permissions that are explicitly granted to users, roles, and groups in the current database.
SVV\$1FUNCTION\$1PRIVILEGES is visible to the following users:
+ Superusers
+ Users with the ACCESS SYSTEM TABLE permission
Other users can only see identities they have access to or own.
## Table columns
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/r_SVV_FUNCTION_PRIVILEGES.html)
## Sample query
The following example displays the result of the SVV\$1FUNCTION\$1PRIVILEGES.
```
SELECT namespace_name,function_name,argument_types,privilege_type,identity_name,identity_type,admin_option FROM svv_function_privileges
WHERE identity_name IN ('role1', 'reguser');
namespace_name | function_name | argument_types | privilege_type | identity_name | identity_type | admin_option
----------------+---------------+----------------------------+----------------+----------------+---------------+--------------
public | test_func1 | integer | EXECUTE | role1 | role | False
public | test_func2 | integer, character varying | EXECUTE | reguser | user | False
```
# SVV\$1GEOGRAPHY\$1COLUMNS
Use SVV\$1GEOGRAPHY\$1COLUMNS to view the list of GEOGRAPHY columns in your data warehouse. This list of columns includes columns from datashares.
SVV\$1GEOGRAPHY\$1COLUMNS is visible to all users. Superusers can see all rows; regular users can see only their own data. For more information, see [Visibility of data in system tables and views](cm_chap_system-tables.md#c_visibility-of-data).
## Table columns
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/r_SVV_GEOGRAPHY_COLUMNS.html)
## Sample query
The following example displays the result of the SVV\$1GEOGRAPHY\$1COLUMNS.
```
SELECT * FROM svv_geography_columns;
f_table_catalog | f_table_schema | f_table_name | f_geography_column | coord_dimension | srid | type
-----------------+-----------------+---------------+---------------------+-----------------+------+--------------
dev | public | spatial_test | test_geography | 2 | 0 | GEOGRAPHY
```
# SVV\$1GEOMETRY\$1COLUMNS
Use SVV\$1GEOMETRY\$1COLUMNS to view the list of GEOMETRY columns in your data warehouse. This list of columns includes columns from datashares.
SVV\$1GEOMETRY\$1COLUMNS is visible to all users. Superusers can see all rows; regular users can see only their own data. For more information, see [Visibility of data in system tables and views](cm_chap_system-tables.md#c_visibility-of-data).
## Table columns
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/r_SVV_GEOMETRY_COLUMNS.html)
## Sample query
The following example displays the result of the SVV\$1GEOMETRY\$1COLUMNS.
```
SELECT * FROM svv_geometry_columns;
f_table_catalog | f_table_schema | f_table_name | f_geometry_column | coord_dimension | srid | type
-----------------+-----------------+---------------+---------------------+-----------------+------+--------------
dev | public | accomodations | shape | 2 | 0 | GEOMETRY
dev | public | zipcode | wkb_geometry | 2 | 0 | GEOMETRY
```
# SVV\$1IAM\$1PRIVILEGES
Use SVV\$1IAM\$1PRIVILEGES to view explicitly granted IAM privileges on users, roles and groups.
SVV\$1IAM\$1PRIVILEGES is visible to the following users:
+ Superusers
+ Users with the ACCESS SYSTEM TABLE permission
Other users can only see entries they have access to.
## Table columns
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/r_SVV_IAM_PRIVILEGES.html)
## Sample queries
The following example shows the results of SVV\$1IAM\$1PRIVILEGES.
```
SELECT * from SVV_IAM_PRIVILEGES ORDER BY IDENTITY_ID;
iam_arn | command_type | identity_id | identity_name | identity_type
----------------------+--------------+-------------+---------------+---------------
default-aws-iam-role | COPY | 0 | public | public
default-aws-iam-role | UNLOAD | 0 | public | public
default-aws-iam-role | CREATE MODEL | 0 | public | public
default-aws-iam-role | EXFUNC | 0 | public | public
default-aws-iam-role | COPY | 106 | u1 | user
default-aws-iam-role | UNLOAD | 106 | u1 | user
default-aws-iam-role | CREATE MODEL | 106 | u1 | user
default-aws-iam-role | EXFUNC | 106 | u1 | user
default-aws-iam-role | COPY | 118413 | r1 | role
default-aws-iam-role | UNLOAD | 118413 | r1 | role
default-aws-iam-role | CREATE MODEL | 118413 | r1 | role
default-aws-iam-role | EXFUNC | 118413 | r1 | role
(12 rows)
```
# SVV\$1IDENTITY\$1PROVIDERS
The SVV\$1IDENTITY\$1PROVIDERS view returns the name and additional properties for identity providers. For more information about how to create an identity provider, see [CREATE IDENTITY PROVIDER](r_CREATE_IDENTITY_PROVIDER.md).
SVV\$1IDENTITY\$1PROVIDERS is visible only to superusers. For more information, see [Visibility of data in system tables and views](cm_chap_system-tables.md#c_visibility-of-data).
## Table columns
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/r_SVV_IDENTITY_PROVIDERS.html)
### Sample queries
To view identity provider properties, run a query like the following after creating identity providers.
```
SELECT name, type, instanceid, namespc, params, enabled
FROM svv_identity_providers
ORDER BY 1;
```
The sample output includes param descriptions for Microsoft Entra ID (formerly Azure AD):
```
name | type | instanceid | namespc | params | enabled
------------------+-------+--------------------------------------+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------
rs5517_azure_idp | azure | e40d4bb2-7670-44ae-bfb8-5db013221d73 | abc | {"issuer":"https://login.microsoftonline.com/e40d4bb2-7670-44ae-bfb8-5db013221d73/v2.0", "client_id":"871c010f-5e61-4fb1-83ac-98610a7e9110", "client_secret":, "audience":["https://analysis.windows.net/powerbi/connector/AmazonRedshift", "https://analysis.windows.net/powerbi/connector/AWSRDS"]} | t
(1 row)
```
The sample output includes param descriptions for AWS IAM Identity Center:
```
name | type | instanceid | namespc | params | enabled
----------+--------+------------------------------------------------------------------------------------+------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------+---------
idc_name | awsidc | arn:aws:sso::123456789012:application/ssoins-12345f67fe123d4/apl-a0b0a12dc123b1a4 | idc_namespc | {"iam_role":"arn:aws:iam::123456789012:role/MyRedshiftRole","instance_arn":"arn:aws:sso:::instance/ssoins-12345f67fe123d4","is_lakehouse_app":"true"} | t
(1 row)
```
# SVV\$1INTEGRATION
SVV\$1INTEGRATION displays details about the configuration of integrations.
SVV\$1INTEGRATION is visible only to superusers. For more information, see [Visibility of data in system tables and views](cm_chap_system-tables.md#c_visibility-of-data).
For information about zero-ETL integrations, see [Zero-ETL integrations](https://docs.aws.amazon.com/redshift/latest/mgmt/zero-etl-using.html).
## Table columns
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/r_SVV_INTEGRATION.html)
## Sample queries
The following SQL command displays the currently defined integrations.
```
select * from svv_integration;
integration_id | target_database | source | state | current_lag | last_replicated_checkpoint | total_tables_replicated | total_tables_failed | creation_time | refresh_interval | source_database | is_history_mode
---------------------------------------+-----------------+--------+-----------------+-------------+-------------------------------------+-------------------------+---------------------+---------------------------+--------------------+-----------------+-----------------
99108e72-1cfd-414f-8cc0-0216acefac77 | perfdb | MySQL | CdcRefreshState | 56606106 | {"txn_seq":9834,"txn_id":126597515} | 152 | 0 | 2023-09-19 21:05:27.520299| 720 + mysourceetl | f
```
# SVV\$1INTEGRATION\$1TABLE\$1MAPPING
SVV\$1INTEGRATION\$1TABLE\$1MAPPING displays the mapping of source database, schema, table, column, and data type to the target when the identifier value of those fields are different.
**Note**
This view is only populated for the following types of zero-ETL integrations:
AWS Glue third-party applications to Amazon SageMaker Lakehouse
Amazon DynamoDB to Amazon SageMaker Lakehouse
For more information, see [Zero-ETL integrations](https://docs.aws.amazon.com/glue/latest/dg/zero-etl-using.html) in the *AWS Glue Developer Guide*.
The transformation of identifier values from source to target follow the following rules:
+ An upper case letter is converted to lower case.
+ A character that is not a lowercase letter, a digit, or underscore (\$1) is converted to an underscore (\$1).
+ If there is a conflict with a existing identifier value, then an Universally Unique Identifier (UUID) is appended to the new identifier.
+ If the source identifier value is an Amazon Redshift keyword, then the suffix `_redshift` is appended to the new identifier.
After transformation, a character must be a lowercase letter, a digit, or underscore (\$1) and match the regex pattern `[a-z0-9_]`. The following examples demonstrate the conversion rules:
| Source | Target | Notes |
| --- | --- | --- |
| foo | foo | No transformation |
| Bar | bar | |
| fooBar | foobar | |
| foo1 | foo1 | No transformation |
| foo\$11 | foo\$11 | No transformation |
| Bar@1 | bar\$11 | |
| foo\$1bar@ | foo\$1bar\$1 | |
| case | case\$1redshift | |
SVV\$1INTEGRATION\$1TABLE\$1MAPPING is visible to all users. Superusers can see all rows; regular users can see only their own data. For more information, see [Visibility of data in system tables and views](cm_chap_system-tables.md#c_visibility-of-data).
For information about zero-ETL integrations, see [Zero-ETL integrations](https://docs.aws.amazon.com/redshift/latest/mgmt/zero-etl-using.html) in the *Amazon Redshift Management Guide*.
## Table columns
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/r_SVV_INTEGRATION_TABLE_MAPPING.html)
## Sample queries
The following SQL command displays mapping of metadata values from source to target.
```
select * from svv_integration_table_mapping;
integration_id | source_database | target_database | source_schema_name | target_schema_name | source_table_name | target_table_name |
---------------------------------------+-----------------+-----------------+---------------------+--------------------+---------------------------------------+
99108e72-1cfd-414f-8cc0-0216acefac77 | mydatabase | mydatabase | myschema | myschema | Mytable | mytable |
| source_column_name | target_column_name | source_data_type | target_data_type |
+--------------------+--------------------+-------------------+------------------+
| Mycolumnname | mycolumnname | Mydatatype | mydatatype |
```
# SVV\$1INTEGRATION\$1TABLE\$1STATE
SVV\$1INTEGRATION\$1TABLE\$1STATE displays details about table-level integration information.
SVV\$1INTEGRATION\$1TABLE\$1STATE is visible only to superusers. For more information, see [Visibility of data in system tables and views](cm_chap_system-tables.md#c_visibility-of-data).
For more information, see [Zero-ETL integrations](https://docs.aws.amazon.com/redshift/latest/mgmt/zero-etl-using.html).
## Table columns
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/r_SVV_INTEGRATION_TABLE_STATE.html)
## Sample queries
The following SQL command displays the columns of the log of integrations.
```
select * from svv_integration_table_state;
integration_id | target_database | schema_name | table_name | table_state |table_last_replicated_checkpoint | reason | last_updated_timestamp |table_rows | table_size | is_history_mode
--------------------------------------+-----------------+-------------+-------------------+--------------+---------------------------------+--------+----------------------------+------------+------------+-----------------
4798e675-8f9f-4686-b05f-92c538e19629 | sample_test2 | sample | SampleTestChannel | Synced | {"txn_seq":3,"txn_id":3122} | | 2023-05-12 12:40:30.656625 | 2 | 16 | f
```
# SVV\$1INTERLEAVED\$1COLUMNS
Use the SVV\$1INTERLEAVED\$1COLUMNS view to help determine whether a table that uses interleaved sort keys should be reindexed using [VACUUM REINDEX](r_VACUUM_command.md#vacuum-reindex). For more information about how to determine how often to run VACUUM and when to run a VACUUM REINDEX, see [Minimizing vacuum times](vacuum-managing-vacuum-times.md).
SVV\$1INTERLEAVED\$1COLUMNS is visible only to superusers. For more information, see [Visibility of data in system tables and views](cm_chap_system-tables.md#c_visibility-of-data).
## Table columns
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/r_SVV_INTERLEAVED_COLUMNS.html)
## Sample queries
To identify tables that might need to be reindexed, run the following query.
```
select tbl as tbl_id, stv_tbl_perm.name as table_name,
col, interleaved_skew, last_reindex
from svv_interleaved_columns, stv_tbl_perm
where svv_interleaved_columns.tbl = stv_tbl_perm.id
and interleaved_skew is not null;
tbl_id | table_name | col | interleaved_skew | last_reindex
--------+------------+-----+------------------+--------------------
100068 | lineorder | 0 | 3.65 | 2015-04-22 22:05:45
100068 | lineorder | 1 | 2.65 | 2015-04-22 22:05:45
100072 | customer | 0 | 1.65 | 2015-04-22 22:05:45
100072 | lineorder | 1 | 1.00 | 2015-04-22 22:05:45
(4 rows)
```
# SVV\$1LANGUAGE\$1PRIVILEGES
Use SVV\$1LANGUAGE\$1PRIVILEGES to view the language permissions that are explicitly granted to users, roles, and groups in the current database.
SVV\$1LANGUAGE\$1PRIVILEGES is visible to the following users:
+ Superusers
+ Users with the ACCESS SYSTEM TABLE permission
Other users can only see identities they have access to or own.
## Table columns
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/r_SVV_LANUGAGE_PRIVILEGES.html)
## Sample query
The following example displays the result of the SVV\$1LANGUAGE\$1PRIVILEGES.
```
SELECT language_name,privilege_type,identity_name,identity_type,admin_option FROM svv_language_privileges
WHERE identity_name IN ('role1', 'reguser');
language_name | privilege_type | identity_name | identity_type | admin_option
---------------+----------------+---------------+---------------+---------------
exfunc | USAGE | reguser | user | False
exfunc | USAGE | role1 | role | False
plpythonu | USAGE | reguser | user | False
```
# SVV\$1MASKING\$1POLICY
Use SVV\$1MASKING\$1POLICY to view all masking policies created on the cluster.
Only superusers and users with the [https://docs.aws.amazon.com/redshift/latest/dg/r_roles-default.html](https://docs.aws.amazon.com/redshift/latest/dg/r_roles-default.html) role can view SVV\$1MASKING\$1POLICY. Regular users will see 0 rows.
## Table columns
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/r_SVV_MASKING_POLICY.html)
# SVV\$1ML\$1MODEL\$1INFO
State information about the current state of the machine learning model.
SVV\$1ML\$1MODEL\$1INFO is visible to all users. Superusers can see all rows; regular users can see only their own data. For more information, see [Visibility of data in system tables and views](cm_chap_system-tables.md#c_visibility-of-data).
## Table columns
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/r_SVV_ML_MODEL_INFO.html)
## Sample query
The following query displays the current state of machine learning models.
```
SELECT schema_name, model_name, model_state
FROM svv_ml_model_info;
schema_name | model_name | model_state
-------------+------------------------------+--------------------------------------
public | customer_churn_auto_model | Train Model On SageMaker In Progress
public | customer_churn_xgboost_model | Model is Ready
(2 row)
```
# SVV\$1ML\$1MODEL\$1PRIVILEGES
Use SVV\$1ML\$1MODEL\$1PRIVILEGES to view the machine learning model permissions that are explicitly granted to users, roles, and groups in the cluster.
SVV\$1ML\$1MODEL\$1PRIVILEGES is visible to the following users:
+ Superusers
+ Users with the ACCESS SYSTEM TABLE permission
Other users can only see identities they have access to or own.
## Table columns
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/r_SVV_ML_MODEL_PRIVILEGES.html)
## Sample query
The following example displays the result of the SVV\$1ML\$1MODEL\$1PRIVILEGES.
```
SELECT namespace_name,model_name,model_version,privilege_type,identity_name,identity_type,admin_option FROM svv_ml_model_privileges
WHERE model_name = 'test_model';
namespace_name | model_name | model_version | privilege_type | identity_name | identity_type | admin_option
----------------+------------+---------------+----------------+----------------+---------------+--------------
public | test_model | 1 | EXECUTE | reguser | user | False
public | test_model | 1 | EXECUTE | role1 | role | False
```
# SVV\$1MV\$1DEPENDENCY
The SVV\$1MV\$1DEPENDENCY table shows the dependencies of materialized views on other materialized views within Amazon Redshift.
For more information about materialized views, see [Materialized views in Amazon Redshift](materialized-view-overview.md).
SVV\$1MV\$1DEPENDENCY is visible to all users. Superusers can see all rows; regular users can see only their own data. For more information, see [Visibility of data in system tables and views](cm_chap_system-tables.md#c_visibility-of-data).
## Table columns
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/r_SVV_MV_DEPENDENCY.html)
## Sample query
The following query returns an output row that indicates that the materialized view `mv_over_foo` uses the materialized view `mv_foo` in its definition as a dependency.
```
CREATE SCHEMA test_ivm_setup;
CREATE TABLE test_ivm_setup.foo(a INT);
CREATE MATERIALIZED VIEW test_ivm_setup.mv_foo AS SELECT * FROM test_ivm_setup.foo;
CREATE MATERIALIZED VIEW test_ivm_setup.mv_over_foo AS SELECT * FROM test_ivm_setup.mv_foo;
SELECT * FROM svv_mv_dependency;
database_name | schema_name | name | dependent_database_name | dependent_schema_name | dependent_name
---------------+----------------------+-------------+-------------------------+---------------------------+----------
dev | test_ivm_setup | mv_over_foo | dev | test_ivm_setup | mv_foo
```
# SVV\$1MV\$1INFO
The SVV\$1MV\$1INFO table contains a row for every materialized view, whether the data is stale, and state information.
For more information about materialized views, see [Materialized views in Amazon Redshift](materialized-view-overview.md).
SVV\$1MV\$1INFO is visible to all users. Superusers can see all rows; regular users can see only their own data. For more information, see [Visibility of data in system tables and views](cm_chap_system-tables.md#c_visibility-of-data).
## Table columns
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/r_SVV_MV_INFO.html)
## Sample query
To view the state of all materialized views, run the following query.
```
select * from svv_mv_info;
```
This query returns the following sample output.
```
database_name | schema_name | user_name | name | is_stale | state | autorefresh | autorewrite
--------------+-------------------------+-----------+---------+-----------+-------+-------------+----------------
dev | test_ivm_setup | catch-22 | mv | f | 1 | 1 | 0
dev | test_ivm_setup | lotr | old_mv | t | 1 | 0 | 1
```
# SVV\$1QUERY\$1INFLIGHT
Use the SVV\$1QUERY\$1INFLIGHT view to determine what queries are currently running on the database. This view joins [STV\$1INFLIGHT](r_STV_INFLIGHT.md) to [STL\$1QUERYTEXT](r_STL_QUERYTEXT.md). SVV\$1QUERY\$1INFLIGHT does not show leader-node only queries. For more information, see [Leader node–only functions](c_SQL_functions_leader_node_only.md).
SVV\$1QUERY\$1INFLIGHT is visible to all users. Superusers can see all rows; regular users can see only their own data. For more information, see [Visibility of data in system tables and views](cm_chap_system-tables.md#c_visibility-of-data).
**Note**
This view is only available when querying provisioned clusters.
## Table columns
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/r_SVV_QUERY_INFLIGHT.html)
## Sample queries
The sample output below shows two queries currently running, the SVV\$1QUERY\$1INFLIGHT query itself and query 428, which is split into three rows in the table. (The starttime and statement columns are truncated in this sample output.)
```
select slice, query, pid, starttime, suspended, trim(text) as statement, sequence
from svv_query_inflight
order by query, sequence;
slice|query| pid | starttime |suspended| statement | sequence
-----+-----+------+----------------------+---------+-----------+---------
1012 | 428 | 1658 | 2012-04-10 13:53:... | 0 | select ...| 0
1012 | 428 | 1658 | 2012-04-10 13:53:... | 0 | enueid ...| 1
1012 | 428 | 1658 | 2012-04-10 13:53:... | 0 | atname,...| 2
1012 | 429 | 1608 | 2012-04-10 13:53:... | 0 | select ...| 0
(4 rows)
```
# SVV\$1QUERY\$1STATE
Use SVV\$1QUERY\$1STATE to view information about the runtime of currently running queries.
The SVV\$1QUERY\$1STATE view contains a data subset of the STV\$1EXEC\$1STATE table.
SVV\$1QUERY\$1STATE is visible to all users. Superusers can see all rows; regular users can see only their own data. For more information, see [Visibility of data in system tables and views](cm_chap_system-tables.md#c_visibility-of-data).
Some or all of the data in this table can also be found in the SYS monitoring view [SYS\$1QUERY\$1DETAIL](SYS_QUERY_DETAIL.md). The data in the SYS monitoring view is formatted to be easier to use and understand. We recommend that you use the SYS monitoring view for your queries.
**Note**
This view is only available when querying provisioned clusters.
## Table columns
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/r_SVV_QUERY_STATE.html)
## Sample queries
**Determining the processing time of a query by step**
The following query shows how long each step of the query with query ID 279 took to run and how many data rows Amazon Redshift processed:
```
select query, seg, step, maxtime, avgtime, rows, label
from svv_query_state
where query = 279
order by query, seg, step;
```
This query retrieves the processing information about query 279, as shown in the following sample output:
```
query | seg | step | maxtime | avgtime | rows | label
------+---------+------+---------+---------+---------+-------------------
279 | 3 | 0 | 1658054 | 1645711 | 1405360 | scan
279 | 3 | 1 | 1658072 | 1645809 | 0 | project
279 | 3 | 2 | 1658074 | 1645812 | 1405434 | insert
279 | 3 | 3 | 1658080 | 1645816 | 1405437 | distribute
279 | 4 | 0 | 1677443 | 1666189 | 1268431 | scan
279 | 4 | 1 | 1677446 | 1666192 | 1268434 | insert
279 | 4 | 2 | 1677451 | 1666195 | 0 | aggr
(7 rows)
```
**Determining if any active queries are currently running on disk**
The following query shows if any active queries are currently running on disk:
```
select query, label, is_diskbased from svv_query_state
where is_diskbased = 't';
```
This sample output shows any active queries currently running on disk:
```
query | label | is_diskbased
-------+--------------+--------------
1025 | hash tbl=142 | t
(1 row)
```
# SVV\$1REDSHIFT\$1COLUMNS
Use SVV\$1REDSHIFT\$1COLUMNS to view a list of all columns that a user has access to. This set of columns includes the columns on the cluster and the columns from datashares provided by remote clusters.
SVV\$1REDSHIFT\$1COLUMNS is visible to all users. Superusers can see all rows; regular users can see only their own data. For more information, see [Visibility of data in system tables and views](cm_chap_system-tables.md#c_visibility-of-data).
## Table columns
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/r_SVV_REDSHIFT_COLUMNS.html)
## Sample query
The following example returns the output of SVV\$1REDSHIFT\$1COLUMNS.
```
SELECT *
FROM svv_redshift_columns
WHERE database_name = 'tickit_db'
AND TABLE_NAME = 'tickit_sales_redshift'
ORDER BY COLUMN_NAME,
TABLE_NAME,
database_name
LIMIT 5;
database_name | schema_name | table_name | column_name | ordinal_position | data_type | column_default | is_nullable | encoding | distkey | sortkey | column_acl | remarks
--------------+-------------+-----------------------+-------------+------------------+-----------+----------------+-------------+----------+---------+---------+-------------+--------
tickit_db | public | tickit_sales_redshift | buyerid | 4 | integer | | NO | az64 | False | 0 | |
tickit_db | public | tickit_sales_redshift | commission | 9 | numeric | (8,2) | YES | az64 | False | 0 | |
tickit_db | public | tickit_sales_redshift | dateid | 6 | smallint | | NO | none | False | 1 | |
tickit_db | public | tickit_sales_redshift | eventid | 5 | integer | | NO | az64 | False | 0 | |
tickit_db | public | tickit_sales_redshift | listid | 2 | integer | | NO | az64 | True | 0 | |
```
# SVV\$1REDSHIFT\$1DATABASES
Use SVV\$1 REDSHIFT\$1DATABASES to view a list of all the databases that a user has access to. This includes the databases on the cluster and the databases created from datashares provided by remote clusters.
SVV\$1REDSHIFT\$1DATABASES is visible to all users by default. To control access to your database's metadata, enable metadata security for your provisioned cluster or serverless workgroup. Metadata security lets you separate view permissions for object metadata by users and roles. For more information, see [Metadata security](t_metadata_security.md).
## Table columns
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/r_SVV_REDSHIFT_DATABASES.html)
## Sample query
The following example returns the output for SVV\$1REDSHIFT\$1DATABASES.
```
select database_name, database_owner, database_type, database_options, database_isolation_level
from svv_redshift_databases;
database_name | database_owner | database_type | database_options | database_isolation_level
--------------+----------------+---------------+------------------+------------------
dev | 1 | local | NULL | Serializable
```
# SVV\$1REDSHIFT\$1FUNCTIONS
Use SVV\$1REDSHIFT\$1FUNCTIONS to view a list of all functions that a user has access to. This set of functions includes the functions on the cluster and the functions from datashares provided by remote clusters.
SVV\$1REDSHIFT\$1FUNCTIONS is visible to all users. Superusers can see all rows; regular users can see only their own data. For more information, see [Visibility of data in system tables and views](cm_chap_system-tables.md#c_visibility-of-data).
## Table columns
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/r_SVV_REDSHIFT_FUNCTIONS.html)
## Sample query
The following example returns the output of SVV\$1REDSHIFT\$1FUNCTIONS.
```
SELECT *
FROM svv_redshift_functions
WHERE database_name = 'tickit_db'
AND SCHEMA_NAME = 'public'
ORDER BY function_name
LIMIT 5;
database_name | schema_name | function_name | function_type | argument_type | result_type
--------------+-------------+-----------------------+------------------+------------------+-------------
tickit_db | public | shared_function | REGULAR FUNCTION | integer, integer | integer
```
# SVV\$1REDSHIFT\$1SCHEMA\$1QUOTA
Displays the quota and the current disk usage for each schema in a database.
SVV\$1REDSHIFT\$1SCHEMA\$1QUOTA is visible to all users. Superusers can see all rows; regular users can see only their own data. For more information, see [Visibility of data in system tables and views](cm_chap_system-tables.md#c_visibility-of-data).
This view is available when querying provisioned clusters or Redshift Serverless workgroups.
## Table columns
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/r_SVV_REDSHIFT_SCHEMA_QUOTA.html)
## Sample query
The following example displays the quota and the current disk usage for the schema named `sales_schema`.
```
SELECT TRIM(SCHEMA_NAME) "schema_name", QUOTA, disk_usage FROM svv_redshift_schema_quota
WHERE SCHEMA_NAME = 'sales_schema';
schema_name | quota | disk_usage
--------------+-------+------------
sales_schema | 2048 | 30
```
# SVV\$1REDSHIFT\$1SCHEMAS
Use SVV\$1REDSHIFT\$1SCHEMAS to view a list of all schemas that a user has access to. This set of schemas includes the schemas on the cluster and the schemas from datashares provided by remote clusters.
SVV\$1REDSHIFT\$1SCHEMAS is visible to all users. Superusers can see all rows; regular users can see only their own data. For more information, see [Visibility of data in system tables and views](cm_chap_system-tables.md#c_visibility-of-data).
## Table columns
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/r_SVV_REDSHIFT_SCHEMAS.html)
## Sample query
The following example returns the output of SVV\$1REDSHIFT\$1SCHEMAS.
```
SELECT *
FROM svv_redshift_schemas
WHERE database_name = 'tickit_db'
ORDER BY database_name,
SCHEMA_NAME;
database_name | schema_name | schema_owner | schema_type | schema_acl | schema_option
--------------+--------------------+--------------+-------------+------------+---------------
tickit_db | public | 1 | shared | |
```
# SVV\$1REDSHIFT\$1TABLES
Use SVV\$1REDSHIFT\$1TABLES to view a list of all tables that a user has access to. This set of tables includes the tables on the cluster and the tables from datashares provided by remote clusters.
SVV\$1REDSHIFT\$1TABLES is visible to all users. Superusers can see all rows; regular users can see only their own data. For more information, see [Visibility of data in system tables and views](cm_chap_system-tables.md#c_visibility-of-data).
## Table columns
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/r_SVV_REDSHIFT_TABLES.html)
## Sample query
The following example returns the output of SVV\$1REDSHIFT\$1TABLES.
```
SELECT *
FROM svv_redshift_tables
WHERE database_name = 'tickit_db' AND TABLE_NAME LIKE 'tickit_%'
ORDER BY database_name,
TABLE_NAME;
database_name | schema_name | table_name | table_type | table_acl | remarks | table_owner
--------------+-------------+--------------------------+------------+-----------+---------+-----------
tickit_db | public | tickit_category_redshift | TABLE | | +
tickit_db | public | tickit_date_redshift | TABLE | | +
tickit_db | public | tickit_event_redshift | TABLE | | +
tickit_db | public | tickit_listing_redshift | TABLE | | +
tickit_db | public | tickit_sales_redshift | TABLE | | +
tickit_db | public | tickit_users_redshift | TABLE | | +
tickit_db | public | tickit_venue_redshift | TABLE | |
```
If the table\$1acl value is null, no access privileges have been explicitly granted to the corresponding table.
# SVV\$1RELATION\$1PRIVILEGES
Use SVV\$1RELATION\$1PRIVILEGES to view the relation (tables and views) permissions that are explicitly granted to users, roles, and groups in the current database.
SVV\$1RELATION\$1PRIVILEGES is visible to the following users:
+ Superusers
+ Users with the SYSLOG ACCESS UNRESTRICTED permission
Other users can only see identities they have access to or own. For more information about data visibility, see [Visibility of data in system tables and views](cm_chap_system-tables.md#c_visibility-of-data).
## Table columns
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/r_SVV_RELATION_PRIVILEGES.html)
## Sample query
The following example displays the result of the SVV\$1RELATION\$1PRIVILEGES.
```
SELECT namespace_name,relation_name,privilege_type,identity_name,identity_type,admin_option FROM svv_relation_privileges
WHERE relation_name = 'orders' AND privilege_type = 'SELECT';
namespace_name | relation_name | privilege_type | identity_name | identity_type | admin_option
----------------+---------------+----------------+----------------+---------------+--------------
public | orders | SELECT | reguser | user | False
public | orders | SELECT | role1 | role | False
```
# SVV\$1RLS\$1APPLIED\$1POLICY
Use SVV\$1RLS\$1APPLIED\$1POLICY to trace the application of RLS policies on queries that reference RLS-protected relations.
SVV\$1RLS\$1APPLIED\$1POLICY is visible to the following users:
+ Superusers
+ Users with the `sys:operator` role
+ Users with the ACCESS SYSTEM TABLE permission
Note that sys:secadmin isn't granted this system permission.
## Table columns
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/r_SVV_RLS_APPLIED_POLICY.html)
## Sample query
The following example displays the result of the SVV\$1RLS\$1APPLIED\$1POLICY. To query the SVV\$1RLS\$1APPLIED\$1POLICY, you must have the ACCESS SYSTEM TABLE permission.
```
-- Check what RLS policies were applied to the run query.
SELECT username, command, datname, relschema, relname, polname, poldefault
FROM svv_rls_applied_policy
WHERE datname = CURRENT_DATABASE() AND query = PG_LAST_QUERY_ID();
username | command | datname | relschema | relname | polname | poldefault
----------+---------+-----------+-----------+--------------------------+-----------------+------------
molly | s | tickit_db | public | tickit_category_redshift | policy_concerts |
```
# SVV\$1RLS\$1ATTACHED\$1POLICY
Use SVV\$1RLS\$1ATTACHED\$1POLICY to view a list of all relations and users that have one or more row-level security policies attached on the currently connected database.
Only users with the sys:secadmin role can query this view.
## Table columns
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/r_SVV_RLS_ATTACHED_POLICY.html)
## Sample query
The following example displays the result of the SVV\$1RLS\$1ATTACHED\$1POLICY.
```
--Inspect the policy in SVV_RLS_ATTACHED_POLICY
SELECT * FROM svv_rls_attached_policy;
relschema | relname | relkind | polname | grantor | grantee | granteekind | is_pol_on | is_rls_on | rls_conjuntion_type
-----------+--------------------------+---------+-----------------+---------+----------+-------------+-----------+-----------+---------------------
public | tickit_category_redshift | table | policy_concerts | bob | analyst | role | True | True | and
public | tickit_category_redshift | table | policy_concerts | bob | dbadmin | role | True | True | and
```
# SVV\$1RLS\$1POLICY
Use SVV\$1RLS\$1POLICY to view a list of all row-level security policies created on the Amazon Redshift cluster.
SVV\$1RLS\$1POLICY is visible to all users. Superusers can see all rows; regular users can see only their own data. For more information, see [Visibility of data in system tables and views](cm_chap_system-tables.md#c_visibility-of-data).
## Table columns
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/r_SVV_RLS_POLICY.html)
## Sample query
The following example displays the result of the SVV\$1RLS\$1POLICY.
```
-- Create some policies.
CREATE RLS POLICY pol1 WITH (a int) AS t USING ( t.a IS NOT NULL );
CREATE RLS POLICY pol2 WITH (c varchar(10)) AS t USING ( c LIKE '%public%');
-- Inspect the policy in SVV_RLS_POLICY
SELECT * FROM svv_rls_policy;
poldb | polname | polalias | polatts | polqual | polenabled | polmodifiedby | polmodifiedtime
-------+---------+----------+--------------------------------------------------+---------------------------------------+------------+---------------+---------------------
my_db | pol1 | t | [{"colname":"a","type":"integer"}] | "t"."a" IS NOT NULL | t | policy_admin | 2022-02-11 14:40:49
my_db | pol2 | t | [{"colname":"c","type":"character varying(10)"}] | "t"."c" LIKE CAST('%public%' AS TEXT) | t | policy_admin | 2022-02-11 14:41:28
```
# SVV\$1RLS\$1RELATION
Use SVV\$1RLS\$1RELATION to view a list of all relations that are RLS-protected.
SVV\$1RLS\$1RELATION is visible to all users. Superusers can see all rows; regular users can see only their own data. For more information, see [Visibility of data in system tables and views](cm_chap_system-tables.md#c_visibility-of-data).
## Table columns
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/r_SVV_RLS_RELATION.html)
## Sample query
The following example displays the result of the SVV\$1RLS\$1RELATION.
```
ALTER TABLE tickit_category_redshift ROW LEVEL SECURITY ON FOR DATASHARES;
--Inspect RLS state on the relations using SVV_RLS_RELATION.
SELECT datname, relschema, relname, relkind, is_rls_on, is_rls_datashare_on FROM svv_rls_relation ORDER BY relname;
datname | relschema | relname | relkind | is_rls_on | is_rls_datashare_on | rls_conjunction_type | rls_datashare_conjunction_type
-----------+-----------+--------------------------+---------+-----------+---------------------+----------------------+--------------------------------
tickit_db | public | tickit_category_redshift | table | t | t | and | and
(1 row)
```
# SVV\$1ROLE\$1GRANTS
Use SVV\$1ROLE\$1GRANTS to view a list of roles that are explicitly granted roles in the cluster.
SVV\$1ROLE\$1GRANTS is visible to the following users:
+ Superusers
+ Users with the ACCESS SYSTEM TABLE permission
Other users can only see identities they have access to or own.
## Table columns
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/r_SVV_ROLE_GRANTS.html)
## Sample query
The following example returns the output of SVV\$1ROLE\$1GRANTS.
```
GRANT ROLE role1 TO ROLE role2;
GRANT ROLE role2 TO ROLE role3;
SELECT role_name, granted_role_name FROM svv_role_grants;
role_name | granted_role_name
-----------+--------------------
role2 | role1
role3 | role2
(2 rows)
```
# SVV\$1ROLES
Use SVV\$1ROLES to view role information.
This table is visible to all users.
## Table columns
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/r_SVV_ROLES.html)
## Sample query
The following example returns the output of SVV\$1ROLES.
```
SELECT role_name,role_owner FROM svv_roles WHERE role_name IN ('role1', 'role2');
role_name | role_owner
-----------+------------
role1 | superuser
role2 | superuser
```
# SVV\$1SCHEMA\$1PRIVILEGES
Use SVV\$1SCHEMA\$1PRIVILEGES to view the schema permissions that are explicitly granted to users, roles, and groups in the current database.
SVV\$1SCHEMA\$1PRIVILEGES is visible to the following users:
+ Superusers
+ Users with the ACCESS SYSTEM TABLE permission
Other users can only see identities they have access to or own.
## Table columns
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/r_SVV_SCHEMA_PRIVILEGES.html)
## Sample query
The following example displays the result of the SVV\$1SCHEMA\$1PRIVILEGES.
```
SELECT namespace_name,privilege_type,identity_name,identity_type,admin_option FROM svv_schema_privileges
WHERE namespace_name = 'test_schema1';
namespace_name | privilege_type | identity_name | identity_type | admin_option
----------------+----------------+----------------+---------------+--------------
test_schema1 | USAGE | reguser | user | False
test_schema1 | USAGE | role1 | role | False
```
# SVV\$1SCHEMA\$1QUOTA\$1STATE
Displays the quota and the current disk usage for each schema.
Regular users can see information for schemas for which they have USAGE permission. Superusers can see information for all schemas in the current database.
SVV\$1SCHEMA\$1QUOTA\$1STATE is visible to all users. Superusers can see all rows; regular users can see only their own data. For more information, see [Visibility of data in system tables and views](cm_chap_system-tables.md#c_visibility-of-data).
**Note**
This view is only available when querying provisioned clusters.
## Table columns
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/r_SVV_SCHEMA_QUOTA_STATE.html)
## Sample query
The following example displays the quota and the current disk usage for the schema.
```
SELECT TRIM(SCHEMA_NAME) "schema_name", QUOTA, disk_usage, disk_usage_pct FROM svv_schema_quota_state
WHERE SCHEMA_NAME = 'sales_schema';
schema_name | quota | disk_usage | disk_usage_pct
--------------+-------+------------+----------------
sales_schema | 2048 | 30 | 1.46
(1 row)
```
# SVV\$1SYSTEM\$1PRIVILEGES
SVV\$1SYSTEM\$1PRIVILEGES is visible to the following users:
+ Superusers
+ Users with the ACCESS SYSTEM TABLE permission
Other users can only see identities they have access to or own.
## Table columns
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/r_SVV_SYSTEM_PRIVILEGES.html)
## Sample query
The following example displays the result for the specified parameters.
```
SELECT system_privilege,identity_name,identity_type FROM svv_system_privileges
WHERE system_privilege = 'ALTER TABLE' AND identity_name = 'sys:superuser';
system_privilege | identity_name | identity_type
------------------+---------------+---------------
ALTER TABLE | sys:superuser | role
```
# SVV\$1TABLE\$1INFO
Shows summary information for tables and materialized views in the currently connected database. The view filters out system tables, and shows only user-defined tables and materialized views that contain at least 1 row of data.
You can use the SVV\$1TABLE\$1INFO view to diagnose and address table design issues that can influence query performance. This includes issues with compression encoding, distribution keys, sort style, data distribution skew, table size, and statistics. The SVV\$1TABLE\$1INFO view doesn't return any information for empty tables.
The SVV\$1TABLE\$1INFO view summarizes information from the following system tables and catalog tables:
+ [STV\$1NODE\$1STORAGE\$1CAPACITY](r_STV_NODE_STORAGE_CAPACITY.md)
+ [STV\$1SLICES](r_STV_SLICES.md)
+ [STV\$1TBL\$1PERM](r_STV_TBL_PERM.md)
+ [PG\$1ATTRIBUTE](https://www.postgresql.org/docs/8.0/static/catalog-pg-attribute.html)
+ [PG\$1CLASS](https://www.postgresql.org/docs/8.0/static/catalog-pg-class.html)
+ [PG\$1DATABASE](https://www.postgresql.org/docs/8.0/static/catalog-pg-database.html)
+ [PG\$1NAMESPACE](https://www.postgresql.org/docs/8.0/static/catalog-pg-namespace.html)
+ [PG\$1STATISTIC\$1INDICATOR](r_PG_STATISTIC_INDICATOR.md)
SVV\$1TABLE\$1INFO is visible only to superusers. For more information, see [Visibility of data in system tables and views](cm_chap_system-tables.md#c_visibility-of-data). To permit a user to query the view, grant SELECT permission on SVV\$1TABLE\$1INFO to the user.
## Table columns
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/r_SVV_TABLE_INFO.html)
## Sample queries
The following example shows encoding, distribution style, sorting, and data skew for all user-defined tables in the database. Here, "table" must be enclosed in double quotation marks because it is a reserved word.
```
select "table", encoded, diststyle, sortkey1, skew_sortkey1, skew_rows
from svv_table_info
order by 1;
table | encoded | diststyle | sortkey1 | skew_sortkey1 | skew_rows
---------------+---------+-----------------+--------------+---------------+----------
category | N | EVEN | | |
date | N | ALL | dateid | 1.00 |
event | Y | KEY(eventid) | dateid | 1.00 | 1.02
listing | Y | KEY(listid) | dateid | 1.00 | 1.01
sales | Y | KEY(listid) | dateid | 1.00 | 1.02
users | Y | KEY(userid) | userid | 1.00 | 1.01
venue | N | ALL | venueid | 1.00 |
(7 rows)
```
# SVV\$1TABLES
Use SVV\$1TABLES to view tables in local and external catalogs.
SVV\$1TABLES is visible to all users by default. To control access to your database's metadata, enable metadata security for your provisioned cluster or serverless workgroup. Metadata security lets you separate view permissions for object metadata by users and roles. For more information, see [Metadata security](t_metadata_security.md).
## Table columns
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/r_SVV_TABLES.html)
# SVV\$1TRANSACTIONS
Records information about transactions that currently hold locks on tables in the database. Use the SVV\$1TRANSACTIONS view to identify open transactions and lock contention issues. For more information about locks, see [Managing concurrent write operations](c_Concurrent_writes.md) and [LOCK](r_LOCK.md).
SVV\$1TRANSACTIONS is visible to all users. Superusers can see all rows; regular users can see only their own data. For more information, see [Visibility of data in system tables and views](cm_chap_system-tables.md#c_visibility-of-data).
## Table columns
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/r_SVV_TRANSACTIONS.html)
## Sample queries
The following command shows all active transactions and the locks requested by each transaction.
```
select * from svv_transactions;
txn_ lockable_
owner | txn_db | xid | pid | txn_start | lock_mode | object_type | relation | granted
-------+--------+--------+-------+----------------------------+---------------------+----------------+----------+---------
root | dev | 438484 | 22223 | 2016-03-02 18:42:18.862254 | AccessShareLock | relation | 100068 | t
root | dev | 438484 | 22223 | 2016-03-02 18:42:18.862254 | ExclusiveLock | transactionid | | t
root | tickit | 438490 | 22277 | 2016-03-02 18:42:48.084037 | AccessShareLock | relation | 50860 | t
root | tickit | 438490 | 22277 | 2016-03-02 18:42:48.084037 | AccessShareLock | relation | 52310 | t
root | tickit | 438490 | 22277 | 2016-03-02 18:42:48.084037 | ExclusiveLock | transactionid | | t
root | dev | 438505 | 22378 | 2016-03-02 18:43:27.611292 | AccessExclusiveLock | relation | 100068 | f
root | dev | 438505 | 22378 | 2016-03-02 18:43:27.611292 | RowExclusiveLock | relation | 16688 | t
root | dev | 438505 | 22378 | 2016-03-02 18:43:27.611292 | AccessShareLock | relation | 100064 | t
root | dev | 438505 | 22378 | 2016-03-02 18:43:27.611292 | AccessExclusiveLock | relation | 100166 | t
root | dev | 438505 | 22378 | 2016-03-02 18:43:27.611292 | AccessExclusiveLock | relation | 100171 | t
root | dev | 438505 | 22378 | 2016-03-02 18:43:27.611292 | AccessExclusiveLock | relation | 100190 | t
root | dev | 438505 | 22378 | 2016-03-02 18:43:27.611292 | ExclusiveLock | transactionid | | t
(12 rows)
(12 rows)
```
# SVV\$1USER\$1GRANTS
Use SVV\$1USER\$1GRANTS to view the list of users that are explicitly granted roles in the cluster.
SVV\$1USER\$1GRANTS is visible to the following users:
+ Superusers
+ Users with the ACCESS SYSTEM TABLE permission
Other users can only see roles that are explicitly granted to them.
## Table columns
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/r_SVV_USER_GRANTS.html)
## Sample queries
The following queries grant roles to users and show the list of users that are explicitly granted roles.
```
GRANT ROLE role1 TO reguser;
GRANT ROLE role2 TO reguser;
GRANT ROLE role1 TO superuser;
GRANT ROLE role2 TO superuser;
SELECT user_name,role_name,admin_option FROM svv_user_grants;
user_name | role_name | admin_option
-----------+-----------+--------------
superuser | role1 | False
reguser | role1 | False
superuser | role2 | False
reguser | role2 | False
```
# SVV\$1USER\$1INFO
You can retrieve data about Amazon Redshift database users with the SVV\$1USER\$1INFO view.
SVV\$1USER\$1INFO is visible to all users. Superusers can see all rows; regular users can see only their own data. For more information, see [Visibility of data in system tables and views](cm_chap_system-tables.md#c_visibility-of-data).
## Table columns
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/r_SVV_USER_INFO.html)
## Sample queries
The following command retrieves user information from SVV\$1USER\$1INFO.
```
SELECT * FROM SVV_USER_INFO;
```
# SVV\$1VACUUM\$1PROGRESS
This view returns an estimate of how much time it will take to complete a vacuum operation that is currently in progress.
SVV\$1VACUUM\$1PROGRESS is visible only to superusers. For more information, see [Visibility of data in system tables and views](cm_chap_system-tables.md#c_visibility-of-data).
Some or all of the data in this table can also be found in the SYS monitoring view [SYS\$1VACUUM\$1HISTORY](SYS_VACUUM_HISTORY.md). The data in the SYS monitoring view is formatted to be easier to use and understand. We recommend that you use the SYS monitoring view for your queries.
For information about SVV\$1VACUUM\$1SUMMARY, see [SVV\$1VACUUM\$1SUMMARY](r_SVV_VACUUM_SUMMARY.md).
For information about SVL\$1VACUUM\$1PERCENTAGE, see [SVL\$1VACUUM\$1PERCENTAGE](r_SVL_VACUUM_PERCENTAGE.md).
**Note**
This view is only available when querying provisioned clusters.
## Table columns
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/r_SVV_VACUUM_PROGRESS.html)
## Sample queries
The following queries, run a few minutes apart, show that a large table named SALESNEW is being vacuumed.
```
select * from svv_vacuum_progress;
table_name | status | time_remaining_estimate
--------------+-------------------------------+-------------------------
salesnew | Vacuum: initialize salesnew |
(1 row)
...
select * from svv_vacuum_progress;
table_name | status | time_remaining_estimate
-------------+------------------------+-------------------------
salesnew | Vacuum salesnew sort | 33m 21s
(1 row)
```
The following query shows that no vacuum operation is currently in progress. The last table to be vacuumed was the SALES table.
```
select * from svv_vacuum_progress;
table_name | status | time_remaining_estimate
-------------+----------+-------------------------
sales | Complete |
(1 row)
```
# SVV\$1VACUUM\$1SUMMARY
The SVV\$1VACUUM\$1SUMMARY view joins the STL\$1VACUUM, STL\$1QUERY, and STV\$1TBL\$1PERM tables to summarize information about vacuum operations logged by the system. The view returns one row per table per vacuum transaction. The view records the elapsed time of the operation, the number of sort partitions created, the number of merge increments required, and deltas in row and block counts before and after the operation was performed.
SVV\$1VACUUM\$1SUMMARY is visible only to superusers. For more information, see [Visibility of data in system tables and views](cm_chap_system-tables.md#c_visibility-of-data).
Some or all of the data in this table can also be found in the SYS monitoring view [SYS\$1VACUUM\$1HISTORY](SYS_VACUUM_HISTORY.md). The data in the SYS monitoring view is formatted to be easier to use and understand. We recommend that you use the SYS monitoring view for your queries.
For information about SVV\$1VACUUM\$1PROGRESS, see [SVV\$1VACUUM\$1PROGRESS](r_SVV_VACUUM_PROGRESS.md).
For information about SVL\$1VACUUM\$1PERCENTAGE, see [SVL\$1VACUUM\$1PERCENTAGE](r_SVL_VACUUM_PERCENTAGE.md).
**Note**
This view is only available when querying provisioned clusters.
## Table columns
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/r_SVV_VACUUM_SUMMARY.html)
## Sample query
The following query returns statistics for vacuum operations on three different tables. The SALES table was vacuumed twice.
```
select table_name, xid, sort_partitions as parts, merge_increments as merges,
elapsed_time, row_delta, sortedrow_delta as sorted_delta, block_delta
from svv_vacuum_summary
order by xid;
table_ | xid |parts|merges| elapsed_ | row_ | sorted_ | block_
name | | | | time | delta | delta | delta
--------+------+-----+------+----------+---------+---------+--------
users | 2985 | 1 | 1 | 61919653 | 0 | 49990 | 20
category| 3982 | 1 | 1 | 24136484 | 0 | 11 | 0
sales | 3992 | 2 | 1 | 71736163 | 0 | 1207192 | 32
sales | 4000 | 1 | 1 | 15363010 | -851648 | -851648 | -140
(4 rows)
```