Thanks for the question, ahmed.
Asked: December 13, 2016 - 12:35 pm UTC
Last updated: December 19, 2016 - 5:29 am UTC
Version: 11c
Viewed 1000+ times
You Asked
Hey all
I have create table with virtual columns like the following
CREATE TABLE employees (
id NUMBER,
first_name VARCHAR2(10),
last_name VARCHAR2(10),
salary NUMBER(9,2),
comm1 NUMBER(3),
comm2 NUMBER(3),
salary1 AS (ROUND(salary*(1+comm1/100),2)),
salary2 NUMBER GENERATED ALWAYS AS (ROUND(salary*(1+comm2/100),2)) VIRTUAL,
salary3 NUMBER GENERATED ALWAYS AS (get_sal(id)) VIRTUAL,-- (get_sal) is function in database
CONSTRAINT employees_pk PRIMARY KEY (id)
);
The problem is ,When i'm trying impdp from dump files returns the next error, with table which has the virtual column, any one have idea?
ora-39151 table exists. all dependent metadata and data will be skibing
and Connor said...
SQL> create user demo identified by demo;
User created.
SQL>
SQL> alter user demo quota 100m on users;
User altered.
SQL> grant create session, create table to demo;
Grant succeeded.
SQL>
SQL> create or replace
2 function demo.get_sal(p int) return int deterministic is
3 begin
4 return 0;
5 end;
6 /
Function created.
SQL>
SQL> CREATE TABLE demo.employees (
2 id NUMBER,
3 first_name VARCHAR2(10),
4 last_name VARCHAR2(10),
5 salary NUMBER(9,2),
6 comm1 NUMBER(3),
7 comm2 NUMBER(3),
8 salary1 AS (ROUND(salary*(1+comm1/100),2)),
9 salary2 NUMBER GENERATED ALWAYS AS (ROUND(salary*(1+comm2/100),2)) VIRTUAL,
10 salary3 NUMBER GENERATED ALWAYS AS (get_sal(id)) VIRTUAL,-- (get_sal) is function in database
11 CONSTRAINT employees_pk PRIMARY KEY (id)
12 );
Table created.
SQL> grant read, write on directory temp to demo;
Grant succeeded.
C:\Users\comcdona\Desktop\pics\fire>expdp userid=demo/demo dumpfile=demo directory=TEMP
Export: Release 12.1.0.2.0 - Production on Mon Dec 19 13:26:03 2016
Copyright (c) 1982, 2016, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Advanced Analytics
and Real Application Testing options
FLASHBACK automatically enabled to preserve database integrity.
Starting "DEMO"."SYS_EXPORT_SCHEMA_01": userid=demo/******** dumpfile=demo directory=TEMP
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 0 KB
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/FUNCTION/FUNCTION
Processing object type SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
Processing object type SCHEMA_EXPORT/POST_SCHEMA/PROCACT_SCHEMA
. . exported "DEMO"."EMPLOYEES" 0 KB 0 rows
Master table "DEMO"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for DEMO.SYS_EXPORT_SCHEMA_01 is:
C:\TEMP\DEMO.DMP
Job "DEMO"."SYS_EXPORT_SCHEMA_01" successfully completed at Mon Dec 19 13:26:31 2016 elapsed 0 00:00:28
SQL> drop user demo cascade;
User dropped.
SQL> create user demo identified by demo;
User created.
SQL>
SQL> alter user demo quota 100m on users;
User altered.
SQL> grant create session, create table, create procedure to demo;
Grant succeeded.
SQL> grant read, write on directory temp to demo;
Grant succeeded.
C:\Users\comcdona\Desktop\pics\fire>impdp userid=demo/demo dumpfile=demo directory=TEMP
Import: Release 12.1.0.2.0 - Production on Mon Dec 19 13:28:49 2016
Copyright (c) 1982, 2016, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Advanced Analytics
and Real Application Testing options
Master table "DEMO"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "DEMO"."SYS_IMPORT_FULL_01": userid=demo/******** dumpfile=demo directory=TEMP
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "DEMO"."EMPLOYEES" 0 KB 0 rows
Processing object type SCHEMA_EXPORT/FUNCTION/FUNCTION
Processing object type SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
Processing object type SCHEMA_EXPORT/POST_SCHEMA/PROCACT_SCHEMA
Job "DEMO"."SYS_IMPORT_FULL_01" successfully completed at Mon Dec 19 13:28:52 2016 elapsed 0 00:00:02
If you have problems with remap_schema as per the review, please check MOS for
Bug 17943479 : ORA-39083/ORA-904 from Import with REMAP_SCHEMA for virtual column or function based index on PLSQL function
Rating
(1 rating)
Is this answer out of date? If it is, please let us know via a Comment