Disappearing Columns

At the site where I am currently working Oracle Designer is used. The version they have here, Designer 10, does not supprt XMLType and this posed a
problem at first.
Table script could not be generated because it is not possible to define a column of XMLType. What we did was create a user defined type called XMLType and use this fake-XMLType
for the table definitions. When you generate a table-creation script, it will look like this:

CREATE TABLE XML_TEST
(ID NUMBER(10,0) NOT NULL
,FAKE_XML XMLTYPE
)
/

....

But that is not all, the generator also creates a script for the XMLType itself:

PROMPT Creating Object Type 'XMLTYPE'
CREATE OR REPLACE TYPE XMLTYPE AS OBJECT
(X INT
)
/

If you are not careful this fake XMLType is created in the database and none of your XMLType methods will work properly, if at all.

And guess what happened yesterday? We forgot to remove the "create-type" script, so the fake-XMLType was created and all fell down. Removing the fake-XMLType was the only option, but
you are not allowed to do so when dependents exist, like a column in a table.

Where did it go?

To remove the fake-XMLType I issued

SQL> drop type xmltype
2 /
drop type xmltype
*
ERROR at line 1:
ORA-02303: cannot drop or replace a type with type or table dependents

That obviously didn’t work, so I force dropped it:

SQL> drop type xmltype force;

Type dropped.

SQL>

And that did the job…. and more. It also removed the columns in the table that were of the XMLType. So the table above changed to:

SQL> desc xml_test
Name Null? Type
---------- -------- ----------
ID NOT NULL NUMBER(10)

SQL>

Actually the columns was not dropped, but marked as UNUSED and this columns become inaccessible.
(more info in the documentation)
To find out how many columns are marked as UNUSED, you can query the datadictionary view USER_UNUSED_COL_TABS. It is also
possible to explicitly mark a column as UNUSED explicitly:

alter table table_name set unused (column_name);

It is not possible reverse this action, you can not mark the column as USED.

Live and learn

Instead of being smart and fix the problem with a single statement, the problem only got worse. Instead of getting rid of the fake XMLtype by dropping it, I had
to recreate the tables as well. If only I had paid a little more attention when I executed the create-scripts…

Oh well, you live and learn. I won’t do that again… until the next time.

15 Comments

  1. Remco Blaakmeer June 24, 2010
  2. Alex Nuijten January 29, 2007
  3. Rachana January 29, 2007
  4. Marco Gralike December 23, 2006
  5. harm December 22, 2006
  6. Alex Nuijten December 21, 2006
  7. Marco Gralike December 20, 2006
  8. Alex Nuijten December 20, 2006
  9. Michael Friedman December 20, 2006
  10. Alex Nuijten December 20, 2006
  11. Marco Gralike December 20, 2006
  12. Marco Gralike December 20, 2006
  13. Marco Gralike December 20, 2006
  14. Marco Gralike December 20, 2006
  15. Nigel Thomas December 20, 2006