--- 需要考虑最后一个字段 //get fields of one row from a char* str comparted by the compart char long CGMDBTable::GetLineFromStr(char* str, char seg) { char* phead = str; char* ptail = str; int ipos = 0; while (*ptail != '/0') { if (*ptail == seg) { if (ptail != phead) { *ptail = '/0'; if ((pFields[ipos])->type == DB_SQL_T_DOUBLE) { *(pFields[ipos]) = atof(phead); } else { *(pFields[ipos]) = phead; } } phead = ptail + 1; ipos++; } ptail++; } if (ptail != phead) { *ptail = '/0'; if ((pFields[ipos])->type == DB_SQL_T_DOUBLE) { *(pFields[ipos]) = atof(phead); } else { *(pFields[ipos]) = phead; } } Insert(); return 1; } -- 注意long 和 double类型不能够使用内存拷贝的方式赋值,因为存储方式不一样 -- 体会封装的极大好处,如oci函数的封装 -- 2011-3-15 sqlldr 生成的日志中,有sqlldr的一些详细参数,如入库方式truncate还是append --1.查看锁表进程 select s.SID, s.SERIAL#, lo.ORACLE_USERNAME, lo.OS_USER_NAME, do.object_name, lo.LOCKED_MODE from v$locked_object lo, dba_objects do, v$session s where lo.OBJECT_ID = do.object_id and lo.SESSION_ID = s.SID; --杀掉锁表进程2 --根据刚才查出的锁表进程的SID和SERIAL#的值,执行alter system kill session 'SID,SERIAL#'解除锁定 如:alter system kill session '738,1429' 查看Oracle错误号含义 oerr ora 942 00942, 00000, "table or view does not exist" // *Cause: // *Action: date -d yesterday '+%Y%m%d' 20110320 > date -d tomorrow '+%Y%m%d' 20110322 用来判断月末非常有用 date -d tomorrow '+%d' 如果是01 则当天是月末最后一天 -- 注意group by的用法 select id, count(id) tt from student t group by id having count(id) > 1; sqlplus可以采用这种方式进行连接 >sqlplus niop_dw/niop_dw@10.41.20.113:1521/orcl 2011-4-26 在使用gmdb进行批量数据插入的时候,如果批量条数太大,如10000,会出错 -- cat .profile export ORACLE_BASE=$HOME export ORACLE_HOME=$ORACLE_BASE/product/11.1.0/db_1 export ORACLE_SID=orcl export NLS_LANG=american_america.ZHS16GBK export NLS_DATE_FORMAT="YYYY-MM-DD HH:mi:ss" export ORA_NLS33=$ORACLE_HOME/nls/data export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH export PATH=$PATH:$ORACLE_HOME/bin -- COCISession ociSess; ociSess.Connect(pValue); COCIStmt conn(&ociSess); OciTable tb; tb.SetConnection(&conn); 2011-5-6 class A { public: // A() // { // } A(int i) { a = i; } protected: private: int a; }; class B:public A{ B() { } B(int i, int j):A(i) { b = j; } int b; }; int main() { } 这样会出错, 基类的默认构造函数被覆盖了 -- bi0001:~ # mkdir /etc/TimesTen bi0001:~ # touch /etc/TimesTen/instance_info bi0001:~ # groupadd timesten bi0001:~ # groupadd ttadmin bi0001:~ # bi0001:~ # useradd -g ttadmin -m -d /ttadm -s /bin/ksh ttadm bi0001:~ # bi0001:~ # passwd ttadm Changing password for ttadm. New Password: Bad password: too simple Reenter New Password: Password changed. bi0001:~ # bi0001:~ # bi0001:~ # chgrp -R ttadmin /etc/TimesTen bi0001:~ # chmod 775 /etc/TimesTen bi0001:~ # chmod 664 /etc/TimesTen/* bi0001:~ # bi0001:~ # chown -R ttadm:timesten /ttadm TNS_ADMIN will be set to /oracle/app/oracle/product/11.1.0/db_1/network/admin You can change TNS_ADMIN later by running <install_dir>/bin/ttmodinstall. ./ttisql "DSN=TT_1121" Command> create user test identified by test; User created. Command> grant create session, create any table to test; ./ttisql "DSN=TT_1121;uid=test;pwd=test" ttDaemonAdmin –restart --- 查询时间点数据,在delete删除数据后进行数据恢复时很有用 select * from student as of timestamp to_timestamp('2011-05-20 16:46:40', 'yyyy-mm-dd hh24:mi:ss'); 迷糊了,竟然写出了这样的语句 insert into test(2, 'HanMm') 报错后还以为是内存库的问题,竟然还去重启,很久才看出来 insert into test values(2, 'HanMm') 编译TT ODBC g++ -I/ttadm/TimesTen/tt1121/include -L/ttadm/TimesTen/tt1121/lib -ltten -lgcc_s noname1.cpp 客户端 g++ -I/ttadm/TimesTen/tt1121/include -L/ttadm/TimesTen/tt1121/lib -lttclient -lgcc_s ins.cpp ttadm@bi0001:/ttadm/TimesTen/tt1121/test> ./a.out [TimesTen][TimesTen 11.2.1.8.0 CLIENT]Cannot find the requested DSN (tt_1121) in ODBCINI /ttadm/TimesTen/tt1121/info/sys.odbc.ini 设置环境 ttadm@bi0001:/ttadm/TimesTen/tt1121/quickstart> . ./ttquickstartenv.sh NOTE: TNS_ADMIN is already set in environment - /ttadm/TimesTen/tt1121/network/admin/samples LD_LIBRARY_PATH set to /ttadm/TimesTen/tt1121/lib:/ttadm/TimesTen/tt1121/ttoracle_home/instantclient_11_1:/oracle/app/oracle/product/11.1.0/db_1/lib ANT_HOME set to /ttadm/TimesTen/tt1121/3rdparty/ant PATH set to /ttadm/TimesTen/tt1121/bin:/ttadm/TimesTen/tt1121/quickstart/sample_code/oci:/ttadm/TimesTen/tt1121/quickstart/sample_code/odbc:/ttadm/TimesTen/tt1121/quickstart/sample_code/odbc/xla:/ttadm/TimesTen/tt1121/quickstart/sample_code/jdbc:/ttadm/TimesTen/tt1121/quickstart/sample_code/odbc_drivermgr:/ttadm/TimesTen/tt1121/quickstart/sample_code/proc:/ttadm/TimesTen/tt1121/quickstart/sample_code/ttclasses:/ttadm/TimesTen/tt1121/quickstart/sample_code/ttclasses/xla:/ttadm/TimesTen/tt1121/ttoracle_home/instantclient_11_1:/ttadm/TimesTen/tt1121/ttoracle_home/instantclient_11_1/sdk:/ttadm/TimesTen/tt1121/3rdparty/ant/bin:/ttadm/bin:/usr/local/bin:/usr/bin:/usr/X11R6/bin:/bin:/usr/games:/opt/gnome/bin:/opt/kde3/bin:/usr/lib/mit/bin:/usr/lib/mit/sbin:/oracle/app/oracle/product/11.1.0/db_1/bin CLASSPATH set to /ttadm/TimesTen/tt1121/lib/ttjdbc5.jar:/ttadm/TimesTen/tt1121/lib/orai18n.jar:/ttadm/TimesTen/tt1121/lib/timestenjmsxla.jar:/ttadm/TimesTen/tt1121/3rdparty/jms1.1/lib/jms.jar:/ttadm/TimesTen/tt1121/quickstart:/ttadm/TimesTen/tt1121/quickstart/sample_code/jdbc:. NOTE: The demos use system DSNs defined in /ttadm/TimesTen/tt1121/info/sys.odbc.ini To use your own DSNs, they must be defined in ~/.odbc.ini, or you may set the environment variable ODBCINI to point to the correct file. 之前没有调通的原因可能是LD_LIBRARY_PATH 中没有配置新的库版本 通过修改LD_LIBRARY_PATH进行验证发现就是这个问题 11.1.0.6.0 版本较低 通过TNS_ADMIN这个环境变量来找tnsnames.ora 这个文件,根据给定的tnsname来找到SERVICE_NAME,再到sys.odbc.ini 中找起对应的内存库配置 test0=(DESCRIPTION=(CONNECT_DATA = (SERVICE_NAME = client0)(SERVER = timesten_direct))) 客户端 testct=(DESCRIPTION=(CONNECT_DATA = (SERVICE_NAME = client0)(SERVER = timesten_client))) 使用TT提供的库可以向Oracle中插入数据 ttadm@bi0001:/ttadm/TimesTen/tt1121/network/admin/samples> export TNS_ADMIN=/ttadm/TimesTen/tt1121/network/admin/samples ttadm@bi0001:/ttadm/TimesTen/tt1121/network/admin/samples> ttadm@bi0001:/ttadm/TimesTen/tt1121/network/admin/samples> ttadm@bi0001:/ttadm/TimesTen/tt1121/network/admin/samples> CD /ttadm/TimesTen/tt1121/quickstart/sample_code/oci -ksh: CD: not found [No such file or directory] ttadm@bi0001:/ttadm/TimesTen/tt1121/network/admin/samples> cd /ttadm/TimesTen/tt1121/quickstart/sample_code/oci ttadm@bi0001:/ttadm/TimesTen/tt1121/quickstart/sample_code/oci> ls addemp.c Makefile mo plsqlOCI.c psswds.c README_oci.html README_oci.txt tptbmOCI.c ttadm@bi0001:/ttadm/TimesTen/tt1121/quickstart/sample_code/oci> ttadm@bi0001:/ttadm/TimesTen/tt1121/quickstart/sample_code/oci> ttadm@bi0001:/ttadm/TimesTen/tt1121/quickstart/sample_code/oci> ./mo Dandan count = 1 over date -d ’3 days ago’ date -d tomorrow date -d ’3 days’ --- # The Timesten Client entries follow the pattern of: # <NAME> =(DESCRIPTION=(CONNECT_DATA = (SERVICE_NAME = <NAME> )(SERVER = timesten_client))) test0=(DESCRIPTION=(CONNECT_DATA = (SERVICE_NAME = client0)(SERVER = timesten_direct))) testct=(DESCRIPTION=(CONNECT_DATA = (SERVICE_NAME = client0)(SERVER = timesten_client))) -- [client0] TTC_SERVER=tthost0 TTC_SERVER_DSN=TT_1121 -- ./mo test/test@testct g++ -o /ttadm/TimesTen/tt1121/test/bin/mo -lpthread -L/ttadm/TimesTen/tt1121/ttoracle_home/instantclient_11_1 -lclntsh main.o database.o OraDB.o SqlUtils.o CSysLog.o SimpleLog.o Field.o OciTable.o -- OCIStmtPrepare 在调用错误的sql下,没有返回错误 ./mo test/test@tt_1121 在oci程序中,1个连接中的公用commit操作 多个线程同时向同一个表里插入数据会出错 测试发现:在同一个连接中,如果有2个线程同时进行插入数据操作(调用OCIStmtExecute),会出错 DBCON_QUERY_ERROR failed! OCIServerAttach error[ORA-01013: user requested cancel of current operation ]! DBCON_QUERY_ERROR failed! OCIServerAttach error[ORA-24909: call in progress. Current operation cancelled ]! DBCON_QUERY_ERROR failed! OCIServerAttach error[ORA-03114: not connected to ORACLE ]! DBCON_QUERY_ERROR failed! OCIServerAttach error[ORA-03114: not connected to ORACLE ]! 4 DBCON_QUERY_ERROR failed! OCIServerAttach error[ORA-03114: not connected to ORACLE ]! Segmentation fault 其效果相当于在一个界面中,先执行一个sql句子,同时再执行另一个sql,就会出错 ttdaemonadmin -restart --- 1个连接中insert没有commit时,在另一个连接中对同一个表进行truncate时,会出错 ORA-00054 资源正忙 --- 2011-6-14 for (int i = 0; i < number; i++) { pthread_t id; cout << "i = " << i << endl; pthread_create(&id, NULL, fun, &i); //sleep(3); } 在多线程使用的时候出现问题:number 的值为2 , 在函数fun中调用i的时候,其值变成了2 不是想要的0 1 原因是传入的是i的地址,多个线程共享这个变量,循环很快的改变了i的值 这种情况下,需要在fun中立即使用一个变量保存i的值,来保证程序安全 -- top 命令 在显示后,输入M ,使显示按照内存占用排序 应用程序中访问TimesTen比较占用内存 PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND 24991 ttadm 16 0 447m 31m 20m S 0 0.4 0:00.02 mo 执行的时候程序占用的内存会逐渐增加,后来就趋于稳定 -- 2011-6-16 使用oci连接oracle时,可以使用EZCONNECT 格式为 192.168.111.102:1521/orcl:server ./mo test/test1@192.168.111.102:1521/orcl:server 或者 ./mo test/test1@192.168.111.102:1521/orcl In TimesTen, sample copies of tnsnames.ora and sqlnet.ora are in the install_dir/network/admin/samples directory. Here is the sqlnet.ora file that TimesTen provides, which supports both tnsnames and easy connect ("EZCONNECT"): # To use ezconnect syntax or tnsnames, the following entries must be # included in the sqlnet.ora configuration. # NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT) With this file, TimesTen will first look for tnsnames syntax in your OCI logon calls. If it cannot find tnsnames syntax, it will look for easy connect syntax. -- ORACLE_SID 数据库实例名 LD_LIBRARY_PATH 对编译也有影响 LD_LIBRARY_PATH=/ttadm/TimesTen/tt1121/lib:/ttadm/TimesTen/tt1121/lib:/ttadm/TimesTen/tt1121/ttoracle_home/instantclient_11_1:/oracle/app/oracle/product/11.1.0/db_1/lib ORA-44818: Unable to load NLS initialization data from /ttadm/TimesTen/tt1121/nls/data ORA-44818: Error opening ODBCINI file /ttadm/TimesTen/tt1121/info/sys.odbc.ini: Permission denied ORA-57000: TT0622: User oracle does not have group permission to connect to data store -- file "db.c", lineno 9722, procedure "sbDbConnect" -- TimesTen 在安装的时候设置的有访问权限限制,如果采用的默认设置,group 其他用户访问TimesTen时,需要给用户设置组(使用root用户来设置),注意,不要忘记了加上该用户以前所在的组 usermod -G oinstall,ttadmin oracle usermod: `oinstall' is primary group name. -- groups 查看用户所在的组,一个用户可以在多个组中