UPDATE OUT OF SPACE

[teknikalbody]

Trying to do an update - but i run out of userspace.

Any suggestions / advice / help appreciated.

TIA

 

[Turkbear]

Hi,
Much more info please:

Which tablespace is out of space?
What OS?
Exactly what error message?

 

[teknikalbody]

Hi

"UNDO" space area

error message - ORA300366

W98

 

[Turkbear]

Does 8i have an UNDO tablespace?
Anyway, you need to add more room ( by expanding an existing datafile or adding another datafile to that tablespace)

As far as I can tell there is no ORA-300366 error..

What does the message say?

 

[teknikalbody]

sorry the message is ORA-30036 - if this helps

 

[Turkbear]

Hi,
It is a space issue...

Error:	  ORA-30036  (ORA-30036)
Text:	  unable to extend segment by %s in undo tablespace '%s'
-----------------------------------------------------------

Cause:	the specified undo tablespace has no more space available.

Action:	Add more space to the undo tablespace before retrying the operation. An alternative is to wait until active transactions to commit.

So increase the size or commit more frequently..

 

[teknikalbody]

Cheers,

Could'nt get the space increased, so wrote query in PL/SQL with more commits - worked !!!

Thanx again

 

[cliousa]

Yeap. That alwasy helps. Use counter and commit when reached the commit point. Example:

SET ECHO OFF
SET TRANSACTION USE ROLLBACK SEGMENT BIG_ONE;

DECLARE
v_commit_point NUMBER := 1000;
v_error_code NUMBER;
v_error_message VARCHAR2(200);
v_id NUMBER;

CURSOR update_owner_cursor
IS
SELECT a.avs_structures_id,
a.ownername,
a.ownersource,
a.ownerdate,
a.address1,
a.address2,
a.address3,
a.address4,
a.ownerzip
FROM v_assessor_aoi_owner a, aoi_owners b
WHERE a.avs_structures_id = b.avs_structures_id
AND b.delete_date IS NULL
AND (a.ownername != b.owner_name
OR a.address3 != b.owner_street);



BEGIN

-- Update records in owners table

FOR update_owner_cursor_rec IN update_owner_cursor LOOP
BEGIN
UPDATE aoi_owners
SET delete_date = (SELECT sysdate FROM dual)
WHERE avs_structures_id = update_owner_cursor_rec.avs_structures_id
AND delete_date IS NULL;

v_id := update_owner_cursor_rec.avs_structures_id;
INSERT INTO aoi_owners
VALUES (aoi_owners_seq.nextval,
update_owner_cursor_rec.avs_structures_id,
update_owner_cursor_rec.ownername,
update_owner_cursor_rec.ownersource,
update_owner_cursor_rec.ownerdate,
update_owner_cursor_rec.address1,
update_owner_cursor_rec.address2,
update_owner_cursor_rec.address3,
update_owner_cursor_rec.address4,
update_owner_cursor_rec.ownerzip,
NULL, NULL);

IF update_owner_cursor%ROWCOUNT >= v_commit_point THEN
v_commit_point := v_commit_point * 2;
COMMIT;
END IF;

EXCEPTION
WHEN OTHERS THEN
v_error_code := SQLCODE;
v_error_message := SUBSTR (SQLERRM, 1, 200);
INSERT INTO aoi_conversion_exceptions
VALUES ('AOI_UPDATE_OWNER.SQL', 'AOI_OWNERS', 'UPDATE',
'V_ASSESSOR_AOI_OWNER - '||'AVS_STRUCTURES_ID = '||TO_CHAR(v_id),
v_error_code, v_error_message);
COMMIT;
END;
END LOOP;
END;
/




Hope that helps,
clio_usa - OCP DBA
------------------

http://www.dbaclick.com