Tablespace Administration - 2

For ASM disks details you can use query as below.

column Name format A30
column Group_Number format 9999
column Path format A50
set linesize 150
select Name,Group_Number,Total_MB,Free_MB,PATH, Hot_Used_MB, Cold_Used_MB from v$asm_disk;

The Total Space details can be seen with below query.

select GROUP_NUMBER,SUM(TOTAL_MB) "Total_MB" ,SUM(FREE_MB) "Free_MB", SUM(Hot_Used_MB) "Hot_Used_MB", SUM(Cold_Used_MB) "Cold_Used_MB"
from v$asm_disk group by group_number;

The same results are also available by view v$asm_diskgroup.

column Name format A40
set linesize 150
SELECT NAME, total_mb, free_mb, hot_used_mb, cold_used_mb, usable_file_mb FROM v$asm_diskgroup;

Thus we can check the availble free space at our disk groups beore adding any space or datafile to a tablespace.

There are many more information available at these views, but its out of scope for this particular post.

Now it comes to adding space to tablespace or adding a datafile.
Click here for Tablespace related sql queries
The command is somewhat similar to what we always do for such issue:-
Suppose the diskgroup name is ORCL_DATA for our datafiles then

alter tablespace <TablespaceName> add Datafile '+ORCL_DATA' size 1000M;

Now if we will query the tablespace again, we can find one more datafile starting with +ORCL_DATA/.. of size 1000M.

The files can be resized or altered for some other properties in a similarly way we do for ordinary file system, like..

alter database datafile '<Complete File Name>' resize <newsize>;