Golden Gate Error: error while loading shared libraries

===================
General
===================
For unknown reason, ggsci is throwing an error:

/software/ogg/1212/ggsci: error while loading shared libraries: libnnz11.so
/software/ogg/1212/ggsci: error while loading shared libraries: libclntsh.so.11.1

===================
Details
===================
libnnz11.so and libclntsh.so.11.1 are libraries that exist under $ORACLE_HOME
Checking $LD_LIBRARY_PATH and $ORACLE_HOME, the definition is correct.
So the reason for the error was not clear.

>% env | grep LIB
LD_LIBRARY_PATH=/usr/lib:/usr/lib:/software/oracle/112/lib:/usr/lib:/software/oracle/112/lib32

>% env | grep ORACLE_HOME
ORACLE_HOME=/software/oracle/112

===================
Solution
===================
Create soft link from Golden Gate home directory to the 2 libraries in question
Once soft links were created, issue was resolved.
/software/ogg/1212>% ln -s $ORACLE_HOME/lib/libnnz11.so libnnz11.so 
/software/ogg/1212>% ln -s $ORACLE_HOME/lib/libclntsh.so.11.1 libclntsh.so.11.1

expdp is failing with ORA-31623: a job is not attached to this session via the specified handle

=====================
General
=====================
When performing export via expdp, following error is coming:
ORA-31623: a job is not attached to this session via the specified handle

=====================
Evidences
=====================
Full error stack:
Export: Release 11.2.0.4.0 - Production on Wed May 23 07:36:34 2018

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining options

UDE-31623: operation generated ORACLE error 31623
ORA-31623: a job is not attached to this session via the specified handle
ORA-06512: at "SYS.DBMS_DATAPUMP", line 3326
ORA-06512: at "SYS.DBMS_DATAPUMP", line 4551
ORA-06512: at line 1

***************************************************
 FINISHED Export of DataBase                       
***************************************************
iu@my_server:/starhome/dbinstall/app/db/Export-Import>% 

=====================
Checking Current settings
=====================
SELECT name, ROUND(bytes/1024/1024) as MB, resizeable 
  FROM V$SGAINFO 
ORDER BY bytes DESC;

NAME                                     MB RESIZEABLE
-------------------------------- ---------- ----------
Maximum SGA Size                      65243 No        
Buffer Cache Size                     33024 Yes       
Free SGA Memory Available             22912           
Streams Pool Size                      5120 Yes       
Shared Pool Size                       3456 Yes       
Startup overhead in Shared Pool         696 No        
Large Pool Size                         384 Yes       
Java Pool Size                          256 Yes       
Granule Size                            128 No        
Redo Buffers                             89 No        
Fixed SGA Size                            2 No        
Shared IO Pool Size                       0 Yes   


SELECT name, ROUND(value/1024/1024) AS MB, description 
FROM  V$PARAMETER 
WHERE name like '%pool%';

NAME                                   MB DESCRIPTION                                       
------------------------------ ---------- -----------------------------------------
shared_pool_size                        0 size of shared pool            
large_pool_size                         0 size of large pool             
java_pool_size                          0 size of java pool               
streams_pool_size                       0 size of the streams pool       
shared_pool_reserved_size             173 size of reserved area of shared pool
buffer_pool_keep                          Number of database blocks/latches in keep
                                          buffer pool                              
buffer_pool_recycle                       Number of database blocks/latches in
                                          recycle buffer pool
global_context_pool_size                  Global Application Context Pool Size
olap_page_pool_size                     0 size of the olap page pool              

9 rows selected.


=====================
Solution
=====================
The solution was to increase the Streams Pool Size explicitly to 8Gb.

This specific system was running Several Golden Gate Replication Processes.

Although Stream Pool Size was set to 0, and Oracle was supposed to allocate optimal memory for Stream Pool, the 5Gb allocated by Oracle, was obviously not enough.

After increasing the Stream Pool Size to 8Gb, the export finished after 4 minutes, without error.


ALTER SYSTEM SET STREAMS_POOL_SIZE=8192M;  

SELECT name, ROUND(bytes/1024/1024) as MB, resizeable 
  FROM V$SGAINFO 
ORDER BY bytes DESC;

NAME                                   MB RESIZEABLE
------------------------------ ---------- ------------
Maximum SGA Size                    65243 No
Buffer Cache Size                   29952 Yes
Free SGA Memory Available           22912
Streams Pool Size                    8192 Yes
Shared Pool Size                     3456 Yes
Startup overhead in Shared Pool       696 No
Large Pool Size                       384 Yes
Java Pool Size                        256 Yes
Granule Size                          128 No
Redo Buffers                           89 No
Fixed SGA Size                          2 No
Shared IO Pool Size                     0 Yes


