Re: Conceivably a daft question...

On Wed, 31 Mar 2004 18:47:32 +1000, "Howard J. Rogers" <hjr_at_dizwell.com> wrote:

>
>"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
>

--
Photo gallery: http://www.pbase.com/john_russell/