ITPub博客

今天要将一台机器的数据导入到另外一台机器，两个机器有数据库链路连接，写了几个sql语句来导数据，具体步骤是：1.sequence同步2.所有外键失效3.倒数据4.所有外键生效语句如下：sequence同步语句：declare v_sequence_owner varchar2(30); v_sequence_name varchar2(30); v_seq_num number; v_seq_num1 number; v_seq_num2 number;beginfor i in (select sequence_owner,sequence_name,last_number from all_sequences@bddb_prod where sequence_owner IN ('ANALYSIS_DW','ANALYSIS_JOB','ANALYSIS_ODS','ANALYSIZ','BD_USER','BEIDO

innodb_buffer_pool_size如 果用Innodb，那么这是一个重要变量。相对于MyISAM来说，Innodb对于buffer size更敏感。MySIAM可能对于大数据量使用默认的key_buffer_size也还好，但Innodb在大数据量时用默认值就感觉在爬了。 Innodb的缓冲池会缓存数据和索引，所以不需要给系统的缓存留空间，如果只用Innodb，可以把这个值设为内存的70%-80%。和 key_buffer相同，如果数据量比较小也不怎么增加，那么不要把这个值设太高也可以提高内存的使用率。innodb_additional_pool_size 这个的效果不是很明显，至少是当操作系统能合理分配内存时。但你可能

/*Merge into 详细介绍MERGE语句是Oracle9i新增的语法，用来合并UPDATE和INSERT语句。通过MERGE语句，根据一张表或子查询的连接条件对另外一张表进行查询，连接条件匹配上的进行UPDATE，无法匹配的执行INSERT。这个语法仅需要一次全表扫描就完成了全部工作，执行效率要高于INSERT＋UPDATE。 *//*語法：MERGE [INTO [schema .] table [t_alias] USING [schema .] { table | view | subquery } [t_alias] ON ( condition ) WHEN MATCHED THEN merge_update_clause WHEN NOT MATCHED THEN merge_insert_clause;*//*我们还是以《sql中的case应

select to_date('2012-01-01','yyyy-mm-dd')+level-1 from dual connect by level<367[@more@]

oracle 求标准偏差:select STDdEV(column1) from (select 50 column1 from dualunion select 90 from dualunion select 70 from dual) ttsqlserver 里求标准偏差:select STDEV(column1) from (select 50 column1 union select 90 union select 70 ) tt[@more@]

频繁对索引字段进行delete、update操作，会对索引造成大量碎片，从而极大地影响索引的使用效率，并造成索引IO的增加。索引碎片分析：analyze index index_name validate structure online;select name,del_lf_rows_len,lf_rows_len,(del_lf_rows_len/lf_rows_len)*100 as "索引碎片率" from index_stats;如果索引碎片率超过20%，则说明索引碎片已经非常严重。可以编写一个检测所有索引碎片率的脚本，定期运行该脚本，保持对索引碎片率的监控。索引碎片整理：重建 alter index index_name rebuild;alter index index_name rebuil

Oracle可以监控索引是否使用到打开监控：alter index index_name monitoring usage;关闭监控：alter index index_name nomonitoring usage;之后就可以查看在打开监控和关闭监控之间的时间段里，这个索引是否使用到了。select * from v$object_usage;[@more@]

lead和lag是偏移量函数，可以查出同一个字段上一个值和下一个值，在新列中显示，需要结合开窗函数进行显示数据准备：create table test(test_id varchar2(10),test_name varchar2(10),test_value number);insert into test values('1','aaa',10);insert into test values('1','bbb',20);insert into test values('1','ccc',30);insert into test values('2','ddd',40);insert into test values('2','eee',50);insert into test values('3','fff',60);commit;lead函数：select test_id,test_name,test_value,lead(test_value,1,null)over(p

Oralce官方文档“Datatypes”部分对NVARCHAR2、VARCHAR2以及VARCHAR有一段描述，可以清晰得到它们的区别。罗列在此，供大家参考。【链接】http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/sql_elements001.htm#sthref71NVARCHAR2 DatatypeThe NVARCHAR2 datatype is a Unicode-only datatype. When you create a table with an NVARCHAR2 column, you supply the maximum number of characters it can hold. Oracle subsequently stores each value in the column exactly as you specify it, provided the value does n

10g可以用:alter system set recyclebin=off; 和alter system set recyclebin=on;来关闭和开启11g可以用:alter system set recyclebin=off deferred; 和alter system set recyclebin=on deferred;来关闭和开启[@more@]

DG 主备机切换主机 alter database commit to switchover to physical standby;如果出现以下错误：ORA-01093: ALTER DATABASE CLOSE only permitted with no sessions connected解决方法： alter database commit to switchover to physical standby with session shutdown;没有问题后：shutdown immediate startup mountalter database recover managed standby database disconnect from session;主机切为备机结束。 备机操作：alter database commit to switchover to primary;shutdown immediatestartup备机变为主机。最大保护出现以下

从Oracle10g开始删除数据库表的时候并不是真正删除表，而是把表放到了recyclebin中，这个过程类似于windows里面删除的文件会被临时放到回收站中。 　　删除的表系统会自动给他重命名就是大家看到的【BIN$】开头的名字。 　　通过show recyclebin命令可以查看被删掉的表的详细信息，或者查询： 　　select * from recyclebin; 　　收回表的命令： 　　flashback table 原表名 to before drop; 　　清空回收站的命令： 　　purge recyclebin; 　　如果想直接删除表而不经过回收站： 　　drop table 表名 p

SQL> shutdown immediate数据库已经关闭。已经卸载数据库。ORACLE 例程已经关闭。SQL> startup nomountORACLE 例程已经启动。Total System Global Area 612368384 bytesFixed Size 1292036 bytesVariable Size 234883324 bytesDatabase Buffers 369098752 bytesRedo Buffers 7094272 bytesSQL> desc user_tablesERROR:ORA-04043: ?? user_tables ???SQL> alter database mount;数据库已更改。SQL> alter database open;数据库已更改。SQL> desc user_tablesERROR:ORA-04043: ???? user_tables ??????SQL> exit从 Ora

Oracle Wallet是用来加密解密的，前两天做的项目中用到了这个技术，现把一些用到的东西总结一下。原文如下：Our application does a web service call (from db to app server) to generate reports. Since SSL is used in prod, I think Oracle wallet needs to be setup in the db server for the web service calls to get through. Here's a sample code that we can use to test: SQL> set define offSQL> set serverout onSQL> declare vHttpReq utl_http.req;begin vHttpReq := utl_http.begin_request ('https://10.37

---核心1 rman全库压缩备份(14:51开始)export ORACLE_SID=ODCORE1rmanconnect catalog rman/rman@rmanconnect target system/system4dbabackup as compressed backupset full databaseformat='/bakdata1/odcore1_backup/rman_backup/20110428/full%u_%p_%c';---核心2 rman全库压缩备份（6分钟,735M）--不压缩方式（2分钟,9.6G）--压缩文件名full03matn2f_1_1--压缩文件名full04matndt_1_1export ORACLE_SID=ODCORE2rmanconnect catalog rman/rman@rmanconnect target system/system4dbabackup as compressed backupset full databasefor

ODPLAT /bakdata1/SCRIPTS> cat oramdm_level0_backup_rman.sh#set ORACLE HOME PATHexport ORACLE_HOME=/opt/oramdm/product/10.2.0/db_1 #set rman HOME pathrman_path="/opt/oramdm/product/10.2.0/db_1/bin/"echo ${rman_path} #set target database ORACLE_SIDORACLE_SID=oramdmexport ORACLE_SID #set catalog database ORACLE_SID or TNSNAMErman_sid="rman"echo ${rman_sid} #set control backup file pathbak_controlfile_path="/bakdata2/rman_oramdm/rmanbak_controlfile_level0"echo ${bak_

参数平台核心节点1核心节点2核心节点3sga_max_size4G4G2G1Gsga_target3584M3584M1536M800Mdb_cache_sizeAutoAutoAutoAutodb_keep_cache_sizeAutoAutoAutoAutoshared_pool_sizeAutoAutoAutoAutojava_pool_sizeAutoAutoAutoAutolog_buffer3050700830507008152688642875392pga_aggregate_target2000M2000M800M300Mpre_page_sgaTRUETRUETRUEFALSElock_sgaFALSEFALSEFALSEFALSEdb_file_multiblock_read_count16161616db_files150015001500200processes500500500150db_writer_processes4441session_cached_cursors20020020020Job_queue_process3

今天关闭一个rac，需要给其中的磁盘增加空间。这个磁盘是ocfs的。用crsctl stop -all这样就全停了。需要各个节点都运行。[@more@]

interval分区，和范围分区类似，不过是自动按照范围分配。oracle中原有的几个分区：hash分区、范围分区、列表分区和混合分区。今天碰到一个问题：Hi Ming, As discussed with you over chat we're testing an Oracle 11g feature called Interval Partitioning. The table partitions are fine and seems to be working as expected, the issue is in the local index when new data is added after the index is created. I've provided some scripts to demonstrate: -- create sample table on daily interval-h

hintalter sessiontable/index degreeQueryselect /*+parallel(a,8)*/ count(1) from table_name a;alter session force query parallel 8;select count(1) from table_name a;alter table table_name parallel 8;select (1) from table_name a;DML默认情况下，parallel dml是禁用的，需要先alter session启用：alter session enable parallel dml;update /*+parallel(a,8)*/ table_name a set col1=1;alter session force parallel dml parallel 8;update table_name a set col1=1;alter session enable parallel dml;alter table table_