Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> c.d.o.server -> Re: Newbie question: SQL*Plus command to see number of query result rows

Re: Newbie question: SQL*Plus command to see number of query result rows

From: Howard J. Rogers <>
Date: Sun, 14 Mar 2004 08:13:41 +1100
Message-ID: <4053798d$0$18490$>

"Lara" <> wrote in message
> Hi, I have what I think is a simple SQL*Plus question, but I can't
> find the answer in any of my books.
> I'm trying to turn off SQL*Plus displaying the result of each row in a
> select statement, and just want to see the number of rows the select
> statement would generate. I'm trying to see if my select statement is
> correctly generating the correct number of rows. I know it should
> generate 154,280 rows, but it takes too long to sit here and wait to
> see the results if every row of the query result displays.
> In other words (in a shortened example), I currently have:
> SQL> Select student_id from student;
> ----------
> 391
> 392
> 393
> 3 rows selected.
> Is there any command I can use to get:
> SQL> Select student_id from student;
> 3 rows selected.
> p.s. I don't think I can use COUNT because there are multiple joins
> and subqueries throughout the select.
> Thanks!

Some surprising other answers to your perfectly reasonable request, I note. So here's my effort.

Yes, you can turn off the results display.

First make sure you have a plan_table created. If 'desc plan_table' produces the error that the table doesn't exist, then create it by running the script utlxplan.sql supplied by Oracle in ORACLE_HOME/rdbms/admin.

With that done, issue the following command:

set autotrace trace statistics

Then execute your query. You won't get the results returned, but you will gets the line about '176573 rows selected', and you'll also get some other interesting information about how much work your SQL statement is doing.

As a simple example, here is my test run:

SQL> desc plan_table
ORA-04043: object plan_table does not exist

SQL> @?\rdbms\admin\utlxplan

Table created.

SQL> set autotrace trace statistics
SQL> select * from emp;

14 rows selected.


        250  recursive calls
          0  db block gets
         42  consistent gets
          3  physical reads
          0  redo size
       1313  bytes sent via SQL*Net to client
        499  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          4  sorts (memory)
          0  sorts (disk)
         14  rows processed

Just one word of warning. Autotrace needs access to some V$ views. If you're not sufficiently privileged, you'll get warned about this when you attempt to trace a SQL statement. In that case, you'll need a dba to create the plustrace role, using a script provided by Oracle for the purpose (called plustrce.sql in ORACLE_HOME/sqlplus/admin) and then grant the new "plustrace" role to you).

HJR Received on Sat Mar 13 2004 - 15:13:41 CST

Original text of this message