Home » Other » General » pctfree and pct used
pctfree and pct used [message #102994] Tue, 07 January 2003 03:44 Go to next message
saraswati
Messages: 3
Registered: August 2002
Junior Member
any bodies know pls give me answer
Re: pctfree and pct used [message #103027 is a reply to message #102994] Fri, 17 January 2003 00:51 Go to previous messageGo to next message
Maithily
Messages: 5
Registered: December 2002
Junior Member
Hi,

PCTFREE and PCTUSED are the block storage parameters.

1. PCTFREE- This parameter is used to specify how much space should be left in the block for updates.
For eg. if The PctFree for a block is 30, then Oracle keeps on adding new rows to the block until the block is 70 % full. It leaves the 30 % for future updates. That is, in case, any row is updated and requires some more space, it is taken out from the 30 % remaining in the Block. U should specify the Value of this parameter HIGH if future updates in the rows of the table are going to need more space. In case ur table is less prone to updates, then this value can be specified LOW.

PCTUSED : As I have mentioned above, Oracle stops INSERTING new Rows in a table when the space usage reaches the PCTFREE limit.
Now consider this ---- When should Oracle start inserting new rows in the Block ?
PctUSED parameter is taken into consideration for the answer of the above question.
Suppose u have specified PCTUSED as 40 %. and PCTFREE as 20 %.

1. Oracle will keep on inserting new rows till the space is 80 % used. It will reserve the remaining 20% for future updates.
2. To start adding new rows again to the block, Oracle will check the space in the Block and the PCTUSED parameter.
3. When the space falls below 40 %, Oracle will start adding new rows to the block.

Hope I have explained the concept fairly.

Thanks and Regards,
Maithily
Re: pctfree and pct used [message #103784 is a reply to message #102994] Sat, 13 March 2004 20:39 Go to previous messageGo to next message
sahadevan
Messages: 2
Registered: November 2003
Junior Member
Hi,
PCTFREE and PCTUSED are the block storage parameters.

1. PCTFREE- This parameter is used to specify how much space should be left in the block for updates.
For eg. if The PctFree for a block is 30, then Oracle keeps on adding new rows to the block until the block is 70 % full. It leaves the 30 % for future updates. That is, in case, any row is updated and requires some more space, it is taken out from the 30 % remaining in the Block. U should specify the Value of this parameter HIGH if future updates in the rows of the table are going to need more space. In case ur table is less prone to updates, then this value can be specified LOW.

PCTUSED : As I have mentioned above, Oracle stops INSERTING new Rows in a table when the space usage reaches the PCTFREE limit.
Now consider this ---- When should Oracle start inserting new rows in the Block ?
PctUSED parameter is taken into consideration for the answer of the above question.
Suppose u have specified PCTUSED as 40 %. and PCTFREE as 20 %.

1. Oracle will keep on inserting new rows till the space is 80 % used. It will reserve the remaining 20% for future updates.
2. To start adding new rows again to the block, Oracle will check the space in the Block and the PCTUSED parameter.
3. When the space falls below 40 %, Oracle will start adding new rows to the block.

Hope I have explained the concept fairly.

Thanks and Regards,
Sahadevan,.M.K,
sobha Renaissance
Bangalore
India
Re: pctfree and pct used [message #239185 is a reply to message #102994] Mon, 21 May 2007 12:44 Go to previous messageGo to next message
mivey4
Messages: 19
Registered: March 2007
Junior Member
Hi.

I know that this thread has been untouched for quite a while now but I still have a question that remains unclear for which this thread addresses. So rather than open a new thread for the same question, I figured I'd just use this existing thread to post my question.

If I am wrong for doing this, please tell me and I'll open another thread.

I understand very clear how PCTFREE works but I am still a bit confused for how PCTUSED works.

So using the existing example:

PCTFREE - 20
PCTUSED - 40

The data block will continue to fill with rows of data until the PCTFREE is reached which would be 80% full.

But, if the block can't be used again until the free space falls below 40%....what is going to cause the block to decrease in size below that 40% threshold??? Delete operations?

Forgive me if this sounds like a stupid question but I can't find an explicit answer anywhere. The definitions I've seen seem to be with the presumption that the reader knows what the answer is and I just want solid clarification.

Thanks
Re: pctfree and pct used [message #239195 is a reply to message #239185] Mon, 21 May 2007 13:13 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
what is going to cause the block to decrease in size below that 40% threshold?

Deletes and updates can decrease the space used in the block.

It's not a stupid question and the rest of your post is correct just a small precision:
Quote:
The data block will continue to fill with rows of data until the PCTFREE is reached

Actually, the rows will continue to be INSERTED until PCTFREE is reached but the block can be more filled with updates till there is no more space (then the rows are migrated but that's another story).

Regards
Michel
Re: pctfree and pct used [message #239200 is a reply to message #239195] Mon, 21 May 2007 13:36 Go to previous messageGo to next message
mivey4
Messages: 19
Registered: March 2007
Junior Member
Thanks Michel -

Your explanation provided the additional clarification that I was looking for.

As for the row migration; that and the row chaining concepts I have a thorough understanding of so that "other story" is clear to me.

Thanks again!!
Re: pctfree and pct used [message #362271 is a reply to message #102994] Mon, 01 December 2008 11:49 Go to previous messageGo to next message
ykozhevnikov
Messages: 58
Registered: November 2008
Location: USA
Member
I have question about PCTFREE and PCTUSED
If PCTUSED set to null,when insert starts again in the block
thanks
Re: pctfree and pct used [message #362276 is a reply to message #362271] Mon, 01 December 2008 12:04 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
PCTUSED cannot be set to null.

Regards
Michel
Re: pctfree and pct used [message #362280 is a reply to message #362276] Mon, 01 December 2008 12:26 Go to previous messageGo to next message
ykozhevnikov
Messages: 58
Registered: November 2008
Location: USA
Member
select * from dba_tables where owner='SATURN1'
I just found for some tables PCTUSED value is null,It could be server uses some default values for PCTUSED.

And dba_tables view. Field PCT_USED allowed to put null value.



ALTER table dept PCTFREE 30 PCTUSED 70 this statement does not allowed to put null. But how come null value apears in
dba_tables

Thanks Michel

[Updated on: Mon, 01 December 2008 12:43]

Report message to a moderator

Re: pctfree and pct used [message #362290 is a reply to message #362280] Mon, 01 December 2008 12:53 Go to previous messageGo to next message
joy_division
Messages: 4644
Registered: February 2005
Location: East Coast USA
Senior Member
I think PCT_USED is null when the table is locally managed, but don't take that as a guarantee.
Re: pctfree and pct used [message #362291 is a reply to message #362280] Mon, 01 December 2008 12:53 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Because (at least one):
- table is partitioned
- tablespace is ASSM
In these cases the parameter is meaningless.

Regards
Michel

[Updated on: Mon, 01 December 2008 12:55]

Report message to a moderator

Re: pctfree and pct used [message #362292 is a reply to message #362291] Mon, 01 December 2008 12:57 Go to previous messageGo to next message
ykozhevnikov
Messages: 58
Registered: November 2008
Location: USA
Member
Thanks Michel.
Re: pctfree and pct used [message #362860 is a reply to message #362292] Thu, 04 December 2008 05:54 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Would also be the case for Index Organized and Externally Organised I reckon.
Previous Topic: EM2Go
Next Topic: Required Unit test Plan for Data migration
Goto Forum:
  


Current Time: Sun Dec 11 02:28:55 CST 2016

Total time taken to generate the page: 0.08191 seconds