• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar
  • Skip to footer navigation

The Geek Diary

  • OS
    • Linux
    • CentOS/RHEL
    • Solaris
    • Oracle Linux
    • VCS
  • Interview Questions
  • Database
    • oracle
    • oracle 12c
    • ASM
    • mysql
    • MariaDB
  • DevOps
    • Docker
    • Shell Scripting
  • Big Data
    • Hadoop
    • Cloudera
    • Hortonworks HDP

Script To Get Tablespace Utilization In Oracle Database 12c

by admin

This is a script to get the tablespace utilization in Oracle Database 12c. You can use this script to get the tablespace utilization ALLOCATED(MB), USED(MB) and Used parentage for all containers tablespaces.

sqlplus -s / as sysdba

set pages 80
set lin 120
set echo off
set feed off
column PCT format 999.99
column tbspce format A30
col container for a30
select substr(f.tablespace_name,1,30) tbspce,
     round(f.tsbytes/(1024*1024),0) "ALLOCATED(MB)",
     round(nvl(s.segbytes,0)/(1024*1024),0) "USED(MB)",
     round((nvl(s.segbytes,0)/f.tsbytes)*100,2) PCT,
     lower(vc.name) as container
from
   (select con_id,tablespace_name,sum(bytes) tsbytes from cdb_data_files group by con_id,tablespace_name) f,
   (select con_id,tablespace_name,sum(bytes) segbytes from cdb_segments group by con_id,tablespace_name) s,
   v$containers vc
where f.con_id=s.con_id(+)
  and f.tablespace_name=s.tablespace_name(+)
  and f.con_id=vc.con_id
order by container, tbspce;

Filed Under: oracle, oracle 12c

Some more articles you might also be interested in …

  1. New Oracle Net features in version 12c
  2. How to get datafile information when PDB is Mounted/Read-Write/Restricted Mode
  3. Whats is PL/SQL
  4. ORA-00257:Archiver Error, Connect Internal Only Until Freed
  5. Patching and Upgrading Databases in Oracle Data Guard Configuration
  6. Undo Modes in 12.2 Multitenant Databases – Local and Shared Modes
  7. Oracle Interview Questions – Flash Recovery Area
  8. How To Setup UDEV Rules For RAC OCR And Voting Devices on Partitions
  9. Beginners Guide to Oracle Database Vault
  10. How To Change A Dictionary Managed Tablespace To A Locally Managed Tablespace

You May Also Like

Primary Sidebar

Recent Posts

  • nixos-rebuild Command Examples in Linux
  • nixos-option: Command Examples in Linux
  • nixos-container : Command Examples in Linux
  • nitrogen Command Examples in Linux

© 2023 · The Geek Diary

  • Archives
  • Contact Us
  • Copyright