Oracle Developer 6i forms and reports client unable to connect with oracle database 11g r2 64 bits by default

Oracle developer 6i wasn't able to connect at (11.2.0.1) oracle database with default multi-byte CHARACTER SET "AL32UTF8" .
altering the single-byte CHARACTER SET as "WE8MSWIN1252" then developer able to connected.

(From some pc's developer 6i builder just flash out (close)
and from another pc its says form designer need to close because of  unable to run .)

Info:-
oracle database version
---------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0    Production
TNS for 64-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

OS is :- Windows enterprize server 2008 R2




Developer forms builder 6i.
 
Below are the steps to alter the CHARACTER SET.
(first test it at testing server)

SQL> shutdown immediate
SQL> startup mount
SQL> alter system enable restricted session;
SQL> alter system set job_queue_processes=0;
SQL> alter system set aq_tm_processes=0;
SQL> alter database open;

SQL> ALTER DATABASE CHARACTER SET WE8MSWIN1252;
SQL> shutdown immediate
SQL> startup


To see the value of CHARACTER SET

SELECT value$ FROM sys.props$ WHERE name = 'NLS_CHARACTERSET' ;



or

SELECT * FROM NLS_DATABASE_PARAMETERS;

[Note :-

"ALTER DATABASE CHARACTER SET INTERNAL_USE WE8MSWIN1252"

It strongly recommend that you not use the INTERNAL_USE clause unless Oracle support explicitly tells you to do so. As the name implies, INTERNAL_USE is not properly documented and is not designed to be used by customers in the field.

To change the NLS_CHARACTERSET or NLS_NCHAR_CHARACTERSET by updating props$ . This is NOT supported and WILL corrupt your database. This is one of the best way's to destroy a complete dataset. Oracle Support will TRY to help you out of this but Oracle will NOT warrant that the data can be recoverd or recovered data is correct. Most likely you WILL be asked to do a FULL export and a COMPLETE rebuild of the database.
225912.1. ]

EXP-00011: SCOTT.BONUS does not exist when exporting...

exp and imp is not fully supported in oracle 11g, try to use expdp and impdp
just an example is
 
"Deferred segment creation in oracle 11g is the "exp" utility doesn't support it properly. Tables with no segments don't get exported as expected. One solution is to turn off the functionality before creating any objects using the following command.

ALTER SYSTEM SET DEFERRED_SEGMENT_CREATION=FALSE;

Alternatively, you can force the allocation of extents on any table with no rows using the following command.

ALTER TABLE tablename ALLOCATE EXTENT;



workaround or live example
==========================

 SQL*Plus: Release 11.2.0.1.0 Production on Wed Dec 26 13:02:41 2012  
 Copyright (c) 1982, 2010, Oracle. All rights reserved.  
 SQL>  
 SQL> $exp system/system@orcl_3 file='d:/scott.dmp'  
 tables=scott.emp,scott.dept,scott.bonus  
 Export: Release 11.2.0.1.0 - Production on Wed Dec 26 13:03:44 2012  
 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights  
 reserved.  
 Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 -  
 64bit Production  
 With the Partitioning, OLAP, Data Mining and Real Application Testing  
 options  
 Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character  
 set  
 About to export specified tables via Conventional Path ...  
 Current user changed to SCOTT  
 . . exporting table              EMP     14 rows  
 exported  
 . . exporting table              DEPT     4 rows  
 exported  
 EXP-00011: SCOTT.BONUS does not exist  
 Export terminated successfully with warnings.  
 SQL>  
 SQL>  
 SQL>  
 SQL> conn scott/tiger@orcl_3  
 Connected.  
 SQL>  
 SQL>  
 SQL>  
 SQL>  
 SQL> ALTER TABLE bonus ALLOCATE EXTENT;  
 Table altered.  
 SQL>  
 SQL>  
 SQL>  
 SQL> $exp system/system@orcl_3 file='d:/scott.dmp'  
 tables=scott.emp,scott.dept,scott.bonus  
 Export: Release 11.2.0.1.0 - Production on Wed Dec 26 13:10:06 2012  
 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights  
 reserved.  
 Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 -  
 64bit Production  
 With the Partitioning, OLAP, Data Mining and Real Application Testing  
 options  
 Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character  
 set  
 About to export specified tables via Conventional Path ...  
 Current user changed to SCOTT  
 . . exporting table              EMP     14 rows  
 exported  
 . . exporting table              DEPT     4 rows  
 exported  
 . . exporting table             BONUS     0 rows  
 exported  
 Export terminated successfully without warnings.  
 SQL>  

ORA-27154: post/wait create failed(CRS-4535 and related to ASM instance) on oracle RAC 11g

ORA-27154: post/wait create failed(CRS-4535 and related to ASM instance)

Workaround
==========

 # crs_stat -t  
 CRS-0184: Cannot communicate with the CRS daemon.  
 # crsctl check crs  
 CRS-4638: Oracle High Availability Services is online  
 CRS-4535: Cannot communicate with Cluster Ready Services  
 CRS-4529: Cluster Synchronization Services is online  
 CRS-4533: Event Manager is online 

 after seeing the grid log and CRSD log, 

 i found that +ASM2 instance is not starting up after reboot. 

 so i tried it manually but not started.  

  $ export ORACLE_SID=+ASM2  
 $ amdcmd  
 ASMCMD> ls  
 ASMCMD-08102: no connection to ASM; command requires ASM to run"  

  Whenever it is tried to start ASM instance manually on the second node 

  below error is shown.  
 

amdcmd> startup;  
 ORA-27154: post/wait create failed  
 ORA-27300: OS system dependent operation:semget failed with status: 28  
 ORA-27301: OS failure message: No space left on device  
 ORA-27302: failure occurred at: sskgpsemsper  

