Sun Oct 23 00:00:44 HKT 2011
From
/weblog/database/vendor
A tool from M$ to help sql server user to manage change of database:
http://msdn.microsoft.com/vstudio/teamsystem/products/dbpro/ . Other similar product is DBGhost, also only work for SQL server
http://www.innovartis.co.uk/home.aspx There is a free version of SQL server 2005 call SQL server express, which just like MSDE
http://www.microsoft.com/sql/editions/express/redistregister.mspx Here is a comparison:
http://www.sqlmag.com[..]le/ArticleID/49618/sql_server_49618.html , may be we can use that instead of MSDE 2005 and discoutinue support of SQL server 2000 so that we can test one thing less?
Import CSV to SQL server -
http://www.sqlservercurry.com[..]oad-comma-delimited-file-csv-in-sql.html Shriking DB cause performance problem... not sure how long this finding is hold -
http://blog.sqlauthority.com[..]eases-fragmentation-reduces-performance/ http://beyondrelational.com[..]ver-storage-internals-part-1-basics.aspx
(google search)
(amazon search)
Sat Mar 05 15:27:37 HKT 2011
From
/weblog/database/vendor
Some comment saying that you cannot complain oracle for non-standard setup... However, should I break anything else just to install oracle? Anyway, may be a useful resource of installing oracle cluster -
http://thedailywtf.com/forums/65743/ShowPost.aspx Oracle SQL links -
http://mycodeblog.blogspot.com/2007/06/sql-cheat-sheet.html Pure java solution to full text search -
http://www.infoq.com/news/2007/10/lucene-oracle http://marceloochoa.blogspot.com[..]nning-lucene-inside-your-oracle-jvm.html http://dbprism.cvs.sourceforge.net/dbprism/ojvm/ Oracle does allow definitions of user-defined types.Please see
http://download.oracle.com[..]er.111/b28286/sql_elements001.htm#i46376 User-defined types can then be used for column definitions. See e.g.
the fourth example given here
http://download.oracle.com[..].111/b28286/statements_7002.htm#i2062833 Oracle also allows creation of a table based on one user-defined type,
i.e. an "object table".
See here
http://download.oracle.com[..].111/b28286/statements_7002.htm#i2159410 User-defined types can be changed; see
http://download.oracle.com[..].111/b28286/statements_4002.htm#i2057828 Oracle even offers an option as to whether existing data in tables
based on previous type definition is converted or not; see
http://download.oracle.com[..].111/b28286/statements_4002.htm#i2079300 There are a couple of pages in the Object-Relational Developer's Guide explaining the consequences, briefly...
Note: all links above are to Oracle 11g documentation, but many, if not all, of these features have been available since 9i.
row base permission -
http://technotes.towardsjob.com[..]/oracle/virtual-private-database-oracle/ How fetch size can affect performance -
http://www.skill-guru.com[..]jdbc-performance-tuning-with-fetch-size/
(google search)
(amazon search)
Wed Jan 30 17:24:04 HKT 2008
From
/weblog/database/vendor
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)
Tue May 16 11:50:22 HKT 2006
From
/weblog/database/vendor
SQL 2005 JDBC fix the resultset re-read throws exception problem. However, you need to use executeUpdate() for insert,update and delete instead of execute() for SQL 2005 JDBC, otherwise will throw exception on this.
Other that, statement.executeBatch() will throws exception:
http://lab.msdn.microsoft.com[..]kid=e13a8009-7466-4803-ba19-6bfd4b5f8966
(google search)
(amazon search)