SELECT name, ROUND(value/1024/1024) AS MB, description 
FROM  V$PARAMETER 
WHERE name like '%pool%';
NAME                                   MB DESCRIPTION                                       
------------------------------ ---------- -----------------------------------------
shared_pool_size                        0 size of shared pool            
large_pool_size                         0 size of large pool             
java_pool_size                          0 size of java pool               
streams_pool_size                    8192 size of the streams pool       
shared_pool_reserved_size             173 size of reserved area of shared pool
buffer_pool_keep                          Number of database blocks/latches in keep
                                          buffer pool                              
buffer_pool_recycle                       Number of database blocks/latches in
                                          recycle buffer pool
global_context_pool_size                  Global Application Context Pool Size
olap_page_pool_size                     0 size of the olap page pool              

9 rows selected.

Golden Gate During dblogin error OGG-01117 and error OGG-01668 are coming

=====================
General
=====================
During dblogin error OGG-01117 and error OGG-01668 are coming

=====================
Example
=====================
oracle@my_server:/software/ogg/1212>% ./ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 12.1.2.0.0 17185003 OGGCORE_12.1.2.0.0_PLATFORMS_130924.1316_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Sep 25 2013 00:31:13
Operating system character set identified as UTF-8.

Copyright (C) 1995, 2013, Oracle and/or its affiliates. All rights reserved.
GGSCI (my_server) 1> dblogin userid ogg  password xxx

Source Context :
  SourceModule            : [ggapp.util.pcs]
  SourceID                : [/scratch/aime1/adestore/views/aime1_adc4150472/oggcore/OpenSys/src/gglib/ggapp/pcsutl.c]
  SourceFunction          : [AbendHandler]
  SourceLine              : [938]
  ThreadBacktrace         : [21] elements
                          : [/software/ogg/1212/libgglog.so(CMessageContext::AddThreadContext()+0x1e) [0x7f4acb86ddee]]
                          : [/software/ogg/1212/libgglog.so(CMessageFactory::CreateMessage(CSourceContext*, unsigned int, ...)+0x31c) [0x7f4acb86ac2c]]
                          : [/software/ogg/1212/libgglog.so(_MSG_ERR_SIGNAL_RECEIVED(CSourceContext*, int, char const*, CMessageFactory::MessageDisposition)+0x3b) [0x7f4acb84cb37]]
                          : [./ggsci() [0x4e4543]]
                          : [/lib64/libpthread.so.0() [0x399900f710]]
                          : [/lib64/libpthread.so.0(pthread_mutex_lock+0) [0x39990093a0]]
                          : [./ggsci(ggs::gglib::MultiThreading::Mutex::Lock()+0x9) [0x516159]]
                          : [./ggsci(CContextItem::operator char const*() const+0x15) [0x4c1a2b]]
                          : [./ggsci(DBOCI_init_connection_logon(ggs::gglib::ggapp::CLoginName const&, ggs::gglib::ggapp::CDBObjName<(DBObjType)12> const&, char const*, int, int, int, char*)+0x1e5) [0x524215]]
                          : [./ggsci() [0x534baf]]
                          : [./ggsci(gl_db_login(char const*, char const*, ggs::gglib::ggapp::CDBObjName<(DBObjType)11>&, ggs::gglib::ggapp::CDBObjName<(DBObjType)12>&, short, char (&) [2048])+0x5f) [0x534edf]]
                          : [./ggsci() [0x4888fb]]
                          : [./ggsci(GGSCIDB_get_command(char const*, char const*, char const*, short, short, char (&) [2048])+0x11b) [0x48628b]]
                          : [./ggsci(do_cmd(char*, unsigned long, char*, unsigned long)+0x908) [0x4bbe58]]
                          : [./ggsci() [0x4c0193]]
                          : [./ggsci(ggs::gglib::MultiThreading::MainThread::ExecMain()+0x4f) [0x513c2f]]
                          : [./ggsci(ggs::gglib::MultiThreading::Thread::RunThread(ggs::gglib::MultiThreading::Thread::ThreadArgs*)+0x104) [0x513e34]]
                          : [./ggsci(ggs::gglib::MultiThreading::MainThread::Run(int, char**)+0x8b) [0x513f2b]]
                          : [./ggsci(main+0x3f) [0x49d33f]]
                          : [/lib64/libc.so.6(__libc_start_main+0xfd) [0x3998c1ed1d]]
                          : [./ggsci() [0x47df19]]

