Re: Transportable tablespaces - changing file names

From: Howard J. Rogers <hjr_at_dizwell.com>
Date: Fri, 03 Dec 2004 10:35:54 +1100
Message-ID: <41afa6d3$0$12650$afc38c87@news.optusnet.com.au>

Chuck wrote:
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> Howard J. Rogers wrote:
> > Chuck wrote:
> >> Is it possible to change datafile names when transporting tablespaces
> >> between two databases? If so, how? The docs don't say much at all about
> >> how to do this. For example I have a tablespace named TTS with one
> >> datafile. My datafile name on the source db might be
> >> /u01/sourcedb/tts01.dbf but on the target db I want it to be
> >> /u01/targetdb/tts01.dbf. Same tablespace name but different datafile
> >> name. In fact the names would have to be different if the two dbs
> are on
> >> the same server.
> >> TIA.
> >
> >
> > It's been a while since I transported a tablespace, and of course whilst
> > I can guess you're using a Unix of some sort, I am completely in the
> > dark regarding your version... which may or may not affect things
> > (probably not in this specific case, but I hope you get the message for
> > the future).
> >
> > But the import parameters specified when you do a transportable
> > tablespace are there precisely to allow you to specify an altered data
> > file name.
> >
> > Try, for example, IMP HELP=Y, and look at the DATAFILES parameter.
> >
> > In fact, when transporting a tablespace (at least in 8i and 9i, which
> > was the last time I checked), you don't need to specify the TABLESPACE
> > parameter, because the dump file itself indicates quite obviously what
> > tablespace is being imported. So the tablespace name doesn't need
> > specifying, but the DATAFILES parameter always MUST be specified -and
> > precisely because it likely won't be now where it used to be originally.
> >
> > Regards
> > HJR
>
> The imp help=y doesn't help much either. It doesn't give any more info
> than the 9i manual did. All it says is "DATAFILES datafiles to be
> transported into database".

Which nevertheless represents the answer to your original "is it possible" and "if so, how" questions, you might note...

> How does the import utility know which datafiles in the DATAFILES=
> parameter to associate with which tablespaces in the export file?

New question! And a good one, too. As I've only done single tablespace transports (which is what your original post suggested you were doing, too), I've never bothered to find out.

A brief review of the documentation gives an example like so:

IMP TRANSPORT_TABLESPACE=y FILE=expdat.dmp

     DATAFILES=('/db/sales_jan','/db/sales_feb',...)
     TABLESPACES=(sales_1,sales_2) TTS_OWNERS=(dcranney,jfee)
     FROMUSER=(dcranney,jfee) TOUSER=(smith,williams)

...which certainly shows how multiple single-file tablespaces can be transported, though it is not very enlightening on whether, or how, import knows '/db/sales_jan' belongs to 'sales_1', and '/db/sales_feb' to sales_2... unless we are to assume that the two things are listed in the same order, and hence the first item in the first parameter's list 'belongs' to the first item in the second parameter's list.

But that cannot, surely, be right -otherwise, a simple mis-ordering of the import parameter values would have disastrous results. So I would imagine that the export metadata itself handles the associations, just as that metadata already supplies the name of the people who own the data in the transportable tablespaes, and the name of the tablespaces themselves: they're specified here to cause a *verification check* of the information to be made, not to spell out the names to an otherwise clueless import program.

My wild speculation is, therefore, that the metadata tells import the association between files and tablespaces. It would be easy enough to check this hypothesis: if I export two tablespaces and deliberately reverse the order of data files (or tablespace names), does the import still work?

Time for some testing...

SQL> create tablespace FIRST datafile '/oracle/oradata/test/f1.dbf' size 5m;

Tablespace created.

SQL> create tablespace SECOND datafile '/oracle/oradata/test/s1.dbf' size 5m;

Tablespace created.

SQL> connect scott/tiger
Connected.
SQL> create table F1 tablespace FIRST as select * from emp;

Table created.

SQL> create table S1 tablespace SECOND as select * from emp;

Table created.

SQL> select name from v$database;

NAME

TEST select table_name, tablespace_name from dba_tables where table_name in ('F1','S1');

TABLE_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
F1                             FIRST
S1                             SECOND

[So, in TEST, we have F1 in First, and S1 in Second)

SQL> alter tablespace first read only;

Tablespace altered.

SQL> alter tablespace second read only;

Tablespace altered.

[oracle_at_emerald oracle]$ exp \"sys/dizwell as sysdba\" transport_tablespace=y tablespaces=(first, second) file=tsport.dmp

Export: Release 10.1.0.2.0 - Production on Thu Dec 2 23:20:36 2004

Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options Export done in US7ASCII character set and AL16UTF16 NCHAR character set server uses WE8ISO8859P1 character set (possible charset conversion) Note: table data (rows) will not be exported About to export transportable tablespace metadata... For tablespace FIRST ...

. exporting cluster definitions
. exporting table definitions
. . exporting table                             F1

For tablespace SECOND ...

. exporting cluster definitions
. exporting table definitions
. . exporting table                             S1
. exporting referential integrity constraints
. exporting triggers
. end transportable tablespace metadata export

Export terminated successfully without warnings.

cp /oracle/oradata/test/f1.dbf /home/oracle/f1new.dbf cp /oracle/oradata/test/s1.dbf /home/oracle/s1new.dbf

[oracle_at_emerald oracle]$ imp \"sys/dizwell as sysdba\" transport_tablespace=y file=tsport.dmp
datafiles=('/home/oracle/s1new.dbf','/home/oracle/f1new.dbf') tablespaces=(FIRST,SECOND)

[Now, notice how I have deliberately listed the tablespaces in the right order (FIRST, SECOND), but I have equally deliberately listed the datafiles in the WRONG order: s1new and then f1new.

Import: Release 10.1.0.2.0 - Production on Thu Dec 2 23:25:17 2004

Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options

Export file created by EXPORT:V10.01.00 via conventional path About to import transportable tablespace(s) metadata... import done in US7ASCII character set and AL16UTF16 NCHAR character set import server uses WE8ISO8859P1 character set (possible charset conversion)

. importing SYS's objects into SYS
. importing SCOTT's objects into SCOTT
. . importing table                           "F1"
. . importing table                           "S1"
. importing SYS's objects into SYS

Import terminated successfully without warnings.

SQL> select table_name, tablespace_name from dba_tables where table_name in ('F1','S1'); 2

TABLE_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
F1                             FIRST
S1                             SECOND

SQL> select name from v$database;

NAME

SALES And in the new SALES database, we nevertheless end up with a table called F1 in FIRST and S1 in SECOND, which is entirely as it should be. The order in which things are listed in the import parameters is utterly irrelevant, therefore.

We can therefore state that the association between a tablespace and its datafiles is made *independent* of how you type the information as import parameters. This test doesn't prove whether it's actually the export metadata that supplies the association information, or whether, perhaps, and merely as an example, it's contained in the data file headers themselves.

But, the point regarding your revised question is: you don't have to worry about setting up the association. So long as you list where all your DATAFILES are now residing, import will just work out everything else for you.

Regards
HJR Received on Thu Dec 02 2004 - 17:35:54 CST