Re: Data import

Guest · October 25, 2010, 2:51am

Hi Norman;

I have already tried the following command :

imp SYSTEM/XXX file=/oradata/ora48001/donnees/archive/hp_ntced_aa.dmp
log=/oradata/ora48001/donnees/archive/hp_ntced_aa.log fromuser=hp_ntced_aa
touser=HP_NTCED_AA rows=y constraints=n compile=n IGNORE=y indexes=n commit=y

But have Always the same error (in french) :

Ligne rejetée à cause de l’erreur ORACLE 1

IMP-00003 : Erreur Oracle 1 rencontrée

ORA-00001: violation de contrainte unique (HP_NTCED_AA.UK_ADMISSION_01)

…

Regards

— En date de : Lun 25.10.10, Norman Dunbar a écrit :

De: Norman Dunbar
Objet: Re: [toad] Data import
À: toad@yahoogroups.com
Date: Lundi 25 octobre 2010, 8h58

Morning djamel,

> *imp SYSTEM/laposte+2
> file=/oradata/ora48001/donnees/archive/hp_ntced_aa.dmp
> log=/oradata/ora48001/donnees/archive/hp_ntced_aa.log
> fromuser=hp_ntced_aa touser=HP_NTCED_AA commit=y ignore=y*
> **
> *But I have of error messages on constraints and indexes !*
> *How realize the import procedure without know the schema of the
> database ? *
> *I would like just to import only the data from a dump file.*

add the following to your imp command:

indexes=no constraints=no

you may also decrease the import time by adding:

buffer=40960000

Adjust the value to suit your system, but the above works for me.

And, one last thing, please don’t hijack an existing thread to post a
question. If you do, people like me with threaded email clients may not
see it as it appears under a thread that we may not be watching. For a
new query, alsways start a new thread please. Thanks.

Cheers,
Norm. [TeamT]

--

Cheers,
Norm. [TeamT]

NormTeamT · October 25, 2010, 3:22am

Morning Djamel,

Ligne rejetée à cause de l'erreur ORACLE 1
IMP-00003 : Erreur Oracle 1 rencontrée
ORA-00001: violation de contrainte unique
(HP_NTCED_AA.UK_ADMISSION_01)
Aha! My French is tes mal, but:

Unique constraint violated on constraint HP_NTCED_AA.UK_ADMISSION_01 seems to be your problem. This implies that the data you are importing already exists in the table that the constraint is protecting.

In order to do a clean import, you must get rid of existing data first.

Have you cleaned out all the tables you wish to import? Is this what you want to do? You need to be sure of what you wish to do before you run the import.

Imp allows you to:

Add data to existing tables;
Import data into empty tables;
Import data into tables, creating them as necessary.

If you need to import into existing tables but leave existing data alone, then you must make sure that the data you are importing will not violate any constraints. This is your current problem.

Cheers,
Norm. [TeamT]

Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else.

We have checked this email and its attachments for viruses. But you should still check any attachment before opening it.
We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes.

If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at www.environment-agency.gov.uk

dboussebha_123807095 · October 25, 2010, 5:05am

Hi;

Have you cleaned out all the tables you wish to import?

No : how I realize this ?

I try try with Toad in specifying the follwing parameters for import file :

Content : All

If table exists : Replace

But I have the following error :

Import: Release 10.2.0.1.0 - Production on Lundi, 25 Octobre, 2010 12:03:35

Connecté à : Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit
Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring
Engine options

ORA-39001: valeur d’argument non valide
ORA-39000: spécification de fichier de vidage erronée
ORA-39143: le fichier de vidage
“/oradata/ora48001/donnees/archive/hp_ntced_aa.dmp” peut être un fichier de
vidage d’export original

— En date de : Lun 25.10.10, Dunbar, Norman (Capgemini)
a écrit :

De: Dunbar, Norman (Capgemini)
Objet: RE: [toad] Data import
À: toad@yahoogroups.com
Date: Lundi 25 octobre 2010, 10h22

Morning Djamel,

>> Ligne rejetée à cause de l'erreur ORACLE 1
>> IMP-00003 : Erreur Oracle 1 rencontrée
>> ORA-00001: violation de contrainte unique
>> (HP_NTCED_AA.UK_ADMISSION_01)
Aha! My French is tes mal, but:

Unique constraint violated on constraint HP_NTCED_AA.UK_ADMISSION_01 seems
to be your problem. This implies that the data you are importing already
exists in the table that the constraint is protecting.

In order to do a clean import, you must get rid of existing data first.

Have you cleaned out all the tables you wish to import? Is this what you
want to do? You need to be sure of what you wish to do before you run the
import.

Imp allows you to:

* Add data to existing tables;
* Import data into empty tables;
* Import data into tables, creating them as necessary.

