Difference between pages "Sys Aux Statistics" and "Create User Script"
(Difference between pages)
Jump to navigation
Jump to search
(Created page with "Note to-self: Hardware Stats are stored in <blockquote><pre> SYS.AUX_STATS$ </pre></blockquote> To gather the statistics: <blockquote><pre> exec dbms_stats.gather_system_st...") |
(Created page with "There are many ways of cloning a user account or two from one database to another. Here are a few simple SQLs to dynamically generate scripts from the source database, which c...") |
||
Line 1: | Line 1: | ||
− | + | There are many ways of cloning a user account or two from one database to another. Here are a few simple SQLs to dynamically generate scripts from the source database, which can then be executed on the target database. | |
+ | |||
+ | I have in this example assumed that the tablespaces etc in the source and target database are identical in name and size. I have also chosen to set all the user roles (by default) as Default. | ||
+ | |||
+ | '''Note: Some word-wrapping done here''' | ||
<blockquote><pre> | <blockquote><pre> | ||
− | + | SELECT 'CREATE USER ' || username || ' IDENTIFIED BY VALUES ''' || password || ''' DEFAULT TABLESPACE ' || | |
+ | default_tablespace || ' TEMPORARY TABLESPACE ' || temporary_tablespace || ' PROFILE ' || profile || | ||
+ | ' ACCOUNT UNLOCK;' | ||
+ | FROM dba_users | ||
+ | WHERE username in (&USERS); | ||
</pre></blockquote> | </pre></blockquote> | ||
− | |||
<blockquote><pre> | <blockquote><pre> | ||
− | + | SELECT 'GRANT ' || granted_role || ' TO ' || grantee || decode(admin_option,'YES',' WITH ADMIN OPTION;',';') | |
− | + | FROM dba_role_privs where grantee IN (&USERS); | |
− | |||
</pre></blockquote> | </pre></blockquote> | ||
− | + | <blockquote><pre> | |
− | <blockquote><pre> | + | SELECT 'GRANT ' || privilege || ' ON ' || owner || '.' || table_name || ' TO ' || grantee || decode(grantable,'YES',' WITH GRANT OPTION;',';') |
− | + | FROM dba_tab_privs | |
+ | WHERE grantee IN (&USERS); | ||
+ | </pre></blockquote> | ||
+ | |||
+ | <blockquote><pre> | ||
+ | SELECT 'ALTER USER ' || username || ' QUOTA ' || decode(max_bytes,-1,'UNLIMITED ON ' || TABLESPACE_NAME || | ||
+ | ';', max_bytes/1024 || 'K ON ' || TABLESPACE_NAME || ';') | ||
+ | FROM dba_ts_quotas | ||
+ | WHERE username in (&USERS); | ||
</pre></blockquote> | </pre></blockquote> |
Latest revision as of 22:57, 8 November 2019
There are many ways of cloning a user account or two from one database to another. Here are a few simple SQLs to dynamically generate scripts from the source database, which can then be executed on the target database.
I have in this example assumed that the tablespaces etc in the source and target database are identical in name and size. I have also chosen to set all the user roles (by default) as Default.
Note: Some word-wrapping done here
SELECT 'CREATE USER ' || username || ' IDENTIFIED BY VALUES ''' || password || ''' DEFAULT TABLESPACE ' || default_tablespace || ' TEMPORARY TABLESPACE ' || temporary_tablespace || ' PROFILE ' || profile || ' ACCOUNT UNLOCK;' FROM dba_users WHERE username in (&USERS);
SELECT 'GRANT ' || granted_role || ' TO ' || grantee || decode(admin_option,'YES',' WITH ADMIN OPTION;',';') FROM dba_role_privs where grantee IN (&USERS);
SELECT 'GRANT ' || privilege || ' ON ' || owner || '.' || table_name || ' TO ' || grantee || decode(grantable,'YES',' WITH GRANT OPTION;',';') FROM dba_tab_privs WHERE grantee IN (&USERS);
SELECT 'ALTER USER ' || username || ' QUOTA ' || decode(max_bytes,-1,'UNLIMITED ON ' || TABLESPACE_NAME || ';', max_bytes/1024 || 'K ON ' || TABLESPACE_NAME || ';') FROM dba_ts_quotas WHERE username in (&USERS);