Oracle enterprise gdb maintenance: native Oracle maintenance or ESRI functionality with python?

JanelleFaulconer — Fri, 15 May 2015 15:37:08 GMT

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!

topic Oracle enterprise gdb maintenance: native Oracle maintenance or ESRI functionality with python? in Data Management Questions

Oracle enterprise gdb maintenance: native Oracle maintenance or ESRI functionality with python?