Pages

Thursday, June 23, 2022

How to increase a datafile or adding extra space to tablespace in oracle database?

How to increase a datafile or adding extra space to tablespace in oracle database?


show parameter db_create_file_dest;
select file_name from dba_data_files;
select count(*) from dba_data_files;
select file_name, bytes/1024/1024 from dba_data_files where file_name like '%&file%';
select file_name, status from dba_data_files ;
select file_name,file_id,autoextensible,bytes/1024/1024/1024,status,maxbytes/1024/1024/1024 from dba_data_files where tablespace_name='&TABLESPACE_NAME';

alter tablespace help_data add datafile '/u04/oradata/qr10.dbf' size 3000m autoextend off;
alter database datafile '/u02/dbdata/qprod/heqa_01.dbf' resize 5g;

alter tablespace nfhprod_biplatform add datafile '+data' size 16m autoextend on maxsize 31g;
alter tablespace kuls_img_tbs add datafile '/u02/oracle/datafiles/nfhprod/kuls_img_tbs_03' size 1g autoextend on maxsize 20g;

To enable datafile autoextend and set the Maxsize
SQL> alter database datafile 'datafile_name' autoextend on maxsize 120 g;

To Resize Datafiles
SQL> alter database datafile 'datafile_name' resize 20G;

No comments:

Post a Comment