Resize a datafile.

Hi all,

Therz a problem while resizing a datafile.
Getting the error ORA-03296: cannot resize datafile - file and datafile not found. but the datafile exists in the specified path.

What could be the reason for this?

Thanks in advance,
dsr.

Hi,

For teh resizing, are u incresaing or decresaing? If decreasing, the talespace can only resize only to the amount of data block not beign used. SO if your tablepsace is very full. you may not be able to resize.

To increase. maybe you can check the exact pathing of the datafile and whether there is enough hardisk space to increase.

Make sure that the file name, extention and path you are typing is correct and included in single quotes.

Sanjay

select file_name , status from dba_data_files ;

if u are trying to decrease the datafile size below high water mark it is not possible . however u should able to increase the size .

Hi,

I carried out the defragmentation procedure and tried to resize the datafile, it worked.

Is it because there was not an extent large enough to hold the resized data file that the resizing process failed earlier?

Thanks all for ur reponses.
dsr.

I don't know what defragmentation procedure you carried out, but the error message you posted earlier sure was wrong. It said the file is not existing. If the datafile you need to shrink has occupied any blocks beyond the point you specified, it will specifically say that!

Hi

it is interesting.. Well.. One possibility..

While you created the datafile the file name may include a space. So oracle can recognize the file, but while you type it may not recognize. First check wether there is a space with the file name.

select '"'||file_name||'"' from dba_data_files;

check in the output there is a space allocated in the file name.


Thomas

Instead of using alter database datafile 'datafile name' resize , use the file_id. If the problem is the space between datafile name, you won't get problem this way.

select file_id, file_name from dba_data_files;

alter database datafile resize


F

ops...

alter database datafile id resize size

Hi,

The error message i gave is correct and there was no space in the file name...ie to tell that the path of the datafile i specified was correct.

Resizing(to increase the size) which was not possible before defragmentation was possible after that. Am new to dba so can anyone conclude something from the above.


Thanx all for ur responses,
dsr.