Friday 16 December 2016

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,

17 comments:

  1. Appreciation to my father who informed me on the topic of this web site, this webpage
    is truly remarkable.

    ReplyDelete

  2. Dear User,

    Thank you very much for your comment and appreciation.

    Regards,
    ora-data Team

    ReplyDelete
  3. Hello, I enjoy reading all of your article.
    I like to write a little comment to support you.

    ReplyDelete
    Replies

    1. Dear User,

      Thank you very much.
      It really support us and feel good.

      Regards,
      ora-data Team

      Delete
  4. I enjoy your writing style genuinely enjoying this web site.

    ReplyDelete
    Replies

    1. Dear User,

      Thanks a lot!!!

      Regards,
      ora-data Team

      Delete
  5. Oh my goodness! Impressive article dude! Thank you so
    much, However I am having difficulties with your RSS.
    I don?t understand why I cannot join it. Is there anyone else getting the same RSS issues?
    Anybody who knows the answer can you kindly respond?
    Thanks!!

    ReplyDelete
  6. Great article. Awesome information

    ReplyDelete
    Replies

    1. Dear JS,

      Thanks a lot for your valuable feedback.

      Regards,
      ora-data Team

      Delete
  7. Advertising and marketing has completely transformed to among the most demanding and also essential elements of must operate a
    business. This eventually implies that the general success of your business is actually mosting likely
    to depend mostly on the effectiveness of your ad campaign.

    ReplyDelete
  8. What's up, aall the time i used to check weblog posts here early in the
    morning, since i ehjoy tto learn moe and more.

    ReplyDelete
    Replies

    1. Dear User,

      Thanks a lots for your good words.

      Regards,
      ora-data Team

      Delete
  9. Excellant article helpmed me a lot

    ReplyDelete
    Replies

    1. Dear User,

      Thank you for your feedback and nice to hear it.

      Regards,
      ora-data Team

      Delete
  10. Thanks. This post helped me a lot.

    ReplyDelete

Thank you for your comments and suggestions. It's good to talk you.