1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

RE:physical_attributes_clause

Discussion in 'SQL PL/SQL' started by Ramayan, Dec 11, 2012.

  1. Ramayan

    Ramayan Active Member

    Messages:
    9
    Likes Received:
    0
    Trophy Points:
    55
    Location:
    India
    I AM CREATING A TABLE AND I WAS WONDERING ABOUT THE physical_attributes_clause AND storage_clause:

    physical_attributes_clause:
    PCTFREE (INTEGER)
    PCTUSED(INTEGER)
    INITRANS(INTEGER)

    storage_clause:

    DO PL/SQL DEVELOPER DEAL WITH physical_attributes_clause AND storage_clause OR DBA DOES.
     
  2. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    Re: physical_attributes_clause

    Special values ​​of the physical attributes of PCTFREE, PCTUSED,INITRANS
    developer defines as he knows well - why he does it.

    Otherwise it is necessary to use the recommended values
    physical attributes of the PCTFREE, PCTUSED,INITRANS
     
    Ramayan likes this.
  3. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Re: physical_attributes_clause

    It depends on how the tablespace was created. If Auto Segment Space Management is in use there is no purpose in setting the PCTUSED or INITTRANS parameters as they will be silently ignored. PCTFREE is the only storage parameter which needs to be set using ASSM, and, by default, it's 10:

    Code (SQL):
     
    TABLE_NAME                       PCT_FREE
    ------------------------------ ----------
    EMP                                    10
    DEPT                                   10
    BONUS                                  10
    SALGRADE                               10
    DUMMY                                  10
     
    This is the threshold value that Oracle must maintain, per data block, to provide reserve space for updates. Rows can increase in length due to an update to a varchar2 column and PCTFREE ensures space for row length growth. If a row exceeds the PCTFREE allotment it is possible that it could be migrated (relocated to a new block with the original ROWID preserved) or chained (broken into pieces and spread across two or more data blocks). Neither situation is a good one for performance. Setting PCTFREE requires a knowledge of the data and it's maintenance process -- rows that are constantly updated may require a larger PCTFREE value to prevent row chaining or migration.

    As I mentioned before the PCTUSED and INITTRANS parameters need not be set with ASSM as Oracle maintains reasonable values for these by default.

    If the tablespace is using MANUAL extent management then ASSM is not available and all three parameters can be set -- by default PCTUSED is 40 and INITTRANS is set to 1. INITRANS sets the number of transasction freelists, and it may be necessary to increase this value from the default if a high number of concurrent transactions are expected against a table. This is not to be confused with FREELISTS which sets the number of process free lists for a table or index.

    Setting these values properly requires knowledge of Oracle as well as knowledge of the data and the maintenance processes involved. Setting any of these parameters without proper investigation can create problems rather than solve them. It is best to read the Concepts Guide for the release of Oracle you are using -- the online documentation is found at http://tahiti.oracle.com.
     
    Ramayan likes this.
  4. Ramayan

    Ramayan Active Member

    Messages:
    9
    Likes Received:
    0
    Trophy Points:
    55
    Location:
    India
    Re: physical_attributes_clause

    Great help. Thanks Zargon.