更高效的DBA_EXTENTS 查询脚本

DBI的Franck Pachot给出了一个更高效率的DBA_EXTENTS脚本,对于特别大的ORACLE数据库定位EXTENT时很有用:

Efficiently query DBA_EXTENTS for FILE_ID / BLOCK_ID

 

 


column owner format a6
column segment_type format a20
column segment_name format a15
column partition_name format a15
set linesize 200
set timing on time on echo on autotrace on stat
WITH
 l AS ( /* LMT extents indexed on ktfbuesegtsn,ktfbuesegfno,ktfbuesegbno */
  SELECT ktfbuesegtsn segtsn,ktfbuesegfno segrfn,ktfbuesegbno segbid, ktfbuefno extrfn,
         ktfbuebno fstbid,ktfbuebno + ktfbueblks - 1 lstbid,ktfbueblks extblks,ktfbueextno extno
  FROM sys.x$ktfbue
 ),
 d AS ( /* DMT extents ts#, segfile#, segblock# */
  SELECT ts# segtsn,segfile# segrfn,segblock# segbid, file# extrfn,
         block# fstbid,block# + length - 1 lstbid,length extblks, ext# extno
  FROM sys.uet$
 ),
 s AS ( /* segment information for the tablespace that contains afn file */
  SELECT /*+ materialized */
  f1.fenum afn,f1.ferfn rfn,s.ts# segtsn,s.FILE# segrfn,s.BLOCK# segbid ,s.TYPE# segtype,f2.fenum segafn,t.name tsname,blocksize
  FROM sys.seg$ s, sys.ts$ t, sys.x$kccfe f1,sys.x$kccfe f2 
  WHERE s.ts#=t.ts# AND t.ts#=f1.fetsn AND s.FILE#=f2.ferfn AND s.ts#=f2.fetsn
 ),
 m AS ( /* extent mapping for the tablespace that contains afn file */
SELECT /*+ use_nl(e) ordered */
 s.afn,s.segtsn,s.segrfn,s.segbid,extrfn,fstbid,lstbid,extblks,extno, segtype,s.rfn, tsname,blocksize
 FROM s,l e
 WHERE e.segtsn=s.segtsn AND e.segrfn=s.segrfn AND e.segbid=s.segbid
 UNION ALL
 SELECT /*+ use_nl(e) ordered */ 
 s.afn,s.segtsn,s.segrfn,s.segbid,extrfn,fstbid,lstbid,extblks,extno, segtype,s.rfn, tsname,blocksize
 FROM s,d e
  WHERE e.segtsn=s.segtsn AND e.segrfn=s.segrfn AND e.segbid=s.segbid
 UNION ALL
 SELECT /*+ use_nl(e) use_nl(t) ordered */
 f.fenum afn,null segtsn,null segrfn,null segbid,f.ferfn extrfn,e.ktfbfebno fstbid,e.ktfbfebno+e.ktfbfeblks-1 lstbid,e.ktfbfeblks extblks,null extno, null segtype,f.ferfn rfn,name tsname,blocksize
 FROM sys.x$kccfe f,sys.x$ktfbfe e,sys.ts$ t
 WHERE t.ts#=f.fetsn and e.ktfbfetsn=f.fetsn and e.ktfbfefno=f.ferfn
 UNION ALL
 SELECT /*+ use_nl(e) use_nl(t) ordered */
 f.fenum afn,null segtsn,null segrfn,null segbid,f.ferfn extrfn,e.block# fstbid,e.block#+e.length-1 lstbid,e.length extblks,null extno, null segtype,f.ferfn rfn,name tsname,blocksize
 FROM sys.x$kccfe f,sys.fet$ e,sys.ts$ t
 WHERE t.ts#=f.fetsn and e.ts#=f.fetsn and e.file#=f.ferfn
 ),
 o AS (
  SELECT s.tablespace_id segtsn,s.relative_fno segrfn,s.header_block   segbid,s.segment_type,s.owner,s.segment_name,s.partition_name
  FROM SYS_DBA_SEGS s
 ),
datafile_map as (
SELECT
 afn file_id,fstbid block_id,extblks blocks,nvl(segment_type,decode(segtype,null,'free space','type='||segtype)) segment_type,
 owner,segment_name,partition_name,extno extent_id,extblks*blocksize bytes,
 tsname tablespace_name,rfn relative_fno,m.segtsn,m.segrfn,m.segbid
 FROM m,o WHERE extrfn=rfn and m.segtsn=o.segtsn(+) AND m.segrfn=o.segrfn(+) AND m.segbid=o.segbid(+)
UNION ALL
SELECT
 file_id+(select to_number(value) from v$parameter WHERE name='db_files') file_id,
 1 block_id,blocks,'tempfile' segment_type,
 '' owner,file_name segment_name,'' partition_name,0 extent_id,bytes,
  tablespace_name,relative_fno,0 segtsn,0 segrfn,0 segbid
 FROM dba_temp_files
)
select * from datafile_map where file_id=5495 and 11970455 between block_id and block_id+blocks


Dgg Oracle MySQL SQL Server数据同步与比对

 

下载:

Dgg Oracle MySQL数据同步校准工具最新版:https://zcdn.askmaclean.com/Dgg2107.zip

Dgg申请个人版注册码: http://askmac.cn:3333/acquire

 

教程:

使用Dgg实现MySQL数据库同构跨库数据校验

使用Dgg实现Oracle数据库同构跨库数据校验

使用Dgg全量同步/初始化Initial Load MySQL数据

使用Dgg全量同步/初始化Initial Load Oracle数据

 

Dgg Oracle/MySQL数据库复制同步校准软件

Dgg Rdbms Oracle MySQL Table/Schema Sync Compare Repair

最新版:

 

Dgg is a single application that allows you to tranfer data between Oracle and MySQL. It will also support Microsoft SQL Server in future.

It supports below directionals:

Oracle To Oracle
Oracle To MySQL
MySQL To MySQL
MySQL To Oracle

特性:

  • 快速比较同步Oracle表数据
  • 快速比较同步MySQL表数据
  • 支持异构传输 Oracle <=> MySQL , 未来将支持 SQL Server
  • 将支持快速数据表校准修复
  • 将支持索引、视图、触发器等其他类型对象

 

当目标数据库类型为MySQL时,需要对应数据库用户具备SUPER ON *.* 权限,

 

GRANT SUPER ON *.* to USER;

 

否则会出现报错:msg: Access denied; you need (at least one of) the SUPER privilege(s) for this operation

 

Oracle到Oracle的传输速度:

在有NVARCHAR、LOB(BLOB CLOB NCLOB)字段的情况下,DataGoGo的单线程传输速度约为4500条/秒。

在没有NVARCHAR、LOB(BLOB CLOB NCLOB)字段的情况下,DataGoGo的单线程传输速度约为30000条/秒。

 

 

MySQL到MySQL的传输速度

没有LOB/LONGTEXT的情况下,单线程速度约为20000条/秒。

 

 

 

使用Dgg实现MySQL数据库同构跨库数据校验

在使用Dgg实现MYSQL跨库数据同步后,可以使用Dgg的数据校验功能来验证数据是否一致。

 

请首先参考《使用Dgg 全量同步/初始化Initial Load MySQL数据》 https://www.askmac.cn/archives/datagogo-initial-mysql.html

选中 树形图最上方的用户名,右击 Compare:

 

 

 

 

 

DataGoGo中存在三种 表数据的比较粒度:

  1. 只比较行数 ( 适用于大表 如日志表,为最粗粒度的比较)
  2. 只比较主键HASH (适用于中大表,要求表有主键,中等粒度比较)
  3. 比较全表HASH (适用于中小表,最细粒度比较 , 速度较慢)

 

测试 90万行 7列的表, 比较约耗时 10秒。

 

 

2015 ORCL-CON 演讲加冰与PPT下载

以下为本次2015 Orcl-Con技术大会的PPT 下载地址:

 

Francisco Munoz 新西兰Oracle用户组主席,Oracle ACE 总监, 就职于Pythian 公司 任VP

 

演讲主题《Oracle OVM最佳实践与技巧》

Francisco Munoz Alvarez – Pythian_OVM_Tips

Francisco Munoz Alvarez – Pythian_OVM_Tips – 中文

 

 

 

