ITPub博客

oracle的not like的一个例子[@more@]有个表t 的一个字段userid其中里面带"_",譬如:SQL>select * from t;USERID -----------xiaguan_aj xiaguan_sb zenxin xiaguan_hb xiaguan_yg请问我想查询userid不带"_"的userid.怎么我就查询不出来呢?SQL>select * from t where userid not like '%_%';查询结果为空.但是里面有一个zenxin的结果啊?请教了一下高手，原来是要转义一下:SQL> select * from t where userid not like '%=_%' escape '=';

How to set multiple events[@more@]in spfile: the name is 'event' and values are quoted then comma separated as in: ALTER SYSTEM SET event='10235 trace name context forever, level 2','27072 trace name errorstack level 3' COMMENT='TEST' SCOPE=SPFILE; If bad syntax, you will get ORA-2194 at next database restart. so double check and copy old spfile just in case. You can remove all events with: ALTER SYSTEM RESET EVENT SCOPE=SPFILE SID='*' ; in memory: the name is 'events' and values are column-sepa

Trace a user's session from its beginning[@more@]If you have to trace a "fast" user session, (for example, initiated by clicking on a button on the GUI of an application), you don't have enough time to query V$SESSION to initiate a trace for the session. A very easy way is to use a database "on logon" trigger; here is an example: CREATE OR REPLACE TRIGGER ON_LOGON_SCOTT AFTER LOGON ON DATABASE WHEN ( USER = 'SCOTT' ) BEGIN execute immediate 'alter session set events ''10046 t

通过sm$ts_free,sm$ts_used,sm$ts_avail快速查看表空间使用情况[@more@]通过sys用户登录SQL>conn / as sysdbaSQL> select tablespace_name,sum(bytes)/1024/1024 "size(M)" from sm$ts_avail group by tablespace_name;TABLESPACE_NAME size(M)------------------------------ ----------ASB 1424CDT_LSTD 1024DEV 1024DRSYS 20INDX 20RBS 50RCPJ 1024SMIS_DATA 1024SYSTEM 280TEMP 179.375TOOLS 10TS_TCMS_DATA 1024USERS 2013 rows selectedSQL> select tablespace_name,sum(bytes)/1024/1024 "size(M)" f

查看ORACLE_HOME的另外一个方法[@more@][root@TestServer conf]# ps -ef |grep oraoracle 8867 1 0 16:08 ? 00:00:00 ora_pmon_orcloracle 8869 1 0 16:08 ? 00:00:00 ora_mman_orcloracle 8871 1 0 16:08 ? 00:00:00 ora_dbw0_orcloracle 8873 1 0 16:08 ? 00:00:00 ora_lgwr_orcloracle 8875 1 0 16:08 ? 00:00:00 ora_ckpt_orcloracle 8877 1 0 16:08 ? 00:00:00 ora_smon_orcloracle 8879 1 0 16:08 ? 00:00:00 ora_reco_

EXP-00008: ORACLE error 6553 encounteredORA-06553: PLS-561: character set mismatch on value for parameter 'SHORTNAME'EXP-00000: Export terminated unsuccessfully[@more@]SQL>conn / as sysdbaSQL> col view_name format a15SQL> col text format a60SQL> set long 10000000SQL> select text from dba_views where view_name = ' EXU81JAVT'TEXT------------------------------------------------------------SELECT obj#FROM sys.obj$WHERE name = 'oracle/aurora/rdbms/DbmsJava'AND type# = 29AND owner# = 0A

grep的-E参数用法[@more@]查看oracle日志的错误和警告$cat alter_ORCL.log |grep -E 'ORA-|WARNING:' ORA-00313: open failed for members of log group 1 of thread 1ORA-00312: online log 1 thread 1: '/oracle8/oradata/dtgdb/redo03.log'ORA-27037: unable to obtain file statusORA-00313: open failed for members of log group 2 of thread 1ORA-00312: online log 2 thread 1: '/oracle8/oradata/dtgdb/redo02.log'ORA-27037: unable to obtain file statusORA-00313: open failed for members of log group 3 of thread 1ORA-00

ORA-600 [2252] Occurs During Alter Database Open[@more@]Subject: ORA-600 [2252] Occurs During Alter Database OpenDoc ID: Note:253977.1 Type: PROBLEMLast Revision Date: 19-OCT-2004 Status: PUBLISHEDSymptom(s)After shutdown of the database you receive the following error during alter database open: ora-600 [2252]You did not restore the database before receiving this error.Cause:The system date is lower than the date of the shutdown. While opening the database, Oracle compares the given SCN value w

ORA-00600: internal error code, arguments: [keltnfy-ldmInit], [46], [1], [], [], [], [], [][@more@]ORA-00600: internal error code, arguments: [keltnfy-ldmInit], [46], [1], [], [], [], [], []原因是:修改了主机名以后，忘记修了/etc/hosts文件redhat linux应该同时修改/etc/hosts和/etc/sysconfig/network文件中的hostname的值.如果忘了将/etc/hosts中127.0.0.1对应的机器修改成/etc/sysconfig/network文件中的一样，将会导致上面描述的错误。

查看oracle的锁的相关信息[@more@]查看锁的用户和等待锁的用户相关信息select 'blockers('||sb.sid||':'||sb.serial#||'-'||sb.username||')-'||qb.sql_text blockers,'waiters('||sw.sid||':'||sw.serial#||'-'||sw.username||')-'||qw.sql_text waitersfrom v$lock lb,v$lock lw,v$session sb,v$session sw,v$sql qb,v$sql qwwhere lb.sid=sb.sidand lw.sid=sw.sidand sb.prev_sql_addr=qb.addressand sw.sql_address=qw.addressand lb.id1=lw.id1and sb.lockwait is nulland sw.lockwait is not nulland lb.block=1;BLOCKERS

ORA-00101: invalid specification for system parameter MTS_DISPATCHERS[@more@]ORA-00101: invalid specification for system parameter MTS_DISPATCHERS这个问题是由于修改了主机名和IP地址以后，没有修改$ORACLE_HOME/network/admin/下面的listener.ora文件中的host地址引起的。修改一下listener.ora文件中的host地址即可LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1)) (ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 1521)) ) )