resizing the tablespace
807932Apr 12 2011 — edited Apr 13 2011Hi all,
I have ran out of space in the asm so couldn't add the datafiles to a tablesapce so I tried to resize the tablespace which has got unused space so that I can use the gained space to add datafile when I tried to do that I am getting the following error. What can I do
select FILE_NAME,TABLESPACE_NAME,sum(MAXBYTES)/1024/1024/1024,sum(USER_BYTES)/1024/1024/1024,sum(BYTES)/1024/1024/1024 from dba_data_files where TABLESPACE_NAME='DATA11' group by TABLESPACE_NAME,FILE_NAME;
FILE_NAME TABLESPACE_NAME SUM(MAXBYTES)/1024/1024/1024 SUM(USER_BYTES)/1024/1024/1024 SUM(BYTES)/1024/1024/1024
-------------------------------------------------- -------------------- ---------------------------- ------------------------------ -------------------------
+DATA_1/affperf/datafile/data11.290.639902725 DATA11 31.9999847 2.01165771 2.01171875
+DATA_1/affperf/datafile/data11.291.639902049 DATA11 31.9999847 1.91497803 1.91503906
+DATA_1/affperf/datafile/data11.296.639896325 DATA11 31.9999847 4.73626709 4.73632813
+DATA_1/affperf/datafile/data11.298.639895327 DATA11 31.9999847 1.99212646 1.9921875
+DATA_1/affperf/datafile/data11.300.639894203 DATA11 31.9999847 2.07611084 2.07617188
+DATA_1/affperf/datafile/data11.305.639892373 DATA11 31.9999847 1.93255615 1.93261719
+DATA_1/affperf/datafile/data11.319.639873689 DATA11 31.9999847 2.04779053 2.04785156
+DATA_1/affperf/datafile/data11.339.639863013 DATA11 31.9999847 5.23431396 5.234375
Actually the alloted size for +DATA_1/affperf/datafile/data11.290.639902725 file is 31 g but used is very less as you can see, but when I tried to make it to 20 g by using the following command i am getting the error.
SQL> ALTER DATABASE DATAFILE '+DATA_1/affperf/datafile/data11.291.639902049' RESIZE 20g;
ALTER DATABASE DATAFILE '+DATA_1/affperf/datafile/data11.291.639902049' RESIZE 20g
*
ERROR at line 1:
ORA-01237: cannot extend datafile 233
ORA-01110: data file 233: '+DATA_1/affperf/datafile/data11.291.639902049'
ORA-17505: ksfdrsz:1 Failed to resize file to size 2621440 blocks
ORA-15041: diskgroup space exhausted
What is the problem, Please help.