John King 演讲主题《 JSON Support in Oracle 12c》;就职于 King Training Resources; Oracle ACE 总监

 

 

John Jay King – Oracle_Database_12c_UsingJSONinOracle – Chinese

John Jay King – Oracle_Database_12c_UsingJSONinOracle – en

 

 

汪伟华 演讲主题《企业级MySQL备份恢复策略》;数据库技术资深讲师, 8年MySQL DBA, Oracle DBA, Oracle EBS相关维护及开发经验

 

汪伟华 – MySQL企业备份发展及实践.pdf

魏兴华演讲主题《ORACLE 12C IN-MEMORY OPTION.》 就职于沃趣科技,原阿里巴巴高级数据库工程师,Oracle internal达人,performance tunning高手,Oracle ACE-A

 

魏兴华 – 揭秘12c in-memory option.pdf

 

Yu Kai

 

 

在MACOSX 上安装Docker

注意MACOSX并不太适合部署DOCKER (在macosx上它似乎依赖于VBOX组件 ,或默认安装一个boot2docker-vm),如果你仅仅是测试或练习那么OK。

此处安装Docker主要依赖于homebrew,所以建议你的环境能正常访问BREW、GITHUB、SF等网站.


ruby -e "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/master/install)"
brew update
brew tap phinze/homebrew-cask
brew install brew-cask
brew cask install virtualbox
brew install boot2docker
boot2docker init
boot2docker up


boot2docker up会要求你设置一系列环境变量,照做即可

brew install docker
docker version


docker目前在国内没有太好的mirror,我在这里主要还是依靠docker.com的原版镜像。

【转】为什么互联网公司都在开曼群岛注册?

开曼群岛

 

本文来自《创业最前线》

开曼群岛(Cayman Islands)是英国在西印度群岛的一块海外属地,由大开曼、小开曼和开曼布拉克3个岛屿组成。开曼群岛是世界第四大离岸金融中心,并是著名的潜水胜地。(Wiki)

其它知名的离岸金融中心包括英属维尔京群岛、萨摩亚、香港、关岛等。离岸公司是指并不在注册地进行实质业务的公司。当地政府对这类公司没有任何税收,只收取少量的年度管理费,具有高度的保密性、减免税务负担、无外汇管制三大特点。

根据开曼群岛的税收规定,岛内税种只有进口税、工商登记税、旅游者税等几个简单的税种。几十年来没有开征过个人所得税、公司所得税、资本利得税、不动产税。这样,国内互联网公司选择设立立案公司的原因就呼之欲出了。

以下的内容来自张珂在知乎对这一问题的回答,详细解读了互联网公司在开曼群岛注册(设立离岸公司)的优势,给正在创业路上的你参考。

不仅仅是互联网公司,绝大多数知道开曼群岛这个地方的都会把公司注册在这儿。主要是它的政策太诱人了:

1.任何国籍年满18岁的人都可以注册;

2.注册股本只要50,000美元,且不需要验资(这可能是互联网公司注册开曼的原因,大家刚起步时都太小);

3.采用的是英式普法,公司形式是豁免公司;

4.豁免公司意味着不用在当地交税,避税效果巨强;

5.并且股东资料绝对保密;

6.还不用在开曼举行周年股东大会;

7.所有能想象到的金融服务业大佬全在开曼,有需要的时候不受其它繁琐政策限制(这意味着你在内地开公司你的公司账户也不用跨国,直接在这些大佬的分行运行);

8.开公司只需要一位股东、一位董事,且股东董事可同为一人(干,我都心动了);

9.可以选择任何词汇在你的公司名称里面(但信托、再保险等要申请),像大学、研究所还有国内受限制的环球、联邦等都可以用;

10.除了对银行、保险、军事等需申请外,公司用途无限制,你想干什么干什么;

11.在国内你享受的是外资待遇,且注册成功后直接可以投资(比如控股、合资、独资);

12.刚做起来没做好扛不住了需要暂停公司也巨方便,开曼允许随时暂停公司,只要交年报费和年审费。

Maclean参加了中国IT博客大赛