If you need to import into existing tables but leave existing data alone,
then you must make sure that the data you are importing will not violate any
constraints. This is your current problem.

Cheers,
Norm. [TeamT]

Information in this message may be confidential and may be legally
privileged. If you have received this message by mistake, please notify the
sender immediately, delete it and do not copy it to anyone else.

We have checked this email and its attachments for viruses. But you should
still check any attachment before opening it.
We may have to make this message and any reply to it public if asked to
under the Freedom of Information Act, Data Protection Act or for litigation.
Email messages and attachments sent to or from any Environment Agency
address may also be accessed by someone other than the sender or recipient,
for business purposes.

If we have sent you information and you wish to use it please read our terms
and conditions which you can get by calling us on 08708 506 506. Find out
more about the Environment Agency at www.environment-agency.gov.uk

NormTeamT · October 25, 2010, 9:09am

Hi Djamel,

Have you cleaned out all the tables you wish to import?
No : how I realize this ?
There are a couple of way, the simplest being:

In Toad, open the SCHEMA BROWSER.
Select the correct user in the list of users.
Select TABLES in the list of objects.
When the list of tables is displayed (on the left) select all of them
(CTRL+A).
Right-click and select TRUNCATE TABLE.
In the following dialogue, check "REUSE STORAGE" as you will be
importing the tables' data again.
Click EXECUTE.
Click OK when done.

The (list of) tables is not blank and ready to import the new data from.

You can then either run a command line "imp" session, or, use the Toad
Database->import->Import Utility Wizard to bring the new data into the
(now) empty tables.

Cheers,
Norm. [TeamT]

Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else.

We have checked this email and its attachments for viruses. But you should still check any attachment before opening it.
We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes.

If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at www.environment-agency.gov.uk

NormTeamT · October 25, 2010, 9:24am

Sorry, I forgot a bit:

I try try with Toad in specifying the follwing parameters
for import file :
ORA-39143: le fichier de vidage
"/oradata/ora48001/donnees/archive/hp_ntced_aa.dmp" peut
être un fichier de vidage d'export original
This sounds like you have asked Toad to use IMPDMP rather than IMP. You need to use the Import Utility Wizard.

Cheers,
Norm. [TeamT]

Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else.

We have checked this email and its attachments for viruses. But you should still check any attachment before opening it.
We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes.

If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at www.environment-agency.gov.uk

dboussebha_123807095 · October 27, 2010, 2:14pm

Hi;

How use Toad to identify the SQL requests which cause the CPU/RAM performance
and which execute with long time ?

Regards;

Guest · October 27, 2010, 4:18pm

How use … the profiler in Toad

Note: These instructions are based on Toad 10.6.0.42

On the toolbar – if you haven’t modified it – there is a
little clock image with a tooltip to toggle profiling. Note that your DBA has to
make sure the DBMS_PROFILER package is set up in the database and you have
access to use it.

You can toggle the profiling on and off with that clock.

If you can’t find the button, you can always toggle using the menu item.
Session -> Toggle PL/SQL profiling

The basic use is:

Toggle profiling on

Activate your procedure

Toggle profiling off

The simplest way to run the procedure would be right-click in your browser and
select run. With a package you’ll be given a popup listing the available
procedures (of which you choose the one you want to run) and allowing you to set
parameters and modify the “wrapper” code to your heart’s
content.

After toggling the profiler off, you can view the profiler information through
the menu selection Database -> Optimize -> Profiler Analysis

Suggestion: make sure you run the process through a number of records. The more
records you process, the more clearly the heaviest cost sections of your code
will be presented. I just ran through the process myself with 100 records and
the single SQL statement causing the performance issue, in a rather large
listing of code, was immediately apparent.

From a performance perspective, your issue may not be readily apparent and you
may have to map through some complex data structure to find the real culprit.
For example, I once had a piece of code to examine that was taking a half hour
to run. The dev that wrote the code figured it should run faster but
didn’t understand what was wrong. The profiler identified the loop itself
to be the most time. However, upon examination of the statements inside the loop
I concluded there was a lot more overhead in the loop then necessary.
Specifically, there were some queries which only had a fraction of the records
of the parent loop level. I moved the child statements outside the loop that
could be moved and changed the runtime of the process from 30 minutes to less
than 30 seconds. The key time was originally spent trying to look up children
records which simply did not exist. That’s a lot of overhead.

Roger S.

dboussebha_123807095 · October 28, 2010, 2:51am

Hi Roger;

I have the version 9.7 for Toad.

I can’t find the button for the SQL profiler and the menu item : Session ->
Toggle PL/SQL profiling is disabled ?

How enabled this menu item ?

Regards

--- En date de : Mer 27.10.10, Simoneau, Roger a
écrit :

