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