Toad Schema Compare

(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

Subject: RE: [Toad for Oracle - Discussion Forum] 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.

ButtonPusher · July 19, 2017, 6:21am

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

bowersd · July 19, 2017, 8:10am

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

(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

Subject: RE: [Toad for Oracle - Discussion Forum] 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.

bowersd · July 19, 2017, 8:14am

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

(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

Subject: RE: [Toad for Oracle - Discussion Forum] 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.

ButtonPusher · July 20, 2017, 7:00am

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?

bowersd · July 20, 2017, 8:17am

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

(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

Subject: RE: [Toad for Oracle - Discussion Forum] 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.

ButtonPusher · July 20, 2017, 10:01am

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

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.

bowersd · July 20, 2017, 10:51am

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

(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

Subject: RE: [Toad for Oracle - Discussion Forum] 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

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.

ButtonPusher · July 20, 2017, 10:56am

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.

bowersd · July 20, 2017, 11:06am

I can also try that. Thanks

Thanks,

Dave Bowers

Sr. Database Administrator

HNI
Corporation

(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

Subject: RE: [Toad for Oracle - Discussion Forum] 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.

bowersd · July 20, 2017, 12:18pm

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

(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

Subject: RE: [Toad for Oracle - Discussion Forum] 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.

JohnDorlon · July 25, 2017, 1:17pm

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

The database is taking a long time to process a query
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.

bowersd · July 26, 2017, 1:26pm

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

(W) 563 272-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

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

Reply by John Dorlon

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

The database is taking a long time to process a query
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.

JohnDorlon · July 26, 2017, 1:30pm

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.

bowersd · July 26, 2017, 1:33pm

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

(W) 563 272-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

Subject: RE: [Toad for Oracle - Discussion Forum] 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.

bowersd · July 26, 2017, 1:45pm

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

Thanks,

Dave Bowers

Sr. Database Administrator

HNI
Corporation

(W) 563 272-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

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