了解Maclean Liu|向Maclean Liu提问 Oracle ALLSTARS 全明星(群内有多位Oracle高级售后support,N位OCM和ACE) QQ群 # QQ群号:23549328 # 已经升级到 2000人群,空位多多。欢迎有一定基础的Oracle骨友加入,现在入群需要经过Maclean的技术面试,欢迎面试,请加QQ号:47079569 为好友参加面试 2群基础群 适合刚入门的同学,会共享最佳入门实践和资料 QQ群 # QQ群号:171092051 # 已经升级到 500人的超级群,空位多多,无需面试

Script: Computing Table Size

This script calculates the average row size for all tables in a schema. It generates a script (getAvgCol.sql) from USER_TABLES and then runs it. The following type of SELECT is generated for each table in USER_TABLES: SELECT round(avg(nvl(vsize(COL1),0)) + round(avg(nvl(vsize(COL2),0)) + ... + round(avg(nvl(vsize(COLn),0)) Where n=# of cols. on the table Tables with LONG and LOB columns will not report row size properly. Also tables with object types will throw the following error and will also not report row size properly: ORA-00932: inconsistent datatypes ============= Sample Output ============= ACCOUNTS 6 ACCTS 39 ACCT_ADDRS 38 BAD_DATA 116 BASE1 6 BONUS CEG1 11 CHESS_SAVE CHESS_SAVE_PLAYER CITIES 36 COMPANY_SUMMARY 60 CR_FILES 113

Script:

SET ECHO off
REM NAME:   ROWSZ.SQL

drop table column_counts;
create table column_counts
        (
        table_name,
        column_count
        )
        as
        (
        select table_name, max(column_id)
        from user_tab_columns
        where data_type not like 'LONG%' AND table_name in
        (select table_name from user_tables)
        group by table_name
        )
        ;
set pages 0
set tab on
set trim on
set verify off
set feedback off
set termout off
set head off
set lines 100
set recsep off
set embedded on
spool getavgcol.sql
prompt column TB format A30
prompt set head off recsep off
prompt set lines 80 feedback off pages 0
prompt spool getavgcol
REM
column select_line format A8
column end_line format A1
column from_stmt format A34 word_wrap
column col_nm format A100
column col_val format A32
column tnm1 noprint
column tnmprint format A37
column column_id noprint
break on tnm1 skip 2
set null ''
clear breaks
select UTC.table_name tnm1,
        decode(column_id,1,'select ' || chr(39) || UTC.table_name || chr(39) ||
                ' TB, ', '        ') ||
        'round(avg(nvl(vsize('||column_name||'),0)),0)' ||
        decode(column_id,column_count, ' row_size from ' || UTC.table_name
             || ';'|| chr(10)||chr(10),
                ' +') col_nm
from user_tab_columns UTC, column_counts CC
where UTC.data_type not like 'LONG%' AND UTC.table_name = CC.table_name
order by UTC.table_name, UTC.column_id;
prompt spool off
prompt exit
spool off
drop table column_counts;
exit

posted on 2013-03-19 00:32  Oracle和MySQL  阅读(130)  评论(0编辑  收藏  举报

导航