how to drop temp tsp

[rommel]

Hi,

I want to drop my temporary tablespace "TEMP" coz i
accendentally removed its datafile counterpart.
sql> drop tablespace temp;
ora-12906: can not drop default temporary tablespace

Whats the right command?

Thanks

[pando]

create a new one, make it default and drop the old one

[adewri]

First you need to create a new temporary tablespace. Then do alter database to set the new one to default temporary tablespace and then drop the old one.

SQL> create temporary tablespace temp1 tempfile 'c:\oracle\oradata\acme\temp1.dbf' size 10M;

Tablespace created.

SQL> alter database default temporary tablespace temp1;

Database altered.

SQL> drop tablespace temp including contents and datafiles;

Tablespace dropped.

HTH

[rommel]

Thanks,

I did all the above...but i make my temfile of size 200M.
CREATE TEMPORARY TABLESPACE temp2 TEMPFILE '/u03/oradata/sap/temp01.dbf'
SIZE 200M REUSE
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 16M;

But when i check the tablespaces in OEM im surprised that
it reflected:
temp2 TEMPORARY size(M)=0 used(M)=0 %used(M)=0

and when i created index on a table, i got ora-error 1652:
unable to extend temp segment by 2048 on tablespace TEMP2;

Is temp tablespace not autoextensible? I tried to make it
autoextend but it doesnt allow me.

[adewri]

Originally posted by rommel

Is temp tablespace not autoextensible? I tried to make it
autoextend but it doesnt allow me.

What command did you fire ? and what was the error ?

Why dont you read the link mentioned on the top obn how to ask question or just read this...

http://tkyte.blogspot.com/2005/06/ho...questions.html

We dont have a crystal ball to know what error or problem you have faced unless you explain them to us.

[rommel]

sorry,

I created a new temporary tablespace....

CREATE TEMPORARY TABLESPACE temp2 TEMPFILE '/u03/oradata/sap/temp01.dbf'
SIZE 200M REUSE
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 16M;

tablespace created.

Then...

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP2;

database altered.

Then...

CREATE INDEX EMP_NDX ON EMP(EMPNO);

ora-error 1652: unable to extend temp segment by 2048 on tablespace TEMP2;

ALTER TABLESPACE TEMP2 AUTOEXTEND ON;

ora-32773: operation not supported for smallfile tablespace temp2.


It seems that my new temporary tablespace is not working or my
database has no permission on it.

Thanks again

[adewri]

Originally posted by rommel

ALTER TABLESPACE TEMP2 AUTOEXTEND ON;

ora-32773: operation not supported for smallfile tablespace temp2.

Try this and see.

alter database tempfile '/u03/oradata/sap/temp01.db' autoextend on maxsize 1000M;

Read some documentation that will help you in long run. All these commands can be found in database administrators guide and SQL reference.