Orphand SYS_PLSQL_% Objects Are Created When Using Data Pump Import or Original Import (Doc ID 757588.1)

Last updated on NOVEMBER 28, 2016

Applies to:

Oracle Server - Enterprise Edition - Version: 10.1.0.2 to 11.1.0.6 - Release: 10.1 to 11.1
Oracle Server - Enterprise Edition - Version: 10.1.0.2 to 11.1.0.6   [Release: 10.1 to 11.1]
Information in this document applies to any platform.

Symptoms

SQL> create table tc.tab (a number);
SQL> create or replace package tc.pkg as
       type coll is table of tab%rowtype;
       function f return coll pipelined;
      end;
      /

SQL> select owner,type_name
     from dba_types where owner='TC';

OWNER   TYPE_NAME
------- ------------------------------
TC      SYS_PLSQL_66985_10_1
TC      SYS_PLSQL_66985_DUMMY_1


SQL> select object_id,owner,object_name,object_type
     from dba_objects
     where object_id in (66985,66986);

OBJECT_ID   OWNER OBJECT_NAME  OBJECT_TYPE
---------- ------ ------------ -------------------
66985      TC     TAB          TABLE
66986      TC     PKG          PACKAGE

 

 

 

TEST CASE

-- Create test objects
drop user tc cascade;
create user tc identified by tc default tablespace users;
grant dba to tc;

create table tc.tab (a number);
create or replace package tc.pkg as
  type coll is table of tab%rowtype; 
  function f return coll pipelined;
end;
/

SQL> select owner,type_name from dba_types where owner='TC';

OWNER     TYPE_NAME
--------- ------------------------------
TC        SYS_PLSQL_66934_10_1
TC        SYS_PLSQL_66934_DUMMY_1
TC        SYS_PLSQL_66935_9_1
TC        SYS_PLSQL_66935_DUMMY_1


-- Create directory
create or replace directory test_dp as '/tmp';

-- Export/Import from/into own schema
host rm /tmp/export_schemas.dmp
host expdp tc/***** DIRECTORY=test_dp DUMPFILE=export_schemas.dmp

-- Drop user
drop user tc cascade;

-- Import objects
host impdp system/******* directory=test_dp DUMPFILE=export_schemas.dmp

-- Check object ids after dropping & re-importing schema

SQL> select owner,type_name from dba_types where owner='TC';

OWNER     TYPE_NAME
--------- ------------------------------
TC        SYS_PLSQL_66934_10_1    <== orphand type
TC        SYS_PLSQL_66934_DUMMY_1 <== orphand type
TC        SYS_PLSQL_66935_9_1     <== orphand type
TC        SYS_PLSQL_66935_DUMMY_1 <== orphand type
TC        SYS_PLSQL_66985_10_1
TC        SYS_PLSQL_66985_DUMMY_1
TC        SYS_PLSQL_66986_9_1
TC        SYS_PLSQL_66986_DUMMY_1

-- Determine existing Object ids
SQL> select object_id,owner,object_name,object_type
from dba_objects where object_id in (66934,66935,66986,66985);

OBJECT_ID  OWNER      OBJECT_NAME        OBJECT_TYPE
---------- ---------- ------------------ -------------------
66985      TC         TAB                TABLE
66986      TC         PKG                PACKAGE


 

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms