ANBOB™

对于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的参考，尤其是自定义类型的列。

打赏

微信扫一扫，打赏作者吧～