Solution:-

Problem is lower settings of semaphores(/etc/sysctl.conf) value so solution is to increase the semaphores value.

like below......

# vi /etc/sysctl.conf
kernel.sem = 250 32000 100 288

On Red Hat Linux system, in order the affect the setting of the value immediately use,

# /sbin/sysctl -p

Oracle Database Query execution plan with runtime statistics

Normal execution plan does not give you run-time of each pieces of any execution plan, time which is reported is not exactly real elapsed time of SQL statement.

you can find execution plan with run time (which could be used greatly in SQL tuning) by following ways

1) excute the query with " /*+gather_plan_statistics */ " hints .
then see the execution plan with
select * from table(dbms_xplan.display_cursor(format=>'allstats last'));

2) set STATISTICS_LEVEL to all like " ALTER SESSION SET STATISTICS_LEVEL = ALL ; "
then see the execution plan with
select * from table(dbms_xplan.display_cursor(format=>'allstats last'));

following are the demonstration with example........

 SQL*Plus: Release 11.2.0.1.0 Production on Mon Nov 5 15:02:55 2012  
 Copyright (c) 1982, 2010, Oracle. All rights reserved.  
 SQL> conn sys@orcl251 as sysdba  
 Enter password:  
 Connected.  
 SQL>  
 SQL>  
 SQL> set linesize 1000  
 SQL> set pagesize 1000  
 SQL>  
 SQL>  
 SQL>  
 SQL> select * from scott.emp where deptno=10 ;  
    EMPNO ENAME   JOB       MGR HIREDATE     SAL    COMM   DEPTNO  
 ---------- ---------- --------- ---------- --------- ---------- ---------- ----------  
    7782 CLARK   MANAGER     7839 09-JUN-81    2450          10  
    7839 KING    PRESIDENT      17-NOV-81    5000          10  
    7934 MILLER   CLERK      7782 23-JAN-82    1300          10  
 SQL>  
 SQL>  
 SQL>  
 SQL> select * from table(dbms_xplan.display_cursor(format=>'allstats last'));  
 PLAN_TABLE_OUTPUT  
 ----------------------------------------------------------------------------------------------  
 ----------------------------------------------------------------------------------------------  
 ----------------------------------------------------------------------------------------------  
 SQL_ID fhr3t48g0hgqu, child number 0  
 -------------------------------------  
 select * from scott.emp where deptno=10  
 Plan hash value: 3956160932  
 -------------------------------------------  
 | Id | Operation     | Name | E-Rows |  
 -------------------------------------------  
 |  0 | SELECT STATEMENT |   |    |  
 |* 1 | TABLE ACCESS FULL| EMP |   6 |  
 -------------------------------------------  
 Predicate Information (identified by operation id):  
 ---------------------------------------------------  
   1 - filter("DEPTNO"=10)  
 Note  
 -----  
   - Warning: basic plan statistics not available. These are only collected when:  
     * hint 'gather_plan_statistics' is used for the statement or  
     * parameter 'statistics_level' is set to 'ALL', at session or system level  
 24 rows selected.  
 SQL>  
 SQL>  
 SQL>  
 SQL>  
 SQL>  
 SQL>  
 SQL>  
 SQL>  
 SQL>  
 SQL>  
 SQL> select /*+gather_plan_statistics */ * from scott.emp where deptno=10  
  2 ;  
    EMPNO ENAME   JOB       MGR HIREDATE     SAL    COMM   DEPTNO  
 ---------- ---------- --------- ---------- --------- ---------- ---------- ----------  
    7782 CLARK   MANAGER     7839 09-JUN-81    2450          10  
    7839 KING    PRESIDENT      17-NOV-81    5000          10  
    7934 MILLER   CLERK      7782 23-JAN-82    1300          10  
 SQL>  
 SQL>  
 SQL> select * from table(dbms_xplan.display_cursor(format=>'allstats last'));  
 PLAN_TABLE_OUTPUT  
 ----------------------------------------------------------------------------------------------  
 ----------------------------------------------------------------------------------------------  
 ----------------------------------------------------------------------------------------------  
 SQL_ID 9j35xrpsjp76j, child number 0  
 -------------------------------------  
 select /*+gather_plan_statistics */ * from scott.emp where deptno=10  
 Plan hash value: 3956160932  
 ------------------------------------------------------------------------------------  
 | Id | Operation     | Name | Starts | E-Rows | A-Rows |  A-Time  | Buffers |  
 ------------------------------------------------------------------------------------  
 |  0 | SELECT STATEMENT |   |   1 |    |   3 |00:00:00.01 |    8 |  
 |* 1 | TABLE ACCESS FULL| EMP |   1 |   6 |   3 |00:00:00.01 |    8 |  
 ------------------------------------------------------------------------------------  
 Predicate Information (identified by operation id):  
 ---------------------------------------------------  
   1 - filter("DEPTNO"=10)  
 18 rows selected.  
 SQL>  
 SQL>  
 SQL>  
 SQL>  
 SQL>  
 SQL>  
 SQL>  
 SQL>  
 SQL>  
 SQL>  
 SQL>  
 SQL>  
 SQL>  
 SQL> select /*+gather_plan_statistics */ * from scott.emp where deptno=10  
  2 ...  
  3 ..  
  4  
 SQL>  
 SQL>  
 SQL> ALTER SESSION SET STATISTICS_LEVEL = ALL;  
 Session altered.  
 SQL>  
 SQL>  
 SQL> select * from scott.emp where deptno=10 ;  
    EMPNO ENAME   JOB       MGR HIREDATE     SAL    COMM   DEPTNO  
 ---------- ---------- --------- ---------- --------- ---------- ---------- ----------  
    7782 CLARK   MANAGER     7839 09-JUN-81    2450          10  
    7839 KING    PRESIDENT      17-NOV-81    5000          10  
    7934 MILLER   CLERK      7782 23-JAN-82    1300          10  
 SQL>  
 SQL>  
 SQL> select * from table(dbms_xplan.display_cursor(format=>'allstats last'));  
 PLAN_TABLE_OUTPUT  
 ----------------------------------------------------------------------------------------------  
 ----------------------------------------------------------------------------------------------  
 ----------------------------------------------------------------------------------------------  
 SQL_ID fhr3t48g0hgqu, child number 1  
 -------------------------------------  
 select * from scott.emp where deptno=10  
 Plan hash value: 3956160932  
 ------------------------------------------------------------------------------------  
 | Id | Operation     | Name | Starts | E-Rows | A-Rows |  A-Time  | Buffers |  
 ------------------------------------------------------------------------------------  
 |  0 | SELECT STATEMENT |   |   1 |    |   3 |00:00:00.01 |    8 |  
 |* 1 | TABLE ACCESS FULL| EMP |   1 |   6 |   3 |00:00:00.01 |    8 |  
 ------------------------------------------------------------------------------------  
 Predicate Information (identified by operation id):  
 ---------------------------------------------------  
   1 - filter("DEPTNO"=10)  
 18 rows selected.  
 SQL>  

