Server Utilities :: IMPDP Hangs On Table Data?

May 26, 2010

I'm trying to do a network datapump between oracle databases, and it seems to continually hang when it gets to the point where it should be processing table data.

C:>impdp DP_USER/DP_USER parfile=sde_webmap_2.par

Import: Release 11.1.0.7.0 - 64bit Production on Wednesday, 26 May, 2010 17:42:03

Copyright (c) 2003, 2007, Oracle. All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "DP_USER"."SYS_IMPORT_FULL_01": DP_USER/******** parfile=sde_webmap_2.par
Estimate in progress using BLOCKS method...

[code]...

It just sits at this point indefinitely.The parfile for those interested:

directory=datapumps
logfile=sde_webmap_2.log
network_link=backup
full=y
INCLUDE=SCHEMA:"IN ('SDE_WEBMAP_BUSINESS','SDE_WEBMAP_BUSINESS_A','SDE_WEBMAP_BUSINESS_B')"

And the results from V$SESSION_LONGOPS

69 SYS_IMPORT_FULL_01 IMPORT 0 1031 MB 5/26/2010 5:50:37 PM 5/26/2010 6:03:29 PM

View 3 Replies


ADVERTISEMENT

Server Utilities :: IMPDP - Big Table's Data Can't Be Imported

Oct 23, 2013

I was running IMPDP for my company's database.Everything is just fine except one problem.A big table(61.1G) could't be imported.The warning message shows "imported TBL_XXXX 61.1G 0 out of 147653981rows".The alert log had no warning message.I didn't know

View 12 Replies View Related

Server Utilities :: Oracle Table Export Hangs

Nov 29, 2010

I got a problem while exporting schema. Export hangs on a particular table table. i checked the table structure and integrity

(ANALYZE TABLE user.table_name VALIDATE STRUCTURE CASCADE; )

and it seems all okay.

View 5 Replies View Related

Server Utilities :: Import A Dump File Using Impdp Data Pump Utility On Oracle 10g

Feb 19, 2012

Is it possible to import a dump file using impdp data pump utility on oracle 10g where the export dump was taken using traditional exp utility and vice versa.

View 1 Replies View Related

Server Utilities :: Expdp Hangs In Oracle 11g?

May 27, 2011

I am trying to export Schema using expdp command. but its going hang after few minutes. it seems that it stucks any where. Even I am trying with normal scott schema it is also hanging.

View 16 Replies View Related

Server Utilities :: Export Hangs At Exporting Cluster Definitions?

Dec 22, 2010

Sunddenly my exports hangs at 'exporting cluster definitions'. I had been using this database since last 4 years and it never cause a problem or hangs at this level. here i'm pasting my screen details. it is my production db.

[oracle1@wbh_as1 smbshare]$ exp wb/wb

Export: Release 9.2.0.1.0 - Production on Thu Dec 23 00:02:44 2010

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected to: Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
Enter array fetch buffer size: 4096 >

Export file: expdat.dmp > wb

(2)U(sers), or (3)T(ables): (2)U >

Export grants (yes/no): yes >

Export table data (yes/no): yes >

Compress extents (yes/no): yes >

Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses WE8ISO8859P1 character set (possible charset conversion)
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user WB
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user WB
About to export WB's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions

View 11 Replies View Related

Server Utilities :: Error While Impdp?

Oct 8, 2010

Getting below error While Impdp..

Processing object type DATABASE_EXPORT/SCHEMA/PROCACT_SCHEMA
ORA-39083: Object type PROCACT_SCHEMA failed to create with error:
ORA-31625: Schema ADAS is needed to import this object, but is unaccessible
ORA-28031: maximum of 148 enabled roles exceeded

[code]...

ORA-06512: at "SYS.KUPW$WORKER", line 1342
ORA-06512: at line 2

Job "SYS"."SYS_IMPORT_FULL_01" stopped due to fatal error at 17:13:38

View 2 Replies View Related

Server Utilities :: Impdp Changes The Ownership Of A DBA_JOB

Apr 30, 2009

When I am exporting a schema that own several DBA_JOBS using expdp, and then importing it into another database - the DBA_JOB change ownership to the schema I used for the import.

For example :
the log_user,priv_user and schema_user used to be schema 'A'
but after the impdp, assuming I imported as SYSTEM, the log_user,priv_user and schema_user are now SYSTEM !.

Is this an expected behavior ? How can I avoid it ? the job will fail executing as SYSTEM , and I need to manually create it as user A again.

View 5 Replies View Related

Server Utilities :: User Creation While Impdp

Jul 9, 2013

I have a question to clarify regarding user creation during export and import.

Will user get created along with roles,privileges by default when using impdp command ?

View 6 Replies View Related

Server Utilities :: Impdp Getting Grants And Privs

May 8, 2012

I did an export using the following parfile (see below) I want to import all the objects associated with this schema into another DB but I want don't want to over-write any of the permissions such as grants.

Is there a way I can get the grants into a sql file before I do the import. If so, provide an example.

cat exp_par

DUMPFILE=exp.dmp
LOGFILE=exp.log
DIRECTORY=DBBACKUP
schemas=t1

View 1 Replies View Related

Server Utilities :: Indexes Parameters Original Impdp

Sep 25, 2011

As we know,there is a parameters named indexes of orignal imp,it use to generate create index ddl,Is there a parameter in impdp compare to it?

View 6 Replies View Related

Server Utilities :: Error Executing Impdp Utility

Nov 9, 2012

I exported three databases : servicedesk, report and mostcmdb on server A to a dumpfile. Moved them to similar dump location on server B. Now, I want to import them into B by using:

[SQL> impdp system/OSS_MOS7100 dumpfile=MOSTCMDB_0211.DMP schemas=mostcmdb

The error was:

SP2-0734: unknown command beginning "impdp syst..." - rest of line ignored.

View 7 Replies View Related

Server Utilities :: IMPDP (ORA-31619 Invalid Dump File)

May 29, 2012

I need to recreate/ clone my database to a new machine. The two machine are not connected in the network.

Step 1. (Oracle 10.2.0.5 AIX 64-bit)
expdp username/password@db1 full=y dumpfile=dp:fpac052912_dp%U.dmp logfile=dp:fpac052912_expdp.log job_name=full_exp

Step 2.
FTP dump files to Windows

Step 3. (Oracle 10.1.0.2 Windows 32-bit)
impdp username/password@db1 dumpfile=dp:fpac052912_dp%U.dmp logfile=dp:fpac052912_impdp.log full=y

I got:
ORA-39001: invalid argument value
ORA-39000: bad dump file specification
ORA-31619: invalid dump file "C:P7DBfpac052912_dp01.dmp"

Done in AIX:
create directory dp as '/bak'
grant read, write on directory dp to public;
grant exp_full_database to username;

Done in Windows:
create directory dp as 'C:P7DB';
grant read, write on directory dp to public;
grant exp_full_database to username;
grant imp_full_database to username;

View 8 Replies View Related

Server Utilities :: Impdp Takes Huge Time In Oracle11gR2

Feb 13, 2012

1) My database dump size near about 4GB , which is provided by the vendor .
2) In the dump , total objects are 364949 , where

Table : 121316
LOB object : 121315
(Normal+LOB) indexes : 122317

3) Now when I run the import using system or another user , it hangs on the below stage for 70+ hours ..

impdp ntest/ntest directory=test_dir dumpfile=JBLLIVE.31Jan2012.11.50AM.dmp remap_schema=JBLLIVE:NTEST logfile=ntest_10feb.log

Import: Release 11.2.0.1.0 - Production on Fri Feb 10 09:49:50 2012

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
Master table "NTEST"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "NTEST"."SYS_IMPORT_FULL_01": ntest/******** directory=test_dir dumpfile=JBLLIVE.31Jan2012.11.50AM.dmp remap_schema=JBLLIVE:NTEST logfile=ntest_10feb.log
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"NTEST" 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/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
----
In this situation I observed the worker status and see that some table and some LOB objects including LOB indexes are imported . Worker process do it in background but it does not show in the front import log file (I dont understand why it not shows in the import logfile). it imports one table,one LOB , one LOB index ..then again one table,one LOB , one LOB index ... in this way .

And my observation first it inserts data into the LOB tables and then it inserts into normal table . And when it is starting to insert data to the normal table then this table's log are shown in the import logfile.

an example of our data type :

Objects :
===================================================
LOB_FD17_RGS_TSTCD2 LOB
FD17_RGS_VERSION TABLE

(here i see one table has one LOB segment, in this way 121316 table has 121316 LOB)

SQL> desc FD17_RGS_VERSION
Name Null? Type
----------------------------------------- -------- ----------------------------
RECID VARCHAR2(255)
XMLRECORD BLOB

Our observation perhaps inserting blob mainly occurs the slowness . Is there any patch or is there any bug regarding BLOB/LOB objects in oracle-11gR2

View 6 Replies View Related

Server Utilities :: Increasing Parallel Process In Impdp In Runtime?

Apr 15, 2012

I started datapump not using parallel option

I issued the following command

impdp t24/t24 directory=dp_dump dumpfile=bef_cob_%U.dmp schemas=t24

The total dumpfile size is 200 GB..

Now I want to add parallel process to the job..

View 9 Replies View Related

Server Utilities :: Faster Impdp Excluding Indexes And Constraints

May 24, 2012

We need to setup a test system using data from one of the offsite customer location.The option we would like to use is impdp with network link. Below given step will make import faster if we exclude indexes and constraints

Steps
Import schema excluding constraints,rf_constraints,indexes with metadata only
Import schema with data only
spool ddl using dbms_metadata.get_ddl from Source for constraints,rf_constraints and indexes
execute on Destination the Index creation ddl
execute on Destination constraints and rf_constraints ddl

View 1 Replies View Related

Server Utilities :: Perform Impdp Command For Specific Function?

Jun 26, 2013

Below is my import command for importing specific function from export file but iam getting below errors

impdp system/PASSWORD schemas=TNC6 directory=dumpdir dumpfile=FULL01-02-2011.dmp logfile=IMP.log include=FUNCTION:"IN ('TNC_IS_NUMBER')"

ORA-39001: invalid argument value
ORA-39071: Value for INCLUDE is badly formed.
ORA-00936: missing expression

View 4 Replies View Related

Server Utilities :: Impdp Procedure Or Function 10.2.0.4 Error ORA-00904

Sep 16, 2011

I have error ORA-00904: "USERNAME" : identificateur non valide. What is the problem ?

My command impdp :
impdp datapump/password@%ORACLE_SID% DIRECTORY=datapump schemas=gcom INCLUDE=PROCEDURE remap_tablespace=gpao_indx:indx remap_tablespace=gpao_data:data DUMPFILE=%ORA_DUMPFILE% LOGFILE=Imp_%annee%%mois%%jour%%hh%%min%%sec%_%ORACLE_SID%.log

The result :
;;;
Import: Release 10.2.0.4.0 - Production on Jeudi, 15 Septembre, 2011 17:47:16

Copyright (c) 2003, 2007, Oracle. All rights reserved.
;;;
Connecté à : Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Table maître "DATAPUMP"."SYS_IMPORT_SCHEMA_29" chargée/déchargée avec succès

[Code]....

View 2 Replies View Related

Server Utilities :: EXP Versus IMPDP Interrogating Dump File

Jul 16, 2010

I have a bit of an issue with Oracle datapump dump files.

Today, I manage the export and import of oracle dump files. As part of the batch export process I have a script which essentially says:

For each schema realated to my application in THIS instance, export schema via the system user (system user allows me privs to all schemas).

On the import UI side of things I am able to run a "head -20" command on the dmp file and determine the "export client version", "date the schema was dumped", and "what schema it was dumped from". All useful info presented in my UI.

Sample output: Begin

EXPORT:V09.02.00
DSYSTEM
RUSERS
8192
Wed Jun 30 11:51:21 UserXXX.dmp
#C##
#C##

[code]....

Sample output: End

in that I allow the importation of production schemas into test schemas, (contained in a different tablespace). Based on naming convention I can determine the schema type (production or test). Additionally and probably most importantly, I am assured where the data has come from.

In looking at "expdp" and the dump file. Using the same method as above, it appears the data pump dump DOES NOT carry similar headers. Because of this, I am unable to return very little useful info from the dump file.

I realize I could run the impdp with the "sqlfile=myfile.sql" and then interrogate the sql file for the info. But on large dump files this would be fairly time consuming compared to a "head -20" on a dump file.

View 4 Replies View Related

Server Utilities :: IMPORT (Impdp) Multiple Dump Files

Aug 2, 2012

i have more than 100 dumpfiles to import into my oracle 11g database. i know how to import(impdp) for same named dumps but here all the dumpfile names are totally different(ex: aa.dmp,bb.dmp,).

View 3 Replies View Related

Server Utilities :: ORA-00001 - Unique Constraint Violated Error During Impdp?

Oct 17, 2011

I am trying to import the dump using the impdp utility and followed the below steps I have disabled all the constraints and executed the below command

impdp DIRECTORY=EXPDP_DAILY_BACKUP_DIR DUMPFILE=expdp_QA_full_11102011_214501.dmp
logfile=imp_EXPDP_QA_full_12102011_203254.log remap_schema=prod:prod remap_tablespace=prod:prod
schemas=prod TABLE_EXISTS_ACTION=truncate content=data_only.

but I am getting the below error like this for 1 or 2 tables . and If I import those tables seperately its getting imported successfully. i am not getting the below error always .

ORA-31693: Table data object "PROD"."DAS_ID_GENERATOR" failed to load/unload and is being skipped due to error:
ORA-00001: unique constraint (PROD.DAS_ID_GENERATOR_P) violated
ORA-31693: Table data object "PROD"."TKT_DIST_SRV_STAT" failed to load/unload and is being skipped due to error:
ORA-00001: unique constraint (PROD.SERVER_STATS_P) violated

View 8 Replies View Related

Server Utilities :: Using Expdp / Impdp To Migrate 4 TB Database From Solaris To Linux

Aug 4, 2011

I am using expdp/impdp to migrate 4 TB database from solaris to Linux.But the import process is taking forever.

View 13 Replies View Related

Server Utilities :: Impdp - Package Body Import Taking Huge Time?

Sep 13, 2012

I am try to import 4G dump in Oracle 11R2 version, in that we have around 9000+ Package Body which is taking huge time than other objects (about 8 to 12 hrs) and also it is expecting lots of system space (roughly about 10GB).

I have tried both parallel and non-parallel.how to improve speed of the package body import.

Details about the Schema & Import No. of objects in Schema

SQL> select object_type,count(1) from user_objects GROUP BY ROLLUP( object_type);

OBJECT_TYPE COUNT(1)
------------------- ----------
FUNCTION 248
INDEX 5161
JAVA CLASS 471
JAVA RESOURCE 1
JAVA SOURCE 16
LIBRARY 1

ORA-00933: SQL command not properly ended

View 3 Replies View Related

Server Utilities :: Exclude View In IMPDP Using Network Link Giving Error

Jan 14, 2011

I am using below command to import a schema using network link. Command is :

impdp system directory = IMP_DIR schemas = XYZ network_link = PQR remap_schema = XYZ:XYZ exclude=view: "= 'XYZ.VW_ACCEPTDETAILS'"

This command is giving below error

LRM-00116: syntax error at 'view:' following '='

When I have tried Like in place of '=' sign i.e. EXCLUDE = VIEW:"LIKE '%VW_ACCEPTDETAILS%'" , it gives me below error:

UDI-00014: invalid value for parameter, 'exclude'

View 4 Replies View Related

Server Utilities :: IMPDP Error ORA-31693 On Oracle Virtual Column With Not Null?

Jun 21, 2013

I am having issue with IMPDP on ORACLE VIRTUAL COLUMNS.I am having following table with Virtual column defined with Not null. Expdp is fine without any issue.

DDL :
------
CREATE TABLE alert_hist
(
alertky INTEGER NOT NULL,
alertcreatedttm TIMESTAMP(6) DEFAULT systimestamp NOT NULL,
alertcreatedt DATE GENERATED ALWAYS AS (To_date(Trunc("alertcreatedttm"))) VIRTUAL NOT NULL

When I do the import (IMPDP) it got failed with the following error.

. . imported "TESTSCHEMA"."VALART" 359.1 KB 4536 rows
ORA-31693: Table data object "TESTSCHEMA"."ALERT_HIST" failed to load/unload and is being skipped due to error:
ORA-39097: Data Pump job encountered unexpected error -1

After that I dropped the Virtual Not null column and recreated that column with Nullable.

DDL :
-----
alter table alert_hist drop column alertcreatedt;
alter table alert_hist add alertcreatedt DATE GENERATED ALWAYS AS (To_date(Trunc("alertcreatedttm"))) VIRTUAL;

After that I took the expdp and impdp , it went fine with out any issue.

View 7 Replies View Related

Server Utilities :: Geometric Data From Text To Table And Wrong CTL Upload Into Table

Jul 11, 2013

I have a requirement to import text files which are generated from 3d modelling software xsteel where it records all geometric information and i want to import this information into oracle table.

CREATE TABLE dstv_head ( wo_no VARCHAR2(12),struct VARCHAR2(12),rev_no NUMBER,
mark VARCHAR2(12),pos VARCHAR2(12),grade VARCHAR2(12),qty NUMBER,PROFILE VARCHAR2(24),TYPE VARCHAR2(12),
len NUMBER,width_web NUMBER,width_bottom NUMBER,flange_thk NUMBER,web_thk NUMBER,radius NUMBER,kgm NUMBER,
kgm1 NUMBER,kgm2 NUMBER,bevel_plus NUMBER,bevel_minus NUMBER,holes_yn VARCHAR2(1),holes_v_yn VARCHAR2(1),
hole_x_dim NUMBER,hole_y_dim NUMBER,hole_dia NUMBER,no_of_holes NUMBER)

-- All the data which has to go under specific field for example **9005.nc1 will go into wo_no field, 1239401A will go under struct.

ST
** 9005.nc1 --WO_NO
1239401A - STRUCT
1 -REV_NO
9005 -MARK
9005 --POS
S275JR --GRADE
2 --QTY
[code]....

View 24 Replies View Related

Server Utilities :: Import Table Data

Jul 12, 2012

i am trying to use exp/imp utility through cmd and exp/imp is done successfully as per message given at last. but data is not import in targeted user.

Microsoft Windows [Version 6.1.7600]
Copyright (c) 2009 Microsoft Corporation. All rights reserved.

C:UsersNeetesh>exp

Export: Release 10.2.0.1.0 - Production on Thu Jul 12 14:18:04 2012

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Username: scott/tiger@localdb

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 Enter array fetch buffer size: 4096 >

Export file: EXPDAT.DMP > d:/scott_data

(2)U(sers), or (3)T(ables): (2)U > t

Export table data (yes/no): yes > y

Compress extents (yes/no): yes > n

Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set

About to export specified tables via Conventional Path ...

Table(T) or Partition(T:P) to be exported: (RETURN to quit) >

Export terminated successfully without warnings.

C:UsersNeetesh>imp

Import: Release 10.2.0.1.0 - Production on Thu Jul 12 14:20:09 2012

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Username: localaepuser/flair22@localdb

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

Import file: EXPDAT.DMP > d:/scott_data

Enter insert buffer size (minimum is 8192) 30720>

Export file created by EXPORT:V10.02.01 via conventional path

Warning: the objects were exported by SCOTT, not by you

import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set List contents of import file only (yes/no): no > y

Import entire export file (yes/no): no > y

importing SCOTT's objects into LOCALAEPUSER Import terminated successfully without warnings.

C:UsersNeetesh>

what is the problem here

View 6 Replies View Related

Server Utilities :: Loading Data Into Table

Aug 31, 2011

I'm not sure if this is so much a SQL Loader problem as it is a database understanding problem, but here it is. I am having trouble loading data into a table (using SQL Loader) due to the fact that I am trying to load data row by row, into corresponding columns.

TestFile.csv

testvalue1, 123445
testvalue2, test
testvalue3, 455321
testvalue4, 65742
testvalue5, 5719

So, using the above data, I am trying to load the value for 'testvalue1' into a column defined as 'testvalue1'; the value for 'testvalue2' into a column defined as 'testvalue2' and so on. From my understanding, SQL loader loads by column not by row, so I am not even sure if this is possible.

View 2 Replies View Related

Server Utilities :: Loading CLOB Data Into Table

Aug 21, 2012

How to load the CLOB data into table..in the attached file 18 column has clob data it's appear like new line..Using external table how to load. i tried it's not working..

View 12 Replies View Related

Server Utilities :: Load Data Into A Table Using SQL Loader

Feb 7, 2013

I'm trying to load data into a table using SQL Loader but getting a failure error below.

Log File
========

SQL*Loader: Release 11.2.0.2.0 - Production on Wed Feb 6 23:54:25 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Control File: /opt/Infor/Outbound_Marketing/7.2.2/EM/metadata/trans.ldr
Data File: /opt/Infor/Outbound_Marketing/7.2.2/EM/logs/trans.log
Bad File: trans.bad
Discard File: none specified
[code]....

View 13 Replies View Related







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