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