ITPub博客

表与表之间错误的连接方式会对性能产生巨大影响。[@more@]SQL> create table t(id int , name char(10));表已创建。SQL> create table tt(id int , name char(2000));表已创建。SQL> begin 2 for i in 1..10000 loop 3 insert into t values(i,'test'); 4 end loop; 5 commit; 6 end; 7 /PL/SQL 过程已成功完成。SQL> begin 2 for i in 1..10000 loop 3 insert into tt values(i,'test1'); 4 end loop; 5 commit; 6 end; 7 /PL/SQL 过程已成功完成。SQL> exec dbms_stats.gather_table_stats('TES

详细解释了备份恢复的原理！[@more@]--controlfile中记录的checkpoint_change#其实一句话就可以说明白：那就是数据文件的头上不仅仅包含了checkpoint_change#,更重要的是它包含了这个checkpoint_change#所在的logfile的sequence＃，准确的说是rba。结果花了很大篇幅，只想以试验的方式做个简单的验证，便于大家理解。欢迎拍砖！另外提个问题：是否存在一些view它是源于redo的？SQL> select checkpoint_change# from v$database;CHECKPOINT_CHANGE#------------------ 1951985--datafile中记录的checkpoint_change#SQL> se

记录一下！http://blog.csdn.net/fjmingyang/archive/2006/06/07/778379.aspx[@more@]http://blog.csdn.net/fjmingyang/archive/2006/06/07/778379.aspx

讲课讲到不完全恢复（找回drop table）时，经常需要确定drop table的确切时间，所以经常需要用到dbms_logmner，记录一下大致过程，备查！[@more@]SQL> desc dbms_logmnr_dPROCEDURE BUILD参数名称 类型 输入/输出默认值?------------------------------ ----------------------- ------ -------- DICTIONARY_FILENAME VARCHAR2 IN DEFAULT DICTIONARY_LOCATION VARCHAR2 IN DEFAULT OPTIONS NUMBER

How Oracle Works[@more@]How Oracle WorksThe following example describes the most basic level of operations that Oracle performs. This illustrates an Oracle configuration where the user and associated server process are on separate computers (connected through a network). An instance has started on the computer running Oracle (often called the host or database server).A computer running an application (a local computer or client workstation) runs the application in a user process. The client appl

物理读的情况是比较复杂的：1、会受到参数db_file_multiblock_read_count的影响已经不用多少了，为了减少物理读，需要设置db_file_multiblock_read_count为最大临界值。doc上提到绝大多数系统是1m，那么db_file_multiblock_read_count=系统的(max I/O size)/db_block_size系统的(max I/O size)如何准确的获得，也是我的疑问2、db_cache_size如果很小的话，那么物理读通常都会显著的高，原因是一次读进来的block无法或者是很少能找到连续的memory来容纳，这样只能通过多次来读取，读进来的block只能离散的分布在memory中，这也正是scattere

本人目前从事自由职业，oracle培训经验丰富，实践经验服务。oracle 10g ocp提供专业、全面的oracle培训、服务。联系方式：tel：13840879243mail：yongsheng.xie@gmial.commsn：xys_666888@hotmail.comqq：9020630blog：http://warehouse.itpub.net个人简介：http://warehouse.itpub.net/post/777/403336[@more@]联系方式：tel：13840879243mail：yongsheng.xie@gmial.commsn：xys_666888@hotmail.comqq：9020630blog：http://warehouse.itpub.net个人简介：http://warehouse.itpub.net/post/777/403336

If the change tracking file was stored in the database area, then it is deleted when you disable change tracking.[@more@]SQL> alter database enable block change tracking using file 2 'C:block_changerman_change_track.txt' ;数据库已更改。SQL> ALTER DATABASE DISABLE BLOCK CHANGE TRACKING;数据库已更改。--block change tracking文件可以放在flash_recovery_area,如果放在flash_recovery_area里面，则tracking文件会被自动删除，否则不会If the change tracking file was stored in the database area, then it

网上很多地方写的编译bbed的命令是：make -f ins_rdbms.mk BBED=/opt/app/oracle/product/10.2.0/db_1/rdbms/lib/bbed /opt/app/oracle/product/10.2.0/db_1/bin/bbed可是总是编译不成功，在yxyup的帮助下总算编译成功了：make -f ins_rdbms.mk BBED=$ORACLE_HOME/bin/bbed $ORACLE_HOME/bin/bbed多谢yxyup!顺手记录下大致过程，进入bbed时需要输入default的password：blockedit[@more@][oracle@xys lib]$ ls ins*ins_rdbms.mk[oracle@xys lib]$ make -f ins_rdbms.mk BBED=/opt/app/oracle/product/10.2.0/db_1/rdbms/lib/bbed /opt/app/

讲课时随手记录的大致过程！[@more@]SQL> shutdown immediate数据库已经关闭。已经卸载数据库。ORACLE 例程已经关闭。SQL> startup mountORACLE 例程已经启动。Total System Global Area 163577856 bytesFixed Size 1247876 bytesVariable Size 83887484 bytesDatabase Buffers 75497472 bytesRedo Buffers 2945024 bytes数据库装载完毕。SQL> select checkpoint_change# from v$database;CHECKPOINT_CHANGE#------------------ 727692SQL> select file#

讲课时随手记录的过程！[@more@]session 1:SQL> alter tablespace users offline;表空间已更改。SQL> select *from t2;select *from t2*第 1 行出现错误:ORA-00376: 此时无法读取文件 11ORA-01110: 数据文件 11: 'E:ORACLEPRODUCT10.2.0ORADATATESTUSERS02.DBF'SQL> alter tablespace users online;表空间已更改。SQL> select *from t2;ID----------123456已选择6行。SQL> select group#,status,sequence# ,archived,thread#,first_change# from v$log;GROUP# STATUS SEQUENCE# ARC THREAD# FIRST_CHANGE#---------- -----

讲课时随手记录的一个大致过程！[@more@]SQL> create table t2(id int) tablespace users;表已创建。SQL> select current_scn from v$database;CURRENT_SCN----------- 704959SQL> select group#,status,sequence# ,archived,thread#,first_change# from v$log; GROUP# STATUS SEQUENCE# ARC THREAD# FIRST_CHANGE#---------- ---------------- ---------- --- ---------- ------------- 1 ACTIVE 89 YES 1 704431 4 CURRENT 90 NO

insert操作放在undo中的rowid怎么来的！[@more@]SQL> create table t1(id int , name varchar2(10)) tablespace users pctfree 99;表已创建。SQL> insert into t1 values(1 , 'a');已创建 1 行。SQL> insert into t1 values(2 , 'b');已创建 1 行。SQL> insert into t1 values(3 , 'c');已创建 1 行。SQL> insert into t1 values(4 , 'd');已创建 1 行。SQL> insert into t1 values(5 , 'e');已创建 1 行。SQL> insert into t1 values(6 , 'f');已创建 1 行。SQL> insert into t1 values(7 , 'g');已创建 1 行。S

ora-01555模拟以及undo tbs的guarantee特性测试！[@more@]session 1:SQL> variable i refcursor;SQL> select *from t1; ID NAME---------- ---------- 3 c 2 test 4 d 1 undoSQL> exec open :i for select *from t1;PL/SQL 过程已成功完成。session 2:11:02:00 SQL> create undo tablespace undotbs3 datafile 'E:ORACLEPRODUCT10.2.0ORADATATESTundotbs3.dbf' size 1m;表空间已创建。11:14:48 SQL> alter system set undo_tablespace=undotbs3;系统已更改。11:15:04 SQL> alte

Password Encryption of Backups参考doc：http://users.telenet.be/oraguy.be/encryption1.htm[@more@]大致测试过程：RMAN> show all;正在启动全部恢复目录的 resync完成全部 resyncRMAN 配置参数为:CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;CONFIGURE BACKUP OPTIMIZATION OFF;CONFIGURE DEFAULT DEVICE TYPE TO DISK; # defaultCONFIGURE CONTROLFILE AUTOBACKUP OFF;CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # defaultCONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BA

configure exclude不常用，经常忘记语法，简单整理一下，备忘！[@more@]RMAN> configure exclude for tablespace 'USERS';今后的全部数据库备份将排除表空间 USERS已成功存储新的 RMAN 配置参数RMAN> SHOW EXCLUDE;使用目标数据库控制文件替代恢复目录RMAN 配置参数为:CONFIGURE EXCLUDE FOR TABLESPACE 'USERS';SQL> select NAME,INCLUDED_IN_DATABASE_BACKUP from v$tablespace;NAME INC------------------------------ ---SYSTEM YESUNDOTBS1 YESSYSAUX YESTEMP NOUSERS NONCLUDED_IN_DATABASE_BACKUP字段表示backup时对应的表

拷贝linux下冷备份db到win下测试过程[@more@]讲课的时候经常会有人问到是否可以把unix/linux下的数据库直接拷贝到win下用，或者相反，从未测试过，隐约的感觉到应该不可以，今天测试了一下，没有出现问题：测试过程是把linux下的db shutdown之后直接拷贝到win下发现没有问题：大致过程：--===================================1、首先确认linux下和win下v$transportable_platform中的ENDIAN_FORMAT都相同：这里都是LittleSQL> select *from v$transportable_platform;PLATFORM_ID PLATFORM_NAME ENDIAN_FORMAT--

通过$ORACLE_HOME/rdbm/admin/utlsyxsz.sql脚本可以估算当前sysaux中各主件对空间的使用情况.[@more@]SQL> @ E:oracleproduct10.2.0db_1RDBMSADMINutlsyxsz.sqlThis script estimates the space required for the SYSAUX tablespace.~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~Specify the Report File Name~~~~~~~~~~~~~~~~~~~~~~~~~~~~The default report file name is utlsyxsz.txt. To use this name,press <return> to continue, otherwise enter an alternative.Enter value for report_name:

使用emca可以手动配置em！配置em的过程中有一个环节要特别主要：不论使用dbca还是使用emca -deconfig dbcontrol db -repos drop在删除资料库时都有可能删除不干净，这样在再次创建资料库时都会出现错误（显示的log中有详细的记录），因此在创建资料库时最好使用下面命令手动删除一次：DROP public synonym SETEMVIEWUSERCONTEXT;DROP PUBLIC SYNONYM MGMT_TARGET_BLACKOUTS;drop role MGMT_USER;DROP USER MGMT_VIEW CASCADE;DROP USER SYSMAN CASCADE;[@more@]C:>emca -deconfig dbcontrol db -repos dropEMCA 开始于 2008-8-31 13:00

Oracle Flashback Query lets you view and repair historical data. You can perform queries on the database as of a certain wall clock time or user-specified system change number (SCN).Flashback Query uses Oracle's multiversion read-consistency capabilities to restore data by applying undo as needed.[@more@]Overview of Oracle Flashback QueryOracle Flashback Query lets you view and repair historical data. You can perform queries on the database as of a certain wall clock time or user-specified syste