A query to check that which oracle table have data and which table is empty?

The following query give the solution that which oracle table have data and which table is empty?

 select table_name,  
      extractvalue(  
       dbms_xmlgen.getXMLtype  
        ('SELECT 1 val FROM dual WHERE EXISTS(SELECT ''x'' FROM '||table_name||')'),  
         '/ROWSET/ROW/VAL') rows_in_table  
   from user_tables  
   where -- a real table  
    ( tablespace_name is not null or partitioned='YES' or nvl(iot_type,'NOT_IOT')='IOT' )  
    -- not an iot overflow  
  and nvl(iot_type,'NOT_IOT') not in ('IOT_OVERFLOW','IOT_MAPPING')   
    -- not a mview container  
  and (user, table_name) not in (select owner, container_name from user_mviews)  
    -- not a mview log  
  and (user, table_name) not in (select log_owner, log_table from user_mview_logs)  
   order by 1  

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  

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&

Oracle Database 12c will be announced

Oracle will announce Oracle Database 12c at OpenWorld this year, the first major upgrade of its flagship product since 11g R2 came out in 2009.

see here more
http://searchoracle.techtarget.com/news/2240163735/Oracle-Database-12c-to-be-announced-at-OpenWorld?asrc=EM_NLT_18901039&track=NL-94&ad=882235&

ORA-00843: Parameter not taking MEMORY_MAX_TARGET into account, ORA-00849

ORA-00843: Parameter not taking MEMORY_MAX_TARGET into account
ORA-00849: SGA_TARGET 4512632 cannot be set to more than MEMORY_MAX_TARGET 0.



Solution:-


Don't set memory_target and memory_max_target parameter to 0 (zero) explicitly.

if above error happened, you can do following...

1) Restore old spfile

or

2) create pfile from parameter value taken from alert log.

or

3) create pfile from spfile and remove the MEMORY_MAX_TARGET=0 and MEMORY_TARGET=0 lines. After that, use the modified PFILE to create a new SPFILE and start the instance with this new setup.
then use the following commands to remove the explicit setting of MEMORY_TARGET=0 and MEMORY_MAX_TARGET=0:

alter system reset memory_target;
alter system reset memory_max_target;

ORA-00600: internal error code, arguments: [kcratr_nab_less_than_odr]

It is for instance crash for power failure/instance aborted.
so control file has been corrupted.

So solution is :-
======================

[Before trying any kind of recovery, cold backup the entire database first]


1) SQL> conn / as sysdba

2) SQL> shutdown immediate ;

3) SQL> startup mount ;

4) SQL>

SELECT a.MEMBER, a.group#, b.status
FROM v$logfile a, v$log b
WHERE a.group# = b.group# AND b.status = 'CURRENT'

5) SQL> shutdown abort ;

6) SQL> startup mount ;

7) SQL> recover database using backup controlfile until cancel ;

Enter location(a.MEMBER) of redo log what found by 4) number point.
if it is not in archivelog mode. if it is in archivelog mode please enter the asking archivelog location. then press enter

it will show.......
Log applied.
Media recovery complete.

8) SQL> Alter database open resetlogs ;

9) Take hot backup of the database immediate.

ORA-09817: Write to audit file failed

its because of full the Mount point where Oracle is installed .
check ..

$ df -k

and make some space where 100% is full.

for details see here
http://halimdba.blogspot.com/2011/10/ora-09945-unable-to-initialize-audit.html

How to insert image/pdf/multimedia or blob data to oracle database table from client machine without using database directory ?

steps are below..

1) first i have created a "d:/sql_ld_control_file.ctl" file for sql loader. contents are belows (no enter/line break is allowed in control file). here i am inserting four image files in t_image table.
=================================================
"d:/sql_ld_control_file.ctl"
---------------------------------

LOAD DATA
INFILE *
INTO TABLE scott.t_image
REPLACE
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
(
file_id INTEGER EXTERNAL(5),
fn BOUNDFILLER,
file_name "SUBSTR(:fn,INSTR(:fn,'\\',-1,1)+1,INSTR(:fn,'.')-INSTR(:fn,'\\',-1,1)-1)",
file_data LOBFILE (fn)
TERMINATED BY EOF
)
BEGINDATA
1,c:\image\Blue hills.jpg
2,c:\image\Sunset.jpg
3,c:\image\Water lilies.jpg
4,c:\image\Winter.jpg

