Toad World® Forums

Toad Schema Compare

I have just downloaded and installed the latest version of Toad. I am trying to do a schema compare. I believe i have set up the options and such correctly. When I actually run the compare I get a message at the bottom of the screen that says “Extracting check constraints” and then Toad goes unresponsive. I need the check constraints because they are required for the “NOT NULL” constraint on table creates. I sure would be glad to get some idea of why I am having this problem.

I guess I should add that the schema I am comparing is in Oracle 9.2.0.4. Yes I know it is out of support but we are not allowed to upgrade as the application is soon (at least so we have been told) going to be shut down. Until that happens, I need to be able to do this compare.

Thanks for your help

Please either spool SQL to screen or run SQL Tracker (Database > Monitor menu) and reproduce the issue. Post the offending query here

Not really any sql o find.

I am doing a schema compare from Database > Compare > Schemas


Thanks,


Dave Bowers

Sr. Database Administrator


HNI
Corporation


bowersd@hnicorp.com

(W) 563 272-7211

© 563 299-7211

P Please consider the environment before printing this email

*** This email may contain information that is confidential. If you are not the intended recipient, please notify me immediately and delete the email and any attachments.***


From: Brad Boddicker [mailto:bounce-ButtonPusher@toadworld.com]

Sent: Tuesday, July 18, 2017 3:30 PM

To: toadoracle@toadworld.com

Subject: RE: [Toad for Oracle - Discussion Forum] Toad Schema Compare

RE: Toad Schema Compare

Reply by Brad Boddicker

Please either spool SQL to screen or run SQL Tracker (Database > Monitor menu) and reproduce the issue. Post the offending query here

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or
Unsubscribe from Toad for Oracle Forum
notifications altogether.

Toad for Oracle - Discussion Forum

Flag
this post as spam/abuse.

SQL is run behind the scenes to run those comparisons. The hang, or slowdown is coming because of a query that is being run against the DB. We need to find out what that query is. If you open SQL Monitor, you can monitor all the SQL that Toad is running and once Toad gets hung up, you can go back to SQL Monitor and see this offending statement

Here is the last sql run before it went to sleep:

Select C.CONSTRAINT_NAME, CC.COLUMN_NAME, C.SEARCH_CONDITION, C.GENERATED, C.TABLE_NAME

from SYS.DBA_CONS_COLUMNS cc , SYS.DBA_CONSTRAINTS c

where c.OWNER = cc.OWNER (+)

and c.CONSTRAINT_NAME = cc.CONSTRAINT_NAME (+)

and c.TABLE_NAME = cc.TABLE_NAME (+)

and c.OWNER = ‘PRODDB’

AND C.CONSTRAINT_TYPE in (‘C’, ‘?’, ‘F’)

order by table_name, constraint_name, column_name

FYI. It seems to do the same thing if you extract this sql and run it against another instance (not one involved in the compares).

As I said in the original post, I need this to provide the “NOT NULL” portion of the table creates.

Thanks for your assistance.



Thanks,


Dave Bowers

Sr. Database Administrator


HNI
Corporation


bowersd@hnicorp.com

(W) 563 272-7211

© 563 299-7211

P Please consider the environment before printing this email

*** This email may contain information that is confidential. If you are not the intended recipient, please notify me immediately and delete the email and any attachments.***


From: Brad Boddicker [mailto:bounce-ButtonPusher@toadworld.com]

Sent: Wednesday, July 19, 2017 8:26 AM

To: toadoracle@toadworld.com

Subject: RE: [Toad for Oracle - Discussion Forum] Toad Schema Compare

RE: Toad Schema Compare

Reply by Brad Boddicker

SQL is run behind the scenes to run those comparisons. The hang, or slowdown is coming because of a query that is being run against the DB. We need to find out what that query is. If you open SQL Monitor, you can monitor all the SQL that Toad is running and once Toad gets hung up, you can go back to SQL Monitor and see this offending statement

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or
Unsubscribe from Toad for Oracle Forum
notifications altogether.

Toad for Oracle - Discussion Forum

Flag
this post as spam/abuse.

Also FYI. I let the one compare I had running yesterday which started at about2 PM and it was still running when I can into work this morning at 6:45 AM.


Thanks,


Dave Bowers

Sr. Database Administrator


HNI
Corporation


bowersd@hnicorp.com

(W) 563 272-7211

