ORACLE-1: Virtual column affects alter to modify table Field Operation!

First, the question:

Yesterday I want to modify a table in the Oracle database a field, found how the changes are not successful!!! and gives the following tips:

　　ORA-54031: The column to be deleted or modified is used by a virtual column expression　

Ii. What is a "virtual column" "Invisible column"

The creation of a virtual column is usually created automatically by the database, and of course it can be created manually.

When to create: data in the table, large or frequent changes, the database will typically automatically create virtual columns!

I think it's easier to understand the cut of a beautiful figure than one of my words a word knocks! Haha, huh!

Oralce's virtual columns address some of the data information that was previously required to use triggers or to calculate statistics from code. Each time the other columns were counted, the new columns were generated using the method of adding new columns by statistical calculation in the SELECT statement, and the execution was inefficient and error-prone because of the lengthy and complex query SQL statements. The development efficiency and the execution efficiency of the program are greatly reduced. The introduction of the Oralce Virtual column solves this problem.

There are also some problems with Oralce's virtual columns. You cannot use the INSERT into talbe_name values (). statement, when you add data to a table that contains a virtual column, you must write the column name of the added table to the INSERT statement. The virtual column name cannot appear in the INSERT INTO table_name (LIST1,LIST2,... listend) column name, otherwise an error is indicated.

Here's a list of solutions:

Second, several solutions

(1) Back up the data, back up the table structure, delete the table, create the table (modify the column information), and then import the data!

When the amount of data is small, it does not matter, can do so, but if the amount of data too much, then the drawbacks are obvious!

(2) Drop the current virtual column. The process is as follows:

1--: Queries the virtual column information for the specified table:2--Select Column_name,data_default,hidden_column3--From User_tab_cols4--where table_name= ' Cb_batch_flow ' and column_name= ' Bfl_oprno '5 6--Description: Modify the steps:7--(1Query the User_tab_cols table first. The column's virtual column data_default the field information, taking the hash value8--(2) if (1) to find the hash value, you need to perform EXEC delete the column of the virtual column operation, if you do not find the hash value, you do not need to delete the virtual column9--(3After you delete a virtual column, you can alter theTen--format: One--Exec dbms_stats.drop_extended_stats (' Ebank ', ' cb_batch_flow ', ' Bfl_cstno,ccp_userid '); ---Delete a virtual column A--ALTER TABLE Cb_batch_flow modify Bfl_cstno varchar2 (10); ---Modifying column information ---Select Dbms_stats.create_extended_stats (' Ebank ', ' Cb_batch_flow ', ' (Ccp_cstno,ccp_userid) ') from DUAL; ---new virtual columns

Perform:

1. Exec Execute method parameter incoming error

2. Successfully deleted virtual column

3. Create a virtual column successfully

However, the value of the virtual column that was successfully created in the: User_tab_cols table in the column_name field is randomly stored,

　

ORACLE-1: Virtual column affects alter to modify table Field Operation!