Compter les objets Oracle, leur taille et quelques statistiques.
Nécessite d'avoir les catalogues Oracle installés

# Long a exécuter
@${ORACLE_HOME}/rdbms/admin/catalog
# dba_data_files est créé ici (encore plus long)
@${ORACLE_HOME}/rdbms/admin/catproc

Requête traficotée :

SET PAGESIZE 50000
set linesize 300
COLUMN owner FORMAT A10
COLUMN tablespace FORMAT A15
COLUMN nomtable FORMAT A40
COLUMN OBJECT_NAME FORMAT A40
COLUMN OBJECT_TYPE FORMAT A15
COLUMN bytes FORMAT 99999999999999
COLUMN num_rows FORMAT 99999999999999
COLUMN avg_row_len FORMAT 99999999999999

SPOOL ./objectSize.txt

select
DBA_OBJECTS.owner as owner,
max(NVL2(DBA_INDEXES.tablespace_name, DBA_INDEXES.tablespace_name, NVL2(DBA_TABLES.tablespace_name, DBA_TABLES.tablespace_name, DBA_LOBS.tablespace_name))) as tablespace,
NVL2(DBA_INDEXES.TABLE_NAME, DBA_INDEXES.TABLE_NAME, NVL2(DBA_TABLES.TABLE_NAME, DBA_TABLES.TABLE_NAME, DBA_LOBS.TABLE_NAME)) as nomtable,
DBA_OBJECTS.OBJECT_NAME,
DBA_OBJECTS.OBJECT_TYPE,
sum(DBA_SEGMENTS.bytes) as bytes,
max(dba_tab_statistics.num_rows) as num_rows,
max(dba_tab_statistics.avg_row_len) as avg_row_len
from
DBA_OBJECTS
left join DBA_TABLES on (DBA_OBJECTS.OBJECT_NAME=DBA_TABLES.TABLE_NAME and DBA_OBJECTS.OWNER = DBA_TABLES.OWNER)
left join DBA_INDEXES on (DBA_OBJECTS.OBJECT_NAME=DBA_INDEXES.INDEX_NAME and DBA_OBJECTS.OWNER = DBA_INDEXES.OWNER)
left join DBA_LOBS on (DBA_OBJECTS.OBJECT_NAME=DBA_LOBS.SEGMENT_NAME and DBA_OBJECTS.OWNER = DBA_LOBS.OWNER)
left join dba_tab_statistics on (dba_tab_statistics.table_name = DBA_TABLES.TABLE_NAME) and (dba_tab_statistics.partition_name is null) and (DBA_OBJECTS.OWNER = dba_tab_statistics.OWNER)
left join DBA_SEGMENTS on (DBA_SEGMENTS.SEGMENT_NAME=DBA_OBJECTS.OBJECT_NAME and DBA_SEGMENTS.OWNER = DBA_OBJECTS.OWNER)
where
DBA_OBJECTS.OBJECT_NAME not like 'BIN$%'
and
DBA_OBJECTS.OWNER not in ('DBSNMP', 'SYS', 'SYSTEM','OUTLN', 'APPQOSSYS', 'WMSYS')
and
DBA_OBJECTS.OBJECT_TYPE IN ('TABLE', 'INDEX', 'LOBINDEX', 'LOBSEGMENT')
group by
DBA_OBJECTS.owner,
DBA_OBJECTS.OBJECT_NAME,
DBA_OBJECTS.OBJECT_TYPE,
NVL2(DBA_INDEXES.TABLE_NAME, DBA_INDEXES.TABLE_NAME, NVL2(DBA_TABLES.TABLE_NAME, DBA_TABLES.TABLE_NAME, DBA_LOBS.TABLE_NAME))
order by
DBA_OBJECTS.owner,
NVL2(DBA_INDEXES.TABLE_NAME, DBA_INDEXES.TABLE_NAME, NVL2(DBA_TABLES.TABLE_NAME, DBA_TABLES.TABLE_NAME, DBA_LOBS.TABLE_NAME)), DBA_OBJECTS.OBJECT_TYPE
;

Que je mets dans un fichier extractSize.sql et

@./extractSize.sql