© 563 299-7211

P Please consider the environment before printing this email

*** This email may contain information that is confidential. If you are not the intended recipient, please notify me immediately and delete the email and any attachments.***


From: Brad Boddicker [mailto:bounce-ButtonPusher@toadworld.com]

Sent: Wednesday, July 19, 2017 8:26 AM

To: toadoracle@toadworld.com

Subject: RE: [Toad for Oracle - Discussion Forum] Toad Schema Compare

RE: Toad Schema Compare

Reply by Brad Boddicker

SQL is run behind the scenes to run those comparisons. The hang, or slowdown is coming because of a query that is being run against the DB. We need to find out what that query is. If you open SQL Monitor, you can monitor all the SQL that Toad is running and once Toad gets hung up, you can go back to SQL Monitor and see this offending statement

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or
Unsubscribe from Toad for Oracle Forum
notifications altogether.

Toad for Oracle - Discussion Forum

Flag
this post as spam/abuse.

Have you figured out why that query is taking so long? It ran instantly for me, but then again, I don’t have a 9i DB to run it against. Do you have any 10g+ DB’s to run the query against? Have you tried running other queries against these Views?

I was under the impression that you would tell me why it takes so long.

I have to run against 9i databases, so having it work on other version might be useful but does me no good.

Is there an alternate query that can be used?


Thanks,


Dave Bowers

Sr. Database Administrator


HNI
Corporation


bowersd@hnicorp.com

(W) 563 272-7211

© 563 299-7211

P Please consider the environment before printing this email

*** This email may contain information that is confidential. If you are not the intended recipient, please notify me immediately and delete the email and any attachments.***


From: Brad Boddicker [mailto:bounce-ButtonPusher@toadworld.com]

Sent: Thursday, July 20, 2017 9:01 AM

To: toadoracle@toadworld.com

Subject: RE: [Toad for Oracle - Discussion Forum] Toad Schema Compare

RE: Toad Schema Compare

Reply by Brad Boddicker

Have you figured out why that query is taking so long? It ran instantly for me, but then again, I don’t have a 9i DB to run it against. Do you have any 10g+ DB’s to run the query against? Have you tried running other queries against these Views?

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or
Unsubscribe from Toad for Oracle Forum
notifications altogether.

Toad for Oracle - Discussion Forum

Flag
this post as spam/abuse.

Well, I’m a QA guy and you’e a DBA, so I think you would be more qualified to figure this one out :slight_smile:

At any rate, our resident Oracle guru who also happens to be the developer who maintains this code is on vacation this week or else he probably would’ve chimed in by now. Maybe he will have some good insight next week.

Since you see this issue on 2 seperate 9i DB’s I was trying to isolate this issue to 9i DB. If the query runs fine against 10g or higher, maybe there is a better view to query in 9i.

When I get a minute, I will run it against newer databases and let you know the results.


Thanks,


Dave Bowers

Sr. Database Administrator


HNI
Corporation


bowersd@hnicorp.com

(W) 563 272-7211

© 563 299-7211

P Please consider the environment before printing this email

*** This email may contain information that is confidential. If you are not the intended recipient, please notify me immediately and delete the email and any attachments.***


From: Brad Boddicker [mailto:bounce-ButtonPusher@toadworld.com]

Sent: Thursday, July 20, 2017 12:03 PM

To: toadoracle@toadworld.com

Subject: RE: [Toad for Oracle - Discussion Forum] Toad Schema Compare

RE: Toad Schema Compare

Reply by Brad Boddicker

Well, I’m a QA guy and you’e a DBA, so I think you would be more qualified to figure this one out :slight_smile:

At any rate, our resident Oracle guru who also happens to be the developer who maintains this code is on vacation this week or else he probably would’ve chimed in by now. Maybe he will have some good insight next week.

Since you see this issue on 2 seperate 9i DB’s I was trying to isolate this issue to 9i DB. If the query runs fine against 10g or higher, maybe there is a better view to query in 9i.

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or
Unsubscribe from Toad for Oracle Forum
notifications altogether.

Toad for Oracle - Discussion Forum

Flag
this post as spam/abuse.

Sounds good.

As a work-around, you could try going to options > startup and uncheck “check for access to DBA views”. Reconnect your session and see if that works out any better. Toad will query different views if this option is unchecked.

I can also try that. Thanks


Thanks,


Dave Bowers

