This discussion is archived
6 Replies Latest reply: Oct 4, 2012 1:42 PM by BSalesRashid RSS

Problem with table

956715 Newbie
Currently Being Moderated
I have a table that a user tried to add a column. During the execution, he lost connection with database.
Now we can do a select but when we try to drop the table or do an update we receive the following error message.

Erro de SQL: ORA-00942: table or view does not exist


I already did a shutdown / startup on database but the problem continues.

someone have a clue?
Im using dba user, so , its not a grant related problem.

Edited by: thpereira on 03/10/2012 17:08

Edited by: thpereira on 03/10/2012 17:09
  • 1. Re: Problem with table
    BSalesRashid Explorer
    Currently Being Moderated
    Hi,
    Check if the table exists:

    select owner,table_name from dba_tables where table_name='&TABLE';

    Maybe you just have to find who's the table's owner.

    Regards,
  • 2. Re: Problem with table
    Veeresh.S Pro
    Currently Being Moderated
    plz post your select statement u used to view the table.

    as dba user did you tried update/drop with <object_owner>.<object_name>
  • 3. Re: Problem with table
    956715 Newbie
    Currently Being Moderated
    the select its simple

    select * from schema.table

    and the update is

    update schema.table
    set columnx = 123
    where columny = 1111

    and table exists, the problemn ocurred when a user tried to add a column.
    the select on dba_objects find the table, the status of this objects is valid.
  • 4. Re: Problem with table
    BSalesRashid Explorer
    Currently Being Moderated
    Please, show the statement that was used to add a column in this table.

    Please, also show us what your statements returns.
  • 5. Re: Problem with table
    956715 Newbie
    Currently Being Moderated
    i already did...
    but

    i just hide the schema name and the table name

    the select is:
    select * from schema.table

    the update is:
    update schema.table
    set columnx = 123
    where columny = 1111

    i tried with uppercase and lowercase...

    the problem is not the syntax or if tabke exists or some kind of schema name, i've made another table using select * into tableb and worked, and the same statement for udate worked too.
    the table exist, i can see in dictionary, i can see the data but i cant do any DDL.

    the problem is... this table have locked for DDL for some reason and now i cant update or drop or delete, but select is working...
    im thinking in a kind o bug, becaouse the v$locked_objects dont show anything so, the table is not locked for any transaction.
  • 6. Re: Problem with table
    BSalesRashid Explorer
    Currently Being Moderated
    thpereira,
    you are not providing the information necessary to find out where is the 'bug'.

    Show the result of the query below:
    select owner, table_name from dba_tables where table_name='&table';

    I don't want you to gimme examples(like you did twice above), i want the real name of owner, and real name of table.
    I want the real query that you used, not an example.

    If you can't give me this information, i can't know where is the error. Help me to help you.

    Regards,

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points