1

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?

Barbaros Özhan
  • 59,113
  • 10
  • 31
  • 55
Jett
  • 781
  • 1
  • 14
  • 30

2 Answers2

3

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.

Barbaros Özhan
  • 59,113
  • 10
  • 31
  • 55
1

did you create table B also with derived columns ? from your question i presume you created tableB also with virtual columns..

One thing you need to notice is since you have a large volume of records to insert , use bulk mode for faster operation.. use append hint as shown below. Please note - you need not include virtual columns in below statement as they would be calculated on the fly.

insert  /*+ APPEND */   into tableB (column1, column2,...columnn) select column1, column2,...columnn from TableA
Hijesh V L
  • 191
  • 1
  • 5