ORA-10662: Segment has long columns, Table Shrink Command Failed: Workaround

advertisements

_____________________________________________________________________________________________________________________

Error Description

‘Alter table tablename shrink space compact’ command failed with following error.

SQL> alter table siebel.EIM_SR_DTL shrink space compact

*

ERROR at line 1:

ORA-10662: Segment has long columns

Problem Description:

The table has long column. Table Shrink command is not supported for the tables with long column. Even you cannot move the table to different tablespace. Normally shrink command is used to for table reorganization to reclaim the space. You need to go ahead with alternative ways to perform the reorganization.

You cannot use CTAS also. It will give error.

SQL> create table test1 as select * from siebel.EIM_SR_DTL where rownum<10;

create table test1 as select * from siebel.EIM_SR_DTL where rownum<10

                             *

ERROR at line 1:

ORA-00997: illegal use of LONG datatype

Workarounds:

1. Use exp/imp:

Export the table, drop table, import the table and recreate index, grants, etc.

2. Use SQL*PLUS COPY command.

Create the table into new/current schema with sql* plus copy command, drop the original table. Use copy command again if it is in different schema or use alter table rename to rename the table.

More On Copy Command

SQL> copy from siebel/<password>@test1 to  scott/tiger@test1 create example_shrink using select * from  siebel.EIM_SR_DTL where rownum<10;

Array fetch/bind size is 15. (arraysize is 15)

Will commit when done. (copycommit is 0)

Maximum long size is 80. (long is 80)

Table EXAMPLE_SHRINK created.

   9 rows selected from siebel@test1.

   9 rows inserted into EXAMPLE_SHRINK.

   9 rows committed into EXAMPLE_SHRINK at scott@test1.

_____________________________________________________________________________________________________________________

Website Stats