对于UDTs自定义数据库类型是oracle扩展的数据类型，对一个对象存储更多的信息，在oracle8版本就存在， 可以在创建表对象用于列类型，前段在一套库迁移时遇到个问题，应用软件使用了ArcGIS 空间数据库组件， 对应数据库中的SDE schema用户，应用部署希望drop sde用户使用软件安装重新生成，但是这会导致其它用户的table使用了该用户的类型导致数据丢失。下面是测试
SQL> create user user1 identified by pwd_123;
SQL> create user user2 identified by pwd_123;
SQL> grant create type to user1;
SQL> grant create table to user2
SQL> alter user user2 quota unlimited on users;
SQL> grant execute on user1.external_person to user2;
SQL> CREATE TYPE user1.external_person AS OBJECT (
name VARCHAR2(30),
phone VARCHAR2(20) );
SQL> CREATE TABLE user2.contacts (
contact user1.external_person,
ctime DATE );
SQL> select OWNER,TABLE_NAME,COLUMN_NAME,DATA_TYPE,DATA_TYPE_OWNER from dba_tab_columns where owner='USER2';
OWNER TABLE_NAME COLUMN_NAME DATA_TYPE DATA_TYPE_OWNER
------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------
USER2 CONTACTS CONTACT EXTERNAL_PERSON USER1
USER2 CONTACTS CTIME DATE
insert into user2.contacts values ( user1.external_person('police','110'),sysdate);
insert into user2.contacts values ( user1.external_person('anbob','13000000000'),sysdate);
SQL> select * from user2.contacts;
CONTACT(NAME, PHONE)
--------------------------------------------------------------------------------
CTIME
-------------------
EXTERNAL_PERSON('police', '110')
2021-06-15 05:39:19
EXTERNAL_PERSON('anbob', '13000000000')
2021-06-15 05:40:04
下面删除用户USER1, 会导致user2的表列丢失, 即使在所对象创建也于事无补
SQL> drop user user1 cascade;
SQL> set lines 80
SQL> desc user2.CONTACTS
Name Null? Type
------------------------------- -------- ----------------------------
1 CTIME DATE
SQL> select * from user2.CONTACTS;
CTIME
-------------------
2021-06-15 05:39:19
2021-06-15 05:40:04
SQL> create user user1 identified by Zas_xxx;
SQL> grant create type to user1;
CREATE TYPE user1.external_person AS OBJECT (
name VARCHAR2(30),
3 phone VARCHAR2(20) );
4 /
Type created.
SQL> select * from user2.CONTACTS;
CTIME
-------------------
2021-06-15 05:39:19
2021-06-15 05:40:04
如果在impdp导入时，如果没有UDT ower 的schema, 参考OWNER的表会创建失败。
[oracle@oel7db1 tpt-oracle-master]$ impdp system/oracle@cdb1pdb1 dumpfile=udt.dump schemas=user2 directory=ORACLE_BASE
Import: Release 19.0.0.0.0 - Production on Tue Jun 15 06:05:46 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Master table "SYSTEM"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_SCHEMA_01": system/********@cdb1pdb1 dumpfile=udt.dump schemas=user2 directory=ORACLE_BASE
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
ORA-39117: Type needed to create table is not included in this operation. Failing sql is:
CREATE TABLE "USER2"."CONTACTS" ("CONTACT" "USER1"."EXTERNAL_PERSON" , "CTIME" DATE) SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "USERS"
所以后期如果对于删用户里，需要注意是否有其它SCHEMA的参考，尤其是自定义类型的列。