ORA-00604 und ORA-02429 bei DROP TABLESPACE

[VuuRWerK]

Hallo liebe Gemeinde,

ich habe mal wieder ein Problem mit Oracle und hoffe das mir jemand helfen kann. Ich habe natürlich schon ausgiebig die Suchmaschine meines Vertrauens strapaziert aber alles was da als Fehlerlösung genannt wurde war: "drop the constraint and not the index". Großartig, ich weiß ja nicht mal welches Constraint sich hier in die Quere stellt?

Mein SQL was ich versuche auszuführen:

DROP TABLESPACE &TS_NAME INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS;

Der Fehler der verursacht wird:

DROP TABLESPACE PARSEC_DM INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS
*
FEHLER in Zeile 1:
ORA-00604: Fehler auf rekursiver SQL-Ebene 1
ORA-02429: Index zum Erzwingen des eindeutigen/Primärschlüssels nicht löschbar

Das CASCADE CONSTRAINTS hatte ich schon nachträglich hinzugefügt weil ich dies u.a. als Ergebnis meiner Fehlersuche im Netz gefunden habe, leider hatte es keine Auswirkungen gehabt.

Zur Info: Ich benutze derzeit OracleXE 10g(die Express Version von Oracle), Release 10.2.0.1.0 auf Windows XP. Ein Oracle 10g Enterprise auf Linux(genauer Debian Lenny) kann ich zwar ebenfalls benutzen, jedoch müsste ich das erst installieren, wozu mir leider momentan die Zeit fehlt. Ich glaube aber nicht das es an der XE Version liegt.

Ich hoffe mir kann jemand einen Tipp geben wie ich den Fehler beheben kann oder wonach ich suchen muss um diesen zu beheben.

Gut Schuß
VuuRWerK

[jum]

Ist es möglich, dass Du einen TABLESPACE löscht, in dem sich der INDEX, PRIMÄRSCHLÜSSEL (was auch immer) eines noch gültigen Schemaobjektes (aus einem anderen TABLESPACE) befindet?
Aber so einfach führt das nicht zu dieser Fehlermeldung, anstandslos arbeitet:

CREATE TABLESPACE temp_space DATAFILE 'temp_space.dbf' SIZE 20M;

CREATE TABLE temp_001 (ID NUMBER);

CREATE INDEX idx_temp_001 ON temp_001(ID) TABLESPACE temp_space; 

CREATE TABLE temp_002 (ID NUMBER UNIQUE) TABLESPACE temp_space;

ALTER TABLE temp_001 ADD (
  CONSTRAINT cstr_temp_001 
 FOREIGN KEY (ID) 
 REFERENCES temp_002 (ID));

DROP TABLESPACE temp_space INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS;

[VuuRWerK]

Hallo jum,

danke für Deine Antwort.
Andere Objekte im selben tablespace kann ich ausschließen da ich den tablespace zuvor erst erzeugt habe, dann erzeugte ich Tabellen (mit PRIMARY KEY und INDEX). Bsp:

CREATE TABLE LabelAsset(
  "Index" Integer NOT NULL,
  Source Integer,
  "Start" Integer,
  Length Integer,
 CONSTRAINT PK_LabelAsset PRIMARY KEY ("Index")
 USING INDEX
 TABLESPACE &TS_NAME
)

Ein paar Defaultwerte mit insert table eingefügt, noch ein Package inkl. Body und einen Trigger.

Jetzt wollte ich das Script aber erweitern. Tabellen hinzufügen, Spalten ändern/hinzufügen etc. Und damit ich immer eine saubere Installation vorliegen habe drop ich vor dem anlegen immer erstmal den tablespace, lege diesen neu an und spiele die Schemas ein. Soweit wie ich vorgehe. Wie gesagt das noch ein "fremdes" Objekt sich im tablespace befindet kann ich somit ausschließen. Das kuriose ist ja das ich das bisher nie anders gemacht habe und da hat es immer funktioniert, sogar ohne CASCADE CONSTRAINTS im DROP-Statement. Es fing erst an als ich mein Script mit den Variablen versehen habe, welche einfach Parameter sind die ich dem Script mitgebe:

DEFINE LOG_FILE = &1;
DEFINE USER_NAME = &2;
DEFINE PASSWD = &3;
-- usw ...

Da hatte ich zu anfangs noch Probleme gehabt weil einige Sachen nicht funktioniert hatten, eventuell hat es dadurch Tabellen angelegt die ich jetzt nicht mehr ansprechen kann weil deren Name vermurkst wurde durch die Variablen. Gibt es eine Möglichkeit sich alle vorhandenen conatrainst in einem tablespace auszugeben sodass ich dann mit einem Select alle dropen kann bevor ich den tablespace lösche?

Gut Schuß
VuuRWerK

[jum]

