TOAD 12.0 hangs on one schema using automation to export DDL

Toad for Oracle
1

I know this is a bit of long shot, but I’m really running out of ideas. I have used TOAD for Oracle to export a number of schemas DDL to local folders on a windows machine periodically. Recently one of the schemas on one of the instances I run it against causes TOAD to hang and never finish the export. No files are written, and I end up having to kill it. To be fair, TOAD is working great on a number of other schemas, so it’s not really a bug in TOAD I don’t think. But I’m unsure how to track down the true source of the problem and resolve it. I don’t think it’s a locking or blocking issue, the instance has been bounced and the problem persists, and the DBAs haven’t seen any visible problems when the job is running, although CPU usage is noticeably increased on the Oracle database server when it’s hung.

Below is the command line that launches the automation, and the control file that is fed into it specifying the schema to dump, and the options. I’m hoping someone might have some thoughts on how to troubleshoot further.

Command line:

start “Running TOAD…” /WAIT “c:\program files\quest\toad for oracle 12\toad.exe” -a “Sync->One | c:\sync\source\schema.ctl”

Control file:

[1]
Name=One
Type=Generate Schema Script
ConnectionCount=1
Connection_1=uuuuu@xxxx.yyy.zzz
SourceSchemaList=sss
SourceDefFile=
ObjectNamesFilter=
ObjectNamesNotLike=0
ObjectNamesCaseSensitive=1
OutputSQLFile=c:\sync\source\expdir\all.ddl
OutputTopLevelDirectory=c:\sync\source\expdir
OutputDefFile=

2

One thought – and it’s a long short – Toad (especially export DDL) hammers the Oracle data dictionary. If you database has old or no stats then Toad can go
very slow on some things. For example a Toad Database Probe screen refresh can take 45 minutes vs. 2-3 seconds. So maybe read this blog and see if this helps:

http://www.toadworld.com/products/toad-for-oracle/b/weblog/archive/2007/10/02/toad-sometimes-slow-on-oracle-10g.aspx

From: billprew [mailto:bounce-billprew_484@toadworld.com]

Sent: Thursday, August 01, 2013 9:53 PM

To: toadoracle@toadworld.com

Subject: [Toad for Oracle - Discussion Forum] TOAD 12.0 hangs on one schema using automation to export DDL

TOAD 12.0 hangs on one schema using automation to export DDL

Thread created by billprew

I know this is a bit of long shot, but I’m really running out of ideas. I have used TOAD for Oracle to export a number of schemas DDL to local folders on a windows machine periodically. Recently
one of the schemas on one of the instances I run it against causes TOAD to hang and never finish the export. No files are written, and I end up having to kill it. To be fair, TOAD is working great on a number of other schemas, so it’s not really a bug in
TOAD I don’t think. But I’m unsure how to track down the true source of the problem and resolve it. I don’t think it’s a locking or blocking issue, the instance has been bounced and the problem persists, and the DBAs haven’t seen any visible problems when
the job is running, although CPU usage is noticeably increased on the Oracle database server when it’s hung.

Below is the command line that launches the automation, and the control file that is fed into it specifying the schema to dump, and the options. I’m hoping someone might have some thoughts
on how to troubleshoot further.

Command line:

start “Running TOAD…” /WAIT “c:\program files\quest\toad for oracle 12\toad.exe” -a “Sync->One | c:\sync\source\schema.ctl”

Control file:

[1]

Name=One

Type=Generate Schema Script

ConnectionCount=1

Connection_1=uuuuu@xxxx.yyy.zzz

SourceSchemaList=sss

SourceDefFile=

ObjectNamesFilter=

ObjectNamesNotLike=0

ObjectNamesCaseSensitive=1

OutputSQLFile=c:\sync\source\expdir\all.ddl

OutputTopLevelDirectory=c:\sync\source\expdir

OutputDefFile=

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or
Unsubscribe from Toad for Oracle - General notifications altogether.

Toad for Oracle - Discussion Forum

Flag
this post as spam/abuse.

3

Looked into that, stats are up to date, and the problem isn’t just a slow execution, it actually never finishes…

~bill

4

Thanks for checking – and just to be sure we’re talking the same thing – not stats on the schema being exported – but stats on the SYS and SYSTEM objects in
the data dictionary gathered buy those special PL/SQL calls I wrote about. You are saying your DBA says the stats on those are current ???

From: billprew [mailto:bounce-billprew_484@toadworld.com]

Sent: Wednesday, August 07, 2013 2:53 PM

To: toadoracle@toadworld.com

Subject: RE: [Toad for Oracle - Discussion Forum] TOAD 12.0 hangs on one schema using automation to export DDL

RE: TOAD 12.0 hangs on one schema using automation
to export DDL

Reply by billprew

Looked into that, stats are up to date, and the problem isn’t just a slow execution, it actually never finishes…

~bill

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or
Unsubscribe from Toad for Oracle - General notifications altogether.

Toad for Oracle - Discussion Forum

Flag
this post as spam/abuse.

5

Correct.

~bill

6

So this schema used to export fine, but it just stopped working? Do you have any ideas what kind of changes could have taken place? I’ve seen Toad get hung up in certain areas of objects contain special characters, or if there are special characters within pl/sql code. Or maybe something happened in your parameter file? Maybe try to re-create the parameter file from scratch and try again.