------------------------------------
[note: file_name "SUBSTR(:fn,INSTR(:fn,'\\',-1,1)+1,INSTR(:fn,'.')-INSTR(:fn,'\\',-1,1)-1)",   will be in one line .]


2) follow the below steps
==================================
SQL*Plus: Release 10.2.0.1.0 - Production on Tue Jul 10 12:28:53 2012

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

SQL> conn scott@orcl251
Enter password:
Connected.
SQL>
SQL>
SQL> drop table t_image ;

Table dropped.

SQL>
SQL> CREATE TABLE t_image
  2   (file_id    NUMBER(5),
  3     file_name  VARCHAR2(30),
  4     file_data  BLOB)
  5  /

Table created.

SQL>
SQL>
SQL>
SQL> HOST SQLLDR scott/tiger@orcl251 CONTROL=d:/sql_ld_control_file.ctl

SQL*Loader: Release 10.2.0.1.0 - Production on Tue Jul 10 12:34:59 2012

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Commit point reached - logical record count 3
Commit point reached - logical record count 4

SQL>
SQL>
SQL> SELECT file_id, file_name, DBMS_LOB.GETLENGTH (file_data) length
  2     FROM   t_image ;

   FILE_ID FILE_NAME                          LENGTH
---------- ------------------------------ ----------
         1 Blue hills                          28521
         2 Sunset                              71189
         3 Water lilies                        83794
         4 Winter                             105542

SQL>
SQL>
SQL>
SQL>
SQL> host
Microsoft Windows XP [Version 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.

C:\Documents and Settings\Administrator>

C:\Documents and Settings\Administrator>cd ..

C:\Documents and Settings>cd ..

C:\>

C:\>cd image

C:\image>dir

 Volume in drive C has no label.
 Volume Serial Number is 04B1-1B54

 Directory of C:\image

07/10/2012  12:33 PM   



          .
07/10/2012  12:33 PM              ..
04/14/2008  08:00 PM            28,521 Blue hills.jpg
04/14/2008  08:00 PM            71,189 Sunset.jpg
04/14/2008  08:00 PM            83,794 Water lilies.jpg
04/14/2008  08:00 PM           105,542 Winter.jpg
               4 File(s)        289,046 bytes
               2 Dir(s)   8,457,371,648 bytes free

C:\image>
C:\image>
C:\image>
C:\image>
C:\image>
C:\image>




Extra steps for Getting name list from command prompt (in windows)
======================================================


C:\image>
C:\image>
C:\image>
C:\image>dir *.jpg
 Volume in drive C has no label.
 Volume Serial Number is 04B1-1B54

 Directory of C:\image

04/14/2008  08:00 PM            28,521 Blue hills.jpg
04/14/2008  08:00 PM            71,189 Sunset.jpg
04/14/2008  08:00 PM            83,794 Water lilies.jpg
04/14/2008  08:00 PM           105,542 Winter.jpg
               4 File(s)        289,046 bytes
               0 Dir(s)   8,457,371,648 bytes free

C:\image>
C:\image>
C:\image>
C:\image>dir /b *.jpg > image_name_list.txt

C:\image>
C:\image>type image_name_list.txt
Blue hills.jpg
Sunset.jpg
Water lilies.jpg
Winter.jpg

C:\image>
C:\image>
C:\image>
C:\image>
C:\image>dir
 Volume in drive C has no label.
 Volume Serial Number is 04B1-1B54

 Directory of C:\image

07/10/2012  12:41 PM              .
07/10/2012  12:41 PM              ..
04/14/2008  08:00 PM            28,521 Blue hills.jpg
07/10/2012  12:41 PM                58 image_name_list.txt
04/14/2008  08:00 PM            71,189 Sunset.jpg
04/14/2008  08:00 PM            83,794 Water lilies.jpg
04/14/2008  08:00 PM           105,542 Winter.jpg
               5 File(s)        289,104 bytes
               2 Dir(s)   8,457,367,552 bytes free

C:\image>
C:\image>
C:\image>

How to insert multimedia or blob via directory see below..
http://halimdba.blogspot.com/2011/01/how-to-insert-multimedia-data-image.html

How to save blob data to disk see below..
http://halimdba.blogspot.com/2012/06/how-to-save-blob-data-to-disk-from.html

How to save blob data to disk from oracle table using UTL_FILE ?

How to Insert multimedia data (image, video) in oracle table

How to insert image/pdf/multimedia or blob data to oracle database table from client machine without using database directory ?

1)Create Directory Where multimedia resides.
===============================================


sql> create or replace directory temp as 'C:/dir_blob';


2)Grant read permission to the user who work with this directory.
===========================================================

sql> grant read on directory temp to test;


3)Create the Table which holds Lov object.

-- the storage table for the image file
CREATE TABLE image (
dname VARCHAR2(30), -- directory name
sname VARCHAR2(30), -- subdirectory name
fname VARCHAR2(30), -- file name
iblob BLOB); -- image file


4) insert data to table see here

http://halimdba.blogspot.com/2011/01/how-to-insert-multimedia-data-image.html



5) selecting table has multiple blob row
===========================================

SQL> select * from atmutl.image ;



DNAME SNAME FNAME IBLOB

TEMP1 This is Image Phoenix-documents.pdf (HUGEBLOB)
TEMP This is pdf1 Phoenix-documents1.pdf (HUGEBLOB)
TEMP2 This is pdf2 Phoenix-documents2.pdf (HUGEBLOB)




6) create a procedure (pass parameter directory name)
========================================================

