Talk About Network

Google


Register and Login
Nick
Password
Register create new account Sign up is FREE and you can post replies, new topics, bookmark posts and more!
Recover lost password


Data Bases > Oracle Server > Re: problem del...
Latest [ Topics | Posts ] Archive Post A New Topic Post a Reply
<< Topic < Post Post 5 of 9 Topic 16841 of 17248
Post > Topic >>

Re: problem deleting datafile

by max.fontain@[EMAIL PROTECTED] Jul 3, 2008 at 10:03 AM

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;
 




 9 Posts in Topic:
problem deleting datafile
max.fontain@[EMAIL PROTEC  2008-07-03 05:31:48 
Re: problem deleting datafile
"Ana C. Dent" &  2008-07-03 14:05:47 
Re: problem deleting datafile
"Vladimir M. Zakhary  2008-07-03 07:10:09 
Re: problem deleting datafile
max.fontain@[EMAIL PROTEC  2008-07-03 09:33:43 
Re: problem deleting datafile
max.fontain@[EMAIL PROTEC  2008-07-03 10:03:49 
Re: problem deleting datafile
Michael Austin <mausti  2008-07-03 19:51:31 
Re: problem deleting datafile
max.fontain@[EMAIL PROTEC  2008-07-04 00:39:42 
Re: problem deleting datafile
gazzag <gareth@[EMAIL   2008-07-04 03:44:05 
Re: problem deleting datafile
max.fontain@[EMAIL PROTEC  2008-07-05 01:36:06 

Post A Reply:
  Go here to Signup

AddThis Feed Button


About - Advertising - Contact - Frequently Asked Questions - Privacy Policy - Terms of Use - Signup

Contact
tan12V112 Tue Oct 7 1:18:09 CDT 2008.