ITPub博客

SQL> startup restrict;ORACLE 例程已经启动。Total System Global Area 524288000 bytesFixed Size 1249968 bytesVariable Size 205524304 bytesDatabase Buffers 310378496 bytesRedo Buffers 7135232 bytes数据库装载完毕。数据库已经打开。SQL> alter system disable restricted session;系统已更改。SQL>restrict 只有管理员能操作database，其他用户连接不上，restrict用于维护数据库时使用[@more@]

1、备份控制文件：ALTER DATABASE BACKUP CONTROLFILE TO '/oracle/backup/control.bkp';ALTER DATABASE BACKUP CONTROLFILE TO TRACE;2、损坏 重建CREATE CONTROLFILESET DATABASE prodLOGFILE GROUP 1 ('/u01/oracle/prod/redo01_01.log','/u01/oracle/prod/redo01_02.log'),GROUP 2 ('/u01/oracle/prod/redo02_01.log','/u01/oracle/prod/redo02_02.log'),GROUP 3 ('/u01/oracle/prod/redo03_01.log','/u01/oracle/prod/redo03_02.log')RESETLOGSDATAFILE '/u01/oracle/prod/system01.dbf' SIZE 3M,'/u01/oracle/prod/rbs01.dbs' SIZE

shutdown 执行过程 是否需要恢复 normal 不允许新的连接 不需要 等待用户断开连接 immediate 不允许新的连接 不需要 不允许新的事务 回滚未提交的事务 隐式断开连接，并回滚 transactional 不允许新的连接 不需要 不允许新的事务 活动事务完成后 隐式断开连接 abort 不允许新的连接 需要 不允许新的

昨天用友u8服务器中招了，原来安装的symantec antivurs。想了下，再装个趋势，杀毒，2个一起岂不杀的更干净。于是乎装了个趋势在安全模式下，用symantec antivurs杀毒，查杀了接近2000个病毒，然后重启动，server怎么也起不来，重起了4-5次，看系统日志一点线索没有，郁闷，极其郁闷今天灵机一想，是不是symantec 和 趋势 2个防病毒软件之间不兼容，不能同时装阿于是卸载了趋势软件，重起机器，好了……幸亏用友u8用的人少，而且不怎么用了，换成axapta了，没出纰漏，要不然，还不知道怎么样呢真是的，折腾了我2天时间，狂晕[@more@]

select ksppinm,ksppdesc from x$ksppi where ksppinm like 'db%'结果如下1 dbwr_io_slaves DBWR I/O slaves2 db_file_name_convert datafile name convert patterns and strings for standby/clone db3 db_block_buffers Number of database blocks cached in memory4 db_block_checksum store checksum in db blocks and check during reads5 db_block_size Size of database block in bytes6 db_cache_size Size of DEFAULT buffer pool for standard block size buffers7 db_2k_cache_size Size of cache for 2K buffer

特别是ENABLE NOVALIDATE；可以不计前嫌，不验证之前的数据Constraint States■ ENABLE ensures that all incoming data conforms to the constraint■ DISABLE allows incoming data, regardless of whether it conforms to theconstraint■ VALIDATE ensures that existing data conforms to the constraint■ NOVALIDATE means that some existing data may not conform to the constraintIn addition:■ ENABLE VALIDATE is the same as ENABLE. The constraint is checked and isguaranteed to hold for all rows.■ ENABLE NOVALIDA

老婆怀孕5周孕酮低，开了琪宁，吃了一次，恶心，就没再吃。老婆特别怕冷，晚上得盖大厚被子，出汗。[@more@]

1)、简单分区rangelisthash2）、复合分区range listrange hashexample：1）、list partitioncreate table aaa_partitioned(id number,xm varchar2(100),location varchar2(100),note varchar2(100),constraint pk_aaa_partitioned primary key (id)) PARTITION BY LIST (location) ( PARTITION aaa_partitioned_beijing VALUES ('beijing'), PARTITION aaa_partitioned_shanghai VALUES ('shanghai'), PARTITION aaa_partitioned_tianjin VALUES ('tianjin'), PARTITION aaa_partitioned_chongqing VALUES ('chongqing'), PART

SQL> select count(*) from aaa_partitioned_table; COUNT(*)---------- 1000000 Executed in 0.375 seconds SQL> truncate table aaa_partitioned; Table truncated Executed in 2.172 seconds SQL> insert /*+ APPEND */ into aaa_partitioned nologging select * from aaa_partitioned_table; 1000000 rows inserted Executed in 29 seconds SQL> commit; Commit complete Executed in 0 seconds[@more@]

说明：冷备份，就是关闭db，物理copy数据文件、日志文件、控制文件、归档文件。rem ***** COLDBACKUP ORACLE DATABASE ON WINDOWS NT *****set ShutDownFile=C:ShutDown.SQLecho connect internal/oracle > %ShutDownFile%echo shutdown immediate >> %ShutDownFile%echo startup >> %ShutDownFile%echo shutdown normal >> %ShutDownFile%echo exit >> %ShutDownFile%rem ***** SHUTDOWN THE DATABASE *******set oracle_sid=dbprimsvrmgrl @%ShutDownFile%net stop OracleStart%oracle_sid%net stop OracleS

一、oracle startup 分3步1)nomount:启动实例，也就是加载sga和启动oracle后台进程2)mount：打开加载control file；把instrance和database关联起来3)open：加载db file；redo file等，并进行一致性检查startup;==startup nomount;alter database mount;alter database open;二、oracle shutdown 也分分3步1）close：把sga buffer写入db file、 redo file。关闭db redo file2）unmount：取消instance和database的关联，关闭control file3）shutdown：释放sga内存，关闭oracle后台进程。shutdown normalshutdown immediate;shutdown abort;[

1）private SQL area2）Session Memory（hold a session’s variables (logon information) and other information related to the session.）3）SQL Work Areas重要参数和动态性能视图PGA_AGGREGATE_TARGET 用来代替SORT_AREA_SIZE, HASH_AREA_SIZE, BITMAP_MERGE_AREA_SIZE and CREATE_BITMAP_AREA_SIZE.OPEN_CURSORS 每个会话打开的游标数，不需要太大，关键是应用程序要及时关闭游标。V$SYSSTATV$SESSTATV$PGASTATV$SQL_WORKAREAV$SQL_WORKAREA_ACTIVE[@more@]

SGA1、Followed components can be managed auto.1)shared pool(sql pl/sql execute, dictionary cache)2)buffer cache(data buffer read from /write to disk；使用两个list管理：write list/LRU list.)The write list holds dirty buffers, which contain data that has been modified but has not yet been written to disk. The LRU list holds free buffers, pinned buffers, and dirty buffers that have not yet been moved to the write list.3)redo buffer pool( redo buffer write to disk，log_buffer一般设置1M到3M,见注1)4)l

