Home » RDBMS Server » Server Administration » space issue  () 1 Vote
space issue [message #335948] Thu, 24 July 2008 04:10 Go to next message
diamond
Messages: 26
Registered: July 2008
Junior Member
I just noticed a proactive report lik this:

The tablespace is 85% filled...

But the datafiles show the status lik:

Say datafile1 is.....2000MB maxsize is 1500MB...

....
....

datafilen is.... 2000MB maxsize is 2000MB

My question is how is this possible? The datafile size is completly filled or exceeds the maxsize , but the tablespace status shows that there is a chance of 15% more growth of data in it???

Kindly explain.

Thanks & Regards.
Re: space issue [message #335949 is a reply to message #335948] Thu, 24 July 2008 04:14 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> create tablespace t datafile 'C:\ORACLE\BASES\MIKA\T.DBF' size 2000M autoextend on maxsize 2000M;

Tablespace created.

SQL> alter database datafile 'C:\ORACLE\BASES\MIKA\T.DBF' autoextend on maxsize 1500M;

Database altered.

SQL> select bytes/1024/1024 M, maxbytes/1024/1024 maxmb from dba_data_files 
  2  where file_name='C:\ORACLE\BASES\MIKA\T.DBF';
         M      MAXMB
---------- ----------
      2000       1500

1 row selected.

Regards
Michel
Re: space issue [message #335951 is a reply to message #335948] Thu, 24 July 2008 04:18 Go to previous messageGo to next message
diamond
Messages: 26
Registered: July 2008
Junior Member
Thanks for the reply. I understood your point.

But, My main concern is that the tablespace shows the space availabilty, but the datafile status shows that there is no space available for future data.
Re: space issue [message #335977 is a reply to message #335951] Thu, 24 July 2008 05:16 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I don't see any proof in what you posted that there is no available space.

In addition, as Ana says, "Those who live by the GUI, die by the GUI".

Regards
Michel
Re: space issue [message #335994 is a reply to message #335977] Thu, 24 July 2008 06:06 Go to previous messageGo to next message
diamond
Messages: 26
Registered: July 2008
Junior Member
ok..

But is there any possibility of this situation because of The Delayed Block Cleanout???

Regards.
Re: space issue [message #336001 is a reply to message #335994] Thu, 24 July 2008 06:42 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
No relation.

Regards
Michel
Re: space issue [message #336192 is a reply to message #336001] Fri, 25 July 2008 02:39 Go to previous messageGo to next message
diamond
Messages: 26
Registered: July 2008
Junior Member
There is 1 more thing i wanted to clarify on..(may be stupid...)


SQL> create tablespace test datafile '/u01/test01.dbf' size 100K autoextend on next 10K maxsize 150K;

Tablespace created.


SQL> alter database datafile '/u01/test01.dbf' autoextend on maxsize 5K;

Database altered.


SQL> select bytes M, maxbytes max from dba_data_files where tablespace_name='TEST';

M MAX
---------- ----------
106496 8192



SQL> select 8192/1024 from dual;

8192/1024
----------
8

Maxsize i have kept as 5K...but the status here shows as 8192.
It should have been 5*1024????

I have just entered into the ORACLE field.. so in many concept i'm not clear... please excuse me.. if i had a very basic or silly doubt.

Thanks & Regards
Re: space issue [message #336201 is a reply to message #336192] Fri, 25 July 2008 03:13 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Size can only be a multiple of block size.

Regards
Michel
Re: space issue [message #336510 is a reply to message #336001] Sun, 27 July 2008 23:49 Go to previous messageGo to next message
diamond
Messages: 26
Registered: July 2008
Junior Member
hmmmm.... ok.. but stil i am not clear on it.. why the max size is not 5k????
Re: space issue [message #336515 is a reply to message #336510] Sun, 27 July 2008 23:56 Go to previous messageGo to next message
BlackSwan
Messages: 25038
Registered: January 2009
Location: SoCal
Senior Member
diamond wrote on Sun, 27 July 2008 21:49
hmmmm.... ok.. but stil i am not clear on it.. why the max size is not 5k????


What is blocksize?
Re: space issue [message #336557 is a reply to message #335948] Mon, 28 July 2008 02:32 Go to previous messageGo to next message
varu123
Messages: 754
Registered: October 2007
Senior Member
5k is not a multiple of 8k which is your block size.
So Oracle rounded off to 8k.[nearest multiple]

I guess this is the reason.
Re: space issue [message #336561 is a reply to message #336557] Mon, 28 July 2008 02:48 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
varu123 wrote on Mon, 28 July 2008 09:32
5k is not a multiple of 8k which is your block size.

How do you know his block size?

Regards
Michel

Re: space issue [message #336566 is a reply to message #335948] Mon, 28 July 2008 03:08 Go to previous messageGo to next message
varu123
Messages: 754
Registered: October 2007
Senior Member
Default is 8k and with your line:
Quote:
Size can only be a multiple of block size.


block size is 8k.
Re: space issue [message #336570 is a reply to message #336566] Mon, 28 July 2008 03:16 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
My line was just to give the rule in order to OP to find himself the explaination.
Default is just default may be changed.
I posted the rule, Ana posted the question, so we just have to wait.

Regards
Michel
Re: space issue [message #336579 is a reply to message #335948] Mon, 28 July 2008 04:11 Go to previous messageGo to next message
varu123
Messages: 754
Registered: October 2007
Senior Member
Quote:
I posted the rule, Ana posted the question, so we just have to wait.


and the OP posted the output:
SQL> select bytes M, maxbytes max from dba_data_files where tablespace_name='TEST';

M MAX
---------- ----------
106496 [b]8192[/b]


Since the value of maxbytes is 8k,as per rule oracle rounded off 5k to the nearest multiple of block size.
2k,4k are not multiples of 8k but 8k is .
Re: space issue [message #336783 is a reply to message #336557] Tue, 29 July 2008 00:13 Go to previous messageGo to next message
diamond
Messages: 26
Registered: July 2008
Junior Member
so if the size was set to say 9k... then... wil oracle set the size as 9k or 16K???is that it rounds of only if the block size is set below 8k only

[Updated on: Tue, 29 July 2008 00:25]

Report message to a moderator

Re: space issue [message #336793 is a reply to message #336783] Tue, 29 July 2008 01:07 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
16K.
The rule is "it is the minimal number of blocks greater than the size you specified".
You don't split block. Please read Database Concepts, at least the first chapter.

Regards
Michel

[Updated on: Tue, 29 July 2008 01:08]

Report message to a moderator

Re: space issue [message #336801 is a reply to message #335948] Tue, 29 July 2008 01:23 Go to previous messageGo to next message
varu123
Messages: 754
Registered: October 2007
Senior Member
@Diamond
What was your block size?
Re: space issue [message #336802 is a reply to message #336801] Tue, 29 July 2008 01:24 Go to previous message
diamond
Messages: 26
Registered: July 2008
Junior Member
my block size is 8k
Previous Topic: resouce_limit issue
Next Topic: Unable to startup database
Goto Forum:
  


Current Time: Mon Dec 05 12:48:07 CST 2016

Total time taken to generate the page: 0.12240 seconds