Monday, February 16, 2015

User Drop and Create script

DECLARE
    u_count number;
    user_name VARCHAR2 (50);
    BEGIN
        u_count :=0;
        user_name :='DB_user';
        SELECT COUNT (1) INTO u_count FROM dba_users WHERE username = UPPER (user_name);
             IF u_count != 0
             THEN
                 EXECUTE IMMEDIATE ('DROP USER '||user_name||' CASCADE');
              END IF;
              u_count := 0;
          EXCEPTION
           WHEN OTHERS
              THEN
                     DBMS_OUTPUT.put_line (SQLERRM);
                     DBMS_OUTPUT.put_line ('   ');
    END;
/


DECLARE
    u_count number;
    user_name VARCHAR2 (50);
    BEGIN
        u_count :=0;
        user_name :='Db_user';
        SELECT COUNT (1) INTO u_count FROM dba_users WHERE username = UPPER (user_name);
             IF u_count = 0
             THEN
                 EXECUTE IMMEDIATE ('create USER '||user_name||' identified by pass');
                 EXECUTE IMMEDIATE ('grant dba to'||user_name);
              END IF;
              u_count := 0;
          EXCEPTION
           WHEN OTHERS
              THEN
                     DBMS_OUTPUT.put_line (SQLERRM);
                     DBMS_OUTPUT.put_line ('   ');
    END;
/

No comments:

Post a Comment