sqldatabaseoracle-databaseoracle11gvirtual-column

How to copy data from TableA to TableB with new partitions?

I have TableA that has hundreds of thousands of rows and is still increasing in size. With no partitions, the speed has decreased very noticeably.

So I made a new table called TableB made columns exactly like (both name and type) TableA in Oracle SQL Developer. (TableA and TableB are in the same database but not the same tables) I additionally created partitions for TableB.

Now, all I want to do is copy all the data from TableA from TableB in order to test the speeds of queries.

In order to test speeds of tables with partitions, I decided to copy all of the data now that TableB has all the same columns as A. 

insert into TableB ( select * from TableA);

What I expected from the statement above was the data to be copied over but instead, I got the error:

Error starting at line : 1 in command - insert into TableB ( select * from TableA) Error at Command Line : 1 Column : 1 Error report - SQL Error: ORA-54013: INSERT operation disallowed on virtual columns 54013. 0000 - "INSERT operation disallowed on virtual columns" *Cause: Attempted to insert values into a virtual column *Action: Re-issue the statment without providing values for a virtual column

I looked up Virtual Columns and it seems to be

"When queried, virtual columns appear to be normal table columns, but their values are derived rather than being stored on disc. The syntax for defining a virtual column is listed below."

However, I do not have any data in TableB whatsoever. TableB only has the columns that match TableA so I am unsure as to how my columns can be derived, when there is nothing to derive?

Solution

  • You can use the query 

    SELECT column_name, virtual_column
  FROM user_tab_cols
 WHERE table_name = 'TABLEA';

COLUMN_NAME VIRTUAL_COLUMN
----------- --------------
ID          NO
COL1        NO
COL2        NO
COL3        YES

    Then use

    INSERT INTO TABLEB(ID,COL1,COL2) SELECT ID,COL1,COL2 FROM TABLEA;

    to be exempt from the virtual columns, those are calculated ones from the other columns' values.