We help IT Professionals succeed at work.

How to drop system generated virtual column in a table in12c

jl66
jl66 asked
on
3,849 Views
Last Modified: 2018-02-02
Found some columns in a table which start with sys_sts... long column names. and failed to drop them. Can any gurus shed some light on them? Thanks a lot.
Comment
Watch Question

Information Technology Specialist
CERTIFIED EXPERT
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
awking00Information Technology Specialist
CERTIFIED EXPERT

Commented:
You might also consider using the listagg function on the column_names and drop them all at the same time.
jl66Consultant

Author

Commented:
Thanks for the info. I ran it.
It complains that "PLS-00302: component 'VIRTUAL_COLUMN' must be declared", which is "v_sql := 'alter table '||c.table_name||' drop '||c.virtual_column;"  Why?
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
awking00Information Technology Specialist
CERTIFIED EXPERT

Commented:
slightwv, thanks for catching my typo.
jl66Consultant

Author

Commented:
Thanks a lot for the info.
I guess those columns are from collecting stat. I got the problem because when I inserted to the table, I got errors and had to consider some values for the virtual columns, which I had no idea.
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
>>I got the problem because when I inserted to the table

This is why you need to always include column names on the inserts.

Instead of:
insert into some_table values('a');

You need to write all your code to do:
insert into some_table(some_column) values('a');

Then as new columns are added to the table, the code doesn't break.

>>I guess those columns are from collecting stat

I've never seen virtual columns added to a table based on system generated stats.  Are you sure they are Oracle system generated or some application running against the database is the 'system' that generated them?
jl66Consultant

Author

Commented:
Now it complains about the line: "execute immediate v_sql;"
---
ORA-00942: table or view does not exist
ORA-06512: at line 7
---

can they be dropped?
I manually ran the query: "select table_name, column_name from user_tab_cols where virtual_column = 'YES' and table_name = <yourtable>" and got 2 columns back. They are there.
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
For two columns in one table, just drop them?  Why worry about the PL/SQL loop?

I'm still going to suggest that if something added those columns, then something might need them.  Dropping them might break things.

Anyway, it looks like another simple typo.  These can be fixed by looking at the syntax of the alter table drop column syntax and correcting the code.

I'm not sure why it is generating a ORA-00942 but I think there is another syntax issue:
v_sql := 'alter table '||c.table_name||' drop column '||c.column_name;


You can check the syntax by adding this above the execute immeidate:
dbms_output.put_line(v_sql);

You can then verify the SQL has the correct syntax.
jl66Consultant

Author

Commented:
The error messages were a bit misled. The error messages are actually
--
ORA-12996: cannot drop system-generated virtual column
ORA-06512: at line 7
--
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
Well, there you go.  You cannot drop them so you'll need to fix all the code that does the inserts.
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
I've seen some references that the virtual column might be due to function based indexes.  If that is what you have, then dropping them could be a really bad idea.
awking00Information Technology Specialist
CERTIFIED EXPERT

Commented:
My suspicions are that someone has create extended statistics, which is used for optimizer enhancements. The following link provides  good description and explanation of its values.

https://blogs.oracle.com/optimizer/entry/extended_statistics
jl66Consultant

Author

Commented:
Thanks for all the tips and info. Since the "extra" columns prevented "inserting", the table was rebuilt to remove the columns. At least now, the insert is OK, but do not know how long it lasts.
jl66Consultant

Author

Commented:
Thanks for the effort.
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.