This topic describes the administrative tasks associated with managing streams.
Enabling Change Tracking on Views and Underlying Tables¶
In order for users to query change data on a view, change tracking must be enabled on the view and underlying tables.
Only the object owner (i.e. the role with the OWNERSHIP privilege) on a given view or underlying tables can enable change tracking.
The following options are available to enable change tracking:
Create a stream on the view using the view owner role. This action enables change tracking on the view.
If the same role also owns the underlying tables, change tracking is also enabled on the tables. Otherwise, the table owner must explicitly enable change tracking on the tables. For these steps, see Explicitly Enable Change Tracking on the Underlying Tables (in this topic).
Explicitly enable change tracking on the view and tables. For instructions, see the remaining instructions in this section.
Explicitly Enable Change Tracking on Views¶
Set the CHANGE_TRACKING parameter when creating a view (using CREATE VIEW) or later (using ALTER VIEW).
Note that change tracking must also be explicitly enabled on the underlying tables for a view. For instructions, see Explicitly Enable Change Tracking on the Underlying Tables (in this topic).
For example, create a secure view in the current schema that selects a subset of rows from a table:
CREATE SECURE VIEW v CHANGE_TRACKING = TRUE AS SELECT col1, col2 FROM t;
For example, modify an existing view to enable change tracking:
ALTER VIEW v2 SET CHANGE_TRACKING = TRUE;
Explicitly Enable Change Tracking on the Underlying Tables¶
When either creating or altering a view to specify CHANGE_TRACKING, the associated dependent database objects are automatically updated to enable change tracking. During the operation, the underlying resources are locked, which can cause latency for DDL/DML operations. For more information, refer to Resource Locking.
If the user executing the statement has not specified a role with sufficient permissions (OWNERSHIP), the statement will fail, underlying database objects will not updated, and locks will be released.
Set the CHANGE_TRACKING parameter when creating a table (using CREATE TABLE) or later (using ALTER TABLE).
For example, to create a table in the current schema:
CREATE TABLE t (col1 STRING, col2 NUMBER) CHANGE_TRACKING = TRUE;
For example,to modify an existing table to enable change tracking:
ALTER TABLE t1 SET CHANGE_TRACKING = TRUE;
When either creating or altering a TABLE to specify CHANGE_TRACKING, the table is locked for the duration of the operation which can cause latency for DML operations. For more information, refer to Resource Locking.
Avoiding Stream Staleness¶
To avoid having a stream become stale, we strongly recommend that you regularly consume its change data before its STALE_AFTER timestamp (that is, within the extended data retention period for the source object).
To view the data retention period for a stream, execute the DESCRIBE STREAM or SHOW STREAMS command. The STALE_AFTER column timestamp indicates when the stream is currently predicted to become stale (or when it became stale, if the timestamp is in the past). This timestamp is calculated by adding the larger of the DATA_RETENTION_TIME_IN_DAYS or MAX_DATA_EXTENSION_TIME_IN_DAYS parameter setting to the current timestamp. Note that if the timestamp is in the past, the stream may already be already stale. The STALE column also indicates whether the stream is expected to be stale, though the stream may not actually be stale yet.
Consuming the change data for a stream moves the STALE_AFTER timestamp forward.
For more information, see Data Retention Period and Staleness.