CREATE OR REPLACE PROCEDURE atmutl.save_blob_jpg_to_disk_halim1 (
   p_directory   IN   VARCHAR2
)
IS
   v_blob        BLOB;
   v_start       NUMBER             := 1;
   v_bytelen     NUMBER             := 2000;
   v_len         NUMBER;
   v_raw         RAW (2000);
   v_x           NUMBER;
   v_output      UTL_FILE.file_type;
   v_file_name   VARCHAR2 (200);
BEGIN

   FOR i IN (SELECT DBMS_LOB.getlength (iblob) v_len, fname v_file_name,
                    iblob v_blob
               FROM atmutl.image
              WHERE ROWNUM <= 500)
  
   LOOP
      v_output :=
           UTL_FILE.fopen (p_directory, i.v_file_name || '.PDF', 'wb', 32760);
      v_x := i.v_len;
      v_start := 1;
      v_bytelen := 2000;

      WHILE v_start < i.v_len AND v_bytelen > 0
      LOOP
         DBMS_LOB.READ (i.v_blob, v_bytelen, v_start, v_raw);
         UTL_FILE.put_raw (v_output, v_raw);
         UTL_FILE.fflush (v_output);
         v_start := v_start + v_bytelen;
         v_x := v_x - v_bytelen;

         IF v_x < 2000
         THEN
            v_bytelen := v_x;
         END IF;
      END LOOP;

      UTL_FILE.fclose (v_output);
   END LOOP;
END save_blob_jpg_to_disk_halim1;
/

7) executing it   ----dirtory name is uppercase
----------------------------------------------------

EXEC atmutl.save_blob_jpg_to_disk_halim1('TEMP');

Announcing Certification of Oracle Database 11g R2 on Oracle Linux 6 and Redhat Linux 6

Oracle announce the general availability of the Oracle RDBMS Server 11gR2
Pre-Install RPM for Oracle Linux 6 x86_64 (64 Bit) architecture.

See more about

 Announcing: Oracle Database 11g R2 Certification on Oracle Linux 6
 
Public Yum Server

Oracle RDBMS Server 11gR2 Pre-Install RPM for Oracle Linux 6 x86_64 (64 Bit) architecture

Document library of oracle 11g Release 2 (11.2)

Bangladesh Government primary School teacher viva Result 2012

Government primary School teacher viva Result 2012 will publish very soon.
plz keep your eyes on

www.dpe.gov.bd

How many Maximum datafiles can i create in oracle database (MAXDATAFILES , DB_FILES)

Today i have got a question regarding error ORA-00059: maximum number of DB_FILES exceeded and MAXDATAFILES , DB_FILES

so here i just want to explain it.

showing the the values of datafiles

SQL> select value from v$parameter where name = 'db_files'


SQL> select records_total from v$controlfile_record_section where type = 'DATAFILE'

[For oracle database 8i and above)

if you attempt to add a new file whose number is greater than MAXDATAFILES, but less than or equal to DB_FILES, the MAXDATAFILES parameter of the control file will expand automatically to accommodate more files.
so, you have to change the value onle DB_FILES init parameter if you want to create more datafiles from existing. you don't have to think about MAXDATAFILES 'S VALUE . I


The DB_FILES parameter limits the maximum number of datafile can exist in oracle database.We can't change this parameter dynamically.

MAXDATAFILES parameter you can find with the CREATE DATABASE command or in CREATE CONTROLFILE command.

So, if you are after oracle 8i then we should just forget about MAXDATAFILES parameter. We should rather think about DB_FILES parameter.

How to Import data From Oracle table to CSV file or txt file?

1) Write a procedure like below......

connect as scott user and create below procedure.


---------------------------------------------------------------
CREATE OR REPLACE PROCEDURE PR_Oracle_table_to_csv_or_txt (
p_tname IN VARCHAR2,
p_dir IN VARCHAR2,
p_filename IN VARCHAR2
)
IS
l_output UTL_FILE.file_type;
l_thecursor INTEGER DEFAULT DBMS_SQL.open_cursor;
l_columnvalue VARCHAR2 (4000);
l_status INTEGER;
l_query VARCHAR2 (1000) DEFAULT 'select * from ' || p_tname;
l_colcnt NUMBER := 0;
l_separator VARCHAR2 (1);
l_desctbl DBMS_SQL.desc_tab;
BEGIN
l_output := UTL_FILE.fopen (p_dir, p_filename, 'w');

EXECUTE IMMEDIATE 'alter session set nls_date_format=''dd-mon-yyyy hh24:mi:ss'' ';

DBMS_SQL.parse (l_thecursor, l_query, DBMS_SQL.native);
DBMS_SQL.describe_columns (l_thecursor, l_colcnt, l_desctbl);

FOR i IN 1 .. l_colcnt
LOOP
UTL_FILE.put (l_output,
l_separator || '"' || l_desctbl (i).col_name || '"'
);
DBMS_SQL.define_column (l_thecursor, i, l_columnvalue, 4000);
l_separator := ',';
END LOOP;

UTL_FILE.new_line (l_output);
l_status := DBMS_SQL.EXECUTE (l_thecursor);

WHILE (DBMS_SQL.fetch_rows (l_thecursor) > 0)
LOOP
l_separator := '';

FOR i IN 1 .. l_colcnt
LOOP
DBMS_SQL.COLUMN_VALUE (l_thecursor, i, l_columnvalue);
UTL_FILE.put (l_output, l_separator || l_columnvalue);
l_separator := ',';
END LOOP;

UTL_FILE.new_line (l_output);
END LOOP;

DBMS_SQL.close_cursor (l_thecursor);
UTL_FILE.fclose (l_output);