Nein, direkt für CONSTRAINTS habe ich nichts gefunden, nur für "echte" Schemaobjekte, hier also:

SELECT owner, segment_name, segment_type
  FROM dba_segments
 WHERE UPPER(tablespace_name) = 'TEMP_SPACE'

[Uschi Blanz]

Hallo,

wenn eine Tabelle in einem anderen Tablespace liegt, der Index für den Primary Key aber in dem Tablespace, den Du löschen willst, dann kann so ein Fehler auftreten. Vielleicht ist Dir beim verwenden der Parameter das passiert.

Kontrollier doch mal mit einem
select distinct tablespace_name from user_tables
mit dem schema-Owner, ob Du eine falsch platzierte Tabelle findest - die musst Du halt einzeln löschen.

Gerade fällt mir noch auf: in Deinem create-skript gibt es keinen Tablespace für die Tabelle, nur für den PKey-Index - vielleicht ging auch der User und dadurch Default-Tablespace für die Tabelle daneben ?

select distinct i.tablespace_name, t.tablespace_name, t.table_name from all_tables t, all_indexes i
where i.table_name = t.table_name and i.tablespace_name <> t.tablespace_name and t.tablespace_name = 'denwillichloeschen'

Gruß
Uschi

[VuuRWerK]

Hallo,

@jum: Mit der Abfrage bekomme ich leider keine Ergebnisse zurück (0 Zeilen).

@Uschi Blanz: Beim select distinct tablespace_name from user_tables; bekomme ich nur den einen tablespace aufgelistet den ich auch angelegt habe und jetzt droppen möchte.
Und bei select distinct i.tablespace_name, t.tablespace_name, t.table_name from all_tables t, all_indexes i
where i.table_name = t.table_name and i.tablespace_name <> t.tablespace_name and t.tablespace_name = 'denwillichloeschen' bekomme ich ebenfalls keine Ergebinisse zurück (0 Zeilen) und ja natürlich hab ich 'denwillichloeschen' mit meinem tablespace namen ersetzt den ich auch löschen will

Gut Schuß
VuuRWerK

Edit: Vielleicht muss ich vor dem drop tablespace einfach alle PK constraints droppen? Also bspw
alter table LabelAsset drop constraint PK_LabelAsset;? Wie gesagt is mir das etwas fremd denn vorher ging es immer ohne dem, deswegen ja auch mein INCLUDING CONTENTS AND DATAFILES beim drop tablespace.

[Uschi Blanz]

probier mal den select mit 'all_indexes' und 'all_tables' mit dem User sys statt Deinem Schema-User (falls Dir der User daneben ging).
Man sieht in den all-Views nur die Objekte, auf die man auch Rechte hat.

noch was aufgefallen - hast Du in dem select von jum den tablespace_name auch mit Deinem namen ersettzt ? - in den segments sieht man eigentlich alles, was einen Tablespace belegt. z.B. LOBs - benutzt Du so was ?

Gruß
Uschi

[VuuRWerK]

AHA! Danke für den Hinweis, jetzt kamen da in der Tat Ergebnisse

Und wenn ich das ganze richtig interpretiere dann sind die Tabellen im Tablespace dem Schema Owner zugeordnet und die PK Constraints sind 2(!) mal vorhanden und einmal dem Schema-Owner zugeordnet und einmal dem sys! Ich denke mal das wird das Problem sein, oder? D.h. jetzt, ich muss als sys die PK Constraints löschen per Hand löschen und danach sollte es wieder gehen. Das ganze wird in der Tat da passiert sein als ich ein paar Probleme mit den Parameter hatte. LOBs setzen wir zur Zeit nicht ein.

Gut Schuß
VuuRWerK

[Uschi Blanz]

im select

select distinct i.tablespace_name, t.tablespace_name, t.table_name from all_tables t, all_indexes i
where i.table_name = t.table_name and i.tablespace_name <> t.tablespace_name and i.tablespace_name = 'denwillichloeschen'

muss hinten das "i" stehen - hatte ich mich vertippt - wahrscheinlich hast Du alle Tabellen komplett doppelt - als user und als sys. dann die Tabellen für sys halt mit dem jeweiligen Tabellennamen einzeln löschen.

Gruß
Uschi

[VuuRWerK]

Alles klar, jetzt ist alles wieder gut und nun kann ich mein script immer und immerwieder ausführen und der tablespace wird ordnungsgemäß und wie gewohnt gedropped und dann wieder neu angelegt.

Vielen Dank Euch beiden für die Hilfe!

Gut Schuß
VuuRWerK

P.S.: Ich Depp hätte mir das ganze tippen, kopieren, History im SQL-Plus durchblättern sparen können um alöle tabellen einzeln zu löschen, denn es gibt in der XE Version ebenfalls ein Database Manager in Form eines Webviews ... Naja so hat man wenigstens wieder bisschen mehr PL/SQL gelernt