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

Les tables temporaires dans mysql

Option Explicit
Public Sub Nettoie()
'd'après LL par <!-- e --><a href="mailto:GeeDee@m6net.fr">GeeDee@m6net.fr</a><!-- e -->
Dim Sht As Worksheet, _
    DCell As Range, plage As Range, _
    Calc As Long, _
    Rien As String, _
    Avant As Double

    On Error Resume Next
    Calc = Application.Calculation    ' ---- mémorisation de l'état de recalcul
   '------------------------------------------------------------
   MsgBox "Pour le classeur actif : " _
         & Chr(10) & ActiveWorkbook.FullName _
         & Chr(10) & "dans chaque feuille de calcul" _
         & Chr(10) & "recherche la zone contenant des données," _
         & Chr(10) & "réinitialise la dernière cellule utilisée" _
         & Chr(10) & "et optimise la taille du fichier Excel", _
           vbInformation
    '-------------------------------------------------------------
   MsgBox "Taille initiale de ce classeur en octets" _
         & Chr(10) & FileLen(ActiveWorkbook.FullName), _
           vbInformation, ActiveWorkbook.FullName
    '------------------------------------------------------------
   With Application
        .Calculation = xlCalculationManual
        .StatusBar = "Nettoyage en cours..."
        .EnableCancelKey = xlErrorHandler
        .ScreenUpdating = True
    End With
    '-------------------- le traitement
   For Each Sht In Worksheets
        Avant = Sht.UsedRange.Cells.Count
        Application.StatusBar = Sht.Name & "-" & Sht.UsedRange.Address
        '-------------------Traitement de la zone trouvée
       If Sht.UsedRange.Address <> "$A$1" Or Not IsEmpty(Sht.[A1]) Then
            Set DCell = Sht.Cells.Find("*", , , , xlByRows, xlPrevious)(2)
            '----------------Suppression des lignes inutilisées
           If Not DCell Is Nothing Then
                Sht.Range(DCell, Sht.Cells([A:A].Count, 1)).EntireRow.Delete
                Set DCell = Nothing
                Set DCell = Sht.Cells.Find("*", , , , xlByColumns, xlPrevious)(, 2)
                '----------------Suppression des colonnes inutilisées
               If Not DCell Is Nothing Then Sht.Range(DCell, Sht.[IV1]).EntireColumn.Delete
            End If
            Rien = Sht.UsedRange.Address
        End If
        ActiveWorkbook.Save
        '---------------------Message pour la feuille traitée
       MsgBox "Nom de la feuille de calcul :" _
             & Chr(10) & Sht.Name _
             & Chr(10) & Format(Sht.UsedRange.Cells.Count / Avant, "0.00%") _
             & " de la taille initiale", vbInformation, ActiveWorkbook.FullName
    Next Sht
    On Error GoTo 0
    '--------------------Message fin de traitement
   MsgBox ActiveWorkbook.FullName & Chr(10) & "Taille optimisée de ce classeur en octets " & _
           Chr(10) & FileLen(ActiveWorkbook.FullName), vbInformation
    '--------------------
   Application.StatusBar = False
    Application.Calculation = Calc
End Sub