OraFAQ Forum: Server Administration » space issue

Home » RDBMS Server » Server Administration » space issue (

) 1 Vote

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

space issue [message #335948]

Thu, 24 July 2008 04:10

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.

Report message to a moderator

Re: space issue [message #335949 is a reply to message #335948]

Thu, 24 July 2008 04:14

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: space issue [message #335951 is a reply to message #335948]

Thu, 24 July 2008 04:18

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.

Report message to a moderator

Re: space issue [message #335977 is a reply to message #335951]

Thu, 24 July 2008 05:16

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: space issue [message #335994 is a reply to message #335977]

Thu, 24 July 2008 06:06

diamond
Messages: 26
Registered: July 2008

Junior Member

ok..

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

Regards.

Report message to a moderator

Re: space issue [message #336001 is a reply to message #335994]

Thu, 24 July 2008 06:42

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

No relation.

Regards
Michel

Report message to a moderator

Re: space issue [message #336192 is a reply to message #336001]

Fri, 25 July 2008 02:39

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

Report message to a moderator

Re: space issue [message #336201 is a reply to message #336192]

Fri, 25 July 2008 03:13

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Size can only be a multiple of block size.

Regards
Michel

Report message to a moderator

Re: space issue [message #336510 is a reply to message #336001]

Sun, 27 July 2008 23:49

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????

Report message to a moderator

Re: space issue [message #336515 is a reply to message #336510]

Sun, 27 July 2008 23:56

BlackSwan
Messages: 26766
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?

Report message to a moderator

Re: space issue [message #336557 is a reply to message #335948]

Mon, 28 July 2008 02:32

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.

Report message to a moderator

Re: space issue [message #336561 is a reply to message #336557]

Mon, 28 July 2008 02:48

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: space issue [message #336566 is a reply to message #335948]

Mon, 28 July 2008 03:08

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.

Report message to a moderator

Re: space issue [message #336570 is a reply to message #336566]

Mon, 28 July 2008 03:16

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: space issue [message #336579 is a reply to message #335948]

Mon, 28 July 2008 04:11

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 .

Report message to a moderator

Re: space issue [message #336783 is a reply to message #336557]

Tue, 29 July 2008 00:13

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

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

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

varu123
Messages: 754
Registered: October 2007

Senior Member

@Diamond
What was your block size?

Report message to a moderator

Re: space issue [message #336802 is a reply to message #336801]

Tue, 29 July 2008 01:24

diamond
Messages: 26
Registered: July 2008

Junior Member

my block size is 8k

Report message to a moderator

Previous Topic:	resouce_limit issue
Next Topic:	Unable to startup database

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Nov 29 19:40:43 CST 2024