declare
ihave integer;
owname varchar2(50);
tblname varchar(200);
cursor owner_cursor is
select username
from dba_users
where user_id = 91
/* where user_id > 93
and user_id < 150*/
order by user_id;
cursor tbl_cursor is
select table_name
from dba_tables
where owner = owname
and (table_name not like 'DM_%'
and table_name not like 'BM%'
and table_name not like 'SYS_DIC%'
and table_name not like 'DICT_%'
and table_name not like 'SYSTEM_CODE_NODE'
and table_name not like 'MV_%'
and table_name not like 'T_BZDM_%'
and table_name not like 'DD_%'
);
begin
open owner_cursor;
fetch owner_cursor into owname;
while owner_cursor%found
loop
dbms_output.put_line(owname);
open tbl_cursor;
fetch tbl_cursor into tblname;
while tbl_cursor%found
loop
--dbms_output.put_line(owname || '-' || tblname);
select count(*) into ihave
from dba_tab_columns
where owner = owname and table_name = upper(tblname) and column_name = 'IMPDATE';
if ihave = 0 then
execute immediate 'alter table '|| owname || '.' || tblname || ' add impdate timestamp(6) default sysdate null';
/* dbms_output.put_line(owname || '-' || tblname);
else
execute immediate 'alter table '|| owname || '.' || tblname || ' drop column impdate '; */
-- dbms_output.put_line(owname || '-' || tblname);
end if;
fetch tbl_cursor into tblname;
end loop;
close tbl_cursor;
fetch owner_cursor into owname;
dbms_output.put_line('');
end loop;
close owner_cursor;
end;
--select * from dba_users where user_id > 90 order by user_id;