6 Replies Latest reply: Oct 4, 2012 3:42 PM by BSalesRashid RSS

    Problem with table

    956715
      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
          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
            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
              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
                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
                  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
                    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,