2018-05-21 07:20:22  ERROR   OGG-01117  Received signal: Segmentation violation (11).

2018-05-21 07:20:22  ERROR   OGG-01668  PROCESS ABENDING.
Segmentation fault (core dumped)


=====================
Solution
=====================

Set the Oracle Environment variables.

In this example, it is done via script oraigt

oracle@my_server:~>% env | grep ORA

ORASH=/etc/sh/orash

BAS_ORACLE_LIST=igt

oracle@my_server:~>% oraigt

oracle@my_server:~>% env | grep ORA

ORASH=/etc/sh/orash

BAS_ORACLE_LIST=igt

ORA_VER=1120

ORACLE_SID=igt

ORACLE_BASE=/software/oracle

ORACLE_ENV_DEFINED=yes

ORA_NLS33=/software/oracle/112/ocommon/nls/admin/data

ORACLE_HOME=/software/oracle/112

oracle@my_server:/software/ogg/1212>% ./ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 12.1.2.0.0 17185003 OGGCORE_12.1.2.0.0_PLATFORMS_130924.1316_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Sep 25 2013 00:31:13
Operating system character set identified as UTF-8.

Copyright (C) 1995, 2013, Oracle and/or its affiliates. All rights reserved.

GGSCI (my_server) 1> dblogin userid ogg  password xxx
Successfully logged into database.

Oracle Golden Gate Extract is failing with: ERROR OGG-00868 Error code 1291, error message: ORA-01291: missing logfile.

=======================
General
=======================
The archive folder was 100% full.
As an SOS operation, all archive files were deleted.
Now, several Extract process are in Abended state.
When checking  with info extract EXT_I_01 DETAIL, the error is:

2018-05-21 06:33:43  ERROR   OGG-00868  Error code 1291, error message: ORA-01291: missing logfile
 (Missing Log File /oracle_db/db2/db_igt/arch/arch0001_136774_827755274.arc. Read Position SCN -976006635).

2018-05-21 06:33:43  ERROR   OGG-01668  PROCESS ABENDING.

How to resolve this situation?

=======================
Evidences
=======================
via ggsci

info all 
EXTRACT     ABENDED     EXT_I_01    00:06:36      118:28:15   
EXTRACT     ABENDED     EXT_I_02    00:06:31      118:28:14   
EXTRACT     ABENDED     EXT_I_04    00:06:28      118:28:20   
EXTRACT     ABENDED     EXT_I_05    00:00:06      263:13:17   
EXTRACT     ABENDED     EXT_I_06    00:06:29      118:28:20   
EXTRACT     ABENDED     EXT_I_07    00:06:28      118:28:18   
EXTRACT     ABENDED     EXT_I_09    00:06:30      118:28:22   
EXTRACT     ABENDED     EXT_I_10    00:06:27      118:28:20   
EXTRACT     ABENDED     EXT_I_11    00:06:26      118:28:21   
EXTRACT     ABENDED     EXT_I_13    00:06:36      118:28:15   
EXTRACT     ABENDED     EXT_I_14    00:06:33      118:28:19   
EXTRACT     ABENDED     EXT_I_15    00:06:31      118:28:19   
EXTRACT     ABENDED     EXT_I_17    00:06:30      118:28:20   
EXTRACT     ABENDED     EXT_P_01    00:06:32      118:28:20   
EXTRACT     ABENDED     EXT_P_02    00:06:32      118:28:15   
EXTRACT     ABENDED     EXT_P_04    00:06:30      118:28:21   
EXTRACT     ABENDED     EXT_P_05    00:00:05      263:13:12   
EXTRACT     ABENDED     EXT_P_06    00:06:25      118:28:26   
EXTRACT     ABENDED     EXT_P_07    00:06:28      118:28:18   
EXTRACT     ABENDED     EXT_P_09    00:06:30      118:28:20   
EXTRACT     ABENDED     EXT_P_10    00:06:27      118:28:21   
EXTRACT     ABENDED     EXT_P_11    00:06:30      118:28:22   
EXTRACT     ABENDED     EXT_P_13    00:06:35      118:28:16   
EXTRACT     ABENDED     EXT_P_14    00:06:28      118:28:19   
EXTRACT     ABENDED     EXT_P_15    00:06:28      118:28:18   
EXTRACT     ABENDED     EXT_P_17    00:06:33      118:28:20   
EXTRACT     ABENDED     EXT_S_13    00:06:29      118:28:19   
EXTRACT     ABENDED     EXT_S_14    00:06:32      118:28:21   
EXTRACT     ABENDED     EXT_S_15    00:06:28      118:28:19   
EXTRACT     ABENDED     EXT_S_17    00:06:34      118:28:18  


