sql error

Hello,

could anyone help me to fix the following error? many thx!


ALTER TABLE gp_ministry RENAME COLUMN
gp_ministry_key
TO gpages_ministry_key;

ALTER TABLE gp_ministry RENAME COLUMN
                               *
ERROR at line 1:
ORA-14155: missing PARTITION or SUBPARTITION keyword
antoniofongAsked:
Who is Participating?
 
Helena MarkováConnect With a Mentor programmer-analystCommented:
You cannot rename column directly. You can do it the way described in this example (the registration is free):
(http://download-west.oracle.com/docs/cd/A87860_01/doc/server.817/a85397/state17e.htm#2064978)

Example
Rename a Database Object Example
To change the name of table dept to emp_dept, issue the following statement:

RENAME dept TO emp_dept;

You cannot use this statement directly to rename columns. However, you can rename a column using this statement together with the CREATE TABLE statement with AS subquery. The following statements re-create the table static, renaming a column from oldname to newname:

CREATE TABLE temporary (newname, col2, col3)
    AS SELECT oldname, col2, col3 FROM static;

DROP TABLE static;

RENAME temporary TO static;


Your error ORA-14155 is because Oracle "thinks" COLUMN ought to be PARTITION or SUBRARTITION - see this example:
(http://download-west.oracle.com/docs/cd/A87860_01/doc/server.817/a85397/statem2a.htm#2057289)
ALTER TABLE employee RENAME PARTITION emp3 TO employee3;
0
 
mr_oracleCommented:
first if your table gp_ministry  is empty ,you can drop the column gp_ministry_key by the command: alter table gp_ministry drop gp_ministry_key;

second, add new column name gp_ministry_key by the command: alter table gp_ministry add gpages_ministry_key;

if your table is not empty you can move the data of that coumn to temp table , update the column gp_ministry to null, then you can drop it.

also, ithink you can rename a table but not a coulmn. thanks
0
 
janu_sCommented:
Try this

EXEC sp_rename 'table11.[feild1]', 'field2', 'COLUMN'
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
antoniofongAuthor Commented:
sorry, it's not exactly what i wanted. thx anyway.
0
 
fmonroyCommented:
check COMPATIBLE value
maybe is too low and does not support RENAME COLUMN
0
 
suredazzleCommented:
Thanks Hanka!

Oracle can't rename column with Partition.

Your work!

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.