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

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

You May Also Like

Upgrade grid infrastructure to 19C oracle
Perform Flashback in pluggable database(PDB) in oracle 12.2
How to multiplex control file in standalone database
Apply patch on oracle 12.2 database ( Release update)
ORA-32773: operation not supported for smallfile tablespace
How to move AUD$ table to another tablespace using DBMS_AUDIT_MGMT
How to install OPatch in ORACLE RAC
How to install postgres database on mac os/linux
How to add a tempfile in primary database in dataguard
How to create encrypted tablespace in PDB( oracle 12c)

From This Website

How to find weblogic version in oracle weblogic 12
Shell script to report failed login attempt in oracle
How to install trace file analyzer( TFACTL)
sec_case_sensitive_logon parameter in oracle
SKIP_CONSTRAINT_ERRORS as DATA_OPTION in impdp
Defgen utility in Oracle goldengate
Find user commits per minute in oracle database
Add database as target oracle 12c cloud control
How to find execution history of an sql_id
Important views in For Oracle DBA in daily usage