Sr. Database Administrator


HNI
Corporation


bowersd@hnicorp.com

(W) 563 272-7211

© 563 299-7211

P Please consider the environment before printing this email

*** This email may contain information that is confidential. If you are not the intended recipient, please notify me immediately and delete the email and any attachments.***


From: Brad Boddicker [mailto:bounce-ButtonPusher@toadworld.com]

Sent: Thursday, July 20, 2017 12:57 PM

To: toadoracle@toadworld.com

Subject: RE: [Toad for Oracle - Discussion Forum] Toad Schema Compare

RE: Toad Schema Compare

Reply by Brad Boddicker

Sounds good.

As a work-around, you could try going to options > startup and uncheck “check for access to DBA views”. Reconnect your session and see if that works out any better. Toad will query different views if this option is unchecked.

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or
Unsubscribe from Toad for Oracle Forum
notifications altogether.

Toad for Oracle - Discussion Forum

Flag
this post as spam/abuse.

This did not work. It runs the exact same query. I will let it run a while since I do have over 11000 check contraints.


Thanks,


Dave Bowers

Sr. Database Administrator


HNI
Corporation


bowersd@hnicorp.com

(W) 563 272-7211

© 563 299-7211

P Please consider the environment before printing this email

*** This email may contain information that is confidential. If you are not the intended recipient, please notify me immediately and delete the email and any attachments.***


From: Brad Boddicker [mailto:bounce-ButtonPusher@toadworld.com]

Sent: Thursday, July 20, 2017 12:57 PM

To: toadoracle@toadworld.com

Subject: RE: [Toad for Oracle - Discussion Forum] Toad Schema Compare

RE: Toad Schema Compare

Reply by Brad Boddicker

Sounds good.

As a work-around, you could try going to options > startup and uncheck “check for access to DBA views”. Reconnect your session and see if that works out any better. Toad will query different views if this option is unchecked.

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or
Unsubscribe from Toad for Oracle Forum
notifications altogether.

Toad for Oracle - Discussion Forum

Flag
this post as spam/abuse.

When Toad is “hung”, there are two possibilities:

  1. The database is taking a long time to process a query

  2. Toad is working really hard on something, like processing a bunch of data.

In the first case, your PC’s CPU usage in task manager will be near zero. And if you connect to the DB with another copy of Toad, and look in the Session Browser to find the session of the “stuck” Toad, you’ll that it’s session status is “ACTIVE”. You can go to the “Current Statement” tab to see the query. Spool SQL shows the query that just completed, so that’s not always the best technique to find a query that won’t finish.

In the 2nd case, your PCs CPU will be near 100% (or 50%, for dual core, 25% for quad core, etc). One of the processors will be at 100%.

There is some work going on with not null constraints, but it shouldn’t take long even with 11K constraints. I have a 10g database with 20K constraints (half PK and half not null) and I can extract all of them for comparison in just a few minutes. Of course there could be other factors that would make yours slow.

Anyway, see if you can narrow down what is happening and let me know.

In my case the CPU is at zero.

Please remember that my databases are 9i. I know but it isn’t going to change now.


Thanks,


Dave Bowers

Sr. Database Administrator


HNI
Corporation


bowersd@hnicorp.com

(W) 563 272-7211

© 563 299-7211

P Please consider the environment before printing this email

*** This email may contain information that is confidential. If you are not the intended recipient, please notify me immediately and delete the email and any attachments.***


From: John Dorlon [mailto:bounce-jdorlon@toadworld.com]

Sent: Tuesday, July 25, 2017 3:18 PM

To: toadoracle@toadworld.com

Subject: RE: [Toad for Oracle - Discussion Forum] Toad Schema Compare

RE: Toad Schema Compare

Reply by John Dorlon

When Toad is “hung”, there are two possibilities:

  1. The database is taking a long time to process a query

  2. Toad is working really hard on something, like processing a bunch of data.

In the first case, your PC’s CPU usage in task manager will be near zero. And if you connect to the DB with another copy of Toad, and look in the Session Browser to find the session of the “stuck” Toad, you’ll that it’s session status is “ACTIVE”. You can go to the “Current Statement” tab to see the query. Spool SQL shows the query that just completed, so that’s not always the best technique to find a query that won’t finish.

In the 2nd case, your PCs CPU will be near 100% (or 50%, for dual core, 25% for quad core, etc). One of the processors will be at 100%.

