Simpler Navigation for Servers and Operating Systems
Completed: a much simpler Servers and Operating Systems section of the Community. We combined many of the older boards, so you won't have to click through so many levels to get at the information you need. Check the consolidated boards here as many sub-forums are now single boards.
cancel
Showing results for 
Search instead for 
Did you mean: 

Import oracle8

Francesco_13
Regular Advisor

Import oracle8

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
4 REPLIES
Jean-Luc Oudart
Honored Contributor

Re: Import oracle8

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
fiat lux
Brian Crabtree
Honored Contributor

Re: Import oracle8

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
Indira Aramandla
Honored Contributor

Re: Import oracle8

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
Never give up, Keep Trying
Francesco_13
Regular Advisor

Re: Import oracle8

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.