- 博客(100)
- 收藏
- 关注
RSS订阅转载 Oracle Materialized Views Containing Joins Only
介绍 只包含表连接而没有聚合的物化视图。与聚合类似，表连接也是相当消耗资源的操作，将其结果预先计算并存储于物化视图中，可以提高SQL执行效率。 每一个基表(包括inline vie...
2013-12-13 14:09:23
241
转载 Oracle物化视图3 - Prebuilt MV
Oracle中，Prebuilt MV建立在同名的普通表上。Prebuilt MV的列是该同名表的列的一个子集。也就是说，该同名表可以包含不在MV中出线的列(Unmanaged columns)。 在MV刷新...
2013-12-09 23:02:32
255
转载 Oracle物化视图2 -- Query Rewrite及参数
Query Rewrite的条件 Individual materialized views must have the ENABLE QUERY REWRITE clause. ...
2013-12-09 22:44:39
269
转载 Oracle 物化视图1 - 单表聚合及其快速刷新
简介 物化视图在数据仓库中常用，将结果预先计算好并存储在物化视图中，Oracle数据库通过Query Rewrite访问物化视图。可以提高SQL反应速度，改善用户体验。整个过程对用户是透明的。对于每个物化视图，Ora...
2013-12-09 11:22:08
380
转载 ORA-04091和Compound Trigger(Oracle 11g)
Trigger 常见有两种:行(Row Trigger)和语句(Statement Trigger) 还有：Instead of Trigger和Event trigger。 例子1...
2013-12-05 23:51:04
185
转载 Oracle Query Result Cache
Introduction The server result cache is a memory pool within the shared pool. When a query executes, the da...
2013-12-04 17:49:19
169
转载 Oracle Data Integrator和GoldenGate集成
先上架构图： 先决条件 1.Oracle database 11g已安装， 2. Oracle GoldenGate 11g for Linux已安装 3. Oracle Data In...
2013-12-04 15:46:15
211
转载 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
139
转载 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
214
转载 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
116
转载 【总结】去重的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
160
转载 Oracle Sequence Nocache
默认情况下，创建Sequence时，缓冲(cache)是20.意即：20次.nextval才会触发一次对sys.seq$的更新(update)操作。这样就能提高SQL执行性能。 当用户指定nocache时，如： create...
2013-12-03 14:20:50
248
转载 Oracle Linux 6.4配置NFS
1. Install su - root yum install nfs-utils service nfs start chkconfig --list nfs chkconfig nfs on 2. Turn...
2013-12-02 10:15:31
127
转载 利用Data Pump快速创建schema及其权限
expdp schemas= content=metadata_only impdpremap_schema=: ...
2013-11-28 23:27:28
151
转载 Oracle分区表迁移
有时，我们需要在另一个Oracle服务器上重建一个表，而这个表包含非常多的分区/子分区时，DB Link +CTAS不是个好选择，因为我们需要列出所有分区，得到所有分区信息也需要一些时间。这时imp/exp或者Data Pum...
2013-11-28 17:20:32
164
转载 Oracle常见数字函数
Round - 四舍五入 Trunc - 简单截取 Ceil - The CEIL function determines the smallest integer greater than (or equal to) a ...
2013-11-27 21:40:00
123
转载 Oracle Reporting 7 - Model Examples
Looping in Model: select product, country, year, week, inventory, sale, receipts from sales_fact where countr...
2013-11-27 14:58:50
146
转载 Oracle Reporting 6 - Model
Example: SELECT SUBSTR(country, 1, 20) country, SUBSTR(product, 1, 15) product, year, sales FROM sales_view W...
2013-11-26 18:18:11
193
转载 Oracle Reporting 5 - Windowing
Winodwing clause : rows | range between ... and .... ROWS - specifies the window in physical units (rows...
2013-11-26 16:04:43
142
转载 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
316
转载 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
93
转载 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
152
转载 Oracle Reporting 4 - Time Series Calculations
Query 1: SELECT SUBSTR(p.Prod_Name,1,6) Prod, t.Calendar_Year, sum(s.amount_sold) sales FROM Sales s, Times t, ...
2013-11-25 21:50:30
120
转载 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
161
转载 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
241
转载 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
128
转载 ORA-00845: MEMORY_TARGET not supported on this system
ORA-00845: MEMORY_TARGET not supported on this system Cause: The MEMORY_TARGET parameter was not supported on thi...
2013-11-24 13:31:42
101
转载 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
206
转载 Linux sed Tips
--Removing the last comma from each line. sed 's/,$//' xxx.file ...
2013-11-23 17:49:26
78
转载 Oracle Database Compression 2 - Advanced/OLTP Compression
Advanced Row Compression - OLTP Compression This type of compression is intended for OLTP applications and compre...
2013-11-21 23:10:35
104
转载 Oracle Database Compression 3 - Hybrid Columnar Compression
Hybrid Columnar Compression Hybrid Columnar Compression is a feature of the Enterprise Edition of Oracle Database...
2013-11-21 23:08:57
183
转载 Oracle Database Compression 1 - Basic Compression
Basic Row Compression This type of compression is intended for bulk load operations.The database does not compres...
2013-11-21 22:19:37
212
转载 Oracle Timezone
Oracle中相关的时区大体可以分为两类：数据库时区和session时区。 select dbtimezone from dual; ALTER DATABASE SET TIME_ZONE='+08:00'; selec...
2013-11-20 23:25:56
265
转载 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
80
转载 SELinux tips
Disabling SELinux sudo vi /etc/selinux/config SELINUX=disabled: ...
2013-11-20 09:58:17
78
转载 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
95
转载 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
137
转载 GoldenGate Tips
1. Download OGG forWindows 7 64 bit Login Oracle E-Delivery, Select "Oracle Fusion Middleware" for proudct pack...
2013-11-18 10:14:13
93
转载 Export with Spool and Parallel Utl_File
Dumpwith SPOOL set trimspool on --removes trailing blanks at the end of each displayed or spooled line. set fee...
2013-11-12 15:00:18
146
转载 Oracle Parallel Parameters
PARALLEL_DEGREE_POLICY Specifies whether or not automatic degree of Parallelism,statement queuing, and in-memory...
2013-11-12 10:59:46
116
空空如也
空空如也
TA创建的收藏夹 TA关注的收藏夹
TA关注的人