CREATE USER MIGUPS
IDENTIFIED BY MKCI_eCI39_C
default tablespace DBADATA TEMPORARY TABLESPACE TEMP
PROFILE DEFAULT
ACCOUNT UNLOCK;
-- 142 Roles for MIGUPS
grant select on sys.user$ to MIGUPS WITH GRANT OPTION;
GRANT SELECT_CATALOG_ROLE TO MIGUPS;
GRANT DBA TO MIGUPS;
GRANT EXP_FULL_DATABASE TO MIGUPS WITH ADMIN OPTION;
ALTER USER MIGUPS DEFAULT ROLE SELECT_CATALOG_ROLE, DBA ;
-- 58 System Privileges for MIGUPS
GRANT DEBUG ANY PROCEDURE TO MIGUPS;
GRANT ADMINISTER DATABASE TRIGGER TO MIGUPS;
GRANT ALTER ANY DIMENSION TO MIGUPS;
GRANT CREATE ANY TRIGGER TO MIGUPS;
GRANT CREATE ANY SYNONYM TO MIGUPS;
GRANT DROP ANY INDEX TO MIGUPS;
GRANT DROP USER TO MIGUPS;
GRANT ALTER SESSION TO MIGUPS;
GRANT CREATE SESSION TO MIGUPS;
GRANT DROP ANY OUTLINE TO MIGUPS;
GRANT ALTER ANY INDEXTYPE TO MIGUPS;
GRANT ALTER ANY TYPE TO MIGUPS;
GRANT CREATE PROCEDURE TO MIGUPS;
GRANT CREATE ROLE TO MIGUPS;
GRANT DROP ANY TABLE TO MIGUPS;
GRANT CREATE TABLE TO MIGUPS;
GRANT MANAGE SCHEDULER TO MIGUPS;
GRANT ALTER ANY LIBRARY TO MIGUPS;
GRANT ALTER ANY MATERIALIZED VIEW TO MIGUPS;
GRANT ALTER ANY SEQUENCE TO MIGUPS;
GRANT CREATE SEQUENCE TO MIGUPS;
GRANT CREATE VIEW TO MIGUPS;
GRANT UPDATE ANY TABLE TO MIGUPS;
GRANT GRANT ANY OBJECT PRIVILEGE TO MIGUPS;
GRANT SELECT ANY DICTIONARY TO MIGUPS;
GRANT ANALYZE ANY TO MIGUPS;
GRANT ALTER ANY OUTLINE TO MIGUPS;
GRANT CREATE MATERIALIZED VIEW TO MIGUPS;
GRANT CREATE PROFILE TO MIGUPS;
GRANT ALTER ANY CLUSTER TO MIGUPS;
GRANT SELECT ANY TABLE TO MIGUPS;
GRANT UNLIMITED TABLESPACE TO MIGUPS;
GRANT AUDIT SYSTEM TO MIGUPS;
GRANT CREATE ANY OUTLINE TO MIGUPS;
GRANT ALTER PROFILE TO MIGUPS;
GRANT CREATE ANY INDEX TO MIGUPS;
GRANT DELETE ANY TABLE TO MIGUPS;
GRANT COMMENT ANY TABLE TO MIGUPS;
GRANT ALTER ANY TABLE TO MIGUPS;
GRANT ALTER USER TO MIGUPS;
GRANT CREATE TABLESPACE TO MIGUPS;
GRANT RESTRICTED SESSION TO MIGUPS;
GRANT DROP ANY MATERIALIZED VIEW TO MIGUPS;
GRANT CREATE ANY MATERIALIZED VIEW TO MIGUPS;
GRANT AUDIT ANY TO MIGUPS;
GRANT CREATE PUBLIC SYNONYM TO MIGUPS;
GRANT INSERT ANY TABLE TO MIGUPS;
GRANT CREATE ANY TABLE TO MIGUPS;
GRANT ALTER SYSTEM TO MIGUPS;
GRANT ADMINISTER SQL MANAGEMENT OBJECT TO MIGUPS;
GRANT ANALYZE ANY DICTIONARY TO MIGUPS;
GRANT ALTER ANY TRIGGER TO MIGUPS;
GRANT ALTER ANY PROCEDURE TO MIGUPS;
GRANT DROP PUBLIC SYNONYM TO MIGUPS;
GRANT ALTER ANY INDEX TO MIGUPS;
GRANT LOCK ANY TABLE TO MIGUPS;
GRANT CREATE USER TO MIGUPS;
GRANT ALTER TABLESPACE TO MIGUPS;
-- 3 Tablespace Quotas for MIGUPS
ALTER USER MIGUPS QUOTA UNLIMITED ON DBADATA;
ALTER USER MIGUPS QUOTA UNLIMITED ON DMLBAKDATA;
ALTER USER MIGUPS QUOTA UNLIMITED ON USERS;
-- 141 Object Privileges for MIGUPS
GRANT DELETE, INSERT, SELECT, UPDATE ON OUTLN.OL$ TO MIGUPS;
GRANT SELECT ON SYS.ALL_INDEXES TO MIGUPS WITH GRANT OPTION;
GRANT SELECT ON SYS.ALL_PART_INDEXES TO MIGUPS WITH GRANT OPTION;
GRANT SELECT ON SYS.ALL_PART_TABLES TO MIGUPS WITH GRANT OPTION;
GRANT SELECT ON SYS.ALL_TAB_MODIFICATIONS TO MIGUPS;
GRANT DELETE ON SYS.AUD$ TO MIGUPS;
GRANT SELECT ON SYS.DBA_DATA_FILES TO MIGUPS WITH GRANT OPTION;
GRANT SELECT ON SYS.DBA_EXTENTS TO MIGUPS WITH GRANT OPTION;
GRANT SELECT ON SYS.DBA_INDEXES TO MIGUPS;
GRANT SELECT ON SYS.DBA_JOBS TO MIGUPS WITH GRANT OPTION;
GRANT SELECT ON SYS.DBA_JOBS_RUNNING TO MIGUPS WITH GRANT OPTION;
GRANT SELECT ON SYS.DBA_OBJECTS TO MIGUPS WITH GRANT OPTION;
GRANT SELECT ON SYS.DBA_PROFILES TO MIGUPS;
GRANT SELECT ON SYS.DBA_ROLES TO MIGUPS;
GRANT SELECT ON SYS.DBA_SERVICES TO MIGUPS;
GRANT SELECT ON SYS.DBA_SYS_PRIVS TO MIGUPS;
GRANT SELECT ON SYS.DBA_TABLES TO MIGUPS;
GRANT SELECT ON SYS.DBA_TAB_MODIFICATIONS TO MIGUPS;
GRANT SELECT ON SYS.DBA_TAB_PARTITIONS TO MIGUPS WITH GRANT OPTION;
GRANT SELECT ON SYS.DBA_TAB_PRIVS TO MIGUPS;
GRANT SELECT ON SYS.DBA_TAB_SUBPARTITIONS TO MIGUPS WITH GRANT OPTION;
GRANT SELECT ON SYS.DBA_USERS TO MIGUPS;
GRANT EXECUTE ON SYS.DBMS_AUDIT_MGMT TO MIGUPS;
GRANT EXECUTE ON SYS.DBMS_CRYPTO TO MIGUPS;
GRANT EXECUTE ON SYS.DBMS_IJOB TO MIGUPS;
GRANT EXECUTE ON SYS.DBMS_JOB TO MIGUPS WITH GRANT OPTION;
GRANT EXECUTE ON SYS.DBMS_LOCK TO MIGUPS;
GRANT EXECUTE ON SYS.DBMS_LOGMNR TO MIGUPS;
GRANT EXECUTE ON SYS.DBMS_LOGMNR_D TO MIGUPS;
GRANT EXECUTE ON SYS.DBMS_METADATA TO MIGUPS;
GRANT EXECUTE ON SYS.DBMS_REDEFINITION TO MIGUPS;
GRANT EXECUTE ON SYS.DBMS_REPAIR TO MIGUPS;
GRANT EXECUTE ON SYS.DBMS_SERVICE TO MIGUPS;
GRANT EXECUTE ON SYS.DBMS_SHARED_POOL TO MIGUPS;
GRANT EXECUTE ON SYS.DBMS_SPACE_ADMIN TO MIGUPS;
GRANT EXECUTE ON SYS.DBMS_STATS TO MIGUPS;
GRANT EXECUTE ON SYS.DBMS_SYSTEM TO MIGUPS;
GRANT SELECT ON SYS.GV_$INSTANCE TO MIGUPS WITH GRANT OPTION;
GRANT SELECT ON SYS.INDEX_STATS TO MIGUPS;
GRANT SELECT ON SYS.LINK$ TO MIGUPS;
GRANT EXECUTE ON SYS.PA_ALTER_SYNONYM TO MIGUPS;
GRANT DELETE, INSERT, SELECT, UPDATE ON SYS.PLAN_TABLE TO MIGUPS;
GRANT SELECT ON SYS.SEG$ TO MIGUPS WITH GRANT OPTION;
GRANT SELECT ON SYS.TS$ TO MIGUPS WITH GRANT OPTION;
GRANT SELECT ON SYS.V_$ACTIVE_SERVICES TO MIGUPS;
GRANT DELETE, INSERT, REFERENCES, SELECT, UPDATE, ON COMMIT REFRESH, QUERY REWRITE, DEBUG, FLASHBACK ON SYS.V_$ARCHIVED_LOG TO MIGUPS;
GRANT SELECT ON SYS.V_$ARCHIVE_PROCESSES TO MIGUPS;
GRANT SELECT ON SYS.V_$DATABASE TO MIGUPS WITH GRANT OPTION;
GRANT SELECT ON SYS.V_$INSTANCE TO MIGUPS;
GRANT SELECT ON SYS.V_$LOCK TO MIGUPS;
GRANT SELECT ON SYS.V_$LOCKED_OBJECT TO MIGUPS;
GRANT SELECT ON SYS.V_$LOG TO MIGUPS;
GRANT SELECT ON SYS.V_$MYSTAT TO MIGUPS WITH GRANT OPTION;
GRANT SELECT ON SYS.V_$PARAMETER TO MIGUPS;
GRANT SELECT ON SYS.V_$PROCESS TO MIGUPS;
GRANT SELECT ON SYS.V_$SESSION TO MIGUPS WITH GRANT OPTION;
GRANT SELECT ON SYS.V_$SESSION_WAIT TO MIGUPS;
GRANT SELECT ON SYS.V_$SESSTAT TO MIGUPS;
GRANT SELECT ON SYS.V_$SQL TO MIGUPS;
GRANT SELECT ON SYS.V_$SQLAREA TO MIGUPS;
GRANT SELECT ON SYS.V_$SQLTEXT TO MIGUPS;
GRANT SELECT ON SYS.V_$SQLTEXT_WITH_NEWLINES TO MIGUPS;
GRANT SELECT ON SYS.V_$STATNAME TO MIGUPS;
GRANT SELECT ON SYS.V_$TRANSACTION TO MIGUPS;
GRANT SELECT ON SYS.X_$BH TO MIGUPS;
---grant directory
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 ;
CREATE TABLE migups.s_user_list(
username VARCHAR2(30) primary key,
flag varchar2(1),
ggflag int,
src_tbs VARCHAR2(30),
dst_tbs VARCHAR2(30)
);
CREATE TABLE migups.s_tbs_list(
tbsname VARCHAR2(30)
);
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)
);
create table migups.TAB_HASH
(
OWNER VARCHAR2(30),
TABLE_NAME VARCHAR2(50),
SQL_VALUE VARCHAR2(4000),
HASH_VALUE NUMBER(20)
) ;
CREATE OR REPLACE PROCEDURE migups.GET_TAB_HASH8(V_OWNER VARCHAR2,
V_TABLE_NAME VARCHAR2)
AS
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) loop
v_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;
/
insert into migups.s_tbs_list
select distinct tablespace_name from dba_segments
where tablespace_name not in ('SYSTEM','SYSAUX','AUDDATA','BANKDATA','BANKIDX','BRCHREPT','I3_ORCL','PSSDATA','SWSDATA','VOULOGTMPDATA','VOULOGTMPIDX','WORKAREA') AND tablespace_name not LIKE 'UNDOTBS%';
commit;
INSERT INTO migups.s_user_list
select DISTINCT OWNER,'Y','1','USERS','USERS'
from dba_objects
where owner not in
('APPQOSSYS', 'AURORA$JIS$UTILITY$', 'OSE$HTTP$ADMIN',
'GSMADMIN_INTERNAL', 'AUDSYS', 'COW_HUQIHUI', 'LBACSYS', 'SYSTEM',
'XDB', 'SYS', 'SCOTT', 'COW_ZHAOQINGHAI001', 'WMSYS', 'OJVMSYS',
'GGMGR', 'COW_FANDI1', 'FOGLIGHT', 'OVSEE', 'TOAD', 'FGLPA', 'DBQUA',
'SPOTLIGHT', 'OUTLN', 'DBSNMP','ORACLE_OCM','PERFSTAT')
and owner not like 'COW_%'
order by 1;
COMMIT;
