升级用户创建-白红宇

--1. 创建源库迁移临时用户

CREATE USER migups IDENTIFIED BY &pw

DEFAULT TABLESPACE users

TEMPORARY TABLESPACE temp

QUOTA UNLIMITED ON users;

GRANT dba TO migups;

GRANT select ON sys.user$ TO migups;

GRANT RESTRICTED SESSION TO migups;

GRANT EXP_FULL_DATABASE TO migups;

GRANT EXECUTE ON SYS.DBMS_LOCK TO migups ;

GRANT SELECT ON SYS.LINK$ TO migups ;

--2. 创建源库迁移用户清单记录表

CREATE TABLE migups.s_user_list(

username VARCHAR2(30) primary key,

flag varchar2(1),

ggflag int,

src_tbs VARCHAR2(30),

dst_tbs VARCHAR2(30)

);

comment on column migups.s_user_list.FLAG

is 'y或Y表示要迁移，n或N表示不迁移';

comment on column migups.s_user_list.GGFLAG

is '1表示要配置在gg回滚链路里 0或空表示不配置在gg回滚链路里';

--在源库中migups用户下创建要迁移的表空间清单配置表

CREATE TABLE migups.s_tbs_list(

tbsname VARCHAR2(30)

);

--3. 创建源库Job备份清单表

create table migups.S_JOB_LIST

(

JOB NUMBER not null,

ISMIG VARCHAR2(1) not null,

SRC VARCHAR2(100) not null,

LOG_USER VARCHAR2(30),

PRIV_USER VARCHAR2(30),

SCHEMA_USER VARCHAR2(30),

LAST_DATE DATE,

LAST_SEC VARCHAR2(16),

THIS_DATE DATE,

THIS_SEC VARCHAR2(16),

NEXT_DATE DATE,

NEXT_SEC VARCHAR2(16),

TOTAL_TIME NUMBER,

BROKEN VARCHAR2(1),

INTERVAL VARCHAR2(200),

FAILURES NUMBER,

WHAT VARCHAR2(4000) not null,

NLS_ENV VARCHAR2(4000) ,

INSTANCE NUMBER,

resume_nextdate varchar2(20)

);

comment on column migups.S_JOB_LIST.ISMIG

is '是否迁移 0:是目标库初始化时创建的不能迁移或删除 1:是 2:否 3:其它(未定)';

comment on column migups.S_JOB_LIST.SRC

is 'job来源, 如：SRC_10G:来自10g源库 TGT_11G:来自11g目标库即11g目标库初始化时创建的';

comment on column migups.S_JOB_LIST.resume_nextdate

is '恢复Job时候，设置下次运行的时间' ;

---4. 在源库部署数据校验对比的table及procedure

create table dba_owner.TAB_HASH

(

OWNER VARCHAR2(30),

TABLE_NAME VARCHAR2(50),

SQL_VALUE VARCHAR2(4000),

HASH_VALUE NUMBER(20)

) ;

CREATE OR REPLACE PROCEDURE dba_owner.GET_TAB_HASH8(V_OWNER VARCHAR2,

V_TABLE_NAME VARCHAR2)

v_sql_temp varchar2(4000);

v_hash number(20);

BEGIN

FOR r in (SELECT COLUMN_NAME FROM DBA_TAB_COLUMNS WHERE OWNER = V_OWNER AND TABLE_NAME = V_TABLE_NAME

AND DATA_TYPE NOT IN ('CLOB', 'BLOB')order by 1 asc) loopv_sql_temp:= v_sql_temp || '||' || R.COLUMN_NAME;

end loop;

v_sql_temp:= 'select/+ parallel(t 8) full(t)/ sum(dbms_utility.get_hash_value(' || substr(v_sql_temp,3) || ', 0, 999999999)) from '

|| V_OWNER || '.' || V_TABLE_NAME || ' t';

execute immediate v_sql_temp into v_hash;

insert into tab_hash(owner, table_name, sql_value, hash_value) values (V_OWNER, V_TABLE_NAME, v_sql_temp || ';', v_hash);

commit;

EXCEPTION

WHEN OTHERS THEN

NULL;

END;

----------------------------------------------------------2

--升级不同的库时，此脚本有个地方需要本地化，搜索“需要修改的地方”

conn / as sysdba

accept pswd prompt 'Input the migupd password: ' hide

SET termout on feedback on verify OFF echo on heading OFF trims ON escape ON pages 0 linesize 999 long 3000

--在新库(目标库)操作系统创建迁移升级使用的目录

host mkdir -p $HOME/$ORACLE_SID/migup/pre

spool $HOME/$ORACLE_SID/migup/pre/pre_deal_oneday.log

--复核新库(目标库)是否使用的是ASM盘上的参数文件，正确结果为no rows selected

select name,value from v$parameter v where name='spfile' and v.VALUE not like '+%spfile%';

--按要求设置11G相关参数

alter system set job_queue_processes=0 scope=both;

alter system set aq_tm_processes =0 scope=both;

alter system set audit_trail='NONE' scope=spfile;

alter system set recyclebin='OFF' scope=spfile;

alter system set deferred_segment_creation=FALSE scope=both;

alter system set optimizer_use_sql_plan_baselines=TRUE scope=both ;

alter system set "_serial_direct_read"=never scope=both ;

alter system set "_use_adaptive_log_file_sync"=false scope=both ;

--按要求设置12c相关参数 ---需要修改的地方：升级11G的请注释此处的参数-----

alter system set audit_sys_operations=false scope=spfile;

alter system set temp_undo_enabled=true scope=spfile;

alter system set parallel_min_servers =0 scope=both;

alter system reset sec_case_sensitive_logon scope=spfile;

alter system set "_optimizer_dsdir_usage_control"=0 scope=spfile;

alter system set "_sql_plan_directive_mgmt_control="=0 scope=spfile ;

alter system set "_use_single_log_writer"=true scope=spfile ;

--复核目标库要求设置的参数是否正确设置，正确结果为no rows selected

select name, value

from v$parameter v

where (v.NAME = 'job_queue_processes' and v.VALUE <> '0')

or (v.NAME = 'aq_tm_processes' and v.VALUE <> '0')or (v.NAME = 'audit_trail' and upper(v.VALUE) <> 'NONE')or (v.NAME = 'recyclebin' and upper(v.VALUE) <> 'OFF')or (v.NAME = 'deferred_segment_creation' and upper(v.VALUE) <> 'FALSE')or (v.NAME = 'optimizer_use_sql_plan_baselines' and upper(v.VALUE) <> 'TRUE')or (v.NAME = 'audit_sys_operations' and upper(v.VALUE) <> 'FALSE')or (v.NAME = 'temp_undo_enabled' and upper(v.VALUE) <> 'TRUE')    or (v.NAME = 'parallel_min_servers' and upper(v.VALUE) <> '0')    or (v.NAME = 'sec_case_sensitive_logon' and upper(v.VALUE) <> 'TRUE')or (v.NAME = 'fixed_date' and v.VALUE is not null);

--在新库(目标库)中migupd用户下创建要迁移的用户清单配置表

CREATE TABLE migupd.t_user_list(

username VARCHAR2(30) primary key,

flag varchar2(1),

ggflag int,

src_tbs VARCHAR2(30),

dst_tbs VARCHAR2(30)

);

comment on column MIGUPD.T_USER_LIST.FLAG

is 'y或Y表示要迁移，n或N表示不迁移,S或者s代表目标库已经初始化创建的不需要迁移的用户';

comment on column MIGUPD.T_USER_LIST.GGFLAG

is '1表示要配置在gg回滚链路里 0或空表示不配置在gg回滚链路里';

--在新库(目标库)中migupd用户下创建要迁移的表空间清单配置表

CREATE TABLE migupd.t_tbs_list(

tbsname VARCHAR2(30)

);

--需要修改的地方------个性化定制----

--初始化新库(目标库)中migupd用户下的待迁移表空间清单配置表特别注意：升级不同的库要修改以下初始化语句

insert into migupd.t_tbs_list

select distinct tablespace_name from dba_data_files@dblink_migup b

where b.tablespace_name not in

(select tablespace_name from dba_tablespaces@dblink_migup a

where (a.tablespace_name in ('TEMP','SYSAUX','SYSTEM','USERS','其他不迁移表空间' ) or a.contents in ('TEMPORARY','UNDO'))

) ;

--初始化新库(目标库)中migupd用户下的待迁移用户清单配置表特别注意：升级不同的库要修改以下初始化语句

--注意:待迁移表空间上对象的属主用户都在待迁移用户清单中;待迁移用户的对象都在待迁移表空间中.否则要提前安排整改和特殊处理步骤

