How can cloning the STAGING schema be achieved?

An Architect has been asked to clone schema STAGING as it looked one week ago, Tuesday June 1st at 8:00 AM, to recover some objects.

The STAGING schema has 50 days of retention.

The Architect runs the following statement:

CREATE SCHEMA STAGING_CLONE CLONE STAGING at (timestamp => ‘2021-06-01 08:00:00’);

The Architect receives the following error: Time travel data is not available for schema STAGING. The requested time is either beyond the allowed time travel period or before the object creation time. The Architect then checks the schema history and sees the following: CREATED_ON|NAME|DROPPED_ON

2021-06-02 23:00:00 | STAGING | NULL

2021-05-01 10:00:00 | STAGING | 2021-06-02 23:00:00

How can cloning the STAGING schema be achieved?
A . Undrop the STAGING schema and then rerun the CLONE statement.
B . Modify the statement: CREATE SCHEMA STAGING_CLONE CLONE STAGING at (timestamp => ‘2021-05-01 10:00:00’);
C . Rename the STAGING schema and perform an UNDROP to retrieve the previous STAGING schema version, then run the CLONE statement.
D . Cloning cannot be accomplished because the STAGING schema version was not active during the proposed Time Travel time period.

View Answer

Answer: C

Explanation:

The error message indicates that the schema STAGING does not have time travel data available for the requested timestamp, because the current version of the schema was created on 2021-06-02 23:00:00, which is after the timestamp of 2021-06-01 08:00:00. Therefore, the CLONE statement cannot access the historical data of the schema at that point in time.

Option A is incorrect, because undropping the STAGING schema will not restore the previous version of the schema that was active on 2021-06-01 08:00:00. Instead, it will create a new version of the schema with the same name and no data or objects.

Option B is incorrect, because modifying the timestamp to 2021-05-01 10:00:00 will not clone the schema as it looked one week ago, but as it looked when it was first created. This may not reflect the desired state of the schema and its objects.

Option C is correct, because renaming the STAGING schema and performing an UNDROP to retrieve

the previous STAGING schema version will restore the schema that was dropped on 2021-06-02

23:00:00. This schema has time travel data available for the requested timestamp of 2021-06-01

08:00:00, and can be cloned using the CLONE statement.

Option D is incorrect, because cloning can be accomplished by using the UNDROP command to access the previous version of the schema that was active during the proposed time travel period.

Reference: Cloning Considerations: Understanding & Using Time Travel: CREATE <object> … CLONE