Re: problem deleting datafile

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;