Hi all,
I'm a GIS Analyst and we've recently upgraded our SDEs and ESRI stack to 10.1 and we're setting up our enterprise geodatabase maintenance plan.
I had suggested using a python script with the following steps:
# 1. enter enterprise gdb connection
# 2. reconcile versions and output to logfile
# 3. compress enterprise gdb
# 4. rebuild indexes on enterprise gdb
# 5. analyze datasets on enterprise gdb
Our Oracle team proposed an alternative solution to the python script (typed out below). Our DBA’s are pushing back a bit on us saying that “spatial doesn’t matter” and that it’s on the application side so any back end changes would not change the actual data. To be fair, I’m not an Oracle DBA, but that is contrary to what I've learned in my past through schooling and the ESRI Configuring and Managing the Multiuser Geodatabase course.
Oracle DBA Suggestion:
"I looked up the web for what this Python script does on DB side and here is what it translates to (see below).
I can reassure you that our processes are doing much better job by providing specific parameter that improve performance and provide better results.
Also, you had mentioned during our meeting that users are needed to be out of a DB during the index rebuild. Based on a script below I see why:
'ALTER INDEX ' || IndexRec.owner || '.' || IndexRec.index_name || ' REBUILD'
Here is a reason – it’s an old technology, at least 4-5 years old.
Our process rebuilds indexes online and I personally guarantee it won’t corrupt your data whatsoever. I run this index rebuild process in SI with App up and running and they run heavy transaction during this time.
We can talk to ESRI about it, but from my personal prospective our maintenance plan is more flexible, robust and powerful.
Here is a script:
SET SERVEROUTPUT ON
DECLARE
CURSOR Owner_Cur IS
SELECT DISTINCT(OWNER) owner
FROM sde.table_registry
ORDER BY owner;
CURSOR Index_Cur IS
SELECT owner, index_name
FROM dba_indexes
WHERE owner IN
(SELECT DISTINCT(owner)
FROM sde.table_registry)
AND INDEX_TYPE = 'NORMAL'
ORDER BY owner, index_name;
SQL_STMT VARCHAR2(200);
BEGIN
DBMS_OUTPUT.ENABLE (100000);
FOR IndexRec IN Index_Cur LOOP
SQL_STMT := ('ALTER INDEX ' || IndexRec.owner || '.' || IndexRec.index_name || ' REBUILD');
DBMS_OUTPUT.PUT_LINE(SQL_STMT);
EXECUTE IMMEDIATE SQL_STMT;
DBMS_OUTPUT.NEW_LINE;
END LOOP;
FOR OwnerRec IN Owner_Cur LOOP
DBMS_OUTPUT.PUT_LINE('Analyzing schema : ' || OwnerRec.owner);
DBMS_STATS.GATHER_SCHEMA_STATS(OwnerRec.owner);
DBMS_OUTPUT.NEW_LINE;
END LOOP;
END; "
Is the DBA team correct and it's better and will not corrupt our geodatabase and spatial data integrity, if so why? Or should we be using the ESRI application layer functionality, if so why?
Any Oracle/GIS DBA guru's out there that can help me? Thanks in advanced!