Creating a reverse key index, compared to a standard index, reverses the bytes of eachcolumn indexed (except the rowid) while keeping the column order. Such an arrangement can help avoid performance degradation with Real Application Clusters where modifications to the index are concentrated on a small set of leaf blocks. By reversing the keys of the index, the insertions become distributed across all leaf keys in the index.把索引的字段每个字段反转，反转索引的目的就是消除热点块，提高并发区别于DES

document.body.oncopy = function() { if (window.clipboardData) { setTimeout(function() { var text = clipboardData.getData("text"); if (text && text.length>300) { text = text + "rnn本文来自CSDN博客，转载请标明出处：" + location.href; clipboardData.setData("text", text); } }, 100); } }

下载并安装oracle9.2.0.1http://download.oracle.com/otn/nt/oracle9i/9201/92010NT_Disk1.ziphttp://download.oracle.com/otn/nt/oracle9i/9201/92010NT_Disk2.ziphttp://download.oracle.com/otn/nt/oracle9i/9201/92010NT_Disk3.zip下载升级到oracle9.2.0.3的patchhttp://download.oracle.com/otn/nt/oracle9i/9203/9203WIN2K3.ziporacle9.2.0.1 -->oracle9.2.0.3 整个升级过程大约持续一个小时阅读patch readme文档，按照文档进行patch升级，大体步骤如下停止所有的application,sqlplus等停止所有相关的oracle service,listener运

总结：1、session级的临时表，只有在truncate table、session结束才能释放对表的锁定，等所有session释放bounding临时表，才能drop临时表；否这会报ORA-14452: 试图创建, 更改或删除正在使用的临时表中的索引。2、临时表需要drop才能去掉其定义3、临时表不产生redo、undo信息4、临时表各个session间的数据是不可见的，只能看到自己session的数据 在Oracle8i或以上版本中，可以创建以下两种临时表： 1.会话特有的临时表 CREATE GLOBAL TEMPORARY <TABLE_NAME> (<column specification>) ON COMMIT PRE

数据库A复制到AA1、dbca，通过dbca根据现有的数据库A创建模板，同generral purpose,olap一样的模板，（该模板可以包含A的结构或者数据），然后再用dbca根据已经创建好的模板来创建跟A一模一样的数据库AA。DbcaClone.rar/* Style Definitions */ table.MsoNormalTable {mso-style-name:普通表格; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-parent:""; mso-padding-alt:0pt 5.4pt 0pt 5.4pt; mso-para-margin:0pt; mso-para-margin-bottom:.0001pt; mso-pagination:wido

异常处理 ========= 为了提高应用程序的健壮性,开发人员必须考虑程序可能出现的各种错误,并进行相应的处理. Oracle中异常分为预定义例外,非预定义例外和自定义例外三种. ## 处理预定义异常 预定义异常是指由PL/SQL所提供的系统异常.当PL/SQL应用程序违反了Oralce规则或系统限制时,则会隐含的触发一个内部异常. # PL/SQL为开发人员提供了二十多个预定义异常: 1.ACCESS_INTO_NULL 该异常对应于ORA-06530错误.当开发对象类型应用时,如果没有初始化对象,直接为对象属性赋值,该异常触发. DECLARE emp emp_type; BEGIN emp.

1、desc命令2、dbms_metadata.get_ddl函数例如查看oracle.a1的表结构：SQL> set serveroutput on;SQL> DECLARE 2 TT varchar2(4000); 3 begin 4 tt := DBMS_METADATA.GET_DDL('TABLE','A1','ORACLE'); 5 dbms_output.put_line(tt); 6 end ; 7 / CREATE TABLE "ORACLE"."A1" ( "ID" VARCHAR2(10), "NAME" VARCHAR2(10) ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147