Error ORA-00406

Norman · #1 (**permalink**) 01-23-03, 10:44

Hello again everyone..

I've got a database setup and foiund that after I did some testing I don't need a specific column, but also found that I can't drop the column..

SQL> alter table tracking drop column test;
alter table tracking drop column test
*
ERROR at line 1:
ORA-00406: COMPATIBLE parameter needs to be 8.1.0.0.0 or greater

SQL>

And I'm kinda confused.. any help would be great....

Thanks

andrewst · #2 (**permalink**) 01-23-03, 10:48

You need to be on at least Oracle 8.1 to drop columns. If your version of Orace IS 8.1 or higher, then it has the COMPATIBLE parameter set to a lower value - this would be done deliberately while evaluating a new Oracle version to ensure no new features were accidentally used.

Norman · #3 (**permalink**) 01-23-03, 11:05

Quote:

Originally posted by andrewst
You need to be on at least Oracle 8.1 to drop columns. If your version of Orace IS 8.1 or higher, then it has the COMPATIBLE parameter set to a lower value - this would be done deliberately while evaluating a new Oracle version to ensure no new features were accidentally used.

As far as I know we are using 8.1.7.0.1

So is there ANY other way to get rid of this column?

andrewst · #4 (**permalink**) 01-23-03, 11:14

Quote:

Originally posted by Norman
As far as I know we are using 8.1.7.0.1

So is there ANY other way to get rid of this column?

Well, you could just change the COMPATBLE parameter to 8.1.7.0.1 and then it will work.

If that isn't permitted, then you would have to do it the hard way:

CREATE TABLE new_tracking AS SELECT ... FROM tracking;

DROP TABLE tracking;

RENAME new_tracking TO tracking;

(Then add back missing indexes, constraints, triggers, ...)

Norman · #5 (**permalink**) 01-23-03, 11:31

Quote:

Originally posted by andrewst
Well, you could just change the COMPATBLE parameter to 8.1.7.0.1 and then it will work.

Hate tos ound so clueless, but oracle is a new beast for me.

Can I change it using the Alter Session and/or some other set command?

Thanks

andrewst · #6 (**permalink**) 01-23-03, 11:41

Quote:

Originally posted by Norman
Hate tos ound so clueless, but oracle is a new beast for me.

Can I change it using the Alter Session and/or some other set command?

Thanks

You could use ALTER SYSTEM, if you have sufficient privileges. If you are not the DBA, I suggest you contact him/her.

marist89 · #7 (**permalink**) 01-23-03, 13:02

Quote:

Originally posted by andrewst
You could use ALTER SYSTEM, if you have sufficient privileges. If you are not the DBA, I suggest you contact him/her.

Ah, no disrespect intended, but you can't modify compatible via ALTER SESSION or ALTER SYSTEM. Your only recourse is to change the init.ora and bounce the instance.

Code:

SQL> l
  1  select name, issys_modifiable, isses_modifiable, value from v$parameter
  2* where upper(name) like 'COMPAT%'
SQL> /

NAME                           ISSYS_MOD ISSES VALUE
------------------------------ --------- ----- --------------------
compatible                     FALSE     FALSE 8.1.0

andrewst · #8 (**permalink**) 01-23-03, 15:54

Quote:

Originally posted by marist89
Ah, no disrespect intended, but you can't modify compatible via ALTER SESSION or ALTER SYSTEM. Your only recourse is to change the init.ora and bounce the instance.

Thanks for correcting me - I was careless in checking the documentation.

Internet Services

Web Development

Digital Marketing

Consumer Tech