This question is not answered

TOAD 12.0 hangs on one schema using automation to export DDL

Follow / 2 Aug 2013 at 3:53am

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=

Replies

All Replies
  • Follow / 2 Aug 2013 at 10:20am
    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:
     
     
     
    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
     
    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.


  • Follow / 7 Aug 2013 at 8:52pm

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

    ~bill

  • Follow / 7 Aug 2013 at 9:01pm
    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
     
    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.


  • Follow / 7 Aug 2013 at 9:09pm

    Correct.

    ~bill

  • Follow / 7 Aug 2013 at 9:13pm

    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.