My Oracle Support Banner

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

Last updated on MAY 09, 2018

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

To view full details, sign in with your My Oracle Support account.

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


In this Document
  Symptoms
     TEST CASE
  Cause
  Solution
     FIX
     WORKAROUND
  References

This document is being delivered to you via Oracle Support's Rapid Visibility (RaV) process and therefore has not been subject to an independent technical review.

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.