ITPub博客

alter system set statistics_level=typical (ALL, basic) scope=both(memory, spfile);--注意，当 sga_target不等于0，即SGA不自动管理时，不能用以上命令，否则会报错。这时要运行METALINK提供的脚本dbmsnoawr.plb创建package: dbms_awr,接着exec dbms_awr.disable_awr;select snap_id,dbid,instance_number from dba_hist_snapshot;select dbms_workload_repository.awr_report_text('2027229153',1,7,10,0) from dual;[@more@]

用oracle trace 来跟踪session =========================================================== 作者: tolywang(http://tolywang.itpub.net)发表于:2007.11.21 08:57分类: Oracle数据库管理 出处：http://tolywang.itpub.net/post/48/414520--------------------------------------------------------------- 1.在数据库安装的电脑上,使用SQL plus 用 SYSDBA 身份连接到数据库.2.找到要进行跟踪的会话.select sid,serial#,username from v$session ;3.启用相关会话的SQL trace .exec dbms_system.set_sql_trace_in_session(7,284,true)4.从

EVENT: 10046 "enable SQL statement tracing (including binds/waits)" [ID 21154.1] Modified 11-NOV-2007 Type REFERENCE Status PUBLISHED Event:10046 Text: enable SQL statement timing ------------------------------------------------------------------------------- For details of interpreting 10046 output see: Note:39817.1 Explanation: This is a special EVENT code. It can be used to signal Oracle to perform SQL_TRACE type actions. The 10046 tr

select t.sql_text from v$process s, v$session n,v$sqltext t where s.spid=18740 and n.PADDR = n.PADDR and n.SQL_ADDRESS=t.ADDRESS order by t.ADDRESS[@more@]

Oracle中行迁移和行链接的清除及检测一、行迁移和行链接的介绍 在实际的工作中我们经常会碰到一些Oracle数据库性能较低的问题，当然，引起Oracle数据库性能较低的原因是多方面的，我们能够通过一些正确的设计和诊断来尽量的避免一些Oracle数据库性能不好，Row Migration (行迁移) & Row Chaining (行链接)就是其中我们可以尽量避免的引起Oracle数据库性能低下的潜在问题。通过合理的诊断行迁移和行链接，我们可以较大幅度上提高Oracle数据库的性能。 那究竟什么是行迁移和行链接呢，先让我们从Oracle的block开始谈起。 操作系统的最小

alter database backup controlfile to trace;CREATE CONTROLFILE REUSE DATABASE "TEST" NORESETLOGS ARCHIVELOG MAXLOGFILES 16 MAXLOGMEMBERS 3 MAXDATAFILES 100 MAXINSTANCES 8 MAXLOGHISTORY 292 LOGFILE GROUP 1 'D:ORACLEPRODUCT10.2.0ORADATATESTREDO01.LOG' SIZE 50M, GROUP 2 'D:ORACLEPRODUCT10.2.0ORADATATESTREDO02.LOG' SIZE 50M, GROUP 3 'D:ORACLEPRODUCT10.2.0ORADATATESTREDO03.LOG' SIZE 50M -- STANDBY LOGFILE DATAFILE 'D:ORACLEPRODUCT10.2.0ORADATATESTSYSTEM01.DBF', 'D:ORACLEPRODUCT10.2.0ORADATAT

run {allocate channel d1 type disk;backup incremental level 0 as compressed backupset format '/u04/backup/ausabo2/%d_LEVEL0_%T_%U' database;backup as compressed backupset format '/u04/backup/ausabo2/%d_ARCH_%T_S%s_P%p_C%c' archivelog all not backed up;release channel d1;}run {allocate channel d1 type disk connect 'sys/zebra@node1';allocate channel d2 type disk connect 'sys/zebra@node2';delete noprompt archivelog until time 'sysdate - 1' backed up 1 times to device type disk;delete noprompt obsol

Creating Physical Standby using RMAN Duplicate Without Shutting down The Primary [ID 789370.1] -------------------------------------------------------------------------------- Modified 03-JUL-2010 Type HOWTO Status MODERATED In this Document Goal Solution References--------------------------------------------------------------------------------Platforms: 1-914CU; This document is being delivered to you via Oracle Support's Rapid Visibility (RaV) process and therefore has not bee

RMAN> run{2> set until time "to_date('2010/07/20 21:10:00','yyyy/mm/dd hh24:mi:ss')";3> restore database;4> recover database;5> }[@more@]

之前有控制文件的备份，数据文件全部丢失，online redo file和archived redo是连续的，恢复如下。 我们要用noresetlogs因为日志文件全都是完好的。 SQL> CREATE CONTROLFILE REUSE DATABASE "ICMNLSDB"NORESETLOGS ARCHIVELOG 2 -- SET STANDBY TO MAXIMIZE PERFORMANCE 3 MAXLOGFILES 5 4 MAXLOGMEMBERS 3 5 MAXDATAFILES 100 6 MAXINSTANCES 1 7 MAXLOGHISTORY 226 8 LOGFILE 9 GROUP 1 'D:ORACLEORADATAICMNLSDBREDO01.LOG' SIZE 100M, 10 GROUP 2 'D:ORACLEORADATAICMNLSDBREDO02.LOG' SIZE 100M, 11 GROUP 3 'D:ORACLEO

NOTE: The ALTER DATABASE DATAFILE OFFLINE DROP command, is not meant to allow you to remove a datafile. What the command really means is that you are offlining the datafile with the intention of dropping the tablespace. If you are running in archivelog mode, you can also use: ALTER DATABASE DATAFILE OFFLINE; instead of OFFLINE DROP. Once the datafile is offline, Oracle no longer attempts to access it, but it is still considered part of that tablespace. This datafile is marked only as offline in

这篇文章中，我们将通过Undo, Redo, DataFile的dump来看看Oracle中一个transaction过程。1：提交当前session中的pending transaction,SQL> show autocommit;autocommit OFFSQL> commit;Commit complete.2：确定transaction前的scn，SQL> connect sys/coffee@testdb as sysdba;Connected.SQL> select max(ktuxescnw * power(2, 32) + ktuxescnb) from x$ktuxe;MAX(KTUXESCNW*POWER(2,32)+KTUXESCNB)------------------------------------416835883：数据更新 SQL> connect system/coffee@testdb;Connected.SQL> update u

写的次序: redo--> undo-->datafile insert一条记录时, 表跟undo的信息都会放进 redo 中, 在commit 或之前, redo 的信息会放进硬盘上. 故障时, redo 便可恢复那些已经commit 了的数据. redo->每次操作都先记录到redo日志中，当出现实例故障（像断电），导致数据未能更新到数据文件，则数据库重启时须redo，重新把数据更新到数据文件 undo->记录更改前的一份copy，但你系统rollback时，把这份copy重新覆盖到原来的数据 redo->记录所有操作，用于恢复（redo records all the database transaction used for recovery） undo-

Complete Checklist for Manual Upgrades to 11gR2 [ID 837570.1] Modified 29-JUL-2010 Type BULLETIN Status PUBLISHED In this Document Purpose Scope and Application Complete Checklist for Manual Upgrades to 11gR2 ReferencesApplies to: Oracle Server - Standard Edition - Version: 9.2.0.8 to 11.2.0.1 - Release: 9.2 to 11.2Oracle Server - Enterprise Edition - Version: 9.2.0.8 to 11.2.0.1.0 [Release: 9.2 to 11.2]Information in this document applies to any platform.PurposeThis document is c

CURSOR1 declarecursor a(i number) is select * from test where id=i;b test%rowtype;beginopen a(i=>1) ;loop fetch a into b;exit when a%notfound;dbms_output.put_line(b.name);end loop;close a;end;2 declarebeginfor a in (select * from test) loopdbms_output.put_line(a.name);end loop;end;3 declarecursor b(i number) is select * from test where id=i;beginfor c in b(i=>1) loopdbms_output.put_line(c.name);end loop;end;4declarecursor a is select * from mao where rownum<100;b mao%rowtype;beginfor b

rman nocatalog target =/ --using controlfile CONTROL_FILE_RECORD_KEEP_TIME: default value 7rman target sys/test@pro catalog rman/rman@rman--install catalog database1.需要在catalog数据库test上建立表空间：create tablespace rman_catalog size 20M;需要用sysdba的权限连接到数据库上，表空间的名字可以自己设置。2.创建用户并制：create user rman identified by rman default tablesapce rman_catalog quota unlimited on rman_ts;建立用户，名称、密码可以自己设置，默认表空间为上面我们刚建立的表空间名称，不受限制。3.

--两库tablespace要一样c:>expdp help=y c:>impdp help=y SQL> create directory exp_dump as 'd:exp_dump';SQL> grant read, write on directory exp_dump to ...Importing Full Dump FileIf you want to Import all the objects in a dump file then you can type the following command. $impdp hr/hr DUMPFILE=dpump_dir1:expfull.dmp FULL=y LOGFILE=dpump_dir2:full_imp.logImporting Objects of One Schema to another SchemaThe following example loads all tables belonging to hr schema to scott schema$impdp SY