Oracle Script timing

Posted on 2009-05-25
Medium Priority
Last Modified: 2013-12-18
The script below is being run by an automated installation (of Peoplesoft)

My question is, I want an idea of how long this should take to run. It has been running for several hours. My concern is that the automated process may not be handling the prompts in the scripts, and therefore the process is hanging, waiting for a response.

Alternatively, this might actually take several hours and I should leave it alone.

Oracle 10.0.2 database
REMARK -- This script sets up the PeopleSoft Owner ID.  An Oracle DBA is
REMARK -- required to run this script.
set echo on
spool psadmin.log
ACCEPT ADMIN CHAR PROMPT 'Enter name of PeopleSoft Owner ID: '
ACCEPT PASSWORD CHAR PROMPT 'Enter PeopleSoft Owner ID password:'
PROMPT Enter a desired default tablespace for this user.
PROMPT Please Note:  The tablespace must already exist
PROMPT               If you are unsure, enter PSDEFAULT or SYSTEM
ACCEPT TSPACE CHAR PROMPT 'Enter desired default tablespace:'
REMARK -- Create the PeopleSoft Administrator schema.
create user &ADMIN identified by &PASSWORD default tablespace &TSPACE
temporary tablespace pstemp;
REMARK -- PeopleSoft Administrator needs unlimited tablespace in order to
REMARK -- create the PeopleSoft application tablespaces and tables in Data
REMARK -- Mover.  This system privilege can only be granted to schemas, not
REMARK -- Oracle roles.
grant unlimited tablespace to &ADMIN;
REMARK -- Run the commands below to create database synonyms.
REMARK -- Modify the connect string appropriately for your organization.
connect system/manager
set echo off
spool off

Open in new window

Question by:jorbagw
  • 5
  • 2
LVL 11

Expert Comment

ID: 24468645
The only things that should take any time are the calls to the scripts catdbsyn, and pupbld. However, I'm very surprised that these should be taking hours to run.

Check V$SESSION, and actually see what the session running this script is doing - this should help you resolve this.  It will either
1). waiting on some event
2). Running SQL

If it is waiting on "SQL*Net message from client" event it could be that the command window has been paused.  Or it could be waiting to acquire some lock, and is being blocked by another session.

If you still can't resolve this post the results of V$SESSION.


Author Comment

ID: 24468676
sorry, complete muppet here (this is not my area). how do I check V$SESSION?

Author Comment

ID: 24468726
have toad as well as the full set of oracle tools
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

LVL 11

Accepted Solution

Andytw earned 800 total points
ID: 24469131
No problem.  When I said check V$SESSION, I was referring to the dynamic view V$SESSION which shows session information for all current sessions. Query it by issue the following command in TOAD / SQL*Plus:

FROM v$session

You should be able to use the machine, terminal and program columns to identify "your" session, and post the results.

Author Comment

