Re: problem deleting datafile

On 3 Jul, 15:10, "Vladimir M. Zakharychev"
<vladimir.zakharyc...@[EMAIL PROTECTED]
> wrote:
> On Jul 3, 4:31=A0pm, max.font...@[EMAIL PROTECTED]
 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> @[EMAIL PROTECTED]
> > Tablespace dropped.
> > Tablespace created.
> > Commit complete.
> > DROP TABLESPACE getre****ts_TEMP INCLUDING CONTENTS AND DATAFILES
> > *
> > ERROR at line 1:
> > ORA-00959: tablespace 'getre****ts_TEMP' does not exist
> > CREATE TEM****ARY TABLESPACE getre****ts_TEMP
> > *
> > ERROR at line 1:
> > ORA-01119: error in creating database file
> > '/u02/oradata/GETREPS/getre****ts_TEMP_DATA'
> > ORA-27038: created file already exists
>
> > And here is the script that caused the error:
> > DROP TABLESPACE getre****ts INCLUDING CONTENTS AND DATAFILES;
> > CREATE TABLESPACE getre****ts
> > DATAFILE '/u02/oradata/GETREPS/getre****ts_DATA' SIZE 32M
> > AUTOEXTEND ON NEXT 32M MAXSIZE 256M
> > DEFAULT STORAGE (
> > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 INITIAL 256K
> > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 NEXT =A0 128K
> > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 MINEXTENTS 1
> > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 MAXEXTENTS UNLIMITED
> > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 PCTINCREASE 0)
> > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 LOGGING
> > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 ONLINE;
> > COMMIT;
>
> > -- Tem****ary tablespace.
> > DROP TABLESPACE getre****ts_TEMP INCLUDING CONTENTS AND DATAFILES;
> > CREATE TEM****ARY TABLESPACE getre****ts_TEMP
> > TEMPFILE '/u02/oradata/GETREPS/getre****ts_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 getre****ts_TEMP INCLUDING CONTENTS AND DATAFILES
> > *
> > ERROR at line 1:
> > ORA-00959: tablespace 'getre****ts_TEMP' does not exist
>
> > CREATE TEM****ARY TABLESPACE getre****ts_TEMP
> > *
> > ERROR at line 1:
> > ORA-01119: error in creating database file
> > '/u02/oradata/GETREPS/getre****ts_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,
> =A0 =A0Vladimir M. Zakharychev
> =A0 =A0N-Networks, makers of Dynamic PSP(tm)
> =A0 =A0http://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 getre****ts
DATAFILE '/u02/oradata/GETREPS/getre****ts_DATA' SIZE 500M
AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED
DEFAULT STORAGE (
                INITIAL 256K
                NEXT   128K
                MINEXTENTS 1
                MAXEXTENTS UNLIMITED
                PCTINCREASE 0)
                LOGGING
                ONLINE;

-- Tem****ary tablespace.
CREATE TEM****ARY TABLESPACE getre****ts_TEMP
TEMPFILE '/u02/oradata/GETREPS/getre****ts_TEMP_DATA' SIZE 32M REUSE
AUTOEXTEND ON NEXT 32M MAXSIZE UNLIMITED;