De: Simoneau, Roger
Objet: RE: [toad] SQL Profiler with Toad
À: "'toad@yahoogroups.com'"
Date: Mercredi 27 octobre 2010, 23h18

How use … the profiler in Toad

Note: These instructions are based on Toad 10.6.0.42

On the toolbar – if you haven’t modified it – there is a little clock
image with a tooltip to toggle profiling. Note that your DBA has to make sure
the DBMS_PROFILER package is set up in the database and you have access to use
it.

You can toggle the profiling on and off with that clock.

If you can’t find the button, you can always toggle using the menu item.
Session -> Toggle PL/SQL profiling

The basic use is:

Toggle profiling on

Activate your procedure

Toggle profiling off

The simplest way to run the procedure would be right-click in your browser and
select run. With a package you’ll be given a popup listing the available
procedures (of which you choose the one you want to run) and allowing you to set
parameters and modify the “wrapper” code to your heart’s content.

After toggling the profiler off, you can view the profiler information through
the menu selection Database -> Optimize -> Profiler Analysis

Suggestion: make sure you run the process through a number of records. The more
records you process, the more clearly the heaviest cost sections of your code
will be presented. I just ran through the process myself with 100 records and
the single SQL statement causing the performance issue, in a rather large
listing of code, was immediately apparent.

From a performance perspective, your issue may not be readily apparent and you
may have to map through some complex data structure to find the real culprit.
For example, I once had a piece of code to examine that was taking a half hour
to run. The dev that wrote the code figured it should run faster but didn’t
understand what was wrong. The profiler identified the loop itself to be the
most time. However, upon examination of the statements inside the loop I
concluded there was a lot more overhead in the loop then necessary.
Specifically, there were some queries which only had a fraction of the records
of the parent loop level. I moved the child statements outside the loop that
could be moved and changed the runtime of the process from 30 minutes to less
than 30 seconds. The key time was originally spent trying to look up children
records which simply did not exist. That’s a lot of overhead.

Roger S.

Guest · October 28, 2010, 11:19am

version 9.7

…

I can’t find the button for the SQL profiler

and the menu item : Session -> Toggle

PL/SQL profiling is disabled ?

Hey Djamel (I’m guessing that’s your first name);

It’s a good idea to keep such follow up questions on the list. Someone else
would then have an opportunity of responding before I even wake up

Additionally, It’s useful for others that might have the same question.

So… on to the answer:

Toad queries the database to see what you have access to and enables/disables
certain features. One of those features is the profiling functionality.

The profiler functionality is Oracle created. Toad is simply a front-end GUI to
access it and parse the information it generates.

This is the part where I mentioned the DBA needs to make sure the proper package
(dbms_profiler) is installed in the database. Additionally, the user you are
logging in as needs to be able to access that package. I’m not sure what other
access you might require. Perhaps Norm can outline the specifics beyond what
I’ve covered.

You can verify the package and access so you know what you need to request from
your DBA with the following as run in SQL Plus. I guide you to SQL Plus for one
reason – you will know for sure that any error message produced is Oracle, not
Toad, so you eliminate any issues with the Tool completely from the picture.

DESC dbms_profiler;

If the package is not installed or you don’t have any access to it at all,
you’ll see an error like:

ERROR:

ORA-04043: object dbms_profiler does not exist

If you see a package described, you can test basic access with:

SELECT dbms_profiler.stop_profiler FROM DUAL;

Note: Don’t be surprised if the DBA refuses to enable the profiler on a
production database. If that is where you are attempting to do your performance
analysis, you will likely need the DBA involved in order to acquire the
information you need. Production is supposed to be a very controlled environment
and allowing a dev such access as to be able to run a 20-hour query in profiling
is a very, very bad idea (and I’m a Developer, not a DBA). However, for a Dev
environment, the DBA shouldn’t have any issues with setting up the profiler so
you can use it.

Roger S.

dboussebha_123807095 · November 3, 2010, 11:28am

Hi;

I have realize a import of a dump file and I have the following errors (Sorry is
in french):

. . import : “HP_NTCED_AA”.“STATUT_TRACE” 5.375 KB 4 lignes

. . import : “HP_NTCED_AA”.“TYPE_INDICATEUR_SUIVI” 5.437 KB 4 lignes
…

Traitement du type d’objet SCHEMA_EXPORT/TABLE/INDEX/INDEX
ORA-39014: Un ou plusieurs processus esclave sont sortis prématurément.
ORA-39029: le processus esclave 1 nommé “DW01” s’est arrêté prématurément
ORA-31672: Le processus esclave DW01 a échoué de manière inattendue.
Tâche “SYS”.“SYS_IMPORT_SCHEMA_06” arrêtée en raison d’une erreur grave

Q: My import is good or not ? How resolve these errors ?

regards;