Topic: Database Administration >> Tablespace full problem
|
Disclaimer: The purpose of all dbapool forums including OCP and Other Oracle Certification forums is to help each other with specific issues but not to share dump and copyrighted exam content, materials or intellectual property.
You may review the entire Oracle Certification Program Candidate Agreement online Here.
|
|
|
|
| Title: Tablespace full problem |
| shekar |
Posted: Jun 30, 2009 11:50:44 PM |
Total Post: 1
Joined: Jun, 2009
|
Hi,
my database is oracle 9i
have disk space enough
tablespace extent management : local and auto allocation
segment space management -- auto
block size --8 k
size 1100 MB
used 99.74%
have only one datafile
---------------------------------------
datafile size -- 1100 MB
used 99.74%
file size -- 1100 MB
Automatically extend datafile when full (Autoextend)
increment -- 1 MB
maximum size unlimited
-----------------------------------------
question---->why its always showing 99.74%?
Thanks
Shekar
|
|
|
 kumar |
| Posted: Jul 01, 2009 06:55:16 AM |  |
|
Total Post: 639
Joined: May, 2006
|
This is because the calculation problem. Maxsize never calculated.
It is based on the actual size. Autoextend is not recommend, in terms of performance. Autoextend will be avoided the ora-* errors.
Kindly execute the following sqls.
-- ************* SPACE USAGE **************
--
prompt Space usage (as of &p_currdate.):
prompt
clear breaks
clear computes
clear columns
set heading on
column tspace heading 'Tablespace' justify left format a20 truncated
column tot_ts_size heading 'Size|in MB.' justify left format 9,99999999990
column free_ts_size heading 'Used|in MB.' justify right format 9,9999999990
column tbusedpct heading '' justify left format a6
column tbfreepct heading '' justify left format a6
break on report
compute sum label 'Totals:' of tot_ts_size free_ts_size on report
col tspace form a25 Heading "Tablespace"
col tot_ts_size form 99999999999999 Heading "Size (Mb)"
col free_ts_size form 99999999999999 Heading "Free (Mb)"
col ts_pct form 999 Heading "% Free"
col ts_pct1 form 999 Heading "% Used"
SELECT df.tablespace_name tspace,
df.bytes/(1024*1024) tot_ts_size,
sum(fs.bytes)/(1024*1024) free_ts_size,
round(sum(fs.bytes)*100/df.bytes) ts_pct,
round((df.bytes-sum(fs.bytes))*100/df.bytes) ts_pct1
FROM dba_free_space fs,
(select tablespace_name, sum(bytes) bytes from dba_data_files group by tablespace_name ) df
WHERE fs.tablespace_name = df.tablespace_name
GROUP BY df.tablespace_name, df.bytes;
|
|
|
|
|
| Gitesh |
| Posted: Jul 11, 2009 02:31:35 PM | |
|
Total Post: 515
Joined: May, 2005
|
Dear friend,
It is simple. You have datafile with increment by only 1 MB means whenever it becomes full it will be expanding for 1 MB, due to this reason it reflects always 99% full.
Thanks and regards,
Gitesh Trivedi
www.dbametrix.com
|
|
|
|
|
| Time Zone: EDT |
Send this thread to your friend |
|
|
|
|
Forum Rules & Description
Who Can Read The Forum? Any registered user or guest
Who Can Post New Topics? Any registered user
Who Can Post Replies? Any registered user
|
| |
Get FREE Magazines
|
Top 10 Forum User
|
| Murtuja Khokhar | 857 |
| Mohammed Taj | 746 |
| menon srivalsala kumar | 639 |
| positive fanatic | 615 |
| Gitesh Trivedi | 515 |
| Jayanta Sur | 480 |
| Vinoth Kumar | 436 |
| Vigyan Kaushik | 398 |
| Gopu Gopi | 364 |
| Vishant Sanghavi | 346 |
|
|
