SQLPLUS hangs for a script ...

GurcanK
GurcanK used Ask the Experts™
on
Dear experts,

I created an sql file running a series of analyze table and index rebuild commands for group of tables. When I run the script in Toad, it is OK. However, via sqlplus -s, it hangs at the middle of the operation.

An example run:

$ORACLE_HOME/bin/sqlplus -s xxx/yyy@zzz @/opt/app/table_statistics.sql

The system is Red-Hat Enterprise Linux with 64 GB of RAM, 4 Processors. How can I solve this problem?

BR,
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
You will need to supply the script so that we can try to determine why it hangs.

One difference between Toad and Sqlplus is the way in which pl/sql blocks are run.  If you have a script containing a pl/sql block, ensure you place a forward slash "/" on the line following the block as it is this slash that tells sqlplus to run the pl/sql block.  I don't believe Toad requires this slash to run the block.

Have you tried running the script in sqlplus without the -s (silent) option so you can determine exactly where it hangs?

Author

Commented:
Hi

The script is just a series of : analyze table <table name> compute statistics;

When I run manually in sqlplus, it again hangs.

BR,
Does it always hang at the same point in the script?  If so, can you provide a sample of the code causing it to hang?

Which version of Oracle are you using?

Are you aware that in later versions, DBMS_STATS is a better method of collecting statistics than using ANALYZE?
Success in ‘20 With a Profitable Pricing Strategy

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

Author

Commented:
May I specify a time-out of a command in a script?

Author

Commented:
The version of Oracle is 11g Release 2...
"May I specify a time-out of a command in a script?"
- I'm not aware of any way of doing this.
Why is the statement taking a long time to run?  

With 11g you should be using the DBMS_STATS package to gather stats, not ANALYZE, which is still available, but is deprecated.

Also, are you aware that 11g automatically gathers statistics each day by default?

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial