Re: problem deleting datafile
Date: Fri, 4 Jul 2008 00:39:42 -0700 (PDT)
Message-ID: <06cbdc75-dcc9-47f3-a225-f5c89a89050d@d1g2000hsg.googlegroups.com>
Michael Austin wrote:
> max.fontain_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;