Tablespace Utilization Script

How to check oracle tablespace report 
1. Check the database details.
2. Check the tablespace Utilization.
3. Check the details of the datafiles for a particular TableSpace which needs attention.
4. Resize or Add the datafiles as per the standards of the existing datafiles on the database.

1. Check the database details.
$ sqlplus "/as sysdba"

set pages 9999 lines 300
col OPEN_MODE for a10
col HOST_NAME for a30
select name DB_NAME,HOST_NAME,DATABASE_ROLE from v$database,v$instance;

2. Check the tablespace Utilization.
Tablespace Utilization Script including AUTOEXTEND (IN GB)
----------------------------------------------------------
$ sqlplus "/as sysdba"

set pages 50000 lines 32767
col tablespace_name format a30
col TABLESPACE_NAME heading "Tablespace|Name"
col Allocated_size heading "Allocated|Size(GB)" form 99999999.99
col Current_size heading "Current|Size(GB)" form 99999999.99
col Used_size heading "Used|Size(GB)" form 99999999.99
col Available_size heading "Available|Size(GB)" form 99999999.99
col Pct_used heading "%Used (vs)|(Allocated)" form 99999999.99

select a.tablespace_name
        ,a.alloc_size/1024/1024/1024 Allocated_size
        ,a.cur_size/1024/1024/1024 Current_Size
        ,(u.used+a.file_count*65536)/1024/1024/1024 Used_size
        ,(a.alloc_size-(u.used+a.file_count*65536))/1024/1024/1024 Available_size
        ,((u.used+a.file_count*65536)*100)/a.alloc_size Pct_used
from     dba_tablespaces t
        ,(select t1.tablespace_name
        ,nvl(sum(s.bytes),0) used
        from  dba_segments s
        ,dba_tablespaces t1
         where t1.tablespace_name=s.tablespace_name(+)
         group by t1.tablespace_name) u
        ,(select d.tablespace_name
        ,sum(greatest(d.bytes,nvl(d.maxbytes,0))) alloc_size
        ,sum(d.bytes) cur_size
        ,count(*) file_count
        from dba_data_files d
        group by d.tablespace_name) a
where t.tablespace_name=u.tablespace_name
and t.tablespace_name=a.tablespace_name
order by t.tablespace_name
/

Tablespace Utilization Script (including AUTOEXTEND) for generating report of more than 80 % used tablespaces (IN GB)
---------------------------------------------------------------------------------------------------------------------
set pages 50000 lines 32767
col tablespace_name format a30
col TABLESPACE_NAME heading "Tablespace|Name"
col Allocated_size heading "Allocated|Size(GB)" form 99999999.99
col Current_size heading "Current|Size(GB)" form 99999999.99
col Used_size heading "Used|Size(GB)" form 99999999.99
col Available_size heading "Available|Size(GB)" form 99999999.99
col Pct_used heading "%Used (vs)|(Allocated)" form 99999999.99

select a.tablespace_name
        ,a.alloc_size/1024/1024/1024 Allocated_size
        ,a.cur_size/1024/1024/1024 Current_Size
        ,(u.used+a.file_count*65536)/1024/1024/1024 Used_size
        ,(a.alloc_size-(u.used+a.file_count*65536))/1024/1024/1024 Available_size
        ,((u.used+a.file_count*65536)*100)/a.alloc_size Pct_used
from     dba_tablespaces t
        ,(select t1.tablespace_name
        ,nvl(sum(s.bytes),0) used
        from  dba_segments s
        ,dba_tablespaces t1
         where t1.tablespace_name=s.tablespace_name(+)
         group by t1.tablespace_name) u
        ,(select d.tablespace_name
        ,sum(greatest(d.bytes,nvl(d.maxbytes,0))) alloc_size
        ,sum(d.bytes) cur_size
        ,count(*) file_count
        from dba_data_files d
        group by d.tablespace_name) a
where t.tablespace_name=u.tablespace_name
and ((u.used+a.file_count*65536)*100)/a.alloc_size>80
and t.tablespace_name=a.tablespace_name
order by t.tablespace_name
/

