CHARACTER SET in Database

A character set determines what languages can be represented in the database.

Oracle recommends Unicode AL32UTF8 as the database character set. Unicode is the universal character set that supports most of the currently spoken languages of the world.

SQL> select PARAMETER,VALUE from nls_database_parameters where PARAMETER like '%NLS_CHARACTERSET%';

PARAMETER                                VALUE

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

NLS_CHARACTERSET                         AL32UTF8

In EBS application also we can find character set information in System Administrator--Oracle Applications Manager---SiteMap--System Configuration--License Manager--Languages

how to find database is running with pfile or spfile?

SQL> select decode(value,null,'PFILE','SPFILE') "init file type" from sys.v_$parameter where name='spfile';

init f
------
PFILE

or

SQL> show parameter spfile;

NAME                                 TYPE        VALUE

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

spfile                               string

if it doesnt returns any value it means the database is started with pfile.

validating the front end user in oracle apps R12?

If we need to validate the front end user password is working fine or not then we can fire the below query to check the authentication of password.

Login to Database as Apps user.

SQL> select fnd_web_sec.validate_login('GUEST','welcome') from dual;

FND_WEB_SEC.VALIDATE_LOGIN('GUEST','WELCOME')
--------------------------------------------------------------------------------
Y

 

query to check work flow mailer status?

SELECT component_status
  FROM apps.fnd_svc_components
 WHERE component_id =
          (SELECT component_id
             FROM apps.fnd_svc_components
            WHERE component_name = 'Workflow Notification Mailer');



                                                                          (or)


select component_status from apps.fnd_svc_components where component_name='Workflow Notification Mailer';

unable to launch the java virtual machine sqldeveloper?

while installing sqldeveloper if you face this error.follow the below steps to resolve this issue:-

copy the mscvcr100.dll file from C:\Users\subbu\Downloads\sqldeveloper-4.2.0.16.356.1154-x64\sqldeveloper\jdk\jre\bin\msvcr100.dll  to C:\sqldeveloper\sqldeveloper\bin\

ORA-27154,ORA-27300,ORA-27301,ORA-27302?

while starting the database if we get the following errors

ORA-27154 : past/wait create failed.
ORA-27300 : OS System dependent operation segment failed.
ORA-27301 : OS failure msg : no space left on device
ORA -27302 : failure occurred at :sskgpsemsper

sql>exit

go to root user----> vi /etc/sysctl.conf

As per the oracle the semaphore value should be set to

kernel.sem=250 32000 100 200

can we change characterset and tablespace block size after database creation?

If Tablespace block size is showing 4096.It is because while creating the database we had given db_block_size=4096,so after creation of database we cannot able to change the block size to 8192.
And also we cannot able to change the change the characterset after the database creation.

If you want to find the block size of tablespaces?

select block_size from dba_tablespaces;

If you want to find the characterset of the database?

select * from NLS_DATABASE_PARAMETERS;

select * from NLS_DATABASE_PARAMETERS where parameter='NLS_CHARACTERSET';