Delete a database user. 1

[wachuna]

Oracle8i on Unix: I have a test system with two databases (test and test2). On test2 database I have a corrupt datafile in tablespace named PGL. Database user PGL uses this tablespace. Test2 will not open, because of the corrupt datafile. I would like to delete and recreate the PGL user, this should create new blank datafile. Can someone tell me how to delete a database user? Thank You

 

[dbtoo2001]

Dropping a user will not remove a datafile, corrupt or not.
Review:

http://www.dbforums.com/showthread.php?threadid=978759&highlight=corrupt+datafile

 

[dsanchez2]

As dbtoo2001 saids, droping the user will not remove the datafile. To do this you will need to drop the table-space.

 

[prosper]

Hi, wachuna.
1. Drop (Delete) a schema (database user) that it will not drop (delete) a tablespace, except you drop the tablespace.
2. To check whether a corrupt datafile exist, you can check it in database alert log. (or login sqlplus with sys / system, select * from v$recover_file
3. If you want to recover a data file and your database is in archive mode and you had full backup database before the datafile was corrupt, you can recover the tablespace with archive file(s).
a. To check whether your database is in archive mode by the following sql (login database with id sys or system)
select * from v$instance; and select * from v$database;
b. shutdown database and make a full database backup
c. copy the data file from the last database full backup
d. copy the archive files when you have delete the archive files. (all archive files which were created after last database backup until now).
e. login database with sysdba
f. startup mount (sqlplus command)
g. receover database (sqlplus command)
h. shutdown database
i. check alert log ( and check it by command : select * from v$recover_file, if it is ok , make a full backup of database.

Hope this can help you.