ITPub博客

安装数据库后。这样搞下还是很不错的[@more@]SQL> conn / as sysdba Connected.SQL> @$ORACLE_HOME/rdbms/admin/utlxplan Table created.SQL> create public synonym plan_table for plan_table; create public synonym plan_table for plan_ta

设置静态路由[@more@]1：/etc/sysconfig/network-scripts/route-eth0 档案加入192.168.2.0/24 via 192.168.1.2532：route add -net 网段 netmask xxx gw xxx如：route add -net 192.168.0.0 netmask 255.255.0.0 gw 192.168.0.201

使用OMF自动创建DBF[@more@]SQL> alter system set db_create_file_dest='/u01/app/oracle/oradata/southdb' scope=both; System altered.SQL> alter system set db_create_online_log_dest_1='/u01/app/oracle/oradata/southdb' scope=both; System altered.SQL> create tablespace omar extent management local uniform size 1m segment space management auto; Tablespace created.SQL> select name,bytes/1024/1024

根据EYGLE的文章自己测试了下。[@more@]SQL> select sid,username,status 2 from v$session 3 where username is not null; SID USERNAME STATUS---------- ------------------------------ --------28 SYS ACTIVE29 READONLY INACTIVESQL> select 'kill -9 '||spid from v$process 2 where addr=(select paddr from v$session where sid=&did); Enter value for did: 29 old 2: where addr=(select paddr from v$session where sid=&did)new 2: where addr=(select paddr from v$session where sid=29)'KILL-9'||SPID-----

例子[@more@]SQL> select name from v$datafile; NAME--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------/u01/app/oracle/oradata/southdb/system01.dbf/u01/app/oracle/oradata/southdb/tools01.dbf/u01/app/oracle/oradata/southdb/rbs01.dbf/u01/app/oracle/oradata/so

http://www.loveunix.cn/html/32/1132-13122.html[@more@]http://www.loveunix.cn/html/32/1132-13122.html

[oracle] 阿里巴巴的笔试题 转贴 [@more@]一:SQL tuning 类　　1:列举几种表连接方式　　2:不借助第三方工具，怎样查看sql的执行计划　　3:如何使用CBO,CBO与RULE的区别　　4:如何定位重要(消耗资源多)的SQL　　5:如何跟踪某个session的SQL　　6:SQL调整最关注的是什么　　7:说说你对索引的认识(索引的结构、对dml影响、对查询影响、为什么提高查询性能)　　8:使用索引查询一定能提高查询的性能吗?为什么　　9:绑定变量是什么?绑定变量有什么优缺点?　　10:如何稳定(固定)执行计划　　11:和排序相关的内存在8i和9i分别怎样调整，临时表空间