我(maclean)参加了中国IT博客大赛, 以下是我自荐的一些博文,如果觉得好 请移步到http://blog.51cto.com/contest2013/2923249 投我一票

 

 

 

vote

 

【视频教学:性能优化】Maclean Liu的Oracle性能优化讲座第一回《真正读懂Oracle SQL执行计划》

推荐理由:  真正读懂ORACLE执行计划,即是必要的,也是困难的

 

 

【性能调优】Oracle AWR报告指标全解析

推荐理由:  可能是最详尽的关于ORACLE AWR快照的解释

 

 

深入理解Oracle中的Mutex

推荐理由:  作者详尽介绍了Oracle内互斥锁mutex的原理,不过多得的关于ORACLE原理的研究分享

 

 

【Oracle Database 12c】12c 常见问题FAQ

推荐理由:  database 12是当红炸子鸡,研究Oracle 12c推荐看这篇

 

 

全面解析9i以后Oracle Latch闩锁原理

推荐理由:  如果你对深入了解ORACLE内部原理感兴趣,那么这篇关于latch的介绍值得一看

 

Maclean教你读Oracle 10046 SQL TRACE

推荐理由:  10046 是 ORACLE入门必备工具节能

 

深入了解Oracle ASM(一):基础概念

推荐理由:  ASM是ORACLE提供的适合于存放ORACLE数据库的LVM,ASM在近年的使用率大大普及,有必要了解一下其基础概念

 

 

Oracle数据恢复专题

推荐理由:  恢复恢复是Oracle中永恒的话题, 只要有数据 就有备份恢复的需求。 而在国内对于备份以及备份的可用性往往被企业所忽视。这造成了再数据库恢复上存在着东西方的差异。 更多的老外DBA把经历花在对Oracle内部原理和性能优化的研究上。

 

 

推荐理由:  ORA-8103是我们Database Consultant 经常要遇到的一个问题,了解ORA-8103的成因非常重要。

Privacy Agreement

COLLECTION OF PERSONAL DATA
If you want to interact with us via our websites or if you wish to register to access or use certain parts of our websites or other services or if you purchase any goods from us, we may ask you to provide us with some personal information.

When collecting your personal information, our staff will provide you with his name and job title so that you can identify who collects your personal information. If we collect your personal information via our websites or other non-personal interface, you will be provided means to contact us if you are in doubt in the process of collecting your personal information.

The personal information to be collected and used by us from you may include your name, sex, address, telephone numbers, email address, other contact details, occupation, and any other information which may help us to identify you, your demographical segment for our marketing and analysis purposes.

The personal information provided by you may be used by us to:

process, validate and verify subscriptions and purchase orders;
contact you when necessary;
send email notifications of new site features, newsletters and topic updates;
potentially tailor use of our websites by varying the content based upon;
understand users of our websites or services including their demographical and profile information; and
monitor users’ visits and generally collate aggregate site traffic information.
Unless you agree otherwise, your personal information will only be used by us to provide you with the products or services you have agreed and requested. Your personal information will be used by our sales and marketing team and customer services team for sales, marketing and customer services purposes. You agree that we and other group companies will use the personal information provided by you to provide you with additional information about our products and services. By accessing and using our websites or using our services, you consent to the foregoing.

CHILDREN
has no intention of collecting any personal information from individuals under thirteen years of age. Where appropriate, will specifically instruct children not to submit such information on our websites and / or will take reasonable steps to ensure parental consent to such submission.

ADDITIONAL INFORMATION COLLECTED AUTOMATICALLY
In some cases, we may automatically (i.e., not via registration) collect technical information when you connect to our site that is not personally identifiable. Examples of this type of information include the type of Internet Browser you are using, the type of computer operating system you are using and the domain name of the website from which you linked to our site.

CREDIT CARD INFORMATION
If you purchase a product or service via our websites or from other channels, you may provide us with your credit card details. These details will only be used to process your purchase. We will not disclose your credit card details to any third parties for marketing purposes under any circumstances.

COOKIES
When you view one of our websites, we may store some information on your computer. This information will be in the form of a “Cookie” or similar file and can help us in many ways. For example, Cookies allow us to tailor a website to better match your interest and preferences. With most Internet Browsers, you can erase Cookies from your computer hard drive, block all Cookies or receive a warning before a Cookie is stored. Please refer to your Browser instructions or help screen to learn more about these functions.

