OraFAQ Forum: Performance Tuning

Home » RDBMS Server » Performance Tuning » Index Issues

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

Index Issues [message #235363]

Sat, 05 May 2007 06:43

thiyagaraj
Messages: 41
Registered: August 2006

Member

Hi
I have two issues on indexes.

Issue #1

Can I drop the index online?If so,can anyone tell me the syntax?If I do so on the live server,what could be the effects that will affect the transaction?

Or else,can I first make the index unusable:

Alter index <name> unusable;

And the drop normal:

Drop index <name>;

If I do like this what will be the effect of the transactions currently running on the DB.

Please suggest me with the safer steps to be followed.

Issue #2

I have droped the index and created index using this query:

Create index <name> tablespace <name>;
The result is index created.

After I have checked the dba_indexes for this table,but it shown no indexes.

Again I have tried to craete the index with the same name,the result is "name already used".

I used different name and tried ,result is "the columns already indexed".

I tried to drop the index ,the result is "specified index does not exist".

I dont understand what happens.Someone suggest me with your experience.

Report message to a moderator

Re: Index Issues [message #235368 is a reply to message #235363]

Sat, 05 May 2007 06:51

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

Senior Member
Account Moderator

1/ Why do you mean online?
Just use drop index.

2/ Post your query on dba_indexes you surely made a type.
Btw, if you really used "Create index <name> tablespace <name>;" you got an error, it is not a valid syntax.

ALWAYS post your full Oracle version.
ALWAYS post your real statements, not just "I did that and it answered that". This only means you THINK you did that but actually we have no way to see if it is really what you did.

Regards
Michel

Report message to a moderator

Re: Index Issues [message #235373 is a reply to message #235368]

Sat, 05 May 2007 07:08

thiyagaraj
Messages: 41
Registered: August 2006

Member

Hi

Oracle version: 9.2.0.8.0
OS: Solaris 10

I have loged on as SYS user.
Here is the real queries:

SQL> drop index PGIDSK.pgi_txn_neft;
Index dropped.

SQL> create index PGI_TXN_Q1 on PGIDSK.pgi_transaction(BANK_ID,BANK_REF_NO,MERC_ID)tablespace index_data;

Index created

SQL>select index_name,status from dba_indexes where owner='LUKE' and table_name='LM_LUKE';
No rows selected.

Report message to a moderator

Re: Index Issues [message #235374 is a reply to message #235363]

Sat, 05 May 2007 07:11

thiyagaraj
Messages: 41
Registered: August 2006

Member

Sorry.

The last query is wrong.I have wrongly typed .Here is the original:

SQL>select index_name,status from dba_indexes where owner='PGIDSK' and table_name='PGI_TRANSACTION';

THANKS

Report message to a moderator

Re: Index Issues [message #235389 is a reply to message #235374]

Sat, 05 May 2007 08:18

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

Senior Member
Account Moderator

This query is also wrong.
Correct one is:
select owner,index_name,status from dba_indexes where table_owner='PGIDSK' and table_name='PGI_TRANSACTION';

Regards
Michel

Report message to a moderator

Re: Index Issues [message #235494 is a reply to message #235373]

Sun, 06 May 2007 21:27

Teddyboy
Messages: 8
Registered: September 2005

Junior Member

thiyagaraj wrote on Sat, 05 May 2007 07:08

SQL> create index PGI_TXN_Q1 on PGIDSK.pgi_transaction(BANK_ID,BANK_REF_NO,MERC_ID)tablespace index_data;

you shouldn't use SYS user to create index, or you create index like this:

create index PGIDSK.PGI_TXN_Q1 on PGIDSK.pgi_transaction(BANK_ID,BANK_REF_NO,MERC_ID)tablespace index_data;

Report message to a moderator

Previous Topic:	INDEX rebuild with ONLINE Option
Next Topic:	DATABASE DOWN WHEN MANY USERS LOG IN

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Nov 27 00:02:09 CST 2024