| ORA-14511: cannot perform operation on a partitioned object [message #668291] |
Fri, 16 February 2018 13:22  |
 |
daulat01
Messages: 62
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 #668390 is a reply to message #668292] |
Tue, 20 February 2018 23:22  |
|
daulat01
Messages: 62
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
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
