MainBlogContactRSS




Login    
Login
 
Recent Entries    
Next Projects
INITCAP this!
APEX 3.1 released ...
Exploiting the strange behavio ...
Where's my SQL?
Undocumented Packages for Back ...
Debugging an APEX application
Splitting strings
Where's the space gone?
The size of a (XE) database
1 2 3 4 5 6 
Search    
Search
 
Recent Articles    
Introduction to Regular Expres ...
Extracting SQL statements from ...
BBCode Parser
MODEL clause vs. old school SQ ...
IF NULL or how to match two va ...
Views    
Firefox1241
IExplorer1597
Linux108
Windows2888
Total Views49340
System    
BCHR 
CMS_TS (100MB) 
DB (XE) (5GB) 
PGA (256MB) 
Daily insights of a database application developer    
Where's the space gone? [2008.01.07] Bookmark (1.0.0.0)

After checking the current size of my XE instance in my yesterday's blog I decided to take a closer look at SYSTEM and SYSAUX tablespace:

SELECT tablespace_name
     , SUM(bytes / 1024 / 1024) size_mb
  FROM dba_segments
 WHERE tablespace_name IN ('SYSAUX', 'SYSTEM')
 GROUP BY tablespace_name
;

TABLESPACE_NAME                   SIZE_MB
------------------------------ ----------
SYSAUX                           764,4375
SYSTEM                           434,6875


I was curious about the top 10 objects' size for those 2 tablespaces, here's SYSTEM:

SELECT segment_name 
     , segment_type
     , bytes / 1024 / 1024 size_mb
  FROM dba_segments
 WHERE tablespace_name = 'SYSTEM'
 ORDER BY bytes DESC
;

SEGMENT_NAME                   SEGMENT_TYPE          SIZE_MB
------------------------------ ------------------ ----------
SOURCE$                        TABLE                     143
IDL_UB1$                       TABLE                      42
IDL_UB2$                       TABLE                      15
C_OBJ#_INTCOL#                 CLUSTER                    11
C_TOID_VERSION#                CLUSTER                    10
PK_C0                          INDEX                      10
PK_O                           INDEX                      10
PK_CT                          INDEX                      10
PK_CS                          INDEX                      10
ARGUMENT$                      TABLE                       7


and SYSAUX:

SELECT segment_name 
     , segment_type
     , bytes / 1024 / 1024 size_mb
  FROM dba_segments
 WHERE tablespace_name = 'SYSAUX'
 ORDER BY bytes DESC
;

SEGMENT_NAME                   SEGMENT_TYPE          SIZE_MB
------------------------------ ------------------ ----------
SYS_LOB0000010794C00025$$      LOBSEGMENT                 58
WWV_FLOW_PAGE_PLUGS            TABLE                      30
WWV_FLOW_STEP_ITEMS            TABLE                      27
I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST INDEX                      22
WRI$_OPTSTAT_HISTGRM_HISTORY   TABLE                      14
WWV_FLOW_REGION_REPORT_COLUMN  TABLE                      12
SYS_LOB0000012406C00009$$      LOBSEGMENT                 12
WWV_FLOW_STEP_PROCESSING       TABLE                      11
WWV_FLOW_PAGE_PLUGS            TABLE                      11
SYS_LOB0000012186C00017$$      LOBSEGMENT                 11


Looks like APEX is taking its share of the system resources.

The size of a (XE) database [2008.01.06] Bookmark (1.0.0.0)

After reading in the OTN thread Does tha UNDO Ts count on Database Storage Limit ? I tested my own instance using Paul M.s SQL statement:

SELECT total
     , allocated
     , allocated / total * 100 percent_free  
  FROM (SELECT 5120 total
             , SUM(bytes) / 1024 / 1024 allocated  
          FROM dba_data_files  
         WHERE tablespace_name != 'UNDO'
       )
;

     TOTAL  ALLOCATED PERCENT_FREE
---------- ---------- ------------
      5120       1510   29,4921875


Interesting - and a bit surprising. 30% already gone? Taking a closer look, I added the SELECT from my Dynamic (performance) views and reports entry.

SELECT total
     , allocated
     , allocated / total * 100 percent_used
  FROM (SELECT 5120 total
             , SUM(allocated) / 1024 / 1024 allocated
          FROM dba_data_files df
             , (SELECT tablespace_name
                     , SUM(bytes) allocated
                  FROM dba_segments
                 GROUP BY tablespace_name
               ) sg
         WHERE df.tablespace_name  = sg.tablespace_name
           AND df.tablespace_name <> 'UNDO'
       )
;
     TOTAL  ALLOCATED PERCENT_USED
---------- ---------- ------------
      5120  1230,6875   24,0368652


BTW. The major share is taken by the system and sysaux tablespace.

Update I added that SELECT to my System section.

1 2 3 
Entries rendered in: 0.30 s




nobody