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

You May Also Like

Flashback primary database in dataguard environment
ERROR: permission denied for schema in postgres
resumable_timeout parameter in oracle database
ORA-01536: space quota exceeded for tablespace
How to rename Columns in R
How to get the execution plan for a SQL between two AWR snapshots
Exception in thread -main" java.lang.OutOfMemoryError: GC overhead limit exceeded with bsu.sh
Alert log rotation script in oracle
Change dbsnmp password for target db in oem 12c
Apply database proactive bundle patch in RAC using manual process

From This Website

COMPRESSION in datapump oracle
COLS & COLSEXCEPT FILTER in goldengate
OUTBOUND_DBLINK_PROTOCOLS in oracle 12.2
LOG_CHECKPOINTS_TO_ALERT parameter in oracle
sec_case_sensitive_logon parameter in oracle
How to enable active dataguard in physical standby database
Hive installation with mysql database
How to recreate MGMT database in ORACLE 12C GRID
ESTIMATE REQUIRED DISK SPACE FOR EXPORT USING estimate_only
Obey command in goldengate