ORA-00997: Illegal use of datatype

One day you've decided to do a simple backup for your table:

create tab1_bkp as select * from tab1;

And ops - you are getting this:

ORA-00997: Illegal use of datatype.

The reason for it is that one or more columns in your table have LONG data type which could only be used for select.

The same error you will get when you try to move your table to a different tablespace.

There are a couple of ways to get around that. One is to do the export and import. This will work if you need to bring the data from this table into a different database (because you would not be able to use a database link or a COPY command due to the same reason).

The other one is to create your backup table by converting this LONG column to CLOB:

create table tab1_bkp as select a, b, c,...,to_lob(k), l,m from tab1;

RMAN restore from un-cataloged backup

What if you need to create a duplicate database from RMAN backup on tape but because of your redundancy policy your backup is gone from RMAN catalog?
The steps below will guide you on how to do it. Same steps are applicable to a plain restore, with running restore controlfile from autobackup & restore database commands rather than a duplicate script.

Just use catalog command.
Let's assume you are doing backupsets (the approach will be the same if your are not). Copy (or ask yor sysadmin to copy) all the contents of your backup directory from tape into some location on the server, say /db/backup/DB1/. Also, copy init.ora file into $ORACLE_HOME/dbs/ and entire archive log directory into /db/archives/DB1. You will need to specify fully qualified backup piece name in the catalog command.

1.Do export ORACLE_SID=DB1
2. Connect to recovery catalog and use catalog command on every backup piece & if you want (but not necessary) on every archive log.
3. Run you duplicate script (see example in my blog).
In the script you will need to use "set until". You could do something like that:
set until time "to_date('10-01-2008' 09:05:00','mm-dd-yyyy hh24:mi:ss')";
4. If the script is missing some archive logs, you still can go into sqlplus and do:
recover database until cancel using backup controlfile;
and use cancel with no ";".
5. Alter database open resetlogs
6. Voila!

One of the reasons your script would fail is that: if you do controlfile autobackup by default it goes into $ORACLE_HOME/dbs (unless you specified differently). Catalog this piece as well. On my system it is name something like "c-167655678-85745849843-00. RMAN will tell you if the one you are trying to catalog is the wrong one.

Unique Constraint Violation

Sometimes you have to run an insert-select statement which produces ORA-00001 "Unique Constraint Violation". Of course, that's because of an attemp to insert a duplicate key.
What to do if your select statement produces 300 rows out of which only 100 are duplicate keys and the rest you still need to insert?

One way to do that is to create a staging table & use MERGE statement to do the job. Lets say you have a table tab1 with a unique index on col1 and col2.

1. Create table tab1_bkp as select * from tab1;
2. Truncate table tab1;
3. Use your insert-select statement on tab1_bkp.
4. Merge into tab1 a using tab1_bkp b
on (a.col1=b.col1 and
a.col2=b.col2)
when not matched then
insert (col1,col2,....coln)
values (b.col1, b.col2,...b.coln);.
5. Drop tab1_bkp;

You are done

Introduction

Hi. My name is Greg Pisarevsky. I worked in different areas of IT for 19 years, last 8 of them as Oracle DBA. I would like to share and exchange some tips and techniques on Oracle Database Administration here. I will add some more profile-related details a little later. In the meantime, feel free to post any related comments, questions and so on. Cheers!

My hobbies include: European history, meditation, extensive stretching, reading, playing with my cat.