How to resize the datafile in Primary

Resize the datafile help us when there is not much space is available in the OS level.

1.Check the datafiles under the tablespace.

SQL> SELECT FILE_NAME,TABLESPACE_NAME,BYTES/1024/1024/1024,AUTOEXTENSIBLE,MAXBYTES/1024/1024/1024 MAXSIZE FROM DBA_DATA_FILES WHERE TABLESPACE_NAME='PROD_DATA';

FILE_NAME                                        TABLESPACE_NAME                BYTES/1024/1024/1024 AUT    MAXSIZE

------------------------------------------------ ------------------------------ -------------------- --- ----------

+DATA/PROD/DATAFILE/prod_data2.256.894385677   PROD_DATA                                365 YES        374

Here only 9gb free space left. 

2. Check is there any provisioned disks are there to add. 

SQL> col path for a35

Select path,disk_number,name,mount_status,header_status,os_mb/1024,total_mb/1024,free_mb/1024,FAILGROUP from v$asm_disk where header_status<>'MEMBER' ;

no rows selected

3. Now identify the HWM for the datafile for how much space to we can resize. The below script will prompt for the datafile.

set linesize 400

col tablespace_name format a20

col file_size format 9999999

col file_name format a66

col hwm format 9999999

col can_save format 99999

SELECT tablespace_name, file_name, file_size, hwm, file_size-hwm can_save FROM (SELECT /*+ RULE */ ddf.tablespace_name, ddf.file_name file_name,

ddf.bytes/1048576 file_size,(ebf.maximum + de.blocks-1)*dbs.db_block_size/1048576 hwm 

FROM dba_data_files ddf,(SELECT file_id, MAX(block_id) maximum FROM dba_extents GROUP BY file_id) ebf,dba_extents de,

(SELECT value db_block_size FROM v$parameter WHERE name='db_block_size') dbs WHERE ddf.file_id = ebf.file_id 

AND de.file_id = ebf.file_id AND ddf.file_name='&Datafile_Name' and de.block_id = ebf.maximum ORDER BY 1,2); 

 Enter value for Datafile: +DATA/PROD/DATAFILE/prod_data2.256.894385677 

TABLESPACE_NAME      FILE_NAME                                                           FILE_SIZE        HWM CAN_SAVE

-------------------- ------------------------------------------------------------------ ---------- ---------- --------

PROD_DATA     +DATA/PROD/DATAFILE/prod_data2.256.894385677         373760     361254    12506

Based on the above output we came to know that we can save upto 12506mb. 

4. Resize the datafile

SQL> alter database datafile '+DATA/PROD/DATAFILE/prod_data2.256.894385677' resize 361255m;

Database altered.

Now check the utilization.

SQL> SELECT FILE_NAME,TABLESPACE_NAME,BYTES/1024/1024/1024,AUTOEXTENSIBLE,MAXBYTES/1024/1024/1024 MAXSIZE FROM DBA_DATA_FILES WHERE TABLESPACE_NAME='PROD_DATA';

FILE_NAME                                        TABLESPACE_NAME                BYTES/1024/1024/1024 AUT    MAXSIZE

------------------------------------------------ ------------------------------ -------------------- --- ----------

+DATA/PROD/DATAFILE/prod_data2.256.894385677    PROD_DATA                                352.78   YES        374

If it has standby, the above change will reflect on standby as well. No need to take any action on standby.

Happy Learning :)