Re: problem deleting datafile

From: Michael Austin <maustin_at_firstdbasource.com>
Date: Thu, 03 Jul 2008 19:51:31 -0500
Message-ID: <Drebk.525$cn7.427@flpi145.ffdc.sbc.com>

max.fontain_at_yahoo.com wrote:

> On 3 Jul, 15:10, "Vladimir M. Zakharychev"
> <vladimir.zakharyc..._at_gmail.com> wrote:

>> 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- Hide quoted text -
>>
>> - Show quoted text -

> thanks to the group for some excellent advice!
> I have removed the drop and commit statements and made the size
> bigger.
> Any further enhancements possible?
> CREATE TABLESPACE getreports
> DATAFILE '/u02/oradata/GETREPS/getreports_DATA' SIZE 500M
> AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED
> DEFAULT STORAGE (
>                 INITIAL 256K
>                 NEXT   128K
>                 MINEXTENTS 1
>                 MAXEXTENTS UNLIMITED
>                 PCTINCREASE 0)
>                 LOGGING
>                 ONLINE;
> 
> -- Temporary tablespace.
> CREATE TEMPORARY TABLESPACE getreports_TEMP
> TEMPFILE '/u02/oradata/GETREPS/getreports_TEMP_DATA' SIZE 32M REUSE
> AUTOEXTEND ON NEXT 32M MAXSIZE UNLIMITED;

IIRC, the docs state that when using LMT (local managed tablespace - also IIRC the default tablespace type in 10g) these parameters have been deprecated and are ignored - I typically do not include these because it may cause errors in a future version:

               NEXT   128K
               MINEXTENTS 1
               MAXEXTENTS UNLIMITED
               PCTINCREASE 0

Received on Thu Jul 03 2008 - 19:51:31 CDT