EXECUTE IMMEDIATE 'alter session set nls_date_format=''dd-MON-yy'' ';
EXCEPTION
WHEN OTHERS
THEN
EXECUTE IMMEDIATE 'alter session set nls_date_format=''dd-MON-yy'' ';

RAISE;
END;

-------------------------------------------------------

2) Grant create directory privileze to scott


SQL> grant CREATE ANY DIRECTORY to scott ;


3) Create a directory

create directory DIR_SCOTT as 'G:\DUMP'


4) then execute the procedure as desire file extension.


exec dump_table_to_csv( 'emp', 'DIR_SCOTT', 'emp_in_csv.csv' );


exec dump_table_to_csv( 'emp', 'DIR_SCOTT', 'emp_in_csv.txt' );

How to import/insert data from csv/excel file into a table by using Oracle forms (6i,8i,9i,10g)?

This is very easy, here i have attached a 6i forms and excel file and read me file
you can Download this (zip) form and excel file and follow the steps for this.

or you can download separately




[you can migrate this form to 8i,9i,10g forms easily, just need to compile]

1) create temp table to scott schema

2) then connect to oracle 6i form with scott/tiger user then run the form.

3) locate the csv file via browse option of the forms.

4) then click the "process to insert temp table" .

-----cheers----

How to Change current time in linux or solaris or unix

change the with "date" command .

given date format is [MMDDhhmm[[CC]YY][.ss]]

like belows

[root@RAC1 etc]#

[root@RAC1 etc]# date 052917012012

---(month 05, date 29 ,17 hour, 01 minute ,2012 year) [.12 second]

Tue May 29 17:01:00 BDT 2012

[root@RAC1 etc]#

How to Change TimeZone Using /etc/localtime File in redhat linux

---showing current time and timezone
# date

---Remove current localtime zone file
# cd /etc
# rm localtime

---Find out your time Zone information or name
# ls /usr/share/zoneinfo/Asia/

---Create an link to file localtime
# cd /etc
# ln -s /usr/share/zoneinfo/US/Pacific localtime

---at last show date and timezone again
# date

ORA-00470: LGWR process terminated with error, ora-00600 [3700]

Today i am seeing following message in my SMS Gateway sever's alert log file.
its seems problem in the file system of this server, log file can't work properly.....
so, i do a temprary solution for immediatelly solve the problem.



Sun May 27 10:31:35 2012
Shutting down instance (abort)
Sun May 27 10:31:35 2012
Starting ORACLE instance (normal)
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
Picked latch-free SCN scheme 2
Using LOG_ARCHIVE_DEST_10 parameter default value as USE_DB_RECOVERY_FILE_DEST
Autotune of undo retention is turned on.
IMODE=BR
ILAT =97
LICENSE_MAX_USERS = 0
SYS auditing is disabled
ksdpec: called for event 13740 prior to event group initialization
Starting up ORACLE RDBMS Version: 10.2.0.3.0.
System parameters with non-default values:
processes = 800
sga_max_size = 734003200
__shared_pool_size = 255852544
__large_pool_size = 4194304
__java_pool_size = 12582912
__streams_pool_size = 0
sga_target = 734003200
control_files = E:\ORACLE\PRODUCT\10.2.0\ORADATA\SMSDB\CONTROL01.CTL, E:\ORACLE\PRODUCT\10.2.0\ORADATA\SMSDB\CONTROL02.CTL, E:\ORACLE\PRODUCT\10.2.0\ORADATA\SMSDB\CONTROL03.CTL
db_block_size = 8192
__db_cache_size = 452984832
compatible = 10.2.0.3.0
db_file_multiblock_read_count= 16
db_recovery_file_dest = E:\oracle\product\10.2.0\flash_recovery_area
db_recovery_file_dest_size= 2147483648
undo_management = AUTO
undo_tablespace = UNDOTBS1
remote_login_passwordfile= EXCLUSIVE
db_domain =
dispatchers = (PROTOCOL=TCP) (SERVICE=smsdbXDB)
job_queue_processes = 20
audit_file_dest = E:\ORACLE\PRODUCT\10.2.0\ADMIN\SMSDB\ADUMP
background_dump_dest = E:\ORACLE\PRODUCT\10.2.0\ADMIN\SMSDB\BDUMP
user_dump_dest = E:\ORACLE\PRODUCT\10.2.0\ADMIN\SMSDB\UDUMP
core_dump_dest = E:\ORACLE\PRODUCT\10.2.0\ADMIN\SMSDB\CDUMP
db_name = smsdb
open_cursors = 300
pga_aggregate_target = 314572800
aq_tm_processes = 2
PMON started with pid=2, OS id=2776
PSP0 started with pid=3, OS id=3404
MMAN started with pid=4, OS id=4088
DBW0 started with pid=5, OS id=4092
LGWR started with pid=6, OS id=1916
CKPT started with pid=7, OS id=448
SMON started with pid=8, OS id=444
RECO started with pid=9, OS id=868
CJQ0 started with pid=10, OS id=1688
MMON started with pid=11, OS id=1576
Sun May 27 10:31:35 2012
starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...
MMNL started with pid=12, OS id=3276
Sun May 27 10:31:35 2012
starting up 1 shared server(s) ...
Sun May 27 10:31:35 2012
ALTER DATABASE MOUNT
Sun May 27 10:31:39 2012
Setting recovery target incarnation to 2
Sun May 27 10:31:39 2012
Successful mount of redo thread 1, with mount id 113714055
Sun May 27 10:31:39 2012
Database mounted in Exclusive Mode
Completed: ALTER DATABASE MOUNT
Sun May 27 10:31:39 2012
ALTER DATABASE OPEN
Sun May 27 10:31:40 2012
Errors in file e:\oracle\product\10.2.0\admin\smsdb\bdump\smsdb_lgwr_1916.trc:
ORA-00600: internal error code, arguments: [3700], [1], [15], [3], [3], [], [], []

