Summary of Tablespace

By -
The following script allows us:

  • Used percent, free space
  • Details and properties
  • Segments inside tablespaces
  • Statistics of segments
  • Statistics of IO per datafiles
  • Datafiles of tablespace
  • Quotas
Script:
view        : tbsinfo.sql
download : tbsinfo.sql


Example:

Tablespace Name :DATACCSL02

--- Tablespaces
Tablespace                        Size MB    Free MB    Used MB Used Pct
------------------------------ ---------- ---------- ---------- --------
DATACCSL02                          49158      48256        902 1.83 %
Status    Logging   Extent Man Allocation ASSM   Next Extent KB Contents  Block Size
--------- --------- ---------- ---------- ------ -------------- --------- ----------
ONLINE    LOGGING   LOCAL      UNIFORM    AUTO           131072 PERMANENT       8192

 --- Quotas
User                          Used  Quota
------------------------ ---------- ---------------
UCS                             896 UNLIMITED

--- Datafiles
FILE_NAME                                                           Size MB    Free MB Used Pct
---------------------------------------------------------------- ---------- ---------- --------
/oradata2/IVR/DATACCSL02_01.dbf                                        8193       8064 2 %
/oradata3/IVR/DATACCSL02_04.dbf                                        8193       8192 0 %
/oradata3/IVR/DATACCSL02_06.dbf                                        8193       8064 2 %
/oradata6/IVR/DATACCSL02_03.dbf                                        8193       7680 6 %
/oradata_01/IVR/DATACCSL02_02.dbf                                      8193       8192 0 %
/oradata_01/IVR/DATACCSL02_05.dbf                                      8193       8064 2 %

6 rows selected.


--- Segments
Owner        Segment Name                 Partition Name           Segment Type        Size MB
------------ ---------------------------- ------------------------ ---------------- ----------
PTP          THIAT_CONCILIACION_AA_1                               TABLE                   128
PTP          THIAT_CONCILIACION_AA_2                               TABLE                   128
PTP          THIAT_CONCILIACION_AA_3                               TABLE                   128
PTP          THAST_CONCILIACION_AA_4                               TABLE                   128
PTP          T_AVANCESSMS_POST                                     TABLE                   384

5 rows selected.

--- Statistics
Statistic Name                                                        Value
---------------------------------------------------------------- ----------
logical reads                                                        154144
db block changes                                                     140960
physical writes                                                        6411
physical reads                                                         2976
ITL waits                                                               249
physical writes direct                                                    0
row lock waits                                                            0
physical reads direct                                                     0
global cache cr blocks served                                             0
global cache current blocks served                                        0
buffer busy waits                                                         0

 --- Monthly Summary (Tables and Indexes)
             Number of    Number of
Year-Month     Tables     Indexes
------------ ---------    ---------
2013-10              1            0
2014-02              4            0
             ---------    ---------
                     5            0


Regards,



Comments

Post a Comment