05 2013 档案

摘要:/*项目案例:主要功能:编写scheduler,自动生成投资收益数据*/一、准备数据-- Create tablecreate table INVESTMENT_INCOME_STATEMENT( SID VARCHAR2(32) not null, BOOK_DATE DATE, TRADING_DATE DATE, TRADE_LOCATION VARCHAR2(64), EXPENDITURE NUMBER(18,2), INCOME ... 阅读全文
posted @ 2013-05-31 22:13 ArcerZhang 阅读(497) 评论(1) 推荐(0) 编辑
摘要:查看Instance和Database信息,可参考如下动态性能视图查看信息,可参考如下动态性能视图查看Memory信息,可参考如下动态性能视图查看Contention信息,可参考如下动态性能视图 阅读全文
posted @ 2013-05-31 17:43 ArcerZhang 阅读(171) 评论(1) 推荐(0) 编辑
摘要:Dynamic Performance Statistics 阅读全文
posted @ 2013-05-31 17:39 ArcerZhang 阅读(101) 评论(0) 推荐(0) 编辑
摘要:Managing Memory CommpentsAutomatic Memory Management(AMM)-Enable you to specifiy total memory allocated to instance (including both SGA and PGA)指定MEMORY TARGET参数后,Oracle会自动分配内存.实现此特性的前提就是Oracle所在的操作系统支持自动释放内存.ASSM(自动共享内存管理,共享内存实际上就是SGA)如何启动自动内存管理?1、通过OEM,设置Enable属性2、手工方式修改MEMORY_TARGET参数SQL> show 阅读全文
posted @ 2013-05-31 17:10 ArcerZhang 阅读(147) 评论(0) 推荐(0) 编辑
摘要:本章前言:每秒钟,产生的日志文件多少,如果产生很多的redo log 信息,说明负荷量大差生的原因是DML操作太多.假如oracle database 属于dedicate server,使用top session方式排查数据库性能问题,是比较适合的.根据SESSION_ID,就能找到相关的程序,然后据此进行调整.shared server方式,不适合用在批量交易、运行报表时间很长的系统,而是适合联机交易系统.而单纯的联机交易本身不会消耗太多资源的.所以通过top session方式去排查影响性能的问题所在,是有些困难的.此时,通过top services来分析瓶颈所在.被动式的维护,首先寻找 阅读全文
posted @ 2013-05-31 11:58 ArcerZhang 阅读(222) 评论(0) 推荐(0) 编辑
摘要:10g、11g性能管理方面提供哪些新特性.昨天的执行效率很高,突然今天的性能就下降(有可能昨天的执行计划于今天的执行不同导致的).被动监控与主动监控.大火燃烧了,大火还没有蔓延.ObjectivesAfter completing this lesson,you should be able to :Use Enterprise Manager to monitor performanceUse Automatic Memory Management(AMM)Use the Memory Advisor to size memory buffersView performance-relate 阅读全文
posted @ 2013-05-31 11:22 ArcerZhang 阅读(202) 评论(0) 推荐(0) 编辑
摘要:服务器预警信息.给dump区设置阀值,提前预警.快照太旧,提前预警.instance挂起,提前预警.Automated Maintenance TasksAutotask maintenance processMaintenance Window opens.Autotask background process schedules jobs.Scheduler initiates jobs.Resource Manager limits impact of Autotask jobs.Default Autotask maintenance jobsGathering optimizer s 阅读全文
posted @ 2013-05-31 09:42 ArcerZhang 阅读(181) 评论(0) 推荐(0) 编辑
摘要:Advisory Framework所有的Advisory Framework都是基于AWR(Automatic workload Repository)的.Enterprise Manager and AdvisorsDBMS_AVISOR Package ProcedureDescriptionCREATE_TASKCreates a new task in the repositoryDELETE_TASKDelete a task from the repositoryEXECUTE_TASKInitiates execution of the taskINTERRUPT_TASKS. 阅读全文
posted @ 2013-05-30 17:22 ArcerZhang 阅读(145) 评论(0) 推荐(0) 编辑
摘要:检测出网络瓶颈、CPU瓶颈、锁竞争、I/O问题、内存参数设置、发现应用问题(SQL语句所占资源多,负载重)等,ADDM可以根据上述瓶颈问题,可以给出科学合理的建议.如果oracle配置不当(规划日志文件),ADDM也会给出科学合理的建议.oracle中有很多专家,有的擅长SQL,有的擅长空间管理.假如想请专家分析SQL语句,ADDM就可以主动分析,有的专家需要“请”才会主动服务.假如将ADDM比作医生专家的话,那么ADDM就是全科专家;oracle中有很多其他专家,大部分都是专科医生.Automatic Databae Diagnostic Monitor(ADDM)Runs after ea 阅读全文
posted @ 2013-05-30 16:17 ArcerZhang 阅读(215) 评论(0) 推荐(0) 编辑
摘要:SQL> select tablespace_name,contents from dba_tablespaces;TABLESPACE_NAME CONTENTS------------------------------ ---------SYSTEM PERMANENTSYSAUX PERMANENTUNDOTBS1 UNDOTEMP TEMPORARYUSERS ... 阅读全文
posted @ 2013-05-30 15:48 ArcerZhang 阅读(127) 评论(0) 推荐(0) 编辑
摘要:Oracle Optimizer:OverviewThe Oracle optimizer determines the most efficient execution plan and is the most important step in the processing of any SQL statement.The OptimizerEvaluates expressions and conditionsUses object and system statisticsDecides how to access the dataDecides how to join tablesD 阅读全文
posted @ 2013-05-30 13:39 ArcerZhang 阅读(139) 评论(0) 推荐(0) 编辑
摘要:主动式维护被动是维护(critical)AWR本身就是一个数据库,专门用来存放数据库本身运行信息的,通直接的用户数据、业务数据无关.AWR是oracle自我管理的一个基础.OjbectivesAfter completing this lesson,you should be able to :Manage optimizer statisticsManage the Automatic Workload Repository(AWRUse the Automatic Database Diagnostic Monitor(ADDM)Describe and use the advisory 阅读全文
posted @ 2013-05-30 11:31 ArcerZhang 阅读(132) 评论(0) 推荐(0) 编辑
摘要:Remote database job是11g的新特性Creating a Job Array--1、Declare variables of types sys.job and sys.job_array:DECLARE newjob sys.job; newjobarr sys.job_array;--2、Initialize the job array:BEGIN newjobarr := SYS.JOB_ARRAY();--3、Size the job array to hold the number of jobs needed:newjob... 阅读全文
posted @ 2013-05-30 10:26 ArcerZhang 阅读(211) 评论(0) 推荐(0) 编辑
摘要:plachholder 阅读全文
posted @ 2013-05-30 09:37 ArcerZhang 阅读(109) 评论(0) 推荐(0) 编辑
摘要:问题描述:在sys用户下直接授权grant create job to U3/grant create any job to U3,执行成功后,切换到U3用户后,执行dbms_scheduler.create_chain('my_chain1');会报权限不足的错误.不知道为什么?begin dbms_scheduler.create_chain('my_chain1');end;/ 4 begin*ERROR at line 1:ORA-01031: insufficient privilegesORA-06512: at "SYS.DBMS_ISC 阅读全文
posted @ 2013-05-30 07:33 ArcerZhang 阅读(554) 评论(1) 推荐(0) 编辑
摘要:/*********************************************************************/实验步骤create chain---my_chain1create chain step1---my_prog1--my_porc1create chain step2---prog1--proc1--logcreate chain rule1---chain step1---my_prog1create chain rule2---chain step2---step1 successcheck/*************************** 阅读全文
posted @ 2013-05-29 23:08 ArcerZhang 阅读(156) 评论(0) 推荐(0) 编辑
摘要:Creating Job ChainsCreate a chain object.Define chain steps.Define chain rules.Starting the chain:Enable the chainCreate a job that points to the chain.chain执行步骤,如果chain A 执行成功了,就执行chain B,执行完B之后,还可以根据条件进行switch,执行chain C或者chain D.chain执行的各个步骤,可以嵌套.一个chain中的子步骤也可以是一个chain.Example of a Chain创建chain步骤 阅读全文
posted @ 2013-05-29 17:33 ArcerZhang 阅读(236) 评论(0) 推荐(0) 编辑
摘要:Creating Complex Schedules/* ================================================================================== Creating Complex Schedules ==================================================================================*/BEGIN DBMS_SCHEDULER.CREATE_SCHEDULE( ... 阅读全文
posted @ 2013-05-29 11:50 ArcerZhang 阅读(147) 评论(0) 推荐(0) 编辑
摘要:1、在创建scheduler时,设置start_date =>systimestamp要比start_date => sysdate要好一些,systimestamp可以自动调整时区.2、job创建参数中,job_type的参数值类型 STORED_PROCEDURE------->job执行存储过程 PLSQL_BLOCK------------>pl/sql匿名块 EXECUTABLE------------->可执行操作系统命令 The type of this job.Valid values are: 'PLSQL_BLOCK',  阅读全文
posted @ 2013-05-29 11:49 ArcerZhang 阅读(274) 评论(0) 推荐(0) 编辑
摘要:Persistent Lightweight JobsPersistent lightweight jobs:Reduce the overhead and time required to start a jobHave a small footprint on disk for the job metadata and for storing run-time data.Are created from a job template(in the commad line)创建lightweight job的语法BEGIN DBMS_SCHEDULER.CREATE_JOB( ... 阅读全文
posted @ 2013-05-29 10:13 ArcerZhang 阅读(218) 评论(1) 推荐(0) 编辑
摘要:/****************************************************/创建一张空表,编写一个Job.每隔1分钟向表中插入一条数据实验步骤1、create tablecreate table log(user_name varchar2(10),user_date date);2、create procedure --- pl/sql3、grant (grant create job or create any job)4、create program5、create scheduler6、create job7、monitor job8、enable jo 阅读全文
posted @ 2013-05-28 22:49 ArcerZhang 阅读(168) 评论(0) 推荐(0) 编辑
摘要:Your Basic Work FlowTo simplify management tasks with the Scheduler:Create a program (enabled or disabled)---optionalTo reuse this action within multiple jobsTo change the schedule for a job without having to re-create the PL/SQL blockCreate and use a schedule.Create and submit a job.Calendaring Exp 阅读全文
posted @ 2013-05-28 18:03 ArcerZhang 阅读(167) 评论(0) 推荐(0) 编辑
摘要:Simplifying Management TasksPerforming a series of month-end tasks on the last day of each monthRunning a dequeue procedure as soon as a message is enqueuedReplicating table data via materialized view refreshesRunning a daily job to back up databaseComputing table and index statistics twice a daySta 阅读全文
posted @ 2013-05-28 15:09 ArcerZhang 阅读(147) 评论(0) 推荐(0) 编辑
摘要:ObjectivesAfter completing this lesson,you should be able to :Simplify management tasks by using the schedulerCreate a job,program,and schedulerUse a time-based or event-based scheduler for executing scheduler jobsdescribe the use of windows,window groups,job classes,and consumer groupsUse email not 阅读全文
posted @ 2013-05-28 14:32 ArcerZhang 阅读(183) 评论(0) 推荐(0) 编辑
摘要:查看Oracle Database系统内存参数情况(as sysdba)其他参数情况查看(as sysdba)SQL> show parameter shared_pool_sizeNAME TYPE VALUE------------------------------------ ----------- ------------------------------shared_pool_size big integer 0SQL> show parameter db_cac... 阅读全文
posted @ 2013-05-28 11:54 ArcerZhang 阅读(188) 评论(0) 推荐(0) 编辑
摘要:Oracle Database Memory ParametersProgram Global Area(PGA)Automatic PGA memory management is enabled by default.Using the V$PARAMETER ViewSQL> show parameter sga_target;NAME TYPE VALUE------------------------------------ ----------- -----------------------------... 阅读全文
posted @ 2013-05-28 11:03 ArcerZhang 阅读(334) 评论(0) 推荐(0) 编辑
摘要:参考文章:http://www.ningoo.net/html/2007/oracle11g_new_feature_flashback_data_archive2.html一.后台进程Oracle11g为Flashback data archive特性专门引入了一个新的后台进程FBDA,用于将追踪表(traced table,也就是将指定使用flashback data archive的table)的历史变化数据转存到闪回归档区。NING@11g>select name,description from v$bgprocess where name=’FBDA’;NAME DESCRI 阅读全文
posted @ 2013-05-28 09:10 ArcerZhang 阅读(250) 评论(0) 推荐(0) 编辑
摘要:SQL> drop table t;drop table t *ERROR at line 1:ORA-55610: Invalid DDL statement on history-tracked table情况分析置于Flashback data archive中的table的一些限制追踪表(Tracked table),也就是指定将历史数据保存到某个flashback data archive中的table,不能执行DDL操作(add column除外)。NING@11g>drop table test;drop table test*ERROR at line 1:... 阅读全文
posted @ 2013-05-28 09:04 ArcerZhang 阅读(1129) 评论(0) 推荐(0) 编辑
摘要:C:\Users\MaryHu>exp ARCER/ARCER@DB234 FILE=E:\FGPS_20130528.dmp statistics=noneExport: Release 11.2.0.1.0 - Production on Tue May 28 08:04:54 2013Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 6 阅读全文
posted @ 2013-05-28 08:10 ArcerZhang 阅读(2369) 评论(0) 推荐(0) 编辑
摘要:placeholder 阅读全文
posted @ 2013-05-27 19:18 ArcerZhang 阅读(119) 评论(0) 推荐(0) 编辑
摘要:Java Pool and Streams PoolJava pool memory is used in server memory for all session-specific Java code and data in the JVM.Streams pool memory is used exclusively by Oracle Streams to:Store buffered queue messagesProvide memory for Oracle Streams processesRedo Log BufferIs a circular buffer in the S 阅读全文
posted @ 2013-05-27 18:51 ArcerZhang 阅读(126) 评论(0) 推荐(0) 编辑
摘要:shared server模式下,UGA放在SGA的哪个位置?如果配置了LARGE POOL,UGA就会放在LARGE POOL中;否则如果没有配置,那么就存放在SHARED POOL当中.oracle 11g以后,可以将sql,pl/sql的执行结果存放在result pool中.Shared PoolContents:Library cache:Command text,parsed code,and execution planData dictionary cache:Definitions for tables,columns,and privileges from the data 阅读全文
posted @ 2013-05-27 17:55 ArcerZhang 阅读(193) 评论(0) 推荐(0) 编辑
摘要:在一个Data Buffer Cache中,有可能存在一个数据块的多个副本.Dirty是指,被修改过的、还没有被flush到磁盘中去的数据状态.Buffer Cache数据在Buffer Cache中的四种状态值PinnedCleanFree/unusedDirtyUsing Multiple Buffer Poolsstep: =>创建Buffer Pool =>使用Buffer Pool 1、在创建对象时指定Buffer Pool CREATE INDEX cust_idx ... STORAGE(BUFFER_POOL KEEP); 2、修改表所在的BUF... 阅读全文
posted @ 2013-05-27 16:24 ArcerZhang 阅读(157) 评论(0) 推荐(0) 编辑
摘要:ObjectivesAfter completing this lesson,you should be able to :Describe the memory components in the SGAImplement Automatic Memory ManagementManually configure SGA parametersConfigure automatic PGA memory managementMemory Management:OverviewDBAs must consider memory managment to be a crucial part of 阅读全文
posted @ 2013-05-27 15:42 ArcerZhang 阅读(134) 评论(0) 推荐(0) 编辑
摘要:Restoring Tables from the Recycle BinRestore dropped tables and dependent objects.If multiple recyclye bin entries have the same original name:Use qunique,system-generated names to restore a particular version.When using original names,the restored table is last in,first out(LIFO)Rename the original 阅读全文
posted @ 2013-05-27 11:09 ArcerZhang 阅读(280) 评论(0) 推荐(0) 编辑
摘要:1、查看当前schema下表中索引名称对应信息SQL> select index_name,table_name from user_indexes;INDEX_NAME TABLE_NAME------------------------------ ------------------------------MYC_IDX MYCLUSTERSYS_C0012950 C_EMPSYS_C0012949 C_DEPTView Code ... 阅读全文
posted @ 2013-05-27 10:52 ArcerZhang 阅读(182) 评论(0) 推荐(0) 编辑
摘要:/********************************/闪回已经删除过的表注意:假如一张表被删除之后,那么它对应的索引以及约束也会一同被删除.被删除的对象(表、索引、约束、触发器)放到回收站之后,oracle都会重新分配一个新的名称.假如通过flashback table技术闪回表的时候,表的名称可以回到之前,但是索引名称、约束的名称将不会回到之前.仍将沿用在回收站分配的名称.如果闪回成功之后,就需要手工将索引、约束、触发器的名称rename到之前的名称./********************************/SQL> drop table t;Table dro 阅读全文
posted @ 2013-05-27 10:09 ArcerZhang 阅读(188) 评论(0) 推荐(0) 编辑
摘要:前言:一个礼拜没有搭理我的数据库服务器,回来查看发现如下问题,经过多方查找,发现解决方案,特此收集整理一下,以备后用.文章参考来源:http://space.itpub.net/15843490/viewspace-580677ORA-38760: This database instance failed to turn on flashback database 今天重启数据库的时候,发现如下错误:ORA-38760: This database instance failed to turn on flashback database 数据库环境:linux64x86 +oracle10 阅读全文
posted @ 2013-05-27 09:26 ArcerZhang 阅读(424) 评论(0) 推荐(0) 编辑
摘要:查看包的结构内容[oracle@arcerzhang ~]$ sqlplus /nologSQL*Plus: Release 11.2.0.1.0 Production on Sun May 19 20:15:32 2013Copyright (c) 1982, 2009, Oracle. All rights reserved.SQL> conn /as sysdbaConnected.SQL> desc dbms_flashback_archive;PROCEDURE DISASSOCIATE_FBA Argument Name Type ... 阅读全文
posted @ 2013-05-19 21:13 ArcerZhang 阅读(251) 评论(0) 推荐(0) 编辑
摘要:闪回数据版本查询->闪回数据归档查询,前者不可以跨越DDL操作,后者可以跨越.SQL> l 1 select versions_xid,to_char(versions_starttime,'yyyy-mm-dd hh24:mi:ss') versions_starttime,versions_endtime,empno,sal 2 from emp 3 versions between timestamp to_timestamp('2013-05-18 14:13:07','yyyy-mm-dd hh24:mi:ss') and 阅读全文
posted @ 2013-05-19 09:10 ArcerZhang 阅读(219) 评论(0) 推荐(0) 编辑
摘要:Oracle Total Recall ScenarioUsing Flashback Data Archive to access historical data:--create the flashback data archivecreate flashback archive default fla1 tablespace tbs1 quota 10g retention 5 year;--specifiy the default flashback data archive alter flashback archive fla1 set default;--enable flash 阅读全文
posted @ 2013-05-18 15:02 ArcerZhang 阅读(162) 评论(0) 推荐(0) 编辑
摘要:本章节为实验课,具体实验内容参看上节视频[bbk5102] 第38集 - 第四章 Flashback Database 02 阅读全文
posted @ 2013-05-18 14:33 ArcerZhang 阅读(106) 评论(0) 推荐(0) 编辑
摘要:1、trace文件SQL> show parameter user_dump_destNAME TYPE VALUE------------------------------------ ----------- ----------------------------------------------------------------------------------------user_dump_dest string /RealData/diag/rd... 阅读全文
posted @ 2013-05-18 13:11 ArcerZhang 阅读(462) 评论(0) 推荐(0) 编辑
摘要:/*********************************************************************************/实验目的:1、create tablespace--->fdba_tbs2、create user ---administer--fbda_amdin3、grant4、grant ---ARCER5、create flashback archive6、transaction--flashback version query7、drop undo8、enable--emp--flashback archive/******** 阅读全文
posted @ 2013-05-18 13:08 ArcerZhang 阅读(199) 评论(0) 推荐(0) 编辑
摘要:数据归档区管理员(FLASHBACK ARCHIVE ADMINISTER)在日常管理中,可以安排一个专门人员负责数据归档区的管理.DBA分配FLASHBACK ARCHIVE ADMINISTER权限给此用户.空间 TABLESPACE权限 |-SELECT ANY TRANSACTION |-FALASHBACK ARCHI |-DBMS_FLASHBACK(PACKAGE EXECUTE PRIVES)How Total Recall WorksHistory data:Row captured asynchronously by background processes at ... 阅读全文
posted @ 2013-05-18 12:23 ArcerZhang 阅读(202) 评论(0) 推荐(0) 编辑
摘要:ObjectivesAfter completing this lesson,you should be be able to :Describe and use Oracle Total RecallCreating and enabling a Flashback Data Archive(FDA)Manageing FDAsViewing metadataDescribe and use flashback recycle binsRestore dropped tables from the recycle binManage space usage in the recycle bi 阅读全文
posted @ 2013-05-18 11:09 ArcerZhang 阅读(153) 评论(0) 推荐(0) 编辑
摘要:/************************************************************************************/实验目的:在存在事务级联的条件下,使用DBMS_FLASHBACK.TRANSACTION_BACKOUT回退事务实验步骤:/************************************************************************************/ 阅读全文
posted @ 2013-05-17 20:37 ArcerZhang 阅读(135) 评论(0) 推荐(0) 编辑
摘要:/************************************************************************************/实验目的:使用DBMS_FLASHBACK.TRANSACTION_BACKOUT回退事务实验步骤: 1、emp-insert into-empno=1 2、emp-insert into-empno=2 3、flashback transaction query--定位 4、flashback transaction--recover/****************************************... 阅读全文
posted @ 2013-05-17 20:36 ArcerZhang 阅读(233) 评论(0) 推荐(0) 编辑
摘要:SQL> select table_name,operation,undo_sql from flashback_transaction_query where xid='06001D00D9080000';TABLE_NAME OPERATION UNDO_SQL---------- ------------ ----------------------------------------EMP1 UNKNOWNDEPT1 UNKNOWNEMP1 UNKNOWN BEGINOracle 11g排查上述操作不正常原因0、前提条件,是当... 阅读全文
posted @ 2013-05-17 17:45 ArcerZhang 阅读(191) 评论(0) 推荐(0) 编辑
摘要:QuizSelect all correct statements:The database can remain open when a table is flashed back.Flashback Table is executed as a single transaction.Flashback Table is requies backups to be available.(flashback table 操作,是不依赖于backup的)Flashback Table is based on undo data.Flashback Transaction Query/****** 阅读全文
posted @ 2013-05-17 14:41 ArcerZhang 阅读(194) 评论(0) 推荐(0) 编辑
摘要:/*************************************************************************/实验目的:Flashback table : Cannot span DDL operations实验步骤:见下图实验结论:Cannot span DDL operations/*************************************************************************/1、建表->查询数据->记录时间戳SQL> create table emp1 as select * f 阅读全文
posted @ 2013-05-17 12:19 ArcerZhang 阅读(209) 评论(0) 推荐(0) 编辑
摘要:/*********************************************************************/实验目的:验证在sys用户下创建的表,无法实现flashback table功能实验步骤: 1、sys->create table emp -> 7369 900 2、error->update --7369 900-1900 3、定位错误--flashback version query 4、recover->flashback table/******************************************** 阅读全文
posted @ 2013-05-17 12:10 ArcerZhang 阅读(247) 评论(0) 推荐(0) 编辑
摘要:实现Flashback table操作的前提条件权限及及开启movement row功能/*************************************************************************/实验:flashback table操作1、emp1-7369-9002、emp1-7369-900-1000 error where deptno=20---7369-19003、flashback version query4、flashback table---recover总结: 1、比incomplete recovery 影响返回小,保证数据库o. 阅读全文
posted @ 2013-05-17 08:27 ArcerZhang 阅读(264) 评论(0) 推荐(0) 编辑
摘要:Quiz1、Flashback Query compares current data with data from the past.To do so,it uses both undo and redo data.TrueFalse2、Select the correct statementFlashback Version Query uses undo data and modifies data.Flashback Version Query uses undo data and does not modify data.Flashback Version Query uses bo 阅读全文
posted @ 2013-05-17 07:33 ArcerZhang 阅读(199) 评论(0) 推荐(0) 编辑
摘要:Flashback Version Query:ConsiderationsThe VERSIONS clause cannot be used to query:External tablesTemporary tablesFixed tablesViewsThe VERSIONS clause cannot span DDL commands.Segment shrink operations are filtered out.在哪种情况下可以使用flashback versions query只能是commited以后的数据只能是dml语句,ddl不行;ddl以后,前面的dml也查询不到 阅读全文
posted @ 2013-05-16 17:31 ArcerZhang 阅读(99) 评论(0) 推荐(0) 编辑
摘要:Flashback Version Query/******************************************/实验:跟踪数据不同版本之间的事物变化目的:利用flashback query data,使用dml进行数据恢复/******************************************/SQL> show userUSER is "U2"SQL> create table m(id int,name varchar2(10));Table created.SQL> insert into m values(0,& 阅读全文
posted @ 2013-05-16 16:21 ArcerZhang 阅读(135) 评论(0) 推荐(0) 编辑
摘要:Guaranteeing Unod RetentionSQL> select ts#,name,included_in_database_backup,bigfile,flashback_on,encrypt_in_backup from v$tablespace; TS# NAME INCLUDED_IN_DATABASE_BACKUP BIGFILE FLASHBACK_ON ENCRYPT_IN_BACKUP---------- --------------- ------------------------------ -------... 阅读全文
posted @ 2013-05-16 14:44 ArcerZhang 阅读(217) 评论(0) 推荐(0) 编辑
摘要:ObjectivesAfter completing this lesson,you should be able to :Describe Flashback technologyPerform Flashback QueryUse Flashback Version QueryEnable row movement on a tablePerform Flashback Table operationsUse Flashback Transaction QueryUse Flashback TransactionPreparing Your Database for FlashbackCr 阅读全文
posted @ 2013-05-16 12:11 ArcerZhang 阅读(176) 评论(0) 推荐(0) 编辑
摘要:使用恢复点,闪回数据:恢复点或者叫做还原点,实际上就是SCN号的别名.V$FLASHBACK_DATABASE_STAT;config restore point1、create restore point2、flashback database/***************************************************************************************************************************************/创建restore point,恢复数据库;/***************** 阅读全文
posted @ 2013-05-15 23:11 ArcerZhang 阅读(305) 评论(0) 推荐(0) 编辑
摘要:Monitoring Flashback DatabaseTo monitor the ability to meet your retention target:View the Fast Recovery Area disk quota:SQL> select estimated_flashback_size,flashback_size FROM V$FLASHBACK_DATABASE_LOG;ESTIMATED_FLASHBACK_SIZE FLASHBACK_SIZE------------------------ -------------- 2369... 阅读全文
posted @ 2013-05-15 22:33 ArcerZhang 阅读(176) 评论(0) 推荐(0) 编辑
摘要:Flashback Database:ExamplesTo flashback:Mounted(in exlusive mode)databseRMAN>FLASHBACK DATABASE TO TIME = "TO_DATE('2009-05-27 16:00:00','YYYY-MM-DD HH24:MI:SS')";RMAN>FLASHBACK DATABASE TO SCN=23536;RMAN>FLASHBACK DATABASE TO SEQUENCE=223 TRHRED=1;Monitor progress 阅读全文
posted @ 2013-05-15 18:00 ArcerZhang 阅读(256) 评论(0) 推荐(0) 编辑
摘要:Configuring Flashback Database如何查看数据库是否处于归档模式?mount database之后,执行如下命令SQL> select log_mode from v$database;LOG_MODE------------ARCHIVELOGconn as sysdba后,执行如下命令:SQL> archive log list;Database log mode Archive ModeAutomatic archival EnabledArchive destination USE_DB_... 阅读全文
posted @ 2013-05-15 15:50 ArcerZhang 阅读(2782) 评论(1) 推荐(0) 编辑
摘要:Flashback DatabaseThe Flashdback Database operation:Works like a rewind button for the database.Can be used in cases of logical data corruptions madeb by usersFlashsback Database:Reducing Restore TimeFlashback Database Architecture 阅读全文
posted @ 2013-05-15 12:49 ArcerZhang 阅读(111) 评论(1) 推荐(0) 编辑
摘要:ObjectivesAfter completing this lesson,you should be able to:Describe FlashbacktechnologyConfigure Flashback DatabasePerform Flashback Database operationsMonitor Flashback DatabaseFlashback Technolog:BenefitsTe Flashback technology is a revolutionary advantage in recovery.Traditional recovery techni 阅读全文
posted @ 2013-05-15 12:12 ArcerZhang 阅读(133) 评论(3) 推荐(0) 编辑
摘要:刚安装了VMware Server2.0,并在其中装了一个ubuntu系统。当时通过:开始菜单->所有程序->VMware->VMware Web Access->Web Access进入登录界面。输入本机的用户名和密码即可进行VMware Server Console界面。但在今天开机后却发现上述方法行不通了:当输入用户名和密码时,返回如下错误:在查找解决办法时,有网友提出如下办法:1. 依次打开 C:\Documents and Settings\All Users\Application Data\VMware\VMware Server\hostd;(Appli 阅读全文
posted @ 2013-05-14 19:27 ArcerZhang 阅读(380) 评论(0) 推荐(0) 编辑
摘要:1、建表方式Acreate table lx_bbk--tablespace test is a created by asm pattern.tablespace testasselect * from scott.emp;Create table(asm) 阅读全文
posted @ 2013-05-14 11:41 ArcerZhang 阅读(131) 评论(0) 推荐(0) 编辑
摘要:Oracle 10g 11g New Features OverviesASM InstanceFlashback IFlashback IIFlashback DatabaseManaging MemoryAuto SchedularMaintaincePerformanceManage DB PerformanceSQL PerformanceDiag DatabaseMoving DataManage SpaceManage Databse SpaceDUP DatabaseSupport 阅读全文
posted @ 2013-05-13 11:05 ArcerZhang 阅读(138) 评论(2) 推荐(0) 编辑
摘要:Table Lock ModesThese table lock modes are automatically assigned by the Oracle server:Row Exclusive(RX):INSERT,UPDATE,DELETERow Share(RS):SELECT ... FOR UPDATEManually Locking a TableManually acquired in LOCK TABLE StatementSQL>LOCK TABLE HR.employees IN share MODE;Share(S)No DML operations allo 阅读全文
posted @ 2013-05-10 14:08 ArcerZhang 阅读(290) 评论(0) 推荐(0) 编辑
摘要:DML LocksA DML transaction gets at least two blocks:A shared table lockAn exclusive row lockA shared table lock主要是阻止DDL语句对当前表进行修改.An exclusive row lock主要是阻止别的transaction修改同一条记录.如何查看锁的信息?有三个重要的动态性能视图:v$transaction:记录了当前活动的transaction.desc v$transactionSQL> desc v$transaction; Name ... 阅读全文
posted @ 2013-05-10 13:58 ArcerZhang 阅读(216) 评论(0) 推荐(0) 编辑
摘要:ObjectivesAfter completing this lesson,you should be able to do the following:Define levels of lockingIdentify causes of contentionPrevent locking problemsUse Oracle utilities to detect lock contentionResolve contention in an emergencyResovle deadlock conditionsModes of LockingOracle Database uses t 阅读全文
posted @ 2013-05-10 11:21 ArcerZhang 阅读(211) 评论(0) 推荐(0) 编辑
摘要:Partition table 三种基本类型:ranage、hash、list partition table,另外composite partition就是对以上三种类型的组合类型.Composite PartitioningIdeal for both historical data and data placementProvides high availability and manageability,like range partitioningImproves performance for parallel DML and supports partition-wise joi 阅读全文
posted @ 2013-05-09 19:18 ArcerZhang 阅读(458) 评论(0) 推荐(0) 编辑
摘要:Hash Partitioning OverviewEasy to ImplementEnables better performance for PDML and partion-wise joinsPDML->P DML并发的修改数据partition-wise join只能的Join,只需要有用的Partition 进行Join,没用的partition就不参加JoinInserts rows into partitions automatically based on the hash of the partion keySupports(hash) local indexesp 阅读全文
posted @ 2013-05-09 16:56 ArcerZhang 阅读(205) 评论(0) 推荐(0) 编辑
摘要:Partitioning MethodsThe following partitioning methods are available:RangeHashListComposite按年度分区,2009年、2010年、...每年一张表;自oracle 9i以后,每个表空间可以指定不同的block_size,但是在分区表中,每个不同的分区必须使用相同的db_block_size;CREATE TABLE sales( acct_no NUMBER(5), person VARCHAR2(30), sales_amount... 阅读全文
posted @ 2013-05-09 16:46 ArcerZhang 阅读(270) 评论(0) 推荐(0) 编辑
摘要:Hash Clustered TablesHash Clustered Table的设计目的就是减少I/O的次数,如果设计不当(一般因为size大小设置问题) 导致overflow blocks ,无疑会增大I/O的次数,造成事倍功半.create hash clustered table and show it space info[oracle@arcerzhang usefull_scripts]$ lltotal 4-rw-r--r-- 1 oracle oinstall 3642 May 9 12:45 show_space.sql[oracle@arcerzhang useful. 阅读全文
posted @ 2013-05-09 13:34 ArcerZhang 阅读(239) 评论(0) 推荐(0) 编辑
摘要:Data Access MethodsTo enhance performance ,you can use the following data access methods:ClustersIndexesB-tree(nomal or reverse key)BitmapFunction basedIndex-organized tablesMaterialized viewsClustersCluster TypesHash Clustered TablesExample创建Index ClusterCREATE CLUSTER mycluster(deptno number(2)) s 阅读全文
posted @ 2013-05-09 11:47 ArcerZhang 阅读(182) 评论(0) 推荐(0) 编辑
摘要:本章主要就是讲解,Oracle提供了哪些手段让我们的访问速度更快!ObjectivesAfter completing this lesson,you should be able to do the following:Compare and evalate the different storage structuessExamine different data access methodsImplement different partitionion methodsData Storage Structures最最普通的表集群表(将几个相互关联的表,一起放在一个块里面,读写的时候只进 阅读全文
posted @ 2013-05-08 19:12 ArcerZhang 阅读(198) 评论(0) 推荐(0) 编辑
摘要:Index ReorganizationIndexes on volatile tables are a performance problem.Only entirely empty index blocks go to the free list.If a block contains only one entry,it must be maintained.You may need to rebuild indexes.Monitoring Indexes SpaceTo collect usage statistics regarding an index:(搜集索引的相关数据)SQL 阅读全文
posted @ 2013-05-08 18:37 ArcerZhang 阅读(296) 评论(0) 推荐(0) 编辑
摘要:Database Block SizeMinimize block visits by:Using a large block sizePacking rows tightlyPreventing row migrationPacking rows tightly与Preventing row migraion是一对矛盾体.如果packing rows tightly过量,就会导致row migration.DB_BLOCK_SIZE ParameterThe database block size:Is defined by the DB_BLOCK_SIZE parameter.Is se 阅读全文
posted @ 2013-05-08 16:56 ArcerZhang 阅读(187) 评论(0) 推荐(0) 编辑
摘要:Locally Managed ExtentsCreate a locally managed tablespace:(以后所有的表空间管理方式都需要使用Locally managed method)SQL>CREATE TABLESPACE user_data_1DATAFILE '/u01/oradata/sid_name/1m_1.dbf'SIZE 100MEXTENT MANAGEMENT LOCALUNIFORM SIZE 2M;With the Oracle database the default extent management is local.Pro 阅读全文
posted @ 2013-05-08 14:44 ArcerZhang 阅读(365) 评论(0) 推荐(0) 编辑
摘要:ObjectivesAfter completing this lesson,you should be able to do the following:Use automatic segment space managementUse manual segment space managementDescribe the use of Oracle block parametersRecover space from sparsely populated segmentsDescribe and detect chaining and migration of Oracle blocksP 阅读全文
posted @ 2013-05-08 11:33 ArcerZhang 阅读(192) 评论(0) 推荐(0) 编辑
摘要:Using System StatisticsSystem statistics enable the CBO to use CPU and I/O characteristics.System statistics must be gathered on a regular basis;this does not invalidate cached plans.Gathering system statistics equals analyzing system activity for a specified period of time.所有的System Statistics 信息,都 阅读全文
posted @ 2013-05-07 18:48 ArcerZhang 阅读(156) 评论(0) 推荐(0) 编辑
摘要:Generating HistogramsHistogram statistics are generated by:SQL>EXECUTE dbms_stats.gather_table_stats('HR','EMPLOYEES',METHOD_OPT => 'FOR COLUMNS SIZE 10 salary') ;Histograms are specified using the METHOD_OPT argument of the DBMS_STATS gathering procedure.Oracle recomme 阅读全文
posted @ 2013-05-07 17:52 ArcerZhang 阅读(231) 评论(0) 推荐(0) 编辑
摘要:Skewed Data分析上述SQL语句,如果查询的数据很多,多到接近于整张表的数据了,那这个时候查询最优执行计划,就是全表扫描(full scan);如果查询的数据不是很多,此时就最好按照索引查询.数据的分布不同,最优的执行计划也不相同. 阅读全文
posted @ 2013-05-07 16:54 ArcerZhang 阅读(129) 评论(0) 推荐(0) 编辑
摘要:总结Table statistics ---> DBA_TABLES;获取Table statistics,通过DBA_TABLES 数据字典SQL> cldesc DBA_TABLES; Name Null? Type ----------------------------------------- -------- ---------------------------- OWNER NOT NULL VARCHAR2(30) TABLE... 阅读全文
posted @ 2013-05-07 15:32 ArcerZhang 阅读(278) 评论(0) 推荐(0) 编辑
摘要:When to Gather Statistics在10g、11g里面,搜集statistics信息分自动和手工方式两种.Table StatisticsNumber of rowsNumber of blocks and empty blocksAverage available free spaceNumber of chained or migrated rowsAverage row lengthLast analyze date and smaple sizeData dictionary view:dba_tables查看表的统计信息,主要依据数据字典dba_tablesIndex 阅读全文
posted @ 2013-05-07 13:27 ArcerZhang 阅读(123) 评论(0) 推荐(0) 编辑
摘要:OjbectivesAfter completing this lesson,you should be able to do the followingUnderstanding Statistics管理statistic的package:dbms_stats.Managing StatisticsUse the dbms_stats packages:gather_table_statsgather_index_statsgather_schema_statsgahter_database_statsgather_stable_stats创建表、插入数据SQL> create tab 阅读全文
posted @ 2013-05-07 13:15 ArcerZhang 阅读(202) 评论(1) 推荐(0) 编辑
摘要:placheholder 阅读全文
posted @ 2013-05-07 11:07 ArcerZhang 阅读(89) 评论(0) 推荐(0) 编辑
摘要:placeholder 阅读全文
posted @ 2013-05-07 11:04 ArcerZhang 阅读(109) 评论(0) 推荐(0) 编辑
摘要:placeholder 阅读全文
posted @ 2013-05-07 11:03 ArcerZhang 阅读(129) 评论(0) 推荐(0) 编辑
摘要:placeholder 阅读全文
posted @ 2013-05-07 11:00 ArcerZhang 阅读(88) 评论(0) 推荐(0) 编辑
摘要:ObjectivesAfter completing this lesson,you should be able to do the following:Set up Database Resource ManagerAssign users to Resource Manager groupsCreate resource plans within groups更加强大的手段来控制每个用户或者每组用户资源使用情况. 阅读全文
posted @ 2013-05-07 10:59 ArcerZhang 阅读(102) 评论(0) 推荐(0) 编辑
摘要:Temporary tablespace里面只有temporary segment,没有 permanent segement.对temporary tablespace的操作,不会更新data dictionary,执行速度会非常快.因为Permanent 类型的segment进行更新的话,都需要更新数据字典.不同的用户可以使用不同的临时表空间.不同的表空间可以分步在不同的磁盘上;假如不同的用户具有不同的临时表空间,在进行大数据排序的时候,就可以减少同一个磁盘的I/O读写,提高性能.一个segment可以被不同的用户使用,使用里面不同的extent.为不同的用户设置不同的临时表空间.Temp 阅读全文
posted @ 2013-05-06 23:37 ArcerZhang 阅读(165) 评论(0) 推荐(0) 编辑
摘要:OEMPGA Target Advice HistogramsV$PGA_TARGET_ADVICE_HISTOGRAM predicts how histograms shown in V$SQL_WORKAREA_HISTOGRAM evolve.STATISTICS_LEVEL must be set to at least TYPICAL.Auto PGA and OEMOverviewThe automatic sort area management feature is :Easier to set up and size than the *_AREA_SIZEP parame 阅读全文
posted @ 2013-05-06 19:41 ArcerZhang 阅读(123) 评论(0) 推荐(0) 编辑
摘要:PGA Target Advice StatisticsV$PGA_TARGET_ADVICE predicts how cache hit percentages shown in V$PGASTAT evolve.STATISTICS_LEVEL must be set to at least TYPICAL.(提示:如果想使V$PGA_TARGET_ADVICE工作,必须将STATISTIC_LEVEL修改成至少TYPICAL级别)SQL> select * from v$PGA_TARGET_ADVICE;PGA_TARGET_FOR_ESTIMATE PGA_TARGET_FA 阅读全文
posted @ 2013-05-06 18:45 ArcerZhang 阅读(248) 评论(0) 推荐(0) 编辑
摘要:cache hit percentage这个参数,可以作为DBA衡量PGA相关参数指标是否合理的一个重要标准.SQL> SELECT low_optimal_size "Low",High_optimal_size "Hight",optimal_executions "Opt",onepass_executions "One",multipasses_executions "Multi",total_executions "Total" 2 FROM v$sql_wo 阅读全文
posted @ 2013-05-06 18:11 ArcerZhang 阅读(213) 评论(0) 推荐(0) 编辑
摘要:Auto Sort Area ManagementkParameter for automatic sort area management:-PGA_AGGREGATE_TARGET(Ranges from 10MB to 4000GB)-WORKAREA_SIZE_POLICY (AUTO | MANUAL)(Oracle 推荐使用自动化管理)Replaces all *_AREA_SIZE parametersSQL> show parameter PGA_AGGREGATENAME TYPE VALUE---... 阅读全文
posted @ 2013-05-06 13:39 ArcerZhang 阅读(272) 评论(0) 推荐(0) 编辑
摘要:ObjectivesAfter completing this lesson,you should be to do the following:List the operations that use temporary space.Create and monitor temporary tablespaces.Identify actions that use the temporary tablespaceDescribe and different disk sorts and memory sortsIdentify the SQL operations that require 阅读全文
posted @ 2013-05-06 11:05 ArcerZhang 阅读(173) 评论(1) 推荐(0) 编辑
摘要:Session 信息的保存,有两种途径:1、基于Cookie的2、基于URL的Session的使用步骤1、session_start(); 开启一个会话,返回任何与session相关的信息.2、session.auto_start=0|1在php.ini文件中修改参数session.auto_start=1,那么在每个页面之前就可以不用通过session_start()函数来启动session.不过因为类的加载都是要在启动session之前加载如此这样就无法将对象设置到session中,因此一般不会将php.ini文件中的参数session.auto_start设置为1. 阅读全文
posted @ 2013-05-05 23:16 ArcerZhang 阅读(159) 评论(0) 推荐(0) 编辑
摘要:#!/bin/shPATH=$PATH:$HOME/binexport PATHORACLE_BASE=/RealDataORACLE_HOME=$ORACLE_BASE/oracleORACLE_SID=DATACENTERPATH=$ORACLE_HOME/bin:$PATHLD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATHexport ORACLE_BASE ORACLE_HOME ORACLE_SID PATH LD_LIBRARY_PATH DISPLAY#add enviroment variable nls_langexport N 阅读全文
posted @ 2013-05-05 22:14 ArcerZhang 阅读(112) 评论(0) 推荐(0) 编辑
摘要:Generate the Execution PlanCan be used without tracingNeeds the plan_table table utlxplan.sqlCreate the explain plan:SQL> explain plan for 2 select * from my_all_objects;Explained.Query plan_table TableQuery plan_table to display the execution plans:Query plan_table directly.Use script utlxpls.sq 阅读全文
posted @ 2013-05-05 12:35 ArcerZhang 阅读(411) 评论(0) 推荐(0) 编辑
摘要:Hint帮助Optimizer得到最优的查询结果.Using Hints in SQLSELECT * FORM employees;SELECT /*+ First_rows */ FROM employees;Optimizer Plan StabilityUsers can stabilize execution plans,to force applications to use a desired SQL access path.A consistent execution path is thereby maintained through database changes.Thi 阅读全文
posted @ 2013-05-04 16:48 ArcerZhang 阅读(204) 评论(0) 推荐(0) 编辑
摘要:Understanding OptimizerThe query optimizer performs the following steps:The optimizer generates a set fo potential plans for the SQL statement based on available access paths and hints.The optimizer estimates the cost of each plan based o statistics in the daa dictionary for the data distribution an 阅读全文
posted @ 2013-05-04 16:48 ArcerZhang 阅读(186) 评论(0) 推荐(0) 编辑
摘要:ObjectivesAfter completing this lesson,you should be able to do the following:Control optimizer optionsUse optimizer hintsEmploye plan stabilityUse store outlines(在10g,11g已经过时)Use SQL Trace and TKPROFOverviewThe purpose of this lesson is:To provide mthods to determine the resources used by SQL state 阅读全文
posted @ 2013-05-04 11:37 ArcerZhang 阅读(127) 评论(0) 推荐(0) 编辑
摘要:1、查看oracle server NLS_LANG参数信息;以SYSDBA身份登录view nls_parametersSQL> select * from v$nls_parameters where parameter='NLS_CHARACTERSET';PARAMETER VALUE------------------------------ ------------------------------NLS_CHARACTERSET AL32UTF82、修改环境变量export NLS_LANG# .bash_... 阅读全文
posted @ 2013-05-03 23:42 ArcerZhang 阅读(289) 评论(1) 推荐(0) 编辑
摘要:Query dba_tables for IOTDBA_TABLES->IOTSQL> SELECT table_name,iot_name,iot_type FROM dba_tables WHERE table_name LIKE '%IOT%';TABLE_NAME IOT_NAME IOT_TYPE------------------------------ ------------------------------ ------------SYS_IOT_OVER_12334 ... 阅读全文
posted @ 2013-05-03 18:20 ArcerZhang 阅读(268) 评论(0) 推荐(0) 编辑
摘要:Index-Organized TablesIOT表要求必须有主键.heap表可以没有主键.没有主键的表,不能称之为IOT表.IOT and Heap TablesCompared to heap tables,IOTs have:Faster key-based access to table dataReduced storage requirementsSecondary indexes and logical rowidsIOTs have the following restrictions:-Must have a primary key-Cannot be clusteredCr 阅读全文
posted @ 2013-05-03 15:35 ArcerZhang 阅读(230) 评论(0) 推荐(0) 编辑
摘要:connection as sysdbaSQL> grant select any dictionary to user_name; 阅读全文
posted @ 2013-05-03 11:43 ArcerZhang 阅读(458) 评论(0) 推荐(0) 编辑
摘要:Compressed IndexesCREATE INDEX t_idx on t(owner,object_type,object_name);SQL> ANALYZE INDEX t_idx validate structure;Index analyzed.SQL> create table idx_stats 2 as 3 select 'noncompressed' what,a.* from index_stats a;Table created.lab1->dataSQL> drop index t_idx;Index dropped.SQ 阅读全文
posted @ 2013-05-03 11:42 ArcerZhang 阅读(176) 评论(0) 推荐(0) 编辑
摘要:ObjectivesAfter completing this lesson,you should be able to do the following:Explain the role of the DBA in tuning applications.Move tables using the ALTER TABLE command.Redefine a table onlineCreate different types of indexesBuild and manage index-organized tablesExplain and plan OLTP,DSS,and hybr 阅读全文
posted @ 2013-05-03 11:22 ArcerZhang 阅读(171) 评论(0) 推荐(0) 编辑
摘要:ObjectivesAfter completing this lesson,you should be able to do the following:Create materialized viewsRefresh materialized viewsCreate nested materialized viewsCreate UNION ALL materialized viewsExplain the use of query rewritesEnable and control query rewritesMaterialized ViewsInstantiations of a 阅读全文
posted @ 2013-05-02 13:03 ArcerZhang 阅读(237) 评论(0) 推荐(0) 编辑

点击右上角即可分享
微信分享提示