Fix the ORA-12801, ORA-08103, and ORA-06512 in Oracle RAC

Step-by-step methods for moving partitioned and non-partitioned tables and indexes in Oracle RAC and standalone setups, with online and offline options . 

Prerequisites :-

• Verify the size of the tables and indexes, both partitioned and non-partitioned, in relation to the maintained space. 
• Verify all of the tablespace and make sure there is enough space where we move the table twice.
• It's crucial to correctly confirm the partition and owner while moving across tablespaces.
• Verify the temporary tablespace and undo tablespace.
• Use RMAN and Export to create a backup of the partitioned and non-partitioned tables along with their indexes for safety.
• Keep an eye on the database level session; if there are any blocking sessions or wait events, take the appropriate action and also check alert_log.

Implementation: Step by Step :- 

STEP 01 :-  Check tablespace and verify using below command.

set pages 100 lines 123
set colsep "|"
set time on
set timing on
alter session force parallel query;
select '| ' || tablespace_name "| TABLESPACE_NAME",Total_Space,(total_space - free_space) Used_Space, Free_Space,'  '||lpad(round((total_space - free_space)*100/Total_Space,0),2,'0') || '  |' "Used% |" from (
select a.tablespace_name, sum(a.bytes)/1024/1024 total_space, b.remaning_space free_space from dba_data_files a,
(select tablespace_name, sum(bytes)/1024/1024 remaning_space from dba_free_space group by tablespace_name) b
where a.tablespace_name = b.tablespace_name group by a.tablespace_name,b.remaning_space) order by 4 desc
/

STEP 02 :-  It displays a certain tablespace's content, including all segments, their owners, names, types, and sizes.

set pages 200 lines 200
set time on timing on
set numwidth 15
col owner for a20
col SEGMENT_NAME for a30
col PARTITION_NAME for a20
select owner,segment_name,partition_name,segment_type, bytes/1024/1024/1024, tablespace_name from dba_segments where tablespace_name=<'&tablespace_name'> order by 5;

STEP 03 :- It displays a single table and indexes the content of all segments, including their owner, name, type, and size

set lines 1000 pages 800
col segment_name for a20
col PARTITION_NAME for a20
col OWNER for a30,
select segment_name, segment_type,owner ,partition_name,bytes/1024/1024/1024,tablespace_name from dba_segments where partition_name like '<partition_name>%<&partition_name%>' order by 1;

STEP 04 :-  Set below parameter for improve performance once activity done then revert back to it's old value.

set time on timing on

set echo on 

set lines 250 pages 5000 

set numwidth 15 

col SEGMENT_NAME for a15 

col OWNER for a20

col PARTITION_NAME for a20 

select * from v$log where status='CURRENT'; 

alter session set resumable_timeout=1800;

(resumable_timeout :-
If there is not enough room in the tablespace for that operation, this argument specifies the time in seconds for which the statements will be suspended. Those transactions will start up again after we add space to that tablespace).

alter session set db_file_multiblock_read_count=128; 

( db_file_multiblock_read_count :- The maximum number of blocks that can be read in a single I/O operation to storage during a sequential scan can be specified by the administrator using the database configuration parameter db_file_multiblock_read_count. A big number for db_file_multiblock_read_count optimizes full table scans for workloads such as online analytical processing, data warehousing, and decision support systems. Full table scans are optimized by larger values since they enable the database to read more blocks.)

STEP 05 :- Check the  table and index it's degree should be 1.

select degree from dba_tables where table_name=<'&table_name'>;

select degree from dba_indexes where index_name = <'&index_name '>;

STEP 06 :- check unusable indexes status using below command.

select index_name from dba_indexes where status = 'UNUSABLE';
select index_name,partition_name from dba_ind_partitions where status = 'UNUSABLE';

Note :- Whether it is the same or a different tablespace, the move command is always used when performing object movement activity tables. After a table migration is successful, its index becomes unusable. Therefore, it can utilize the rebuilt command when we move the index, whether it is in the same tablespace or a different tablespace.

STEP 07 :- For improve performance and consuming less time.

ALTER SESSION FORCE PARALLEL QUERY PARALLEL 16;​
ALTER SESSION FORCE PARALLEL DML PARALLEL 16;​
ALTER SESSION FORCE PARALLEL DDL PARALLEL 16;​

For NON-PARTITION TABLE and INDEXES :

[ Using  OFFLINE Movement]  :- 

STEP 08 :- TABLE

alter table <OWNER.TABLE_NAME> move TABLESPACE <TABLESPACE_NAME> parallel 16; 
e.g.
alter table GANESH.User_tab_01 move TABLESPACE TS_DATA_01 parallel 16;

STEP 09 :-  Once table move successfully then run NOPARALLEL query.

alter table <OWNER.TABLE_NAME> noparallel; 
e.g.
alter table OWNER.User_tab_01 noparallel;

STEP 10 :- INDEX

alter index <OWNER.INDEX_NAME> rebuild parallel 16 ;    ( If you rebuild in same tablespace no need to mention tablespace name. )
alter index <OWNER.INDEX_NAME> rebuild  TABLESPACE <TABLESPACE_NAME> parallel 16 ;   ( If you rebuild in different tablespace .) 
e.g.
alter index GANESH.User_ind_01 rebuild parallel 16 ;
alter index GANESH.User_ind_01 rebuild TABLESPACE  TS_DATA_02  parallel 16 ;

STEP 11 :-  Once Index move successfully then run NOPARALLEL query.

alter index  <OWNER.INDEX_NAME> noparallel; 
e.g.
alter index GANESH.User_ind_01 noparallel ;

NOTE : If one table contained one or more indexes then you have to rebuild all the indexes.

Using the command below, check the temporary segment of both table and index :- 

