Hi all,

I hope someone could help me with this [wierd things seem to happen only to me]. I am new to Oracle and this has been an interesting learning process.

I have three tablespaces in my current database [DB-1] with a total of 5 datafiles. I created a new Oracle instance and database on another server [DB-2]. I need to move tablespaces from DB-1 to DB-2.

Just yesterday I followed all the directions and ran the following commands sucessfully:

execute DBMS_TTS.TRANSPORT_SET_CHECK('DB1TBS1,DB1TBS2,DB1TBS3',TRUE);

SQL> select count(*) from TRANSPORT_SET_VIOLATIONS;
Results = 0 [which is good]

SQL>ALTER TABLESPACE DB1TBS1 READ ONLY;
SQL>ALTER TABLESPACE DB1TBS2 READ ONLY;
SQL>ALTER TABLESPACE DB1TBS3 READ ONLY;

Then I ran the following in the Windows Command prompt:
Exp TRANSPORT_TABLESPACE=y TABLESPACES=DB1TBS1, DB1TBS2, DB1TBS3 FILE=transport_tbs.dmp

Username: sys/syspass@DB1.domain.com AS SYSDBA

This executed fine and created the transport_tbs.dmp. Life is good.

After all this I realized I forgot to run a procedure that goes through all our tables and deletes unnecessary data. So I made the tablespaces READ WRITE, executed my procedure.

Then I ran the following commands:
SQL>ALTER TABLESPACE DB1TBS1 COALESCE
SQL>ALTER TABLESPACE DB1TBS2 COALESCE
SQL>ALTER TABLESPACE DB1TBS3 COALESCE
in order to coalesce free space in the tablespaces.

I come in this morning make my tablespaces READ ONLY again and try to export it - the window just hangs after I put in the SYS username & password at “About to export transportable tablespace metadata…”. I rebooted the server, restarted the Oracle Instance and tried again... same thing happened.

I would really appreciate anyone’s help on this.

Thanks,
Archita.