Sun May 27 10:31:40 2012
Errors in file e:\oracle\product\10.2.0\admin\smsdb\bdump\smsdb_lgwr_1916.trc:
ORA-00600: internal error code, arguments: [3700], [1], [15], [3], [3], [], [], []

Sun May 27 10:31:40 2012
LGWR: terminating instance due to error 470
Sun May 27 10:31:40 2012
Errors in file e:\oracle\product\10.2.0\admin\smsdb\bdump\smsdb_dbw0_4092.trc:
ORA-00470: LGWR process terminated with error

Sun May 27 10:31:41 2012
Errors in file e:\oracle\product\10.2.0\admin\smsdb\bdump\smsdb_pmon_2776.trc:
ORA-00470: LGWR process terminated with error

Sun May 27 10:31:41 2012
Errors in file e:\oracle\product\10.2.0\admin\smsdb\bdump\smsdb_reco_868.trc:
ORA-00470: LGWR process terminated with error

Sun May 27 10:31:41 2012
Errors in file e:\oracle\product\10.2.0\admin\smsdb\bdump\smsdb_smon_444.trc:
ORA-00470: LGWR process terminated with error

Sun May 27 10:31:41 2012
Errors in file e:\oracle\product\10.2.0\admin\smsdb\bdump\smsdb_mman_4088.trc:
ORA-00470: LGWR process terminated with error

Sun May 27 10:31:41 2012
Errors in file e:\oracle\product\10.2.0\admin\smsdb\bdump\smsdb_psp0_3404.trc:
ORA-00470: LGWR process terminated with error

Sun May 27 10:31:42 2012
Errors in file e:\oracle\product\10.2.0\admin\smsdb\bdump\smsdb_ckpt_448.trc:
ORA-00470: LGWR process terminated with error

Instance terminated by LGWR, pid = 1916




===========================================================================
=============================solution======================================
===========================================================================

For thid i do the follwing steps:

SQL> CONN / AS SYSDBA
SQL> startup mount
SQL> recover database until cancel;
SQL> alter database open resetlogs;


like.........


SQL*Plus: Release 10.2.0.3.0 - Production on Sun May 27 10:28:15 2012

Copyright (c) 1982, 2006, Oracle. All Rights Reserved.

SQL> conn / as sysdba
Connected to an idle instance.
SQL> startup
ORACLE instance started.

Total System Global Area 734003200 bytes
Fixed Size 1292780 bytes
Variable Size 272631316 bytes
Database Buffers 452984832 bytes
Redo Buffers 7094272 bytes
Database mounted.
ORA-03113: end-of-file on communication channel



SQL> conn / as sysdba
Connected to an idle instance.
SQL> startup force
ORACLE instance started.

Total System Global Area 734003200 bytes
Fixed Size 1292780 bytes
Variable Size 276825620 bytes
Database Buffers 448790528 bytes
Redo Buffers 7094272 bytes
Database mounted.
ORA-03113: end-of-file on communication channel


SQL> shutdown immediate
ORA-24324: service handle not initialized
ORA-01041: internal error. hostdef extension doesn't exist
SQL>
SQL>
SQL> conn / as sysdba
Connected to an idle instance.
SQL>
SQL>
SQL> startup mount
ORACLE instance started.

Total System Global Area 734003200 bytes
Fixed Size 1292780 bytes
Variable Size 281019924 bytes
Database Buffers 444596224 bytes
Redo Buffers 7094272 bytes
Database mounted.
SQL>
SQL>
SQL> recover database until cancel ;
Media recovery complete.
SQL>
SQL>
SQL> alter database open resetlogs ;

Database altered.

SQL>
SQL>
SQL> $exp smsgtway1/smsgtway2 file=D:/smsgtway2_27052012.dmp owner=smsgtway1 ;

Export: Release 10.2.0.3.0 - Production on Sun May 27 10:42:06 2012

Copyright (c) 1982, 2005, Oracle. All rights reserved.


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Produc
tion
With the Partitioning, OLAP and Data Mining options
Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set

About to export specified users ...
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user SMSGTWAY1
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user SMSGTWAY1
About to export SMSGTWAY1's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export SMSGTWAY1's tables via Conventional Path ...
. . exporting table SMS_ALL_TRAN 66131 rows exported
. . exporting table SMS_ALL_TRAN_BA 63166 rows exported
. . exporting table SMS_ALL_TRAN_REJECT 101 rows exported
. exporting synonyms
. exporting views
. exporting stored procedures
. exporting operators
. exporting referential integrity constraints
. exporting triggers
. exporting indextypes
. exporting bitmap, functional and extensible indexes
. exporting posttables actions
. exporting materialized views
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting dimensions
. exporting post-schema procedural objects and actions
. exporting statistics
Export terminated successfully without warnings.

SQL>

Is Oracle Database 10g out of download and out of support ?

Today i am not finding any link to download oracle database 10g from oracle.com site.

Is it means, oracle database 10g is out of download and out of support ?

yes now it is out of support. oracle database 10.2 has just gone from premium to extended support. so you need a extended support to get a download.

see here more
https://forums.oracle.com/forums/thread.jspa?threadID=2393163&tstart=0
https://forums.oracle.com/forums/thread.jspa?threadID=2392802&tstart=0

This CPU is VT-capable, but VT is not enabled in Virtual Machine

when trying to install 64 bits linux os in dell server.

In Dell Server turns off Intel’s Virtualization Technology (VT) by default .