SELECT SUM(BYTES/1024/1024/1024) FROM DBA_SEGMENT WHERE SEGMENT_TYPE='TEMPORARY' ;

' OR ' 

[ Using  ONLINE Movement]  :- 

STEP 08 :- TABLE

alter table <OWNER.TABLE_NAME> move TABLESPACE <TABLESPACE_NAME> parallel 16 online; 
e.g.
alter table GANESH.User_tab_01 move TABLESPACE TS_DATA_01 parallel 16 online ;

STEP 09 :-  Once table move successfully then run NOPARALLEL query.

alter table <OWNER.TABLE_NAME> noparallel; 
e.g.
alter table OWNER.User_tab_01 noparallel;

STEP 10 :- INDEX

• alter index <OWNER.INDEX_NAME> rebuild parallel 16 online ; 
• alter index <OWNER.INDEX_NAME> rebuild  TABLESPACE <TABLESPACE_NAME> parallel 16 online ;  

e.g.

• alter index GANESH.User_ind_01 rebuild parallel 16 online;
• alter index GANESH.User_ind_01 rebuild TABLESPACE  TS_DATA_02  parallel 16  online;

STEP 11 :-  Once Index move successfully then run NOPARALLEL query.

alter index  <OWNER.INDEX_NAME> noparallel 
e.g.
alter index GANESH.User_ind_01 noparallel ;

NOTE :  During ONLINE keyword table can be access by the user but user can't perform any DDL and DML Operations.

For PARTITION TABLE and INDEXES :

SEGMENT_NAME  SEGMENT_TYPE    OWNER   PARTITION_NAME SIZE_GB TABLESPACE_NAME
------------- --------------- ------- -------------- ------- ------------- 
PSGI          TABLE PARTITION GANESH PSGI_15        159     TS_DATA_01
PSGIPK_01     INDEX PARTITION GANESH PSGIPK_01_15   83       TS_INDX
PSGIPK_02     INDEX PARTITION GANESH PSGIPK_02_15   60      TS_INDX

[ Using  OFFLINE Movement]  :- 

STEP 08 :- TABLE 

alter table <OWNER.SEGMENT_NAME> move partition <PARTITION_NAME> TABLESPACE <TABLESPACE_NAME> parallel 16;
e.g.
alter table GANESH.PSGI move partition PSGI_15 TABLESPACE TS_DATA_02 parallel 16;

STEP 09 :-  Once partition table move successfully then run NOPARALLEL query.

alter table <OWNER.SEGMENT_NAME> noparallel; 
e.g.
alter table GANESH.PSGI noparallel;

STEP 10 :- INDEX

• alter index <OWNER.SEGMENT_NAME> rebuild partition <PARTITION_NAME> TABLESPACE <TABLESPACE_NAME> parallel 16 ; ( If you rebuild in same tablespace no need to mention tablespace name. )
• alter index <OWNER.SEGMENT_NAME> rebuild partition <PARTITION_NAME>  parallel 16 ; ( If you rebuild in different tablespace .) 

e.g.

• alter index GANESH.PSGIPK_01 rebuild partition PSGIPK_01_15 TABLESPACE TS_INDX_02   parallel 16 ;
  
• alter index GANESH.PSGIPK_01 rebuild partition PSGIPK_01_15 parallel 16 ; 

STEP 11 :-  Once Index move successfully then run NOPARALLEL query.

alter index <OWNER.SEGMENT_NAME> noparallel ;
e.g.

alter index GANESH.PSGIPK_01 noparallel ;
alter index GANESH.PSGIPK_02 noparallel ;

' OR ' 

 [ Using  ONLINE Movement ]  : - 

STEP 08 :- TABLE 

alter table <OWNER.SEGMENT_NAME> move partition <PARTITION_NAME> TABLESPACE <TABLESPACE_NAME> parallel 16 online;

e.g.
alter table GANESH.PSGI move partition PSGI_15 TABLESPACE TS_DATA_02 parallel 16 online;

STEP 09 :-  Once partition table move successfully then run NOPARALLEL query.

alter table <OWNER.SEGMENT_NAME> noparallel; 
e.g.
alter table GANESH.PSGI noparallel;

STEP 10 :- [ INDEX ]

• alter index <OWNER.SEGMENT_NAME> rebuild partition <PARTITION_NAME> TABLESPACE <TABLESPACE_NAME> parallel 16 online ; ( If you rebuild in same tablespace no need to mention tablespace name. )
• alter index <OWNER.SEGMENT_NAME> rebuild partition <PARTITION_NAME>  parallel 16 online ; ( If you rebuild in different tablespace .) 

e.g.

• alter index GANESH.PSGIPK_01 rebuild partition PSGIPK_01_15 TABLESPACE TS_INDX_02   parallel 16 online;
  
• alter index GANESH.PSGIPK_01 rebuild partition PSGIPK_01_15 parallel 16 online ; 

STEP 11 :-  Once Index move successfully then run NOPARALLEL query.

alter index <OWNER.SEGMENT_NAME> noparallel ;
e.g.

alter index GANESH.PSGIPK_01 noparallel ;
alter index GANESH.PSGIPK_02 noparallel ;

POST CHECK : - 

Check the tablespace when the activity is finished.

Use an index to confirm that the partition and non-partition tables are functioning correctly.

Verify that the previous tablespace is now free.

Use an index to check the partition and non-partition tables.

Verify the size of the partitioned and non-partitioned tables.

Return every parameter to its initial value.

Verify the partition and non-partition tables, and make sure the degree is 1.

Compile the table's statistics and use its index to partition the data. 

--------------------------------- Thanks for Reading ----------------------------------

If you face any issues related to database administration, feel free to write down your queries in the comments. I will do my best to resolve your issues and get back to you as soon as possible.