CODEWITHSUNDEEP
sqloracle-database
Naros
Naros

Reputation: 21113

Unusual Oracle ALTER TABLE keyword, CHECK

I was provided an unusual Oracle syntax recently:

ALTER TABLE tableName SHRINK SPACE CHECK;

What is odd about this syntax is the CHECK keyword. Looking at the Oracle grammar for ALTER TABLE I cannot find any reference to what this keyword is implying and would love to have some reference documentation on this before I just make a change to our grammar that doesn't fit the official Oracle docs.

Upvotes: 0

Views: 246

Answers (1)

Connor McDonald
Connor McDonald

Reputation: 11581

The blog post mentioned is simply grabbing from MOS note 1132163.1

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

but omits a very important point

"Note: In some cases the CHECK option can cause deadlocks"

Upvotes: 2

Related Questions