Snapshot of high level concepts of Oracle DB for WCS Commerce Developer

Commerce Developer : Working with ORACLE

As a commerce developer working with Oracle back end, it is very important to have good understanding of SQL.

I am not a DBA, but the way I have learned was to started with basic Insert/Update/Delete, Select commands and using group by, order by
but progressively learned some handy SQL commands.

Refresh local schema for development.
I would use the script below for creating tablespace/schema/assign grants and import schema. Replace wcsatdev with the schema name of your choice.

drop tablespace atdata including contents and datafiles;
drop tablespace atindx including contents and datafiles;

create tablespace atdata
datafile 'C:\oracle\product\ORCL10G\atdata_01.dbf' size 250m reuse autoextend on;

create tablespace atindx
datafile 'C:\oracle\product\ORCL10G\atindx_01.dbf' size 250m reuse autoextend on;

drop user wcsatdev cascade;

create user wcsatdev identified by wcsatdev
default tablespace atdata
temporary tablespace temp
quota unlimited on atdata
quota unlimited on atindx;

grant connect,resource,create materialized view to wcsatdev;

grant create view to wcsatdev;

grant create synonym to wcsatdev;

create or replace directory dpdumpdir as 'C:\projects\db';

create or replace directory dplogdir as 'C:\projects\db';

GRANT READ, WRITE ON DIRECTORY DPDUMPDIR TO wcsatdev;

GRANT READ, WRITE ON DIRECTORY DPLOGDIR TO wcsatdev;

Using data pump in Oracle 10g, faster way to import.
impdp system/oracle@orcl10g dumpfile=DPDUMPDIR:export.dmp logfile=DPLOGDIR:from_wcsatdev_10g.log REMAP_SCHEMA=from_schema:to_schema PARALLEL=8 CONTENT=ALL



When ever you have a larger database on your local database, it is a good idea to run DB stats once in a while to improve performance.

exec dbms_stats.GATHER_SCHEMA_STATS(ownname=>'WCSATDEV',estimate_percent=>dbms_stats.auto_sample_size, CASCADE=>TRUE, DEGREE=>4)

Where degree will invoke the 4 parallel slaves, cascade is required for indexes.

Size of the Tables: Run this command in the schema

select SEGMENT_NAME,sum(BYTES)/(1024) size_in_kil from user_extents where segment_type='TABLE' group by SEGMENT_NAME order by size_in_kil desc;


Dropping Stage prop Triggers: Stage prop is a completely new discussion but on my local I run this for better performance.

set pages 0 lines 100 ;
spool drop_trigger1.sql ;
select 'drop trigger '||trigger_name||' ;' from user_triggers where trigger_name like '%STG_%' or trigger_name = 'STGLOG_STGRESERVED1' ;
spool off;
@drop_trigger1.sql;



Issues and Fixes:

java.sql.SQLException: ORA-01000: maximum open cursors exceeded

check for this. select count(*) from v$open_cursor;

Login with system previleges and type show parameter OPEN_CURSOR

Alter cursors
ALTER SYSTEM SET OPEN_CURSORS=4000 SCOPE=BOTH;

Soln: restart DB


Error: ORA-12514: TNS: listener does not currently know of service requested in connect descriptor.

I made these changes and since then i did not see the problem.
ORACLE_HOME\network\admin\listener.ora, Please look for HOST and change that to
HOST=Windows hostname
ORACLE_HOME\network\admin\tnsnames.ora
For the 10g local instance tnsentry: give localhost instead of the windows hostname
HOST=localhost

Sources: Raj Sangavi