COUNT AND SIZE TABLES

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;
/