Ferhat's Blog

There will be only one database

Archive for July, 2010

11gr2 linux 8080 port used by XDB and APEX has lost after reboot

Posted by fsengonul on July 25, 2010

On my RAC system , port 8080 does not open after the reboot of the machine.  The listener endpoint for APEX is lost.

All that needs to be done to re-establish the dropped listener endpoint for APEX is to toggle the APEX port using EXEC DBMS_XDB.SETHTTPPORT to a free port, then back to the defined port.

SQL> EXEC DBMS_XDB.SETHTTPPORT(8090); <– Pick any available port
SQL> EXEC DBMS_XDB.SETHTTPPORT(8080); <– Return to the established APEX port
netstat -an | grep 8080 will show that APEX has returned.

I haven’t try it on non-RAC systems.

Posted in apex | Leave a Comment »

To show NLS parameters for session-database-instance together by Pivot and Listagg on 11gr2

Posted by fsengonul on July 21, 2010

I’ve always confused and needed to check for the nls parameters. I thing it’s a good chance to play around with the pivot function on 11gR2 in order to show all of them together.

The pivot function seems to be used only with aggregate functions. How can the text values be aggregated?  At this point a new LISTAGG function ,which also comes with 11gr2, is really useful.

After that I have the problem to show the results on terminator. The headings last forever when I do not use aliases on the in clause of pivot. And lastly do not forget to use double quotes for SESSION, just because it is a reserved word.
script:

col "SESSION" format a30
col DATABASE  format a30
col INSTANCE  format a30
select * from
(select 'SESSION' SCOPE,nsp.* from nls_session_parameters nsp
union
select 'DATABASE' SCOPE,ndp.* from nls_database_parameters ndp
union
select 'INSTANCE' SCOPE,nip.* from nls_instance_parameters nip
) a
pivot  (LISTAGG(VALUE) WITHIN GROUP (ORDER BY SCOPE)
FOR SCOPE
in ('SESSION' as "SESSION",'DATABASE' as DATABASE,'INSTANCE' as INSTANCE));
.
And the output:
22:22:53 SQL> @nls
PARAMETER                      SESSION                        DATABASE                       INSTANCE
------------------------------ ------------------------------ ------------------------------ ------------------------------
NLS_COMP                       BINARY                         BINARY                         BINARY
NLS_SORT                       BINARY                         BINARY
NLS_CALENDAR                   GREGORIAN                      GREGORIAN
NLS_CURRENCY                   TL                             $
NLS_LANGUAGE                   AMERICAN                       AMERICAN                       AMERICAN
NLS_TERRITORY                  TURKEY                         AMERICA                        AMERICA
NLS_DATE_FORMAT                YYYYMMDD HH24:MI:SS            DD-MON-RR
NLS_TIME_FORMAT                HH24:MI:SSXFF                  HH.MI.SSXFF AM
NLS_CHARACTERSET                                              WE8ISO8859P9
NLS_ISO_CURRENCY               TURKEY                         AMERICA
NLS_DATE_LANGUAGE              AMERICAN                       AMERICAN
NLS_DUAL_CURRENCY              YTL                            $
NLS_RDBMS_VERSION                                             11.2.0.1.0
NLS_TIME_TZ_FORMAT             HH24:MI:SSXFF TZR              HH.MI.SSXFF AM TZR
NLS_NCHAR_CONV_EXCP            FALSE                          FALSE                          FALSE
NLS_LENGTH_SEMANTICS           BYTE                           BYTE                           BYTE
NLS_TIMESTAMP_FORMAT           DD/MM/RRRR HH24:MI:SSXFF       DD-MON-RR HH.MI.SSXFF AM
NLS_NCHAR_CHARACTERSET                                        AL16UTF16
NLS_NUMERIC_CHARACTERS         ,.                             .,
NLS_TIMESTAMP_TZ_FORMAT        DD/MM/RRRR HH24:MI:SSXFF TZR   DD-MON-RR HH.MI.SSXFF AM TZR
20 rows selected.
Elapsed: 00:00:00.04

Democracy is the recurrent suspicion that more than half of the people are right more than half of the time.            — E. B. White

Posted in oracle | Tagged: | 4 Comments »