OPT OUTS
You may at any time opt out of receiving marketing or promotional materials, direct mails or telemarketing calls from us or other group companies by contacting us in writing (details set out in the “Contact Details” session below) or by using the opt-out facility as accompanied with such marketing or promotional materials sent by us through emails or other electronic communication channels or direct mails. You may also contact us if you do not want to receive any telemarketing calls from us.

USE OF PERSONAL DATA BY THIRD PARTIES
To enable us to better provide our services to you, we will engage third parties to process your personal information or share your personal data to our international Customer Relationship Management (CRM) system, for processing, use and retention. By continuing to use our services, access to our websites or purchase from us, you consent that your personal data will be used and maintained by us or by other companies and other third party processors engaged by us. We will make our best efforts to ensure that such processing of your personal data, either by us directly or by other companies and other third party processors, are also in compliance with the relevant privacy laws in Hong Kong.

Where or its parent organizations within group restructures its business, or those acting on its behalf, will, as part of this process, transfer and/or share your personal information to another entity within group in accordance with all applicable laws.

Subscription usage data may be stored and used to calculate charges, to monitor and review usage and to perform statistical analysis to enable us to understand who is using our websites and how it is being used. This information is used solely for our internal business purposes and will not be shared with or sold to any third parties.

We will not sell, rent, lease or make available your personal information, mailing lists or other customer data to others, and we will not make your personal information available to any unaffiliated parties, except as follows:

To agents and/or contractors who may use it on our behalf or in connection with their relationship with us (for example, we may use third parties to help us with promotional campaigns).
As required by law, in a matter of public safety or policy, as needed in connection with the transfer of our business assets (for example, if we are acquired by another company or if we are liquidated during bankruptcy proceedings), or if we believe in good faith that sharing the data is necessary to protect our rights or property.
We also rely on some of our affiliates for support of the products and services we offer, and we share some of our back-office functions with other companies. Our affiliates are all required to preserve the confidentiality of any personal information they may access.

Also, please note that we may store and process your personal information in systems located outside of your home country. However, regardless of where storage and processing may occur, we take appropriate steps to ensure that your information is protected, consistent with the principles set forth under this Privacy Agreement.

ACCESS TO YOUR INFORMATION
At any time you may ask us to

send you a copy of your personal information which we retain;
remove your personal information from our records; and/or
correct or update any of your personal information in our database.
CHANGES TO THIS PRIVACY AGREEMENT
reserves the right to amend, change or modify this Privacy Agreement at any time without prior notice to you or your consent and such amendment, changes or modification shall be effective immediately upon either posting on our websites or notifying you as the case may be.

COMPLAINTS
is committed to working with you to resolve, quickly and fairly any complaints you may have about privacy. If you have any questions or comments please contact us through one of the methods set out in the “Contact Details” section below.

SECURITY
Our websites have security measures in place to protect the loss, misuse and alteration of the information under our control. In addition to this, we have privacy policies and security measures in place to protect our customer database and access to this is restricted internally.

DISCLAIMERS AND LIMITATION OF LIABILITY
We endeavour to ensure proper collection, use and retention of your personal information. However, in no event shall we be liable for any loss or damages including without limitation, indirect or consequential loss or damages, or any loss or damages whatsoever arising from or in connection with the collection, use and retention of personal information unless we are in material breach of or gross negligence in our obligations under this Privacy Agreement or in breach of any privacy laws applicable in Hong Kong. In any event, any claim against us by a user shall be limited to HK$500.

LAW AND JURISDICTION
This Privacy Agreement shall be governed by Hong Kong law. You agree that any action arising under this Privacy Agreement and/or arising from or in connection with your privacy right shall be subject to the non-exclusive jurisdiction of Hong Kong courts.

云级Key-value数据库大比较

以下列表对各类Key-Value 的NoSQL数据库从 备份backup 到 Language API 各个方面进行了比较:

 

 