To enabling VT(Virtualization Technology) from Bios, do the following....

1) Restart your server and stroke on the F2 key repeatedly to enter BIOS
2) Navigate to the Processor section of the BIOS settings
3) Under Virtualization Technology, change the setting to "Enable"
4) Press ESC and choose to Save/Exit the BIOS

How to earn money from internet via google adsense

For this you can see here .
very prominent site.

http://arjudba.blogspot.com/2008/06/how-to-make-money-online-please-read-it.html

http://www.educationboardresults.gov.bd

http://www.educationboardresults.gov.bd

SSC Result 2012 Bangladesh

SSC Result 2012 Bangladesh at http://www.educationboardresults.gov.bd
-------------------------------------------------------------
See Your Result here

WARNING: aiowait timed out 1 times at alert log in Oracle Database server sun solaris

A formal response for Oracle DB server hanging......

subject: Possible Causes and solution of BA live Database server hang on dated 26/04/2012.

Dear sir,
Assalamu alaikum.
As you requested that, what were the causes of that hanging the DB sever
on date Thu Apr 26 14:10:15 2012.

Here i have quoted the related log file of the Database as well as timing
of occurrence.I have also described here the possible causes, solutions
and have attached technical details (reliable) .




Contents of Oracle Database alert log
=======================================

Thu Apr 26 13:02:17 2012
Thread 1 advanced to log sequence 122529 (LGWR switch)
Current log# 2 seq# 122529 mem# 0: /d01/oracle/oradata/orcl/redo02.log
Thu Apr 26 13:02:58 2012
Thread 1 advanced to log sequence 122530 (LGWR switch)
Current log# 3 seq# 122530 mem# 0: /d01/oracle/oradata/orcl/redo03.log
Thu Apr 26 13:19:03 2012
Thread 1 advanced to log sequence 122531 (LGWR switch)
Current log# 4 seq# 122531 mem# 0: /d01/oracle/oradata/orcl/redo04.log
Thu Apr 26 13:42:48 2012
Thread 1 advanced to log sequence 122532 (LGWR switch)
Current log# 5 seq# 122532 mem# 0:
/d01/oracle/oradata/orcl/redo05.log----------------------------Database
is running well.
Thu Apr 26 14:10:08 2012
WARNING: aiowait timed out 1 times------------------------DB server has
hanged on this time.
Thu Apr 26 14:10:15 2012
WARNING: aiowait timed out 1 times
Thu Apr 26 14:10:35 2012
WARNING: aiowait timed out 1 times
Thu Apr 26 14:10:08 2012
WARNING: aiowait timed out 1 times
Thu Apr 26 14:10:11 2012
WARNING: aiowait timed out 1 times
Thu Apr 26 14:10:11 2012
WARNING: aiowait timed out 1 times
Thu Apr 26 14:43:48 2012
WARNING: aiowait timed out 1 times
Thu Apr 26 14:43:49 2012
WARNING: aiowait timed out 1 times
Thu Apr 26 14:53:48 2012
WARNING: aiowait timed out 2 times
Thu Apr 26 14:53:48 2012
WARNING: aiowait timed out 2 times
Thu Apr 26 14:53:48 2012
WARNING: aiowait timed out 2 times
Thu Apr 26 15:03:48 2012
WARNING: aiowait timed out 3 times
Thu Apr 26 15:03:48 2012
WARNING: aiowait timed out 3 times
............................
.................
etc.

-------------------------------------------------------------------
In a Summary(timing):-

DB server has hanged on this time.----------- Apr 26 14:10:15 2012
Server force shutdown time------------------- Apr 26 15:04:29
Host has been powered off-------------------- Apr 26 15:22:28
Starting ORACLE instance (normal)------------ Apr 26 15:23:50 2012
Completed: DATABASE OPEN -------------------- Apr 26 15:24:33 2012


=============================================================
Cause and solution of (WARNING: aiowait timed out 1 times)
=============================================================

After a details analysis we have found that...

Oracle signals a (WARNING: aiowait timed out x times) in the alert log
file, if after waiting a reasonable amount of time the Operating System is
not completing async I/O requests made by Oracle.

If this call times out it indicates a severe problem with asynchronous I/Os
at the system/os level. For example, it can mean that an I/O was lost
(Oraclehad issued it and was waiting for it to be completed but the O/S
has no record
of it).

This particular situation is an abnormal condition usually caused by the
Operating System I/O subsystem or in some cases the related volume manager.
As a result of this condition the database may hang or crash(depends on
oracle version).

A reboot may be the only way to clear the problem for this
moment(temprorary).

Note also that a recovery may be necesary on subsequent startup of the
database.(Database may be crashed also)



so possible causes may be:-
============================

1) severe aynchronous I/O problems at the O/S level.
2) Network request timeout/latency.
3) poor comunication with Storage.
4) High load on Memory/shortness of memory to oracle SGA,PGA .

possible solution will be:-
===================

1) Need to investigate the aynchronous I/O problems at the O/S level by
the O/S vendor(may be need to be apply O/S patches to fix known
Asynchronous I/O problems.)
2) Need to investigate the networking (request timeout/latency problem) etc.
3) checking Asynchronous I/O is correctly configured at OS level as well
as with storage.
4) Allocate more memory to the Database SGA, PGA (Buffer Cache and Sort
Areas to reduce I/O from/to datafiles and temporary tablespaces).


for details please see on metalink the Attached technical documents.........

1) Warning aiowait timed out x times- Id 222989.1
2) Warning Aiowait Timed Out 1 Times - Id 743425.1
3) WARNING aiowait timed out- ID 760968.1


Regards
Muhammad Abdul Halim
Database Administrator