Transportable Tablespace

[yumi_rama]

Hey,

I am trying to make a transportable tablespace. The steps i do is
Make the tablespace readonly
Copy the datafiles using O/s command.

Now whenever i do this, it says the files are being shared. I am unable to copy the datafiles.
Could anybody tell me do i have to shutdown the database or take the database offline, before i copy the datafiles.
I am using noarchivelog mode.
Please reply at yumi_rama@yahoo.com
Thanks in advance,

Rama

[omegamark]

For you to make the Transportable tablespace

First you have identify the tablespace whether it is self contained.

To say self-contained means say if there is only one table in a tablespace and for that if index is there on the other tablespace.You have to do a transporatble tablespace both for the data and index tablespace

you can check by

1Checking if a transportable tablespace set is self-contained

execute DBMS_TTS.TRANSPORT_SET_CHECK('AGG_DATA,AGG_INDEXES','True');

For your case pls check the file permissions and whether you do have rights to copy the files.

[hrishy]

execute DBMS_TTS.TRANSPORT_SET_CHECK ('AGG_DATA,AGG_INDEXES','FALSE');

If the specified set has any self-containment violations, the Oracle database server will populate the TRANSPORT_SET_ VIOLATIONS data-dictionary view with the error information. If the set has no violations, the view will be empty.

Once you have verified that the selected tablespace set is self-contained, make the tablespaces read only, as follows:

alter tablespace AGG_DATA read only;
alter tablespace AGG_INDEXES read only;

Next, export the metadata for the tablespaces, using the TRANSPORT_TABLESPACES and TABLESPACES parameters of the Oracle Export utility:

exp user/pwd TRANSPORT_TABLESPACE=Y TABLESPACES=(AGG_DATA,AGG_INDEXES) CONSTRAINTS=N GRANTS=Y TRIGGERS=N

As the example shows, you can specify whether triggers, constraints, and grants are exported along with the tablespace metadata. You should also note the names of the accounts that own objects in the transportable tablespace set. You can now copy the tablespaces' datafiles to an area accessible by the target database. If necessary, you can put the tablespaces back into read-write mode in the current database.

After you have generated the transportable tablespace set, you can move all files to an area the target database can access. Note, however, that views are not transported as part of the tablespace set! If you want to move views, you need to create those views manually once you have plugged the tablespace set into the target database.


Plugging in the Transportable Tablespace Set
Once you have moved the transportable tablespace set to an area the target database can access, you can plug the set into the target database. First, use the Oracle Import utility to import the exported metadata:
imp user/pwd TRANSPORT_TABLESPACE=Y DATAFILES=(agg_data.dbf,agg_indexes.dbf)

In the import command, you specify the datafiles that are part of the transportable tablespace set. When the import has finished, all of the new tablespaces in the transportable tablespace set are in read-only mode. To change them to read-write mode, you issue the alter tablespace read write command in the target database:

alter tablespace AGG_DATA read write;
alter tablespace AGG_INDEXES read write;

After you've made these changes, you should back up the control files of both the target and the source database:

alter database backup controlfile to trace;
Note that instead of importing the table's data, you have imported only the tablespaces' metadata—a significant performance savings. When you import the data into the target database, the schema owner must already exist—the import does not create a user. Also, if you exported grants as part of the metadata export, you must pre-create any users that will be granted privileges for the tables

Note that instead of importing the table's data, you have imported only the tablespaces' metadata—a significant performance savings. When you import the data into the target database, the schema owner must already exist—the import does not create a user. Also, if you exported grants as part of the metadata export, you must pre-create any users that will be granted privileges for the tables

hth
hrishy