OraFAQ Forum: Server Administration » Maxsize of Tablespace

Home » RDBMS Server » Server Administration » Maxsize of Tablespace

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

Maxsize of Tablespace [message #437459]

Mon, 04 January 2010 09:17

pokhraj_d
Messages: 117
Registered: December 2007

Senior Member

Hi ,

I have one tablespace PSINDEX with Maxsize of 6 GB.
But when I query the tablespace its showing the BYTES is greater than MAXBYTES.

Please advice why it has showing like that.

Pokhraj

Report message to a moderator

Re: Maxsize of Tablespace [message #437460 is a reply to message #437459]

Mon, 04 January 2010 09:19

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>But when I query the tablespace its showing the BYTES is greater than MAXBYTES.
>Please advice why it has showing like that.
because size is greater than 6GB

Post Operating System (OS) name & version for DB server system.
Post results of
SELECT * from v$version

use CUT & PASTE so we can see what you see

Report message to a moderator

Re: Maxsize of Tablespace [message #437463 is a reply to message #437459]

Mon, 04 January 2010 09:35

Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...

Senior Member

Hi Pokhraj,
have a look at http://arjudba.blogspot.com/2009/11/when-datafile-bytes-size-greater-than.html
and I hope it will clear your question.

Report message to a moderator

Re: Maxsize of Tablespace [message #437464 is a reply to message #437459]

Mon, 04 January 2010 09:43

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

Senior Member
Account Moderator

You can modify a file to give it a size that is lower than the current one:

SQL> create tablespace t datafile 'E:\ORACLE\BASES\MIKA\T.DBF' size 100m 
  2  autoextend on next 10m maxsize 200m;

Tablespace created.

SQL> select bytes, maxbytes from dba_data_files where file_name='E:\ORACLE\BASES\MIKA\T.DBF' ;
     BYTES   MAXBYTES
---------- ----------
 104857600  209715200

1 row selected.

SQL> alter database datafile 'E:\ORACLE\BASES\MIKA\T.DBF' autoextend on maxsize 50m;

Database altered.

SQL> select bytes, maxbytes from dba_data_files where file_name='E:\ORACLE\BASES\MIKA\T.DBF' ;
     BYTES   MAXBYTES
---------- ----------
 104857600   52428800

1 row selected.

Regards
Michel

Report message to a moderator

Re: Maxsize of Tablespace [message #440865 is a reply to message #437459]

Wed, 27 January 2010 10:00

daverich
Messages: 23
Registered: January 2010
Location: UK

Junior Member

Run this query below:

select file_name, (bytes/1024)/1024 "Size in MB", maxbytes, autoextensible from dba_data_files where tablespace_name='PSINDEX';

Check if "autoextensible" is YES or NO.

I found that (well, not in books) if autoextensible "NO" then maxbytes will be 0.

If autoextensible is "YES" then it should show max filesize in byte.

Perhaps you should use Michel's command
Quote:

alter database datafile 'E:\ORACLE\BASES\MIKA\T.DBF' autoextend on maxsize 50m;

to change the autoextend "on" and then run the query again. That should display your maxbytes.

[Updated on: Wed, 27 January 2010 10:01]

Report message to a moderator

Re: Maxsize of Tablespace [message #442991 is a reply to message #437459]

Thu, 11 February 2010 22:04

oradbaexp
Messages: 16
Registered: January 2009

Junior Member

Hi

Generally , maxbytes become 0 (Zero) or OS defaule value ( 32 GB) , when while creating tablepsace maxsize is not mentioned.

======

Sorry, in link all was given.
No need to repeat.

Regards
Om Raj

[Updated on: Fri, 12 February 2010 00:38]

Report message to a moderator

Previous Topic:	Creating a physical standby server
Next Topic:	Oracle Down time

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Jan 27 01:24:37 CST 2025