[筆記]一些SQL指令碼

每次看AskTom都會有新的發現.

不錯!

prompt -- 取6個1到49的隨機數
select r
from ( select r
         from ( select rownum r
                  from all_objects
                 where rownum < 50 )
        order by dbms_random.value )
where rownum <= 6
/

-- 原理參考
select rownum r, dbms_random.value some_random_value
  from all_objects                                 
 where rownum < 50                                 
/
 
 
prompt -- spfile
SELECT DECODE(COUNT(*), 1, 'spfile', 'pfile' )
FROM v$spparameter
WHERE ROWNUM=1
AND isspecified='TRUE'
/

prompt -- 如果判斷物化檢視是如何重新整理的?
select mview_name, refresh_mode, refresh_method,
last_refresh_type, last_refresh_date
from user_mviews
/