Toad for Oracle - Issue with the Tablespace history

Toad for Oracle
1

I am using Toad for Oracle 11.5.1.2.

After enabling the tablespace history (to see the space evolution) in one of my database (Oracle Database 11.2.0.3), I am getting the following error every week in the RDBMS alert log:

286509 : Wed Oct 15 00:00:01 2014

286510 : Errors in file /oracle/app/oracle/diag/rdbms/strm/strm/trace/strm_j000_4456698.trc:
286511 : ORA-12012: error on auto execute of job 106
286512 : ORA-00001: unique constraint (SYS.TOAD_TABLESPACES_PK) violated
286513 : ORA-06512: at “SYS.TOAD_SPACEMAN”, line 66
286514 : ORA-06512: at line 2

I understand that the problem comes from the line 66 of the package TOAD_SPACEMAN which corresponds to the following code:

/* INSERT NEW TABLESPACES FOUND */
INSERT INTO TOAD_TABLESPACES
SELECT TABLESPACE_NAME, V_MON_DATE
FROM DBA_TABLESPACES;
COMMIT;

I compared the results of the SELECT part of the SQL above with what we have. We have the same number of rows every week.

Moreover the results always have unique values so I do not understand why the unique constraint will detect non-unique values.

Could please someone advise?

2

I didn’t installed this option (have mine own tool for same purpose) but could you please check what is primary key in “TOAD_TABLESPACES” table and definition of before insert trigger on same table (is it enabled and valid).
Brg
Damir

3

a few lines up in that package, this line is found:

V_MON_DATE := TRUNC(SYSDATE);

and this…

DELETE FROM TOAD_TABLESPACES WHERE MON_DATE = V_MON_DATE;

…So one of these must be true:

  1. The job is running more than once during the same day and the delete isn’t happening (in which case you’d think it would throw an error)

  2. somehow there are duplicate tablespace names in DBA_TABLESPACES (not sure how this could happen…)

  3. someone has altered the TOAD_SPACEMAN package (maybe to remove the delete?)

My suggestion would be to drop the space manager objects and reinstall them.

4

@Damir,

the current primary key DDL on TOAD_TABLESPACES is:

CREATE UNIQUE INDEX SYS.TOAD_TABLESPACES_PK ON SYS.TOAD_TABLESPACES

(TABLESPACE_NAME, MON_DATE)

LOGGING

TABLESPACE SYSTEM

PCTFREE 5

INITRANS 2

MAXTRANS 255

STORAGE (

INITIAL 64K

NEXT 1M

MINEXTENTS 1

MAXEXTENTS UNLIMITED

PCTINCREASE 0

FREELISTS 1

FREELIST GROUPS 1

BUFFER_POOL DEFAULT

)

NOPARALLEL;

There is no trigger but an activated constraint defined by:

ALTER TABLE SYS.TOAD_TABLESPACES ADD (

CONSTRAINT TOAD_TABLESPACES_PK

PRIMARY KEY

(TABLESPACE_NAME, MON_DATE)

USING INDEX SYS.TOAD_TABLESPACES_PK

ENABLE VALIDATE);

@John,

  1. that could explain why we get this type of error. the job seems to have run once but I can search for something like this

  2. don’t think it is what happened

  3. In the package I see the following DELETE statements before the INSERT:

/* DELETE DATA OLDER THAN USER SPECIFIED DAYS */

DELETE FROM TOAD_TABLESPACES WHERE MON_DATE < V_MON_DATE - NumDaysToRemove;

COMMIT;

/* DELETE PRE-EXISTING DATA FOR CURRENT DAY */

DELETE FROM TOAD_TABLESPACES WHERE MON_DATE = V_MON_DATE;

COMMIT;

/* DELETE DATA FOR NONEXISTENT TABLESPACES */

DELETE FROM TOAD_TABLESPACES X

WHERE NOT EXISTS (SELECT 1 FROM DBA_TABLESPACES D

WHERE X.TABLESPACE_NAME = D.TABLESPACE_NAME);

COMMIT;

  1. How do I drop the space manager objects an re-install them?
5
  1. How do I drop the space manager objects an re-install them?

In database -> Administer -> Tablespaces, on the Space History tab, the first toolbar button will do this.

6

John, you help me to find the answer.

There were 2 same jobs running at the same time : one under the user SYS and one under the user TOAD. I dropped the job under the user TOAD. That should fix it.

Thank you very much!