Re: Request for test help

From: DA Morgan <damorgan_at_psoug.org>
Date: Mon, 16 Jul 2007 18:23:12 -0700
Message-ID: <1184635390.370986@bubbleator.drizzle.com>

Tom Swier wrote:

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

Thanks everyone that helped. Oracle has now opened an official bug on this and I expect we won't be seeing it in 11gR1. Though it would also seem no one in four years has tried that particular overload before.

Again thanks.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

Received on Mon Jul 16 2007 - 20:23:12 CDT