Oracle tablespace size freespace, datafiles and disk group details query

Hi DBA-Mates,

Recently, we have done this in our real time. As we know, for every one working on ASM is little bit hard and different. So, when my junior asked about this how to find out the details in ASM, I thought this may be happens with many of us. Then why should not provide the details. So, let’s see. 

a. Tablespace details.

b. Diskgroup details. 
c. Datafile details. 

1. Login to Server.

2. Run the .dbenv file. It may be bydefault also. If .env file is not bydefault, please run the .env file. 
a. $ ls *env
ASM.env     db.env

b. $ . ./db.env 

3. Connect as a either sysdba or db user. Here, we are connecting as sysdba user.

$slqplus ‘/as sysdba’ 

Check the instance details: 
SQL>  select INST_ID,INSTANCE_NAME,HOST_NAME,status from gv$instance;

   INST_ID INSTANCE_NAME    HOST_NAME                                                        STATUS

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

         1 SKU            ora-data                                                         OPEN

         2 SKU             ora-data                                                       OPEN

SQL>

Check the Tablespace details: 

SQL> select    tablespace_name,  used_percent from   dba_tablespace_usage_metrics where tablespace_name='ABCD';

TABLESPACE_NAME                USED_PERCENT

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

ABCD                        80

Check the Tablespace details with different command, above command may not work: 
SQL>select round((bytes/1024)/1024,0) "Used Space(MB)",

round(total,0) "Allocated size(MB)",

round(max,0) "Maximum allowable(MB)",

round(max-(BYTES/1024)/1024,0) "Effective free(MB)",

round(((max-(BYTES/1024)/1024)/max)*100,2) "FREE(%)"

from SYS.SM$TS_USED,

(select sum((BYTES/1024)/1024) total, sum((decode(MAXBYTES,0,bytes,maxbytes)/1024)/1024) max

from dba_data_files where tablespace_name='&1') where tablespace_name='&1'; 

It will prompt or ask for Tablespace name.

old   8: from dba_data_files where tablespace_name='&1') where tablespace_name='&1'

new   8: from dba_data_files where tablespace_name='ABCD') where tablespace_name='ABCD'

Output as shown below:

Used Space(MB) Allocated size(MB) Maximum allowable(MB) Effective free(MB)    FREE(%)

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

        123456            12345                 1234              1234      123

SQL>

Check the datafiles details: 

SQL> select file_name||'  '|| bytes/1024/1024||'  '|| autoextensible||'  '|| maxbytes/1024/1024 from dba_data_files where tablespace_name=upper('ABCD');

FILE_NAME||''||BYTES/1024/1024||''||AUTOEXTENSIBLE||''||MAXBYTES/1024/1024

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

+DiskGroupName/***/datafile/abcd.12.12345678   *****  YES  12345

SQL>

For ASM Case:

Run the ASM env file as shown below:

$ . ./ASM.env

Connect as sysdba user.

$ sqlplus '/as sysdba'

SQL>

SQL> select instance_name from v$instance;

INSTANCE_NAME

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

+ASM

Check the ASM Diskgroup details:

SQL>  select   name,total_mb,usable_file_mb from v$asm_diskgroup where name='DiskGroupName';

NAME                                                           TOTAL_MB      USABLE_FILE_MB

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

DiskGroupName                                             12345           1234

SQL>

SQL>SELECT name, free_mb, total_mb, free_mb/total_mb*100 as percentage, total_mb-free_mb as used_mb FROM gv$asm_diskgroup where name='<DiskgroupName>';

 

Note: We have modified or removed the actual output because it’s directly from real time experiences which we can’t share.

Some more useful link:
Oracle Database 12c Issues and Solution
Dataguard Concepts and Setup
Database control file Concepts and Multiplexing
R12 Patching Steps
Important DBA Scripts 
Hope this will be useful and helpful. If you have suggestions, Please comment or contact us@ora-data.blogspot.com.
Thanks,