SVV_DATASHARES
Use SVV_DATASHARES to view a list of datashares created on the cluster, and datashares shared with the cluster.
SVV_DATASHARES 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.
Table columns
| Column name | Data type | Description |
|---|---|---|
| share_name | varchar(128) | The name of a datashare. |
| share_id | integer | The ID of the datashare. |
| share_owner | integer | The owner of the datashare. |
| source_database | varchar(128) | The source database for this datashare. |
| consumer_database | varchar(128) | The consumer database that is created from this datashare. |
| share_type | varchar(8) | The type of the datashare. Possible values are INBOUND and OUTBOUND. |
| createdate | timestamp without time zone | The date when datashare was created. |
| is_publicaccessible | boolean | The property that specifies whether a datashare can be shared to a publicly accessible cluster. |
| share_acl | varchar(256) | The string that defines the permissions for the specified user or user group for the datashare. |
| producer_account | varchar(16) | The ID for the datashare producer account. |
| producer_namespace | varchar(64) | The unique cluster identifier for the datashare producer cluster. |
| managed_by | varchar(64) | The property that specifies the Amazon service that manages the datashare. |
Usage notes
Retrieving additional metadata – Using the integer returned in the share_owner column, you can join with usesysid
in SVL_USER_INFO to get data about the datashare owner. This includes the name and
additional properties.
Sample query
The following example returns the output for SVV_DATASHARES.
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_DATASHARES 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_DATASHARES 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