用DBMS_DATAPUMP叶子索引有组织的表格在Oracle 19c中克隆架构

Question

使用脚本来克隆架构从或 this 来源，它在oracle 11g中的工作正常很好我们正在使用。最近，我们切换到Oracle 19c，克隆不再正常。乍一看，一切都还不错，经过仔细检查，索引有组织的表是空的。当我检查这些表的结构时，缺少索引和约束。

索引架构中有组织的表的示例：

CREATE TABLE some_table
(
    id1    INTEGER NOT NULL,
    id2    INTEGER NOT NULL,
    CONSTRAINT pk_some_table PRIMARY KEY (id1, id2) ENABLE VALIDATE
)
ORGANIZATION INDEX
/

CREATE INDEX ix_some_table_01
    ON some_table (id1)
/

CREATE INDEX ix_some_table_02
    ON some_table (id2)
/

ALTER TABLE some_table
    ADD (CONSTRAINT fk_some_table_01 FOREIGN KEY (id1) REFERENCES parent_table1 (id) ENABLE VALIDATE)
/

ALTER TABLE some_table
    ADD (CONSTRAINT fk_some_table_02 FOREIGN KEY (id2) REFERENCES parent_table2 (id) ENABLE VALIDATE)
/

以及目的地（克隆）架构中的同一表：

CREATE TABLE some_table
(
    id1    INTEGER NOT NULL,
    id2    INTEGER NOT NULL,
    CONSTRAINT pk_some_table PRIMARY KEY (id1, id2) ENABLE VALIDATE
)
ORGANIZATION INDEX
/

而且，正如我提到的那样，没有克隆的数据和克隆的索引有组织的表是空的。 我怀疑克隆过程试图在索引自己并且失败之前创建约束（使用索引），但我不确定这是原因。

任何帮助将不胜感激。谢谢！

Answer 1

感谢您使用我的脚本:-)，但对不起，我无法在19.14中复制

SQL> create user demo identified by demo quota 100m on users;

User created.

SQL> grant create session to demo;

Grant succeeded.

SQL> grant create table to demo;

Grant succeeded.

SQL> grant create cluster to demo;

Grant succeeded.

SQL> grant create sequence to demo;

Grant succeeded.

SQL> grant create procedure to demo;

Grant succeeded.

SQL> grant create trigger to demo;

Grant succeeded.

SQL> grant create type to demo;

Grant succeeded.

SQL> grant create operator to demo;

Grant succeeded.

SQL> grant create indextype to demo;

Grant succeeded.

SQL> create table demo.iot (
  2   empno primary key
  3  ,ename
  4  ,job
  5  ,mgr
  6  ,hiredate
  7  ,sal
  8  ,comm
  9  ,deptno
 10  )
 11  organization index
 12  as select * from scott.emp;

Table created.

SQL>
SQL> create table demo.heap (
  2   empno primary key
  3  ,ename
  4  ,job
  5  ,mgr
  6  ,hiredate
  7  ,sal
  8  ,comm
  9  ,deptno
 10  )
 11  as select * from scott.emp;

Table created.

SQL> set serverout on
SQL> exec clone_schema('DEMO','DEMO2',p_drop_new=>false);
173413:Starting job
173601:Final state:COMPLETED
Starting "MCDONAC"."DEMO_SCHEMA_IMP":
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 128 KB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
. . imported "DEMO2"."HEAP"                                  14 rows
. . imported "DEMO2"."IOT"                                   14 rows
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
Job "MCDONAC"."DEMO_SCHEMA_IMP" successfully completed at Fri Jun 3 17:35:00 2022 elapsed 0 00:00:47

PL/SQL procedure successfully completed.

SQL> select count(*) from demo2.heap;

  COUNT(*)
----------
        14

SQL> select count(*) from demo2.iot;

  COUNT(*)
----------
        14