ITPub博客

如果startup nomount卡死不动，而且没有看到警告信息，如果是32位操作系统，就要考虑去查一下alert_xxx.log文件的大小是否超过了4G。因为启动是读取pfile，spfile后分配内存的速度是很快的，后台还做了一步就是记录这个启动的日志，如果这个日志满了，就卡在那里不启动数据库了。[@more@]

ALTER PROFILE "DEFAULT" LIMIT PASSWORD_LIFE_TIME UNLIMITED;alter user jbpm_demo identified by jbpm_demo_0591_888;[@more@]

Copyright (c) 1982, 2007, Oracle. All rights reserved. 连接到: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> shutdown immediate ORA-01109: 数据库未打开 已经卸载数据库。 ORACLE 例程已经关闭。 SQL> startup mount ORACLE 例程已经启动。 Total System Global Area 397557760 bytes Fixed Size 1333452 bytes Variable Size 360711988 bytes Database Buffers 29360128 bytes Redo Buffers 6152192 bytes

oracle中查看表是否被锁 查看表是否被锁 SELECT /*+ rule*/ a.sid, b.owner, object_name, object_type FROM v$lock a, all_objects b WHERE TYPE = 'TM' and a.id1 = b.object_id; 查到的都是被锁的表 这样可以把它杀掉 SELECT sid,serial# FROM v$session WHERE sid = &sid; alter system kill session ‘sid,serial#’; 要想查到更详细的信息select a.sid, b.serial#, b.username, b.osuser, b.machine, b.program, c.object_name,d.spid , decode(a.type, 'mr', 'media recovery', 'rt',

转自 http://tech.it168.com/db/2007-09-07/200709071114985.shtml 【IT168技术文档】 摘要：（简要介绍Oracle11g SQL的新功能 pivot/unpivot 的使用方法以及如何使用它们做到行列转换. 蓄势以久的Oracle 11g 终于七月敲锣打鼓隆重推出，接下来就是网上漫天盖地的新功能介绍。11g面向开发的新功能本来就不多，掰着手指头也就是pivot和查询结果缓存的新Hint。本以为不久就会有人详述，谁知盼到两眼欲穿，大家还是翻来覆去的讨论DBA的自动分区之类。Oracle自己的门脸上到是每每用客气的冷漠写着“马上就来” (coming soon)，可这马上

算法有点复杂： select to_date( '2005-01-01 ', 'yyyy-mm-dd ') +mod(7 - to_number(to_char(to_date( '2005-01-01 ', 'yyyy-mm-dd '), 'd ')) + i,7) + (n - 1)*7 as bngdate, to_date( '2005-01-01 ', 'yyyy-mm-dd ') +mod(7 - to_number(to_char(to_date( '2005-01-01 ', 'yyyy-mm-dd '), 'd ')) + i,7) + n*7-1 as enddate from dual; 上式中有两个参数,n和i,n为第n个完整周，i为每星期起始日，i=1表示从星期日开始算一周的第一天，i=2表示从星期一开始算一周的第一天，依次类推....。 将2005换成其它年份，就可以算其它年份的。可以

select a, substr(replace(max(sys_connect_by_path(b, ';')), ';', ','), 2) b from (select a, b, (row_number() over(order by a, b) + dense_rank() over(order by a)) rn, min(b) over(partition by a) mb from t) start with b = mbconnect by rn - 1 = prior rn group by aSQL> select * from t order by a; A B---------- -------------------- 1 01 1 02 1 AA 1 03 2 05 2 BB 3 06

select event_id, item_id, event_title, event_content, bgn_time, end_time,versions_starttime, versions_endtime, versions_xid, versions_operation from calendar_detail versions between timestamp minvalue and maxvalue where versions_operation='D'[@more@]

select u_dump.value || '/' || db_name.value || '_ora_' || v$process.spid || nvl2(v$process.traceid, '_' || v$process.traceid, null ) || '.trc' "Trace File"from v$parameter u_dump cross join v$parameter db_name cross join v$process join v$session on v$process.addr = v$session.paddrwhere u_dump.name = 'user_dump_dest' and db_name.name = 'db_name' and v$session.audsid=sys_context('userenv','sessionid');11g以后的版本就可以直接

