Re: problem deleting datafile

On 4 Jul, 08:39, max.font..._at_yahoo.com wrote:
> Michael Austin wrote:
> > max.font..._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
>
> Thanks a lot!
> If specify another slice on another disk for the temp tablespace what
> would be the  downside to making the temp tablespace much bigger, say
> 10GB?
> Now I have this:
> CREATE TABLESPACE getreports
> DATAFILE '/u02/oradata/GETREPS/getreports_DATA' SIZE 500M
> AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED
> DEFAULT STORAGE (
>                 INITIAL 256K
>                 )
>                 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;- Hide quoted text -
>

Don't set MAXSIZE UNLIMITED. Your disk is not really unlimited, is it?

HTH -g Received on Fri Jul 04 2008 - 05:44:05 CDT