POST
|
Patch http://resources.arcgis.com/content/patches-and-service-packs?fa=viewPatch&PID=66&MetaID=1793
... View more
08-18-2011
03:28 AM
|
0
|
0
|
722
|
POST
|
Travis problem in oracle, Bug Number 11666567 Esri Error "table Regitered As Versioned" MDSYS.SDO_GEOM_METADATA_TABLE in SDO_DIMINFO column True : ArcSDE_Delta_Table False : ArcSDE Delta Table (blank) e.g False ((_ArcSDE Delta Table_; 220000; 220214,7483647; 5E-7); (_ArcSDE Delta Table_; 3900000; 3900214,7483647; 5E-7); ; ) Esri ArcSDE 9.3.1 Service Pack 2, users can publish the patch for True ((_ArcSDE_Delta_Table_; 220000; 220214,7483647; 5E-7); (_ArcSDE Delta Table_; 3900000; 3900214,7483647; 5E-7); ; ) Bug Number 11666567 Metalink Knowlledge Problem : ORA-39127, ORA-13249 from EXP and EXPDP when exporting Spatial indexes in 11.2.0.2 Applies to: Oracle Spatial - Version: 11.2.0.2 to 11.2.0.2 - Release: 11.2 to 11.2 Information in this document applies to any platform. Symptoms Since upgrading the database to 11.2.0.2, EXP and EXPDP generate errors while exporting some Spatial indexes: EXP: EXP-00008: ORACLE error 13249 encountered ORA-13249: Only alphanumeric characters and "_" are allowed in SDO_DIMNAME ORA-06512: at "SYS.DBMS_EXPORT_EXTENSION", line 270 ORA-06512: at line 1 EXP-00078: Error exporting metadata for index SDO_INDEX_A. Index creation will be skipped EXPDP: ... Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS Processing object type SCHEMA_EXPORT/TABLE/INDEX/DOMAIN_INDEX/INDEX ORA-39127: unexpected error from call to local_str := SYS.DBMS_EXPORT_EXTENSION.GET_DOMAIN_INDEX_METADATA('SDO_INDEX_A','USER_A','SDO_INDEX_METHOD_10I','MDSYS',11.02.00.01.00,newblock,0) ORA-13249: Only alphanumeric characters and "_" are allowed in SDO_DIMNAME ORA-06512: at "SYS.DBMS_EXPORT_EXTENSION", line 270 ORA-06512: at line 1 ORA-06512: at "SYS.DBMS_METADATA", line 6498 ORA-39127: unexpected error from call to local_str := SYS.DBMS_EXPORT_EXTENSION.GET_DOMAIN_INDEX_METADATA('SDO_INDEX_B','USER_A','SDO_INDEX_METHOD_10I','MDSYS',11.02.00.01.00,newblock,0) ORA-13249: Only alphanumeric characters and "_" are allowed in SDO_DIMNAME ORA-06512: at "SYS.DBMS_EXPORT_EXTENSION", line 270 ORA-06512: at line 1 ORA-06512: at "SYS.DBMS_METADATA", line 6498 ... Changes Beginning 11.2.0.2, the DIMINFO of Spatial indexes in MDSYS.SDO_GEOM_METADATA_TABLE cannot have a <space> in SDO_DIMNAME. Cause In earlier releases, a <space> in SDO_DIMNAME is acceptable. Therefore, in an upgraded database, these "invalid" data already exist in MDSYS.SDO_GEOM_METADATA_TABLE, and cause ORA-13249 error during export when the metadata is accessed. Solution Update the index's metadata and remove the <space> from its SDO_DIMNAME. Example: SQL> SELECT table_name, column_name AS colname, diminfo FROM USER_SDO_GEOM_METADATA; TABLE_NAME COLNAM DIMINFO(SDO_DIMNAME, SDO_LB, SDO_UB, SDO_TOLERANCE) ------------ ------ ----------------------------------------------------- COLA_MARKETS SHAPE SDO_DIM_ARRAY(SDO_DIM_ELEMENT('X Axis', 0, 20, .005), SDO_DIM_ELEMENT('Y', 0, 20, .005)) SQL> -- Changing "X Axis" TO "XAxis" SQL> UPDATE user_sdo_geom_metadata SET diminfo = MDSYS.SDO_DIM_ARRAY( MDSYS.SDO_DIM_ELEMENT('XAxis', 0, 20, 0.005), MDSYS.SDO_DIM_ELEMENT('Y', 0, 20, 0.005)) WHERE table_name = 'COLA_MARKETS' AND column_name = 'SHAPE'; 1 row updated. SQL> COMMIT; Commit complete. SQL> -- Query USER_SDO_GEOM_METADATA to view the change SQL> SELECT table_name, column_name AS colname, diminfo FROM USER_SDO_GEOM_METADATA; TABLE_NAME COLNAM DIMINFO(SDO_DIMNAME, SDO_LB, SDO_UB, SDO_TOLERANCE) ------------ ------ ----------------------------------------------------- COLA_MARKETS SHAPE SDO_DIM_ARRAY(SDO_DIM_ELEMENT('XAxis', 0, 20, .005), SDO_DIM_ELEMENT('Y', 0, 20, .005)) =============== Oracle Bug Knowledge Line Oracle Database Products Family Oracle Database Area Spatial - Location Services Product 619 - Oracle Spatial Hdr: 11666567 11.2.0.2 SDOGEN 11.2.0.2 PRODID-619 PORTID-226 ORA-13249 Abstract: NEW RESTRICTION ON SDO_DIMNAME IN 11.2.0.2 IS NOT DOCUMENTED *** 01/20/11 06:56 am *** *** 01/20/11 07:02 am *** Beginning in 11.2.0.2, a <space> in SDO_DIMNAME is no longer accepted, however, this new restriction is not found in 11.2.0.2's documentation. SQL> INSERT INTO mdsys.sdo_geom_metadata_table 2 VALUES ('SPATIAL_TEST', 3 'COLA_MARKETS2','SHAPE', 4 MDSYS.SDO_DIM_ARRAY( -- 20X20 grid 5 MDSYS.SDO_DIM_ELEMENT('X TEST', 0, 20, 0.005), <--- X<space>TEST 6 MDSYS.SDO_DIM_ELEMENT('Y', 0, 20, 0.005) 7 ), 8 NULL -- SRID this is our own Coordinate System. 9 ); INSERT INTO mdsys.sdo_geom_metadata_table * ERROR at line 1: ORA-13249: Only alphanumeric characters and "_" are allowed in SDO_DIMNAME ORA-6512: at "MDSYS.MD", line 1723 ORA-6512: at "MDSYS.MDERR", line 17 ORA-6512: at "MDSYS.CHK_SDO_DIMNAME", line 11 ORA-4088: error during execution of trigger 'MDSYS.CHK_SDO_DIMNAME' In previous releases up to 11.1.0.7, a <space> in SDO_DIMNAME is acceptable. *** 01/20/11 08:08 am *** (CHG: Asg->NEW OWNER OWNER) *** 01/20/11 10:48 am *** (CHG: Fixed->12) *** 01/20/11 10:48 am *** (CHG: Sta->89) *** 01/20/11 10:48 am *** I have revised the material about valid characters in the "Geometry Metadata Views" section (Section 2.8 in the current manual) in my document source file to include this: ------------------ The following considerations apply to schema, table, and column names, and to any SDO_DIMNAME values, that are stored in any Oracle Spatial metadata views: - They must contain only letters, numbers, and underscores. For example, such a name cannot contain a space ( ), an apostrophe ('), a quotation mark ("), or a comma (,). [etc.] --------------------- This revised text will appear in the next published version of the Oracle Spatial Developer's Guide. koraykey, Disabling that trigger will certainly avoid getting that error when versioning SDO_Geometry feature classes or creating multi-versioned views on feature classes that were versioned in previous releases. I'm not sure how Oracle would feel about having one of their triggers disabled though.... In this case I will be very surprised if this helps as danarng said that they were using SDE.ST_GEOMETRY. So metadata isn't stored in the SDO_GEOM_METADATA views. danarng, It kind of sounds like there is a problem with the type when the SYS_TYPE function is called, so maybe having two dump files would actually solve the problem. If I were you I would open an incident with ESRI technical support. Something else that you could try, if you haven't already, is dropping the table indexes and disassociating any stats before you export the data. As for giving exp or imp a try, I think for ST_Geometry you have to use the expdp and impdp. Finally, here is a KB article with a number links to other known issues with import/export of ST_Geometry: http://resources.arcgis.com/content/kbase?fa=articleShow&d=34342 Good luck Travis
... View more
03-21-2011
11:09 PM
|
0
|
0
|
1345
|
POST
|
Run Script Toad or SQL Developer /************************************************* *Koray GUNDUZ (Ibb Cbs) - 2010 *Procedure analyze all index and tables *************************************************/ 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; /
... View more
03-21-2011
12:12 PM
|
0
|
0
|
372
|
POST
|
Oracle version: 11.2.0.2.0 bug Oracle bug number 11666567. Oracle SDO_DIMNAME Trigger Bug �?� For the Adds table: "_ArcSDE Delta Table_" �?� For the MV View: "_ArcSDE IMV Table_" Bug fix ArcSDE 10 Service Pack 2 Bug fix ArcSDE 9.3.1 Unknown ??? Install ArcSDE 9.3.1 Sp2 for Oracle 11.2.0.1.0 or ArcSDE 9.3.1 Sp 2 for Oracle 11.2.0.2.0 disable MDSYS.CHK_SDO_DIMNAME Trigger and restore dump file Oracle 11.2.0.1.0 copy and paste data ArcCatalog Oracle 11.2.0.2.0 Trigger Disable Script CREATE OR REPLACE TRIGGER MDSYS.chk_sdo_dimname BEFORE INSERT OR UPDATE ON MDSYS.SDO_GEOM_METADATA_TABLE FOR EACH ROW DISABLE DECLARE cnt NUMBER; res NUMBER; BEGIN FOR cnt IN 1 .. :NEW.sdo_diminfo.COUNT LOOP SELECT REGEXP_INSTR(:NEW.sdo_diminfo(cnt).sdo_dimname, '[^a-zA-Z0-9_]') INTO res FROM DUAL; IF (res > 0) THEN mderr.raise_md_error('MD', 'SDO_GEOM_METADATA_TABLE',-13249, 'Only alphanumeric characters and "_" are allowed in SDO_DIMNAME'); END IF; END LOOP; END; / ========================================== Bug: Oracle 11.2.0.2 constraint on SDO_DIMNAME http://resources.arcgis.com/content/kbase?fa=articleShow&d=38713 Regards H.Koray GUNDUZ Izmir Metropolitan Municipalty Geographic Information Systems
... View more
03-21-2011
11:58 AM
|
0
|
0
|
1345
|
POST
|
Hi Brad Using sde : oracle11g:/;LOCAL=TNSNAME Regard
... View more
03-07-2011
09:58 AM
|
0
|
0
|
404
|
POST
|
Hi Joseph, #Apex Uninstall cd oracledirectory/apex sqlplus sys/password as sysdba @apxremov.sql e.g cd /u01/app/oracle/product/11.2.0/db_1/apex sqlplus sys/oracle as sysdba @apxremov.sql or ArcSDE update "NIM052156 - Ora11gR2 APEX* & ORDDATA* tables need to be filtered." It is also listed in the issues addressed in 9.3.1 SP2: http://resources.arcgis.com/content/patches-and-service-packs?fa=viewPatch&PID=66&MetaID=1623 and http://resources.arcgis.com/content/patches-and-service-packs?fa=viewPatch&PID=66&MetaID=1718 Regards
... View more
03-03-2011
04:25 AM
|
0
|
0
|
217
|
POST
|
deploy to Oracle Users Oracle Real Application Cluster (RAC) Users run the node 1 and node 2... sqlplus sys/password@sidname as sysdba; SQL>exec SYS.ARCSDE_USER_KILL; or SQL>exec ARCSDE_USER_KILL; =========================================================== CREATE OR REPLACE PROCEDURE SYS."ARCSDE_USER_KILL" /************************************************* *Koray GUNDUZ (Ibb Cbs) - 2010 *Procedure kills all sessions listed in the sde.process_information table *************************************************/ AS SQL_STMT VARCHAR2(200); CURSOR SDE_USERS IS SELECT SERVER_ID,OWNER FROM SDE.PROCESS_INFORMATION WHERE DIRECT_CONNECT = 'Y'; NEWC INTEGER; BEGIN FOR SDEREC IN SDE_USERS LOOP DECLARE CURSOR KILL_DC IS SELECT SID,SERIAL# FROM SYS.V$SESSION WHERE PROCESS LIKE SDEREC.SERVER_ID || ':%' AND USERNAME = SDEREC.OWNER; BEGIN FOR KILLREC IN KILL_DC LOOP SQL_STMT := 'ALTER SYSTEM KILL SESSION ' || '''' || KILLREC.SID || ',' || KILLREC.SERIAL# || ''''; EXECUTE IMMEDIATE SQL_STMT; END LOOP; END; END LOOP; SDE.PINFO_UTIL.PURGE_UNUSED(NEWC); END; /
... View more
01-06-2011
11:37 PM
|
0
|
0
|
1224
|
Online Status |
Offline
|
Date Last Visited |
11-11-2020
02:23 AM
|