Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Usenet -> c.d.o.server -> Re: Conceivably a daft question...
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
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
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 *
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 bytesDatabase mounted.
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
![]() |
![]() |