AnsweredAssumed Answered

How to compress ArcSDE without having to shut down all SDE applications?

Question asked by geonetadmin on Jun 13, 2012
Latest reply on Jun 14, 2012 by geonetadmin
Original User:

We have a business need to compress ArcSDE timely (for example nightly) to force saved edits in A & D tables to move into the base business table as we need to copy the base table to other locations. I was told if I kill all ArcSDE Oracle sessions, clear up sde.process_information and sde.table_locks tables, I will be able to do that. However, I find I still have to close all SDE applications (like Arcmap) in order to do a full compression. Below is my test work flow and test Linux shell script. Can anybody see what I might have missed here? We only use direct connect.

Step 1. make edits in arcmap and save edits
Step 2. keep the same arcmap open and run a shell script (see below)  to kill all oracle SDE sessions (direct and indirect), truncate process_information and table_locks (verify all sessions no longer exist in the databases and the two tables are empty), and do a "sdeversion -o compress".  Unfortunate this does not compress A & D tables to the base business table.
Step 3. close above arcmap, and rerun the script, this compresses A & D tables to the base business table. However, this will not be practical  in a normal working environment as we can not guarantee all users will close their ArcSDE applications. This, of cause, raise a question how SDE still knows in step 2 there are arcmap applications open? Are there any other SDE system tables I need to clear up beside process_information and table_locks?

Can you advise what I might have missed?

Linux Shell Script:

  echo "compressing migsde ..." `date "+Date = %D Time = %H:%M"` | tee -a $logfile

  echo "   Kill all SDE connections ..." | tee -a $logfile
  # sdemon will kill all 3 tier connection for SDE 9.x, and direct connections/oracle sessions for SDE 10
  # for SDE 10, must grant "grant alter system, SELECT_CATALOG_ROLE to sde" to execute "sdemon -o kill "
  sdemon -o kill -t all -u sde -p $sdepwd -i sde:oracle10g:\;TWO_TASK=migsde  -N | tee -a $logfile

  # need to kill Oracle sessions for SDE 9.3 on Oracle server
  if [ "$sdever" = "93" ]; then
    sqlplus /nolog <<EOF
       set serveroutput on
       conn sdedba/$dbapwd@$SDEDATABASE
       whenever sqlerror exit 1
          mysql varchar2(200);
          cursor cUser is
          select s.sid,s.serial#,pi.sde_id, pi.server_id
          from sde.process_information pi, v\$session s
          where pi.direct_connect = 'Y'
          and pi.server_id = SUBSTR(s.process,1,INSTR(s.process,':')-1);
          for rec in cUser loop
             mysql := 'alter system kill session '|| '''' ||rec.sid||','||rec.serial#||''' immediate';
             dbms_output.put_line('mysql = '||mysql);
             execute immediate mysql;
          end loop;


sdetable -o truncate -t process_information -u sde -p $sdepwd -i sde:oracle10g:\;TWO_TASK=$SDEDATABASE -N | tee -a $logfile
sdetable -o truncate -t table_locks -u sde -p $sdepwd -i sde:oracle10g:\;TWO_TASK=$SDEDATABASE -N | tee -a $logfile

  echo "    Executing full compression of $SDEDATABASE " `date "+Date = %D Time = %H:%M"` | tee -a $logfile
  sdeversion -o compress -u sde -p $sdepwd -i sde:oracle10g:\;TWO_TASK=$SDEDATABASE -N | tee -a $logfile

Allen Guan
Hess Corp