-
编辑 删除
Oracle Hash Group By
Starting with 10g, Oracle introduced “HashGroup By” as the default grouping by mechanism. Oracle employs a hash algorithm tocalculate the hash value for each row based on the GROUP BY columns. Thisallows rows with identical column values (and hence identical hash values) tobe "br...
-
编辑 删除
Oracle Collecting Statstics
Procedure signature:DBMS_STATS.GATHER_TABLE_STATS (ownname VARCHAR2, --Owner/schema nametabname VARCHAR2, --Table Namepartname VARCHAR2 DEFAULT NULL, estimate_percent NUMBER DEFAULT to_estimate_percent_type block_sample BOOLEAN DEFAULT FALSE,method_opt...
-
编辑 删除
Oracle RBA
Redo Byte Address (RBA) An RBA points to a specific location in a particular redo thread. It is ten bytes long and has three components: Redo log file sequence number (4 bytes)Block number w...
-
编辑 删除
Enabling Archive Log mode in Oracle
1. Login as sysdba2. Configure the archive dest. db_recovery_file_dest db_recovery_file_dest_size3. Shutdown instance.4. Start the instance in mount mode....
-
编辑 删除
Oracle SID VS Service Name
https://forums.oracle.com/thread/540986?start=0&tstart=0Discussions in this post explain the concepts of sid and service well. Here're some important notes:1. With Oracle Resource...
-
编辑 删除
Deferred Constraint Check
Two ways to accomplish such goal:1. Constraints are created deferrable. Alter session set constraints = deferred.create table p( p_id number primary key);create table c(c_i...
-
编辑 删除
DDL Parameters
Enable --> Incoming dataValidate --> Existing dataRely --> For query rewriteDefer:1. Defers constraint check to the end of the transaction. This is helpful in cases where parent-child reco...
-
编辑 删除
Study of Parallel DML ( Insert into ... Select)
Here, parallel DML refers to insert, update, delete, merge, not select.Rules of Parallel DML1. alter session enable parallel DML --Must!2.A transaction can contain multiple parallel DML statements...
-
编辑 删除
Understanding Parallel Execution Plan
The execution plan:SQL_ID fjwcnz05c5ugv, child number 0-------------------------------------select /*+ PARALLEL(4) */ COUNT(order_id), CUSTOMER_ID from orders GROUP BY CUSTOMER_ID&n...
-
编辑 删除
VLDB and Partitioning Guide -- Chapter 8 Parallel DML Notes
Parallel DML Basics:1. DOP (Degree of Parallism): The number of parallel execution servers associated with a single operation.2. Query Coordinator Master. The serve...
-
编辑 删除
Oracle blocking issue with lock table in exclusive mode
A PL/SQL program previously worked in serial, but it hung when running in parallel.select ses.SID, substr(SQ.SQL_TEXT,0),sq.LAST_LOAD_TIME,ses.schemaname,SES.LOCKWAIT, SES.BLOCKING_SESSION_STATUS...
-
编辑 删除
Pipelined table function statistics and dynamic sampling
This is an extraction of Adrian Billington's article:http://www.oracle-developer.net/display.php?id=429At some point, you might need to join a pipelined function to another rowsource (...
-
编辑 删除
Direct-load insert notes
Enabling by:Insert /*+ APPEND */ into xxxCharacteristics:1. Data is written to data file directly, bypassing buffer cache.2. Inserts data beyond the High Water Mark.3. Referential integrity is...
-
编辑 删除
Oracle 时间函数NumToYMInterval()
格式：NumToYMInterval(n, interval_unit);n: 数值类型interval_unit: 'YEAR', 'MONTH' ，或其他可以转换成这两个值之一的表达式NumToYMInterval(1, 'YEAR') ：一年后的间隔NumToYMInterval(-1, 'MONTH'): 一个月前...
-
编辑 删除
ORA-00904/ORA-00942和ORACLE 大小写区分
Oracle数据库中各种对象（包括表，列，索引等）默认不区分大小写，在数据字典中一律用大写。假如用户在建表（包括外部表）时，将列名/表名用双引号括起，这些都成为大小写区分的数据库对象。这时select 时需要严...
-
编辑 删除
PL/SQL Profiler 和SQL Developer 报表
执行PLSQL数据库需要： 1. 以SYS用户执行$ORACLE_HOME/rdbms/admin/profload.sql， 创建dbms_profiler包2. 当前schema(以scott为例)执行$ORACLE_HOME/rdbms/admin/proftab.sql创建profiler需要的表和se...
-
编辑 删除
VBA从Excel中生成Oracle create table
有时会碰到Oracle设计文档用Excel，需要根据Excel生成create table语句。当表多，列多，设计变更时，这个VBA小函数就能节省很多时间。在google, baidu搜了一圈，没有找到类似的。写出来方便自己也方便他人。假设:...
-
编辑 删除
Extracting DDLs from Oracle
1. DBMS_METADATAThis example extracts DDLs of every tableand index from scott, run it in sqlplus: set pagesize 0 ---Doesnot display column names.set long 90000 &n...
-
编辑 删除
12c -- Pattern Matching
Refer to：http://www.oracle.com/webfolder/technetwork/tutorials/obe/db/12c/r1/misc/sql_pattern/sql_pattern.htmlChapter 18 SQL for Pattern MatchingBoth provide comprehensive examples to this feature....
-
编辑 删除
Oracle 12c - Data Redaction
Env:Virtualbox + Oracle Linux 64bit 6.4 + Oracle database 12.1 Introduction: A new security feature is intorudced in 12c, one of top-10 favourite new features of ...
