Difference between pages "Sys Aux Statistics" and "Create User Script"

From NazimWIKI
(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:
Note to-self:  Hardware Stats are stored in
+
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>
SYS.AUX_STATS$
+
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>
  
To gather the statistics:
 
 
<blockquote><pre>
 
<blockquote><pre>
exec dbms_stats.gather_system_stats('Start');
+
SELECT 'GRANT ' || granted_role || ' TO ' || grantee || decode(admin_option,'YES',' WITH ADMIN OPTION;',';')  
-- during peak times
+
  FROM dba_role_privs where grantee IN (&USERS);
exec dbms_stats.gather_system_stats('Stop');
 
 
</pre></blockquote>
 
</pre></blockquote>
  
To delete the statistics:
+
<blockquote><pre>
<blockquote><pre>
+
SELECT 'GRANT ' || privilege || ' ON ' || owner || '.' || table_name || ' TO ' || grantee || decode(grantable,'YES',' WITH GRANT OPTION;',';')
exec dbms_stats.delete_system_stats;
+
  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);