RDBMS version: 11.2.0.4
OS : Oracle Linux 6.4
SKU_DTL_BKP is table which is 155GB in size. It is a clone created using CTAS from another table.
To create the primary key on this table I ran the below command . But, it was taking more than an hour.
alter table SKU_DTL_BKP add constraint PK_SKU_DTL_BKP primary key (SKU_ID) parallel 20 ;
I assume that implicit INDEX creation was causing the delay. So, I cancelled the above command ran the below command which only creates the index just to test. It finished within 5 minutes !!
create index SKU_DTL_BKP_IND1 ON SKU_DTL_BKP (sku_id) nologging parallel 20 ;
What is so special about Primary key creation which was taking too long ?
Unable to create EXPLAIN PLAN issue:
Unfortunately I couldn't generate explain plan for the ALTER TABLE SKU_DTL_BKP ADD CONSTRAINT command.
As shown below, It kept saying Invalid SQL statement although it is a valid ALTER TABLE statement.
SQL> explain plan for alter table SKU_DTL_BKP add constraint PK_SKU_DTL_BKP primary key (SKU_ID) parallel 20 ;
explain plan for alter table SKU_DTL_BKP add constraint PK_SKU_DTL_BKP primary key (SKU_ID) parallel 20
*
ERROR at line 1:
ORA-00900: invalid SQL statement
So, I tried to get the explain plan from Shared pool too. But, I got the below error
SQL> select * from table(dbms_xplan.display_cursor('7k9ym67wsaaaw'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------
SQL_ID 7k9ym67wsaaaw, child number 0
alter table UIN_DETA
NOTE: cannot fetch plan for SQL_ID: 7k9ym67wsaaaw, CHILD_NUMBER: 0
Please verify value of SQL_ID and CHILD_NUMBER;
It could also be that the plan is no longer in cursor cache (check v$sql_plan)