All we need is an easy explanation of the problem, so here it is.
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)
How to solve :
I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.
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;
The following works. Maybe there’s a better way to write this though. Perhaps decode shouldn’t be used.
set feedback off set heading off select name from ts$ ts where decode(ts.contents$, 0,(decode(bitand(ts.flags, 4503599627370512), 16, 'UNDO',4503599627370496, 'LOST WRITE PROTECTION','PERMANENT')), 1, 'TEMPORARY')='UNDO' ;
Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