GGSCI (my_server) 19> VIEW PARAM EXT_I_01 
Will display extact parameter files

GGSCI (my_server) 19> info extract EXT_I_01 DETAIL

EXTRACT    EXT_I_01  Last Started 2018-01-29 06:48   Status ABENDED
Checkpoint Lag       00:06:36 (updated 118:35:46 ago)
Log Read Checkpoint  Oracle Integrated Redo Logs
                     2018-05-16 07:52:30
                     SCN 3.3318960661 (16203862549)

  Target Extract Trails:

  Trail Name                                       Seqno        RBA     Max MB Trail Type

  /software/ogg/1212/dirdat/01/out/ei                 12     458809        100 EXTTRAIL  


Integrated Extract outbound server first scn: Unavailable.Need DBLOGIN.

  Extract Source          Begin             End        
  Not Available           2018-01-17 10:36  2018-05-16 07:52
  Not Available           2018-01-17 10:36  2018-01-29 06:47
  Not Available           2018-01-17 10:36  2018-01-28 06:47
  Not Available           2018-01-17 10:36  2018-01-27 06:47
  Not Available           2018-01-17 10:36  2018-01-26 06:47
  Not Available           2018-01-17 10:36  2018-01-25 06:47
  Not Available           2018-01-17 10:36  2018-01-24 06:47
  Not Available           2018-01-17 10:36  2018-01-23 06:47
  Not Available           2018-01-17 10:36  2018-01-22 06:47
  Not Available           2018-01-17 10:36  2018-01-21 06:47
  Not Available           2018-01-17 10:36  2018-01-20 06:47
  Not Available           2018-01-17 10:36  2018-01-19 06:47
  Not Available           2018-01-17 10:36  2018-01-18 06:47
  Not Available           * Initialized *   2018-01-17 10:36



Current directory    /software/ogg/1212
Report file          /software/ogg/1212/dirrpt/EXT_I_01.rpt
Parameter file       /software/ogg/1212/dirprm/ext_i_01.prm
Checkpoint file      /software/ogg/1212/dirchk/EXT_I_01.cpe
Process file         /software/ogg/1212/dirpcs/EXT_I_01.pce
Error log            /software/ogg/1212/ggserr.log


 less  /software/ogg/1212/dirrpt/EXT_I_01.rpt
Source Context :
  SourceModule            : [er.redo.oraxo]
  SourceID                : [/scratch/aime1/adestore/views/aime1_adc4150472/oggcore/OpenSys/src/app/er/redo/oracle/redooraix.c]
  SourceFunction          : [REDOORAIX_abend_if_missing_logfile]
  SourceLine              : [8206]
  ThreadBacktrace         : [15] elements
                          : [/software/ogg/1212/libgglog.so(CMessageContext::AddThreadContext()+0x1e) [0x7fd7c02e5dee]]
                          : [/software/ogg/1212/libgglog.so(CMessageFactory::CreateMessage(CSourceContext*, unsigned int, ...)+0x31c) [0x7fd7c02e2c2c]]
                          : [/software/ogg/1212/libgglog.so(_MSG_ERR_DB_GENERIC_FAILURE(CSourceContext*, char const*, CMessageFactory::MessageDisposition)+0x31) [0x7fd7c02c81d1]]
                          : [/software/ogg/1212/extract(REDOORAIX_abend_if_missing_logfile(char*)+0x1b9) [0x7b9a57]]
                          : [/software/ogg/1212/extract(REDOORAIX_attach(ggs::gglib::ggunicode::UString&, int, char (&) [2048])+0x274) [0x7ae574]]
                          : [/software/ogg/1212/extract(REDOORAIX_validate_config()+0x4e9) [0x7b21f9]]
                          : [/software/ogg/1212/extract(REDO_validate_config(int, unsigned int*, int*)+0xfab) [0x71f07b]]
                          : [/software/ogg/1212/extract(redo_log_setup()+0x36) [0x5ad3c6]]
                          : [/software/ogg/1212/extract(extract_main(int, char**)+0x29e) [0x5b096e]]
                          : [/software/ogg/1212/extract(ggs::gglib::MultiThreading::MainThread::ExecMain()+0x4f) [0x68743f]]
                          : [/software/ogg/1212/extract(ggs::gglib::MultiThreading::Thread::RunThread(ggs::gglib::MultiThreading::Thread::ThreadArgs*)+0x104) [0x687694]]
                          : [/software/ogg/1212/extract(ggs::gglib::MultiThreading::MainThread::Run(int, char**)+0x8b) [0x68785b]]
                          : [/software/ogg/1212/extract(main+0x3f) [0x5b047f]]
                          : [/lib64/libc.so.6(__libc_start_main+0xfd) [0x3998c1ed1d]]
                          : [/software/ogg/1212/extract() [0x51dc59]]

