Re: Conceivably a daft question...

From: Connor McDonald <hamcdc_at_yahoo.co.uk>
Date: Wed, 31 Mar 2004 21:12:22 +0800
Message-ID: <406AC3B6.5896@yahoo.co.uk>

Niall Litchfield wrote:
>
> "Holger Baer" <holger.baer_at_science-computing.de> wrote in message
> news:c4e4lc$463$1_at_news.BelWue.DE...
> > No, what I was getting at is the fact that even with dba, you don't seem
> > to have all necessary privileges. I followed you test with a vanilla
> > 9.2.0.5 installation with the same results. After creating the plustrace
> > role (which btw. is explicitly granted to dba by running
> @?\sqlplus\admin\plustrce)
> > I got the expected trace data.
>
> my script to test this
>
> set echo on
>
> connect /@nl9204 as sysdba
> startup force
>
> drop user howard cascade;
> create user howard identified by rogers
> default tablespace users
> temporary tablespace temp;
>
> @c:\oracle\ora92\sqlplus\admin\plustrce
>
> grant dba to howard;
>
> connect howard/rogers_at_nl9204
>
> create table blah
> as select * from dba_objects where rownum < 1000;
>
> connect /@nl9204 as sysdba
> shutdown immediate
> startup
>
> connect /@nl9204 as sysdba
> spool blocktestrun2.txt
> alter system checkpoint;
>
> connect howard/rogers_at_nl9204
> @c:\oracle\ora92\rdbms\admin\utlxplan
>
> set timing on
> set autotrace trace stat
> select * from blah;
> spool off
>
> which resulted in the exact same behaviour that Howard describes.
>
> System altered.
>
> Elapsed: 00:00:01.09
> Connected.
>
> Table created.
>
> Elapsed: 00:00:00.58
>
> 999 rows selected.
>
> Elapsed: 00:00:00.23
>
> 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
>
> this also reproduces on 10.1
>
> --
> Niall Litchfield
> Oracle DBA
> Audit Commission UK
> --
> Niall Litchfield
> Oracle DBA
> Audit Commission UK

Your script works fine on my 9204 (Win)

C:\>sqlplus /nolog

SQL*Plus: Release 9.2.0.4.0 - Production on Wed Mar 31 21:08:59 2004

SQL> connect sys/sys as sysdba
Connected.
SQL> drop user howard cascade;
drop user howard cascade

*
ERROR at line 1:
ORA-01918: user 'HOWARD' does not exist

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

User created.

SQL> @c:\ora92\sqlplus\admin\plustrce
SQL>
SQL> drop role plustrace;
drop role plustrace
          *

ERROR at line 1:
ORA-01919: role 'PLUSTRACE' does not exist

SQL> create role plustrace;

Role created.

SQL>
SQL> grant select on v_$sesstat to plustrace;

Grant succeeded.

SQL> grant select on v_$statname to plustrace;

Grant succeeded.

SQL> grant select on v_$mystat to plustrace;

Grant succeeded.

SQL> grant plustrace to dba with admin option;

Grant succeeded.

SQL>
SQL> set echo off
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 sys/sys as sysdba
Connected.
SQL> startup force
ORACLE instance started.

Total System Global Area 85007956 bytes

Fixed Size                   454228 bytes
Variable Size              67108864 bytes
Database Buffers           16777216 bytes
Redo Buffers                 667648 bytes

Database mounted.
Database opened.
SQL> alter system checkpoint;

System altered.

SQL> connect howard/rogers
Connected.
SQL> @c:\ora92\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.03

Statistics

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

SQL> spool off
not spooling currently
SQL>

-- 
-------------------------------
Connor McDonald
http://www.oracledba.co.uk
Co-Author: "Mastering Oracle PL/SQL - Practical Solutions"

Received on Wed Mar 31 2004 - 07:12:22 CST