Suppose, there is a column named "USERNAME" in some tables with varying length size 12 bytes to 50 bytes. You need to change the length of less than 40 bytes in all tables to 40 bytes only for the column "USERNAME"
-------- Query to find tables whose length is less than 40 bytes-----------------------------------------
select owner, table_name, column_name, data_type, data_length from dba_tab_columns
where owner not in ('SYSMAN', 'OLAPSYS', 'SYSTEM', 'SYS') ---- this line to exclude system tables
and column_name='USERNAME'
and data_length<40
and table_name in (select table_name from dba_tables) ---- this line to include only tables, not view
--------- Prepare a script to revert back to original length------------------------------------------------
select 'ALTER TABLE '||owner||'.'||table_name||' MODIFY(USERNAME VARCHAR2('||data_length||' BYTE));' from dba_tab_columns
where owner not in ('SYSMAN', 'OLAPSYS', 'SYSTEM', 'SYS')
and column_name='USERNAME'
and data_length<40
and table_name in (select table_name from dba_tables)
--------- Prepare a script to change the length to 40 Bytes-------------------------------------------------
select 'ALTER TABLE '||owner||'.'||table_name||' MODIFY(USERNAME VARCHAR2(40 BYTE));' from dba_tab_columns
where owner not in ('SYSMAN', 'OLAPSYS', 'SYSTEM', 'SYS')
and column_name='USERNAME'
and data_length<40
and table_name in (select table_name from dba_tables)
No comments:
Post a Comment