+DATA_1/affperf/datafile/data11.291.639902049 DATA11 31.9999847 1.91497803 1.91503906The datafile is only 1.9GB currently. Why not try resizing it to 2GB
+DATA_1/affperf/datafile/data11.290.639902725 DATA11 31.9999847 2.01165771 2.01171875this datafile is only 2.012GB in size.
Hemant K Chitale wrote:actually i am trying to shrink data11 tablespace+DATA_1/affperf/datafile/data11.291.639902049 DATA11 31.9999847 1.91497803 1.91503906The datafile is only 1.9GB currently. Why not try resizing it to 2GB
Similarly :+DATA_1/affperf/datafile/data11.290.639902725 DATA11 31.9999847 2.01165771 2.01171875this datafile is only 2.012GB in size.
What other files do you have in the DiskGroup ? What is the total size of the files ? How large is the DiskGroup ?
Hemant K Chitale
Hemant K Chitale wrote:FILE_NAME SUM(MAXBYTES)/1024/1024/1024 SUM(USER_BYTES)/1024/1024/1024 SUM(BYTES)/1024/1024/1024
But your command was attempting to resize the datafile upwards (i.e. increase it) ! Your SQL command wasn't shrinking the datafile but increasing the size.
Do you need to reduce OR to increase the sizes ?
Hemant K Chitale
In your queries (which are inconsistent) you mix the concept of tablespace (which is a logical grouping of one or more datafiles) and datafile (which is a physical allocation of space).
select tablespace_name, file_name, maxbytes/1024/1024/1024 Max_Size_GB, bytes/1024/1024/1024 Curr_Size_GB from dba_data_files -- optionally -- where tablespace_name = 'DATA11'
ALTER DATABASE DATAFILE '+DATA_1/affperf/datafile/data11.291.639902049' RESIZE 20g;was not shrinking but increasing the size of that datafile in DATA11.
Note : If the tablespace block size (which you can obtain from DBA_TABLESPACES) is 4K or 16K instead of 8K, change the 8192 multiplier accordingly.
select e.file_id file_id, f.file_name file_name, f.bytes/1024/1024 sz, f.maxbytes/1024/1024 msz, max(e.block_id+e.blocks)*8192/1024/1024 hwm from dba_data_files f, dba_extents e where e.tablespace_name = '&tbs_name' and f.file_id=e.file_id group by e.file_id, f.file_name, f.bytes, f.maxbytes order by f.file_name /