Announcement

Collapse
No announcement yet.

[SCRIPT] DB Usage - mw-dbusage.sql

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • [SCRIPT] DB Usage - mw-dbusage.sql

    All,

    Here's a simple script that I use to get the overall and per-tablespace usage from an Oracle DB. I use this when recreating a DB on another system, so that I can pre-allocate the datafiles and tablespaces before export/import. We often do this when replicating a client environment for thorough internal testing or migration assistance.

    You must be logged in as sys or system in order to run and the output looks like:

    Code:
    SQL> @mw-dbusage.sql
    
      TOTAL_GB
    ----------
    41.3867188
    
    
       USED_GB
    ----------
    15.9564209
    
    
    TABLESPACE_NAME                PERCENT_USED TOTAL_MB  USED_MB
    ------------------------------ ------------ -------- --------
    INDX                                     97    5,750    5,570
    DATA                                     99    5,250    5,197
    LOB1                                     95    2,000    1,895
    ARCHIVE                                  15    8,000    1,182
    LOB4                                     93    1,000      925
    SYSTEM                                   99      540      533
    SYSAUX                                   98      290      285
    LOB3                                     21    1,000      205
    LOB2                                      9    1,000       90
    EXAMPLE                                  52      150       78
    LOB5                                      6    1,000       60
    PART_4                                    6    1,000       59
    LOB7                                      5    1,000       50
    LOB6                                      4    1,000       40
    PART_1                                    3    1,000       31
    PART_3                                    3    1,000       30
    PART_2                                    3    1,000       28
    UNDOTBS1                                 13      175       22
    REPORT                                    1    1,000       14
    REPORTINDX                                1    1,000       14
    BPL_DAY4                                  0    1,000        4
    BPL_DAY7                                  0    1,000        4
    BPL_DAY2                                  0    1,000        4
    BPL_DAY5                                  0    1,000        4
    BPL_DAY6                                  0    1,000        4
    BPL_DAY1                                  0    1,000        4
    BPL_DAY3                                  0    1,000        4
    USERS                                    59        5        3
    
    28 rows selected.
    
    SQL>
    Hope this helps!

    --Chris
    Attached Files
    Last edited by chrisplough; May 15, 2007, 18:08. Reason: Added attachment (mw-dbusage.sql.zip)

  • #2
    Re: Script: DB Usage - mw-dbusage.sql

    Can you please attach the query? You seemed to have missed the attachment.

    Comment


    • #3
      Re: Script: DB Usage - mw-dbusage.sql

      Thanks for letting me know - I'm not sure how I missed it. The script has now been attached.

      --Chirs

      Comment

      Working...
      X