This website is not affiliated with, sponsored by, or approved by SAP AG.

Relocating indexes

Basis (Basis Technology Modules: Basis Component/System Administration, GUIs)

Moderators: Snowy, thx4allthefish

Relocating indexes

Postby nimo » Mon May 20, 2013 9:17 pm

Hi
I reorg'd tablespace PSAPSR3 into PSAPSR3TMP. The old tablespace still contains 3 objects, displayed below.

select index_name from dba_indexes where tablespace_name='PSAPSR3';
INDEX_NAME
------------------------------
ACLPERMIS~010
SPROXDAT~OB2
TSTCT~001

I am trying to move these indexes to the new tablespace, but it's giving me an error:

SQL> ALTER INDEX "TSTCT~001" REBUILD TABLESPACE PSAPSR3TMP ;
ALTER INDEX "TSTCT~001" REBUILD TABLESPACE PSAPSR3TMP
*
ERROR at line 1:
ORA-01418: specified index does not exist

I tried to add the schema name, but still not working. Any suggestion?
nimo
nimo
 
Posts: 183
Joined: Fri Sep 26, 2003 1:23 pm

Re: Relocating indexes

Postby Snowy » Tue May 21, 2013 8:45 am

instead, try using BRSPACE to do this.

the tilde ~ is a special character and your issues might be caused by this.

BRSPACE might be able to treat this better.
SapFans Moderator

Search: http://www.sapfans.com/forums/search.php
Notes: http://service.sap.com/notes
Help: http://help.sap.com
Rules: http://www.sapfans.com/forums/viewtopic.php?t=344127
Snowy
 
Posts: 28767
Joined: Mon Oct 21, 2002 2:33 pm
Location: 3.1415926535

Re: Relocating indexes

Postby blueteeth » Tue May 21, 2013 1:10 pm

Please go ahead with Snowy's suggestion.

But I am intrigued by your tablespace name: PSAPSR3TMP.

While SAP treats its PSAPTEMP (as part of its definition it is permanent tablespace, much to astonishment of non SAP Oracle DBAs), is your PSAPSR3TMP a permanent tablespace or temporary tablespace?

Indexes should reside in permanent tablespaces.

BT
blueteeth
 
Posts: 499
Joined: Sat Apr 05, 2008 12:22 pm

Re: Relocating indexes

Postby nimo » Tue May 21, 2013 9:04 pm

Thanks everyone
The issue resolved after adding the schema owner and also use upper case (it failed for me in lower case)

ALTER INDEX SAPSR3."TSTCT~001" REBUILD TABLESPACE PSAPSR3TMP ;

PSAPSR3TMP is only a tablespace used to reorg the PSAPSR3, and then I moved the files back to PSAPSR3.
nimo
nimo
 
Posts: 183
Joined: Fri Sep 26, 2003 1:23 pm

Re: Relocating indexes

Postby Snowy » Mon May 27, 2013 3:51 pm

are you completely reorganizing PSAPSR3?

If so, when PSAPSR3 is completely empty, you can drop it and use Oracle 10 and above functionality to rename an existing tablespace.
SapFans Moderator

Search: http://www.sapfans.com/forums/search.php
Notes: http://service.sap.com/notes
Help: http://help.sap.com
Rules: http://www.sapfans.com/forums/viewtopic.php?t=344127
Snowy
 
Posts: 28767
Joined: Mon Oct 21, 2002 2:33 pm
Location: 3.1415926535


Return to Basis

Who is online

Users browsing this forum: No registered users and 5 guests





loading...


This website is not affiliated with, sponsored by, or approved by SAP AG.