Transportable Tablespaces and Mviews

[mike9]

Hi everybody,

We are using Oracle 9iR2 on a DWH project.

We would like to use the great Oracle feature called "Transportable Tablespace".

Unfortunalty this great feature doesn't seem to support Mviews.

For example:

• we got a tablespace called default_tbs
• we got a tablespace called mv_tbs
• on the tablespace default_tbs we got a table called my_table
• on the tablespace mv_tbs we got a mview called my_mviews referencing the table my_table

So now if I execute:
dbms_tts.TRANSPORT_SET_CHECK('default_tbs', TRUE);

I got the following violation:
Master table MY_USER.MY_TABLE in tablespace DEFAULT_TBS not allowed in transportable set

Moving this MV to the tablespace default_tbs would not solve the problem.

After reading a second time the Oracle doc I finally found a place saying:
"Limitations: Transportable tablespaces do not support: Materialized views/replication"

So my question is: are there any workarounds so that it would be possible to transport a tablesapce even if some tables on it are refenrenced by some MV?

Thanks for any feedback
Mike

[Axr2]

Oracle streams may be?

[julian]

How about if you:

1. Drop the MV first

2. Transport the TS

Check first:

select reason from sys.pluggable_set_check where ts1_name = 'DEFAULT_TBS';

3. Recreate te MV.

[mike9]

Hi Julian,

Thanks for your fedback.

So it seems that there is realy no other workaround than to drop these MVs.

Hope that this will change with some later releases.

Mike

[julian]

If the MVs are not that big, just drop them. Extract the DDL (mainly for the indexes) in advance and recreate them after transporting the TS.

[mike9]

Hi,

Based on the documentation it will be possible to do it with 10g

Oracle9i Database Administrator's Guide, Chapter 11
Transporting Tablespaces Between Databases, Limitations:
Transportable tablespaces do not support: Materialized views/replication

Oracle® Database Administrator's Guide 10g, Chapter 8
Transporting Tablespaces Between Databases, Limitations on Transportable Tablespace Use:
Objects with underlying objects (such as materialized views) or contained objects (such as partitioned tables) are not transportable unless all of the underlying or contained objects are in the tablespace set.

So now I got a least one good reason to migrate once to 10g