Home » RDBMS Server » Server Administration » how autoextend works
how autoextend works [message #281312] Fri, 16 November 2007 06:53 Go to next message
varu123
Messages: 754
Registered: October 2007
Senior Member
I created a datafile with 700mb size and autoextend on.
I imported some empty tables into it.
When i checked my disk size,it was 99% full and the size of the file became 2000mb.This means the datafile got autoextended as the tables were imported.BUt the tables were empty and i guess the autoextend option works when there is no space left in the datafile for the data but there was no data in the tables.

Any lights??

Report message to a moderator

Re: how autoextend works [message #281316 is a reply to message #281312] Fri, 16 November 2007 06:58 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Each table will have an extent, whose size is specified by the INITIAL parameter used when the table is created.

Report message to a moderator

Re: how autoextend works [message #281322 is a reply to message #281312] Fri, 16 November 2007 07:11 Go to previous messageGo to next message
Michel Cadot
Messages: 68684
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The table grows, it fills the file which extends.
You empty the table (using delete), the table is empty but keeps the same size as well as the file.

Regards
Michel

[Updated on: Fri, 16 November 2007 07:11]

Report message to a moderator

Re: how autoextend works [message #281343 is a reply to message #281312] Fri, 16 November 2007 08:17 Go to previous messageGo to next message
varu123
Messages: 754
Registered: October 2007
Senior Member
No delete took place.
The tables were only created and no inserting was done.but they were created in system tablespace.I exported the empty tables from system and imported them into users schema.
but the datafile was autoextended to 2000mb from 700mb.

Report message to a moderator

Re: how autoextend works [message #281345 is a reply to message #281343] Fri, 16 November 2007 08:19 Go to previous messageGo to next message
Michel Cadot
Messages: 68684
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
See initial extent size as JRowbottom said.

Regards
Michel

Report message to a moderator

Re: how autoextend works [message #281346 is a reply to message #281312] Fri, 16 November 2007 08:26 Go to previous messageGo to next message
varu123
Messages: 754
Registered: October 2007
Senior Member
When a table gets created in any tablespace,its initial,minextent,maxextent,pctfree etc are set to default unless specified in the create table statement.
The initial extent values of system and users tablespace vary.

Report message to a moderator

Re: how autoextend works [message #281349 is a reply to message #281346] Fri, 16 November 2007 08:34 Go to previous messageGo to next message
Michel Cadot
Messages: 68684
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And then?
When you import, initial extent size is specified in dmp file as well as other storage parameters.

Regards
Michel

Report message to a moderator

Re: how autoextend works [message #281354 is a reply to message #281312] Fri, 16 November 2007 08:43 Go to previous messageGo to next message
varu123
Messages: 754
Registered: October 2007
Senior Member
So there happens a mismatch between intial,next,min and max extents and the error unable to extend minextents by "number" in tablespace when tables are transfered among tablespaces.
So even though the tablespace has space but its minimum extents cannot extend beyond a specified limit and hence the error.
So instead of adding space to the tablespace,the storage parameters should be tuned as shown during import.

Report message to a moderator

Re: how autoextend works [message #281364 is a reply to message #281354] Fri, 16 November 2007 09:51 Go to previous messageGo to next message
Michel Cadot
Messages: 68684
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Question

Is there any question in what you posted?

Regards
Michel

Report message to a moderator

Re: how autoextend works [message #281369 is a reply to message #281312] Fri, 16 November 2007 10:02 Go to previous messageGo to next message
varu123
Messages: 754
Registered: October 2007
Senior Member
Quote:

So there happens a mismatch between intial,next,min and max extents and the error unable to extend minextents by "number" in tablespace when tables are transfered among tablespaces.
So even though the tablespace has space but its minimum extents cannot extend beyond a specified limit and hence the error.
So instead of adding space to the tablespace,the storage parameters should be tuned as shown during import.


Michel,
Please verify whether my understanding is right or wrong.

Report message to a moderator

Re: how autoextend works [message #281370 is a reply to message #281369] Fri, 16 November 2007 10:07 Go to previous messageGo to next message
Michel Cadot
Messages: 68684
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Sorry, I don't understand what you posted.

Regards
Michel

Report message to a moderator

Re: how autoextend works [message #281371 is a reply to message #281312] Fri, 16 November 2007 10:17 Go to previous messageGo to next message
varu123
Messages: 754
Registered: October 2007
Senior Member
Sad perhaps my english is not good.
Jrowbottom said:
Quote:

Each table will have an extent, whose size is specified by the INITIAL parameter used when the table is created

Thats obvious.
My question is if i have a table in an x tablespace which has its own storage parameters defined..forex intial 1m,minextent 3 maxextent 10.

Now if i import the tables from x tablespace to a tablespace named y which has storage parameters for ex. initial 2m,minextent 5 maxextent 20.

Is this mismatch of storage parameters cause the error:
unable to extend minextent beyond "number",unable to create initial extent etc...

Report message to a moderator

Re: how autoextend works [message #281373 is a reply to message #281371] Fri, 16 November 2007 10:25 Go to previous messageGo to next message
Michel Cadot
Messages: 68684
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Storage parameters applied during import are those in the dump file and only that.
Original table parameters or tablespace parameters don't matter.

Regards
Michel

Report message to a moderator

Re: how autoextend works [message #281525 is a reply to message #281312] Sat, 17 November 2007 22:13 Go to previous message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member
In addition with Michel, the table's initial extent in dump(export) is not necessarily the same as in original table from which dump was taken.

And varu123 , when you took dump table itself has the initial clause. So no matter of the tablespace parameter to which you import.

Report message to a moderator

Previous Topic: UPDATE OF CONFIGURATION PARAMETER
Next Topic: Problem..Install Oracle 8.1.7 in Windows XP SP2
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Wed Sep 18 18:11:32 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.