3. Check the details of the datafiles for a particular TableSpace which needs attention.
Datafiles of a particular TableSpace:
------------------------------------
set pages 50000 lines 32767
col tablespace_name for a30
col CREATION_TIME for a15
col file_name for a70
select dd.tablespace_name TABLESPACE_NAME,dd.file_name,dd.bytes/1024/1024 Size_MB,dd.autoextensible,dd.maxbytes/1024/1024 MAXSIZE_MB,df.CREATION_TIME
from dba_data_files dd, v$datafile df where df.name=dd.file_name and tablespace_name='&TABLESPACENAME' order by 1,2,6;

Note:- If required, can get the DDL of a tablespace as below.

TABLESPACE DDL
--------------
set pagesize 0
SET LONG 9999999
select dbms_metadata.get_ddl('TABLESPACE','&TABLESPACE_NAME') FROM DUAL;

How to find OSDBA OSASM OSOPER group

How to find OSDBA or OSOPER or OSASM Group

What is OSDBA group

OSDBA or OSASM or OSOPER refers to the operating system groups,which are being assigned with the database privileges. This mapping takes place during the time of the installation.

# The DBA_GROUP is the OS group which is to be granted OSDBA privileges.
# The OPER_GROUP is the OS group which is to be granted OSOPER privileges.
# The OSASM_GROUP is the OS group which is to be granted OSASM privileges.

An important point to be noted is that, only the members or OSDBA group can connect to the database instance with SYSDBA a privilege using operating system authentication

Finding OSDBA group

Most of the time we can easily find out the information from group name of the owner of the Oracle Binaries. But there are chances that the ownership is changed in later point of time mostly accidental. So if we wanted to find out the values set up initially that is during the time of the installation the best method is to look into the configuration file created during the time of installation

#1 go to your $ORACLE_HOME/rdbms/lib
#2 grep for “define SS_” in config.c file

Eg :

[oracle@localhost]$ cd $ORACLE_HOME/rdbms/lib

 [oracle@wysheid12crac60 lib]$ grep “define SS_” config.c

#define SS_DBA_GRP “dba”
#define SS_OPER_GRP “dba”
#define SS_ASM_GRP “”
#define SS_BKP_GRP “dba”
#define SS_DGD_GRP “dba”
#define SS_KMT_GRP “dba”

[oracle@localhost ]$

 In this example we can see that DBA group is assigned with the OSDBA privilege

Alternatively you can find out this information from your installation log files if it is available.In most cases the installation log files will be available in the log directory in Oracle inventory location.

Hope this helps.

Whats new in Exadata X8 Server 19.2 Exadata Database Machine?

This blog post quickly scan through the new features of Exadata Database Machine in 19.2 and also hardware capacity and change in Exadata X8 server.

• Exadata Database Machine Software 19.2.0, supports Exadata X8-2 and X8-8 hardware
• Changes in IORM's flashcachesize and Disk I/O limits attributes
• To control the cost of Exadata storage, X8 introduced a new configuration, Exadata Storage Extended (XT)
• The XT model comes with 14TB hard drives with HCC compression capacity
• The XT model doesn't have flash drive
• The lower cost storage option comes with one CPU, less memory and without the core feature of SQL offloading
• Exadata X8 server has the below hardware capacity per rack:
• Limit of 912 CPU core and 28 TB memory
• 2-19 database servers
• 3-18 cell storage
• 920 TB of RAW flash capacity
• 3 PB of RAW disk capacity

References:

http://jaffardba.blogspot.com/2019/04/whats-in-exadata-x8-server-192-exadata.html

https://www.oracle.com/a/ocom/docs/engineered-systems/exadata/exadata-x8-2-ds.pdf

https://docs.oracle.com/en/engineered-systems/exadata-database-machine/dbmso/whats-new-oracle-exadata-database-machine-19.2.html#GUID-B2A6BEAD-873A-4524-82EF-CDF53098820B