-- 테이블별 사용공간 저장 테이블 생성
SQL> CREATE TABLE tmp_used_space
     (table_name             varchar2(30),
      allocated_space        number,
      used_space             number)
      /

-- 테이블별 사용공간 계산
SQL> DECLARE
        w_blocksize     number;
        w_alloc         number;
        w_used          number;
        LClongstring    varchar2(2000);
        cid             integer;
        rows_processed  number;
    
        CURSOR c_tables IS 
        SELECT segment_name,bytes 
        FROM dba_segments where owner = '&&OWNER' 
        AND segment_type = 'TABLE';
        
    BEGIN
    
        SELECT value INTO w_blocksize 
        FROM v$parameter 
        WHERE name = 'db_block_size';

        FOR r_tables IN c_tables
        LOOP
	        LClongstring := 'SELECT COUNT(DISTINCT(dbms_rowid.rowid_block_number(rowid))) FROM '||r_tables.segment_name;
	        cid := DBMS_SQL.OPEN_CURSOR;
	        DBMS_SQL.PARSE(cid,LClongstring,dbms_sql.v7); 
	        DBMS_SQL.DEFINE_COLUMN(cid,1,w_used);
	        rows_processed := DBMS_SQL.EXECUTE_AND_FETCH(cid);
	        DBMS_SQL.COLUMN_VALUE(cid,1,w_used);
	        INSERT INTO tmp_used_space
	        VALUES (r_tables.segment_name, r_tables.bytes, w_used*w_blocksize);
	        DBMS_SQL.CLOSE_CURSOR(cid);
        END LOOP;
        COMMIT;
    END;
    /

--  테이블별 사용공간 조회
SQL> SET PAGESIZE 60
SQL> SELECT table_name,allocated_space/1024 "ALLOCATED (K)", used_space/1024 "USED (K)" 
     FROM tmp_used_space
     ORDER BY table_name;

-- 임시테이블 삭제
SQL> DROP TABLE tmp_used_space;