ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 减少全库导入时发生的错误

减少全库导入时发生的错误

原创 Linux操作系统 作者:qgrape 时间:2009-04-12 12:59:42 0 删除 编辑

为减少导入时产生的错误,导入前先运行下面脚本:

DECLARE
  TYPE T_VARCHAR_TAB IS TABLE OF VARCHAR2(32767) INDEX BY BINARY_INTEGER;
  V_PRIVS_STR  T_VARCHAR_TAB;
  V_ROLE_STR   T_VARCHAR_TAB;
  V_DROP_STR   VARCHAR2(32767);
  V_CREATE_STR VARCHAR2(32767);
BEGIN
  FOR I IN (SELECT USERNAME
              FROM DBA_USERS
             WHERE USERNAME NOT IN
                   ('SYS', 'SYSTEM', 'DBSNMP', 'WMSYS', 'ORDSYS', 'OE')) LOOP
 
    SELECT DBMS_METADATA.GET_DDL('USER', I.USERNAME)
      INTO V_CREATE_STR
      FROM DUAL;
    SELECT 'GRANT ' || PRIVILEGE || ' ON ' || OWNER || '.' || TABLE_NAME ||
           ' TO ' || GRANTEE BULK COLLECT
      INTO V_PRIVS_STR
      FROM DBA_TAB_PRIVS
     WHERE GRANTEE = I.USERNAME;
 
    SELECT 'GRANT ' || GRANTED_ROLE || ' TO ' || GRANTEE BULK COLLECT
      INTO V_ROLE_STR
      FROM DBA_ROLE_PRIVS
     WHERE GRANTEE = I.USERNAME;
    SELECT 'DROP USER ' || I.USERNAME || ' CASCADE'
      INTO V_DROP_STR
      FROM DUAL;
 
    EXECUTE IMMEDIATE V_DROP_STR;
    EXECUTE IMMEDIATE V_CREATE_STR;
    FOR I IN 1 .. V_PRIVS_STR.COUNT LOOP
      BEGIN
        EXECUTE IMMEDIATE V_PRIVS_STR(I);
      EXCEPTION
        WHEN OTHERS THEN
          NULL;
      END;
    END LOOP;
    FOR I IN 1 .. V_ROLE_STR.COUNT LOOP
      EXECUTE IMMEDIATE V_ROLE_STR(I);
    END LOOP;
  END LOOP;
END;
/

详细内容看YANGTINGKUN的博客:

      http://space.itpub.net/4227/viewspace-69014

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/12330444/viewspace-588724/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2008-04-09

  • 博文量
    223
  • 访问量
    514374