Home » RDBMS Server » Server Administration » Alter tablespace add datafile taking too much time
Alter tablespace add datafile taking too much time [message #62258] Thu, 08 July 2004 03:19 Go to next message
Jitendra Agrawal
Messages: 71
Registered: December 2003
Member
Hi,

I am trying to add a datafile to an existing tablespace. It takes more than 2 hours and still it is not added. I have to kill the instance.

Following is a part of alert logs.

/* OracleOEM */ ALTER DATABASE DATAFILE '/home/oracle/product/9.0.1/oradata/stpora/crbt_01.dbf' AUTOEXTEND ON MAXSIZE 2000M
Wed Jul 7 23:48:47 2004
Completed: /* OracleOEM */ ALTER DATABASE DATAFILE '/home/ora
Wed Jul 7 23:49:51 2004
/* OracleOEM */ ALTER TABLESPACE "CRBT" ADD DATAFILE '/home/oracle/product/9.0.1/oradata/stpora/crbt_02.dbf' SIZE 1000M
Thu Jul 8 00:49:27 2004
alter tablespace "crbt" add datafile '/home/oracle/product/9.0.1/oradata/stpora/crbt_02.dbf' size 1000M
Thu Jul 8 00:49:27 2004
ORA-959 signalled during: alter tablespace "crbt" add datafile '/home/oracle...
Thu Jul 8 00:49:38 2004
alter tablespace crbt add datafile '/home/oracle/product/9.0.1/oradata/stpora/crbt_02.dbf' size 1000M
Thu Jul 8 00:49:38 2004
ORA-1119 signalled during: alter tablespace crbt add datafile '/home/oracle/p...
Thu Jul 8 00:49:48 2004
alter tablespace crbt add datafile '/home/oracle/product/9.0.1/oradata/stpora/crbt_03.dbf' size 1000M
Thu Jul 8 03:49:40 2004
Thread 1 advanced to log sequence 40
Current log# 3 seq# 40 mem# 0: /home/oracle/product/9.0.1/oradata/stpora/redo03.log
Thu Jul 8 04:35:08 2004
ORA-604 signalled during: /* OracleOEM */ ALTER TABLESPACE "CRBT" ADD DATAFI...
Thu Jul 8 04:38:00 2004
ORA-604 signalled during: alter tablespace crbt add datafile '/home/oracle/p...
Thu Jul 8 04:49:17 2004

Should I reduce the size? What should be the size for datafile so that it gets added?

Regards,
Jitendra Agrawal.

Report message to a moderator

Re: Alter tablespace add datafile taking too much time [message #62260 is a reply to message #62258] Thu, 08 July 2004 04:32 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Please make sure u have the enought diskspace in server to hold a new 1000m datafile.
else
create a file with smaller size...

Report message to a moderator

Re: Alter tablespace add datafile taking too much time [message #62262 is a reply to message #62260] Thu, 08 July 2004 04:48 Go to previous messageGo to next message
Jitendra Agrawal
Messages: 71
Registered: December 2003
Member
Hi,

Yes I have ENOUGH disk space to hold the new 1000 M datafile. But Still I am not able to create it. I have tried twice and second time I waited for more than 2 hours for the process to complete.

Any pointers why it is taking this much time?

Regards,
Jitendra Agrawal.

Report message to a moderator

Re: Alter tablespace add datafile taking too much time [message #62265 is a reply to message #62262] Thu, 08 July 2004 06:07 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
1. check initsid.ora
if there is any event set, remove or unset them.

2. try directly using sqlplus instead of OEM (OEM issue?).
if you get any errors post the exact error message.

3. Check whether the tablespace exists.
check whether there is a temp tablespace and it is locally managed.

4. go to the OS,
try copying some huge file into the same diretory.
if this works good, the your disk is good.

Report message to a moderator

Re: Alter tablespace add datafile taking too much time [message #62271 is a reply to message #62265] Thu, 08 July 2004 15:22 Go to previous messageGo to next message
Jitendra Agrawal
Messages: 71
Registered: December 2003
Member
Hi,

1. What do you mean by 'any event set'?

2. I tried the crbt_02 from OEM and crbt_03.dbf from sqlplus. I had to kill the instance after 2 hours.

3. Tablespace exists. There is a temp tablespace.

4. The file is being made.

I have 320 MB of SGA allocated (have enough RAM) should I increase SGA to 1 GB? Will that help? alert.log does not show any errors..

Thanks and Regards,
Jitendra Agrawal.

Report message to a moderator

Re: Alter tablespace add datafile taking too much time [message #62272 is a reply to message #62271] Thu, 08 July 2004 15:30 Go to previous messageGo to next message
Jitendra Agrawal
Messages: 71
Registered: December 2003
Member
Hi,

I finally restarted the database. It removed the datafiles that were made in earlier attempts..

I tried to add datafile and it worked.

Thnx.
Jitendra Agrawal.

Report message to a moderator

Re: Alter tablespace add datafile taking too much time [message #63311 is a reply to message #62265] Mon, 27 September 2004 02:23 Go to previous message
Jitendra Agrawal
Messages: 71
Registered: December 2003
Member
Hi,

I am again trying to add the datafile. Last time it was added after I restarted database. This time even that is not working. Any pointers?

Regards,
Jitendra Agrawal.

Report message to a moderator

Previous Topic: On certification.
Next Topic: How to manager mixed databases?
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Thu Jan 09 14:38:13 CST 2025
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.