ID: 24469213
Sorry, not clear yet ;o)
have run the sql via toad. result below
Guessing it's the third row but what does "jobq slave wait" mean?
I'm an app specialist. work with sql and procedural languages. So can probably follow you, but dont assume any background knowledge
SQL Statement which produced this data:
FROM v$session
2932A004,141,12,66,2925132C,51,SYSMAN,47,2147483644,283A4BB8,null,ACTIVE,DEDICATED,51,SYSMAN,null,1234,vm,null,OMS,USER,20239C48,4281219134,2b064ybzkwf1y,0,20239C48,4281219134,2b064ybzkwf1y,0,OEM.SystemPool,2960518376,null,0,vm.mydomain.com_Management_Service,5510,-1,0,0,0,25/05/2009 21:30:14,27,NO,NONE,NONE,NO,null,DISABLED,ENABLED,ENABLED,0,null,UNKNOWN,null,null,1674,43,wait for unread message on broadcast channel,channel context,680224920,288B6898,channel handle,680193184,288AECA0,null,0,00,2723168908,6,Idle,0,27,WAITING,PTSYS,DISABLED,FALSE,FALSE
2932B2CC,142,8,64,2925074C,51,SYSMAN,0,2147483644,null,null,INACTIVE,DEDICATED,51,SYSMAN,null,1234,vm,null,OMS,USER,00,0,null,null,2023BA98,4052273390,b9huk6zssjk7f,0,OEM.SystemPool,2960518376,XMLLoader0,76787928,vm.mydomain.com_Management_Service,5510,-1,0,0,0,25/05/2009 21:30:14,10,NO,NONE,NONE,NO,null,DISABLED,ENABLED,ENABLED,0,null,NO HOLDER,null,null,8046,256,SQL*Net message from client,driver id,675562835,28444553,#bytes,1,00000001,null,0,00,2723168908,6,Idle,0,10,WAITING,PTSYS,DISABLED,FALSE,FALSE
2932C594,143,1265,0,2924CBEC,0,null,0,2147483644,null,null,ACTIVE,DEDICATED,0,SYS,SYSTEM,3432,VM,VM,ORACLE.EXE (J000),USER,00,0,null,0,00,0,null,0,null,0,null,0,null,5486,-1,0,0,0,25/05/2009 23:56:02,42,NO,NONE,NONE,NO,null,DISABLED,ENABLED,ENABLED,0,null,UNKNOWN,null,null,1,229,jobq slave wait,null,0,00,null,0,00,null,0,00,2723168908,6,Idle,0,42,WAITING,SYS$USERS,DISABLED,FALSE,FALSE
2932EB24,145,10,68,2924D1DC,24,DBSNMP,0,2147483644,null,null,INACTIVE,DEDICATED,24,DBSNMP,NT AUTHORITY\SYSTEM,3444:1968,WORKGROUP\VM,VM,emagent.exe,USER,00,0,null,null,20008280,788854118,0abhdj0rh9xb6,0,emagent.exe,0,null,0,null,5506,8754,3,2508,0,25/05/2009 21:31:39,42,NO,NONE,NONE,NO,null,DISABLED,ENABLED,ENABLED,0,null,NO HOLDER,null,null,4299,256,SQL*Net message from client,driver id,1413697536,54435000,#bytes,1,00000001,null,0,00,2723168908,6,Idle,0,42,WAITING,SYS$USERS,DISABLED,FALSE,FALSE
29333644,149,33,69,2925191C,24,DBSNMP,0,2147483644,null,null,ACTIVE,DEDICATED,24,DBSNMP,NT AUTHORITY\SYSTEM,3444:1352,WORKGROUP\VM,VM,emagent.exe,USER,00,0,null,null,2005B954,1744635340,c1599tjmzu1fc,0,emagent.exe,0,null,0,null,5514,-1,0,0,0,25/05/2009 21:31:42,0,NO,NONE,NONE,NO,null,DISABLED,ENABLED,ENABLED,0,null,UNKNOWN,null,null,7995,266,Streams AQ: waiting for messages in the queue,queue id,8808,00002268,process#,690297772,29251BAC,wait time,5,00000005,2723168908,6,Idle,0,0,WAITING,SYS$USERS,DISABLED,FALSE,FALSE
2933490C,150,83,65,29250D3C,51,SYSMAN,0,2147483644,null,null,INACTIVE,DEDICATED,51,SYSMAN,null,1234,vm,null,OMS,USER,00,0,null,null,202433BC,4162642968,cb75rw3w1tt0s,0,OEM.SystemPool,2960518376,null,0,vm.mydomain.com_Management_Service,5514,182,1,1308,0,25/05/2009 21:30:14,6,NO,NONE,NONE,NO,null,DISABLED,ENABLED,ENABLED,0,null,NO HOLDER,null,null,12759,256,SQL*Net message from client,driver id,675562835,28444553,#bytes,1,00000001,null,0,00,2723168908,6,Idle,0,6,WAITING,PTSYS,DISABLED,FALSE,FALSE
29335BD4,151,3432,143,2924EF8C,5,SYSTEM,3,2147483644,null,null,ACTIVE,DEDICATED,5,SYSTEM,VM\J,1556:2360,WORKGROUP\VM,VM,toad.exe,USER,28D2236C,2787743394,ca7hfvam2m5p2,0,200439B0,3464925656,dzm19sb78d4fs,0,TOAD,754829434,null,0,null,5521,-1,0,0,0,25/05/2009 23:55:25,0,NO,NONE,NONE,NO,null,DISABLED,ENABLED,ENABLED,0,null,NOT IN WAIT,null,null,69,252,SQL*Net message to client,driver id,1413697536,54435000,#bytes,1,00000001,null,0,00,2000153315,7,Network,-1,0,WAITED SHORT TIME,PTSYS,DISABLED,FALSE,FALSE

Open in new window


Author Comment

ID: 24469415
Andy, getting late here in the UK. pls post as comprehensive an answer as possible and I will address in the a.m.
LVL 27

Assisted Solution

sujith80 earned 200 total points
ID: 24470177
>> My question is, I want an idea of how long this should take to run
You may run the script from an sqlplus prompt to see how long it actually runs.

>> My concern is that the automated process may not be handling the prompts in the scripts
How is this script run from your utility? Are you invoking a command like "sqlplus user/pass @scipt" ?
If so consider changing the substitution variables to positional parameters like &1, &2 .. so that you can pass the values for these arguments on command line.

Author Comment

ID: 24470411
Left the script running overnight. No way it should take that long. Reverting to manual creation

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
An introductory discussion about Oracle Analytic Functions which are used to calculate or compute Aggregate values, based on a group of rows.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines

624 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question