problem deleting datafile
in [Oracle]
|
Prev: table getting analyzed automatically in oracle 10g
Next: Before turning to oracle support.......component 'DBMS_SYS_SQL' must be declared
From: max.fontain on 3 Jul 2008 08:31 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
From: Ana C. Dent on 3 Jul 2008 10:05 max.fontain(a)yahoo.com wrote in news:c4e25171-ff1d-4b66-86cd-7fc9af411c63 @2g2000hsn.googlegroups.com: > 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 > Why OH, Why are you creating & dropping tablespaces on a regular basis? Did the designer learn databases on MS Access or SQLServer? If you cease doing this, you won't get any errors.
From: Vladimir M. Zakharychev on 3 Jul 2008 10:10 On Jul 3, 4:31 pm, max.font...(a)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
From: max.fontain on 3 Jul 2008 12:33 Ana C. Dent wrote: > > ORA-27038: created file already exists > > > > > Why OH, Why are you creating & dropping tablespaces on a regular basis? > Did the designer learn databases on MS Access or SQLServer? > > > If you cease doing this, you won't get any errors. Thanks Ana! Good point. Perhaps an excel worksheet or two joined up with vba..
From: max.fontain on 3 Jul 2008 13:03
On 3 Jul, 15:10, "Vladimir M. Zakharychev" <vladimir.zakharyc...(a)gmail.com> wrote: > On Jul 3, 4:31 pm, max.font...(a)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; |