Home » RDBMS Server » Server Administration » How to clean corrupted dba_tables (oracle 11.2.0.3.0, aix)
How to clean corrupted dba_tables [message #576486] Tue, 05 February 2013 09:24 Go to next message
metabaron
Messages: 13
Registered: September 2010
Junior Member
Hi all

I have a table enrty in dba_tables:

SQL> select OWNER,TABLE_NAME from dba_tables where TABLE_NAME='Viep_Porteur_Etudiant';

OWNER TABLE_NAME
------------------------------ ------------------------------
IEP_PESSAC Viep_Porteur_Etudiant

But this tables doesn't exist then I can't drop this table:

SQL> desc IEP_PESSAC.Viep_Porteur_Etudiant;
ERROR:
ORA-04043: objet IEP_PESSAC.Viep_Porteur_Etudiant inexistant


SQL> drop table IEP_PESSAC.Viep_Porteur_Etudiant;
drop table IEP_PESSAC.Viep_Porteur_Etudiant
*
ERREUR à la ligne 1 :
ORA-00942: Table ou vue inexistante


How can I clean dba_table? I suppose that that's happened because the user tablespace was full when he tried to create this table...
Thanks for help

[Updated on: Tue, 05 February 2013 09:25]

Report message to a moderator

Re: How to clean corrupted dba_tables [message #576487 is a reply to message #576486] Tue, 05 February 2013 09:27 Go to previous messageGo to next message
BlackSwan
Messages: 22800
Registered: January 2009
Senior Member
> drop table IEP_PESSAC.Viep_Porteur_Etudiant;

not as above; but as below


drop table IEP_PESSAC."Viep_Porteur_Etudiant";

shown is classic problem when double quote marks are used to CREATE TABLE
Re: How to clean corrupted dba_tables [message #576488 is a reply to message #576487] Tue, 05 February 2013 09:30 Go to previous messageGo to next message
metabaron
Messages: 13
Registered: September 2010
Junior Member
I didn't know about that. Thank you
Re: How to clean corrupted dba_tables [message #576531 is a reply to message #576488] Wed, 06 February 2013 00:06 Go to previous messageGo to next message
Littlefoot
Messages: 19635
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
In other words: try NOT to create tables (columns, views, whatever) using mixed case because you'll have to use double quotes all the time and pay attention to EXACTLY type these names (because "Viep_Porteur_Etudiant" will be different from "Viep_porteur_etudiant" or "viep_porteur_etudiant" etc.).
Re: How to clean corrupted dba_tables [message #576538 is a reply to message #576531] Wed, 06 February 2013 00:58 Go to previous messageGo to next message
Michel Cadot
Messages: 59166
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And verify V$RESERVED_WORDS before naming to not use any word that is in it.

Regards
Michel

[Edit: typo]

[Updated on: Wed, 06 February 2013 03:29]

Report message to a moderator

Re: How to clean corrupted dba_tables [message #576550 is a reply to message #576538] Wed, 06 February 2013 03:23 Go to previous messageGo to next message
metabaron
Messages: 13
Registered: September 2010
Junior Member
Sure, but I think you cant prevent users to do whatever they want in their schema. I only noticed that because I have DBMS_STATS.GATHER_TABLE_STATS scripts based on dba_tables that failed on that table.
Re: How to clean corrupted dba_tables [message #576551 is a reply to message #576550] Wed, 06 February 2013 03:33 Go to previous message
Michel Cadot
Messages: 59166
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes, you can prevent from this publishing programming standard.
And you can also prevent from this in the database with a DDL trigger which will check the name of all created objects.

Regards
Michel

[Updated on: Wed, 06 February 2013 03:34]

Report message to a moderator

Previous Topic: Oracle dbms_job hangs for Long time
Next Topic: change port number
Goto Forum:
  


Current Time: Mon Sep 22 16:08:22 CDT 2014

Total time taken to generate the page: 0.08926 seconds