This script will provide you the alter statement to add a datafile to tablespace.
Copy below script to a file Ex: test1sql
sqlplus / as sysdba
@test.sql
prompt
select name,open_mode from v$database
/
prompt
set pagesize 100
set linesize 132
set feedback off
set verify off
set wrap off
select a.tablespace_name name,
round((sum(b.bytes)/count( distinct a.file_id||'.'||a.block_id ))/1024/1024) Total_MB,
round((sum(b.bytes)/count( distinct a.file_id||'.'||a.block_id ) -
sum(a.bytes)/count( distinct b.file_id ))/1024/1024) Used_MB,
round((sum(a.bytes)/count( distinct b.file_id ))/1024/1024) Free_MB,
round(100 * ( (sum(b.bytes)/count( distinct a.file_id||'.'||a.block_id )) -
(sum(a.bytes)/count( distinct b.file_id ) )) /
(sum(b.bytes)/count( distinct a.file_id||'.'||a.block_id ))) pct_used,
round(100 - round(100 * ( (sum(b.bytes)/count( distinct a.file_id||'.'||a.block_id )) -
(sum(a.bytes)/count( distinct b.file_id ) )) /
(sum(b.bytes)/count( distinct a.file_id||'.'||a.block_id )))) "PCT_FREE(%)"
from sys.dba_free_space a, sys.dba_data_files b
where a.tablespace_name = b.tablespace_name
group by a.tablespace_name, b.tablespace_name
order by pct_used
/
prompt
prompt ********************************************************************************************
prompt
accept ts_name prompt 'Enter Tablespace name: '
prompt
set lines 200
set pages 200
col FILE_NAME for a50
select file_name, bytes/1024/1024 sizem,maxbytes/1024/1024 msizem,autoextensible,increment_by from dba_data_files where tablespace_name='&ts_name' order by file_name
/
prompt
prompt ********************************************************************************************
prompt
accept mount prompt 'Enter moount point name to Check avilable space(Ex: /d05): '
prompt
!df -h &mount
prompt
prompt ###############################################################################################
prompt
prompt ==> Now lets add a datafile...
prompt -------------------------------
prompt
accept ts_name prompt 'Enter Tablespace name: '
prompt
accept new_data_file prompt 'Enter new datafile name(make sure no spaces befor and after of filename) : '
prompt
accept initial_size prompt 'Enter initial size(Ex: 2048M): '
prompt
accept ON_OFF prompt 'Enter AUTOEXTEND option ON or OFF: '
prompt
accept next_size prompt 'Enter next size(Ex: 1024M): '
prompt
accept max_size prompt 'Enter MAX size (Ex: 8192M): '
prompt
prompt ****************************************************************************************************
prompt
prompt ==> verify the below statement before execution...
prompt ---------------------------------------------
prompt
prompt ALTER TABLESPACE &ts_name ADD DATAFILE '&new_data_file' SIZE &initial_size AUTOEXTEND &ON_OFF NEXT &next_size MAXSIZE &max_size
prompt
prompt #####################################################################################################
prompt
prompt
Copy below script to a file Ex: test1sql
sqlplus / as sysdba
@test.sql
prompt
select name,open_mode from v$database
/
prompt
set pagesize 100
set linesize 132
set feedback off
set verify off
set wrap off
select a.tablespace_name name,
round((sum(b.bytes)/count( distinct a.file_id||'.'||a.block_id ))/1024/1024) Total_MB,
round((sum(b.bytes)/count( distinct a.file_id||'.'||a.block_id ) -
sum(a.bytes)/count( distinct b.file_id ))/1024/1024) Used_MB,
round((sum(a.bytes)/count( distinct b.file_id ))/1024/1024) Free_MB,
round(100 * ( (sum(b.bytes)/count( distinct a.file_id||'.'||a.block_id )) -
(sum(a.bytes)/count( distinct b.file_id ) )) /
(sum(b.bytes)/count( distinct a.file_id||'.'||a.block_id ))) pct_used,
round(100 - round(100 * ( (sum(b.bytes)/count( distinct a.file_id||'.'||a.block_id )) -
(sum(a.bytes)/count( distinct b.file_id ) )) /
(sum(b.bytes)/count( distinct a.file_id||'.'||a.block_id )))) "PCT_FREE(%)"
from sys.dba_free_space a, sys.dba_data_files b
where a.tablespace_name = b.tablespace_name
group by a.tablespace_name, b.tablespace_name
order by pct_used
/
prompt
prompt ********************************************************************************************
prompt
accept ts_name prompt 'Enter Tablespace name: '
prompt
set lines 200
set pages 200
col FILE_NAME for a50
select file_name, bytes/1024/1024 sizem,maxbytes/1024/1024 msizem,autoextensible,increment_by from dba_data_files where tablespace_name='&ts_name' order by file_name
/
prompt
prompt ********************************************************************************************
prompt
accept mount prompt 'Enter moount point name to Check avilable space(Ex: /d05): '
prompt
!df -h &mount
prompt
prompt ###############################################################################################
prompt
prompt ==> Now lets add a datafile...
prompt -------------------------------
prompt
accept ts_name prompt 'Enter Tablespace name: '
prompt
accept new_data_file prompt 'Enter new datafile name(make sure no spaces befor and after of filename) : '
prompt
accept initial_size prompt 'Enter initial size(Ex: 2048M): '
prompt
accept ON_OFF prompt 'Enter AUTOEXTEND option ON or OFF: '
prompt
accept next_size prompt 'Enter next size(Ex: 1024M): '
prompt
accept max_size prompt 'Enter MAX size (Ex: 8192M): '
prompt
prompt ****************************************************************************************************
prompt
prompt ==> verify the below statement before execution...
prompt ---------------------------------------------
prompt
prompt ALTER TABLESPACE &ts_name ADD DATAFILE '&new_data_file' SIZE &initial_size AUTOEXTEND &ON_OFF NEXT &next_size MAXSIZE &max_size
prompt
prompt #####################################################################################################
prompt
prompt
No comments:
Post a Comment