Debug database application in visual studio 2005

After upgrading from visual studio 2003 to visual studio 2005, some configurations are needed for debugging database applications.

1. download occi for vc8. Place it at $oracle_home\OCI\lib\MSVC\vc8.

2. add it to Path. Becareful, it must stay before $oracle_home\bin in path.

3. Go to project properties page. Linker -> Eingabe -> Zusaetzliche Abhaengigkeiten, use oraocci10d.lib for debug mode, oraocci10.lib for release mode.

Script to create user

# user auf Ziel-Rechner anlegen, zB script in CYGWIN

-- user anlegen
sqlplus 'sys/lbsys as sysdba' <
-- drop user $1 cascade;

-- vielleicht datafile ' ' noetig?
CREATE TABLESPACE $1
LOGGING
DATAFILE 'd:\ORADATA\\DAT_$1_1.dbf' SIZE 500M AUTOEXTEND
ON NEXT 200M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO;
CREATE USER $1 PROFILE DEFAULT
IDENTIFIED BY $1 DEFAULT TABLESPACE $1
QUOTA UNLIMITED
ON $1
ACCOUNT UNLOCK;
GRANT ALTER SESSION TO $1;
GRANT CREATE PROCEDURE TO $1;
GRANT CREATE SEQUENCE TO $1;
GRANT CREATE TRIGGER TO $1;
GRANT CREATE TYPE TO $1;
GRANT CONNECT TO $1;

GRANT CREATE TABLE TO $1;
GRANT CREATE view TO $1;
GRANT CREATE synonym TO $1;

EOF

Validate SDO_GEOMETRY

SELECT A.COMPLEX_ID, SDO_GEOM.VALIDATE_GEOMETRY_WITH_CONTEXT(A.GEOMETRY, M.diminfo) AS PROBLEM
FROM LB_GDF_COMPLEX A, USER_SDO_GEOM_METADATA M
WHERE M.table_name = 'LB_GDF_COMPLEX'
AND M.column_name = 'GEOMETRY'
AND A.feature_class = 1119
AND SDO_GEOM.VALIDATE_GEOMETRY_WITH_CONTEXT(A.GEOMETRY, M.diminfo) <> 'TRUE'

IMP and EXP

imp 'sys/syspwd as sysdba' FROMUSER=fu TOUSER=tu FILE=file.dmp ignore=y log=d:\dump_imp.log

imp 'sys/syspwd as sysdba' FROMUSER=fu TOUSER=tu FILE=file.dmp TABLES=(t1,t2,...,tn)

exp hildesheim/hildesheim@arden file=hildesheim.dmp

Same text, different binary

When PL/SQL code is checked into CVS, ASCII mode should be used, not binary mode. As we know, the same texts under Linux and Windows are not same in binary mode. For example, "NEWLINE" is LF under Linux but CR+LF under windows.

If PL/SQL code is written under windows but checked into CVS in binary mode, we will get problems when we wrap the code under Linux.

Create index parallel nologging

alter table t1 nologging;
alter table t2 nologging;

create index T1_IDX on T1 (Field1, Field2, Field3, Field4) parallel 2 nologging;
create index T2_IDX on T2 (Field1, Field2, Field3, Field4) parallel 2 nologging;

parallel 2 for 2 CPUs.

Rebuild Spatial Index

When I excute "truncate table ..." or "delete from ...", sometimes I encounter ORA-29859 or ORA-29861 error. In most cases, I just rebuild the spatial index on the geometry column and the problem will be fixed.

DROP INDEX GEO_PS_POSITION_SX;
CREATE INDEX GEO_PS_POSITION_SX ON GEO_PS_POSITION (ROUTE) INDEXTYPE IS MDSYS.SPATIAL_INDEX;