OraFAQ Forum: Server Administration

Home » RDBMS Server » Server Administration » see ora-01653 again (oracle 10g r2,linux)

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

see ora-01653 again [message #536040]

Mon, 19 December 2011 01:40

lzfhope
Messages: 69
Registered: July 2006

Member

hi,

I find a strange problem.
i am used to copying data from another server by using dblink.

in brief,their relationship like below:

SERVER :A,B
SCHEMAS: A:X,Y ; B:SRC
USERS: A:X,Y; B:SRC
TABS: A.Y.TB, B.SRC.TB .they are identical ,but on two seperate server.
procedures : a.x.p_x; a.y.p_y ; p_x call p_y ,and p_y copies data from b.

one of the segments of p_y is like below:

    insert into y.tb select * from src.tb@fromsrc;

here ,dblink "fromsrc" link to b using user src.
..
for a long time,they run well. but recently, about 2-3 days,p_y raised application errors like this " ora-01653 : unable to extend table x.tb by 8 in tablespace tb_y "。

in fact,tablespace tb_y is autoextensible ,and there about 100g free space (from view dba_free_spaces),which is much more bigger than tb.
why?

Report message to a moderator

Re: see ora-01653 again [message #536046 is a reply to message #536040]

Mon, 19 December 2011 02:06

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

ORA-01653: unable to extend table %s.%s by %s in tablespace %s
 *Cause:  Failed to allocate an extent of the required number of blocks for
          a table segment in the tablespace indicated.
 *Action: Use ALTER TABLESPACE ADD DATAFILE statement to add one or more
          files to the tablespace indicated.

Report message to a moderator

Re: see ora-01653 again [message #536053 is a reply to message #536040]

Mon, 19 December 2011 02:27

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

Senior Member

Autoextension is not an attribute of the tablespace: it is an attribute of the datafile(s). You need to check dba_data_files to see if autoextension has been enabled, and whether the maximum has been reached.
Quote:

and there about 100g free space (from view dba_free_spaces),

Are you certain? Can you post the SQL that proves this, and the error?

Report message to a moderator

Re: see ora-01653 again [message #536055 is a reply to message #536046]

Mon, 19 December 2011 02:30

lzfhope
Messages: 69
Registered: July 2006

Member

in fact .dba_data_files diplay that ,there are over 100g free space.
just add datafiles, do not work!

Report message to a moderator

Re: see ora-01653 again [message #536058 is a reply to message #536053]

Mon, 19 December 2011 02:33

lzfhope
Messages: 69
Registered: July 2006

Member

did not find record about tb_y .
strange!

but dba_data_files shows that over 100g

Report message to a moderator

Re: see ora-01653 again [message #536066 is a reply to message #536058]

Mon, 19 December 2011 02:49

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

Senior Member

What SQL are you running? If you won't show it, no-one can help you to interpret it. And when (or if...) you do post the statements, please do a simple copy/paste from you SQL*Plus session, and format it properly: be sure to read How to use [code] tags and make your code easier to read first.

Report message to a moderator

Previous Topic:	10g Default Job?
Next Topic:	Adding Datafiles to Tablespace

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 25 01:48:02 CDT 2024