Ora 39083 object type user failed to create with error

While importing the schema from one database to another using Datapump I am getting the below error I am using Sqldeveloper for EXPDP & IMPDP Master table "Ram"."IMPORT_JOB_SQLDEV...

While importing the schema from one database to another using Datapump I am getting the below error
I am using Sqldeveloper for EXPDP & IMPDP

Master table «Ram».»IMPORT_JOB_SQLDEV_3039″ successfully loaded/unloaded
Starting «Ram».»IMPORT_JOB_SQLDEV_3039″:
Processing object type SCHEMA_EXPORT/USER
ORA-39083: Object type USER failed to create with error:
ORA-02380: profile NUID_PROFILE does not exist
Failing sql is:
CREATE USER «Suyash» IDENTIFIED BY VALUES ‘S:636C786C7970CE256AD639A3675D31BCB117F13E37EC0BB266B1E319F5F2;6790453968A8975B’ DEFAULT TABLESPACE «ACSDAT01» TEMPORARY TABLESPACE «TEMP01» PROFILE «NUID_PROFILE»
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
ORA-39083: Object type SYSTEM_GRANT failed to create with error:
ORA-01917: user or role ‘Suyash’ does not exist
Failing sql is:
GRANT CREATE JOB TO «Suyash»
ORA-39083: Object type SYSTEM_GRANT failed to create with error:
ORA-01917: user or role ‘Suyash’ does not exist
Failing sql is:
GRANT CREATE TYPE TO «Suyash»
ORA-39083: Object type SYSTEM_GRANT failed to create with error:
ORA-01917: user or role ‘Suyash’ does not exist
Failing sql is:
GRANT CREATE ANY DIRECTORY TO «Suyash»
ORA-39083: Object type SYSTEM_GRANT failed to create with error:
ORA-01917: user or role ‘ORA-39083’ does not exist
Failing sql is:
GRANT CREATE MATERIALIZED VIEW TO «Suyash»
ORA-39083: Object type SYSTEM_GRANT failed to create with error:
ORA-01917: user or role ‘Suyash’ does not exist
Failing sql is:
GRANT CREATE TRIGGER TO «Suyash»
ORA-39083: Object type SYSTEM_GRANT failed to create with error:
ORA-01917: user or role ‘Suyash’ does not exist
Failing sql is:
GRANT CREATE PROCEDURE TO «Suyash»
ORA-39083: Object type SYSTEM_GRANT failed to create with error:
ORA-01917: user or role ‘Suyash’ does not exist
Failing sql is:
GRANT CREATE SEQUENCE TO «Suyash»
ORA-39083: Object type SYSTEM_GRANT failed to create with error:
ORA-01917: user or role ‘Suyash’ does not exist
Failing sql is:
GRANT CREATE VIEW TO «Suyash»
ORA-39083: Object type SYSTEM_GRANT failed to create with error:
ORA-01917: user or role ‘Suyash’ does not exist
«IMPORTFree.LOG» 15707L, 1873883C

Edited by: 953741 on Aug 20, 2012 12:16 PM

Источник

Содержание

  1. DataPump Import Of Object Types Fails With Errors ORA-39083/ORA-39082 ORA-2304 Or ORA-39117 ORA-39779 (Doc ID 351519.1)
  2. Applies to:
  3. Symptoms
  4. Changes
  5. Cause
  6. To view full details, sign in with your My Oracle Support account.
  7. Don’t have a My Oracle Support account? Click to get started!
  8. ORA-39083 / ORA-01031 ‘ insufficient privileges’ Error Using Data Pump to Import Data when the Schema Used to Import has the Necessary Privileges (Doc ID 2224183.1)
  9. Applies to:
  10. Symptoms
  11. Changes
  12. Cause
  13. To view full details, sign in with your My Oracle Support account.
  14. Don’t have a My Oracle Support account? Click to get started!
  15. DataPump Import (IMPDP) Fails With Errors ORA-39083 & ORA-01861
  16. ORA-39083: Object type failed to create with error
  17. ORA-39083: Object type failed to create with error
  18. Object type failed to create with error
  19. Help: Why is my impdp command producing ORA-39083 and ORA-01917? How to fix?
  20. Answers
  21. impdp transform=segment_attributes

DataPump Import Of Object Types Fails With Errors ORA-39083/ORA-39082 ORA-2304 Or ORA-39117 ORA-39779 (Doc ID 351519.1)

Last updated on NOVEMBER 03, 2020

Applies to:

Oracle Database Cloud Schema Service — Version N/A and later
Oracle Database Exadata Cloud Machine — Version N/A and later
Oracle Cloud Infrastructure — Database Service — Version N/A and later
Oracle Database Cloud Exadata Service — Version N/A and later
Oracle Database Exadata Express Cloud Service — Version N/A and later
Information in this document applies to any platform.

NOTE: In the images and/or the document content below, the user information and data used represents fictitious data. Any similarity to actual persons, living or dead, is purely coincidental and not intended in any manner.

Symptoms

The example below is based on the directory object my_dir that refers to an existing directory on the server where the Oracle RDBMS is installed. Example:

