Forum Stats

  • 3,875,697 Users
  • 2,266,948 Discussions
  • 7,912,304 Comments

Discussions

Truncating subpartition

Arunadeepti Jakka
Arunadeepti Jakka Member Posts: 62 Blue Ribbon
edited Jun 5, 2020 4:20AM in SQL & PL/SQL

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

Tagged:
Arunadeepti Jakka

Best Answer

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 19,953 Red Diamond
    edited Jun 4, 2020 2:10PM Answer ✓
    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

Answers

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 19,953 Red Diamond
    edited Jun 4, 2020 2:10PM Answer ✓
    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
  • Arunadeepti Jakka
    Arunadeepti Jakka Member Posts: 62 Blue Ribbon
    edited Jun 4, 2020 2:23PM

    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
    Solomon Yakobson Member Posts: 19,953 Red Diamond
    edited Jun 4, 2020 2:27PM

    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.

    Arunadeepti Jakka
  • Jonathan Lewis
    Jonathan Lewis Member Posts: 10,117 Blue Diamond
    edited Jun 5, 2020 4:20AM

    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

    Arunadeepti Jakka