Re: Refreshing test d/base

Valentin Minzatu wrote:
> Is there a constraint which prevents you from using transportable
> tablespaces? If not I would see that as an alternative to be considered.
> PL/SQL is stored in the SYSTEM tablespace so as far as you don't alter it
> your code stays the same.
>
> Cheers,
> Valentin Minzatu
>

Have you ever used TTS to exchange the system tablespace? That should be something to watch :-)

The SYSTEM tablespace holds the data dictionary. When using TTS basically you tell the database wich objects are in the transported tablespaces. No wait, you just exchanged the tablespace with the data dictionary so where did that info go? Ah, well it's in the new system tablespace already, so never mind.

But unfortunately you missed two points:

1.) The manual:
<quote>
Limitations

Be aware of the following limitations as you plan for transportable tablespace use:

• The source and target database must be on the same hardware platform. For example, you can transport tablespaces between Sun Solaris Oracle databases, or you can transport tablespaces between Windows NT Oracle databases. However, you cannot transport a tablespace from a Sun Solaris Oracle database to an Windows NT Oracle database.
• The source and target database must use the same character set and national character set.
• You cannot transport a tablespace to a target database in which a tablespace with the same name already exists. </quote> ^^^^^^ ||||||

Now if system doesn't already exist you don't have a database.

2.) the test

sys_at_DEMO10G>execute dbms_tts.transport_set_check('system', true); BEGIN dbms_tts.transport_set_check('system', true); END;

*
ERROR at line 1:

ORA-01001: invalid cursor
ORA-06512: at "SYS.DBMS_SYS_SQL", line 899
ORA-06512: at "SYS.DBMS_SQL", line 19
ORA-06512: at "SYS.DBMS_TTS", line 815
ORA-29351: can not transport system, sysaux, or temporary tablespace 'system'
ORA-06512: at "SYS.DBMS_TTS", line 844
ORA-06512: at line 1

Cheers
Holger Received on Fri Apr 15 2005 - 02:10:07 CDT