用DBMS_DATAPUMP叶索引组织表空克隆Oracle19c中的模式
用DBMS_DATAPUMP叶索引组织表空克隆Oracle19c中的模式
提问于 2022-06-02 07:18:44
当使用脚本从这或这源代码克隆模式时，它在我们正在使用的Oracle11g中非常正常。最近，我们转向了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
/而且，正如我所提到的，没有数据被克隆，而克隆的索引组织表则是空的。我怀疑克隆过程试图在索引本身之前创建约束(使用索引)，但我不确定这是否是原因。
任何帮助都将不胜感激。谢谢!
回答 1
Stack Overflow用户
发布于 2022-06-03 09:40:29
谢谢你用我的剧本:-)对不起，我不能在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页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/72472151
复制相关文章
相似问题
腾讯云开发者