INSERT INTO migupd.t_user_list

(username, flag ,ggflag,src_tbs, dst_tbs)

SELECT username, 'Y', 1, default_tablespace, default_tablespace

FROM dba_users@dblink_migup

WHERE username not in (select username from migupd.t_user_list)

and username not in    `      ('SYS', 'SYSTEM', 'PUBLIC', 'OUTLN', 'WMSYS', 'ORDSYS', 'MDSYS','CTXSYS', 'XDB', 'WKSYS',       'ANONYMOUS', 'ORDPLUGINS', 'EXFSYS', 'LBACSYS' ,'DIP', 'LBACSYS', 'ORACLE_OCM', 'TSMSYS' ,       'SCOTT', 'JONES', 'CLARK', 'BLAKE', 'ADAMS', 'TRACESVR','DBSNMP','SYSMAN','MGMT_VIEW','DMSYS') and username not in ('FGLPA','PERFSTAT','SPOTLIGHT','I3_ORCL','FOGLIGHT','DBSTATS','DBAQUERY','MIGUPS',                      'GGMGR','GGBAK', 'GGDEPLOY','RSMSMONOPR', 'EXPFULL', 'DBCONN', 'TOAD',                      'DBQINTF'/*,'DBMON','OVSEE','FACTUSERMGR', 'DEPLOYOP','DBMONOPR' , 'DBMONOPR02'*/);

commit ;

----以下用户不需要迁移到目标库,但为使迁移过程不报错,仍然将相关的对象迁移到新库后再清理掉

INSERT INTO migupd.t_user_list

(username, flag ,ggflag,src_tbs, dst_tbs)

SELECT username, 'N', 0, default_tablespace, default_tablespace

FROM dba_users@dblink_migup

WHERE username in ('SPOTLIGHT','GGMGR')

;

commit ;

INSERT INTO migupd.t_user_list

(username, flag ,ggflag,src_tbs, dst_tbs)

SELECT username, 'S', 0, default_tablespace, default_tablespace

FROM dba_users

WHERE username not in (select username from migupd.t_user_list)

and username not in ('MIGUPD','GGBAK','FOGLIGHT') and username not in      ('SYS', 'SYSTEM', 'PUBLIC', 'OUTLN', 'WMSYS', 'ORDSYS', 'MDSYS','CTXSYS', 'XDB', 'WKSYS',       'ANONYMOUS', 'ORDPLUGINS', 'EXFSYS', 'LBACSYS' ,'DIP', 'LBACSYS', 'ORACLE_OCM', 'TSMSYS' ,       'SCOTT', 'JONES', 'CLARK', 'BLAKE', 'ADAMS', 'TRACESVR','DBSNMP','APPQOSSYS')

;

commit ;

--标志要配置在GG回滚链路里的用户

--实名用户不在GG回滚链路中配置

update migupd.t_user_list set ggflag=0 where username in( select oa_name from dba_owner.factuser_list@dblink_migup );

update migupd.t_user_list set ggflag=0 where username in( 'FACTUSERMGR','DBMONOPR','DBMONOPR_JZZC','DBQINTF','DBACOMM','DEPLOYOP','DBQUA','DBMONOPR02','dba_owner');

--整库升级或整库迁移升级时，不改变数据库架构，所以用户的表空间不变

--UPDATE migupd.t_user_list t SET t.dst_tbs = 'USERS' WHERE src_tbs IN ('xxx','xxx');

--UPDATE migupd.t_user_list t set t.dst_tbs = 'yyy' where t.username = 'yyy' and t.src_tbs ='ddd' ;

commit;

-- XTTS检查是否有业务对象都在迁移表空间上,业务对象在迁移表空间范围外的需要提前处理,正确结果为no rows selected

select distinct a.owner, a.segment_name , a.partition_name , a.segment_type , a.tablespace_name from dba_segments@dblink_migup a

where a.tablespace_name not in (select tbsname from migupd.t_tbs_list )

and a.owner in (select username  from  migupd.t_user_list where flag ='Y');

-- XTTS检查迁移表空间上的对象属主是否都在迁移用户清单中. 否则需要提前处理迁移到其他表空间或者加入迁移用户清单,正确结果为no rows selected

select distinct a.owner, a.segment_name , a.partition_name , a.segment_type , a.tablespace_name from dba_segments@dblink_migup a

where a.tablespace_name in (select tbsname from migupd.t_tbs_list )

and a.owner not in (select username  from  migupd.t_user_list where flag ='Y');

--在新库(目标库)中migupd用户下创建job清单表--

create table migupd.T_JOB_LIST(

JOB NUMBER not null,

ISMIG VARCHAR2(1) not null,

SRC VARCHAR2(100) not null,

LOG_USER VARCHAR2(30),

PRIV_USER VARCHAR2(30),

SCHEMA_USER VARCHAR2(30),

LAST_DATE DATE,

LAST_SEC VARCHAR2(16),

THIS_DATE DATE,

THIS_SEC VARCHAR2(16),

NEXT_DATE DATE,

NEXT_SEC VARCHAR2(16),

TOTAL_TIME NUMBER,

BROKEN VARCHAR2(1),

INTERVAL VARCHAR2(200),

FAILURES NUMBER,

WHAT VARCHAR2(4000) not null,

NLS_ENV VARCHAR2(4000) ,

INSTANCE NUMBER,

resume_nextdate varchar2(20)

);

comment on column migupd.T_JOB_LIST.ISMIG

is '是否迁移 0:是新库初始化时创建的不能迁移或删除 1:是 2:否 3:其它(未定)';

comment on column migupd.T_JOB_LIST.SRC

is 'job来源 OLD:来自旧库 NEW:来自新库(目标库)即新库初始化时创建的';

comment on column migupd.T_JOB_LIST.resume_nextdate

is '恢复Job时候，设置下次运行的时间' ;

SET termout OFF feedback OFF verify OFF echo OFF heading OFF trims ON escape ON pages 0 linesize 999 long 3000

spool $HOME/$ORACLE_SID/migup/pre/01_11g_deal_tablespaces.sql

-----需要修改的地方:调整目标库已有表空间的大小, 如Users表空间、临时表空间、Undo表空间

select 'alter database tempfile '||''''||df.file_name||''''||' resize 20000M ;' from dba_temp_files df where df.tablespace_name= 'TEMP' ;

select 'alter tablespace TEMP add tempfile '''||substr(file_name, 1, instr(file_name, '/') - 1)||''' size 20000M autoextend on;'

from dba_temp_files where rownum <=2 ;

select 'alter database datafile '||''''||df.file_name||''''||' resize 20000M ;' from dba_data_files df where df.autoextensible='NO' and df.tablespace_name='UNDOTBS1' ;

select 'alter tablespace UNDOTBS1 add datafile '''||substr(file_name, 1, instr(file_name, '/') - 1)||''' size 20000M autoextend on;'

from dba_data_files where rownum <=3 ;

spool off;

commit;

SET termout on feedback on verify OFF echo on heading OFF trims ON escape ON pages 0 linesize 999 long 3000

spool $HOME/$ORACLE_SID/migup/pre/pre_deal_oneday.log append

@$HOME/$ORACLE_SID/migup/pre/01_11g_deal_tablespaces.sql

--在新库(目标库)中创建迁移角色

spool off;

SET termout OFF feedback OFF verify OFF echo OFF heading OFF trims ON escape ON pages 0 linesize 999 long 3000

spool $HOME/$ORACLE_SID/migup/pre/01_11g_deal_role.sql

select 'create role ' || role || ';'

from (select role

from dba_roles@dblink_migup  where role not in('MGMT_USER','CTXAPP','LBAC_DBA')    minus    select role from dba_roles);

spool off;

commit;

SET termout on feedback on verify OFF echo on heading OFF trims ON escape ON pages 0 linesize 999 long 3000

spool $HOME/$ORACLE_SID/migup/pre/pre_deal_oneday.log append

--使用migupd临时用户来创建role，避免sys角色太多出问题

conn migupd/&pswd

@$HOME/$ORACLE_SID/migup/pre/01_11g_deal_role.sql

--切换回sys用户继续执行

conn / as sysdba

--设置migupd临时用户的default role，避免登录报错ORA-28031: maximum of 148 enabled roles exceeded----

ALTER USER MIGUPD DEFAULT ROLE DBA;

--复核migupd临时用户的default role个数为1

select count(1) default_role_cnt from dba_role_privs rl where rl.grantee = 'MIGUPD' and rl.default_role = 'YES' ;

--复核新库(目标库)中角色是否创建完整，正确结果为no rows selected

select role

from dba_roles@dblink_migup where role not in ('MGMT_USER','CTXAPP','LBAC_DBA')

minus

select role from dba_roles;

commit;

--在新库(目标库)中创建迁移profile并复核

spool off;

SET termout OFF feedback OFF verify OFF echo OFF heading OFF trims ON escape ON pages 0 linesize 999 long 3000

spool $HOME/$ORACLE_SID/migup/pre/01_11g_deal_profile.sql

select (case

when rn = 1 then       ('create profile ' || profile ||' limit ' || resource_name ||' ' || limit || (case      when rn = max_rn then       ';'    end)) else(resource_name || ' ' || limit || (case     when rn = max_rn then      ';'   end)) end)

from (select profile,

resource_name,           limit,           row_number() over(partition by profile order by profile, resource_name) rn,           count(*) over(partition by profile) max_rn      from dba_profiles@dblink_migup     where profile in           (select distinct profile              from dba_profiles@dblink_migup            minus            select distinct profile from dba_profiles)     order by profile, resource_name)

order by profile, resource_name;

spool off;

commit;

SET termout on feedback on verify OFF echo on heading OFF trims ON escape ON pages 0 linesize 999 long 3000

spool $HOME/$ORACLE_SID/migup/pre/pre_deal_oneday.log append

@$HOME/$ORACLE_SID/migup/pre/01_11g_deal_profile.sql

--复核新库(目标库)中profile是否创建完整，正确结果为no rows selected

select distinct profile

from dba_profiles@dblink_migup

minus

select distinct profile from dba_profiles;

commit;

--在新库(目标库)中创建要迁移的context并复核

spool off;

SET termout OFF feedback OFF verify OFF echo OFF heading OFF trims ON escape ON pages 0 linesize 999 long 3000

spool $HOME/$ORACLE_SID/migup/pre/01_tgt_deal_context.sql

select 'create or replace context ' || c.namespace || ' using ' ||

c.schema || '.' || c.package || ' ' || case       when c.type like '% LOCALLY%' then        ';'       else        c.type || ';'     end Create_contextfrom dba_context@dblink_migup c

where c.schema in (select username

from migupd.t_user_list t                   where flag = 'Y'  or flag is null) and c.namespace not in (select namespace from dba_context);

SPOOL OFF ;

commit;

SET termout on feedback on verify OFF echo on heading OFF trims ON escape ON pages 0 linesize 999 long 3000

spool $HOME/$ORACLE_SID/migup/pre/pre_deal_oneday.log append

@$HOME/$ORACLE_SID/migup/pre/01_tgt_deal_context.sql

--复核新库(目标库)中context是否创建完整，正确结果为no rows selected

select namespace

from dba_context@dblink_migup

where schema in (select username

from migupd.t_user_list t               where flag = 'Y' or flag is null)

minus

select namespace

from dba_context

where schema in (select username

from migupd.t_user_list t               where flag = 'Y' or flag is null);

commit;

--记录新建空库中的初始用户

spool off;

SET termout on feedback on verify OFF echo on heading OFF trims ON escape ON pages 0 linesize 999 long 3000

spool $HOME/$ORACLE_SID/migup/pre/pre_deal_oneday.log append

select username from dba_users;

commit;

--查询并记录keep到内存中的对象

spool off;

SET termout on feedback on verify OFF echo on heading OFF trims ON escape ON pages 0 linesize 999 long 3000

spool $HOME/$ORACLE_SID/migup/pre/pre_deal_oneday.log append

select 'alter table '||owner||'.'||table_name||' storage(buffer_pool keep);' from dba_tables@dblink_migup where buffer_pool = 'KEEP';

select 'alter index '||owner||'.'||index_name||' storage(buffer_pool keep);' from dba_indexes@dblink_migup where buffer_pool = 'KEEP';

commit;

spool off

-----------------------------------------------------3

conn / as sysdba

SET termout on feedback on verify OFF echo on heading OFF trims ON escape ON pages 0 linesize 999 long 3000

--在新库(目标库)操作系统创建迁移升级使用的目录

host mkdir -p $HOME/$ORACLE_SID/migup/pre

spool $HOME/$ORACLE_SID/migup/pre/tgt_pre_xtts_deal_oneday_check.log

--复核目标库是否使用的是ASM盘上的参数文件，正确结果为no rows selected

select name,value from v$parameter v where name='spfile' and v.VALUE not like '+%spfile%';

--复核新库(目标库)是否为非归档模式

archive log list;

--复核新库(目标库)要求设置的参数是否正确设置，正确结果为no rows selected

select name, value

from v$parameter v

where (v.NAME = 'job_queue_processes' and v.VALUE <> '0')

or (v.NAME = 'aq_tm_processes' and v.VALUE <> '0')or (v.NAME = 'audit_trail' and upper(v.VALUE) <> 'NONE')or (v.NAME = 'recyclebin' and upper(v.VALUE) <> 'OFF')or (v.NAME = 'deferred_segment_creation' and upper(v.VALUE) <> 'FALSE')or (v.NAME = 'audit_sys_operations' and upper(v.VALUE) <> 'FALSE')or (v.NAME = 'temp_undo_enabled' and upper(v.VALUE) <> 'TRUE')    or (v.NAME = 'parallel_min_servers' and upper(v.VALUE) <> '0')    or (v.NAME = 'sec_case_sensitive_logon' and upper(v.VALUE) <> 'TRUE')or (v.NAME = 'fixed_date' and v.VALUE is not null);

--复核新库(目标库)中临时使用的迁移用户migupd是否已经创建，正确结果为MIGUPD

select username from dba_users where username in ('MIGUPD');

--复核新库(目标库)中临时使用的dblink是否已经创建，正确结果为dblink_migup

col owner for a32

col db_link for a32

col username for a32

col host for a32

col object_name for a32

select owner,db_link,username,host from dba_db_links where db_link ='DBLINK_MIGUP' and owner in ('MIGUPD', 'PUBLIC' );

--复核新库(目标库)中临时使用的表migupd.t_user_list,migupd.T_JOB_LIST是否已创建，正确结果这2张表都有

select owner,table_name,tablespace_name from dba_tables

where owner='MIGUPD' and table_name in('T_USER_LIST','T_JOB_LIST');

--复核新库(目标库)中临时表t_user_list,t_tbs_list是否已经初始化

select * from MIGUPD.T_USER_LIST order by 1;

select * from MIGUPD.T_tbs_LIST order by 1;

-- XTTS检查是否有业务对象都在迁移表空间上,业务对象在迁移表空间范围外的需要提前处理,正确结果为no rows selected

select distinct a.owner, a.segment_name , a.partition_name , a.segment_type , a.tablespace_name from dba_segments@dblink_migup a

where a.tablespace_name not in (select tbsname from migupd.t_tbs_list )

and a.owner in (select username  from  migupd.t_user_list where flag ='Y');

-- XTTS检查迁移表空间上的对象属主是否都在迁移用户清单中. 否则需要提前处理迁移到其他表空间或者加入迁移用户清单,正确结果为no rows selected

select distinct a.owner, a.segment_name , a.partition_name , a.segment_type , a.tablespace_name from dba_segments@dblink_migup a

where a.tablespace_name in (select tbsname from migupd.t_tbs_list )

and a.owner not in (select username  from  migupd.t_user_list where flag ='Y');

--复核新库(目标库)已经存在的临时表t_user_list清单中的用户---add by liuyong

select username , to_char( u.created,'yyyy-mm-dd hh24:mi:ss') created, u.default_tablespace from dba_users u where exists (

select 1 from migupd.t_user_list t where t.username = u.username and t.flag ='Y'

);

--复核新库(目标库)中角色是否创建完整，正确结果为no rows selected

select role

from dba_roles@dblink_migup where role not in ('MGMT_USER','CTXAPP','LBAC_DBA','XDBADMIN','WKUSER','AUTHENTICATEDUSER','GGS_GGSUSER_ROLE')

minus

select role from dba_roles;

commit;

--复核新库(目标库)中profile是否创建完整，正确结果为no rows selected

select distinct profile

from dba_profiles@dblink_migup

minus

select distinct profile from dba_profiles;

commit;

--复核新库(目标库)中context是否创建完整，正确结果为no rows selected

select namespace

from dba_context@dblink_migup

where schema in (select username

from migupd.t_user_list t               where flag = 'Y'                  or flag is null)

minus

select namespace

from dba_context

where schema in (select username

from migupd.t_user_list t               where flag = 'Y'                  or flag is null);

spool off;

commit;