When you don’t care so much about copying the contents of the schema…and you’re more concerned about just copying a user’s account, this PL/SQL block will allow you to make a user account and model it after an existing account. (If you are more interested in copying the schema contents…tables, indexes, etc., check out this blog post: How to Copy a Schema in Oracle)
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 |
declare v_create_user varchar2(128) := 'NEW_USER'; v_create_like varchar2(128) := 'EXISTING_USER'; v_sql varchar2(32767); begin dbms_output.enable(null); v_sql := replace(dbms_metadata.get_ddl('USER', v_create_like), v_create_like, v_create_user); dbms_output.put_line('SQL: [' || v_sql || ']');<a class="edit-post-status hide-if-no-js" href="https://kcdatabase.com/wp-admin/post.php?post=234&action=edit#post_status">Edit <span class="screen-reader-text">Edit status</span></a> execute immediate v_sql; begin v_sql := replace(dbms_metadata.get_granted_ddl('DEFAULT_ROLE', v_create_like), v_create_like, v_create_user); dbms_output.put_line('SQL: [' || v_sql || ']'); execute immediate v_sql; exception when others then if sqlcode <> -31608 then raise; end if; end; begin v_sql := replace(dbms_metadata.get_granted_ddl('SYSTEM_GRANT', v_create_like), v_create_like, v_create_user); dbms_output.put_line('SQL: [' || v_sql || ']'); execute immediate v_sql; exception when others then if sqlcode <> -31608 then raise; end if; end; begin v_sql := replace(dbms_metadata.get_granted_ddl('OBJECT_GRANT', v_create_like), v_create_like, v_create_user); dbms_output.put_line('SQL: [' || v_sql || ']'); execute immediate v_sql; exception when others then if sqlcode <> -31608 then raise; end if; end; begin v_sql := replace(dbms_metadata.get_granted_ddl('ROLE_GRANT', v_create_like), v_create_like, v_create_user); dbms_output.put_line('SQL: [' || v_sql || ']'); execute immediate v_sql; exception when others then if sqlcode <> -31608 then raise; end if; end; begin v_sql := replace(dbms_metadata.get_granted_ddl('TABLESPACE_QUOTA', v_create_like), v_create_like, v_create_user); dbms_output.put_line('SQL: [' || v_sql || ']'); execute immediate v_sql; exception when others then if sqlcode <> -31608 then raise; end if; end; end; / |