A schema that owns object types needs to be duplicated from schema U1 to schema U2 in the same database. Example:

The schema U1 can be exported successfully. E.g.:

The import into schema U2 fails though with the following errors:

Similar errors with the original export and import utilities are:

Changes

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

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

Oracle offers a comprehensive and fully integrated stack of cloud applications and platform services. For more information about Oracle (NYSE:ORCL), visit oracle.com. пїЅ Oracle | Contact and Chat | Support | Communities | Connect with us | | | | Legal Notices | Terms of Use

Источник

ORA-39083 / ORA-01031 ‘ insufficient privileges’ Error Using Data Pump to Import Data when the Schema Used to Import has the Necessary Privileges (Doc ID 2224183.1)

Last updated on JANUARY 16, 2020

Applies to:

Symptoms

The Data Pump import utility (impdp) fails with errors similar to:

ORA-39083: Object type TABLE:» «.» » failed to create with error:
ORA-01031: insufficient privileges

However the schema that impdp is connecting to has the necessary privileges to create the object. It is noticed that the affected objects have XMLTYPE columns defined with STORE AS SECUREFILE BINARY XML.

Changes

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

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

Oracle offers a comprehensive and fully integrated stack of cloud applications and platform services. For more information about Oracle (NYSE:ORCL), visit oracle.com. пїЅ Oracle | Contact and Chat | Support | Communities | Connect with us | | | | Legal Notices | Terms of Use

Источник

DataPump Import (IMPDP) Fails With Errors ORA-39083 & ORA-01861

I face with this error message while I am doing import process on 11g database.

My source db version is 10g( 10.2.0.4.3 ) and OS is AIX 6.1 and my target db version is 11g RAC( 11.2.0.4.6 ) and OS is AIX 7.1.

The errors:
ORA-39083: Object type PASSWORD_HISTORY failed to create with error:
ORA-01861: literal does not match format string

From related MOS document:

The problem is that DBMS_PSWMG_IMPORT.IMPORT_HISTORY expects passwords to be in ‘yyyy/mm/dd’ format.

The issue is discussed in related bugs
Bug 13039027 – IPB01D : IMPDP ORA-39083: OBJECT TYPE PASSWORD_HISTORY FAILED TO CREATE WI
Bug 14521182 – DATAPUMP IMPORT PASSWORD_HISTORY ORA-39083,ORA-01847,ORA-01861
which are both closed with status ‘Not a Bug’.

For your information, because of security reasons I changed my schema name as SCOOT in below logs.

