Oracle Script timing

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
PROMPT Enter a desired default tablespace for this user.
PROMPT
PROMPT Please Note:  The tablespace must already exist
PROMPT               If you are unsure, enter PSDEFAULT or SYSTEM
PROMPT
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;
grant PSADMIN TO &ADMIN;
 
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
 
@%ORACLE_HOME%\rdbms\admin\catdbsyn
@%ORACLE_HOME%\sqlplus\admin\pupbld
 
spool off

Open in new window

jorbagwAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

AndytwCommented:
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.

0
jorbagwAuthor Commented:
sorry, complete muppet here (this is not my area). how do I check V$SESSION?
0
jorbagwAuthor Commented:
have toad as well as the full set of oracle tools
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

AndytwCommented:
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:

SELECT  *
FROM v$session
WHERE type <> 'BACKGROUND';

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jorbagwAuthor Commented:
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:
  SELECT  *
FROM v$session
WHERE type <> 'BACKGROUND'
 
SADDR,SID,SERIAL#,AUDSID,PADDR,USER#,USERNAME,COMMAND,OWNERID,TADDR,LOCKWAIT,STATUS,SERVER,SCHEMA#,SCHEMANAME,OSUSER,PROCESS,MACHINE,TERMINAL,PROGRAM,TYPE,SQL_ADDRESS,SQL_HASH_VALUE,SQL_ID,SQL_CHILD_NUMBER,PREV_SQL_ADDR,PREV_HASH_VALUE,PREV_SQL_ID,PREV_CHILD_NUMBER,MODULE,MODULE_HASH,ACTION,ACTION_HASH,CLIENT_INFO,FIXED_TABLE_SEQUENCE,ROW_WAIT_OBJ#,ROW_WAIT_FILE#,ROW_WAIT_BLOCK#,ROW_WAIT_ROW#,LOGON_TIME,LAST_CALL_ET,PDML_ENABLED,FAILOVER_TYPE,FAILOVER_METHOD,FAILED_OVER,RESOURCE_CONSUMER_GROUP,PDML_STATUS,PDDL_STATUS,PQ_STATUS,CURRENT_QUEUE_DURATION,CLIENT_IDENTIFIER,BLOCKING_SESSION_STATUS,BLOCKING_INSTANCE,BLOCKING_SESSION,SEQ#,EVENT#,EVENT,P1TEXT,P1,P1RAW,P2TEXT,P2,P2RAW,P3TEXT,P3,P3RAW,WAIT_CLASS_ID,WAIT_CLASS#,WAIT_CLASS,WAIT_TIME,SECONDS_IN_WAIT,STATE,SERVICE_NAME,SQL_TRACE,SQL_TRACE_WAITS,SQL_TRACE_BINDS
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 9.6.1.1,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

0
jorbagwAuthor Commented:
Andy, getting late here in the UK. pls post as comprehensive an answer as possible and I will address in the a.m.
0
SujithData ArchitectCommented:
>> 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.
0
jorbagwAuthor Commented:
Left the script running overnight. No way it should take that long. Reverting to manual creation
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.