OraFAQ Forum: Server Administration » Alter tablespace issue

Home » RDBMS Server » Server Administration » Alter tablespace issue (oracle 11.2.0.1 )

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Alter tablespace issue [message #611301]

Tue, 01 April 2014 03:07

sathish16787
Messages: 56
Registered: September 2011
Location: mumbai

Member

Altered tablespace with adding datafile with below command.

Alter tablespace XXX '/u02/oradata/test/tre_05.dbf' size 25;(created successfully)
Alter tablespace XXX '/u03/oradata/test/tre_05.dbf' size 25;

second datafile is not added when i queried dba_data_files. But in os datafile is created and size occupied. in trace file ORA-604 signalled during: Alter tablespace getting the error.

if the datafile is not craeted .can we drop the datafile using os command on location /u03/oradata/test/tre_05.dbf.

will it affect when bounce the database.

please help . thanks in advance.

Report message to a moderator

Re: Alter tablespace issue [message #611309 is a reply to message #611301]

Tue, 01 April 2014 03:31

John Watson
Messages: 8989
Registered: January 2010
Location: Global Village

Senior Member

One cannot assist if you do not describe, correctly,what happened. Your code does not run:

orclz> Alter tablespace XXX '/u02/oradata/test/tre_05.dbf' size 25;
Alter tablespace XXX '/u02/oradata/test/tre_05.dbf' size 25
                     *
ERROR at line 1:
ORA-02142: missing or invalid ALTER TABLESPACE option


orclz>

Report message to a moderator

Re: Alter tablespace issue [message #611313 is a reply to message #611309]

Tue, 01 April 2014 03:46

sathish16787
Messages: 56
Registered: September 2011
Location: mumbai

Member

Alter tablespace XXX add datafile '/u02/oradata/test/tre_05.dbf' size 25g ;( this datafile created successfully)

Alter tablespace XXX add datafile '/u03/oradata/test/tre_05.dbf' size 25g ; (this is not created)

when i checked dba_data_files /u03/oradata/test/tre_05.dbf is not created but when i checked in os level datafile file present on that location . can we drop the datafile on os level on location /u03/oradata/test/tre_05.dbf. this db is running in noarchive log mode.

Report message to a moderator

Re: Alter tablespace issue [message #611319 is a reply to message #611313]

Tue, 01 April 2014 04:36

John Watson
Messages: 8989
Registered: January 2010
Location: Global Village

Senior Member

I have no idea what your situation is.

What happened when you ran those commands?
How did you "checked dba_data_files" ?
How did you "checked in os level datafile file present" ?

Please understand that I cannot see your computer screen. Use copy/paste to show all your commands, enclosing them in [code] tags as I did in my example.

Report message to a moderator

Previous Topic:	ORA-01033 ORACLE initiation or shutdown in progess
Next Topic:	Oracle database migration at OS level

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Nov 11 14:32:19 CST 2025