Home » RDBMS Server » Server Administration » table can not shrink (11.1.0.7 windows xp)
table can not shrink [message #524730] Mon, 26 September 2011 06:48 Go to next message
andy huang
Messages: 498
Registered: July 2011
Senior Member
Dear all,
My table can not shrink, why?
SQL> Alter Table tb_hxl_user Shrink Space Cascade;
Alter Table tb_hxl_user Shrink Space Cascade
*
ERROR at line 1:
ORA-10635: Invalid segment or tablespace type


SQL> desc tb_hxl_user;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 STATEDATE                                 NOT NULL DATE
 USERNUMBER                                NOT NULL VARCHAR2(13)
 PROVCODE                                  NOT NULL NUMBER
 REM1                                               VARCHAR2(100)
 REM2                                      NOT NULL VARCHAR2(100)
 REM3                                               VARCHAR2(100)
 REM4                                               VARCHAR2(100)
 REM5                                               VARCHAR2(100)
 REM6                                               VARCHAR2(100)
 REM7                                               VARCHAR2(100)
 REM8                                               VARCHAR2(100)
 REM9                                               VARCHAR2(100)
 REM10                                              VARCHAR2(100)
 CREATE_DATE                               NOT NULL DATE
 CREATE_BY                                 NOT NULL NUMBER
 LAST_UPDATE_DATE                          NOT NULL DATE
 LAST_UPDATE_BY                            NOT NULL NUMBER
 REM11                                              VARCHAR2(1024)
 REM12                                              CLOB

SQL> Select * From v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production
PL/SQL Release 11.1.0.7.0 - Production
CORE    11.1.0.7.0      Production
TNS for 32-bit Windows: Version 11.1.0.7.0 - Production
NLSRTL Version 11.1.0.7.0 - Production


Re: table can not shrink [message #524731 is a reply to message #524730] Mon, 26 September 2011 07:00 Go to previous messageGo to next message
John Watson
Messages: 4672
Registered: January 2010
Location: Global Village
Senior Member
A freelist managed tablespace?
Re: table can not shrink [message #524734 is a reply to message #524730] Mon, 26 September 2011 07:13 Go to previous messageGo to next message
andy huang
Messages: 498
Registered: July 2011
Senior Member
The user HXL is belong to tablespace TSP_HXL_01 which segment management is AUTO
SQL> show user;
USER is "HXL"
SQL>
SQL> Select bb.default_tablespace,bb.username From dba_users bb
  2  Where bb.username ='HXL'
  3  /

DEFAULT_TABLESPACE             USERNAME
------------------------------ ------------------------------
TSP_HXL_01                     HXL

SQL> Select aa.TABLESPACE_NAME,aa.SEGMENT_SPACE_MANAGEMENT
  2  From  Dba_Tablespaces aa
  3  Where aa.TABLESPACE_NAME ='TSP_HXL_01';

TABLESPACE_NAME                SEGMEN
------------------------------ ------
TSP_HXL_01                     AUTO
Re: table can not shrink [message #524735 is a reply to message #524734] Mon, 26 September 2011 07:18 Go to previous messageGo to next message
John Watson
Messages: 4672
Registered: January 2010
Location: Global Village
Senior Member
USers don't belong to tablespaces. Segments do.
Re: table can not shrink [message #524736 is a reply to message #524735] Mon, 26 September 2011 07:32 Go to previous messageGo to next message
andy huang
Messages: 498
Registered: July 2011
Senior Member
hi,
The table tb_hxl_user belong to user hxl,and the default tablespace of user hxl is 'TSP_HXL_01'.
Re: table can not shrink [message #524738 is a reply to message #524736] Mon, 26 September 2011 07:38 Go to previous messageGo to next message
Michel Cadot
Messages: 59405
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Which not mean that the table is in this tablespace.

Regards
Michel
Re: table can not shrink [message #524745 is a reply to message #524738] Mon, 26 September 2011 08:09 Go to previous messageGo to next message
andy huang
Messages: 498
Registered: July 2011
Senior Member
SQL> Select aa.TABLE_NAME,aa.TABLESPACE_NAME
  2   From User_Tables aa Where aa.TABLE_NAME='TB_HXL_USER';

TABLE_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
TB_HXL_USER                    TSP_HXL_01

SQL>
Re: table can not shrink [message #524746 is a reply to message #524745] Mon, 26 September 2011 08:13 Go to previous messageGo to next message
andy huang
Messages: 498
Registered: July 2011
Senior Member
The table have a clob column,it may be cause to the error?
Re: table can not shrink [message #524747 is a reply to message #524746] Mon, 26 September 2011 08:21 Go to previous messageGo to next message
andy huang
Messages: 498
Registered: July 2011
Senior Member
hi,
The issue have been sloved,because the table have been compressed.
Re: table can not shrink [message #524748 is a reply to message #524746] Mon, 26 September 2011 08:22 Go to previous message
Michel Cadot
Messages: 59405
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Did you previously check the restrictions mentioned in the documentation?

Quote:
Restrictions on the shrink_clause
The shrink_clause is subject to the following restrictions:
* You cannot specify this clause for a cluster, a clustered table, or any object with a LONG column.
* Segment shrink is not supported for tables with function-based indexes or bitmap join indexes.
* This clause does not shrink mapping tables of index-organized tables, even if you specify CASCADE.
* You cannot specify this clause for a compressed table.
* You cannot shrink a table that is the master table of an ON COMMIT materialized view. Rowid materialized views must be rebuilt after the shrink operation.


Regards
Michel

[Edit: Add link]

[Updated on: Mon, 26 September 2011 08:22]

Report message to a moderator

Previous Topic: System TableSpace is so big
Next Topic: important question [merged 3]
Goto Forum:
  


Current Time: Mon Oct 20 23:51:56 CDT 2014

Total time taken to generate the page: 0.09188 seconds