oracle ora 10635,shrink space何以会报ora-10635?

The note describes the purpose of the CHECK option for the ALTER TABLE ...SHRINK SPACE CHECK statement.

Questions and Answers

The "ALTER TABLE ... SHRINK SPACE CHECK" statement is used to to properly check for proper segment type and segment attributes (e.g. row movement enabled) to allow shrink. The statement performs the exact same verifications as the "ALTER TABLE ... SHRINK SPACE", but it does not perform any actual shrinking on the segment.

E.g.

-- assuming the TEST table does not have the row movement enabled, the statement (regardless of the CHECK option) will report the error:

SQL> ALTER TABLE test SHRINK SPACE CHECK;

alter table test shrink space check

*

ERROR at line 1:

ORA-10636: ROW MOVEMENT is not enabled

-- the row movement is enabled in order to fix the error

SQL> ALTER TABLE test ENABLE ROW MOVEMENT;

Table altered.

-- running again the SHRINK SPACE CHECK against the table it says that the segment can be shrunk:

SQL> ALTER TABLE test SHRINK SPACE CHECK;

alter table test shrink space check

*

ERROR at line 1:

ORA-10655: Segment can be shrunk

This is not an error message, but rather a confirmation message about the possibility of shrinking the segment.

Note: In some cases the CHECK option can cause deadlocks as described in Note 6761624.8

"Bug 6761624 - ALTER INDEX .. SHRINK SPACE can deadlock with concurrent sessions". Executing an ALTER INDEX .. SHRINK SPACE command can lead to a deadlock with other processes that are concurrently accessing the index. To be seeing this problem the following should be true:

- A session waits on the event 'Wait for shrink lock' while holding a shared library cache lock on a base table.

- Another session is trying to acquire an exclusive library cache lock on the base table and holds the TM enqueue in exclusive mode.

- The sessions are blocking each other.

Note: The SHRINK SPACE CHECK command is used mainly internally by Oracle, for example by the  segment advisor feature.

The note describes the purpose of the CHECK option for the ALTER TABLE ...SHRINK SPACE CHECK statement.

Questions and Answers

The "ALTER TABLE ... SHRINK SPACE CHECK" statement is used to to properly check for proper segment type and segment attributes (e.g. row movement enabled) to allow shrink. The statement performs the exact same verifications as the "ALTER TABLE ... SHRINK SPACE", but it does not perform any actual shrinking on the segment.

E.g.

-- assuming the TEST table does not have the row movement enabled, the statement (regardless of the CHECK option) will report the error:

SQL> ALTER TABLE test SHRINK SPACE CHECK;

alter table test shrink space check

*

ERROR at line 1:

ORA-10636: ROW MOVEMENT is not enabled

-- the row movement is enabled in order to fix the error

SQL> ALTER TABLE test ENABLE ROW MOVEMENT;

Table altered.

-- running again the SHRINK SPACE CHECK against the table it says that the segment can be shrunk:

SQL> ALTER TABLE test SHRINK SPACE CHECK;

alter table test shrink space check

*

ERROR at line 1:

ORA-10655: Segment can be shrunk

This is not an error message, but rather a confirmation message about the possibility of shrinking the segment.

Note: In some cases the CHECK option can cause deadlocks as described in Note 6761624.8

"Bug 6761624 - ALTER INDEX .. SHRINK SPACE can deadlock with concurrent sessions". Executing an ALTER INDEX .. SHRINK SPACE command can lead to a deadlock with other processes that are concurrently accessing the index. To be seeing this problem the following should be true:

- A session waits on the event 'Wait for shrink lock' while holding a shared library cache lock on a base table.

- Another session is trying to acquire an exclusive library cache lock on the base table and holds the TM enqueue in exclusive mode.

- The sessions are blocking each other.

Note: The SHRINK SPACE CHECK command is used mainly internally by Oracle, for example by the  segment advisor feature.