1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Truncate table if no data

Discussion in 'SQL PL/SQL' started by jhonnyrip, Oct 4, 2017.

  1. jhonnyrip

    jhonnyrip Active Member

    Messages:
    29
    Likes Received:
    1
    Trophy Points:
    90
    Location:
    lansing
    I need sql or pl/sql to truncate table if there is no data . Please help me out
     
  2. ashokgoud.e

    ashokgoud.e Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    100
    Location:
    Hyderabad
    Why to truncate the table if there is no data in it?
    Or Do you want to drop the table if there is no data in it. Please clarify.

    Thanks
    Ashok
     
  3. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    739
    Likes Received:
    145
    Trophy Points:
    830
    Location:
    Russian Federation
    TRUNCATE TABLE

    N.B. DDL in PL/SQL is implicity of commits
     
  4. jhonnyrip

    jhonnyrip Active Member

    Messages:
    29
    Likes Received:
    1
    Trophy Points:
    90
    Location:
    lansing
    I am getting the following error
    ERROR at line 1:

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

    But, there is no data in the table. I want to check before I truncate
     
  5. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    739
    Likes Received:
    145
    Trophy Points:
    830
    Location:
    Russian Federation
    Additional : might use to v$lock_object
    for example:
    check who locked your table :
    Code (Text):

    select l.SID,l.type
    from  sys.all_objects o, v$lock l
    where
    --l.sid = sys_context('USERENV','SID') and
    --l.type IN ('TM' ,'TX') and
    o.object_id = l.id1
    AND o.OBJECT_NAME = 'TEST_TAB'
    AND o.OWNER = sys_context('USERENV','SESSION_USER');
     
    Code (Text):

    CREATE TABLE test_tab  AS  SELECT LEVEL ID FROM dual CONNECT BY LEVEL<=7;
     
    Code (Text):

    DECLARE
     i pls_integer;
    BEGIN
        SELECT 1 INTO i FROM test_tab WHERE ROWNUM = 1 FOR UPDATE NOWAIT ;
        EXECUTE IMMEDIATE 'TRUNCATE TABLE test_tab';
    EXCEPTION WHEN OTHERS THEN IF SQLCODE = -54 THEN dbms_output.put_line('Table is locked');END IF;
    END;
    /
     
    Code (Text):


    DECLARE
     v_sid pls_integer;
    BEGIN
      select l.sid
      INTO v_sid
      from  sys.all_objects o, v$lock l
      where
      --l.sid = sys_context('USERENV','SID') and
      --l.type IN ('TM' ,'TX') and
      o.object_id = l.id1
      AND o.OBJECT_NAME = 'TEST_TAB'
      AND o.OWNER = sys_context('USERENV','SESSION_USER')
      AND ROWNUM=1;

        EXECUTE IMMEDIATE 'TRUNCATE TABLE test_tab';
    EXCEPTION WHEN OTHERS THEN IF SQLCODE = -54 THEN dbms_output.put_line('Table is locked in session: '||v_sid);END IF;
    END;
    /
     
     
    Last edited: Oct 5, 2017
  6. jhonnyrip

    jhonnyrip Active Member

    Messages:
    29
    Likes Received:
    1
    Trophy Points:
    90
    Location:
    lansing
    is there way that I can check data if any before I issue truncate command ?
     
  7. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    739
    Likes Received:
    145
    Trophy Points:
    830
    Location:
    Russian Federation
    what is mean "I can check data if any"
     
  8. jhonnyrip

    jhonnyrip Active Member

    Messages:
    29
    Likes Received:
    1
    Trophy Points:
    90
    Location:
    lansing
    In other words, would like to only issue truncate table command if there is data in the table
     
  9. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    739
    Likes Received:
    145
    Trophy Points:
    830
    Location:
    Russian Federation
    what could be easier?
    you need to take the script of the first example and a bit to fix it for their own purposes...
    Code (Text):

    DECLARE
     i pls_integer;
    BEGIN
        SELECT 1 INTO i FROM YOUR_TABLE WHERE ROWNUM = 1 FOR UPDATE NOWAIT ;
        EXECUTE IMMEDIATE 'TRUNCATE TABLE YOUR_TABLE ';
    EXCEPTION
        WHEN no_data_found THEN dbms_output.put_line('Data not found!');
        WHEN OTHERS THEN IF SQLCODE = -54 THEN dbms_output.put_line('Table is locked');END IF;
    END;
    /
     
     
  10. jhonnyrip

    jhonnyrip Active Member

    Messages:
    29
    Likes Received:
    1
    Trophy Points:
    90
    Location:
    lansing
  11. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    739
    Likes Received:
    145
    Trophy Points:
    830
    Location:
    Russian Federation
    The most important thing is to figure it out as an example,understand what , and how, and where...
     
  12. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,541
    Likes Received:
    362
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Just because YOU can't see the data doesn't mean there isn't any data being inserted into that table by another session. You can't go by what YOU can see because you're not performing the inserts. This also explains WHY the table is locked.