Tablespace creation error.

[Former Member]

Hi,

we have created the temporary tablespace PSAPTEMP1 for the index creation.

CREATE TEMPORARY TABLESPACE "PSAPTEMP" TEMPFILE '/archive/temp' SIZE 51200M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 10240K;

ALTER USER PSAPPRD TEMPORARY TABLESPACE PSAPTEMP1

but when we use the 2nd query to use for the index creation, but for the index creation it is using the old tablespace PSAPTEMP.

could any one suggest how do we make use of the newly created tablespace for the index creation.

This we are doing live PRD system with the down time.

My number: is 0 98861 45002 else you give me your number I will call you.

please do the needful.

Many Thanks,

[Former Member]

Oops, you are right. Thank you for this remark. I thought to be sure that only users having assigned SYSTEM as temporary tablespace get the new default temporary tablespace. But in fact all users get the new temporary tablespace (and you can just overwrite this setting afterwards with an explicite ALTER USER TEMPORARY TABLESPACE command).

Sorry for the wrong information provided.

[Former Member]

The previous answer is partially wrong: Defining PSAPTEMP1 as default temporary tablespace will not change anything for users with an already assigned temporary tablespace.

I think the problem was that the index creation was performed under the SYS user and so PSAPTEMP1 had to be assigned to the SYS user.

[KayKa]

Hi Martin,

i think you are wrong. alter database default temporary ... will affected all users:

< sys:op33@syse > select USERNAME, DEFAULT_TABLESPACE, TEMPORARY_TABLESPACE from dba_users where username = 'SDN';

USERNAME DEFAULT_TABLESPACE TEMPORARY_TABLESPACE

-

-

---

-

-

---

-

-

---

SDN SYSTEM PSAPTEMP

1 Zeile wurde ausgewählt.

< sys:op33@syse > alter database DEFAULT TEMPORARY TABLESPACE TS_USER_temp;

Datenbank wurde geändert.

< sys:op33@syse > select USERNAME, DEFAULT_TABLESPACE, TEMPORARY_TABLESPACE from dba_users where username = 'SDN';

USERNAME DEFAULT_TABLESPACE TEMPORARY_TABLESPACE

-

-

---

-

-

---

-

-

---

SDN SYSTEM TS_USER_TEMP

1 Zeile wurde ausgewählt.

regards

Kay

[Former Member]

Your create temporary tablespace has PSAPTEMP but not PSAPTEMP1..can you check?

You can also set this as the default temporary tablespact at database level using

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE PSAPTEMP1;

[Former Member]

Hi,

if you use SYSTEM you should:

ALTER USER SYSTEM TEMPORARY TABLESPACE PSAPTEMP1;

if you SYS ( / AS SYSDBA)

ALTER USER SYS TEMPORARY TABLESPACE PSAPTEMP1;

Thanks

[Former Member]

Hello,

Which user you use to create the index?.

which Oracle version you use?

Thanks