Difference between pages "Create User Script" and "Lookup v$ Views"

From NazimWIKI
(Difference between pages)
Jump to navigation Jump to search
(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...")
 
(Created page with "It's often difficult to remember which v$ view contains information you might be after. One way of quickly looking up the various views, is to run a query on the v$fixed_tabl...")
 
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.
+
It's often difficult to remember which v$ view contains information you might be after.
  
−
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.
+
One way of quickly looking up the various views, is to run a query on the v$fixed_table view.
−
 
 
−
'''Note: Some word-wrapping done here'''
 
−
 
 
−
<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>
 
  
 
<blockquote><pre>
 
<blockquote><pre>
−
SELECT 'GRANT ' || granted_role || ' TO ' || grantee || decode(admin_option,'YES',' WITH ADMIN OPTION;',';')
+
SQL> select name from v$fixed_table where name like '%FLASHBACK%';
−
  FROM dba_role_privs where grantee IN (&USERS);
 
−
</pre></blockquote>
 
  
−
<blockquote><pre>
+
NAME
−
SELECT 'GRANT ' || privilege || ' ON ' || owner || '.' || table_name || ' TO ' || grantee || decode(grantable,'YES',' WITH GRANT OPTION;',';')
+
------------------------------
−
  FROM dba_tab_privs
+
GV$FLASHBACK_DATABASE_LOGFILE
−
WHERE grantee IN (&USERS);
+
V$FLASHBACK_DATABASE_LOGFILE
−
</pre></blockquote>
+
GV$FLASHBACK_DATABASE_LOG
 +
V$FLASHBACK_DATABASE_LOG
 +
GV$FLASHBACK_DATABASE_STAT
 +
V$FLASHBACK_DATABASE_STAT
 +
V$FLASHBACK_TXN_MODS
 +
V$FLASHBACK_TXN_GRAPH
  
−
<blockquote><pre>
+
8 rows selected.
−
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:58, 8 November 2019

It's often difficult to remember which v$ view contains information you might be after.

One way of quickly looking up the various views, is to run a query on the v$fixed_table view. 

SQL> select name from v$fixed_table where name like '%FLASHBACK%';

NAME
------------------------------
GV$FLASHBACK_DATABASE_LOGFILE
V$FLASHBACK_DATABASE_LOGFILE
GV$FLASHBACK_DATABASE_LOG
V$FLASHBACK_DATABASE_LOG
GV$FLASHBACK_DATABASE_STAT
V$FLASHBACK_DATABASE_STAT
V$FLASHBACK_TXN_MODS
V$FLASHBACK_TXN_GRAPH

8 rows selected.
Retrieved from "http://www.nazimcricket.com/wiki/index.php?title=Create_User_Script&oldid=54"