There is some work going on with not null constraints, but it shouldn’t take long even with 11K constraints. I have a 10g database with 20K constraints (half PK and half not null) and I can extract all of them for comparison in just a few minutes. Of course there could be other factors that would make yours slow.

Anyway, see if you can narrow down what is happening and let me know.

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or
Unsubscribe from Toad for Oracle Forum
notifications altogether.

Toad for Oracle - Discussion Forum

Flag
this post as spam/abuse.

OK, so it’s likely that the database is taking forever to run a query. Try what I suggested with Session Browser so we know exactly what the problem query is, then post the query here. 9i, I know.

Here is the query that is currently running:

Select C.CONSTRAINT_NAME, CC.COLUMN_NAME, C.SEARCH_CONDITION, C.GENERATED, C.TABLE_NAME

from SYS.DBA_CONS_COLUMNS cc , SYS.DBA_CONSTRAINTS c

where c.OWNER = cc.OWNER (+)

and c.CONSTRAINT_NAME = cc.CONSTRAINT_NAME (+)

and c.TABLE_NAME = cc.TABLE_NAME (+)

and c.OWNER = ‘PRODDB’

AND C.CONSTRAINT_TYPE in (‘C’, ‘?’, ‘F’)

order by table_name, constraint_name, column_name


Thanks,


Dave Bowers

Sr. Database Administrator


HNI
Corporation


bowersd@hnicorp.com

(W) 563 272-7211

© 563 299-7211

P Please consider the environment before printing this email

*** This email may contain information that is confidential. If you are not the intended recipient, please notify me immediately and delete the email and any attachments.***


From: John Dorlon [mailto:bounce-jdorlon@toadworld.com]

Sent: Wednesday, July 26, 2017 3:31 PM

To: toadoracle@toadworld.com

Subject: RE: [Toad for Oracle - Discussion Forum] Toad Schema Compare

RE: Toad Schema Compare

Reply by John Dorlon

OK, so it’s likely that the database is taking forever to run a query. Try what I suggested with Session Browser so we know exactly what the problem query is, then post the query here. 9i, I know.

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or
Unsubscribe from Toad for Oracle Forum
notifications altogether.

Toad for Oracle - Discussion Forum

Flag
this post as spam/abuse.

I have let this query run for as long as 24 hours with no changes.


Thanks,


Dave Bowers

Sr. Database Administrator


HNI
Corporation


bowersd@hnicorp.com

(W) 563 272-7211

© 563 299-7211

P Please consider the environment before printing this email

*** This email may contain information that is confidential. If you are not the intended recipient, please notify me immediately and delete the email and any attachments.***


From: John Dorlon [mailto:bounce-jdorlon@toadworld.com]

Sent: Wednesday, July 26, 2017 3:31 PM

To: toadoracle@toadworld.com

Subject: RE: [Toad for Oracle - Discussion Forum] Toad Schema Compare

RE: Toad Schema Compare

Reply by John Dorlon

OK, so it’s likely that the database is taking forever to run a query. Try what I suggested with Session Browser so we know exactly what the problem query is, then post the query here. 9i, I know.

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or
Unsubscribe from Toad for Oracle Forum
notifications altogether.

Toad for Oracle - Discussion Forum

Flag
this post as spam/abuse.

Your options are somewhat limited in 9i but for me that query runs in less than one second. There’s no reason that you should have to wait 24 hours on this query. It hasn’t changed in Toad since version 12.8, so I think if there were something really wrong with it, we would have heard about it before now.

You can try that query in the editor with various optimizer hints. If you get one that makes it go faster, you can go to Options -> Oracle -> Optimizer Hints, and add a row for DBA_CONS_COLUMNS with 9i as the Oracle version and then Toad will use whatever hint you plug in.

You might want to try Database -> Optimize -> Auto Optimize SQL. That will generate several rewrites and try them automatically. If you get lucky, one of them will just be an optimizer hint and you can plug in into Toad.

You might also want to look at your database - maybe there is a lot of memory swapping going on or something like that and you can throw some more memory at it.

For what it’s worth, I just created 12,000 tables on my 9i database - each with a primary key, a NOT NULL constraint, and a regular check constraint. I was able to compare that schema to another on the same database in just a couple of minutes.

The database is running on a Windows XP virtual machine with 4GB ram and 2 CPUs, on my desktop PC.