VIEW: SYS.USER_AWS

Source

View Text - Preformatted

SELECT a.awseq#, a.awname,
       DECODE(a.version,
              0, '9.1',
              1, '10.1', 2, '10.2',
              3, '11.1', 4, '11.2',
              5, '12.0', NULL),
       n.num, g.gen, f.frozen
FROM aw$ a,
     (SELECT awseq#, COUNT(psgen) gen FROM ps$ WHERE psnumber IS NULL GROUP BY awseq#) g,
     (SELECT awseq#, COUNT(UNIQUE(psnumber)) num FROM ps$ WHERE psnumber IS NOT NULL GROUP BY awseq#) n ,
     (SELECT max(awseq#) awmax, decode(max(mapoffset), 1, 'Frozen',
         2, 'NoThaw', NULL) frozen from ps$ where psnumber is NULL
         group by awseq#) f
WHERE   a.owner#=USERENV('SCHEMAID') and a.awseq#=g.awseq# and a.awseq#=n.awseq#
        and a.awseq# = f.awmax

View Text - HTML Formatted

SELECT A.AWSEQ#
, A.AWNAME
, DECODE(A.VERSION
, 0
, '9.1'
, 1
, '10.1'
, 2
, '10.2'
, 3
, '11.1'
, 4
, '11.2'
, 5
, '12.0'
, NULL)
, N.NUM
, G.GEN
, F.FROZEN FROM AW$ A
, (SELECT AWSEQ#
, COUNT(PSGEN) GEN
FROM PS$
WHERE PSNUMBER IS NULL GROUP BY AWSEQ#) G
, (SELECT AWSEQ#
, COUNT(UNIQUE(PSNUMBER)) NUM
FROM PS$
WHERE PSNUMBER IS NOT NULL GROUP BY AWSEQ#) N
, (SELECT MAX(AWSEQ#) AWMAX
, DECODE(MAX(MAPOFFSET)
, 1
, 'FROZEN'
, 2
, 'NOTHAW'
, NULL) FROZEN
FROM PS$
WHERE PSNUMBER IS NULL GROUP BY AWSEQ#) F WHERE A.OWNER#=USERENV('SCHEMAID')
AND A.AWSEQ#=G.AWSEQ#
AND A.AWSEQ#=N.AWSEQ#
AND A.AWSEQ# = F.AWMAX