Add datafile to the tablespace

on
Sometimes  size of tablespace reaches to a point where we need add new data file to the tablepsace.

 Use the below query to find the current datafiles state: 
 set pages 999 
set lines 400 
col file_name format a75 
SELECT d.tablespace_name, v.file#,
       d.file_name, 
       d.bytes / 1024 / 1024                           SIZE_MB, 
       d.autoextensible, 
       d.maxbytes / 1024 / 1024                        MAXSIZE_MB, 
       d.increment_by * ( v.block_size / 1024 ) / 1024 INCREMENT_BY_MB 
FROM   dba_data_files d, 
       v$datafile v 
WHERE  d.file_id = v.file# 
AND d.tablespace_name like '%&tablespace_name%'
ORDER  BY  v.file#;


Adding datafile :
Use the below query to add new data file to the tablespace.
ALTER TABLESPACE apps_ts ADD DATAFILE '/u01/oracle/oradata/new_data_file_02.dbf'
SIZE 100m;
In case if you wanted to add the tablespace name and datafile name in run time, please use the below query
ALTER TABLESPACE &tablespace_name ADD DATAFILE '&datafile_name_with_location'
SIZE &SIZE_IN_MB m;
Datafile with autoextend on:
Use the below query to add new data file with auto-extend on.

 ALTER TABLESPACE apps_ts ADD DATAFILE '/u01/oracle/oradata/new_data_file_02.dbf' 
SIZE 100m AUTOEXTEND ON NEXT 100M MAXSIZE 1024M;
In case if you wanted to add the tablespace name and datafile name in run time, please use the below query
ALTER TABLESPACE &tablespace_name ADD DATAFILE '&datafile_name_with_location'
SIZE &SIZE_IN_MB m AUTOEXTEND ON NEXT &NEXT_SIZE_IN_MB M MAXSIZE &MAX_SIZE_IN_MB M;

Comments

Post a Comment