datafile resize

Hi guys,

how do i reset size of data file.currently for example temp and undo tablspace have lot of data files around 7 files each , where the utilization is very minimal.

Some other tablespace don't have any space left.the Drive on which all these tablespace are also full.the only option left for me resize couple of data files on various table spaces and assign this to the required ones.

when i try to resize it gives me an error, there is data in this you cannot resize .

if we resize will be there be any chance of DB crash or data lost, the reason asking is, it's production.

Pls help.

give an example of what you did, with error messages.

Nut if there is data above the point you are trying to resize to then it will fail

Hi Prodadmin,

If there is data in the data files and you try to resize(reduce) it some times it does give you error. Infact I tried once and it did not give me error and the database crashed and I have to recover it from the backups. Of course the file I was resizing was from a data tablespace not from TEMP or UNDO tablespace. Since that incedence I don't dare to do the resizing act in my life again, but I have seen some DBAs are comfortable doing it.

In your case you can do as follows (for 8i):

1. Do this in the maintenace window not when users are connected.

2. Stop and start the database.

3. Force an archive log. (Preferable SQL > ARCHIVE LOG LIST ; )

3. Stop the database and take a cold backup and start it again.

4. Identify the databafiles to be removed from the TEMP/UNDO tablespaces.

5. Take each datafile (that you want to drop) offline and drop them. The command should be similar (not same) to the command shown below.
ALTER DATABASE DATAFILE '/u02/oracle/rbdb1/users03.dbf' OFFLINE DROP;

6. The above procedure just delinks the datafiles from oracle tablespaces but they sill remain in the filesystem (unless you are using OMF feature of 9i).

7. Delete the files from the OS.

8. Stop the database, take a cold backup and restart the database.


Note:
MAKE SURE TO TAKE A COLD BACKUP AS MENTIONED IN STEP 3.
MAKE SURE TO TAKE A COLD BACKUP AS MENTIONED IN STEP 3.
MAKE SURE TO TAKE A COLD BACKUP AS MENTIONED IN STEP 3.
MAKE SURE TO TAKE A COLD BACKUP AS MENTIONED IN STEP 3.
MAKE SURE TO TAKE A COLD BACKUP AS MENTIONED IN STEP 3.


-Hidayath.

your crash wasnt due to resizing a datafile - thats a perfectly normal thing to do and used everywhere