pdb quota exceeded

- June 17, 2025

SELECT owner, segment_name, segment_type, bytes FROM dba_segments WHERE tablespace_name = 'your_tablespace_name' ORDER BY bytes DESC;

select tablespace_name,username,bytes/1024/1024,max_bytes/1024/1024 from dba_ts_quotas where username='DBACLASS';

SELECT owner, sum(bytes)/1024/1024/1024 as GB FROM dba_segments GROUP BY owner ORDER BY GB DESC

SELECT owner, sum(bytes)/1024/1024/1024 as GB FROM dba_segments GROUP BY owner ORDER BY GB DESC;

How to determine index size

========================

Size of INDEX

select segment_name,sum(bytes)/1024/1024/1024 as "SIZE in GB" from user_segments where segment_name='INDEX_NAME' group by segment_name;

select owner,segment_name,sum(bytes)/1024/1024/1024 as "SIZE in GB" from dba_segments where owner='SCHEMA_NAME' and segment_name='INDEX_NAME' group by owner,segment_name;

List of Size of all INDEXES of a USER

===============================

select segment_name,sum(bytes)/1024/1024/1024 as "SIZE in GB" from user_segments where segment_type='INDEX' group by segment_name order by "SIZE in GB" desc;

select owner,segment_name,sum(bytes)/1024/1024/1024 as "SIZE in GB" from dba_segments where owner='SCHEMA_NAME' and segment_type='INDEX' group by owner,segment_name order by "SIZE in GB" desc;

Sum of sizes of all indexes

=======================

select owner,sum(bytes)/1024/1024/1024 as "SIZE in GB" from dba_segments where owner='SCHEMA_NAME' and segment_type='INDEX' group by owner;

Database used size and free size
=======================

select

"Reserved_Space(GB)", "Reserved_Space(GB)" - "Free_Space(GB)" "Used_Space(GB)",

"Free_Space(GB)"

from(

select

(select sum(bytes/(1014*1024*1024)) from dba_data_files) "Reserved_Space(GB)",

(select sum(bytes/(1024*1024*1024)) from dba_free_space) "Free_Space(GB)"

from dual

);

https://sqlmaria.com/2023/11/28/how-to-identify-which-indexes-can-be-safely-dropped-in-oracle/

How to determine index size

Size of INDEX

select segment_name,sum(bytes)/1024/1024/1024 as "SIZE in GB" from user_segments where segment_name='INDEX_NAME' group by segment_name;

select owner,segment_name,sum(bytes)/1024/1024/1024 as "SIZE in GB" from dba_segments where owner='SCHEMA_NAME' and segment_name='INDEX_NAME' group by owner,segment_name;

List of Size of all INDEXES of a USER

select segment_name,sum(bytes)/1024/1024/1024 as "SIZE in GB" from user_segments where segment_type='INDEX' group by segment_name order by "SIZE in GB" desc;

select owner,segment_name,sum(bytes)/1024/1024/1024 as "SIZE in GB" from dba_segments where owner='SCHEMA_NAME' and segment_type='INDEX' group by owner,segment_name order by "SIZE in GB" desc;

Sum of sizes of all indexes

select owner,sum(bytes)/1024/1024/1024 as "SIZE in GB" from dba_segments where owner='SCHEMA_NAME' and segment_type='INDEX' group by owner;

How to determine the Index definition

set long 4000

select dbms_metadata.get_ddl('INDEX','<INDEX Name>','<INDEX OWNER') from dual;

How to determine the Index statistics

set pages 250

set linesize 100

set verify off

col table_name format a24 heading 'TABLE NAME'

col index_name format a23 heading 'INDEX NAME'

col u format a1 heading 'U'

col blevel format 0 heading 'BL'

col leaf_blocks format 999990 heading 'LEAF|BLOCKS'

col distinct_keys format 9999990 heading 'DISTINCT|KEYS'

col avg_leaf_blocks_per_key format 9999990 heading 'LEAF|BLKS|/KEY'

col avg_data_blocks_per_key format 9999990 heading 'DATA|BLKS|/KEY'

rem

break on table_name

rem

select table_name, index_name,

decode( uniqueness, 'UNIQUE', 'U', null ) u,

blevel, leaf_blocks, distinct_keys,

avg_leaf_blocks_per_key, avg_data_blocks_per_key

from sys.dba_indexes

where table_owner like upper('&owner')

and table_name like upper('&table')

order by table_owner, table_name, index_name;

How to determine the Index statistics
================================