Home » RDBMS Server » Server Administration » ORA-14511: cannot perform operation on a partitioned object (Oracle 12c (12.1.0.1.0)- Solaris)
ORA-14511: cannot perform operation on a partitioned object [message #668291] Fri, 16 February 2018 13:22 Go to next message
daulat01
Messages: 55
Registered: May 2011
Location: Delhi
Member
Hello team,

can you please help me to move the lob column of a table on separate tablespace created for lob's.

I have tried to do it via the below approach.

SQL> ALTER TABLE CONN.TF_CONN_BUY MOVE LOB(TDATA) STORE AS (TABLESPACE CONN_LOBS);
ALTER TABLE CONN.TF_CONN_BUY MOVE LOB(TDATA) STORE AS (TABLESPACE CONN_LOBS)
*
ERROR at line 1:
ORA-14511: cannot perform operation on a partitioned object

can you please let me know the rules & procedure to do this task.

Regards,
Daulat

Re: ORA-14511: cannot perform operation on a partitioned object [message #668292 is a reply to message #668291] Fri, 16 February 2018 14:42 Go to previous messageGo to next message
Michel Cadot
Messages: 65978
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

You have to move partition by partition as each partition can have a different LOB tablespace.

https://docs.oracle.com/database/121/SQLRF/statements_3001.htm#SQLRF53230

Re: ORA-14511: cannot perform operation on a partitioned object [message #668390 is a reply to message #668292] Tue, 20 February 2018 23:22 Go to previous messageGo to next message
daulat01
Messages: 55
Registered: May 2011
Location: Delhi
Member
Hi Michel,

Thanks to give idea about the task. I have tested the activity on test env. successfully .

SQL> select D.OWNER, D.index_name, d.tablespace_name, I.index_type from DBA_LOBS D,DBA_INDEXES I where D.TABLE_NAME in ('TF_CONN_SITE','TF_CONN_BUY') AND D.index_name=I.INDEX_NAME;

OWNER INDEX_NAME TABLESPACE_NAME INDEX_TYPE
-----------------------------------------------------------------------------------------------------------------------
CONN SYS_IL0004803173C00007$$ CONN_S_PART_TABLES LOB
CONN SYS_IL0004805280C00003$$ CONN_S_PART_TABLES LOB

Note: Both tables TF_CONN_SITE & TF_CONN_BUY are having partion objects.

SQL> select TABLE_NAME,PARTITIONING_TYPE,DEF_TABLESPACE_NAME from dba_part_tables where table_name in ('TF_CONN_BUY', 'TF_CONN_SITE');

TABLE_NAME PARTITION DEF_TABLESPACE_NAME
----------------------------------------------------------------------------------------------------------------------
TF_CONN_BUY HASH CONN_S_PART_TABLES
TF_CONN_SITE HASH CONN_S_PART_TABLES

Select table_name, partition_name, tablespace_name from ALL_TAB_PARTITIONS where table_owner='CONN' and table_name='TF_CONN_BUY'

SQL> SELECT 'alter table ' || TABLE_OWNER || '.' || TABLE_NAME || ' move partition ' || partition_name || ' lob (' || COLUMN_NAME || ') store as (tablespace CONN_LOBS);'
FROM DBA_LOB_PARTITIONS WHERE TABLE_OWNER = 'CONN' AND TABLESPACE_NAME = 'CONN_S_PART_TABLES' AND SECUREFILE='NO';

'ALTERTABLE'||TABLE_OWNER||'.'||TABLE_NAME||'MOVEPARTITION'||PARTITION_NAME||'LOB('||COLUMN_NAME||')STORE AS(TABLESPACECONN_LOBS);'
------------------------------------------------------------------------------------------------------------------------------------- -----------------------------------------------------------------------------
alter table CONN.TF_CONN_BUY move partition TFCB_PARTITION_1 lob (DATA) store as (tablespace CONN_LOBS);
alter table CONN.TF_CONN_BUY move partition TFCB_PARTITION_2 lob (DATA) store as (tablespace CONN_LOBS);
alter table CONN.TF_CONN_BUY move partition TFCB_PARTITION_3 lob (DATA) store as (tablespace CONN_LOBS);
alter table CONN.TF_CONN_BUY move partition TFCB_PARTITION_4 lob (DATA) store as (tablespace CONN_LOBS);
alter table CONN.TF_CONN_BUY move partition TFCB_PARTITION_5 lob (DATA) store as (tablespace CONN_LOBS);

SQL> select owner, index_name, tablespace_name, status FROM dba_indexes WHERE status = 'UNUSABLE';

OWNER I TABLESPACE_NAME STATUS NDEX_NAME
-----------------------------------------------------------------------------------------------------------------
CONN CONN_S_INDEXES UNUSABLE PK_TF_CONN_BUY
CONN CONN_S_INDEXES UNUSABLE PK_TF_CONN_SITE

SQL> select index_name, status, partitioned from dba_indexes where table_name='TF_CONN_SITE';

INDEX_NAME STATUS PAR
-------------------------------------------------------------------------------------------------------------------
SYS_IL0004805280C00003$$ N/A YES
PK_TF_CONN_SITE UNUSABLE NO

SQL> select index_name, status, partitioned from dba_indexes where table_name='TF_CONN_BUY';

INDEX_NAME STATUS PAR
--------------------------------------------------------------------------------------------------------------------
SYS_IL0004803173C00007$$ N/A YES
PK_TF_CONN_BUY UNUSABLE NO
TFCB_USERID_CAMPAIGNID_IDX N/A YES

SELECT owner, index_name, tablespace_name FROM dba_indexes WHERE status = 'UNUSABLE';

SQL> select 'alter index '||owner||'.'||index_name||' rebuild;' from dba_indexes where status='UNUSABLE';

'ALTERINDEX'||OWNER||'.'||INDEX_NAME||'REBUILD;'
--------------------------------------------------------------------------------
alter index CONN.PK_TF_CONN_BUY rebuild;
alter index CONN.PK_TF_CONN_SITE rebuild;

SELECT index_owner, index_name, partition_name, subpartition_name, tablespace_name FROM dba_ind_SUBPARTITIONS WHERE status = 'UNUSABLE';

SQL> SELECT index_name, partition_name, tablespace_name FROM dba_ind_PARTITIONS WHERE status = 'UNUSABLE' and index_owner ='CONN';

INDEX_NAME TABLESPACE_NAME PARTITION_NAME
- ------------------------------------------------------------------------------------------------------
TFCB_USERID_CAMPAIGNID_IDX TFCB_PARTITION_99 CONN_S_PART_INDEXES
TFCB_USERID_CAMPAIGNID_IDX TFCB_PARTITION_98 CONN_S_PART_INDEXES


SQL> select ' alter index '||index_owner||'.'||index_name||' rebuild partition '||partition_name||';' from dba_ind_partitions where status='UNUSABLE';

'ALTERINDEX'||INDEX_OWNER||'.'||INDEX_NAME||'REBUILDPARTITION'||PARTITION_NAME||';'
------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------
alter index CONN.TFCB_USERID_CAMPAIGNID_IDX rebuild partition TFCB_PARTITION_46;
alter index CONN.TFCB_USERID_CAMPAIGNID_IDX rebuild partition TFCB_PARTITION_460;


Select user_lobs.index_name, index_type, tablespace_name from user_lobs, user_indexes where user_LOBS.table_name ='TF_CONN_BUY'

SELECT SEGMENT_NAME, SEGMENT_TYPE, OWNER, TABLESPACE_NAME FROM DBA_SEGMENTS WHERE TABLESPACE_NAME = 'CONN_LOBS' AND SEGMENT_TYPE LIKE 'LOB%' ORDER BY 1; Result: 1024


Re: ORA-14511: cannot perform operation on a partitioned object [message #668391 is a reply to message #668390] Wed, 21 February 2018 00:39 Go to previous message
Michel Cadot
Messages: 65978
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Thanks for your feedback and solution which will help future readers.

Previous Topic: ORA-1652: unable to extend temp segment by 32 in tablespace TEMP
Next Topic: Global Temp Table and UNDO Generation
Goto Forum:
  


Current Time: Mon Nov 19 11:44:43 CST 2018