OracleNotes

Category: Database

  • Tablespace Free Space and Max Size

    Earlier, provided a small script to find the maximum growable size of a tablespace… Here is little more comprehensive script that shows more tablespace space info. Current size, growable size based on the autoextensible data files, free space, biggest chunk…All size is in MB.

    SELECT a.tablespace_name TSNAME, 
        ROUND(SUM(a.growable)/1048576) growable, 
        ROUND(SUM(a.tots)/1048576) Tot_Size,
        ROUND(SUM(a.sumb)/1048576) Tot_Free,
        ROUND(SUM(a.sumb)*100/sum(a.tots)) Pct_Free,
        ROUND(SUM(a.largest)/1048576) Large_Ext
FROM   (SELECt tablespace_name, 0 tots, SUM(bytes) sumb,
               MAX(bytes) largest, 0 growable
        FROM   dba_free_space a
        GROUP BY tablespace_name
        UNION
        SELECT tablespace_name, SUM(bytes) tots, 0, 0,  SUM (GREATEST (bytes, DECODE (autoextensible, 'YES', maxbytes, 0))) growable
        FROM   dba_data_files
        GROUP BY tablespace_name
        UNION
        SELECT tablespace_name, SUM(bytes) tots, 0, 0,  SUM (GREATEST (bytes, DECODE (autoextensible, 'YES', maxbytes, 0))) growable
        FROM   dba_temp_files
        GROUP BY tablespace_name) a, dba_tablespaces b
WHERE b.tablespace_name = a.tablespace_name
GROUP BY rollup(a.tablespace_name)
ORDER by a.tablespace_name

    Enjoy!

     

  • Script to Refresh Materialized Views

    The following script can be used to refresh materialized views that are capable of FAST (incremental) refresh automatically. Can be used on EBS database as well if you un-comment the commented (REM) lines. The refresh criteria used is any fast refresh-able materialized view that has not been refreshed in the past 24 hours, but was refreshed in the last one month…

    REM Uncomment below line if EBS database
REM alter session set current_schema=apps;

set serveroutput on size 9999

Declare
cursor mv2ref is select owner ||'.'||  mview_name mview
       from dba_mviews
       where last_refresh_date between sysdate -30 and sysdate -1
       and fast_refreshable = 'DML';
dummy pls_integer;

begin

REM Uncomment below line if EBS database
REM select MRP_AP_REFRESH_S.NEXTVAL into dummy from dual;

for rmv2ref in mv2ref loop
begin
    dbms_mview.refresh(rmv2ref.mview, 'F');
    dbms_output.put_line(rmv2ref.mview ||' Refreshed at '|| to_char(systimestamp));

exception
when others then
    dbms_output.put_line(rmv2ref.mview ||' Errored at '|| to_char(systimestamp));
    dbms_output.put_line('    Error: '|| sqlcode ||':'||sqlerrm);

end;

end loop;

end;
/

    Enjoy!