OraFAQ Forum: Server Administration » How to clean corrupted dba

Home » RDBMS Server » Server Administration » How to clean corrupted dba_tables (oracle 11.2.0.3.0, aix)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

How to clean corrupted dba_tables [message #576486]

Tue, 05 February 2013 09:24

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

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

Report message to a moderator

Re: How to clean corrupted dba_tables [message #576488 is a reply to message #576487]

Tue, 05 February 2013 09:30

metabaron
Messages: 13
Registered: September 2010

Junior Member

I didn't know about that. Thank you

Report message to a moderator

Re: How to clean corrupted dba_tables [message #576531 is a reply to message #576488]

Wed, 06 February 2013 00:06

Littlefoot
Messages: 21823
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.).

Report message to a moderator

Re: How to clean corrupted dba_tables [message #576538 is a reply to message #576531]

Wed, 06 February 2013 00:58

Michel Cadot
Messages: 68729
Registered: March 2007
Location: Saint-Maur, France, https...

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

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.

Report message to a moderator

Re: How to clean corrupted dba_tables [message #576551 is a reply to message #576550]

Wed, 06 February 2013 03:33

Michel Cadot
Messages: 68729
Registered: March 2007
Location: Saint-Maur, France, https...

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:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Jan 12 23:23:04 CST 2025