vsize[@more@]

显示一年里的每一天SELECT trunc(sysdate, 'year') + level -1 FROM dual CONNECT BY level <= 366;显示从 100到200select 100+level from dual connect by level<=100[@more@]

2009-9-16 6:47的时间停电了,导致数据库出现错误EM home页面出现以两个错误1:STREAMS 捕获进程 "CAP_TEACHPLAN_N" 已中止, 出现 ORA-12802:Internal error (ORA-600[4194]) detected in D:oraclediagrdbmsgoldbridgoldbridalertlog.xml at time/line number: Wed Sep 16 17:15:05 2009/197189.POSSIBLE ROLLBACK SEGMENT CORRUPTION查看详细日志,发现是datafile 3出了问题,查看dba_data_files发现是undotbs1出了问题,由于没有做备份,只好重新建立一个新的undo表空间undotbs2来代替16-Sep-2009 16:23:58 CST INCIDENT_ERROR 1 15

不懂什么原因list backup无法显示出前三天做的两个备份,只显示了自动备份的控制文件和参数文件List of Backup Sets===================BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ -------------------1 Full 7.11M DISK 00:00:00 2009-09-08 15:33:00 BP Key: 1 Status: AVAILABLE Compressed: NO Tag: TAG20090908T153300 Piece Name: /oracle/add_disk/flash_recovery_area/TEST/autobackup/2009_09_08

例如piece '/xxx/o1_xx_.bkp'的backupset 为20,当alter database mount之后,数据库的备份集BACKUPSET>=20的所有备份将会无效,在此状态下登录库后(rman target /;) ,用list backup命令也只会显示到备份集到19;restore 也无法使用19以后的备份集.[@more@]

没有设置oracle的环境变量.recover until time会报字符出错su - oracleexport NLS_LANG=export NLS_DATE_FORMAT=rman target /;run{set until time ='2009-09-04 10:56:01';}executing command: SET until clauseRMAN-00571: ===========================================================RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============RMAN-00571: ===========================================================RMAN-03002: failure of set command at 09/04/2009 16:57:35ORA-01861: literal does not match

1：访问EM2：由于在运行大数据量操作时，db_recovery_file_dest目录被急剧增加的归档占满，em报出archiver在归档日志的时候出错3：用rmam，delete noprompt archivelog all;(说明测试数据库所以归档日志不重要可以删除)3：继续访问EM，无法成功，报错信息如下Enterprise Manager is not able to connect to the database instance. The state of the components are listed below. Status Open But Unavailable Host localhost.localdomain Port 1521 SID test Oracle Home /oracle/product/10.2.0/db_1 Details Archiver is unable to ar

安装步骤来源于官方的p6810189_10204_Linux-x86.zip的README.HTML,由于里面的步骤集合了RAC,ASM,single intance,所以把single intance的安装步骤抽取出来1:升级时区包（这一步通常不需要，只是征对有利用到TIMESTAMP WITH LOCAL TIME ZONE (TSLTZ) , TIMESTAMP WITH TIME ZONE (TSTZ) 的数据类型和函数 TZ_OFFSET ）安装之前先查看这个版本号，正常升级后这个版本值是4SQL> select version from v$timezone_file;VERSION ---------- 2 下载包utltzpv4.zip，可以在google上找一下，在itpub.net里有下载地址直接@utltzpv4.sql;2:关闭orac

由于修改了操作系统的时间区域，在运行emctl start dbconsole的时候会出现如下信息：Timezone mismatch: The agentTZRegion value (US/Eastern) in /oracle/product/10.2.0/db_1/localhost.localdomain_test/sysman/config/emd.propertiesdoes not match the current environment TZ setting(PRC).The dbconsole cannot run with this mismatch.If US/Eastern is the correct timezone, set your timezone environment variable to US/Eastern and repeat the 'emctl start dbconsole' operation.If US/Eastern is not the correct timezo

由于数据库IP变更,引起DBCONSOLE异常,重新配置一下EMemca -repos dropemca -repos createemca -config dbcontrol db[@more@]重新配置dbconsole的步骤

http://www.databasedesign-resource.com[@more@]