Tuesday, October 2, 2012

How to generate create USER script in oracle Database?

Just follow the steps (below i have gave an live example also)

1) create a table where you insert all the users name those for
you want to generate script for migration (with migrated_flag 'Y') .



drop table migrated_users ;


CREATE TABLE migrated_users
(old_name VARCHAR2(30),
new_name VARCHAR2(30),
migrated_flag VARCHAR2(10) DEFAULT 'Y'
);


2) insert all the users which you want migrate (with migrated_flag ='Y')


INSERT INTO migrated_users
(old_name, new_name, migrated_flag)
SELECT username, username, 'Y'
FROM all_users
WHERE username NOT IN ('SYS', 'SYSTEM', 'SYSMAN');


COMMIT ;



3) change the migrated_flag='N' for those which you are not wanted to migrate


SQL> select * from migrated_users ;


SQL> select count(*) from migrated_users
where migrated_flag='Y' ;


4) make ready sqlplus for spooling in a text file well.


begin
dbms_metadata.set_transform_param (dbms_metadata.session_transform, 'SQLTERMINATOR', true);
dbms_metadata.set_transform_param (dbms_metadata.session_transform, 'PRETTY', true);
end;
/

set long 2000000000
set head off
set pages 0
set feedback off
set termout off



5) Execute the following sql statement



SELECT REPLACE (DBMS_METADATA.get_ddl ('USER', UPPER (mig.old_name)),
UPPER (mig.old_name),
UPPER (mig.new_name)
)
FROM migrated_users mig JOIN dba_users usr ON usr.username =
UPPER (old_name)
WHERE migrated_flag = 'Y'
UNION ALL
SELECT REPLACE (DBMS_METADATA.get_granted_ddl ('TABLESPACE_QUOTA',
UPPER (old_name)
),
UPPER (old_name),
UPPER (new_name)
)
FROM migrated_users mig JOIN dba_ts_quotas tq
ON tq.username = UPPER (mig.old_name)
WHERE migrated_flag = 'Y'
UNION ALL
SELECT REPLACE (DBMS_METADATA.get_granted_ddl ('ROLE_GRANT', UPPER (old_name)),
UPPER (old_name),
UPPER (new_name)
)
FROM migrated_users mig JOIN dba_role_privs rpv
ON rpv.grantee = UPPER (mig.old_name)
WHERE migrated_flag = 'Y'
UNION ALL
SELECT REPLACE (DBMS_METADATA.get_granted_ddl ('SYSTEM_GRANT',
UPPER (old_name)
),
UPPER (old_name),
UPPER (new_name)
)
FROM migrated_users mig JOIN dba_sys_privs spv
ON spv.grantee = UPPER (mig.old_name)
WHERE migrated_flag = 'Y'
UNION ALL
SELECT REPLACE (DBMS_METADATA.get_granted_ddl ('OBJECT_GRANT',
UPPER (old_name)
),
UPPER (old_name),
UPPER (new_name)
)
FROM migrated_users mig JOIN dba_tab_privs tpv
ON tpv.grantee = UPPER (mig.old_name)
WHERE migrated_flag = 'Y'
UNION ALL
SELECT CASE
WHEN ((SELECT COUNT (*)
FROM dba_role_privs
WHERE grantee = UPPER (old_name)
AND default_role = 'YES'
AND ROWNUM = 1) > 0
)
THEN REPLACE (DBMS_METADATA.get_granted_ddl ('DEFAULT_ROLE',
UPPER (old_name)
),
UPPER (old_name),
UPPER (new_name)
)
END
FROM migrated_users
WHERE migrated_flag = 'Y'
UNION ALL
SELECT REPLACE (DBMS_METADATA.get_ddl ('PROFILE', PROFILE), old_name,
new_name)
FROM migrated_users mig JOIN dba_users usr
ON usr.username = UPPER (mig.old_name)
WHERE usr.PROFILE <> 'DEFAULT' AND migrated_flag = 'Y'


---double enter


6) spool it in a text file.

SQL> spool make_script_for_mig_user.sql
SQL> /
SQL> spool off

SQL> ed make_script_for_mig_user.sql


7) Now copy the script and execute it where you want to create these users.




