Relocating indexes

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

Moderators: Snowy, thx4allthefish

Post Reply
nimo
Posts: 183
Joined: Fri Sep 26, 2003 1:23 pm

Relocating indexes

Post by 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

Snowy
Posts: 28790
Joined: Mon Oct 21, 2002 2:33 pm
Location: 3.1415926535

Re: Relocating indexes

Post by 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.

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

Re: Relocating indexes

Post by 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

nimo
Posts: 183
Joined: Fri Sep 26, 2003 1:23 pm

Re: Relocating indexes

Post by 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

Snowy
Posts: 28790
Joined: Mon Oct 21, 2002 2:33 pm
Location: 3.1415926535

Re: Relocating indexes

Post by 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.

Post Reply