Simple Oracle SQL question

[vince_laurent]

I am trying to do the following:

SQL> alter table C303 move tablespace PSAPPOOLD;

SQL> alter index 'C303~0' rebuild tablespace PSAPPOOLI online;

But I get an error on the index.

Digging I can 'see' the table but not the index.

SQL> connect sapr3/********

Connected.

SQL> desc c303

Name Null? Type

-

---

-

---

-

---

MANDT NOT NULL VARCHAR2(3)

KAPPL NOT NULL VARCHAR2(2)

KSCHL NOT NULL VARCHAR2(4)

KTOPL NOT NULL VARCHAR2(4)

KVSL1 NOT NULL VARCHAR2(3)

SAKN1 NOT NULL VARCHAR2(10)

SAKN2 NOT NULL VARCHAR2(10)

SQL> desc 'c303~0'

SP2-0565: Illegal identifier.

SQL> desc "c303~0"

ERROR:

ORA-04043: object "c303~0" does not exist

When doing stuff with SAP indexes what is the correct SQL to use when sap uses a "~" in the name?

Thanks!

[Former Member · Accepted solution]

Hi,

Just to clarify the rules for quoting object names in Oracle: the tilde "~", which SAP uses in its naming convention for indexes is an extraneous character for Oracle. If such a character is part of the object name then you must quote it. With a quoted name however the name becomes case-dependent and you must give it in upper case.

alter table sapr3.MYTAB ...   --> OK
alter table sapr3.mytab ...  --> OK
alter table sapr3."MYTAB" --> OK
alter table sapr3."mytab"  --> not ok!

alter index sapr3.mytab~z1  --> not ok (tilde is extraneous character)
alter index sapr3."MYTAB~Z1" --> OK
alter index sapr3."mytab~z1" --> not ok (wrong case)

Regards,

Mark

[fidel_vales · Accepted solution]

and, in addition, desc does not work for indexes:

> DESC[RIBE] {[schema.]object[@db_link]}

> Lists the column definitions for the specified table, view or synonym, or the specifications for the specified function or procedure.

[Former Member · Accepted solution]

Yes, but Joe Bo had already mentioned that fact in his reply.

My preferred method of looking at index information inside SQLPLUS is with GET_DDL, e.g.

select dbms_metadata.get_ddl('INDEX','C303~0','SAPR3') from dual;

This gives you all the details, including uniqueness and storage parameters.

Rgds,

Mark

[fidel_vales · Accepted solution]

>

> Yes, but Joe Bo had already mentioned that fact in his reply. 

ups, missed that

>

> My preferred method of looking at index information inside SQLPLUS is with GET_DDL, e.g.

>

>

select dbms_metadata.get_ddl('INDEX','C303~0','SAPR3') from dual;

> This gives you all the details, including uniqueness and storage parameters.

yes, that is the best one, thanks for pointing it out

[Former Member]

Hi Vince

I suppose you can describe a Table in Oracle database but you CAN NOT describe an index it would retun the error of an invalid Identifier.You may find all the information about the Index in the views DBA_INDEXES,ALL_INDEXES or USER_INDEXES

which ever is relevent to you as far as your privledges are concerned.

Hope this would resolve ur issue

Regards

Aditya Pathak

[Former Member]

In addition to what Lars pointed out, there seems to be one more misconception:

SQL> desc "c303~0"
ERROR:
ORA-04043: object "c303~0" does not exist

sqlplus command desc or describe is not meant for indexes. You can't describe an index.

If you are looking for information about indexes, you may either use SAP tools like SE14 or SQL queries like

select index_name from user_indexes
where table_name = 'C303'
/

Here table and index names are case sensitive by the way, it's 'C303' and not 'c303'.

hope this helps

[lbreddemann]

> SQL> desc 'c303~0'

> SP2-0565: Illegal identifier.

> SQL> desc "c303~0"

> ERROR:

> ORA-04043: object "c303~0" does not exist

>

> When doing stuff with SAP indexes what is the correct SQL to use when sap uses a "~" in the name?

You've to enclose the index name in " ":

alter index sapr3."C303~0" rebuild online tablespace ... ;

Anyhow, all this does BRSPACE for you and in constrast to ALTER TABLE MOVE brspace uses the DBMS_REDEFINITION package for online reorgs.

That means, you the table is not locked during the reorg like it is with ALTER TABLE MOVE.

regards,

Lars

[vince_laurent]

I just tried brtools and got the following error:

BR1180E No appropriate index found for function 'idrebuild'

So why does the index show up in DB02 if it doesn't exist?

[anindya_bose]

Did you try it from SE14-->Enter the Table Name>Indexes ( select the index)-->Adjust Database

You can also try RSORAISQ and RSANAORA reports

[vince_laurent]

I looked at it but they do not seem to let me move the index to a new tablespace - that is what I need to do.

[anindya_bose]

Is it a partitioned index? I am not sure ..You can check the links below

http://www.sc.ehu.es/siwebso/KZCC/Oracle_10g_Documentacion/server.101/b10759/statements_1008.htm

http://www.stanford.edu/dept/itss/docs/oracle/10g/server.101/b10739/partiti.htm#i1009216