Re: v$log_history and Y2K problem
Date: 1998/03/03
Message-ID: <34FC8A19.436E_at_criticaldata.com>#1/1
OK, I guess I'll have to cobble something together like:
define ARCHIVE_LOG_DAYS=30
select archive_name
from v$log_history where
sysdate - to_date(
substr(time,1,6)||decode(substr(time,7,2),
'98','1998','99','1999','00','2000','01','2001','02','2002',
'03','2003','04','2004','05','2005','06','2006','07','2007',
'08','2008','09','2009','10','2010','11','2011','12','2012',
'13','2013','14','2014','15','2015','16','2016','17','2017',
'18','2018','19','2019','20','2020','21','2021','22','2022','##'),
'MM/DD/YYYY')
> &ARCHIVE_LOG_DAYS;
This will at least give me the archive log files older than 30 days
until the year 2022 at which time I hope most of my customers are
running Ora 8.0!
David A. Owens wrote:
>
> Here's one I have not heard talked about before.
>
> I'm looking at the v$log_history view in Ora 7.3.3 and notice that the
> TIME column is definitely not Y2K compliant. In fact this column is
> defined as (eek!) a varchar2 string. I would like to use this column
> to manage my archive log files (i.e. age them out from offline storage)
> but I will have problems when the year rolls to 00. Is there any way
> to reformat this column or are there other underlying tables which
> represent the log switch date/time in a proper date type?
>
> Thanks for your reply:
>
> David A. Owens DBA --> www.criticaldata.com
> <daveo_at_criticaldata.com>
>
> SQL> desc v$log_history;
> Name Null? Type
> ------------------------------- -------- ----
> THREAD# NUMBER
> SEQUENCE# NUMBER
> TIME VARCHAR2(20)
> LOW_CHANGE# NUMBER
> HIGH_CHANGE# NUMBER
> ARCHIVE_NAME VARCHAR2(257)
>
> SQL> select * from v$log_history;
>
> THREAD# SEQUENCE# TIME LOW_CHANGE# HIGH_CHANGE#
> --------- --------- -------------------- ----------- ------------
> ARCHIVE_NAME
> --------------------------------------------------------------------
> 1 6 02/28/98 13:06:24 5633 5763
> F:\ORANT\RDBMS73\%ORACLE_SID%00006.001
>
> 1 5 02/28/98 13:05:57 5517 5632
> F:\ORANT\RDBMS73\%ORACLE_SID%00005.001
>
> 1 4 02/28/98 13:05:27 5368 5516
> F:\ORANT\RDBMS73\%ORACLE_SID%00004.001
Received on Tue Mar 03 1998 - 00:00:00 CET