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

[Anonymous User]

Original User: aguan@hess.com

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
       declare
          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);
       begin
          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;
      end;
/
     commit;
EOF

  fi

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

[MattSimmons]

I've spoken with ESRI Support about a compress issue I was having and my response was that all ArcMap applications and table locks had to be closed.  The only way I am able to get a full compress to State_ID=0 is:

1.) stop the SOM service on the server and remove all table locks (sdemon -o kill -t all)
2.) reconcile and post any edits
3.) delete all versions
4.) sync all replicas (replicas are used in a second application here)
5.) compress the database
6.) check for state_ID by using sdeversion -o describe -V SDE.DEFAULT -i <xxxx> -u <xxxx> -p <xxxx>

[Anonymous User]

Original User: aguan@hess.com

Matt,

Thanks for the information. I missed truncating state_locks table in my previous tests. I did more tests and here are my findings:

1.) The good news is, for ArcSDE 10, I can use "sdemon -o delete -t all" to cleanly delete all ArcSDE sessions and locks, and the "sdeversion -o compress" will compress all to state = 0, including cleaning up A & D tables.
2.) for ArcSDE 9.3, I will have to clear all Oracle sessions on RDBM level, plus truncating process_information, table_locks, and state_locks tables, then "sdeversion -o compress" will compress all to state = 0.

I would think doing a compression this way is not recommended when there are editing activities. For compression during editing hours, it is best to ask all users to close their applications  to ensure all edits are saved before compression. However, it could be a good option when needing to force a compression during off hours. This is particularly true for applications using direct connect.

I have asked several of my ArcSDE expert friends in other companies that how they compress the ArcSDE databases.  It is clear to me when all the applications use 3 tier connections, it is easier to implement a weekly compression just by "sdemon -o kill -t all" and even shutting down giomgr process. For companies using direct connect and arcsde 9.3 below, they normally do not compress well and in case performance degrades too much, they will rebounce the database to kick out users for a period of time and immediately run SDE compression. It is very common for them not to worry about compressing A & D tables unless the performance becomes an issue.