Server Utilities :: ORA-31685 Deploy Schema Using DATAPUMP API

Feb 7, 2011

I'm trying to deploy the schema using DATAPUMP API. The user from which the schema get deployed has the direct privilege of CREATE USER (not through role). But got the insufficient privileges error.

Processing object type SCHEMA_EXPORT/USER
ORA-31685: Object type USER:"SCOTT1" failed due to insufficient privileges. Failing sql is:
CREATE USER "SCOTT1" IDENTIFIED BY VALUES '4EBB0DDE3C79FE47' DEFAULT TABLESPACE "USERS"
TEMPORARY TABLESPACE "TEMP2" PROFILE "APP_PROFILE".

But the user get created successfully when run the CREATE statement manually.I have created the user manually and again run the deployment procedure. Got the below error for ROLE_GRANTS.

Processing object type SCHEMA_EXPORT/ROLE_GRANT
ORA-39083: Object type ROLE_GRANT failed to create with error:
ORA-01932: ADMIN option not granted for role 'EXP_FULL_DATABASE'
Failing sql is:
GRANT "EXP_FULL_DATABASE" TO "SCOTT1"

The user has EXP_FULL_DATABASE with ADMIN Option and IMP_FULL_DATABASE with ADMIN option direct privileges.which privileges the user needs to deploy the schema successfully?

View 1 Replies


ADVERTISEMENT

Server Utilities :: Datapump API?

Jun 15, 2011

I am trying to use the export the database using datapump API dbms_datapump.I want to export the entire database excluding some schemas.

My question is how to exclude some schemas using datapump API?

View 1 Replies View Related

Server Utilities :: Data Migration Using Datapump?

May 11, 2011

I got an assignment to create Oracle 11g db. I will be provided the full datapump export dump of an Oracle 10g db in linux. I need to import it to 11g Database in Windows. I have no information about the tablespaces, users etc I have created db with system,sysaux,undotbs temp and users tablespaces.

View 28 Replies View Related

Server Utilities :: Export XML Object Using Datapump

Jan 2, 2012

As a part of our back up we used to export the production data every day using Original export Utility but from 11g original export Utility is de supported and also datapump doesn't support XML Objects so is there any other way to export the full database else any option to export xml Object using datapump.

View 2 Replies View Related

Server Utilities :: Oracle Directory And Datapump

Mar 31, 2010

We have three instances on the RAC, When I create a directory is there a default instance that it gets created on? When I execute an datapump export on instance 1, it works but on 2 and 3 it fails with error relating to directory.

Error -
ORA-39002: invalid operation
ORA-39070: Unable to open the log file.
ORA-29283: invalid file operation
ORA-06512: at "SYS.UTL_FILE", line 475
ORA-29283: invalid file operation

Is my only option is to use shared storage space?

View 6 Replies View Related

Server Utilities :: Datapump And Index Building

Jan 13, 2012

Currently we are using "exp and imp" utilities to unload from production and load into Dev server. While importing, we are following below steps

(1) Load only data [by specifying INDEXES=N in the par file]
(2) Unlock statistics
(3) Load indexes, other objects [by specifying ROWS=N]

After doing these steps, both data, indexes and others objects are loaded. To verify indexes, we are checking DBA_INDEXES.

DBA_INDEXES :
-------------
OWNER INDEX_NAME TABLE_NAME STATUS LAST_ANALYZED
----- ---------- ---------- ------ -------------
MYSCH CP_INDEX_1 CP_TABLE_1 VALID 14/JAN/12

Question :-

(1) Does imp utility rebuild the indexes while loading data ? or it simply takes the rows from dump and load into test system without building from scratch ?

(2) I am trying to replace 'exp' and 'imp' with datapump utilities ? But, I am confused about the parameters to be used ?

(a) Can I load both data and meta data at the same time (Using CONTENT=ALL option) ?
(b) I am planning to implement this in two steps :

