oracle批量授权&维护索引&外键

--1 批量授权：
DECLARE
  V_GRANTOR_USER VARCHAR2(20) := 'USER1'; -- 原来
  V_GRANTEE_USER VARCHAR2(20) := 'USER2'; -- 被授予的用户
BEGIN
  FOR L IN (SELECT OBJECT_NAME
              FROM DBA_OBJECTS
             WHERE OWNER = V_GRANTOR_USER
               AND OBJECT_TYPE IN ('TABLE','VIEW')
            ) LOOP
    --EXECUTE IMMEDIATE 'GRANT SELECT,DELETE,UPDATE,INSERT ON ' || V_GRANTOR_USER || '.' || L.OBJECT_NAME || ' TO ' || V_GRANTEE_USER;
    EXECUTE IMMEDIATE 'CREATE PUBLIC SYNONYM ' || L.OBJECT_NAME || ' FOR ' || V_GRANTOR_USER || '.' || L.OBJECT_NAME;
  END LOOP;

  -- 具体存储过程，视图，包的授权
  FOR L IN (SELECT OBJECT_NAME
              FROM DBA_OBJECTS
             WHERE OWNER = V_GRANTOR_USER
               AND OBJECT_TYPE IN ('FUNCTION', 'PROCEDURE','PACKAGE BODY')
            ) LOOP
    --EXECUTE IMMEDIATE 'GRANT EXECUTE ON ' || V_GRANTOR_USER || '.' || L.OBJECT_NAME || ' TO ' || V_GRANTEE_USER;
    EXECUTE IMMEDIATE 'CREATE PUBLIC SYNONYM ' || L.OBJECT_NAME || ' FOR ' || V_GRANTOR_USER || '.' || L.OBJECT_NAME;
  END LOOP;
 

END;


--2 索引

declare
  s_sql varchar2(1000);
begin
 
  FOR c IN (SELECT T.OWNER, T.INDEX_NAME
                  FROM ALL_INDEXES T
                 WHERE T.OWNER = UPPER('ZEHB_YC1')
                   AND T.TABLE_TYPE = 'TABLE'
                   AND T.TEMPORARY = 'N'
                   AND T.INDEX_TYPE = 'NORMAL') LOOP
    S_SQL   := 'alter index ' || c.OWNER || '.' || c.INDEX_NAME ||
               ' rebuild';
    --ACCOUNT := ACCOUNT + 1;
    EXECUTE IMMEDIATE S_SQL;
  END LOOP;
end;

--3 启用禁用外键

select 'alter table '|| t.table_name ||' enable constraint '||t.constraint_name||';'
from user_constraints t where t.constraint_type = 'R'
order by t.table_name

select 'alter table '|| t.table_name||' disable constraint '||t.constraint_name||';'
from user_constraints t where t.constraint_type = 'R'
order by t.table_name