topic Re: Import oracle8 in Operating System - HP-UX

Import oracle8

Francesco_13 — Wed, 28 Apr 2004 09:15:20 GMT

Hi,
i do it to import in a new , and empty ,database oracle8 a export full from another database.
I do it to create previously a users or they are created automatically.
The data tablespace was created in a new database with different names.
Thanks.
Francesco

Re: Import oracle8

Jean-Luc Oudart — Wed, 28 Apr 2004 09:32:17 GMT

Francesco,

You can import to same user or another user. Create users with appropriate privileges (and quota) 1st.

If the tablespace name changes you will have to create the tables 1st and then import the DATA using parameter IGNORE=Y.

This is explained with more details in Metalink doc ID 1012307.6

Regards,
Jean-Luc

Re: Import oracle8

Brian Crabtree — Wed, 28 Apr 2004 17:25:12 GMT

Hmm. Users will be recreated in the database with the passwords and tablespaces from the original database. However, if the default or temporary tablespace name changes, the user creation will fail.

Tables/indexes will be created in the original tablespace. That is why changing a tablespace name is not normally a good idea. The only way to import a table into another tablespace is to create the table in the new tablespace, and import with the "IGNORE=Y" option on the import. This can be dangerous, as this could possibly import data twice if there is not a primary key or unique key associated with the table.

You can get a list of the table and index creation statements by issing the 'imp' command with the "INDEXFILE=table.sql", which will give all of the commands. If you just want the tables (no indexes), then you need to issue the "INDEXES=N" option as well.

Unless you really need to change the tablespace names, I would recommend going back to the original names instead of trying to change them.

Let me know if you have any questions.

Thanks,

Brian

Re: Import oracle8

Indira Aramandla — Wed, 28 Apr 2004 20:01:49 GMT

Hi,

Yes you can import from the original database from one user to the new database into another user in a straight forward way if the default tablespaces of the users were called the same name.

But you had mentioned that you created the data tablespace in the new database with different names.

Now you can still import the data, but you need to create the tables and indexes first.

So in your new database, as you had already created the tablespaces, first create the users assign the default tablespace and temporaryt tablespace and grant privileges and quotes.

Then on your old database, export the data user by user by owner = option.

Then on the new database you can run a fake import with the option imp fromuser= touser= and show =Y. This will not import but create a log file which will contain all the create table and index statements.

Then you can edit this log file and save it as .sql and run against the new database to create the tables and indexes.

Then import from the same export dump file with fromuser= touser= and ignore=Y options.

You can do this for all the schemas in the old database to be imported into the new database.

I hope this helps.

Indira A

Re: Import oracle8

Francesco_13 — Thu, 29 Apr 2004 08:03:38 GMT

Thanks to all.
I have created only before the users ad assigned to default tablespace, then i have imported.
Create before the tables, for rollback question was no ideally for me.
Best regards.