Truncating subpartition

Arunadeepti Jakka

Hi all ,

I have a table that has around 3 partitions [ A,B,C]  and in each partition I have 2 sub partitions.

I am inserting data into a sub partition  named ABC  , also at the same time  I am  truncating another sub partition DEF with below command. Both ABC and DEF are inside partition named A

ALTER TABLE  TABLENAME TRUNCATE SUBPARTITION DEF DROP STORAGE.

Is this valid ?

I am getting ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired.

Is this because that I cant do both at the same time . Please let me know.

Thanks

Solomon Yakobson

2727166 wrote:I am getting ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired.Is this because that I cant do both at the same time . Please let me know.

Yes, ALTER TABLE requires an exclusive lock.

SY.

Solomon Yakobson

2727166 wrote:I am getting ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired.Is this because that I cant do both at the same time . Please let me know.

Yes, ALTER TABLE requires an exclusive lock.

SY.

Arunadeepti Jakka

Hi Solomon,

Thankyou , Is there any alternate way to do this without locking the table ?

I cant use delete as data is very huge and it is taking longer time .

Thanks

Solomon Yakobson

You can try increasing DDL_LOCK_TIMEOUT:

alter session set DDL_LOCK_TIMEOUT = ...

but it doesn't guarantee your session will acquire a lock withing that DDL_LOCK_TIMEOUT.

SY.

Jonathan Lewis

Are you using partition-extended syntax to insert into the subpartition ?

Which version are you on ?

Demo from 12.2.0.1

Session 1:

SQL> select table_name, partition_name, subpartition_name, num_rows from user_tab_subpartitions;

TABLE_NAME          PARTITION_NAME        SUBPARTITION_NAME        NUM_ROWS

-------------------- ---------------------- ---------------------- ----------

PT_COMPOSITE_1      P2                    SYS_SUBP44402                  50

PT_COMPOSITE_1      P2                    SYS_SUBP44403                  49

PT_COMPOSITE_1      P2                    SYS_SUBP44404                100

PT_COMPOSITE_1      P2                    SYS_SUBP44405                200

PT_COMPOSITE_1      P3                    SYS_SUBP44406                100

PT_COMPOSITE_1      P3                    SYS_SUBP44407                150

PT_COMPOSITE_1      P3                    SYS_SUBP44408                  50

PT_COMPOSITE_1      P3                    SYS_SUBP44409                100

...

SQL> insert into pt_composite_1 subpartition (SYS_SUBP44408) select * from temp;

50 rows created.

-- note 44408 is from partition p3

-- go to session 2 and truncate another subpartition of p3

SQL> alter table  pt_composite_1 truncate subpartition SYS_SUBP44406;

Table truncated.

No problem.

If you are inserting data into the table "knowing" that it will go into a specific segment but without identifying that segment explicitly Oracle has to be much more aggressive about locking.

Regards

Jonathan Lewis