Live example
==================


 SQL*Plus: Release 10.2.0.1.0 - Production on Tue Oct 2 11:58:18 2012  
 Copyright (c) 1982, 2005, Oracle. All rights reserved.  
 SQL>  
 SQL>  
 SQL>  
 SQL> set sqlprompt 'HalimDba >'  
 HalimDba >  
 HalimDba >  
 HalimDba >  
 HalimDba >set sqlprompt 'HalimDba@SQL>'  
 HalimDba@SQL>  
 HalimDba@SQL>  
 HalimDba@SQL>CREATE TABLE migrated_users  
  2 (old_name VARCHAR2(30),  
  3 new_name VARCHAR2(30)  
  4 );  
 SP2-0640: Not connected  
 HalimDba@SQL>  
 HalimDba@SQL>  
 HalimDba@SQL>conn test@orcl  
 Enter password:  
 Connected.  
 HalimDba@SQL>  
 HalimDba@SQL>  
 HalimDba@SQL>  
 HalimDba@SQL>  
 HalimDba@SQL>drop table migrated_users ;  
 Table dropped.  
 HalimDba@SQL>  
 HalimDba@SQL>  
 HalimDba@SQL>CREATE TABLE migrated_users  
  2 (old_name VARCHAR2(30),  
  3 new_name VARCHAR2(30),  
  4 migrated_flag VARCHAR2(10) DEFAULT 'Y'  
  5 );  
 Table created.  
 HalimDba@SQL>  
 HalimDba@SQL>  
 HalimDba@SQL>  
 HalimDba@SQL>  
 HalimDba@SQL>INSERT INTO migrated_users  
  2       (old_name, new_name, migrated_flag)  
  3   SELECT username, username, 'Y'  
  4    FROM all_users  
  5   WHERE username NOT IN ('SYS', 'SYSTEM', 'SYSMAN');  
 62 rows created.  
 HalimDba@SQL>  
 HalimDba@SQL>commit ;  
 Commit complete.  
 HalimDba@SQL>  
 HalimDba@SQL>  
 HalimDba@SQL>  
 HalimDba@SQL>  
 HalimDba@SQL>select * from migrated_users ;  
 
 BI               BI               N  
 PM               PM               N  
 SH               SH               Y  
 IX               IX               N  
 OE               OE               Y  
 HR               HR               Y  
 SCOTT             SCOTT             Y  
 OWBSYS_AUDIT          OWBSYS_AUDIT          N  
 KIOSK             KIOSK             N  
 OWBSYS             OWBSYS             N  
 APEX_030200          APEX_030200          N  
 APEX_PUBLIC_USER        APEX_PUBLIC_USER        N  
 FLOWS_FILES          FLOWS_FILES          N  
 MGMT_VIEW           MGMT_VIEW           N  
 SPATIAL_CSW_ADMIN_USR     SPATIAL_CSW_ADMIN_USR     N  
 SPATIAL_WFS_ADMIN_USR     SPATIAL_WFS_ADMIN_USR     N  
 MDDATA             MDDATA             N  
 MDSYS             MDSYS             N  
 SI_INFORMTN_SCHEMA       SI_INFORMTN_SCHEMA       N  
 ORDPLUGINS           ORDPLUGINS           N  
 ORDDATA            ORDDATA            N  
 ORDSYS             ORDSYS             N  
 OLAPSYS            OLAPSYS            N  
 ANONYMOUS           ANONYMOUS           N  
 XDB              XDB              N  
 CTXSYS             CTXSYS             N  
 EXFSYS             EXFSYS             N  
 XS$NULL            XS$NULL            N  
 WMSYS             WMSYS             N  
 APPQOSSYS           APPQOSSYS           N  
 DBSNMP             DBSNMP             N  
 ORACLE_OCM           ORACLE_OCM           N  
 DIP              DIP              N  
 OUTLN             OUTLN             N  
 HalimDba@SQL>  
 HalimDba@SQL>  
 HalimDba@SQL>  
 HalimDba@SQL>select count(*) from migrated_users where migrated_flag='Y' ;  
      8  
 HalimDba@SQL>  
 HalimDba@SQL>  
 HalimDba@SQL>select * from migrated_users where migrated_flag='Y' ;  
 EMON              EMON              Y  
 ORAPROBE            ORAPROBE            Y  
 SCOTT2             SCOTT2             Y  
 FAR              FAR              Y  
 SH               SH               Y  
 OE               OE               Y  
 HR               HR               Y  
 SCOTT             SCOTT             Y  
 HalimDba@SQL>  
 HalimDba@SQL>  
 HalimDba@SQL>  
 HalimDba@SQL>  
 HalimDba@SQL>  
 HalimDba@SQL>  
 HalimDba@SQL>  
 HalimDba@SQL>  
 HalimDba@SQL>begin  
  2   dbms_metadata.set_transform_param (dbms_metadata.session_transform, 'SQLTERMINATOR', true);  
  3   dbms_metadata.set_transform_param (dbms_metadata.session_transform, 'PRETTY', true);  
  4 end;  
  5 /  
 PL/SQL procedure successfully completed.  
 HalimDba@SQL>  
 HalimDba@SQL>set long 2000000000  
 HalimDba@SQL>set head off  
 HalimDba@SQL>set pages 0  
 HalimDba@SQL>set feedback off  
 HalimDba@SQL>set termout off  
 HalimDba@SQL>  
 HalimDba@SQL>  
 HalimDba@SQL>  
 HalimDba@SQL>  
 HalimDba@SQL>SELECT REPLACE (DBMS_METADATA.get_ddl ('USER', UPPER (mig.old_name)),  
  2         UPPER (mig.old_name),  
  3         UPPER (mig.new_name)  
  4         )  
  5  FROM migrated_users mig JOIN dba_users usr ON usr.username =  
  6                                UPPER (old_name)  
  7  WHERE migrated_flag = 'Y'  
  8 UNION ALL  
  9 SELECT REPLACE (DBMS_METADATA.get_granted_ddl ('TABLESPACE_QUOTA',  
  10                         UPPER (old_name)  
  11                        ),  
  12         UPPER (old_name),  
  13         UPPER (new_name)  
  14         )  
  15  FROM migrated_users mig JOIN dba_ts_quotas tq  
  16     ON tq.username = UPPER (mig.old_name)  
  17  WHERE migrated_flag = 'Y'  
  18 UNION ALL  
  19 SELECT REPLACE (DBMS_METADATA.get_granted_ddl ('ROLE_GRANT', UPPER (old_name)),  
  20         UPPER (old_name),  
  21         UPPER (new_name)  
  22         )  
  23  FROM migrated_users mig JOIN dba_role_privs rpv  
  24     ON rpv.grantee = UPPER (mig.old_name)  
  25  WHERE migrated_flag = 'Y'  
  26 UNION ALL  
  27 SELECT REPLACE (DBMS_METADATA.get_granted_ddl ('SYSTEM_GRANT',  
  28                         UPPER (old_name)  
  29                        ),  
  30         UPPER (old_name),  
  31         UPPER (new_name)  
  32         )  
  33  FROM migrated_users mig JOIN dba_sys_privs spv  
  34     ON spv.grantee = UPPER (mig.old_name)  
  35  WHERE migrated_flag = 'Y'  
  36 UNION ALL  
  37 SELECT REPLACE (DBMS_METADATA.get_granted_ddl ('OBJECT_GRANT',  
  38                         UPPER (old_name)  
  39                        ),  
  40         UPPER (old_name),  
  41         UPPER (new_name)  
  42         )  
  43  FROM migrated_users mig JOIN dba_tab_privs tpv  
  44     ON tpv.grantee = UPPER (mig.old_name)  
  45  WHERE migrated_flag = 'Y'  
  46 UNION ALL  
  47 SELECT CASE  
  48      WHEN ((SELECT COUNT (*)  
  49           FROM dba_role_privs  
  50          WHERE grantee = UPPER (old_name)  
  51           AND default_role = 'YES'  
  52           AND ROWNUM = 1) > 0  
  53         )  
  54        THEN REPLACE (DBMS_METADATA.get_granted_ddl ('DEFAULT_ROLE',  
  55                              UPPER (old_name)  
  56                              ),  
  57               UPPER (old_name),  
  58               UPPER (new_name)  
  59              )  
  60     END  
  61  FROM migrated_users  
  62  WHERE migrated_flag = 'Y'  
  63 UNION ALL  
  64 SELECT REPLACE (DBMS_METADATA.get_ddl ('PROFILE', PROFILE), old_name,  
  65         new_name)  
  66  FROM migrated_users mig JOIN dba_users usr  
  67     ON usr.username = UPPER (mig.old_name)  
  68  WHERE usr.PROFILE <> 'DEFAULT' AND migrated_flag = 'Y'  
  69  
 HalimDba@SQL>  
 HalimDba@SQL>  
 HalimDba@SQL>spool make_script_for_mig_user.sql  
 HalimDba@SQL>  
 HalimDba@SQL>/  
   CREATE USER "SCOTT" IDENTIFIED BY VALUES 'S:5032AB0C7ABA383E0D28092737A91D932  
 3FD910459A177C3E9D8DB5569DA;F894844C34402B67'  
    DEFAULT TABLESPACE "USERS"  
    TEMPORARY TABLESPACE "TEMP";  
   CREATE USER "HR" IDENTIFIED BY VALUES 'S:FE1054E863661B1CE8191B2810D3AB0BFAC6  
 63C2B11E5800D35998377F3F;4C6D73C3E8B0F0DA'  
    DEFAULT TABLESPACE "USERS"  
    TEMPORARY TABLESPACE "TEMP";  
   CREATE USER "OE" IDENTIFIED BY VALUES 'S:5EE7A0404572BB90A2A789DC86B1BD368725  
 F4A92E05EADD02F9666AE230;D1A2DFC623FDA40A'  
    DEFAULT TABLESPACE "USERS"  
    TEMPORARY TABLESPACE "TEMP";  
   CREATE USER "FAR" IDENTIFIED BY VALUES 'S:ECC63700AB0394C7DA58EE01D6F58611988  
 EDC3E80C904917610869E2D35;7645F42D0980A970'  
    DEFAULT TABLESPACE "USERS"  
    TEMPORARY TABLESPACE "TEMP";  
   CREATE USER "SCOTT2" IDENTIFIED BY VALUES 'S:C08B6C101681BAF61FFADB424757B94F  
 1F3789111A75DB84B680281201A0;FC9CF3389C9FDC3A'  
    DEFAULT TABLESPACE "USERS"  
    TEMPORARY TABLESPACE "TEMP";  
   CREATE USER "ORAPROBE" IDENTIFIED BY VALUES 'S:2D8EBE0FDDE28A4E88292A9462A40F  
 EEAA15D4221B00D334DFE410B70F47;2E3EA470A4CA2D94'  
    DEFAULT TABLESPACE "USERS"  
    TEMPORARY TABLESPACE "TEMP";  
   CREATE USER "EMON" IDENTIFIED BY VALUES 'S:C40F1EF9696F7B3240C360588F9C562E3A  
 5A770E74B3B5812EF0BB1F8D34;EF9CDAF80D05BE2F'  
    DEFAULT TABLESPACE "USERS"  
    TEMPORARY TABLESPACE "TEMP";  
   CREATE USER "SH" IDENTIFIED BY VALUES 'S:96CCBC97D834A105218FB2180FB70ED528AB  
 296A7C98150C0C5439D5628E;9793B3777CD3BD1A'  
    DEFAULT TABLESPACE "USERS"  
    TEMPORARY TABLESPACE "TEMP"  
    PASSWORD EXPIRE  
    ACCOUNT LOCK;  
  DECLARE  
  TEMP_COUNT NUMBER;  
  SQLSTR VARCHAR2(200);  
 BEGIN  
  SQLSTR := 'ALTER USER "ORAPROBE" QUOTA UNLIMITED ON "USERS"';  
  EXECUTE IMMEDIATE SQLSTR;  
 EXCEPTION  
  WHEN OTHERS THEN  
   IF SQLCODE = -30041 THEN  
    SQLSTR := 'SELECT COUNT(*) FROM USER_TABLESPACES  
        WHERE TABLESPACE_NAME = ''USERS'' AND CONTENTS = ''TEMPORARY''';  
    EXECUTE IMMEDIATE SQLSTR INTO TEMP_COUNT;  
    IF TEMP_COUNT = 1 THEN RETURN;  
    ELSE RAISE;  
    END IF;  
   ELSE  
    RAISE;  
   END IF;  
 END;  
 /  
   GRANT "RESOURCE" TO "OE";  
   GRANT "XDBADMIN" TO "OE";  
   GRANT "CONNECT" TO "FAR";  
   GRANT "DBA" TO "FAR";  
   GRANT "DBA" TO "SCOTT2";  
   GRANT "CONNECT" TO "SCOTT";  
   GRANT "RESOURCE" TO "SCOTT";  
   GRANT "DBA" TO "SCOTT";  
   GRANT "RESOURCE" TO "HR";  
   GRANT "RESOURCE" TO "OE";  
   GRANT "XDBADMIN" TO "OE";  
   GRANT "SELECT_CATALOG_ROLE" TO "ORAPROBE";  
   GRANT "CONNECT" TO "SCOTT";  
   GRANT "RESOURCE" TO "SCOTT";  
   GRANT "DBA" TO "SCOTT";  
   GRANT "RESOURCE" TO "SH";  
   GRANT "SELECT_CATALOG_ROLE" TO "SH";  
   GRANT "CWM_USER" TO "SH";  
   GRANT "RESOURCE" TO "SH";  
   GRANT "SELECT_CATALOG_ROLE" TO "SH";  
   GRANT "CWM_USER" TO "SH";  
   GRANT "CONNECT" TO "FAR";  
   GRANT "DBA" TO "FAR";  
   GRANT "DBA" TO "EMON";  
   GRANT "CONNECT" TO "SCOTT";  
   GRANT "RESOURCE" TO "SCOTT";  
   GRANT "DBA" TO "SCOTT";  
   GRANT "RESOURCE" TO "SH";  
   GRANT "SELECT_CATALOG_ROLE" TO "SH";  
   GRANT "CWM_USER" TO "SH";  
  GRANT CREATE DATABASE LINK TO "HR";  
  GRANT CREATE SEQUENCE TO "HR";  
  GRANT CREATE VIEW TO "HR";  
  GRANT CREATE SYNONYM TO "HR";  
  GRANT UNLIMITED TABLESPACE TO "HR";  
  GRANT ALTER SESSION TO "HR";  
  GRANT CREATE SESSION TO "HR";  
  GRANT CREATE DATABASE LINK TO "HR";  
  GRANT CREATE SEQUENCE TO "HR";  
  GRANT CREATE VIEW TO "HR";  
  GRANT CREATE SYNONYM TO "HR";  
  GRANT UNLIMITED TABLESPACE TO "HR";  
  GRANT ALTER SESSION TO "HR";  
  GRANT CREATE SESSION TO "HR";  
  GRANT CREATE DATABASE LINK TO "HR";  
  GRANT CREATE SEQUENCE TO "HR";  
  GRANT CREATE VIEW TO "HR";  
  GRANT CREATE SYNONYM TO "HR";  
  GRANT UNLIMITED TABLESPACE TO "HR";  
  GRANT ALTER SESSION TO "HR";  
  GRANT CREATE SESSION TO "HR";  
  GRANT CREATE DIMENSION TO "SH";  
  GRANT QUERY REWRITE TO "SH";  
  GRANT CREATE MATERIALIZED VIEW TO "SH";  
  GRANT CREATE DATABASE LINK TO "SH";  
  GRANT CREATE SEQUENCE TO "SH";  
  GRANT CREATE VIEW TO "SH";  
  GRANT CREATE SYNONYM TO "SH";  
  GRANT CREATE CLUSTER TO "SH";  
  GRANT CREATE TABLE TO "SH";  
  GRANT UNLIMITED TABLESPACE TO "SH";  
  GRANT ALTER SESSION TO "SH";  
  GRANT CREATE SESSION TO "SH";  
  GRANT CREATE DIMENSION TO "SH";  
  GRANT QUERY REWRITE TO "SH";  
  GRANT CREATE MATERIALIZED VIEW TO "SH";  
  GRANT CREATE DATABASE LINK TO "SH";  
  GRANT CREATE SEQUENCE TO "SH";  
  GRANT CREATE VIEW TO "SH";  
  GRANT CREATE SYNONYM TO "SH";  
  GRANT CREATE CLUSTER TO "SH";  
  GRANT CREATE TABLE TO "SH";  
  GRANT UNLIMITED TABLESPACE TO "SH";  
  GRANT ALTER SESSION TO "SH";  
  GRANT CREATE SESSION TO "SH";  
  GRANT CREATE PROCEDURE TO "ORAPROBE";  
  GRANT CREATE TABLE TO "ORAPROBE";  
  GRANT CREATE SESSION TO "ORAPROBE";  
  GRANT UNLIMITED TABLESPACE TO "SCOTT";  
  GRANT CREATE DATABASE LINK TO "HR";  
  GRANT CREATE SEQUENCE TO "HR";  
  GRANT CREATE VIEW TO "HR";  
  GRANT CREATE SYNONYM TO "HR";  
  GRANT UNLIMITED TABLESPACE TO "HR";  
  GRANT ALTER SESSION TO "HR";  
  GRANT CREATE SESSION TO "HR";  
  GRANT QUERY REWRITE TO "OE";  
  GRANT CREATE MATERIALIZED VIEW TO "OE";  
  GRANT CREATE DATABASE LINK TO "OE";  
  GRANT CREATE VIEW TO "OE";  
  GRANT CREATE SYNONYM TO "OE";  
  GRANT UNLIMITED TABLESPACE TO "OE";  
  GRANT CREATE SESSION TO "OE";  
  GRANT QUERY REWRITE TO "OE";  
  GRANT CREATE MATERIALIZED VIEW TO "OE";  
  GRANT CREATE DATABASE LINK TO "OE";  
  GRANT CREATE VIEW TO "OE";  
  GRANT CREATE SYNONYM TO "OE";  
  GRANT UNLIMITED TABLESPACE TO "OE";  
  GRANT CREATE SESSION TO "OE";  
  GRANT CREATE DIMENSION TO "SH";  
  GRANT QUERY REWRITE TO "SH";  
  GRANT CREATE MATERIALIZED VIEW TO "SH";  
  GRANT CREATE DATABASE LINK TO "SH";  
  GRANT CREATE SEQUENCE TO "SH";  
  GRANT CREATE VIEW TO "SH";  
  GRANT CREATE SYNONYM TO "SH";  
  GRANT CREATE CLUSTER TO "SH";  
  GRANT CREATE TABLE TO "SH";  
  GRANT UNLIMITED TABLESPACE TO "SH";  
  GRANT ALTER SESSION TO "SH";  
  GRANT CREATE SESSION TO "SH";  
  GRANT CREATE DIMENSION TO "SH";  
  GRANT QUERY REWRITE TO "SH";  
  GRANT CREATE MATERIALIZED VIEW TO "SH";  
  GRANT CREATE DATABASE LINK TO "SH";  
  GRANT CREATE SEQUENCE TO "SH";  
  GRANT CREATE VIEW TO "SH";  
  GRANT CREATE SYNONYM TO "SH";  
  GRANT CREATE CLUSTER TO "SH";  
  GRANT CREATE TABLE TO "SH";  
  GRANT UNLIMITED TABLESPACE TO "SH";  
  GRANT ALTER SESSION TO "SH";  
  GRANT CREATE SESSION TO "SH";  
  GRANT CREATE DIMENSION TO "SH";  
  GRANT QUERY REWRITE TO "SH";  
  GRANT CREATE MATERIALIZED VIEW TO "SH";  
  GRANT CREATE DATABASE LINK TO "SH";  
  GRANT CREATE SEQUENCE TO "SH";  
  GRANT CREATE VIEW TO "SH";  
  GRANT CREATE SYNONYM TO "SH";  
  GRANT CREATE CLUSTER TO "SH";  
  GRANT CREATE TABLE TO "SH";  
  GRANT UNLIMITED TABLESPACE TO "SH";  
  GRANT ALTER SESSION TO "SH";  
  GRANT CREATE SESSION TO "SH";  
  GRANT UNLIMITED TABLESPACE TO "FAR";  
  GRANT CREATE DATABASE LINK TO "HR";  
  GRANT CREATE SEQUENCE TO "HR";  
  GRANT CREATE VIEW TO "HR";  
  GRANT CREATE SYNONYM TO "HR";  
  GRANT UNLIMITED TABLESPACE TO "HR";  
  GRANT ALTER SESSION TO "HR";  
  GRANT CREATE SESSION TO "HR";  
  GRANT CREATE DATABASE LINK TO "HR";  
  GRANT CREATE SEQUENCE TO "HR";  
  GRANT CREATE VIEW TO "HR";  
  GRANT CREATE SYNONYM TO "HR";  
  GRANT UNLIMITED TABLESPACE TO "HR";  
  GRANT ALTER SESSION TO "HR";  
  GRANT CREATE SESSION TO "HR";  
  GRANT CREATE DIMENSION TO "SH";  
  GRANT QUERY REWRITE TO "SH";  
  GRANT CREATE MATERIALIZED VIEW TO "SH";  
  GRANT CREATE DATABASE LINK TO "SH";  
  GRANT CREATE SEQUENCE TO "SH";  
  GRANT CREATE VIEW TO "SH";  
  GRANT CREATE SYNONYM TO "SH";  
  GRANT CREATE CLUSTER TO "SH";  
  GRANT CREATE TABLE TO "SH";  
  GRANT UNLIMITED TABLESPACE TO "SH";  
  GRANT ALTER SESSION TO "SH";  
  GRANT CREATE SESSION TO "SH";  
  GRANT CREATE DATABASE LINK TO "HR";  
  GRANT CREATE SEQUENCE TO "HR";  
  GRANT CREATE VIEW TO "HR";  
  GRANT CREATE SYNONYM TO "HR";  
  GRANT UNLIMITED TABLESPACE TO "HR";  
  GRANT ALTER SESSION TO "HR";  
  GRANT CREATE SESSION TO "HR";  
  GRANT QUERY REWRITE TO "OE";  
  GRANT CREATE MATERIALIZED VIEW TO "OE";  
  GRANT CREATE DATABASE LINK TO "OE";  
  GRANT CREATE VIEW TO "OE";  
  GRANT CREATE SYNONYM TO "OE";  
  GRANT UNLIMITED TABLESPACE TO "OE";  
  GRANT CREATE SESSION TO "OE";  
  GRANT QUERY REWRITE TO "OE";  
  GRANT CREATE MATERIALIZED VIEW TO "OE";  
  GRANT CREATE DATABASE LINK TO "OE";  
  GRANT CREATE VIEW TO "OE";  
  GRANT CREATE SYNONYM TO "OE";  
  GRANT UNLIMITED TABLESPACE TO "OE";  
  GRANT CREATE SESSION TO "OE";  
  GRANT CREATE DIMENSION TO "SH";  
  GRANT QUERY REWRITE TO "SH";  
  GRANT CREATE MATERIALIZED VIEW TO "SH";  
  GRANT CREATE DATABASE LINK TO "SH";  
  GRANT CREATE SEQUENCE TO "SH";  
  GRANT CREATE VIEW TO "SH";  
  GRANT CREATE SYNONYM TO "SH";  
  GRANT CREATE CLUSTER TO "SH";  
  GRANT CREATE TABLE TO "SH";  
  GRANT UNLIMITED TABLESPACE TO "SH";  
  GRANT ALTER SESSION TO "SH";  
  GRANT CREATE SESSION TO "SH";  
  GRANT CREATE DIMENSION TO "SH";  
  GRANT QUERY REWRITE TO "SH";  
  GRANT CREATE MATERIALIZED VIEW TO "SH";  
  GRANT CREATE DATABASE LINK TO "SH";  
  GRANT CREATE SEQUENCE TO "SH";  
  GRANT CREATE VIEW TO "SH";  
  GRANT CREATE SYNONYM TO "SH";  
  GRANT CREATE CLUSTER TO "SH";  
  GRANT CREATE TABLE TO "SH";  
  GRANT UNLIMITED TABLESPACE TO "SH";  
  GRANT ALTER SESSION TO "SH";  
  GRANT CREATE SESSION TO "SH";  
  GRANT CREATE DIMENSION TO "SH";  
  GRANT QUERY REWRITE TO "SH";  
  GRANT CREATE MATERIALIZED VIEW TO "SH";  
  GRANT CREATE DATABASE LINK TO "SH";  
  GRANT CREATE SEQUENCE TO "SH";  
  GRANT CREATE VIEW TO "SH";  
  GRANT CREATE SYNONYM TO "SH";  
  GRANT CREATE CLUSTER TO "SH";  
  GRANT CREATE TABLE TO "SH";  
  GRANT UNLIMITED TABLESPACE TO "SH";  
  GRANT ALTER SESSION TO "SH";  
  GRANT CREATE SESSION TO "SH";  
  GRANT CREATE PROCEDURE TO "ORAPROBE";  
  GRANT CREATE TABLE TO "ORAPROBE";  
  GRANT CREATE SESSION TO "ORAPROBE";  
  GRANT QUERY REWRITE TO "OE";  
  GRANT CREATE MATERIALIZED VIEW TO "OE";  
  GRANT CREATE DATABASE LINK TO "OE";  
  GRANT CREATE VIEW TO "OE";  
  GRANT CREATE SYNONYM TO "OE";  
  GRANT UNLIMITED TABLESPACE TO "OE";  
  GRANT CREATE SESSION TO "OE";  
  GRANT QUERY REWRITE TO "OE";  
  GRANT CREATE MATERIALIZED VIEW TO "OE";  
  GRANT CREATE DATABASE LINK TO "OE";  
  GRANT CREATE VIEW TO "OE";  
  GRANT CREATE SYNONYM TO "OE";  
  GRANT UNLIMITED TABLESPACE TO "OE";  
  GRANT CREATE SESSION TO "OE";  
  GRANT UNLIMITED TABLESPACE TO "SCOTT2";  
  GRANT CREATE PROCEDURE TO "ORAPROBE";  
  GRANT CREATE TABLE TO "ORAPROBE";  
  GRANT CREATE SESSION TO "ORAPROBE";  
  GRANT QUERY REWRITE TO "OE";  
  GRANT CREATE MATERIALIZED VIEW TO "OE";  
  GRANT CREATE DATABASE LINK TO "OE";  
  GRANT CREATE VIEW TO "OE";  
  GRANT CREATE SYNONYM TO "OE";  
  GRANT UNLIMITED TABLESPACE TO "OE";  
  GRANT CREATE SESSION TO "OE";  
  GRANT CREATE DIMENSION TO "SH";  
  GRANT QUERY REWRITE TO "SH";  
  GRANT CREATE MATERIALIZED VIEW TO "SH";  
  GRANT CREATE DATABASE LINK TO "SH";  
  GRANT CREATE SEQUENCE TO "SH";  
  GRANT CREATE VIEW TO "SH";  
  GRANT CREATE SYNONYM TO "SH";  
  GRANT CREATE CLUSTER TO "SH";  
  GRANT CREATE TABLE TO "SH";  
  GRANT UNLIMITED TABLESPACE TO "SH";  
  GRANT ALTER SESSION TO "SH";  
  GRANT CREATE SESSION TO "SH";  
  GRANT CREATE DIMENSION TO "SH";  
  GRANT QUERY REWRITE TO "SH";  
  GRANT CREATE MATERIALIZED VIEW TO "SH";  
  GRANT CREATE DATABASE LINK TO "SH";  
  GRANT CREATE SEQUENCE TO "SH";  
  GRANT CREATE VIEW TO "SH";  
  GRANT CREATE SYNONYM TO "SH";  
  GRANT CREATE CLUSTER TO "SH";  
  GRANT CREATE TABLE TO "SH";  
  GRANT UNLIMITED TABLESPACE TO "SH";  
  GRANT ALTER SESSION TO "SH";  
  GRANT CREATE SESSION TO "SH";  
  GRANT CREATE DIMENSION TO "SH";  
  GRANT QUERY REWRITE TO "SH";  
  GRANT CREATE MATERIALIZED VIEW TO "SH";  
  GRANT CREATE DATABASE LINK TO "SH";  
  GRANT CREATE SEQUENCE TO "SH";  
  GRANT CREATE VIEW TO "SH";  
  GRANT CREATE SYNONYM TO "SH";  
  GRANT CREATE CLUSTER TO "SH";  
  GRANT CREATE TABLE TO "SH";  
  GRANT UNLIMITED TABLESPACE TO "SH";  
  GRANT ALTER SESSION TO "SH";  
  GRANT CREATE SESSION TO "SH";  
  GRANT UNLIMITED TABLESPACE TO "EMON";  
  GRANT EXECUTE ON "SYS"."DBMS_STATS" TO "OE";  
  GRANT SELECT, REFERENCES ON "HR"."COUNTRIES" TO "OE";  
  GRANT SELECT, REFERENCES ON "HR"."LOCATIONS" TO "OE";  
  GRANT SELECT ON "HR"."DEPARTMENTS" TO "OE";  
  GRANT SELECT ON "HR"."JOBS" TO "OE";  
  GRANT SELECT, REFERENCES ON "HR"."EMPLOYEES" TO "OE";  
  GRANT SELECT ON "HR"."JOB_HISTORY" TO "OE";  
  GRANT READ, WRITE ON DIRECTORY "SS_OE_XMLDIR" TO "OE" WITH GRANT OPTION;  
  GRANT READ, WRITE ON DIRECTORY "SUBDIR" TO "OE" WITH GRANT OPTION;  
  GRANT EXECUTE ON "SYS"."DBMS_STATS" TO "OE";  
  GRANT SELECT, REFERENCES ON "HR"."COUNTRIES" TO "OE";  
  GRANT SELECT, REFERENCES ON "HR"."LOCATIONS" TO "OE";  
  GRANT SELECT ON "HR"."DEPARTMENTS" TO "OE";  
  GRANT SELECT ON "HR"."JOBS" TO "OE";  
  GRANT SELECT, REFERENCES ON "HR"."EMPLOYEES" TO "OE";  
  GRANT SELECT ON "HR"."JOB_HISTORY" TO "OE";  
  GRANT READ, WRITE ON DIRECTORY "SS_OE_XMLDIR" TO "OE" WITH GRANT OPTION;  
  GRANT READ, WRITE ON DIRECTORY "SUBDIR" TO "OE" WITH GRANT OPTION;  
  GRANT EXECUTE ON "SYS"."DBMS_STATS" TO "SH";  
  GRANT READ ON DIRECTORY "DATA_FILE_DIR" TO "SH";  
  GRANT READ, WRITE ON DIRECTORY "LOG_FILE_DIR" TO "SH";  
  GRANT EXECUTE ON "SYS"."DBMS_STATS" TO "SH";  
  GRANT READ ON DIRECTORY "DATA_FILE_DIR" TO "SH";  
  GRANT READ, WRITE ON DIRECTORY "LOG_FILE_DIR" TO "SH";  
  GRANT EXECUTE ON "SYS"."DBMS_STATS" TO "SH";  
  GRANT READ ON DIRECTORY "DATA_FILE_DIR" TO "SH";  
  GRANT READ, WRITE ON DIRECTORY "LOG_FILE_DIR" TO "SH";  
  GRANT SELECT ON "SYS"."DBA_USERS" TO "ORAPROBE";  
  GRANT EXECUTE ON "SYS"."DBMS_STATS" TO "SH";  
  GRANT READ ON DIRECTORY "DATA_FILE_DIR" TO "SH";  
  GRANT READ, WRITE ON DIRECTORY "LOG_FILE_DIR" TO "SH";  
  GRANT EXECUTE ON "SYS"."DBMS_STATS" TO "OE";  
  GRANT SELECT, REFERENCES ON "HR"."COUNTRIES" TO "OE";  
  GRANT SELECT, REFERENCES ON "HR"."LOCATIONS" TO "OE";  
  GRANT SELECT ON "HR"."DEPARTMENTS" TO "OE";  
  GRANT SELECT ON "HR"."JOBS" TO "OE";  
  GRANT SELECT, REFERENCES ON "HR"."EMPLOYEES" TO "OE";  
  GRANT SELECT ON "HR"."JOB_HISTORY" TO "OE";  
  GRANT READ, WRITE ON DIRECTORY "SS_OE_XMLDIR" TO "OE" WITH GRANT OPTION;  
  GRANT READ, WRITE ON DIRECTORY "SUBDIR" TO "OE" WITH GRANT OPTION;  
  GRANT EXECUTE ON "SYS"."DBMS_STATS" TO "HR";  
  GRANT EXECUTE ON "SYS"."DBMS_STATS" TO "OE";  
  GRANT SELECT, REFERENCES ON "HR"."COUNTRIES" TO "OE";  
  GRANT SELECT, REFERENCES ON "HR"."LOCATIONS" TO "OE";  
  GRANT SELECT ON "HR"."DEPARTMENTS" TO "OE";  
  GRANT SELECT ON "HR"."JOBS" TO "OE";  
  GRANT SELECT, REFERENCES ON "HR"."EMPLOYEES" TO "OE";  
  GRANT SELECT ON "HR"."JOB_HISTORY" TO "OE";  
  GRANT READ, WRITE ON DIRECTORY "SS_OE_XMLDIR" TO "OE" WITH GRANT OPTION;  
  GRANT READ, WRITE ON DIRECTORY "SUBDIR" TO "OE" WITH GRANT OPTION;  
  GRANT EXECUTE ON "SYS"."DBMS_STATS" TO "OE";  
  GRANT SELECT, REFERENCES ON "HR"."COUNTRIES" TO "OE";  
  GRANT SELECT, REFERENCES ON "HR"."LOCATIONS" TO "OE";  
  GRANT SELECT ON "HR"."DEPARTMENTS" TO "OE";  
  GRANT SELECT ON "HR"."JOBS" TO "OE";  
  GRANT SELECT, REFERENCES ON "HR"."EMPLOYEES" TO "OE";  
  GRANT SELECT ON "HR"."JOB_HISTORY" TO "OE";  
  GRANT READ, WRITE ON DIRECTORY "SS_OE_XMLDIR" TO "OE" WITH GRANT OPTION;  
  GRANT READ, WRITE ON DIRECTORY "SUBDIR" TO "OE" WITH GRANT OPTION;  
  GRANT EXECUTE, READ, WRITE ON DIRECTORY "DIR_MVIEW" TO "SCOTT" WITH GRANT OPTI  
 ON;  
  GRANT EXECUTE, READ, WRITE ON DIRECTORY "DIR_MVIEW" TO "SCOTT" WITH GRANT OPTI  
 ON;  
  GRANT EXECUTE, READ, WRITE ON DIRECTORY "DIR_MVIEW" TO "SCOTT" WITH GRANT OPTI  
 ON;  
  GRANT EXECUTE ON "SYS"."DBMS_STATS" TO "OE";  
  GRANT SELECT, REFERENCES ON "HR"."COUNTRIES" TO "OE";  
  GRANT SELECT, REFERENCES ON "HR"."LOCATIONS" TO "OE";  
  GRANT SELECT ON "HR"."DEPARTMENTS" TO "OE";  
  GRANT SELECT ON "HR"."JOBS" TO "OE";  
  GRANT SELECT, REFERENCES ON "HR"."EMPLOYEES" TO "OE";  
  GRANT SELECT ON "HR"."JOB_HISTORY" TO "OE";  
  GRANT READ, WRITE ON DIRECTORY "SS_OE_XMLDIR" TO "OE" WITH GRANT OPTION;  
  GRANT READ, WRITE ON DIRECTORY "SUBDIR" TO "OE" WITH GRANT OPTION;  
  GRANT EXECUTE ON "SYS"."DBMS_STATS" TO "OE";  
  GRANT SELECT, REFERENCES ON "HR"."COUNTRIES" TO "OE";  
  GRANT SELECT, REFERENCES ON "HR"."LOCATIONS" TO "OE";  
  GRANT SELECT ON "HR"."DEPARTMENTS" TO "OE";  
  GRANT SELECT ON "HR"."JOBS" TO "OE";  
  GRANT SELECT, REFERENCES ON "HR"."EMPLOYEES" TO "OE";  
  GRANT SELECT ON "HR"."JOB_HISTORY" TO "OE";  
  GRANT READ, WRITE ON DIRECTORY "SS_OE_XMLDIR" TO "OE" WITH GRANT OPTION;  
  GRANT READ, WRITE ON DIRECTORY "SUBDIR" TO "OE" WITH GRANT OPTION;  
  GRANT EXECUTE ON "SYS"."DBMS_STATS" TO "OE";  
  GRANT SELECT, REFERENCES ON "HR"."COUNTRIES" TO "OE";  
  GRANT SELECT, REFERENCES ON "HR"."LOCATIONS" TO "OE";  
  GRANT SELECT ON "HR"."DEPARTMENTS" TO "OE";  
  GRANT SELECT ON "HR"."JOBS" TO "OE";  
  GRANT SELECT, REFERENCES ON "HR"."EMPLOYEES" TO "OE";  
  GRANT SELECT ON "HR"."JOB_HISTORY" TO "OE";  
  GRANT READ, WRITE ON DIRECTORY "SS_OE_XMLDIR" TO "OE" WITH GRANT OPTION;  
  GRANT READ, WRITE ON DIRECTORY "SUBDIR" TO "OE" WITH GRANT OPTION;  
  GRANT EXECUTE ON "SYS"."DBMS_STATS" TO "OE";  
  GRANT SELECT, REFERENCES ON "HR"."COUNTRIES" TO "OE";  
  GRANT SELECT, REFERENCES ON "HR"."LOCATIONS" TO "OE";  
  GRANT SELECT ON "HR"."DEPARTMENTS" TO "OE";  
  GRANT SELECT ON "HR"."JOBS" TO "OE";  
  GRANT SELECT, REFERENCES ON "HR"."EMPLOYEES" TO "OE";  
  GRANT SELECT ON "HR"."JOB_HISTORY" TO "OE";  
  GRANT READ, WRITE ON DIRECTORY "SS_OE_XMLDIR" TO "OE" WITH GRANT OPTION;  
  GRANT READ, WRITE ON DIRECTORY "SUBDIR" TO "OE" WITH GRANT OPTION;  
  GRANT EXECUTE ON "SYS"."DBMS_STATS" TO "OE";  
  GRANT SELECT, REFERENCES ON "HR"."COUNTRIES" TO "OE";  
  GRANT SELECT, REFERENCES ON "HR"."LOCATIONS" TO "OE";  
  GRANT SELECT ON "HR"."DEPARTMENTS" TO "OE";  
  GRANT SELECT ON "HR"."JOBS" TO "OE";  
  GRANT SELECT, REFERENCES ON "HR"."EMPLOYEES" TO "OE";  
  GRANT SELECT ON "HR"."JOB_HISTORY" TO "OE";  
  GRANT READ, WRITE ON DIRECTORY "SS_OE_XMLDIR" TO "OE" WITH GRANT OPTION;  
  GRANT READ, WRITE ON DIRECTORY "SUBDIR" TO "OE" WITH GRANT OPTION;  
  GRANT EXECUTE ON "SYS"."DBMS_STATS" TO "OE";  
  GRANT SELECT, REFERENCES ON "HR"."COUNTRIES" TO "OE";  
  GRANT SELECT, REFERENCES ON "HR"."LOCATIONS" TO "OE";  
  GRANT SELECT ON "HR"."DEPARTMENTS" TO "OE";  
  GRANT SELECT ON "HR"."JOBS" TO "OE";  
  GRANT SELECT, REFERENCES ON "HR"."EMPLOYEES" TO "OE";  
  GRANT SELECT ON "HR"."JOB_HISTORY" TO "OE";  
  GRANT READ, WRITE ON DIRECTORY "SS_OE_XMLDIR" TO "OE" WITH GRANT OPTION;  
  GRANT READ, WRITE ON DIRECTORY "SUBDIR" TO "OE" WITH GRANT OPTION;  
  GRANT EXECUTE ON "SYS"."DBMS_STATS" TO "OE";  
  GRANT SELECT, REFERENCES ON "HR"."COUNTRIES" TO "OE";  
  GRANT SELECT, REFERENCES ON "HR"."LOCATIONS" TO "OE";  
  GRANT SELECT ON "HR"."DEPARTMENTS" TO "OE";  
  GRANT SELECT ON "HR"."JOBS" TO "OE";  
  GRANT SELECT, REFERENCES ON "HR"."EMPLOYEES" TO "OE";  
  GRANT SELECT ON "HR"."JOB_HISTORY" TO "OE";  
  GRANT READ, WRITE ON DIRECTORY "SS_OE_XMLDIR" TO "OE" WITH GRANT OPTION;  
  GRANT READ, WRITE ON DIRECTORY "SUBDIR" TO "OE" WITH GRANT OPTION;  
  GRANT EXECUTE ON "SYS"."DBMS_STATS" TO "OE";  
  GRANT SELECT, REFERENCES ON "HR"."COUNTRIES" TO "OE";  
  GRANT SELECT, REFERENCES ON "HR"."LOCATIONS" TO "OE";  
  GRANT SELECT ON "HR"."DEPARTMENTS" TO "OE";  
  GRANT SELECT ON "HR"."JOBS" TO "OE";  
  GRANT SELECT, REFERENCES ON "HR"."EMPLOYEES" TO "OE";  
  GRANT SELECT ON "HR"."JOB_HISTORY" TO "OE";  
  GRANT READ, WRITE ON DIRECTORY "SS_OE_XMLDIR" TO "OE" WITH GRANT OPTION;  
  GRANT READ, WRITE ON DIRECTORY "SUBDIR" TO "OE" WITH GRANT OPTION;  
  GRANT EXECUTE ON "SYS"."DBMS_STATS" TO "OE";  
  GRANT SELECT, REFERENCES ON "HR"."COUNTRIES" TO "OE";  
  GRANT SELECT, REFERENCES ON "HR"."LOCATIONS" TO "OE";  
  GRANT SELECT ON "HR"."DEPARTMENTS" TO "OE";  
  GRANT SELECT ON "HR"."JOBS" TO "OE";  
  GRANT SELECT, REFERENCES ON "HR"."EMPLOYEES" TO "OE";  
  GRANT SELECT ON "HR"."JOB_HISTORY" TO "OE";  
  GRANT READ, WRITE ON DIRECTORY "SS_OE_XMLDIR" TO "OE" WITH GRANT OPTION;  
  GRANT READ, WRITE ON DIRECTORY "SUBDIR" TO "OE" WITH GRANT OPTION;  
   ALTER USER "EMON" DEFAULT ROLE ALL;  
   ALTER USER "ORAPROBE" DEFAULT ROLE ALL;  
   ALTER USER "SCOTT2" DEFAULT ROLE ALL;  
   ALTER USER "FAR" DEFAULT ROLE ALL;  
   ALTER USER "SH" DEFAULT ROLE ALL;  
   ALTER USER "OE" DEFAULT ROLE ALL;  
   ALTER USER "HR" DEFAULT ROLE ALL;  
   ALTER USER "SCOTT" DEFAULT ROLE ALL;  
 HalimDba@SQL>  
 HalimDba@SQL>  
 HalimDba@SQL>  
 HalimDba@SQL>  
 HalimDba@SQL>  
 HalimDba@SQL>  
 HalimDba@SQL>spool off  
 HalimDba@SQL>  
 HalimDba@SQL>ed make_script_for_mig_user.sql  



Monday, October 1, 2012

When I should rebuild the index in oracle database?

You should not rebuild indexes based on a schedule.

Rather You will/can rebuild an index in response to Identified and
Degraded performance (on a period). It is the RARE index that needs to be rebuilt.



See more....
https://forums.oracle.com/forums/thread.jspa?threadID=626809&start=0&tstart=0
https://forums.oracle.com/forums/thread.jspa?messageID=2017174&
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:6601312252730
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:2913600659112
http://richardfoote.files.wordpress.com/2007/12/index-internals-rebuilding-the-truth.pdf
http://www.jlcomp.demon.co.uk/index_efficiency.html
http://www.jlcomp.demon.co.uk/index_efficiency_2.html
http://jonathanlewis.wordpress.com/index-efficiency-3/
http://jonathanlewis.wordpress.com/2008/09/26/index-analysis
http://www.orafaq.com/wiki/Oracle_database_Performance_Tuning_FAQ#When_should_one_rebuild_an_index.3F
http://webcache.googleusercontent.com/search?q=cache:80JD78CDuH0J:www.dba-oracle.com/art_index1.htm+When+I+should+rebuild+the+index+in+oracle+database%3F&cd=1&hl=en&ct=clnk
https://blogs.oracle.com/sysdba/entry/when_to_rebuild_index

Will reclaim space from tablespace by rebuilding the index in oracle database?

Someone asked that,
"We want to reclaim space, so we will rebuild all of the indexes that have too much unused space. "

Answer is (from Tom kytes quote) :-
You can’t expect it, The index is right back the way it was. Because the system got back to where the system actually wanted to be. So indexes just grow all over again .You would just be wasting your time to rebuild it again causing this vicious cycle to repeat itself.

By the by, rebuilding the index on the system might have these effects:

o the system would generate 4.5 times the redo ( for some times/days)
o the system would run slower
o the system would consume more resources (CPU, IO, latching, etc)
o the system would not be able to handle the same user load

Tom kytes also says, “this is (reclaim space) the funniest reason to rebuild index, since they just get fat anyway -- all but the degenerate cases stop growing. if you put them on a diet by rebuilding, they just get fat again anyway”


See here more about index rebuild
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:6601312252730
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:2913600659112
https://forums.oracle.com/forums/thread.jspa?messageID=2017174&