Katana DB Cassandra MongoDB CouchDB HBase Voldemort Redis Tokyo /Kyoto Cabinet Riak RavenDB CitrusLeaf BigData
License TDB Apache 2.0 AGPLv3 Apache 2.0 Apache 2.0 Apache 2.0 BSD GPLv3 Apache 2.0 AGPLv3 Proprietary GPLv2
Price TDB Free Free, Commercial Free Free Free Free Free or $12K Free Free or $1k/dev/yr Per TB per month per DataCenter Free, Commercial
Support TDB Riptano, others, Community 10gen, Community CouchBase,  Cloudant, Community Cloudera, Community Community, LinkedIn* Community, VMWare* Community, FallLabs Community, Basho Tech, Akamai* Hibernating Rhinos, Community CitrusLeaf Community, 3rd Parties
Data model Key-value with sub-keys Columnar  + Indexes Documents + Indices (JSON), Cappable Documents + Indices, Views Columnar,
?Indexing?
Key-value, no sub keys Key-value, support for sets, lists, hash maps Key-value, DBM successor (like BDB) Key-value, Dyanmo-based Documents + Indices (JSON), Views Key-value Graph
Partitioning Static Hash in R1, Split nodes evenly Dynamic Hash, Split busiest node Sorted Shards with UDSpec, Chunks (200MB) move around Not supported Sorted Regions/Shards,  automatic Region splits when reaches size X Consistent Hash, migratable partitions planned Not supported, although some clients implement consistent hashing Not supported Consistent Hash, Shards UD Key range Shards with round robin Clustering
Language APIs Java Multiple, Thrift, RPC C, C#, JavaScript, Python, PHP, Ruby, Java, C++, Perl, many 3rd party REST/JSON, many 3rd party Java, REST, Thrift Java, Python, C++ Telnet style, C, C++, C#, Java, Perl, PHP, Python, R, Ruby, Scala, others C++, C, Java, Python, Ruby, Perl, and Lua MapReduce,  Python, JavaScript, Erlang, Java, PHP, Ruby C#, HTTP C, C#, Java, Python, Ruby, PHP
Replication Single Master with configurable durability Multi-Master with vector clocks Single Master, configurable durability with early visibility Bi-directional, delayed or synchronous with conflict resolution and partial replicas Single Master, delayed WAL log replay Multi-Master, Read/Write anywhere with data repair Single Master, writable Slaves, durability is “all”? Not supported Multi-Master, Read/Write anywhere with data repair and vector clocks Multi-Master, R/W anywhere with  conflict resolution
Topology Options None in R1 Rack Aware & Unaware, DC Aware Data Center priority Data Center aware None None Hinted Handoff None
Storage Log-based FS, configurable durability Log-based FS Lazy writes to FS, HA for durability ACID, append-only Hadoop FS, durable via replication BDB JE or MySQL In-memory, w/ Snapshots or Append-only log Page-based files with WAL and shadow paging Pluggable storage layer including InnoDB
Transactions Yes, Autocommit, Major Key + Sub keys Yes, single record including Super-columns Yes, autocommit Document-centric , lockless with client conflict resolution Record &  Multi-record locking transactions (new) Autocommit operations None Yes,  single and multiple operations Autocommit operations, pre & posrt commit hooks Autocommit & multi-operation
Consistency Transactional, Configurable Eventual, with read repair option Eventual, with delay if master fails Distributed edits with conflict resolution Read-committed  transaction isolation Eventual with client-centric conflict resolution ? Serializable and Read  Committed. Eventual with client-centric conflict resolution Eventual with client-centric conflict resolution Immediate
Admin Web Console, API, CLI Ganglia, JConsole, CLI Multiple UIs, Ganglia, REST, HTTP, Java Script Futon Web Admin  tool ? Java API, CLI CLI CLI and utilities CLI and utilities CLI and utilities
Backup Single or multi-node All Nodes for consistency Quiescent, Write Lock or Snapshot File copy? Import/export BDB JE/MySQL backup? Replication or copy  file Snapshots File copy Per node or  all nodes Snapshot backups

 

 

其中*符号表示 该软件受到团体的支持(corporate support),这里的团体是指 该数据库的最主要用户和开发基金会,但是并不提供服务支持。

 

 

 

User-Proof Your Google Apps Data

沪ICP备14014813号-2

沪公网安备 31010802001379号