2018年05月_xiadingling

10月 09月 08月 07月 06月 05月 04月

原创 How to get the values of bind variable for a sql_id

SELECT * FROM v$sql_bind_capture WHERE sql_id='';

2018-05-31 17:45:32 142

原创分区表exchange 实验

Exchange PartitionsNote:Exchange partitions only work between a partition and a temporary table.如果分区表要转换到分区表需要一个普通临时表。Cureent 分区表到 Archival 分区表的思路如下：需要注意的是：索引的维护 UPDATE INDEXES添加新的分区在历史分区表 Archival p...

2018-05-30 13:36:54 360

原创时区 Oracle deal with different time zone sys_extract_utc

Method 1:SELECT TRUNC(SYSTIMESTAMP - NUMTODSINTERVAL( SIGN(EXTRACT(TIMEZONE_HOUR FROM SYSTIMESTAMP)) * (ABS(EXTRACT(TIMEZONE_HOUR FROM SYSTIMESTAMP)) * 60 ...

2018-05-29 12:58:54 774

原创 Oracle 空字符串的比较 empty string

很容易犯的一个错误， In Oracle when empty string compare with other not null string, the value is always NULL,当 STR_1 是空字符串，STR_1 != STR_2 将返回FALSE. STR! 不会被赋值为STR2.IF STR_1 != STR_2 THENSTR1:=STR2;END I...

2018-05-22 13:01:09 2035 1

原创 Oracle 分区表 granularity 收集统计信息

GRANULARITYDetermines the granularity of statistics to collect. This value is only relevant for partitioned tables.BEGINDBMS_STATS.gather_table_stats(ownname=> 'USER', tabname => 'TABLE_NAME',pa...

2018-05-22 09:50:08 2426

原创 Oralce cannot Grant Privileges on a single partition of a partition table （Oracle 不支直接持授权给某个分区）

Oracle 不支直接持授权给某个分区objectSpecify the schema object on which the privileges are to be granted. If you do not qualify object with schema, then the database assumes the object is in your own schema. The ...

2018-05-18 17:47:57 425

原创 Oracle 11g 分区表收集统计信息使用INCREMENTAL

在11g中，数据库可以增量维护global统计信息，通过仅扫描那些有一定比例数据发生变化的分区，其他分区仍然使用老的统计信息。about INCREMENTAL option:INCREMENTAL Determines whether the global statistics of a partitioned table will be maintained without doing a...

2018-05-18 11:38:28 2183

原创 Oracle hint Leading 的使用

Leading()：指示Oracle在执行join(hash join, nested loop join, merge join)时的连接顺序。当执行计划不按照最优的表连接顺序时，考虑使用Leading 改变执行计划。eg:select v.*, t.* from v_view1 ,(select /*+ leading(t1, tftc) */ * from tftc, t1 where ...

2018-05-16 17:19:57 10743

原创 Oracle DB automatic collect statistics

1. Check Oracle DB automatic collect statistics or not: SELECT t.CLIENT_NAME, t.TASK_NAME, t.OPERATION_NAME,t.STATUS FROM dba_autotask_task t;2. check the Scheduler programsSELECT t.PROGRAM...

2018-05-14 17:28:42 318

原创 Getting a Nanny

1. hire: to pay money to someone to work2.nanny: a person usually taking care of kids, pick them up and help household chores, run some errands(like pay bills, pick up clothes or food). long-term, m...

2018-05-10 16:01:33 162