This script reports the tablespace quota allocated for each database user and the amount of tablespace consumed by that user.

TABLESPACE QUOTA DETAILS OF ALL THE USERS:

set pagesize 200
set lines 200
col ownr format a20         justify c heading 'Owner' 
col name format a20         justify c heading 'Tablespace' trunc 
col qota format a12         justify c heading 'Quota (KB)' 
col used format 999,999,990 justify c heading 'Used (KB)' 
set colsep '|'
select 
  username          ownr, 
  tablespace_name   name, 
  decode(greatest(max_bytes, -1), 
    -1, 'UNLIMITED', 
    to_char(max_bytes/1024, '999,999,990') 
  )                 qota, 
  bytes/1024        used 
from 
  dba_ts_quotas 
where 
  max_bytes!=0 
    or 
  bytes!=0 
order by 
  1,2 
/ 

TABLESPAE QUOTA DETAILS FOR A PARTICULAR USER:

set pagesize 200
set lines 200
col ownr format a20         justify c heading 'Owner' 
col name format a20         justify c heading 'Tablespace' trunc 
col qota format a12         justify c heading 'Quota (KB)' 
col used format 999,999,990 justify c heading 'Used (KB)' 
set colsep '|'
select 
  username          ownr, 
  tablespace_name   name, 
  decode(greatest(max_bytes, -1), 
    -1, 'UNLIMITED', 
    to_char(max_bytes/1024, '999,999,990') 
  )                 qota, 
  bytes/1024        used 
from 
  dba_ts_quotas 
where 
 ( max_bytes!=0 
    or 
  bytes!=0) and username='&USERNAME'
order by 
  1,2 
/ 

OUTPUT:

Enter value for username: AKHEHIFA
old  14:   bytes!=0) and username='&USERNAME'
new  14:   bytes!=0) and username='AKHEHIFA'

       Owner        |     Tablespace     | Quota (KB) | Used (KB)
--------------------|--------------------|------------|------------
AKHEHIFA            |USERS               |   1,048,576|           0

tablespace DATABASE SCRIPTS


Related Topics

Find sessions consuming lot of CPU
Get sid from ospid
How to find execution history of an sql_id
Script to get cpu usage and wait event information in oracle database
How to get tablespace quota details of an user in oracle
How to find cpu and memory information of oracle database server
Find user commits per minute in oracle database
Find pending distributed pending transactions in oracle
Find the active transactions in oracle database
How to monitor parallel queries in oracle db

You May Also Like

Steps for upgrading oracle database to 19c using DBUA
How to rename Columns in R
Deinstall Management Agents Oracle 12c cloud control
Exception in thread -main" java.lang.OutOfMemoryError: GC overhead limit exceeded with bsu.sh
DBMS_PARALLEL_EXECUTE in oracle PL/SQL
how to send mail using utl_mail in oracle 11g
oraversion utility in oracle 18c - New feature
Add database as target oracle 12c cloud control
how to Print execution time of tasks in ansible
How to drop SQL baselines in oracle

From This Website

ERROR: permission denied for schema in postgres
sec_case_sensitive_logon parameter in oracle
ENABLE_PARALLEL_DML hint in oracle 12c
How to deinstall/cleanup standalone grid infrastructure
Steps To Set Up Master Slave Replication in MySQL
Blackout targets in OEM 12c cloud control
Apply patch on oracle 12.2 database ( Release update)
Deinstall Management Agents Oracle 12c cloud control
Shell script to monitor goldengate process
How to change flash recovery area location