cuimidiao3147-CSDN博客

转载 Oracle Materialized Views Containing Joins Only

介绍只包含表连接而没有聚合的物化视图。与聚合类似，表连接也是相当消耗资源的操作，将其结果预先计算并存储于物化视图中，可以提高SQL执行效率。每一个基表(包括inline vie...

2013-12-13 14:09:23 130

转载 Oracle物化视图3 - Prebuilt MV

Oracle中，Prebuilt MV建立在同名的普通表上。Prebuilt MV的列是该同名表的列的一个子集。也就是说，该同名表可以包含不在MV中出线的列(Unmanaged columns)。在MV刷新...

2013-12-09 23:02:32 150

转载 Oracle物化视图2 -- Query Rewrite及参数

Query Rewrite的条件 Individual materialized views must have the ENABLE QUERY REWRITE clause. ...

2013-12-09 22:44:39 157

转载 Oracle 物化视图1 - 单表聚合及其快速刷新

简介物化视图在数据仓库中常用，将结果预先计算好并存储在物化视图中，Oracle数据库通过Query Rewrite访问物化视图。可以提高SQL反应速度，改善用户体验。整个过程对用户是透明的。对于每个物化视图，Ora...

2013-12-09 11:22:08 249

转载 ORA-04091和Compound Trigger(Oracle 11g)

Trigger 常见有两种:行(Row Trigger)和语句(Statement Trigger) 还有：Instead of Trigger和Event trigger。例子1...

2013-12-05 23:51:04 109

转载 Oracle Query Result Cache

IntroductionThe server result cache is a memory pool within the shared pool.When a query executes, the da...

2013-12-04 17:49:19 103

转载 Oracle Data Integrator和GoldenGate集成

先上架构图：先决条件1.Oracle database 11g已安装，2. Oracle GoldenGate 11g for Linux已安装3. Oracle Data In...

2013-12-04 15:46:15 121

转载 Oracle取随机数函数

The basic function gets a random number, greater than or equal to 0 and less than 1, with 38 digits to the right o...

2013-12-04 14:53:27 79

转载 DBMS_Utility学习

Comma_to_table 和 Table_to_Comma，比较有用。以下是例子：DECLARE v_len BINARY_INTEGER; v_tab DBMS_UTILITY.UNCL_ARRAY;...

2013-12-04 14:46:40 129

转载 Oracle Deterministic Function

Specify DETERMINISTIC to indicate that the function returns the same result value whenever it is called with the s...

2013-12-04 13:22:09 61

转载【总结】去重的SQL

1. Group-by，最简单的办法select owner from all_objects group by owner;2. 分析函数select owner from (select owner, r...

2013-12-04 10:47:30 90

转载 Oracle Sequence Nocache

默认情况下，创建Sequence时，缓冲(cache)是20.意即：20次.nextval才会触发一次对sys.seq$的更新(update)操作。这样就能提高SQL执行性能。当用户指定nocache时，如：create...

2013-12-03 14:20:50 139

转载 Oracle Linux 6.4配置NFS

1. Installsu - rootyum install nfs-utilsservice nfs startchkconfig --list nfschkconfig nfs on2. Turn...

2013-12-02 10:15:31 82

转载利用Data Pump快速创建schema及其权限

expdp schemas= content=metadata_onlyimpdpremap_schema=: ...

2013-11-28 23:27:28 92

转载 Oracle分区表迁移

有时，我们需要在另一个Oracle服务器上重建一个表，而这个表包含非常多的分区/子分区时，DB Link +CTAS不是个好选择，因为我们需要列出所有分区，得到所有分区信息也需要一些时间。这时imp/exp或者Data Pum...

2013-11-28 17:20:32 95

转载 Oracle常见数字函数

Round - 四舍五入Trunc - 简单截取Ceil - The CEIL function determines the smallest integer greater than (or equal to) a ...

2013-11-27 21:40:00 74

转载 Oracle Reporting 7 - Model Examples

Looping in Model:select product, country, year, week, inventory, sale, receiptsfrom sales_factwhere countr...

2013-11-27 14:58:50 68

转载 Oracle Reporting 6 - Model

Example:SELECT SUBSTR(country, 1, 20) country,SUBSTR(product, 1, 15) product, year, salesFROM sales_viewW...

2013-11-26 18:18:11 121

转载 Oracle Reporting 5 - Windowing

Winodwingclause : rows | range between ... and .... ROWS - specifies the window in physical units (rows...

2013-11-26 16:04:43 100

转载 Oracle Create Table as Select

CTAS employs thedirect path load, in other words, it skips loading data into buffer cache. PGA...

2013-11-26 13:30:30 209

转载 Oracle Linux xargs Command

This manual page documents the GNU version of xargs. xargs reads items from the standard input, delimited by blan...

2013-11-25 22:54:11 50

转载 ORA-00001 : Unique Constraint Violated caused by DATE VS Timestamp(6)

Create table tt1 (dt date primary key);Create table tt2(dt timestamp(6) primary key);Insert into tt2 v...

2013-11-25 21:56:01 79

转载 Oracle Reporting 4 - Time Series Calculations

Query 1:SELECT SUBSTR(p.Prod_Name,1,6) Prod, t.Calendar_Year,sum(s.amount_sold) salesFROM Sales s, Times t, ...

2013-11-25 21:50:30 63

转载 Oracle Reporting 3 - Aggregation Level

To determine the aggregation level in a report, Oracle provides grouping_id and group_id functions.Grouping_ID:G...

2013-11-25 21:19:16 98

转载 Oracle Reporting 2 - Subtotals and Grand Total

Oracle provides rollup, cube, and grouping sets extensions to groupto calculate subtotals and grandtotals. Each o...

2013-11-24 20:47:08 147

转载 Oracle Reporting 1 - Ratio_to_Report Function

The RATIO_TO_REPORT function computes the ratio of a value to the sum of a set ofvalues.RATIO_TO_REPORT ( expr )...

2013-11-24 20:09:25 67

转载 ORA-00845: MEMORY_TARGET not supported on this system

ORA-00845: MEMORY_TARGET not supported on this systemCause: The MEMORY_TARGET parameter was not supported on thi...

2013-11-24 13:31:42 54

转载 Oracle nologging

The size of a redo log generated for an operation in NOLOGGING mode is significantly smaller than the log generate...

2013-11-24 00:37:16 63

转载 Linux sed Tips

--Removing the last comma from each line.sed 's/,$//' xxx.file ...

2013-11-23 17:49:26 45

转载 Oracle Database Compression 2 - Advanced/OLTP Compression

Advanced Row Compression - OLTP CompressionThis type of compression is intended for OLTP applications and compre...

2013-11-21 23:10:35 74

转载 Oracle Database Compression 3 - Hybrid Columnar Compression

Hybrid Columnar CompressionHybrid Columnar Compression is a feature of the Enterprise Edition of Oracle Database...

2013-11-21 23:08:57 133

转载 Oracle Database Compression 1 - Basic Compression

Basic Row CompressionThis type of compression is intended for bulk load operations.The database does not compres...

2013-11-21 22:19:37 127

转载 Oracle Timezone

Oracle中相关的时区大体可以分为两类：数据库时区和session时区。select dbtimezone from dual;ALTER DATABASE SET TIME_ZONE='+08:00';selec...

2013-11-20 23:25:56 149

转载 Linux Ctrl Z

Ctrl +Z putsa process in background. For instance, while you're editing a file using vi, hitting Ctrl+z brings us...

2013-11-20 11:24:56 44

转载 SELinux tips

Disabling SELinuxsudo vi /etc/selinux/config SELINUX=disabled: ...

2013-11-20 09:58:17 46

转载 Oracle Lock Information Queries

--Find out what objects are locked.select c.owner, c.object_name, c.object_type, b.sid, b.se...

2013-11-19 13:51:33 52

转载 Setting up Samba3.6.9 on Oracle Linux 6

This post is based on:http://www.techotopia.com/index.php/Sharing_Files_between_RHEL_6_and_Windows_Systems_with_S...

2013-11-18 22:48:26 87

转载 GoldenGate Tips

1. Download OGG forWindows 7 64 bitLogin Oracle E-Delivery,Select "Oracle Fusion Middleware" for proudct pack...

2013-11-18 10:14:13 59

转载 Export with Spool and Parallel Utl_File

Dumpwith SPOOLset trimspool on --removes trailing blanks at the end of each displayed or spooled line.set fee...

2013-11-12 15:00:18 79

转载 Oracle Parallel Parameters

PARALLEL_DEGREE_POLICYSpecifies whether or not automatic degree of Parallelism,statement queuing, and in-memory...

2013-11-12 10:59:46 71

空空如也

空空如也