Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
pras2007
Posting Yak Master
216 Posts |
 Posted - 2007-11-13 : 03:19:45
|
| Hello All,I have a job that being running for about two years now with no problems; the job on average normally takes about 1 hour 30 minutes to finish running. Over the weekend the Oracle team upgraded from oracle 9i to Oracle 10.1 now my job runs and runs without end. Below is the routine that the job performs: Job Routine- Truncates two tables- Load data from multiple Oracle servers- Drop Indexes- Move data to staging table- Create indexes- Load data into summary tableDoes anyone know how I can trouble shoot this problem on my side? Also what questions do I need to ask the Oracle team?Please advice. Thanks.Ps. I have verified that I have data connection. I am connecting to the Oracle db via tnsnames. |
|
|
elancaster
A very urgent SQL Yakette
1208 Posts |
Posted - 2007-11-13 : 03:25:29
|
| have you run a trace with profiler while the job is running?Em |
 |
|
|
pras2007
Posting Yak Master
216 Posts |
Posted - 2007-11-13 : 06:23:14
|
| Thanks for the response elancaster, no I have not used a trace profile. How would I even start a trace profile? What can of information can I obtain from running the profile? Does anyone know. Please advice. Thanks. |
|
|
|
elancaster
A very urgent SQL Yakette
1208 Posts |
Posted - 2007-11-13 : 10:43:24
|
| sql profiler comes with SQL Server. I always think the best way to get started is to just open it and have a bit of a play. look at the different filters etc you can set so you can narrow it down to just looking at the tables you are manipulating maybe? Books online will be useful too of coursei guess i would use it as a starting point to help identify what's happening on the database when the job hangs.Em |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-11-13 : 10:56:04
|
Job Routine- Drop Indexes- Truncates two tables- Load data from multiple Oracle servers- Move data to staging table- Load data into summary table- Create indexes
E 12°55'05.25"N 56°04'39.16" |
|
|
|
pras2007
Posting Yak Master
216 Posts |
Posted - 2007-11-13 : 16:54:14
|
| Thanks for the response folks; does anyone know if I needed to change my version of the tnsname file once the Oracle db was upgraded? Please advice. Thanks. |
|
|
|
|
|
|
Jobs runs and runs without end