Missing trailing slash in listener.ora caused ORA-27101 when attempting to connect

Not too long ago, I got an error when connecting to my database using TOAD:

ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
IBM AIX RISC System/6000 Error: 2: No such file or directory

However, when connecting to the same database with sqlplus from the command line from a remote client, I could connect without errors.


Doc ID 1296982.1 "DVCA receives ORA-01034, ORA-27101" pointed me in the right direction:


"The value of ORACLE_HOME (or ORACLE_SID) passed to the DVCA utility does not match the value of ORACLE_HOME (or ORACLE_SID) that was in effect when the instance was started.

The shared memory segment key for an Oracle instance uses a hashed value based on the contents of ORACLE_HOME and ORACLE_SID. So, if one or the other of these values does not match what was used to start the instance, the resulting hash will not match, and one will encounter "ORA-27101: shared memory realm does not exist" when trying to connect to the instance.

This can commonly be caused by the presence (or lack thereof) of trailing slashes in the string for ORACLE_HOME, as passed to the -oh parameter of DVCA."

I changed my listener.ora file to read as follows:

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = mydb)
      (ORACLE_HOME = /u01/oracle/product/11204 )
      (SID_NAME = mydb)
    )
  )

to

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = mydb)
      (ORACLE_HOME = /u01/oracle/product/11204/) <-- Note the trailing slash character at the end of the ORACLE_HOME path
      (SID_NAME = mydb)
    )
  )

Reload the listener with lsnrctl reload, and the listener once again accepted connections from TOAD.

How to list partitions and their sizes

Using an inline view with a correlated subquery:

set lines 200
col partition_name format a40
SELECT P.PARTITION_NAME, (  SELECT ROUND(SUM(BYTES)/1024/1024/1024)
                            FROM DBA_SEGMENTS S
                            WHERE S.PARTITION_NAME = P.PARTITION_NAME
                            AND SEGMENT_NAME='&&TABLE_NAME') "size GB"
FROM DBA_TAB_PARTITIONS P
WHERE P.TABLE_NAME = '&&TABLE_NAME'
ORDER BY P.PARTITION_POSITION ASC;

Example output:

sqlplus / as sysdba @get_size.sql
Enter value for table_name: ARCHIVED_DOCUMENTS


PARTITION_NAME                      size GB
------------------------------   ----------
DOKARCHIVE1                           2.875
DOKARCHIVE2                               3
DOKARCHIVE3                               3
DOKARCHIVE4                               3
DOKARCHIVE5                               3
DOKARCHIVE6                          2.8125
DOKARCHIVE7                            2.75

Or, limit the output to specific partitions:

select s.partition_name,s.tablespace_name, p.compression,p.num_rows,Round(sum(s.bytes)/1024/1024/1024,1) "GB"
from dba_segments S join dba_tab_partitions p
on (s.partition_name = p.partition_name)
where s.segment_name='SALES_ARCHIVE'
and   s.owner='SH'
and s.partition_name in ( 'P_2015_01','P_2016_01','P_2017_01')
group by s.partition_name,s.tablespace_name,p.compression,p.num_rows
order by 1;

Example output:

PARTITION_NAME	TABLESPACE_NAME	COMPRESSION	NUM_ROWS	GB
P_2015_01	SALES_2015	ENABLED	22077874	2,3
P_2016_01	SALES_2016	ENABLED	394694450	42,3
P_2017_01	SALES_2017	ENABLED	481708328	48,9

If your partitions involve lob columns, make sure you fetch the sizes of the lob partitions, too.