Insert as select on a table with 759 columns including Vitrual columns
Dear Folks,
DB version: 11.2.0.3.4
Quality : Production
Type: 2 node RAC
Issue Description: We were encountering repeated ORA-00600 when we tried expdp a table as part of the application process. This was later explored as a logical corruption on primary key index. Application team is working on dropping and recreating the index as per the oracle support suggestion.
In the meantime, I have been trying to copy the impacted table data using insert as select , since pump/conventional exp/CTAS dont either work or fulfill the requirement.
The affected table contains more than 255 columns, to be accurate - 757 normal columns and 2 virtual columns, those 2 virtual columns are used to build composite subpartition on the table.The subpartitions are BASIC compressed.
Since we cant load data onto a virtual column, i have to exclude those 2 virtual columns and load data on to rest of the columns, the virtual columns get populated based on the input to other 2 columns. With number of columns to select in the insert greater than 255, I am not able to load data using sql *plus and sql developer, since they arent able handle such huge select list.
SQL * Plus fails with "Too Long" error
SQL Developer fails with "left/right parenthesis not found"
I dont have toad license, so I havent tried the same, not sure if TOAD can handle such huge select list either.
My insert would look like the below..
Insert into target (t1,t2,t3....t757) select (t1,t2,t3...t757) from source;
Can someone please give me some directions?
Thanks & Regards
Raja