Re: problem deleting datafile

On Jul 3, 4:31 pm, max.font..._at_yahoo.com wrote:
> 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

Add REUSE to the tempfile clause, this should do the trick. And you don't need to explicitly COMMIT after DDL - Oracle does this implicitly. Initial size could be larger, too.

Regards,

   Vladimir M. Zakharychev
   N-Networks, makers of Dynamic PSP(tm)    http://www.dynamicpsp.com Received on Thu Jul 03 2008 - 09:10:09 CDT