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
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
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>
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.