Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Dec 2008
    Posts
    99

    Unanswered: strange delete operation

    Hello
    Mistakenly my friend truncated abc_sub_event table.Because there isnot field called subscription_id in abc_subscription table.could it be possible in oracle?
    Thanks

    delete from abc_sub_event where subscription_id in
    (select subscription_id from abc_subscription where number = 123 and code = 88);

  2. #2
    Join Date
    Dec 2007
    Posts
    253
    The delete would fail, and it would certainly not cause a truncate.

  3. #3
    Join Date
    Oct 2009
    Posts
    5

    Unhappy

    Hi, I'm the friend. The query did not fail, which is where the puzzle starts. Allow me to explain (as short as possible):

    There are two tables, a parent and a child, linked by a foreign key:

    ---------------
    foo_sub_event
    ---------------
    event_id (PK, number)
    ..
    subscription_id (FK -> foo_subscription.foo_subscription_id


    ---------------
    foo_subscription
    ---------------
    foo_subscription_id (PK, number)
    msisdn
    shortcode
    ...


    A very simple, ordinary set-up.


    The query i ran is literally

    delete from foo_sub_event where subscription_id in
    (select subscription_id from foo_subscription where msisdn = [number] and shortcode = [code]);

    as you'll notice in the sub-query part, i'm trying to query the 'subscription_id' column from the 'foo_subscription' table, which is not present; the column is called 'foo_subscription_id'. If i run the sub-query separately, it fails (column does not exist). If I run the whole query, it runs without error and starts deleting every record in the foo_sub_event table as if the query would have been:

    delete from foo_sub_event where subscription_id in
    (select subscription_id from foo_sub_event);


    thus ignoring the FROM and WHERE clause of the sub query completely. This 'behaviour' caused a lot of data loss for us.

    I ran the query using SQL Developer. Not the best tool, I agree, but I believe the SQL is sent unaltered to the Oracle server and it simply accepts it and starts running.

    The following query has the same result:

    delete from foo_sub_event where subscription_id in
    (select subscription_id from dual);


    Is this a feature or a bug?
    Last edited by boumabouma; 10-01-09 at 10:09.

  4. #4
    Join Date
    Dec 2007
    Posts
    253
    Please post a copy and paste of exactly what you did (Like the following: )
    Code:
    set echo on
    
    set feed on
    
    drop table xtr purge
    
     drop table xtr succeeded.
    create table xtr as select level l, sysdate + level dte
    from dual connect by level <= 100
    
    create table succeeded.
    delete from xtr where l in (select non_exist_col from xtr)
    
    
    Error starting at line 9 in command:
    delete from xtr where l in (select non_exist_col from xtr)
    Error at Command Line:9 Column:35
    Error report:
    SQL Error: ORA-00904: "NON_EXIST_COL": invalid identifier
    00904. 00000 -  "%s: invalid identifier"
    *Cause:    
    *Action:
    delete from xtr where l in (select non_exist_col from dual)
    
    
    Error starting at line 11 in command:
    delete from xtr where l in (select non_exist_col from dual)
    Error at Command Line:11 Column:35
    Error report:
    SQL Error: ORA-00904: "NON_EXIST_COL": invalid identifier
    00904. 00000 -  "%s: invalid identifier"
    *Cause:    
    *Action:

  5. #5
    Join Date
    Dec 2008
    Posts
    99
    it is not sqlplus interface, it is oracle sql developer he used.

  6. #6
    Join Date
    Oct 2009
    Posts
    5
    The only thing I did is that one query:

    delete from foo_sub_event where subscription_id in
    (select subscription_id from foo_subscription where msisdn = [number] and shortcode = [code]);

    There was no output by the server, it just started running until I killed it, ie. I clicked on the abort button in the SQL Developer client. Then I ran some other queries on other tables and committed. After that, a lot of records (not all, the query wasn't done yet) were gone.

  7. #7
    Join Date
    Dec 2007
    Posts
    253
    Quote Originally Posted by sunsail
    it is not sqlplus interface, it is oracle sql developer he used.
    Yes, I'm well aware of that. The output that I pasted was also generated from SQL developer.

    Feel free to ignore my request. I will do likewise. Good luck.

  8. #8
    Join Date
    Oct 2009
    Posts
    5
    We do not intend to ignore your request. On the contrary, your help is very much appreciated. It's just that there was no output to my knowing, and if there was I don't have it anymore; all this took place yesterday. I ran that one query and killed it after a couple of minutes. No output from the server of any form.

    Could this be a bug of some form?

  9. #9
    Join Date
    Dec 2007
    Posts
    253
    Then I ran some other queries on other tables and committed. After that, a lot of records (not all, the query wasn't done yet) were gone.
    This is not how oracle works. If you issue a delete, then cancel the transaction halfway through, the entire transaction will rollback and all rows will be as before as if nothing had happened. This is the basics of Oracle transaction control. Something alse has happened/been done that you are not telling me. Either because you are not aware that something else happened, or that you do not think it relevant, which is why I asked you to replicate what you did and post it here. Without that, I have nothing to add.

  10. #10
    Join Date
    Oct 2009
    Posts
    5
    I see your point.

    I think you should be able to run the following query on any (recent) Oracle database with the SYS user:

    select * from USER$ where user# in
    (select user# from VIEW$);

    These are system tables and should be present and usually filled, right?

    running only the sub query wont work:

    select user# from VIEW$;

    error:
    Error starting at line 4 in command:
    select user# from VIEW$
    Error at Command Line:4 Column:7
    Error report:
    SQL Error: ORA-00904: "USER#": ongeldige ID (yup dutch...)
    00904. 00000 - "%s: invalid identifier"
    *Cause:
    *Action:

    So my question is: Why does the sub-query alone not work but the whole thing does work, returning every row in the USER$ table?

  11. #11
    Join Date
    Dec 2007
    Posts
    253
    1. I don't have SYS access (and quite rightly so)
    2. Please tell me you did not execute your original query as SYS.
    3. SYS should never, ever, ever be used to execute 'normal' actions. SYS is a very special type of user and should only ever be used to perform very high level actions (such as Startup and Shutdown of the database.
    4. Rules associated with other users will not necessarily hold true for SYS.
    Run the following query as any user (other than SYS)
    Code:
    SELECT * 
    FROM ALL_USERS 
    WHERE USERNAME IN (SELECT OWNER 
                                  FROM USER_USERS)
    Same principle, these tables will be available in any Oracle DB with any access level. It will fail, as I said before.

  12. #12
    Join Date
    Feb 2009
    Posts
    62
    The behaviour of the DELETE is usually unexpected, but normal.

    IF you rewrite the query like this (explicitly referencing the source of each column[CODE]delete from foo_sub_event fse where fse.subscription_id in
    (select fs.subscription_id from foo_subscription fs where fs.msisdn = [number] and fs.shortcode =
    Code:
    );
    then you'll get the error you would expect.

    Without any table aliasing, oracle parses the query in the only way that it can:[CODE]delete from foo_sub_event fse where fse.subscription_id in
    (select fse.subscription_id from foo_subscription fs where fs.msisdn = [number] and fs.shortcode =
    Code:
    );
    Here's an example you can run yourself to demonstrate that rows deleted by a session that gets killed aren't commited. After you've started the commit, go to another session, wait for 30 seconds or so, and then kill the session that is running the delete.
    Code:
    SQL> create table test_075 (col_1 number);
    
    Table created.
    
    SQL> 
    SQL> insert into test_075 select level from dual connect by level <= 100;
    
    100 rows created.
    
    SQL> 
    SQL> create or replace trigger test_075_del
      2  before delete on test_075 for each row
      3  begin
      4     dbms_lock.sleep(2);
      5  end;
      6  /
    
    Trigger created.
    
    SQL> delete test_075;
    delete test_075
           *
    ERROR at line 1:
    ORA-00028: your session has been killed
    
    
    SQL> conn dev/dev@dev10g
    Connected.
    SQL> select count(*) from test_075;
    
      COUNT(*)
    ----------
           100

  13. #13
    Join Date
    Oct 2009
    Posts
    5
    @pablolee:

    agreed, I did not run my queries under the SYS user. The only reason I referred to the SYS user now is to use table I assumed all Oracle servers have. I am too much of a noob to know another way. The example you provide missed this very important fact:
    The column selected in the sub-query must exist in the query containing the sub query, but not in the table in the FROM of the sub-query.

    Thanks for the tips though

    @JRowbottom:

    I understand what you are saying with the alias addition. I figured this would solve the problem. I'm just very surprised to see this query working and what an unexpected destructive effect it can have.

    About the session killing: I did not kill the session, I stopped the query in SQL Developer. I'm not sure what kind of command that is towards the server. I continued using the session (ran other queries) and committed, thus making the delete permanent, leaving me feeling quite confused and stupid...

    Thank you both for your replies, it is much appreciated. I now understand this is not a bug, just unexpected behavior (for me anyway).

    Cheers

  14. #14
    Join Date
    Dec 2008
    Posts
    99
    Quote Originally Posted by JRowbottom
    The behaviour of the DELETE is usually unexpected, but normal.

    IF you rewrite the query like this (explicitly referencing the source of each column[CODE]delete from foo_sub_event fse where fse.subscription_id in
    (select fs.subscription_id from foo_subscription fs where fs.msisdn = [number] and fs.shortcode =
    Code:
    );
    then you'll get the error you would expect.

    Without any table aliasing, oracle parses the query in the only way that it can:[CODE]delete from foo_sub_event fse where fse.subscription_id in
    (select fse.subscription_id from foo_subscription fs where fs.msisdn = [number] and fs.shortcode =
    Code:
    );
    Here's an example you can run yourself to demonstrate that rows deleted by a session that gets killed aren't commited. After you've started the commit, go to another session, wait for 30 seconds or so, and then kill the session that is running the delete.
    Code:
    SQL> create table test_075 (col_1 number);
    
    Table created.
    
    SQL> 
    SQL> insert into test_075 select level from dual connect by level <= 100;
    
    100 rows created.
    
    SQL> 
    SQL> create or replace trigger test_075_del
      2  before delete on test_075 for each row
      3  begin
      4     dbms_lock.sleep(2);
      5  end;
      6  /
    
    Trigger created.
    
    SQL> delete test_075;
    delete test_075
           *
    ERROR at line 1:
    ORA-00028: your session has been killed
    
    
    SQL> conn dev/dev@dev10g
    Connected.
    SQL> select count(*) from test_075;
    
      COUNT(*)
    ----------
           100
    Missing alias usage looks meaningful ,but how about inner query? does "in" not have any affect?
    Even though alias is missing oracle must not proceed as it cannot find any matching data in "in" section,right?
    Thanks

  15. #15
    Join Date
    Feb 2009
    Posts
    62
    Even though alias is missing oracle must not proceed as it cannot find any matching data in "in" section,right?
    Given that the data has been deleted, patently not right.

    What the missing alias means is that oracle finds a match for the IN statment for each row it looks at, as each row looks at it's own value of subscription_id

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •