Altering and Dropping a SQL Snapshot

Prerequisite – SQL Snapshots

As changes in the database occur, the snapshot starts to grow/change, and may even see significant variation in size. Therefore, we change them when required to avoid low-disk-space alerts. Hence, we alter snapshots whenever necessary. If by mistake (or any other circumstance) a user drops a stored procedure, a view or a table, or any object, you can recover the database object using the snapshot. If all the objects and data were dropped, it would possible to recover all the information from the snapshot to the original database. 

Altering Snapshots in SQL :

Syntax :

ALTER SNAPSHOT <snapshot-name>
[schema]
[ [PCTFREE <Integer>] [PCTUSED <Integer>] 
[INITRANS <Integer>] [MAXTRANS <Integer>] 
[TABLESPACE <Tablespace>] [STORAGE <StorageClause>] 
[CLUSTER <cluster>(<column1> [<column2>, ....])]
]
[USING
 [INDEX
     [PCTFREE <Integer>] [PCTUSED <Integer>] 
     [INITRANS <Integer>] [MAXTRANS <Integer>]
]
[DEFAULT ROLLBACK SEGMENT
  [MASTER <RollbackSegment>/LOCAL]
]
]
[REFRESH [FAST/COMPLETE/FORCE]
 [START WITH <Date>][NEXT <Date>]  
 [WITH [PRIMARY KEY/ROWID]]
]

Keywords and Parameters :

• schema –
  Contains snapshot. If not specified, Oracle creates snapshot in user’s schema. 
  
• PCTFREE, PCTUSED, INITRANS and MAXTRANS –
  These establish values for specified parameters for internal table Oracle uses to maintain snapshot’s data.
• TABLESPACE –
  Specifies tablespace in which the snapshot is to be created. If it is omitted, Oracle creates a snapshot in the default tablespace of the owner of the snapshot’s schema. 
  
• STORAGE –
  Establishes storage characteristics for table Oracle uses to maintain the snapshot’s data.
• CLUSTER –
  Creates the snapshot as part of the specified cluster. Because a clustered snapshot uses the cluster’s space allocation. 
  
• USING INDEX –
  Specifies parameters for the index to maintain snapshot. PCTFREE, PCTUSED, INITRANS and MAXTRANS specify default storage and transaction attributes for snapshot. 
  
• ROLLBACK SEGMENT –
  Specifies local snapshot and/or remote master rollback segments to be used during snapshot refresh. 
  
• MASTER –
  Specifies which rollback segment to use. 
  
• LOCAL –
  Specifies rollback segment to be used for local refresh group that contains snapshot. This is if not specified then Oracles uses by default. If a user specifies DEFAULT, the user cannot specify the rollback segment. 
  
• REFRESH –
  Specifies how and when Oracle refreshes snapshot automatically. 
  
• FAST –
  Specifies fast refresh or one using only the updated data stored in the snapshot log associated with master table. 
  
• COMPLETE –
  Specifies a complete refresh or one that re-executes snapshot’s query. 
  
• FORCE –
  Specifies fast refresh, if one is possible, or complete refresh, if a fast refresh is not possible. This is default induced by Oracle. 
  
• START WITH –
  Specifies date expression for first automatic refresh time. 
  
• NEXT –
  Specifies date expression for calculating the interval between automatic refreshes. 
  
• WITH PRIMARY KEY –
  Specifies primary key snapshots to be created. These allow snapshot master tables to be recognized without impacting snapshot’s ability to continue to fast refresh. 
  
• WITH ROWID –
  Specifies that ROWID snapshots are to be created that provide backward compatibility with Oracle. 
  

Example :

ALTER SNAPSHOT snp_emp
REFRESH COMPLETE  
START WITH TRUNC(SYSDATE+7) + 2/24 
NEXT SYSDATE+1

Dropping a SQL Snapshot :
Dropping an SQL snapshot removes the snapshot from the database. 

Syntax :

DROP SNAPSHOT 

When a snapshot is dropped, if it has a snapshot log associated with, only the rows required for maintaining that snapshot are dropped. Dropping a master table upon which a snapshot is based does not drop the snapshots. Any subsequent refreshes, however, will fail. 

Example :

drop snapshot snp_emp;