Re: problem deleting datafile

From: <max.fontain_at_yahoo.com>
Date: Sat, 5 Jul 2008 01:36:06 -0700 (PDT)
Message-ID: <aed8228b-5a2c-4b11-8b4f-21d332a2494f@z72g2000hsb.googlegroups.com>

gazzag wrote:

> 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

CREATE TABLESPACE getreports
DATAFILE '/u02/oradata/GETREPS/getreports_DATA' SIZE 2000M AUTOEXTEND ON NEXT 200M MAXSIZE 50000M

                LOGGING
                ONLINE;

• Temporary tablespace. CREATE TEMPORARY TABLESPACE getreports_TEMP TEMPFILE '/u03/oradata/GETREPS/getreports_TEMP_DATA' SIZE 2000M REUSE AUTOEXTEND ON NEXT 200M MAXSIZE 20000M;

Thanks! How ia this looking now?
I have removed the storage clause cos I read in the Oracle Press 10G complete reference that "If no storage clause is specified the tablespace's default storage parameters. Although I dont quite know what this measm in reality.
Any further improvements possible?

TIA Max Received on Sat Jul 05 2008 - 03:36:06 CDT