Re: Request for test help

On Fri, 13 Jul 2007 14:54:50 -0700, DA Morgan <damorgan_at_psoug.org> wrote:

>Can anyone get this to work in any version of 10gR1 or 10gR2?
>
>col instart_fmt noprint;
>col inst_name format a12 heading 'Instance';
>col db_name format a12 heading 'DB Name';
>col snap_id format 99999990 heading 'Snap Id';
>col snapdat format a18 heading 'Snap Started' just c;
>col lvl format 99 heading 'Snap|Level';
>set heading on;
>break on inst_name on db_name on host on instart_fmt skip 1;
>ttitle off;
>
>SELECT TO_CHAR(s.startup_time,' DD MON "at" HH24:MI:SS') INSTART_FMT,
>di.instance_name INST_NAME, di.db_name DB_NAME, s.snap_id SNAP_ID,
>TO_CHAR(s.end_interval_time,'DD MON YYYY HH24:MI') SNAPDAT,
>s.snap_level LVL
>FROM dba_hist_snapshot s, dba_hist_database_instance di
>WHERE di.dbid = s.dbid
>AND di.instance_number = s.instance_number
>AND di.startup_time = s.startup_time
>ORDER BY snap_id;
>
>SELECT dbms_undo_adv.undo_advisor(<starting_snap_id, <ending_snap_id>,
>1) FROM dual;
>
>For example:
>
>SELECT dbms_undo_adv.undo_advisor(587, 600, 1)
>FROM dual;
>
>If you do not have a license to access the two dba_hist tables
>please do not do so at your employer's expense.
>
>I would like to know the exact version number of 4 decimal places
>and whether it succeeds or fails. Thanks.

Tru64 UNIX does the same thing.

Compaq Tru64 UNIX V5.1B (Rev. 2650); Fri Jul 28 10:53:10 EDT 2006

SQL*Plus: Release 10.1.0.3.0 - Production on Mon Jul 16 10:06:53 2007 Copyright (c) 1982, 2004, Oracle. All rights reserved.  

Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.3.0 - Production With the Partitioning, OLAP and Data Mining options SQL> SELECT dbms_undo_adv.undo_advisor(13354, 13529, 1) FROM dual; SELECT dbms_undo_adv.undo_advisor(13354, 13529, 1) FROM dual

       *
ERROR at line 1:
ORA-14552: cannot perform a DDL, commit or rollback inside a query or DML

ORA-06512: at "SYS.PRVT_ADVISOR", line 3658 
ORA-14551: cannot perform a DML operation inside a query 
ORA-06512: at "SYS.DBMS_UNDO_ADV", line 703 
ORA-06512: at "SYS.DBMS_UNDO_ADV", line 752 
 

Elapsed: 00:00:01.17
SQL> Received on Mon Jul 16 2007 - 18:47:32 CDT