Home » RDBMS Server » Performance Tuning » Index Issues
Index Issues [message #235363] Sat, 05 May 2007 06:43 Go to next message
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.


Re: Index Issues [message #235368 is a reply to message #235363] Sat, 05 May 2007 06:51 Go to previous messageGo to next message
Michel Cadot
Messages: 68722
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

Re: Index Issues [message #235373 is a reply to message #235368] Sat, 05 May 2007 07:08 Go to previous messageGo to next message
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.


Re: Index Issues [message #235374 is a reply to message #235363] Sat, 05 May 2007 07:11 Go to previous messageGo to next message
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
Re: Index Issues [message #235389 is a reply to message #235374] Sat, 05 May 2007 08:18 Go to previous messageGo to next message
Michel Cadot
Messages: 68722
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

Re: Index Issues [message #235494 is a reply to message #235373] Sun, 06 May 2007 21:27 Go to previous message
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;
Previous Topic: INDEX rebuild with ONLINE Option
Next Topic: DATABASE DOWN WHEN MANY USERS LOG IN
Goto Forum:
  


Current Time: Thu Dec 12 09:01:31 CST 2024