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 Views49346
System    
BCHR 
CMS_TS (100MB) 
DB (XE) (5GB) 
PGA (256MB) 
Daily insights of a database application developer    
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.

Bookmarks revisited [2008.01.05] Bookmark (1.0.0.0)

Just a minor change on the [bookmark] section of my blog, instead of IDs I'll provide unique names, with an optional version tag later on. So instead of ::::P3_ID:<ID> the link changed to P3_NAME:<NAME>. This required of course some (minor) changes on the APEX page and on the pipelined function.

1 2 3 
Entries rendered in: 0.33 s




nobody