Home » SQL & PL/SQL » SQL & PL/SQL » FIXED ORA-02180 when Creating a Temporary Tablespace
FIXED ORA-02180 when Creating a Temporary Tablespace [message #241621] Wed, 30 May 2007 03:43 Go to next message
t.summerfield
Messages: 39
Registered: March 2007
Location: UK
Member
I am using Oracle 9i on a Win 2003 machine.

I have just performed a recovery of all my datafiles and have successfully opened the database using ALTER DATABASE OPEN;

I have been using Mohammad Taj's blog as a guide and am now at the stage of creating a new temp tablespace and dropping the old one (step 8.).

I use the following SQL,
CREATE TEMPORARY TABLESPACE temp02 TEMPFILE 'c:\oracle\oradata\bcs\temp02.dbf' SIZE 100mb AUTOEXTEND OFF EXTENT MANAGEMENT LOCAL;
However it returns the following error:
ERROR at line 1:
ORA-02180: invalid option for CREATE TABLESPACE


Having googled the error it just says to make sure I am specifying a vaild option such as a datafile. I thought that is what I am doing!?

Could anyone please point out where I am going wrong and suggest a solution. It would be greatly appreciated.

Regards
Toby

[Updated on: Wed, 30 May 2007 03:53]

Report message to a moderator

Re: FIXED ORA-02180 when Creating a Temporary Tablespace [message #241627 is a reply to message #241621] Wed, 30 May 2007 04:00 Go to previous messageGo to next message
Michel Cadot
Messages: 64098
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Temporary tablespace is always "extent management local", remove this part.

Regards
Michel
Re: FIXED ORA-02180 when Creating a Temporary Tablespace [message #241636 is a reply to message #241627] Wed, 30 May 2007 04:14 Go to previous messageGo to next message
t.summerfield
Messages: 39
Registered: March 2007
Location: UK
Member
It has been fixed now.

The problem was I had put 100mb when it should have been 100m Embarassed
Re: FIXED ORA-02180 when Creating a Temporary Tablespace [message #244370 is a reply to message #241636] Tue, 12 June 2007 08:36 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

Yes, it was a problem becuase create tablespace syntax in my blog is tested.

SQL> ed
Wrote file afiedt.buf

1 CREATE TEMPORARY TABLESPACE TEMP2
2 TEMPFILE 'C:\TEMP02.DBF' SIZE 20M
3 AUTOEXTEND OFF
4* EXTENT MANAGEMENT LOCAL
5 /

Tablespace created.

PS : Sorry for very let response i didn't check this thread.
Re: FIXED ORA-02180 when Creating a Temporary Tablespace [message #244378 is a reply to message #244370] Tue, 12 June 2007 09:05 Go to previous messageGo to next message
t.summerfield
Messages: 39
Registered: March 2007
Location: UK
Member
No problem!

As I said above it was just a syntax error.

I have now managed to successfully restore the whole database from a user managed backup. Cool

Although the official documentation provides a great insight into the theory behind the process; your blog was very helpful as a step by step guide - I did have to add a couple of steps Razz

Many thanks!

Toby
Re: FIXED ORA-02180 when Creating a Temporary Tablespace [message #244383 is a reply to message #244378] Tue, 12 June 2007 09:16 Go to previous message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

You're Welcome !!!
Previous Topic: adding 1 hour to a time field in PL/SQL
Next Topic: Replace Leading Spaces
Goto Forum:
  


Current Time: Fri Dec 02 12:32:44 CST 2016

Total time taken to generate the page: 0.11074 seconds