Import problems when defragmenting a database

From: Guy Dallaire <gdallair_at_criq.qc.ca>
Date: Thu, 13 Jan 2000 20:58:07 GMT
Message-ID: <387e3953.27707408@news.risq.qc.ca>

Hi,

Last time I defragged one of the databases here, I had a lot of problems while re-importing my full datasase export.

I proceeded by:

1. Taking a full export of the database to be defragged
2. Take a cold backup of the database files just in case
3. Drop and recreate the database + tablespaces (At the same time, move some stuff around to better distribute I/Os)
4. Reimport

I stumbled accross many problems

1. I had NOT recreated the users before doing the import. Assuming import would create them

This seemed to cause problems. For example, some users owned database links in the database, when they got recreated, the import would not be able to re-create the database links because the users where not granted the "create database link" privilege (It had been granted temporarily only to create the database link in the past and revoked immediatly after)

This lead to a myriad of other problems. Those users had synonyms that pointed to tables accross the databse links, those synonyms where invalid because the db link were not recreated. The users also had packages and procedures and views that used those synonyms and the packages etc were therefore invalid

Well, the import was a total mess.

Now, I have to defrag a new database and I'm wondering what to do to totally eliminate any pitfalls.

Before I restart the import, I think I should first recreate:

1. The tablespaces
2. The users
3. Grant ALL provileges to the users DURING the import (Grant DBA role ?)
4. import
5. Revoke DBA from all users (except those that already had it in the fist place)

Is this a good way to do it ? Any other advice ? Does anyone know of a script that would create a script to recreate my users (with the correct passwords) in the new database ?

What about a script to recreate the tablespaces ?

Thanks

---

Guy Dallaire, Sysadmin/DBA
Centre de recherche industrielle du Quebec Ste-Foy, QC, Canada
http://www.criq.qc.ca
e-mail: gdallair_at_criq.qc.ca
ICQ: 18846092 Received on Thu Jan 13 2000 - 14:58:07 CST