Complete logs here:
Master table “SYS”.”SYS_IMPORT_FULL_04″ successfully loaded/unloaded
Starting “SYS”.”SYS_IMPORT_FULL_04″: “/******** AS SYSDBA” directory=EXPDP_DIR dumpfile=SCOOT%U.dmp PARALLEL=8 logfile=impdp_SCOOT.log cluster=N DATA_OPTIONS=DISABLE_APPEND_HINT
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:”SCOOT” already exists
ORA-31684: Object type USER:”SCOOTUSR1″ already exists
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/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PASSWORD_HISTORY
ORA-39083: Object type PASSWORD_HISTORY failed to create with error:
ORA-01861: literal does not match format string
Failing sql is:
DECLARE SUBTYPE HIST_RECORD IS SYS.DBMS_PSWMG_IMPORT.ARRAYOFHISTORYRECORDS; HIST_REC HIST_RECORD; i number := 0; BEGIN i := i+1; HIST_REC(i).USERNAME := ‘SCOOT’; HIST_REC(i).PASSWORD := ‘F99CA5C8EC2F8836′; HIST_REC(i).PASSWD_DATE := ’16-03-2015 15:03:34’; i := i+1; HIST_REC(i).USERNAME := ‘SCOOT’; HIST_REC(i).PASSWORD := ’98F7AE6C84E7A2E8′; HIST_REC(i).PASSWD_DATE :=
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA

So here is the solutions:

1. At 10g source environment, first set the NLS_DATE_FORMAT environment variable:

$ export NLS_DATE_FORMAT=YYYY/MM/DD HH24:MI:SS

PS: If you are using AIX than you may hit this error message:
ksh: HH24:MI:SS: is not an identifier

If you hit this message you can use below syntax:

$ export NLS_DATE_FORMAT=”YYYY/MM/DD HH24:MI:SS”

verify:
$ echo $NLS_DATE_FORMAT
YYYY/MM/DD HH24:MI:SS

2. Re-run the Datapump export.

3. Perform the Datapump import into the 11g database using the new dump file.

Reference:
ORA-39083 and ORA-06502 during IMPDP for PASSWORD_HISTORY (Doc ID 1553898.1)
DataPump Import (IMPDP) Fails With Errors ORA-39083 ORA-6550 PLS-00103 On Object Type PASSWORD_HISTORY [ID 1053162.1]

Источник

ORA-39083: Object type failed to create with error

I got ” ORA-39083: Object type failed to create with error ” error in Oracle database.

ORA-39083: Object type failed to create with error

Details of error are as follows.

Object type failed to create with error

Here are the steps to reproduce the issue:

1. Create a table under “TEST0” schema:

5. The index with the system generated name is missing because the primary key constraint was created in the source database without providing a name and before the named index was created, so an index with a system generated name was created to facilitate this primary key constraint. Upon import, Data Pump first imports the indexes (i.e. only the named index TEST0INDEX), and then imports the constraints.

When creating the primary key constraint, we can use the existing named index, so there is no need for the system generated index anymore, and hence it won’t be created. When import tries to import the statistics for the system generated index later on, it fails because the system generated index is missing.

1. Ignore the ORA-39083 (Object type INDEX_STATISTICS failed to create with error) and ORA-01403 (no data found) on this system generated index.

2. Ensure you create named indexes for PK constraints in the source database. E.g.:

3. Do a DataPump import excluding indexes, then import indexes. Doing that we guarantee that the index associated to the primary key constraints will be created first.

The following query can be executed to check the objects created after the import process is completed.

4. Use conventional export/import (exp/imp) (not recommended).

Источник

Help: Why is my impdp command producing ORA-39083 and ORA-01917? How to fix?

EDIT: Please read my second reply for the most recent problem related to my impdp command producing errors.

I have a user/schema called webportal. I want to copy everything from it exactly into a test environment on another machine called dev_webportal. I also created a tablespace for dev_webportal called dev_webportal.

I have the following impdp command:

imdp dev_webportal/xxxx DIRECTORY=dmpdir NETWORK_LINK=old_webportal schemas=webportal:dev_webportal

Things did not go well, and I am not sure why. Here is the initial error message in my log.

Total estimation using BLOCKS method: 7.413 GB

Processing object type SCHEMA_EXPORT/USER

ORA-31684: Object type USER:»DEV_WEBPORTAL» already exists

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/TYPE/TYPE_SPEC

Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE

Processing object type SCHEMA_EXPORT/TABLE/TABLE

ORA-39083: Object type TABLE:»DEV_WEBPORTAL».»CUSTOM_ASICONTACTS» failed to create with error:

ORA-00959: tablespace ‘WEBPORTAL’ does not exist

I am very confused by this track of failures. Shouldn’t the impdp as I read it in the documentation just copy everything from webportal to dev_webportal and rename it from webportal to dev_webportal? If not, what should I be doing? As it stands now, nothing copied into dev_webportal and there were 357 errors reported. I believe these errors are all related to what happened

Basically, I reiterate. All I want to do is copy everything in the user/schema webportal to dev_webportal. The command above clearly did not accomplish this. Is there an easier way to do this?

PS — Sorry for the weird funky user name reported. I tried to change the handle, but only an option for changing email is given. What the heck?

Answers

You should have the exact setup of target like source .

ORA-39083: Object type TABLE:»DEV_WEBPORTAL».»CUSTOM_ASICONTACTS» failed to create with error:

ORA-00959: tablespace ‘WEBPORTAL’ does not exist

>> you need to specify the remap_tablespace in order to table get created in the tablespace you want.

ORA-31684: Object type USER:»DEV_WEBPORTAL» already exists

>>User is already present in the target database.

Also you can try with

impdp transform=segment_attributes

Thanks for your reply. I still have errors and I want to remove them. All I want to do is completely duplicate the webportal user/schema from the production server to my test server. I have the following command:

impdp dev_webportal/xxxx DIRECTORY=dmpdir NETWORK_LINK=old_webportal schemas=webportal remap_schema=webportal:dev_webportal remap_tablespace=webportal:dev_webportal transform=segment_attributes:n

Here is the errors I am obtaining:

Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 7.413 GB

Processing object type SCHEMA_EXPORT/USER

ORA-31684: Object type USER:»DEV_WEBPORTAL» already exists

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/TYPE/TYPE_SPEC

Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE

Processing object type SCHEMA_EXPORT/TABLE/TABLE

. . imported «DEV_WEBPORTAL».»PORTAL_DOCUMENTS» 917 rows

Omitting all the imports until we got the following

ORA-39083: Object type OBJECT_GRANT failed to create with error:

ORA-01917: user or role ‘EXTENSION’ does not exist

GRANT SELECT ON «DEV_WEBPORTAL».»TBI_LOCATIONS» TO «EXTENSION»

Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX

Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

Processing object type SCHEMA_EXPORT/TABLE/COMMENT

Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_SPEC

Processing object type SCHEMA_EXPORT/FUNCTION/FUNCTION

Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE

Processing object type SCHEMA_EXPORT/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPEC

ORA-39082: Object type ALTER_PACKAGE_SPEC:»DEV_WEBPORTAL».»PKG_PORTAL_LIBRARY» created with compilation warnings

Processing object type SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION

ORA-39082: Object type ALTER_FUNCTION:»DEV_WEBPORTAL».»CLIMATE_KEYWORDS» created with compilation warnings

ORA-39082: Object type ALTER_FUNCTION:»DEV_WEBPORTAL».»CLIMATE_DISCIPLINES» created with compilation warnings

Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE

ORA-39082: Object type ALTER_PROCEDURE:»DEV_WEBPORTAL».»KAES_SEND_REMINDER» created with compilation warnings

ORA-39082: Object type ALTER_PROCEDURE:»DEV_WEBPORTAL».»PORTAL_INSERT_POSTERDETAIL» created with compilation warnings

ORA-39082: Object type ALTER_PROCEDURE:»DEV_WEBPORTAL».»INSERT_KEYWORDS» created with compilation warnings

Processing object type SCHEMA_EXPORT/VIEW/VIEW

ORA-39082: Object type VIEW:»DEV_WEBPORTAL».»REPRA_IMPACT_VIEW» created with compilation warnings

ORA-39082: Object type VIEW:»DEV_WEBPORTAL».»REPRA_PROGRAM_DETAIL_VIEW» created with compilation warnings

ORA-39082: Object type VIEW:»DEV_WEBPORTAL».»REPRA_USER_AREA_VIEW» created with compilation warnings

ORA-39082: Object type VIEW:»DEV_WEBPORTAL».»PERSONNEL_MAIN» created with compilation warnings

ORA-39082: Object type VIEW:»DEV_WEBPORTAL».»KEYWORDS_VIEW» created with compilation warnings

Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_BODY

ORA-39082: Object type PACKAGE_BODY:»DEV_WEBPORTAL».»PKG_PORTAL_FORMS» created with compilation warnings

ORA-39082: Object type PACKAGE_BODY:»DEV_WEBPORTAL».»PKG_PORTAL_LIBRARY» created with compilation warnings

ORA-39082: Object type PACKAGE_BODY:»DEV_WEBPORTAL».»PKG_REPRA» created with compilation warnings

ORA-39082: Object type PACKAGE_BODY:»DEV_WEBPORTAL».»WIKI» created with compilation warnings

Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT

Processing object type SCHEMA_EXPORT/TABLE/TRIGGER

ORA-39083: Object type TRIGGER failed to create with error:

ORA-00942: table or view does not exist

It keeps getting worse and then I get this final result

completed with 40 error(s) at 10:25:44

I am very confused. How can I get rid of ORA-31684 and the ORA-39083/ORA-01917 errors? Since I am trying to copy webportal user/schema, it came as a huge surprise there is this second tablespace ‘extension’. How can I refactor my impdp to address these errors? Simply put, how can I make sure I conduct a datapump with no more errors. I feel that I am close as I imported actual data this time, but these errors indicate to me that I didn’t get everything.

Источник

Источник

Problem

​Customers who need to move their Oracle™ database to a new location might encounter import errors with the schema to schema impdp command during database restore operation due to table spaces not existing.

Symptom

SQL failure errors similar to as follows might occur:

Processing object type DATABASE_EXPORT/SCHEMA/USER
ORA-39083: Object type USER:"CONFIG" failed to create with error: ORA-00959: tablespace 'RIS_TEMP' does not exist Failing sql is: CREATE USER "CONFIG" IDENTIFIED BY VALUES '6927AD6126C5EDB6' DEFAULT TABLESPACE "USERS" TEMPORARY TABLESPACE "RIS_TEMP" PASSWORD EXPIRE ACCOUNT LOCK

ORA-39083: Object type USER:"RIODS" failed to create with error: ORA-00959: tablespace 'RIS_TEMP' does not exist Failing sql is: CREATE USER "RIODS" IDENTIFIED BY VALUES 'EDA2AD77A7B97EC7' DEFAULT TABLESPACE "USERS" TEMPORARY TABLESPACE "RIS_TEMP" PASSWORD EXPIRE ACCOUNT LOCK

ORA-39083: Object type USER:"RIDW" failed to create with error: ORA-00959: tablespace 'RIS_TEMP' does not exist Failing sql is: CREATE USER "RIDW" IDENTIFIED BY VALUES 'A9FFF3AFEB2C5AB9' DEFAULT TABLESPACE "USERS" TEMPORARY TABLESPACE "RIS_TEMP" PASSWORD EXPIRE ACCOUNT LOCK

ORA-39083: Object type USER:"RIASSET" failed to create with error: ORA-00959: tablespace 'RIS_TEMP' does not exist Failing sql is: CREATE USER "RIASSET" IDENTIFIED BY VALUES '8D9AEDCDC452CD6E' DEFAULT TABLESPACE "USERS" TEMPORARY TABLESPACE "RIS_TEMP" PASSWORD EXPIRE ACCOUNT LOCK

ORA-39083: Object type USER:"RISCHK" failed to create with error: ORA-00959: tablespace 'RIS_TEMP' does not exist Failing sql is: CREATE USER "RISCHK" IDENTIFIED BY VALUES '4E9D65B260D41388' DEFAULT TABLESPACE "USERS" TEMPORARY TABLESPACE "RIS_TEMP" PASSWORD EXPIRE ACCOUNT LOCK

ORA-39083: Object type USER:"RICALM" failed to create with error: ORA-00959: tablespace 'RIS_TEMP' does not exist Failing sql is: CREATE USER "RICALM" IDENTIFIED BY VALUES '0E25A26491AF10B9' DEFAULT TABLESPACE "USERS" TEMPORARY TABLESPACE "RIS_TEMP" PASSWORD EXPIRE ACCOUNT LOCK

Cause

Table spaces were not defined for these tables and indices.

  • VNF_IDX
  • VNF_32K
  • VSTR_IDX
  • VSTR_32K
  • RIS_TEMP

Environment

Customer environments that use Oracle™ 12c Enterprise Edition database and where a data warehouse is configured.

Diagnosing The Problem

Look for error messages about non-existent table spaces. For example, you might see a message similar to the following:

​ORA-00959: tablespace 'RIS_TEMP' does not exist

Resolving The Problem

These tables and indices belong to the data warehouse. During database set up, it is necessary to run repotools-jts -createWarehouse to create the table space for these tables and indices.

 

The creation of the table spaces is seen in the details of the createWarehouse command:

CREATE TABLESPACE VNF_IDX LOGGING DATAFILE 'c:jazzdb/VNF_IDX_1.ora' SIZE 200M AUTOEXTEND ON NEXT 50M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;

CREATE TABLESPACE VNF_32K LOGGING DATAFILE 'c:jazzdb/VNF_32K_1.ora' SIZE 200M AUTOEXTEND ON NEXT 50M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;

CREATE TABLESPACE VSTR_IDX LOGGING DATAFILE 'c:jazzdb/VSTR_IDX_1.ora' SIZE 200M AUTOEXTEND ON NEXT 50M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;

CREATE TABLESPACE VSTR_32K LOGGING DATAFILE 'c:jazzdb/VSTR_32K_1.ora' SIZE 200M AUTOEXTEND ON NEXT 50M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;

CREATE TEMPORARY TABLESPACE RIS_TEMP TEMPFILE 'c:jazzdb/RIS_TEMP01.DBF' SIZE 250M REUSE AUTOEXTEND ON NEXT 50M MAXSIZE 32767M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1024K;

Do not run the repotools -createTables command instead of repotools -createWarehouse, as it regenerates the JTS com.ibm.team.repository.db.repoLockId in teamserver.properties. The createTables command is not used when you restore a database. Use this command when a new database is created.

Related Information

Document Location

Worldwide

[{«Business Unit»:{«code»:»BU059″,»label»:»IBM Software w/o TPS»},»Product»:{«code»:»SSPRJQ»,»label»:»IBM Engineering Lifecycle Management Base»},»Component»:»Database»,»Platform»:[{«code»:»PF025″,»label»:»Platform Independent»}],»Version»:»All Versions»,»Edition»:»»,»Line of Business»:{«code»:»LOB59″,»label»:»Sustainability Software»}}]

Источник

DataPump Import Of Object Types Fails With Errors ORA-39083 ORA-2304 Or ORA-39117 ORA-39779

<<Back to Oracle DATAPUMP Main Page

ORA-39083: Object type TYPE:»TEST_QA01″.»LOG_RECORD» failed to create with error:
ORA-02304: invalid object identifier literal

Import: Release 12.1.0.2.0 — Production on Tue May 29 07:59:12 2018
Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.
Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 — 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, Oracle Label Security,
OLAP, Advanced Analytics and Real Application Testing options
Master table «SYSTEM».»SYS_IMPORT_FULL_01″ successfully loaded/unloaded
Starting «SYSTEM».»SYS_IMPORT_FULL_01″:  system/********@TEST_QA parfile=import_TEST.par
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/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TYPE/TYPE_SPEC
ORA-39083: Object type TYPE:»TEST_QA01″.»LOG_RECORD» failed to create with error:
ORA-02304: invalid object identifier literal
Failing sql is:
CREATE EDITIONABLE TYPE «TEST_QA01″.»LOG_RECORD»   OID ‘3D90A5F990BE773EF0531103830A52A8’ as object(
        code varchar2(250 char),
        words varchar2(4000 char),
        component varchar2(1000 char),
        severity number(1,0),
        contextTypes varchar2(4000 char),
        contextIds varchar2(1000 char),
        time timestamp(6),
        userName varchar2(250 char),
        stationName varchar2(1
ORA-39083: Object type TYPE:»TEST_QA01″.»TXPRDATE» failed to create with error:
ORA-02304: invalid object identifier literal
Failing sql is:
CREATE EDITIONABLE TYPE «TEST_QA01″.»TXPRDATE»   OID ‘3D90A5F990D8883FE0441103830B52A8’ as object(
        Prohibited integer(1),
        FamilyID varchar2(50),
        KeyVal varchar2(4000)
);

CAUSE

Trying to import an object of type «type» with the same OID already present in the database.eg
The object types «LOG_RECORD» already exist in the source schema TEST_QA02. When the types are exported, oracle also export the object_identifier (OID) of the types. Within the current architecture, the object-identifier needs to be unique in the database.
During import (into the same database «I used remap_schema to duplicate the schema TEST_QA02 as TEST_QA01 in the same PDB»), oracle try to create the same object types in the TEST_QA01 schema. As the OID of the types already exists in the source schema TEST_QA02, the types cannot be created in the target schema TEST_QA01. resulting Import will fail due to:
ORA-02304: invalid object identifier literal.

Solution:

You can use the Import DataPump parameter TRANSFORM which enables you to alter object creation DDL for the types. The value N (= No) for the transform name OID implies that a new OID is assigned.
I modified the Import pafile like below to resolve the issue and it worked.

directory=ASM_DUMP
dumpfile=TEST_EXP_1_QA02%U.dmp
logfile=EXT:TEST_1_QA02_import.log
remap_schema=TEST_QA02:TEST_QA01
parallel=8
TRANSFORM=oid:n

NOTE:-In Oracle10g Release 1 (10.1.0.x.y), you can pre-create the object types and the tables in the target schema, and run an import with the TABLE_EXISTS_ACTION parameter.

For more detail check oracle support note Doc ID 351519.1

Источник

I face with this error message while I am doing import process on 11g database.

My source db version is 10g( 10.2.0.4.3 ) and OS is AIX 6.1 and my target db version is 11g RAC( 11.2.0.4.6 ) and OS is AIX 7.1.

The errors:
ORA-39083: Object type PASSWORD_HISTORY failed to create with error:
ORA-01861: literal does not match format string

From related MOS document:

The problem is that DBMS_PSWMG_IMPORT.IMPORT_HISTORY expects passwords to be in ‘yyyy/mm/dd’ format.

The issue is discussed in related bugs
Bug 13039027 – IPB01D : IMPDP ORA-39083: OBJECT TYPE PASSWORD_HISTORY FAILED TO CREATE WI
Bug 14521182 – DATAPUMP IMPORT PASSWORD_HISTORY ORA-39083,ORA-01847,ORA-01861
which are both closed with status ‘Not a Bug’.

For your information, because of security reasons I changed my schema name as SCOOT in below logs.

Complete logs here:
Master table “SYS”.”SYS_IMPORT_FULL_04″ successfully loaded/unloaded
Starting “SYS”.”SYS_IMPORT_FULL_04″: “/******** AS SYSDBA” directory=EXPDP_DIR dumpfile=SCOOT%U.dmp PARALLEL=8 logfile=impdp_SCOOT.log cluster=N DATA_OPTIONS=DISABLE_APPEND_HINT
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:”SCOOT” already exists
ORA-31684: Object type USER:”SCOOTUSR1″ already exists
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/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PASSWORD_HISTORY
ORA-39083: Object type PASSWORD_HISTORY failed to create with error:
ORA-01861: literal does not match format string
Failing sql is:
DECLARE SUBTYPE HIST_RECORD IS SYS.DBMS_PSWMG_IMPORT.ARRAYOFHISTORYRECORDS; HIST_REC HIST_RECORD; i number := 0; BEGIN i := i+1; HIST_REC(i).USERNAME := ‘SCOOT’; HIST_REC(i).PASSWORD := ‘F99CA5C8EC2F8836′; HIST_REC(i).PASSWD_DATE := ’16-03-2015 15:03:34’; i := i+1; HIST_REC(i).USERNAME := ‘SCOOT’; HIST_REC(i).PASSWORD := ’98F7AE6C84E7A2E8′; HIST_REC(i).PASSWD_DATE :=
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA

So here is the solutions:

1. At 10g source environment, first set the NLS_DATE_FORMAT environment variable:

$ export NLS_DATE_FORMAT=YYYY/MM/DD HH24:MI:SS

PS: If you are using AIX than you may hit this error message:
ksh: HH24:MI:SS: is not an identifier

If you hit this message you can use below syntax:

$ export NLS_DATE_FORMAT=”YYYY/MM/DD HH24:MI:SS”

verify:
$ echo $NLS_DATE_FORMAT
YYYY/MM/DD HH24:MI:SS

2. Re-run the Datapump export.

3. Perform the Datapump import into the 11g database using the new dump file.

Reference:
ORA-39083 and ORA-06502 during IMPDP for PASSWORD_HISTORY (Doc ID 1553898.1)
DataPump Import (IMPDP) Fails With Errors ORA-39083 ORA-6550 PLS-00103 On Object Type PASSWORD_HISTORY [ID 1053162.1]

Источник

May 2, 2021

I got ” ORA-39083: Object type failed to create with error ”  error in Oracle database.

ORA-39083: Object type failed to create with error

Details of error are as follows.

Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
ORA-39083: Object type TABLE_STATISTICS failed to create with error:
A DataPump import using the parameter REMAP_SCHEMA reports errors ORA-39083 and ORA-1403 while importing index statistics:

...
ORA-39083: Object type INDEX_STATISTICS failed to create with error:
ORA-01403: no data found
ORA-01403: no data found
Failing sql is:
DECLARE IND_NAME VARCHAR2(60); IND_OWNER VARCHAR2(60); BEGIN DELETE FROM
"SYS"."IMPDP_STATS"; SELECT index_name, index_owner INTO IND_NAME, IND_OWNER
FROM (SELECT UNIQUE sgc1.index_name, sgc1.index_owner,
COUNT(*) mycount
FROM sys.ku$_find_sgc_view sgc1,
TABLE (sgc1.col_list) myc


Object type failed to create with error


Here are the steps to reproduce the issue:


1. Create a table under “TEST0” schema:


#> sqlplus test0/password

DROP TABLE TEST0 PURGE;

CREATE TABLE TEST0
(
   COL1 NUMBER NOT NULL,
   COL2 NUMBER NOT NULL,
   COL3 NUMBER NOT NULL
)
TABLESPACE USERS;

ALTER TABLE TEST0 ADD (PRIMARY KEY (COL2, COL1, COL3) USING INDEX TABLESPACE USERS);

CREATE INDEX TEST0INDEX ON TEST0 (COL3, COL1, COL2) TABLESPACE USERS;

select object_name, object_type from user_objects;

OBJECT_NAME                    OBJECT_TYPE
------------------------------ -------------------
TEST0                          TABLE
SYS_C009845                    INDEX <-- Index associated with PK constraint.
TEST0INDEX                     INDEX

2. Collect statistics for "TEST0" schema.

#> sqlplus system/password
exec dbms_stats.gather_schema_stats('TEST0',cascade => TRUE);

3. Execute the DataPump export an import process to reproduce the problem.

#> expdp system/password schemas=test0 dumpfile=test0.dmp
...

(completed successfully without warnings)

#> impdp system/password remap_schema=test0:test1 dumpfile=test0.dmp

...
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
ORA-39083: Object type INDEX_STATISTICS failed to create with error:
ORA-01403: no data found
ORA-01403: no data found
Failing sql is:
DECLARE IND_NAME VARCHAR2(60); IND_OWNER VARCHAR2(60); BEGIN DELETE FROM "SYS"."IMPDP_STATS";
SELECT index_name, index_owner INTO IND_NAME, IND_OWNER
FROM (SELECT UNIQUE sgc1.index_name, sgc1.index_owner,
COUNT(*) mycount
FROM sys.ku$_find_sgc_view sgc1,
TABLE (sgc1.col_list) myc
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/POST_SCHEMA/PROCACT_SCHEMA

4. Open a sqlplus session to check objects created for the import process under "TEST1" schema.

#> sqlplus test1/password

col object_name for a30
SELECT OBJECT_NAME, OBJECT_TYPE FROM USER_OBJECTS;

OBJECT_NAME                    OBJECT_TYPE
------------------------------ -------------------
TEST0                          TABLE
TEST0INDEX                     INDEX





5. The index with the system generated name is missing because the primary key constraint was created in the source database without providing a name and before the named index was created, so an index with a system generated name was created to facilitate this primary key constraint. Upon import, Data Pump first imports the indexes (i.e. only the named index TEST0INDEX), and then imports the constraints.


When creating the primary key constraint, we can use the existing named index, so there is no need for the system generated index anymore, and hence it won’t be created. When import tries to import the statistics for the system generated index later on, it fails because the system generated index is missing.


1. Ignore the ORA-39083 (Object type INDEX_STATISTICS failed to create with error) and ORA-01403 (no data found) on this system generated index.


— or —


2. Ensure you create named indexes for PK constraints in the source database. E.g.:


-- instead of:
ALTER TABLE TEST0 ADD (PRIMARY KEY (COL2, COL1, COL3) USING INDEX TABLESPACE USERS);

-- do:
ALTER TABLE TEST0 ADD CONSTRAINT MY_PK PRIMARY KEY (COL2, COL1, COL3) USING INDEX TABLESPACE USERS);


— or —


3. Do a DataPump import excluding indexes, then import indexes. Doing that we guarantee that the index associated to the primary key constraints will be created first.





  



                       
  






$ impdp system/password remap_schema=test0:test1 exclude=index dumpfile=test0.dmp

$ impdp system/password remap_schema=test0:test1 include=index dumpfile=test0.dmp


The following query can be executed to check the objects created after the import process is completed.


#> sqlplus test1/password

col object_name for a30
SELECT OBJECT_NAME, OBJECT_TYPE FROM USER_OBJECTS;

OBJECT_NAME                    OBJECT_TYPE
------------------------------ -------------------
TEST0                          TABLE
SYS_C009868                    INDEX
TEST0INDEX                     INDEX

-- or --


4. Use conventional export/import (exp/imp) (not recommended).


Do you want to learn Oracle Database for Beginners, then read the following articles.




Oracle Tutorial | Oracle Database Tutorials for Beginners ( Junior Oracle DBA )




  1,515 views last month,  1 views today












About Mehmet Salih Deveci









I am Founder of SysDBASoft IT and IT Tutorial and Certified Expert about Oracle & SQL Server database, Goldengate, Exadata Machine, Oracle Database Appliance administrator with 10+years experience.I have OCA, OCP, OCE RAC Expert Certificates I have worked 100+ Banking, Insurance, Finance, Telco and etc. clients as a Consultant, Insource or Outsource.I have done 200+ Operations in this clients such as Exadata Installation & PoC & Migration & Upgrade, Oracle & SQL Server Database Upgrade, Oracle RAC Installation, SQL Server AlwaysOn Installation, Database Migration, Disaster Recovery, Backup Restore, Performance Tuning, Periodic Healthchecks.I have done 2000+ Table replication with Goldengate or SQL Server Replication tool for DWH Databases in many clients.If you need Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS Consultancy and Training you can send my email adress [email protected].-                                                                                                                                                                                                                                                 -Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS ve linux Danışmanlık ve Eğitim için  [email protected] a mail atabilirsiniz.










Источник








The error:


Connected to: Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Master table "SYS"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_FULL_01":  userid="/******** AS SYSDBA" directory=exp dumpfile=moot_%U.dmp logfile=importmoot.log parallel=4 full=y cluster=n
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
ORA-39083: Object type ROLE_GRANT failed to create with error:
ORA-01919: role 'ADMIN_MOOT' does not exist
Failing sql is:
 GRANT "ADMIN_MOOT" TO "MOOT"
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/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
......


This is a common error when the export in the source database is made at schema or tablespace level. Schema/tablespace level don’t include create role DDL, only the DDL to grant the role to the user. I mean, there is no command in the dump like this: 





  



                       
  






create role role_name;


Just the ddl to grant the role to the schema.


grant role_name to schema_name; 


This is very clear in the image bellow. I believe everyone knows it because we cannot specify some schema name in create role command ie: create role schema_name.role_name, this is not possible. Database role doesn’t belong to a schema (user).




So, the workaround is:


If you already made the import





  



                       
  






    

  • Generate the role ddl from source database and manually create the role in the target where the import was done. You can use the script at the end of this post.
    • 



    

  • Do the import again, but with the option include=role_grant, this option will grant only the roles to the users.
    • 



If you still will do the import


    

  • Generate all roles DDL in the source database.
    • 



    

  • Import with the option exclude=role_grant.
    • 



    

  • Create all roles manually in the target with the generated roles DDL.
    • 



    

  • Import again with the option include=role_grant.
    • 



To generate the ROLE DDL, you can use this script from Tim Hall




-- -----------------------------------------------------------------------------------
-- File Name    : https://oracle-base.com/dba/script_creation/role_ddl.sql
-- Author       : Tim Hall
-- Description  : Displays the DDL for a specific role.
-- Call Syntax  : @role_ddl (role)
-- Last Modified: 28/01/2006
-- -----------------------------------------------------------------------------------
SET LONG 999999999 LONGCHUNKSIZE 20000 PAGESIZE 0 LINESIZE 1000 FEEDBACK OFF VERIFY OFF TRIMSPOOL ON ECHO OFF;
column ddl format a1000
begin
   dbms_metadata.set_transform_param (dbms_metadata.session_transform, 'SQLTERMINATOR', true);
   dbms_metadata.set_transform_param (dbms_metadata.session_transform, 'PRETTY', true);
end;
/
 
variable v_role VARCHAR2(30);
exec :v_role := upper('&ROLE_NAME');
select dbms_metadata.get_ddl('ROLE', r.role) AS ddl
from   dba_roles r
where  r.role = :v_role
union all
select dbms_metadata.get_granted_ddl('ROLE_GRANT', rp.grantee) AS ddl
from   dba_role_privs rp
where  rp.grantee = :v_role
and    rownum = 1
union all
select dbms_metadata.get_granted_ddl('SYSTEM_GRANT', sp.grantee) AS ddl
from   dba_sys_privs sp
where  sp.grantee = :v_role
and    rownum = 1
union all
select dbms_metadata.get_granted_ddl('OBJECT_GRANT', tp.grantee) AS ddl
from   dba_tab_privs tp
where  tp.grantee = :v_role
and    rownum = 1
/
SELECT '--END OF COMMANDS' FROM DUAL;
UNDEFINE ROLE_NAME
set linesize 80 pagesize 50000 feedback on verify on





Plus:


As you see in the image above, public synonyms are not exported as well at schema level, you must worry about it. Check this link bellow.








Источник







  



                       
  





	









    
    
    
    



    

                
Понравилась статья? Поделить с друзьями:

        
        

























    




Читайте также: 


			

  • Ora 29400 data cartridge error
    • 

  • Ora 01119 error in creating database file
    • 

  • Oracle exception код ошибки
    • 

  • Ora 29285 file write error
    • 

  • Ora 01114 io error writing block to file block

    • 







    






    
    
    
    
            
    
            
    
            
    
                
    
                    0
                    0
                    голоса
    
                
    Рейтинг статьи
    
                
    
            
        
    
                    
    
                            
    
            
    
                
    
                                            
    
                             Подписаться
                            
                        
    
                                            
    
                                                
                    
    
                
    
                                                    
    
                                                    
    
                                
    Уведомить о 
    
                                
    
                                    
                                
    
                                                                    
                                                                    
    
                                    
                                
    
                                                            
    
                                                
    
                            
    
                                        
    
                                        
    
                        
    
                            
    
                                                                                                        
    
                                        guest                                    
    
                                                
                                        
    
                        
    
                    
    
                    
                                        
                    
                    
    
                        
    
                
                    
    
                        
    
                
    
                    
    
                        0 комментариев                    
    
                                        
    
                    
    
                                                    
    
                                
    
                                                        
    
                                
    
                                                        
    
                                                                        Старые
                                                                        
                                
    
                                                                            Новые
                                                                                Популярные
                                                                        
    
                            
    
                                                
    
                
    
                
    
                    
     Межтекстовые Отзывы                     
    
                    
    Посмотреть все комментарии