Re: Conceivably a daft question...

From: Howard J. Rogers <hjr_at_dizwell.com>
Date: Wed, 31 Mar 2004 18:47:32 +1000
Message-ID: <406a85a6$0$15063$afc38c87@news.optusnet.com.au>

"Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in message news:c4dtqd$ok7$1_at_hercules.btinternet.com...
>
> Having created user HOWARD, do you connect as
> howard, or were you still connected as SYS. Autotrace
> acquires its statistics from a second session on the same
> connection, and uses the AUDSID to identify the first
> session. Funny things happen for SYS, though, as any
> session on the SYS account runs with AUDSID = 0.
>
> --
> Regards
>
> Jonathan Lewis
> http://www.jlcomp.demon.co.uk

No, I definitely connect as Howard.

I've met the SYS problem many times, and learnt the hard way to remember to log in as Scott before doing a demo. But this is the first time I can recall meeting the problem as an ordinary user -though I did wonder if granting the dba role makes a difference (it doesn't).

Here's a sample script:

set echo on

connect / as sysdba
startup force

create user howard identified by rogers
default tablespace users
temporary tablespace temp;

grant dba to howard;

connect howard/rogers

create table blah
as select * from dba_objects where rownum < 1000;

connect / as sysdba
shutdown immediate
startup

connect / as sysdba
spool blocktestrun.txt
alter system checkpoint;

connect howard/rogers
@?\rdbms\admin\utlxplan

set timing on
set autotrace trace stat
select * from blah;

... And here's the output:

SQL> @blah
SQL> set echo on
SQL>
SQL> connect / as sysdba

Connected.
SQL> startup force
ORACLE instance started.

Total System Global Area 183978608 bytes

Fixed Size                   453232 bytes
Variable Size             142606336 bytes
Database Buffers           16777216 bytes
Redo Buffers               24141824 bytes

Database mounted.
Database opened.
SQL>
SQL> create user howard identified by rogers   2 default tablespace users
  3 temporary tablespace temp;

User created.

SQL> grant dba to howard;
Grant succeeded.

SQL> connect howard/rogers
Connected.
SQL> create table blah
  2 as select * from dba_objects where rownum < 1000;

Table created.

SQL> connect / as sysdba
Connected.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 183978608 bytes

Fixed Size                   453232 bytes
Variable Size             142606336 bytes
Database Buffers           16777216 bytes
Redo Buffers               24141824 bytes

Database mounted.
Database opened.
SQL>
SQL> connect / as sysdba
Connected.
SQL> spool blocktestrun.txt
SQL> alter system checkpoint;

System altered.

SQL> connect howard/rogers
Connected.
SQL> @?\rdbms\admin\utlxplan

Table created.

SQL> set timing on
SQL> set autotrace trace stat
SQL> select * from blah;

999 rows selected.

Elapsed: 00:00:00.01

Statistics

---

          0  recursive calls
          0  db block gets
          0  consistent gets
          0  physical reads
          0  redo size
          0  bytes sent via SQL*Net to client
          0  bytes received via SQL*Net from client
          0  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
        999  rows processed

So it's obviously not a size thing as I first suspected, but for the life of me I can't see why it's happening. Incidentally, I wondered whether it mattered who I was when I first invoked the SQL script, but it doesn't. Log on as SYS and run it, above result. Log on as howard and run it... same thing.

Any clues to the (probably) blindingly obvious that I've missed?

Cheers,
HJR Received on Wed Mar 31 2004 - 02:47:32 CST