Thankyou for your helpful reply.

I have studied the "transport_tablespace" entries in your questions and answers and looked at the EXP parameters etc in the database utilities documentation. However, I am not there yet so I would like a little more help.

My Oracle 9iV2 datafile is not currently plugged into a 9i database as we no longer have 9i running.
Therefore, as I cannot export the tables from my datafile directly (EXP does not appear to accept a datafile filename, rather a tablespace name), I created a tablespace in our 10g database and added our existing 9iV2 datafile and set it to read-only status.

I ran EXP with "transport_tablespace=Y" and "tablespaces=old_tspace_name" and "rows=Y".
EXP reported the following (with other messages):
"Note: table data (rows) will not be exported." (Presumably this is correct).
It then appeared to export the various items of metadata, although no tables were listed. I allowed other options to be their defaults.

Then I ran IMP with "transport_tablespace=Y" and "tablespaces=old_tspace_name" and "datafile=old_tspace_name.dbf".
IMP reported the following (with other messages):
IMP-00003 Oracle error 1565 encountered.
ORA-01565 Error in identifying file "OLD_TSPACE_NAME.DBF"
ORA-27041 Unable to open file
OSD-04002 Unable to open file
O/S-Error The system cannot find the file specified.

I know that the file is present, and I have tried running IMP with the datafile attached to the 10g database and unnattached (in case the file was locked by Oracle).

I cannot see why the file cannot be found by IMP. May I ask for further help with this ?

Many thanks (and Happy New Year!)
Ken.

Thankyou again for replying.

I somehow thought that it might be the case. It is a pity because I was hoping I might be able to get at the data without having to reinstall Oracle 9i.

Dare I say it: In MS SQLServer you can take any datafile and simply "attach" it to your database with no real fuss or bother. Maybe in Oracle 11 we could feed in a request for "One-step attachment of datafiles into a database" ?

Thanks for your help.
Ken.

Sql Server does not attach a File to the Server. It does an automatic Import of the contents of the Export Dump just like Oracle.

Pity you know nothing about either databases. You have a job!!!!!!!!

John, maybe you didn't really intend your response to sound as uncharitable as it did !
And, while not wishing to make nor to prolong an argument with you, MS SQL Server Enterprise Manager menu options "Action -> All Tasks -> Attach Database ... " leads to a handy little dialogue that includes "MDF file of database to attach" including a browse button. I assure you that I have attached such a file without having run an export dump on the database that originally contained the file. Hence my comment !
As for how much I know about either database - I make no claims whatsoever on that score.

Many thanks.
Ken.

"I assure you that I have attached such a file without having run an export dump on the database that originally contained the file."

Granted I know very little about SQLServer. If it really is this easy, then this to me says that the database vendor has NO CONCEPT of security! Someone can simply take a copy of a data file without having to run any of the database utilities thus bypassing any kind security like EXP or OS privs would provide.

Sounds scary to me.

Thankyou for the comments about SQLServer. Just to add - I didn't run the sp_detach_db procedure against the file and it is not a single file database. In fact I have attached several different datafiles to several databases using the menu option mentioned, in SQLServer 2k. Thinking back to SQLServer v6/6.5/7 I think you had to do it by running the procedures whereas in the later versions Enterprise Manager also had the option on its menus.

However, may I please get back to my Oracle question ?
I have accpted that I need to get the metadata from the original SYSTEM tablespace. As I still have all the datafiles (including the SYSTEM) from my 9i database, and if possible I wish to avoid reinstalling 10g, will 10g accept all the 9i datafiles if I were to temporarily move to the 9i files so i can then do the EXP ? or are you saying that I HAVE to reinstall 9i.

When I get an answer to that, I reckon I should close this correspondence eh ? At least for my part. And thanks for all the contributions !

Regards
Ken.

as Tom said earlier, you have to reinstall 9i, and use the 9i exp tool to create the metadata from a living 9i instance.

I have all the files.
Many thanks !

Ken.

I don't mean to be a preacher, but one shouldn't comment on anybody's knowledge or judge anybody. I think the purpose of this website is to gain more and more knowledge and experience in order to make oracle world better & better. We should listen what the other person has to say or what is he experiencing.

Tom is doing a great job by sharing his precious knowledge and time with us, I think we should make a heavy use of it.

Hi Tom,

I need to refresh the Dev database (running on 9.2.0.6)
with ST database (running on 9.2.0.5).

My first apporach is using "Transportable tablespace feature" using export/import.

Question is, Will transportable tablespace method works across different Oracle9i releases ?


Thanks