Home » RDBMS Server » Server Administration » Need help.. Urgent  () 1 Vote
icon8.gif  Need help.. Urgent [message #229803] Tue, 10 April 2007 01:43 Go to next message
Dipali Vithalani
Messages: 278
Registered: March 2007
Location: India
Senior Member

dear all,
i have some problem regarding index in our database.
when i fire the query :
select * from all_indexes where table_name like 'PARA';
i get the following index:
index name : BIN$G9QHFTx8iILgQAB/AQBvkQ==$0
it is in tablespace 'APP_INDEX'.
status = Valid.

The problem is : when i try to drop this index with command
drop index BIN$G9QHFTx8iILgQAB/AQBvkQ==$0;

the error comes like following:
ORA-00953: missing or invalid index name.

i afraid that the original index ( named PARA_X ) is corrupted..

the database is up for 24 hours..

So please guide me how should i drop this index.
Re: Need help.. Urgent [message #229806 is a reply to message #229803] Tue, 10 April 2007 01:47 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

HI,
Are you try with quotes

drop index "BIN$G9QHFTx8iILgQAB/AQBvkQ==$0";


Regards
Taj
Re: Need help.. Urgent [message #229808 is a reply to message #229803] Tue, 10 April 2007 01:49 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Try: drop index "BIN$G9QHFTx8iILgQAB/AQBvkQ==$0"; (with quotes)
or purge recyclebin; to... empty your recyclebin.

Regards
Michel

Re: Need help.. Urgent [message #229814 is a reply to message #229803] Tue, 10 April 2007 01:59 Go to previous messageGo to next message
Dipali Vithalani
Messages: 278
Registered: March 2007
Location: India
Senior Member

Hi michel,
Thanks for such a quick reply...
->We try for drop index... with "<index name>" but the same error, that 'index doesn't exists".. Sad
-> we purged the recyclebin successfully, but after that we fire the same query on all_indexes for table_name="PARA", and i got the same result.. ie that index..

I exactly can't get, what's the problem...
Please help..
Re: Need help.. Urgent [message #229816 is a reply to message #229806] Tue, 10 April 2007 02:01 Go to previous messageGo to next message
Dipali Vithalani
Messages: 278
Registered: March 2007
Location: India
Senior Member

hi taj, we tried with both ' and ", but the same msg, that the index doesn't exist. but when we query all_indexes, it is listed ..
Re: Need help.. Urgent [message #229819 is a reply to message #229816] Tue, 10 April 2007 02:11 Go to previous messageGo to next message
Dipali Vithalani
Messages: 278
Registered: March 2007
Location: India
Senior Member

we can't even rename this index..
error code : ORA-02243 Embarassed
Re: Need help.. Urgent [message #229821 is a reply to message #229819] Tue, 10 April 2007 02:16 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please, copy and paste what you've done (and format it).
Regards
Michel

Re: Need help.. Urgent [message #229839 is a reply to message #229821] Tue, 10 April 2007 02:46 Go to previous messageGo to next message
Dipali Vithalani
Messages: 278
Registered: March 2007
Location: India
Senior Member

hi..
--->
SQL> select index_name from all_indexes where table_name like 'PARA';
result:
INDEX_NAME
------------------------------
BIN$G9QHFTx8iILgQAB/AQBvkQ==$0

--->
SQL> drop index "BIN$G9QHFTx8iILgQAB/AQBvkQ==$0";
result : error: ORA-01418: specified index does not exist

---> SQL> ALTER INDEX "BIN$G9QHFTx8iILgQAB/AQBvkQ==$0" RENAME TO PARA_X;
result: error : ORA-01418: specified index does not exist



Re: Need help.. Urgent [message #229849 is a reply to message #229839] Tue, 10 April 2007 03:00 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

Oracle Version ?
OS ?

Please post.

Regards
Taj
Re: Need help.. Urgent [message #229855 is a reply to message #229839] Tue, 10 April 2007 03:17 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
vithalani_dipali wrote on Tue, 10 April 2007 09:46
hi..
--->
SQL> select index_name from all_indexes where table_name like 'PARA';
result:
INDEX_NAME
------------------------------
BIN$G9QHFTx8iILgQAB/AQBvkQ==$0

--->
SQL> drop index "BIN$G9QHFTx8iILgQAB/AQBvkQ==$0";
result : error: ORA-01418: specified index does not exist

---> SQL> ALTER INDEX "BIN$G9QHFTx8iILgQAB/AQBvkQ==$0" RENAME TO PARA_X;
result: error : ORA-01418: specified index does not exist


OK, but you still didn't show us what happens when you
SQL> purge recyclebin;

Recyclebin purged.

SQL> select index_name from all_indexes where table_name like 'PARA';

Re: Need help.. Urgent [message #229858 is a reply to message #229849] Tue, 10 April 2007 03:27 Go to previous messageGo to next message
Dipali Vithalani
Messages: 278
Registered: March 2007
Location: India
Senior Member

version : 10.1.0.3
os : RHEL AS 4.0

----------------------------------------------------------------

Actually, the index is perhaps corrupted..
I think so because, the name of index was PARA_X, which is now bin$.. Generally, when we flashback the dropped table, its index is renamed like such name.. But we have never dropped the table..!
Other thing is, it's entry is shown as a result of query or all_indexes. Means there is some entry in data dictonary regarding this index. but it does'n allow to reference it(ie for drop, rename.. nothis is allowed.. error msg is shown that it doesn't exists).

I am trying to find hw to remove it's entry from data dictionary so that it is not displayed in all_indexes or anywhere.. (Means to drop it)

Thanks...
Re: Need help.. Urgent [message #229862 is a reply to message #229858] Tue, 10 April 2007 03:30 Go to previous messageGo to next message
Dipali Vithalani
Messages: 278
Registered: March 2007
Location: India
Senior Member

SQL> purge recyclebin;

Recyclebin purged.

SQL> select index_name from all_indexes where table_name like 'PARA';

INDEX_NAME
------------------------------
BIN$G9QHFTx8iILgQAB/AQBvkQ==$0
Re: Need help.. Urgent [message #229880 is a reply to message #229862] Tue, 10 April 2007 04:04 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
purge recyclebin; empty only your recycle bin.
all_indexes gives indexes that are not yours.

Add user and owner to your query.

Regards
Michel

Re: Need help.. Urgent [message #229897 is a reply to message #229880] Tue, 10 April 2007 04:55 Go to previous messageGo to next message
Dipali Vithalani
Messages: 278
Registered: March 2007
Location: India
Senior Member

dear michel,
actually, owner user is say 'xyz' and the tablespace of the index is 'app_index' (as showed in query of all_indexes).

i am logged in as sysdba

"empty only your recyclebin"

Means..? how should i empty only mine?
Re: Need help.. Urgent [message #229907 is a reply to message #229897] Tue, 10 April 2007 05:09 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If owner is xyz and you are connected as sysdba (very very bad idea) then "purge recyclebin;" purges SYS recycle bin and not xyz one.
Each user has a recycle bin and "purge recyclebin" only empties his one (I should quote the statement in my previous post).

Regards
Michel

Re: Need help.. Urgent [message #229921 is a reply to message #229880] Tue, 10 April 2007 05:51 Go to previous messageGo to next message
aboodlardy
Messages: 80
Registered: November 2006
Location: India
Member
hi
Try purge tablespace command .
Re: Need help.. Urgent [message #229926 is a reply to message #229921] Tue, 10 April 2007 05:57 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Why not "try drop database"?
Re: Need help.. Urgent [message #230015 is a reply to message #229926] Tue, 10 April 2007 09:39 Go to previous messageGo to next message
Dipali Vithalani
Messages: 278
Registered: March 2007
Location: India
Senior Member

hey michel,
what do you mean by "delete the database?" Are u jocking.
It's the live production database yaar.
We can't even make it down...
Please, atleast do not jock in such matter.
I was thinking u a sincere and helpful friend..
This is not fair...
Re: Need help.. Urgent [message #230021 is a reply to message #230015] Tue, 10 April 2007 10:47 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
@vithalani_dipali
Before making assertion of not being fair or whatever,
please be advised that this is just a forum. Not a paid support service. Smile
Meanwhile, Michel was responding to abdoolardy. The said suggestion by abdoolardy will purge ALL THE OBJECTS in the tablespace. And if that was our intention, Michel's suggestion could make your life easier~.

[Updated on: Tue, 10 April 2007 10:48]

Report message to a moderator

Re: Need help.. Urgent [message #230022 is a reply to message #230015] Tue, 10 April 2007 10:47 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Dipali,

I was not joking about you. I was joking about Abood's answer which advice you to try to purge a whole tablespace recycle bin to delete only one object.
So I suggest droppping the whole database, so the object will also be dropped.

Btw, you didn't say if my previous answer helped you to understand your problem.

Regards
Michel
Re: Need help.. Urgent [message #230088 is a reply to message #229803] Tue, 10 April 2007 18:17 Go to previous messageGo to next message
harshad.gohil
Messages: 157
Registered: April 2007
Location: USA
Senior Member
Dipali,

I would suggest without dropping that database and tablespace best alternative is to take export of that table and drop with purge that table and import without index and create index on that tbale, it would affect your application so you can try this option during off-peak hours or week-ends.

Seems the respective index is dropped or corrupted. So you dont have any other alternative.


Regards,
Harshad
Re: Need help.. Urgent [message #230105 is a reply to message #230088] Tue, 10 April 2007 22:25 Go to previous messageGo to next message
aboodlardy
Messages: 80
Registered: November 2006
Location: India
Member
Hi
once again i have learn something valuable from this great forum.
Salute to Orafaq and its DBAs

thnks
ragards
Abood

[Updated on: Tue, 10 April 2007 22:29]

Report message to a moderator

Re: Need help.. Urgent [message #230124 is a reply to message #230105] Tue, 10 April 2007 23:42 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Abood,

You well done to erase your first sentence.
Common sense is not so common and many will do exactly what you post without any thought.

Regards
Michel

icon7.gif  Re: Need help.. Urgent [message #230125 is a reply to message #230105] Tue, 10 April 2007 23:47 Go to previous messageGo to next message
Dipali Vithalani
Messages: 278
Registered: March 2007
Location: India
Senior Member

hi frndz,

harshad, ur solution for importing that table is fine, but i think to apply at last, in case when i completely fail to find other option. as it will help me learn new thing as well as for using import solution, i have to make the tablespace offline.. so...

dear michel,
you didn't specify to whom u were answering.. so i just thought, u.. any ways, it was just misunderstanding.. which is now over.. Cool

I am still tring to find the solution, so that i can make that index working or drop and create the other one, so that i would not have to brind the ts offline..

Nice forum this is.. i am very happy to join this..
I put my confusion on this forum for first time, and so happy to get such a nice frndz, with a great helping nature..

Thanks to alll Cool
icon7.gif  Re: Need help.. Urgent [message #230136 is a reply to message #230125] Wed, 11 April 2007 00:50 Go to previous messageGo to next message
Dipali Vithalani
Messages: 278
Registered: March 2007
Location: India
Senior Member

hi frndz,
Cheers, at last the problem is solved.. Smile
I login as the owner user "xyz" and rename the index..
I couldn't be dropped because it was enforced by the oracle for the primary key constraint.. If we drop that constraint, that associated index will also be dropped by oracle..

Thanks a lot to all.. Smile
You all help me not only to solve the problem but also to be more confident.. ( As I have just completed study, and just put my first step in the live world of DBA..)

Thanks again... Razz

[Updated on: Wed, 11 April 2007 00:51]

Report message to a moderator

Re: Need help.. Urgent [message #230139 is a reply to message #230125] Wed, 11 April 2007 00:55 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Dipali,

I start to be lost where you are in your problem.

Can you connect as a DBA (not sysdba) and post the result of:
select owner, object_name, original_name, type from dba_recyclebin where name='<your object name>';
select owner, index_name, table_owner, table_name, table_type from dba_indexes where index_name='<your object name>';
select owner, segment_name, partition_name, segment_type from dba_segments where segment_name='<your object name>';
select owner, object_name, subobject_name, object_type from dba_objects where object_name='<your object name>';

Regards
Michel
Re: Need help.. Urgent [message #230156 is a reply to message #230139] Wed, 11 April 2007 01:46 Go to previous messageGo to next message
Dipali Vithalani
Messages: 278
Registered: March 2007
Location: India
Senior Member

can't get u..!
dear, my problem has been solved.
U need the result of these all queries.?
Re: Need help.. Urgent [message #230164 is a reply to message #230156] Wed, 11 April 2007 02:11 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
No, As I said: I'm lost where you are in your problem.
But if it is solved, it's OK.

Regards
Michel
Re: Need help.. Urgent [message #230331 is a reply to message #230156] Wed, 11 April 2007 09:48 Go to previous messageGo to next message
joy_division
Messages: 4644
Registered: February 2005
Location: East Coast USA
Senior Member
vithalani_dipali wrote on Wed, 11 April 2007 02:46
can't get u..!
dear, my problem has been solved.
U need the result of these all queries.?


And I am lost with the complete disregard for the english language here. I find it so hard to read gibberish IM speak.
Re: Need help.. Urgent [message #230335 is a reply to message #230331] Wed, 11 April 2007 10:11 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

Hi,
I think @OP need to go through below link
http://tkyte.blogspot.com/2006/01/im-speak.html


Regards
Taj
Re: Need help.. Urgent [message #230356 is a reply to message #230335] Wed, 11 April 2007 11:45 Go to previous message
joy_division
Messages: 4644
Registered: February 2005
Location: East Coast USA
Senior Member
Excellent Taj!
Previous Topic: Oracle10G
Next Topic: ODBC setting for oracle
Goto Forum:
  


Current Time: Fri Dec 09 17:29:48 CST 2016

Total time taken to generate the page: 0.27742 seconds