一个SQL[@more@]SELECT rn,COUNT(*) FROM(SELECT t.Phone, t.Create_Date, b.Rn, b.y_DateFROM Yzqj4_Molog t, (SELECT Ceil(Rownum / 30) Rn, To_Date('2007-08-06 00:00', 'yyyy-MM-dd HH24:mi') + (Rownum - 1) / 24 / 60 y_Date FROM Yzqj4_Molog t WHERE Rownum <= 1 * 24 * 60) bWHERE To_Char(t.Create_Date, 'yyyy-mm-dd hh24:mi') = To_Char(b.y_Date, 'yyyy-mm-dd hh24:mi')AND Cid = '2301'AND Create_Date > To_Date('2007-08-06 00:00:00', 'yyyy-MM-dd HH24:mi:ss')AND Create_Date

ORA-01157: cannot identify/lock data file 68 - see DBWR trace fileORA-01110: data file 68: '/u01/app/oracle/oradata/querydb/phs_data01.dbf'[@more@]SQL> conn / as sysdbaConnected to an idle instance.SQL> startupORACLE instance started.Total System Global Area 994132128 bytesFixed Size 73888 bytesVariable Size 364249088 bytesDatabase Buffers 629145600 bytesRedo Buffers 663552 bytesDatabase mounted.ORA-01157: cannot identify/lock data f

配置好了后开始做备份。WIN平台上的NETBACKUP报错。RMAN-10035 ORA-19557。。。[@more@]RMAN-03022: compiling command: allocateRMAN-03023: executing command: allocateRMAN-00571: ===========================================================RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============RMAN-00571: ===========================================================RMAN-03007: retryable error occurred during execution of command: allocateRMAN-07004: unhandled exception during command execut

ORA-29531: no method get_crlf in class oracle/plsql/net/TCPConnection[@more@]fact: Oracle Server - Enterprise Edition 8.1.7 symptom: Error when using UTL_SMTP symptom: ORA-29531: no method get_crlf in class oracle/plsql/net/TCPConnection symptom: ORA-6512: at "SYS.UTL_TCP" symptom: ORA-6512: at "SYS.UTL_SMTP" change: patchset 8.1.7.2 or above applied cause: The steps in the patchset readme for 8172+ does not include re- running initplsj or manually loading this jar. Original

今天因停点导致宕机，汗哦。这样的事情都发生了。[@more@]处理：远程SSH公密登陆服务器，发现ORACLE的HOME目录竟然丢失了。相应的.bash_profile丢失了。利用备份的profile启动然后source ora_envbak 环境变量就生效了。startup OK

ALTER SESSION SET EVENTS '10046 trace name context forever, level 12';[@more@]10046事件：用于跟踪SQL执行过程，这是我们在调整一个SQL时比较常见的方法。Level 12是最详细的级别。ALTER SESSION SET EVENTS '10046 trace name context forever, level 12'; 其中各个Level的含义： 1：显示SQL语句，执行计划和执行统计值 4：显示级别1的内容和绑定变量8：显示级别1的那同和等待事件统计 12：显示级别1的内容和绑定变量、等待事件统计alter system set events '1031 trace name errorstack level 3';10053事件：当Oracle使用CBO进行执

今天遇到一个 ORA-01031: 权限不足[@more@]scott@ORCL> CREATE OR REPLACE VIEW stats 2 as select 'STAT...' || a.name name, b.value 3 from v$statname a, v$mystat b 4 where a.statistic# = b.statistic# 5 union all 6 select 'LATCH.' || name, gets 7 from v$latch;from v$statname a, v$mystat b *第 3 行出现错误:ORA-01031: 权限不足;SQL> create role pub_role;Role createdSQL> grant select on v$statname to pub_role;grant select on v$statname to pub_roleORA-02030: 只能从固

SQL> set autotrace traceonly;SP2-0613: 无法验证 PLAN_TABLE 格式或实体SP2-0611: 启用EXPLAIN报告时出错[@more@]要运行oracle_home/rdbms/utlxplan.sql,oracle_home/sqlplus/plustrce.sql

netasst后一直死在那里[@more@]1、不要在.bash_profile环境变量文件中设置export CLASSPATH=.:/u01/app/oracle/product/8.1.7/jdbc/lib/classes111.zip 2、修改$ORACLE_HOME/bin/netasst,将最后的两面行改为：edit /u01/app/oracle/product/8.1.7/bin/netasst:change from: #$JRE -classpath $CLASSPATH oracle.net.asst.container.NetApplication oracle.net.asst.container.NetApplicationto: $JRE -nojit -classpath $CLASSPATH oracle.net.asst.container.NetApplication oracle.net.asst.container.NetApplicationexit $status

一些收集SQL的语句：）[@more@]1．查找前十条性能差的sqlSELECT * FROM (SELECT parsing_user_id executions, sorts, command_type, disk_reads, sql_text FROM v$sqlarea ORDER BY disk_reads DESC) WHERE rownum < 10;2．捕捉运行很久的SQLSELECT username, sid, opname, round(sofar * 100 / totalwork, 0) || '%' AS progress, time_remaining, sql_text FROM v$session_longops, v$sql WHERE time_remaining <>

817竟然不支持CREATE OR REPLACE SYNONYM8174就可以支持了。汗。。[@more@]

高级子查询[@more@]SQL> select ename,sal,deptno from emp e 2 where sal>(select avg(sal) from emp where deptno=e.deptno);ENAME SAL DEPTNO---------- ---------- ----------ALLEN 1600 30JONES 2975 20BLAKE 2850 30SCOTT 3000 20KING 5000 10FORD 3000 20已選取 6 個資料列.外查询中的行每被处理一次，内查询就求值一次.=======================================================

PART20--9i extensions to ddl&dml statements objectives1,多表插入的類型: 無條件insert 重點insert 條件all insert 條件first insert2,創建和使用外部表3,創建constrance時name index [@more@]1 無條件insert allexample:insert allinto copy_table_emp1 values(ename,sal)into copy_table_emp2 values(ename,sal)select ename,sal from emp where sal>1000;2,條件insert allexample:insert allwhen sal>3000 then into copy_table_emp1 values(ename,sal)when sal<3000 then into copy_table_emp2 values(ena