first load only metadata using - CONTENT=METADATA_ONLY TABLE_EXISTS_ACTION=REPLACE

then, load data - CONTENT=DATA_ONLY.

View 1 Replies View Related

Server Utilities :: Using The Datapump Tool To Migrate 10g DB To 11g?

Jan 3, 2012

I am trying to use the datapump tool to migrate a 10g db to 11g. Everything works fine except for the "nameless" check constraints.

View 7 Replies View Related

Server Utilities :: DBMS-DATAPUMP Import Error?

Oct 25, 2010

have a requirement to load .dmp files into existing staging tables and there is package to load the ODS tables from staging.So,I thought of using DBMS_Datapump utility to import the data from .DMP files to the Tables and this need be automated.

--Create Directory
CREATE
OR REPLACE DIRECTORY test_dir AS 'C:Test'

--grant Access to the User
GRANT READ, WRITE ON DIRECTORY test_dir TO scott;

--Script to import
DECLARE
l_dp_handle1 NUMBER;
BEGIN
l_dp_handle1 := dbms_datapump.OPEN(operation => 'IMPORT',

[code]...

Errors

ERROR at line 1:
ORA-31634: job already exists
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79
ORA-06512: at "SYS.DBMS_DATAPUMP", line 938
ORA-06512: at "SYS.DBMS_DATAPUMP", line 4590
ORA-06512: at line 4

View 6 Replies View Related

Server Utilities :: Rename Table While Using Datapump Import?

Mar 18, 2013

I have a Datapump Export File which was created in Schema mode.

I have to import the tabelles in a new database where a have to use the REMAP SCHEMA Parameter.

Additionally I would like to add a prefix to tablenames.

For example:

original tablename: THE_TABLE
Name after import: IMP_THE_TABLE

Is there a way to add a prefix while using Datapump Import?

View 5 Replies View Related

Server Utilities :: Import A Schema From One Database Schema To Another Schema B?

Aug 10, 2010

I want to import a schema from one database schema to another schema b from db STBTST to STATST and from schema CMSSTAGINGB to CMSSTAGINGA

I first want to test this to my own schema (mvanmannekes) CMSSTAGINGA is filled at the moment.

So i've created a dump from STBTST-CMSTAGINGB For importing im using this statement:

impdp mvanmannekes/password schemas=cmsstagingb remap_tablespace=cmsliveb_data:cmslivea_data
remap_tablespace=cmsliveb_index:cmslivea_index
remap_schema=cmsstagingb:mvanmannekes directory=expdp_dir dumpfile=cmstagingb.dmp

I'm getting this:

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "MVANMANNEKES"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded
Starting "MVANMANNEKES"."SYS_IMPORT_SCHEMA_01": mvanmannekes/********
schemas=cmsstagingb remap_tablespace=cmsliveb_data:cmslivea_data

[code]....

View 4 Replies View Related

Server Utilities :: Datapump Import And Referential Integrity Constraints

May 23, 2012

I am using Datapump import using database link to import an entire schema from another Server but it gives issues with constraints.I tried to first only import the metadata and then disable the constraints and import data and enable constraint but in this case the temp tablespace keeps filling up and i am out of space. Is there any method to do a full import including constraints and indexes.

View 7 Replies View Related

Server Utilities :: Total Estimation Using BLOCKS Method - Datapump

Apr 5, 2013

I have analyzed that, datapump estimation is 9.902GB. When i check size of .dmp file, it's shows 1.44Gb.

Export: Release 11.2.0.1.0 - Production on Fri Apr 5 02:00:05 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
;;;
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_FULL_01": system/******** dumpfile=expdp_LVGITRN_30_24_050413.dmp directory=DP_DIR logfile=expdp_LVGITRN_30_24_050413.log full=y exclude=statistics
Estimate in progress using BLOCKS method...
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
Total estimation using BLOCKS method: [bold]9.902 GB [/bold]

Why Datapump estimate so much than actual size?

View 8 Replies View Related

Server Utilities :: Datapump Export Taking Long Time (HUNG)?

Aug 23, 2012

Expdp directory=xxx.dmp dumpfile=aaa.dmp logfile=xxx.log FULL=Y
: :: : : :: : : : ;
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 24.87 MB
Processing object type SCHEMA_EXPORT/USER

[code]...

then my export hangs..... checked in alert log nothing found.and then killed the job and reran again but same....checked the status and it's saying EXECUTING.

View 15 Replies View Related

Server Utilities :: DBMS-DATAPUMP.metadata-filter And Long Table List?

Jun 9, 2010

I have a problem with DBMS_DATAPUMP.metadata_filter.Let's suppose that I need to export a huge list of tables (a,b,c,d,e,f,g,h,i...). Let's suppose that the list is dynamic do NOT want to use

DBMS_DATAPUMP.metadata_filter (handle => h1,
NAME => 'NAME_EXPR',
VALUE => 'IN (''a'', ''b'', ...)',
object_type => NULL);

In my_export_table there is the list:

CREATE TABLE my_export_table
(
EXPORT_OBJECT_NAME VARCHAR2(50 BYTE)
)

Now I'm trying to use this form:

DBMS_DATAPUMP.metadata_filter (handle => h1,
NAME => 'NAME_EXPR',
VALUE => 'IN (SELECT a.export_object_name FROM my_export_table a, user_objects b WHERE a.export_object_name = b.object_name AND b.object_type = ''TABLE'')',
object_type => NULL
);

but it results in error.

Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
ORA-39125: Worker unexpected fatal error in KUPW$WORKER.GET_TABLE_DATA_OBJECTS while calling DBMS_METADATA.FETCH_XML_CLOB []
ORA-00942: table or view does not exist

[code]...

View 1 Replies View Related

Server Utilities :: How To Take All Schema Metadata Export Except One Schema (scott)

Jul 5, 2012

how to take all schema metadata export except one schema (scott)

can i use like EXCLUDE=schema:"IN('SCOTT')

View 4 Replies View Related

Server Utilities :: How To Move Schema To Another Schema In Same Database

Jan 5, 2009

move the tables with data present in the user scott(full) to another schema named test. In my case scott is in user tablespace and for test schema i have created different tablespace named test_tbs.

View 14 Replies View Related

Server Utilities :: Exported Data Of One User Importing Into Another Schema At Another Server

Sep 15, 2010

I have exported data of one user an importing into another schema at another server. when i am trying to imoport it is working fine for quite no of imports into tables, but after some time it starts giving me below mention error...

IMP-00008: unrecognized statement in the export file:
<
IMP-00008: unrecognized statement in the export file:
<
IMP-00008: unrecognized statement in the export file:
<‏ے
IMP-00008: unrecognized statement in the export file:
+A
IMP-00008: unrecognized statement in the export file:
[code]...

View 6 Replies View Related

Server Utilities :: XDB Schema?

Nov 23, 2005

I have a problem that goes like this. My senior sent me the recent dumps of our database from production. He told me that dont drop the tablespaces and users in order to save time of re-creation. He told me told me to drop the objects of individual users. Now, i have dropped the objects of XDB oracle's default schema which is required for XML support functions.

How to export the XDB schema from another database with the same version and re-store in the present database.Whenever I try to take the export of XDB user from other database I get error user does not exist. I have checked in the console it is locked.

Now, how do i restore the XDB schema.

View 12 Replies View Related

Server Utilities :: Compare Column Name Of Schema With Other Schema's Column Name

Dec 15, 2012

I have a standard schema named ABC and 600 more schema's over there in my database.They all has same table name and column name as on standard schema. But in some tables number of columns varying. So I need to compare all schemas with my standard schemas column name. I create below script but it is generating output in infinite loop.

SET SERVEROUTPUT ON
DECLARE
V_COLS VARCHAR2(20);

BEGIN
FOR CUR_CCD IN(SELECT DISTINCT TABLE_NAME,OWNER FROM ALL_TABLES
WHERE OWNER LIKE 'CCD_MAIN'
[code]....

View 16 Replies View Related

Server Utilities :: Schema Not Found?

Apr 16, 2012

I'm new to oracle and solaris.got a dmp file that I need to restore from. I've managed to successfully restore it in windows but not in solaris. the following command:

$impdp SCHEMAS=<schema> DUMPFILE=<dumpfile> LOGFILE=<logfile>

I got the following error:

SCHEMAS=<schema>: not found

Basically anything after $impdp is always not found. Is there any problem during setup?

View 8 Replies View Related

Server Utilities :: Merge Two Distinct Schema

Apr 29, 2010

I have a requirement to add all the objects of schema A of database X to schema B of database Y. Schema A and B does not have any common objects.

Apart from tables,indexes schema A also have BLob object, materialize views, type. The size of database X is 50 GB. Through initial research I found data pump , traspoting tablespaces , and RMAN duplication .

Is the any other method I should be looking at? Is RMAN duplication even a option because I just want to copy a schema not the whole database? The data base has other schemas too.

View 6 Replies View Related

Server Utilities :: Move Data From One Schema To Another

Feb 7, 2013

I am trying to move data from one schema in database A to another schema in database B and only move data not others (procedure, view, function). What is the best way to do it? I am thinking database link then insert into select from database link. Is there a better way?

View 8 Replies View Related

Server Utilities :: Dump Table From One To Other Schema

Mar 4, 2010

i need to dump the table from A schema to B schema with different table name.

Suppose i have TABLE A IN "A" SCHEMA i need to dump the table with DATA+sTRUCTURE in " B"SCHEMA WITH TABLE NAME AS B.

View 3 Replies View Related

Server Utilities :: Import Only Tables Of A Schema

Jul 26, 2013

The way to do IMPORT of a schema's ALL the TABLES only. Through Data Pump. I don't want to import any other objects like 'package',procedure etc...

Is it the only way is specifying them within EXCLUDE parameter?

View 3 Replies View Related

Server Utilities :: How To Backup Tables In A Schema

May 31, 2012

How to backup the tables in a schema? I need som halp on this subjekt. I hav Dev envrnment that I must to backup.

View 9 Replies View Related

Server Utilities :: How To Import Dump In Schema

Feb 7, 2011

I have exported a schema dump with schema name as 'A'.I want to import that dump in to schema 'B'.how ?

View 5 Replies View Related

Server Utilities :: Data Restore From One Schema To Another

Apr 19, 2013

This message for my clarification,we have two schemas MAD_LIVE and MAD_TEST we want to take data from MAD_LIVE to MAD_TEST for particular period of time. way to import data from MAD_LIVE to MAD_TEST for particular period

My SCHEMA MAD_LIVE using from year 2010 till now,we want to take only 2012 data from MAD_LIVE and restore it in MAD_TEST.

View 3 Replies View Related

Server Utilities :: Loading Data To Another Schema

Sep 5, 2010

How to load data to another schema's table through Sql*loader.

I tried the below things in the control file.

Load data
"
Into table scott.emp

View 14 Replies View Related

Server Utilities :: Export Schema Error?

May 31, 2012

when i try to export schema using expdp i got error Like

Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 6.25 MB
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA

[code]...

HOw to solve this issue?

View 3 Replies View Related

Server Utilities :: Importing Other Schema Object

Sep 19, 2010

I have 2 users (scott and Krishnan).

Krishnan wants to import the table, which was exported by scott.

1) but we should not run the script file of CATALOG.SQL.
2) we can't give the rights of imp_full_database from outside.

Also how user Krishnan can import that table to another user's schema?

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved