Script: Computing Table Size Abstract This script calculates the average row size for all tables in a schema, using the VSIZEfunction of SQL Product Name, Product Version Oracle Server, 7.3.x to 9.x Platform Platform Independent Date Created 28-Jul-. 2002 Instructions Execution Environment:
Access Privileges: The Scripts Needs to Be Executed with the user who oowns the table to be analyzed.
USAGE: SQLPlus
INSTRUCTIONS: COPY THE Script Into a file named Rowsz. Execute the script from sql * plus connection as the user who onees the tables to be analyzed.
PROOFREAD THIS SCRIPT BEFORE USING IT! Due to differences in the way text editors, e-mail packages, and operating systems handle text formatting (spaces, tabs, and carriage returns), this script may not be in an executable statewhen you first receive it . Check over the script to ensure that errors ofthis type are corrected. The script will produce an output file named getavgcol.sql.This needs to be executed to obtain a report of the average size of the tables in the schema.
. Description 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 (VSIZE (COL2), 0)) ... ROUND (AVG (VSIZE (Coln), 0)) where n = # of cols. On The Table
Tables with long and lob columns Will NOT REPORT ROW SIZE PROPERLY. Alsotables with Object Types Will Throw The Following Error and Will Also Not Report Row Size Properly:
ORA-00932: InconsiStent Dattypes ================================================================================================================================================= # 36 Company_Summary 60 CR_Files 113
Script set echo off remnime: Rowsz.sql Rem usage: "@ rowsz" remote ------------------------------------------------------------------------------------------------------------------------------------------------------------------ ------------------------------------------ Rem Requirements: Rem shop bern Under The Schema ID of the Tables Being Reported. Rem Tables with long columns Will Not Report Row Size Properly !! Rem --------------------------- ---------------------------------------------- Rem purpose: . REM Calculate the average row size for all tables in a schema REM It generates a script (getavgcol.sql) off of USER_TABLES and then runs REM it The following type of SELECT is generated for table in USER_TABLES:. REM SELECT round (avg ( NVL (VSIZE (COL1), 0)) Rem Round (AVG (VSIZE (COL2), 0)) ... Rem Round (AVG (NVL (VSIZE (Coln), 0)) Rem Where n = # of cols. on The Table Rem Rem INPUT: NONE Rem Output: GetAvgcol.sql - Script That Is Generated and Run Rem GetAvgcol.lst - Report of Each Table and ITS REM average row length REM REM DISCLAIMER: REM This script is provided for educational purposes only It is NOT REM supported by Oracle World Wide Technical Support REM The script has been tested and appears to work as intended REM You should always run new scripts on... A Test Instance Initially. Rem -------------------------------------------- ---------------------------- Rem Main Text of Script Follows: 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 (VSI ('|| 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
Disclaimer EXCEPT WHERE EXPRESSLY PROVIDED OTHERWISE, THE INFORMATION, SOFTWARE, PROVIDED ON AN "AS IS" AND "AS AVAILABLE" BASIS. ORACLE EXPRESSLY DISCLAIMSALL WARRANTIES OF ANY KIND, WHETHER EXPRESS OR IMPLIED, INCLUDING, BUT NOTLIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY , FITNESS FOR A PARTICULARPURPOSE AND NON-INFRINGEMENT ORACLE MAKES NO WARRANTY THAT:. (A) THE RESULTSTHAT MAY BE OBTAINED FROM THE USE OF THE SOFTWARE WILL BE ACCURATE ORRELIABLE; OR (B) THE INFORMATION, OR OTHER MATERIAL OBTAINED WILL MEET YOUREXPECTATIONS. ANY CONTENT, MATERIALS, INFORMATION OR SOFTWARE DOWNLOADED OROTHERWISE OBTAINED IS DONE AT YOUR OWN DISCRETION AND RISK. ORACLE SHALL HAVENO RESPONSIBILITY FOR ANY DAMAGE TO YOUR COMPUTER SYSTEM OR LOSS OF DATA THATRESULTS FROM THE DOWNLOAD OF ANY CONTENT, MATERIALS, INFORMATION OR SOFTWARE.ORACLE Reserves the right to make changes or updates to the software at anytime without notice.
Limitation of Liability IN NO EVENT SHALL ORACLE BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL OR CONSEQUENTIAL DAMAGES, OR DAMAGES FOR LOSS OF PROFITS, REVENUE, DATA OR USE, INCURRED BY YOU OR ANY THIRD PARTY, WHETHER IN AN ACTION INCONTRACT OR Tort, Arising from your access to, or us, the software.
Some Jurisdiction or Exclusion of Liability.accordingly, Some of the Above Limitations May Not Apply to you.
