problem deleting datafile

From: <max.fontain_at_yahoo.com>
Date: Thu, 3 Jul 2008 05:31:48 -0700 (PDT)
Message-ID: <c4e25171-ff1d-4b66-86cd-7fc9af411c63@2g2000hsn.googlegroups.com>

Hello,
I have a problem with a script that ran OK with 9i but which does not work with 10G
Here is the output:
SQL> @tmp
Tablespace dropped.
Tablespace created.
Commit complete.
DROP TABLESPACE getreports_TEMP INCLUDING CONTENTS AND DATAFILES
*

ERROR at line 1:
ORA-00959: tablespace 'getreports_TEMP' does not exist CREATE TEMPORARY TABLESPACE getreports_TEMP
*

ERROR at line 1:
ORA-01119: error in creating database file '/u02/oradata/GETREPS/getreports_TEMP_DATA' ORA-27038: created file already exists

And here is the script that caused the error: DROP TABLESPACE getreports INCLUDING CONTENTS AND DATAFILES; CREATE TABLESPACE getreports
DATAFILE '/u02/oradata/GETREPS/getreports_DATA' SIZE 32M AUTOEXTEND ON NEXT 32M MAXSIZE 256M
DEFAULT STORAGE (

                INITIAL 256K
                NEXT   128K
                MINEXTENTS 1
                MAXEXTENTS UNLIMITED
                PCTINCREASE 0)
                LOGGING
                ONLINE;

COMMIT;

• Temporary tablespace. DROP TABLESPACE getreports_TEMP INCLUDING CONTENTS AND DATAFILES; CREATE TEMPORARY TABLESPACE getreports_TEMP TEMPFILE '/u02/oradata/GETREPS/getreports_TEMP_DATA' SIZE 32M AUTOEXTEND ON NEXT 32M MAXSIZE UNLIMITED;

COMMIT Any obvious solution? Plaese help!
BTW do the sizes look OK for a database that will grow to say 40GB?

TIA Max

Tablespace dropped.

Tablespace created.

Commit complete.

DROP TABLESPACE getreports_TEMP INCLUDING CONTENTS AND DATAFILES
*

ERROR at line 1:
ORA-00959: tablespace 'getreports_TEMP' does not exist

CREATE TEMPORARY TABLESPACE getreports_TEMP
*

ERROR at line 1:
ORA-01119: error in creating database file '/u02/oradata/GETREPS/getreports_TEMP_DATA' ORA-27038: created file already exists Received on Thu Jul 03 2008 - 07:31:48 CDT