This content has been marked as final. Show 10 replies
Fran wrote: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 PCTFREE number;
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
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
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.
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?
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
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
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.