2018-05-21 06:33:43  ERROR   OGG-00868  Error code 1291, error message: ORA-01291: missing logfile
 (Missing Log File /oracle_db/db2/db_igt/arch/arch0001_136774_827755274.arc. Read Position SCN -976006635).

2018-05-21 06:33:43  ERROR   OGG-01668  PROCESS ABENDING.

======================================================
Solution
======================================================

The solution would be to unregister and register the extract process, causing it to reset to currect SCN value.

GGSCI (my_server) 2> DBLOGIN USERID OGG  PASSWORD <password>

Successfully logged into database.

GGSCI (my_server) 15> STOP EXTRACT EXT_I_02

EXTRACT EXT_I_02 is already stopped.

If extract cannot be stopped do:

ALTER EXTRACT EXT_P_02, BEGIN NOW

GGSCI (my_server) 16> UNREGISTER EXTRACT EXT_I_02 DATABASE   

Successfully unregistered EXTRACT EXT_I_02 from database.

This might take several minutes

GGSCI (my_server) 17> REGISTER EXTRACT EXT_I_02 DATABASE

Extract EXT_I_02 successfully registered with database at SCN 16308342873.

This might take several minutes

GGSCI (my_server) 18> START EXTRACT EXT_I_02

Sending START request to MANAGER ...

EXTRACT EXT_I_02 starting

GGSCI (my_server) 13> INFO EXTRACT EXT_I_02

EXTRACT    EXT_I_01  Last Started 2018-05-21 08:20   Status RUNNING

Checkpoint Lag       00:00:00 (updated 00:00:00 ago)

Process ID           36729

Log Read Checkpoint  Oracle Integrated Redo Logs

                     First Record       

                     SCN 3.3405189667 (16290091555)

===============================

Additional Information

===============================

Checking the SCN with INFO EXTRACT XXX SHOWCH command one can see that the SCN in extract is pointing to a deleted archive file

Get data from Database

oracle@my_server:/oracle_db/db2/db_igt/arch>% ls -ltr | head -10

total 59342928

-rw-r----- 1 oracle dba 372660224 May 20 15:44 arch0001_137783_827755274.arc

-rw-r----- 1 oracle dba 371138560 May 20 15:44 arch0001_137784_827755274.arc

-rw-r----- 1 oracle dba 393000960 May 20 15:44 arch0001_137785_827755274.arc

-rw-r----- 1 oracle dba 372824064 May 20 15:45 arch0001_137786_827755274.arc

-rw-r----- 1 oracle dba 384171008 May 20 15:46 arch0001_137787_827755274.arc

-rw-r----- 1 oracle dba 491667968 May 20 15:49 arch0001_137788_827755274.arc

-rw-r----- 1 oracle dba 489092608 May 20 15:51 arch0001_137789_827755274.arc

-rw-r----- 1 oracle dba 377072128 May 20 15:53 arch0001_137790_827755274.arc

-rw-r----- 1 oracle dba 379522048 May 20 15:55 arch0001_137791_827755274.arc

SELECT recid, 

       name, 

       first_change#, 

       next_change#,  

       TO_CHAR(first_time, 'YYYYMMDD hh24:mi:ss') AS first_time 

  FROM V$ARCHIVED_LOG 

 WHERE name LIKE '%arch0001_137783_827755274%';

recid  name

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

137782 /oracle_db/db2/db_igt/arch/arch0001_137783_827755274.arc

first_change# next_change#  first_time

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

16290091555 16290177738 20180520 13:23:04

SELECT dbid, current_scn  FROM V$DATABASE;

dbid current_scn

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

1066039690 16307173674

Get data from Extract

GGSCI (my_server) 27> INFO EXTRACT EXT_I_05 SHOWCH

EXTRACT    EXT_I_05  Last Started 2018-05-16 07:53   Status ABENDED

