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: 26766
Registered: January 2009
Location: SoCal
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: 21806
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: 68625
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: 68625
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: Fri Mar 29 06:34:53 CDT 2024