ORA-10635: Invalid segment or tablespace type problem resolution !!
2 min readNormally if you are trying to shrink a compressed table lets say you get below error, and the trick to solve it is below :-
For partition tables and for normal tables
alter table table_name modify partition part_name shrink space ;
*
ERROR at line 1:
ORA-10635: Invalid segment or tablespace type
SQL> alter table <table_name> enable row movement;
Table altered.
SQL> alter table <table_name> shrink space compact;
alter table <table_name> shrink space compact
*
ERROR at line 1:
ORA-10635: Invalid segment or tablespace type
There are two possibilities:
1: The SHRINK clause is not valid on a compressed table and you will get ORA-10635 .
(a) In case of a table being compressed table, can resolve issue like this:
sql> conn / as sysdba
sql> alter table <table_name> nocompress;
sql> alter table <table_name> enable row movement;
sql> alter table <table_name> shrink space compact;
sql> exit
(b) Autosegment space management was not set for the tablespace. Shrink operations can be performed only on segments in locally managed tablespaces with automatic segment space management (ASSM). Within an ASSM tablespace, all segment types are eligible for online segment shrink except these:
* IOT mapping tables
* Tables with rowid based materialized views
* Tables with function-based indexes.
2: In case autosegment space management is not set for the tablespace, please enable it. The way to “convert” to ASSM is to export all the objects from the MSSM tablespace, then create a new ASSM tablespace and finally import in this new one all the objects.
Tracing a session with a client identifier
Below is the Methodology to trace a session with Client Identifiers, I have created a special trigger also to generate client_identifier for a user with DBMS_SESSION package.
SQL> create table sys.trigger_test (cid varchar2(1000), sdate date, loggin varchar2(1000));
Table created.
CREATE OR REPLACE TRIGGER trig_logon_scott_v1
AFTER LOGON
ON SCOTT.SCHEMA
BEGIN
DBMS_SESSION.SET_IDENTIFIER('hr');
END;
/
Trigger created.
================================================================================
CREATE OR REPLACE TRIGGER set_trace_logon_test1
AFTER LOGON ON SCOTT.SCHEMA
WHEN (lower(sys_context('USERENV', 'CLIENT_IDENTIFIER')) like '%hr%')
DECLARE
lcommand varchar(200);
BEGIN
EXECUTE IMMEDIATE 'alter session set tracefile_identifier=''From_Trigger''';
EXECUTE IMMEDIATE 'alter session set statistics_level=ALL';
EXECUTE IMMEDIATE 'alter session set max_dump_file_size=UNLIMITED';
EXECUTE IMMEDIATE 'alter session set events ''10046 trace name context forever, level 12 : 10053 trace name context forever, level 1''';
EXECUTE IMMEDIATE 'alter session set "_fix_control"="6765823:off"';
insert into sys.trigger_test values (sys_context('USERENV', 'CLIENT_IDENTIFIER'),sysdate,'LOGON');
commit;
END set_trace_logon_test1;
/
Trigger created.
================================================================================
[oracle@node4 ~]$
[oracle@node4 ~]$ sqlplus scott
SQL*Plus: Release 11.2.0.1.0 Production on Wed Mar 11 12:00:55 2015
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Enter password:
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>
Checking and confirming at the trace location
[oracle@node4 trace]$ ls -ltr |tail -2
-rw-r----- 1 oracle oinstall 131747 Mar 11 12:00 prd_ora_6289_From_Trigger.trm
-rw-r----- 1 oracle oinstall 477686 Mar 11 12:00 prd_ora_6289_From_Trigger.trc
