 Need help.. Urgent [message #229803] |
Tue, 10 April 2007 01:43  |
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 #229839 is a reply to message #229821] |
Tue, 10 April 2007 02:46  |
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 #229858 is a reply to message #229849] |
Tue, 10 April 2007 03:27 |
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 |
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 #229897 is a reply to message #229880] |
Tue, 10 April 2007 04:55 |
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 #230015 is a reply to message #229926] |
Tue, 10 April 2007 09:39 |
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 |
 |
Mahesh Rajendran
Messages: 10707
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. 
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 #230088 is a reply to message #229803] |
Tue, 10 April 2007 18:17 |
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 #230125 is a reply to message #230105] |
Tue, 10 April 2007 23:47 |
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.. 
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
|
|
|
|
|
|
| Re: Need help.. Urgent [message #230139 is a reply to message #230125] |
Wed, 11 April 2007 00:55 |
 |
Michel Cadot
Messages: 68686
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 #230331 is a reply to message #230156] |
Wed, 11 April 2007 09:48 |
joy_division
Messages: 4963
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.
|
|
|
|
|
|
|
|
) 1 Vote
] 
Blog
Search
Help
Members
Register
Login
Home
