 |
 |
 |
|
|
 |
 |
 |
|
 |
 |
 |
|
|
| Firefox | 1241 | | IExplorer | 1597 | | Linux | 108 | | Windows | 2888 | | Total Views | 49314 | |
|
| | BCHR |
| | CMS_TS (100MB) |
| | DB (XE) (5GB) |
| | PGA (256MB) |
|
|
|
| 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. | | |
Entries rendered in: 0.28 s |
|
|
 |
 |
 |
|
 |
 |
 |
|
|
 |
 |
 |