| Tablespace size growth [message #72804] |
Mon, 27 October 2003 20:22  |
NG
Messages: 18
Registered: March 2003
|
Junior Member |
|
|
Product Oracle 8i on NT platform.
Recently I have done a healthcheck and checking on the tablespace size which shown below...
T1TBS, T1TEMP are the user defined tablespace and is used for business application. I notice that volumn remaining space for T1TBS is .05MB. Do I have to increase the size manually or what should I do in order to prevent any corruption of data happening here. This information is taken from running the following queries..
SELECT RPAD(t.name,18,' ') tablespace ,
LPAD(TO_CHAR(CEIL( (SUM(s.blocks)*COUNT(*)) / (SUM(f.blocks) *
POWER(COUNT(DISTINCT(f.file#)),2)) * 100 )),3) Pct ,
LPAD(TO_CHAR(TRUNC(SUM(f.blocks) * t.blocksize *
COUNT(DISTINCT(f.file#)) /
( COUNT(*) * 1024 * 1024 * 1024 ),2)),6) vol_G ,
LPAD(TO_CHAR(SUM(f.blocks) * t.blocksize * COUNT(DISTINCT(f.file#)) /
( COUNT(*) * 1024 * 1024 )),8) volume_M ,
TRUNC(SUM(s.blocks) * t.blocksize /
( 1024 * 1024 * COUNT(DISTINCT(f.file#))),2) taken_M ,
TRUNC( ( SUM(f.blocks) * t.blocksize * COUNT(DISTINCT(f.file#))
/ ( COUNT(*) * 1024 * 1024 ) )
- ( NVL(suM(s.blocks),0) * t.blocksize
/ ( 1024 * 1024 * COUNT(DISTINCT(f.file#)) ) ),2) remain_M
FROM sys.seg$ s, sys.ts$ t, sys.file$ f
WHERE s.ts# (+) = t.ts#
AND f.ts# = t.ts#
AND f.status$ = 2
GROUP BY t.name, t.blocksize
ORDER BY 1;
TABLESPACE PCT VOL_G VOLUME_M TAKEN_M REMAIN_M
------------ --- ------ -------- ---------- ----------
DRSYS 20 .01 20 3.81 16.18
INDX 18 .09 100 17.62 82.37
RBS 47 .24 250 116 134
SYSTEM 63 .39 400 249.71 150.28
TEMP 13 0 10 1.25 8.75
T1TBS 100 8.36 8561.964 8561.9 .05
T1TEMP 13 .03 40 5 35
TOOLS 0 10 10
USERS 0 10 10
Your help is much appreciated...
TIA
|
|
|
|
|
|
| Re: Tablespace size growth [message #72812 is a reply to message #72811] |
Wed, 29 October 2003 19:17  |
NG
Messages: 18
Registered: March 2003
|
Junior Member |
|
|
do i need to add/resize datafile if the AUTOEXTENSIBLE
option is enable? i am consent on the remaining size of 0.5M... Any help is much appreciated
TIA
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
