Copy table values from one DB to another DB

[InShadows]

I would like to copy all the values from one table in an 8i database to another 8i database. I need the rowid to be copied over as well. I can copy the rows over but I haven't been able to copy over the rowid. Please help. thanks. The tools that I have accessible to me is Oracle 8i tools and Toad 7.3.

 

[carp]

You won't be able to copy over the rowid. Rowids are generated automatically by Oracle.

 

[karluk]

I think your only option to preserve rowids would be to copy the entire database including all data files, control files and redo logs. That will preserve all the current structure, including rowids. I'm not sure if that's an option for you or not. You may need to preserve the existing database, and copying everything would overwrite what's currently there.

 

[InShadows]

thank you. I was afraid of that. and I cannot copy the database. that isn't an option either.

 

[karluk]

Well, your options are limited. You may have to abandon your plan to copy rowids and adopt a completely different approach. You don't explain why you need to preserve rowids, so it's hard to say what might work.

Why do you need the same rowid on two different databases? Are you looking for a way to uniquely identify a row? Do you have primary keys defined on your table? If not, can you create primary or unique keys before copying the table?

 

[InShadows]

the problem is that I am using the rowid as an identifier when a user logs into the system for the first time. They enter their name and at that point I retrieve their row which does not have the unique identifier except for the rowid (I cannot go by the name either). The rowid works as an identifier fine until I started getting rowids with a + at the end of the string and the + would be dropped from the querystring. We have a development and acceptance and production databases and the rowids with the + at the end are present in the acceptance and production environments but all the coding is in development so I wanted to test code in development without having to push my code to the acceptance level which will take up to 4 days.

 

[karluk]

I hesitate to recommend a complete rewrite of your application, but it certainly sounds as if you are using rowids in the same way as most programmers use sequences. If you used sequences to generate unique keys, you wouldn't have to deal with the problems you are seeing. Specifically, you would be able to transport tables between environments, and you wouldn't have to worry about unexpected characters showing up in your key fields.

 

[InShadows]

unfortunately we did have a sequence key for that table and the boss made us take it out and saying that he thought there was no reason for it to be in there. we complained but since he's the boss we took it out. I'm trying a session variable now but I just didn't want to wait on the other

http://www developers

to update the code to acceptance. thanks for all your help.

 

[carp]

You might want to open a TAR with Oracle asking them if they think this is a reasonable approach. Their answer will be "No". Oracle recommends you do NOT use ROWID like this, partly because of the problem you are running into. ROWIDs not only change from DB to DB, but also can change within the same database over time.

Your boss sounds like the personification of "a little knowledge is a dangerous thing"!

Why can't you use the user name as an identifier?

 

[InShadows]

I will probably now but the reason for that was I didn't want the name in the querystring because of spaces and possible apostrophes. Right now I have it in a session variable so it should work.