select 'ALTER TABLE '||OWNER||'.'||TABLE_NAME||' MODIFY('||COLUMN_NAME||' NVARCHAR2('||DATA_LENGTH||'));' AS GEN_SQL_STATEMENTfrom SYS.DBA_tab_columnswhere owner = 'ownername' and DATA_TYPE = 'VARCHAR2';
The result is the rows of ALTER statement filled with the table name, column name and new data type.
Then I copied all rows and concatenate them with some editor. and then run them as a sql script.If you want to convert from NVARCHAR2 to VARCHAR2, just switch 'VARCHAR2' and NVARCHAR2' in above sql statement. You can modify above script as well to suit your propose.Finally, you cannot convert the column which is not empty otherwise the error will occur.