Wed Jan 30 09:24:04 GMT 2008

oracle cookbook

After default install of oracle at win32, here is the step of creating user:

1) sqlplus /noloh (enter sqlplus shell without login)
2) connect oracle/oracle as sysdba (super user connection)
3) CREATE USER CITY IDENTIFIED BY city DEFAULT TABLESPACE "USERS" TEMPORARY TABLESPACE "TEMP" PROFILE DEFAULT ACCOUNT UNLOCK;
4) GRANT "CONNECT" TO CITY;
5) GRANT "RESOURCE" TO CITY;
grant dba to CITY;

-- Grant/Revoke system privileges
grant create view to CITY;
grant unlimited tablespace to CITY;

imp CITY/city full=y file=c:\xxxxx.dmp

6) sqlplus city (try login)

About create table space

create tablespace TBL01 datafile 'H:\oracle\oradata\ora9\TBL01.dbf' size 50M extent management local segment space management auto;

Other useful views
1) sys.dba_tablespaces;
2) sys.dba_users
3) v$database
4) dba_data_files
5) v$datafile

Start and stop oracle batch
net start OracleCSService
net start OracleDBConsole[db name]
net start OracleOraDb10g_home1iSQL*Plus
net start OracleOraDb10g_home1TNSListener
net start OracleService[db name]

net stop OracleCSService
net stop OracleDBConsole[db name]
net stop OracleOraDb10g_home1iSQL*Plus
net stop OracleOraDb10g_home1TNSListener
net stop OracleService[db name]

An useful link: http://agents.csie.ntu.edu.tw/Projects/VF/docs/use_oracle.txt

How to install oracle in Redhat 9 - http://www.linuxdevcenter.com/lpt/a/4141

Check store procedure issues: select * from user_errors

If there is join like a.column1 = b.column2, and column1 in DATE datatype while column2 is VARCHAR2 datatype, oracle will auto-cast it in JDeveloper, but always return false if I call that stored procedure using JDBC.

Show plsql function detail implementation: select text from user_source where name = MY_PROCEDURE order by line; - http://forums.oracle.com/forums/thread.jspa?threadID=515948

It is possible to get NullPointerException from oracle driver with IBM JDK... it look like IBM issue - http://www.ibm.com[..]s/forums/message.jspa?messageID=13980641

plsql result caching - http://gojko.net[..]ed-up-database-code-with-result-caching/

Oracle date foramt - http://www.oradev.com/oracle_date_format.jsp


(google search) (amazon search)
1 2 3 second