-
编辑 删除
OGG - No Valid Default Archive Destination Directory Found for Thread #
Basically GG keeps a pointer to the oldest open transaction in the redo/archive logs -- which could be very, very old if someone starts a Toad session, locks their screen & goes on vacation for tw...
-
编辑 删除
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 (...
-
编辑 删除
Oracle replace function to delete sub-string
select replace('abc', 'a') from dual;REPLACE('abc','a')---------------------------bc
-
编辑 删除
Multi-table insert using pipelined function
Thanks to Adrian Billington's work. This article is an extraction of:http://www.oracle-developer.net/display.php?id=429one source, two targetsConsider an example of loading customers and addresses...
-
编辑 删除
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...
-
编辑 删除
Linux下高效地删除一个大文件最后N行记录
head 和管道， sed等都可以完成这个任务。但是效率非常低，因为他们都需要遍历整个文件。Oracle Linux带了Truncate命令。truncate --size=<new size> <file_name>计算出删除的行的字节数，并得到新...
-
编辑 删除
Oracle 时间函数NumToYMInterval()
格式：NumToYMInterval(n, interval_unit);n: 数值类型interval_unit: 'YEAR', 'MONTH' ，或其他可以转换成这两个值之一的表达式NumToYMInterval(1, 'YEAR') ：一年后的间隔NumToYMInterval(-1, 'MONTH'): 一个月前...
-
编辑 删除
A study of Inline view and analytic function
show relrelease 1102000300desc orders;Name Null Type ------------ ---- ------------ ORDER_ID &nb...
-
编辑 删除
使用exists(Semi-Join)优化distinct语句
在Oracle 官方文档，semi-join是这么解释的：A semijoin returns rows that match an EXISTS subquery without duplicating rowsfrom the left side of the predicate when multiple rows on the right ...
-
编辑 删除
在SQL Developer中Debug一个procedure
与调试Java程序类似，在SQL Developer中，可以设置断点，一步步跟踪，观察变量值等。需要注意几点：1. 在编译时选择-Compile for debug。 在创建procedure后，在SQL Developer中打开，点击似两个小齿轮...
-
编辑 删除
ORA-00904/ORA-00942和ORACLE 大小写区分
Oracle数据库中各种对象（包括表，列，索引等）默认不区分大小写，在数据字典中一律用大写。假如用户在建表（包括外部表）时，将列名/表名用双引号括起，这些都成为大小写区分的数据库对象。这时select 时需要严...
