Re: Q:Snapshot index location

chuckh_at_ix.netcom.com (Chuck Hamilton) wrote:

>Does anyone know of a way to specify a different tablespaces for the
>snapshot index and the snapshot table when you create a new snapshot?
>Since it's usually recommended to put tables in a different tablespace
>from their indexes, you'd think that a command that creates both would
>allow this but I can't find any way to do it.
>--
>Chuck Hamilton
>chuckh_at_ix.netcom.com
 

>If at first you don't succeed, sky-diving isn't for you.

The syntax for this is documented in the SQL Language reference manual but here is an example:

SQL> l
  1 create snapshot
  2 emp_snap
  3 tablespace users
  4 using index tablespace temp
  5 as
  6* select * from scott.emp
SQL> /   Snapshot created.  

SQL> select table_name, tablespace_name from user_tables   2 where table_name like '%EMP_SNAP%';  

TABLE_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
SNAP$_EMP_SNAP                 USERS
 

SQL> select index_name, tablespace_name from user_indexes   2 where table_name like '%EMP_SNAP%';  

INDEX_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
I_SNAP$_EMP_SNAP               TEMP
 

You have complete control over pctfree, pctused, initial, next, tablespace etc for all of the created objects (table and index)

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government Received on Sun Oct 29 1995 - 00:00:00 CET