Checkpoint Lag       00:00:06 (updated 265:36:17 ago)

Log Read Checkpoint  Oracle Integrated Redo Logs

                     2018-05-10 07:13:58

                     SCN 3.3213072676 (16097974564)

Current Checkpoint Detail:

Read Checkpoint #1

  Oracle Integrated Redo Log

  Startup Checkpoint (starting position in the data source):

    Timestamp: 2015-08-09 08:42:51.000000

    SCN: Not available

  Recovery Checkpoint (position of oldest unprocessed transaction in the data source):

    Timestamp: 2018-05-10 07:13:58.000000

    SCN: 3.3213072615 (16097974503)

  Current Checkpoint (position of last record read in the data source):

    Timestamp: 2018-05-10 07:13:58.000000

    SCN: 3.3213072676 (16097974564)

  BR Previous Recovery Checkpoint:

    Timestamp: 2018-01-29 06:48:22.685278

    SCN: Not available

  BR Begin Recovery Checkpoint:

    Timestamp: 2018-05-10 05:21:30.000000

    SCN: 3.3212132747 (16097034635)

  BR End Recovery Checkpoint:

    Timestamp: 2018-05-10 05:21:30.000000

    SCN: 3.3212132747 (16097034635)

Write Checkpoint #1

  GGS Log Trail

  Current Checkpoint (current write position):

    Sequence #: 363

    RBA: 1495

    Timestamp: 2018-05-10 07:14:04.547894

    Extract Trail: /software/ogg/1212/dirdat/05/out/ei

    Trail Type: EXTTRAIL

Header:

  Version = 2

  Record Source = A

  Type = 13

  # Input Checkpoints = 1

  # Output Checkpoints = 1

File Information:

  Block Size = 2048

  Max Blocks = 100

  Record Length = 2048

  Current Offset = 0

Configuration:

  Data Source = 3

  Transaction Integrity = 1

  Task Type = 0

Status:

  Start Time = 2018-05-16 07:53:24

  Last Update Time = 2018-05-10 07:14:04

  Stop Status = A

  Last Result = 0

  

So in archive logs, the first available SCN is 16290091555 starting at 20180520 13:23:04

But the extract is expecting to process SCN 16097974503 from 2018-05-10 07:13:58.

The latest available archive file start with first_change# 16290091555 but extract expect to process SCN starting from 16097974503.

Since the archive files were deleted, the only option is to reset the extract, by register and register it again to the database.

Once that was done, the SCN in Golden Gate extract was in sync with SCN in database.

SQL Server. Top Segments and shrink datafile

=======================
To see top Segments
=======================
SELECT CAST(object_name(id) as varchar(50)) AS object_name, 
       SUM(CASE WHEN indid<2 THEN rows END) AS rows,
       ROUND(SUM(reserved)*8/1024,0) AS reserved_Mb, 
       ROUND(SUM(dpages)*8/1024,0) AS Size_Mb, 
       ROUND(SUM(used-dpages)*8/1024,0) AS Used_Mb, 
       ROUND(SUM(reserved-used)*8/1024,0) AS Unused_Mb
  FROM sysindexes with (nolock) 
 WHERE indid in(0,1,255) and id>100 
 GROUP BY id with rollup
 ORDER BY sum(dpages)*8 desc;


object_name            rows reserved_Mb Size_Mb Used_Mb Unused_Mb
------------------ -------- ----------- ------- ------- ---------
NULL 34550143      120475    7626   82027     30822
email_contact       9439214        3094    3084      10         0
email_user_details  3845153        1008     987      13         7
email_message       1236052      112406     587   81804     30014
USER_CompanyStatus 11786380 466     460       0         5
email_association   1445918 355     350       3         2
IncidentAuditLog     682479 390     345      43         1
UserActivityLog     2194807 390     296       0        94
IncidentWorkNote     306004         291     278      12         0
EmailsLOG            265786         237     236       0         0


=======================
To free up unused space:
=======================

DBCC SHRINKFILE('cdb_Data',125000)

cdb_Data is the file logical name.

The output:

(1 row(s) affected)

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Dbid Field CurrentSize MinimumSize UsedPages  EstimatedPagaed

   7     1    16000000         128  15760728         15760728

15760728*8=126085824 Kb = 123130 Mb

So in theory we can shrink data file only to 123,130Mb

Lets try to shrink to 120,000Mb

DBCC SHRINKFILE('Starhome_Data',120000) 

This should not work...

After long long time, several hours, the command was still running...

I had to kill it.