10 Replies Latest reply: May 8, 2012 8:39 PM by Richard Foote-Oracle RSS

    How to change PCTFREE parameter for an existing table

    750075
      How to change PCTFREE parameter for an existing table?

      Guys can you please help me
        • 1. Re: How to change PCTFREE parameter for an existing table
          Fran
          ALTER TABLE table_name PCTFREE number;


          Bharath.allam     
               
               
          Handle:     Bharath.allam
          Status Level:     Newbie
          Registered:     Jan 29, 2010
          Total Posts:     29
          Total Questions:     12 (10 unresolved)

          Please, mark as answered the post when you think you got the correct answer
          • 2. Re: How to change PCTFREE parameter for an existing table
            fjfranken
            Fran wrote:
            ALTER TABLE table_name PCTFREE number;


            Bharath.allam     
                 
                 
            Handle:     Bharath.allam
            Status Level:     Newbie
            Registered:     Jan 29, 2010
            Total Posts:     29
            Total Questions:     12 (10 unresolved)

            Please, mark as answered the post when you think you got the correct answer
            This change will only be valid for new allocated blocks for this table. All existing data blocks will retain the old value unless you rebuild the tables using
            ALTER TABLE table_name MOVE;
            Keep in mind that a MOVE of a table also invalidates its indexes, which then also need to be rebuild

            Cheers
            FJFranken
            • 3. Re: How to change PCTFREE parameter for an existing table
              750075
              Thanks Fran. Will do that. Thanks for making aware to mark answered.
              • 4. Re: How to change PCTFREE parameter for an existing table
                750075
                @fjfranken : Is there any way to do it using exp/imp and creating indexfile

                I came to know this from one of my mate and we are not sure of exact the procedure.

                Can you please let me know
                • 5. Re: How to change PCTFREE parameter for an existing table
                  fjfranken
                  Hi ,

                  Yes of course that is possible.
                  In that case you would export the table out of the database ( by means of old style exp )
                  Start the import with the index_file option to generate the table/index statements
                  Edit the file created and update the PCTFREE and uncomment all statement that need to be executed ( by default if I remember correctly it comments out the Index statements )
                  The run the file so the table and indexes get created
                  After that start the import again with the option "ignore=yes"

                  Make sure that you only export this particular table by using "tables=<table_name" option.
                  Otherwise at import the "ignore=yes" will also import back other data which still exists.


                  In my opinion however this is not what you want to do. This is more complex, failure sensitive and the actual table needs to be dropped, so users are affected unnecessary long.



                  Success!!
                  FJFranken
                  • 6. Re: How to change PCTFREE parameter for an existing table
                    750075
                    Thanks FJFranken,

                    Then, what method would you suggest for this task with low risk?
                    • 7. Re: How to change PCTFREE parameter for an existing table
                      jgarry
                      It depends what you want to do. What is pctfree, and what do you want to change it to? Are you sure there aren't implicit data distributions where data is now? Are you trying to fix some problem like row locking? Are you trying to maximally compact old data? Do you update?

                      Everything has a risk, including doing nothing. Sometimes doing nothing is least risky. Sometimes doing something is a waste of time. What are you really trying to accomplish?
                      • 8. Re: How to change PCTFREE parameter for an existing table
                        rp0428
                        >
                        All existing data blocks will retain the old value unless you rebuild the tables
                        >
                        Incorrect - the table does not need to be rebuilt.

                        You can execute the DBMS_REPAIR.SEGMENT_FIX_STATUS procedure to implement the new setting on blocks already allocated to the segment as documented in the SQL reference.

                        See the 'physical_attributes_clause of ALTER TABLE in the SQL reference doc
                        http://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_3001.htm
                        • 9. Re: How to change PCTFREE parameter for an existing table
                          rp0428
                          >
                          Is there any way to do it using exp/imp and creating indexfile
                          >
                          I agree with jgarry - don't do anything until you 1) have a reason for doing it, 2) have determined that doing it will have the desired effect and 3) understand the ramifications of doing it.

                          You just mentioned 'creating indexfile'. What does changing the PCTFREE on a table have to do with indexes?
                          Indexes have their own attribute settings and those settings are independent of the settings for the table.

                          Also, you may not even be able to change the PCTFREE for your table. Among other things it depends on whether the table resides in a locally managed tablespace; if it does you will get an error.

                          And for segments with automatic segment-space management there are other restrictions.
                          So before you even think about changing this parameter you need to know if the tablespace is locally managed and whether you are using automatic segment-space management.

                          Do you know this?

                          See the 'physical_attributes_clause' in ALTER TABLE of the SQL Language doc
                          http://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_3001.htm
                          >
                          Restrictions on Altering Table Physical Attributes Altering physical attributes is subject to the following restrictions:
                          You cannot specify the PCTUSED parameter for the index segment of an index-organized table.
                          If you attempt to alter the storage attributes of tables in locally managed tablespaces, then Oracle Database raises an error. However, if some segments of a partitioned table reside in a locally managed tablespace and other segments reside in a dictionary-managed tablespace, then the database alters the storage attributes of the segments in the dictionary-managed tablespace but does not alter the attributes of the segments in the locally managed tablespace, and does not raise an error.
                          For segments with automatic segment-space management, the database ignores attempts to change the PCTUSED setting. If you alter the PCTFREE setting, then you must subsequently run the DBMS_REPAIR.SEGMENT_FIX_STATUS procedure to implement the new setting on blocks already allocated to the segment.
                          • 10. Re: How to change PCTFREE parameter for an existing table
                            Richard Foote-Oracle
                            Hi rp0428

                            I think you're confusing PCTFREE with PCTUSED as PCTFREE can be set in a LMT (as indeed your extract from the doco says).

                            Cheers

                            Richard Foote
                            http://richardfoote.wordpress.com/