Re: Request for test help

DA Morgan 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.

Hi Daniel,

Same here on 10.2.0.3 on Linux x86, will try x86_64 later.

SQL> SELECT dbms_undo_adv.undo_advisor(1489,1549, 1) FROM dual;

SELECT dbms_undo_adv.undo_advisor(1489,1549, 1)

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

Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Prod PL/SQL Release 10.2.0.3.0 - Production
CORE 10.2.0.3.0 Production
TNS for Linux: Version 10.2.0.3.0 - Production NLSRTL Version 10.2.0.3.0 - Production

Linux 2.6.9-42.0.0.0.1.ELhugemem #1 SMP Sun Oct 15 14:06:18 PDT 2006 i686 athlon i386 GNU/Linux

Regards,
Marc Received on Fri Jul 20 2007 - 06:41:30 CDT