ORACLE using decode
I need to list all undo tablespace names in an Oracle (19c) database (regardless of whether they're used or not) from the
My SQL statement below gives that info but it also lists all the tablespace types (which I don't want). If I refine the statement to exclude the types and return just the list of UNDO tablespace name(s) is DECODE the core T function to use?
select name, decode(ts.contents$, 0,(decode(bitand(ts.flags, 4503599627370512), 16, 'UNDO',4503599627370496, 'LOST WRITE PROTECTION','PERMANENT')), 1, 'TEMPORARY') from ts$ ts ;
Is there a better alternative to this function? (Like CASE)
carriann last edited by
It seems you only want rows where
bitand(ts.flags, 4503599627370512)= 16. No decode/case needed.
select * from ts$ ts where ts.contents$ = 0 and bitand(ts.flags, 4503599627370512) = 16;