CREATE OR REPLACE PROCEDURE ORHAN.proc_count_and_size_tables
IS
— ORHAN.count_and_size_tables ve ORHAN.count_and_size_tables_hist tablosuna DWH user’larının
–tüm tabloların count’unu ve toplam tablo büyüklüğünü girer,
— created by ORHAN ERİPEK. 26.02.2009

s_name varchar2 (2000);
s_type varchar2 (2000);
s_owner varchar2 (2000);
v_select_count varchar2 (2000);
v_select_size varchar2 (2000);
v_update_main varchar2 (2000);
v_insert varchar2 (2000);
v_update_hist varchar2 (2000);
s_count number;
s_size number;

CURSOR cur
IS
SELECT owner,
segment_type,
segment_name,
ROUND (SUM (bytes) / (1024 * 1024)) segment_size_mb
FROM sys.dba_segments
WHERE UPPER (segment_type) LIKE ‘%TABLE%’
OR UPPER (segment_type) LIKE ‘%INDEX%’
AND owner IN
(‘USER1’,’USER2’)
GROUP BY owner, segment_type, segment_name
ORDER BY segment_size_mb DESC;
BEGIN
EXECUTE IMMEDIATE ‘truncate table ORHAN.count_and_size_tables’;

COMMIT;

v_insert :=
‘INSERT /*+ APPEND NOLOGGING */ALL
INTO ORHAN.count_and_size_tables(owner,segment_name)
INTO ORHAN.count_and_size_tables_hist(owner,segment_name)
SELECT owner,segment_name
FROM sys.dba_segments WHERE UPPER (segment_type) LIKE ”%TABLE%” OR UPPER (segment_type) LIKE ”%INDEX%”
AND owner IN (”USER1”,”USER2”)
GROUP BY owner,segment_name
ORDER BY segment_name’;

–DBMS_OUTPUT.put_line (v_insert);

EXECUTE IMMEDIATE v_insert;

COMMIT;

OPEN cur;

LOOP
FETCH cur INTO s_owner, s_type, s_name, s_size;

EXIT WHEN cur%NOTFOUND;

–v_select_count := ‘select count(*) from ‘ || s_name || ”;

–DBMS_OUTPUT.put_line (v_select_count);

— EXECUTE IMMEDIATE v_select_count INTO s_count;

–DBMS_OUTPUT.put_line (s_count);

v_update_main :=
‘update ORHAN.count_and_size_tables set log_date=sysdate,segment_type=’
|| ””
|| s_type
|| ””
|| ‘,segment_size_mb=’
|| s_size
|| ‘ where owner=”’
|| s_owner
|| ”’ and segment_name=”’
|| s_name
|| ””;

–DBMS_OUTPUT.put_line (v_update_main);

EXECUTE IMMEDIATE v_update_main;

COMMIT;

v_update_hist :=
‘update ORHAN.count_and_size_tables_hist set log_date=sysdate,segment_type=’
|| ””
|| s_type
|| ””
|| ‘,segment_size_mb=’
|| s_size
|| ‘ where owner=”’
|| s_owner
|| ”’ and segment_name=”’
|| s_name
|| ””;

–DBMS_OUTPUT.put_line (v_update_hist);

EXECUTE IMMEDIATE v_update_hist;

COMMIT;

END LOOP;

CLOSE cur;
END;
/