Re: problem deleting datafile
Date: Thu, 3 Jul 2008 10:03:49 -0700 (PDT)
Message-ID: <ac3020b3-8008-4941-a0ad-a7a5505faf42@z66g2000hsc.googlegroups.com>
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;