23:21 关于non-existent objects与negative dependency (11475 Bytes) » NinGoo@Net

在logzgh的blog上看到《在obj$基表中大量的non-existent类型对象是咋回事？》。这里简单解释下non-existent object和negative dependency。

对于Non-existent object，有两种来源，一种是drop或者rename object留下的，一种则是因为negative dendency机制。在我的试验中，这两种情况实际上都和synonym有关。

第一种情况，对于table，view等对象的drop/rename，我没有试验到non-existent的存在，但是在删除synonym时，可以看到Obj$实际上没有删除该synonym的记录，只是将其type#从5修改成10，也就是变成了non-existent

NING@ ning>create table test_synonym(i int);

表已创建。

NING@ ning>grant select on test_synonym to test;

授权成功。

TEST@ ning>create synonym test_synonym for ning.test_synonym;

同义词已创建。

SYS@ ning>select obj#,name,type# from obj$ where name='TEST_SYNONYM';

   OBJ# NAME TYPE#
---------- ------------------------------ ----------
   44651 TEST_SYNONYM 2
   44652 TEST_SYNONYM 5

其中type#=2的是table，type#=5的是synonym

TEST@ ning>drop synonym test_synonym;

同义词已删除。

SYS@ ning>select obj#,name,type# from obj$ where name='TEST_SYNONYM';

   OBJ# NAME TYPE#
---------- ------------------------------ ----------
   44651 TEST_SYNONYM 2
   44652 TEST_SYNONYM 10

这种情况，在数据库没有重启之前，再次创建该synonym，可以重用obj#，也就是讲type#再修改回来，这样就不会因为不停的删除重建对象导致obj$出现太多的垃圾。重启的时候，这些non-existent应该是由smon清理掉的。

TEST@ ning>create synonym test_synonym for ning.test_synonym;

同义词已创建。

SYS@ ning>select obj#,name,type# from obj$ where name='TEST_SYNONYM';

   OBJ# NAME TYPE#
---------- ------------------------------ ----------
   44651 TEST_SYNONYM 2
   44652 TEST_SYNONYM 5

SYS@ ning>shutdown immediate;
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
SYS@ ning>startup
ORACLE 例程已经启动。

Total System Global Area 130023424 bytes
Fixed Size 1247660 bytes
Variable Size 79693396 bytes
Database Buffers 41943040 bytes
Redo Buffers 7139328 bytes
数据库装载完毕。
数据库已经打开。
SYS@ ning>select obj#,name,type# from obj$ where name='TEST_SYNONYM';

   OBJ# NAME TYPE#
---------- ------------------------------ ----------
   44651 TEST_SYNONYM 2

至于第二种情况,则需要解释下什么是negative dependency。

对于一个查询中的对象，oracle是按照以下顺序来解析的：首先是current schema下找相应的对象如table,view或者private synonym，如果还没有就找public synonym，再没有就报ORA-00942了。

而对于依赖其他对象的对象，在被依赖对象出现变更后，可能会失效。这就需要有一种机制来保证oracle能对依赖关系进行跟踪。如果依赖的对象是current schem下的表，视图或者private synonym，则没有什么问题。如果是public synonym，则需要考虑的不仅是public synonym的变更会造成依赖其的对象失效，当依赖其的对象的schema下出现和public synonym相同名称的对象时，oracle应该是优先使用schema的对象，所以也需要使这些对象失效，以便重新解析到表或者视图上，private synonym没有这种问题，因为不允许在schema下出现同名的private synonym和table/view。这种情况下，oracle会在schema下创建一个non-existent对象，然后在该对象上创建一个依赖，如果以后schema下创建了同名的表或者视图，则non-existent对象被修改成实际创建的对象，使得依赖其的对象失效。Non-existent对象上的依赖就是negative denpendency。

上面讲的有些拗口，我自己都看得有点糊涂了，还是举个例子来看，注意不同语句是在不同用户下执行的：

NING@ ning>create table test_negative(i int);

表已创建。

NING@ ning>grant select on test_negative to test;

授权成功。

TEST@ ning>create public synonym test_negative for ning.test_negative;

同义词已创建。

TEST@ ning>create view v_test_negative
2 as
3 select * from test_negative;

视图已创建。

此时，由于test用户下没有叫test_negative的表或者视图，会通过public synonym来解析该对象，则会产生一个non-existent对象和一个negative dependency

SYS@ ning>select obj#,owner#,name,type# from obj$ where name='TEST_NEGATIVE';

   OBJ# OWNER# NAME TYPE#
---------- ---------- ------------------------------ ----------
   44662 1 TEST_NEGATIVE 5
   44661 42 TEST_NEGATIVE 2
   44663 43 TEST_NEGATIVE 10

SYS@ ning>select obj#,name,type# from obj$ where name='V_TEST_NEGATIVE';

   OBJ# NAME TYPE#
---------- ------------------------------ ----------
   44664 V_TEST_NEGATIVE 4

SYS@ ning>select d_obj#,p_obj# from dependency$
2 where d_obj#=44664;

   D_OBJ# P_OBJ#
---------- ----------
   44664 44663
   44664 44662

可以看到,v_test_dependency出现了两个依赖，一个是对owner#为42的ning.test_negative表的依赖，一个则是对OWNER#为43的non-existent的test.test_negative的依赖。

此时，如果在test下创建一个test_negative表，则会使用原来的non-existent的obj#，也就是实际上不会再在obj$中插入记录，只是将原来的non-existent记录的type#从10修改为2，由于obj$记录变更，导致依赖失效，下次再访问该视图时，会重新编译解析到test.test_negative表上。

TEST@ ning>create table test_negative(i int);

表已创建。

SYS@ ning>select obj#,owner#,name,type# from obj$ where name='TEST_NEGATIVE';

   OBJ# OWNER# NAME TYPE#
---------- ---------- ------------------------------ ----------
   44662 1 TEST_NEGATIVE 5
   44661 42 TEST_NEGATIVE 2
   44663 43 TEST_NEGATIVE 2

NING@ ning>insert into test_negative values(10);

已创建 1 行。

NING@ ning>commit;

提交完成。

TEST@ ning>select * from v_test_negative;

未选定行

参考：
http://www.jlcomp.demon.co.uk/faq/non_exist.html
http://www.ixora.com.au/q+a/library.htm

Related Articles

21:37 Analyze or DBMS_STATS, choose one but don't mix them (3342 Bytes) » DBA Tools

I am not so familiar with the DBMS_STATS procedure, so I always use the "ANALYZE" command. One day I got the following problem. Let's start a demo, first step is creating demo partitioned table.

SQL> CREATE TABLE T_PARTDEMO(
  2    COL1 NUMBER(10),
  3    COL2 VARCHAR2(30)
  4  ) PARTITION BY HASH(COL1) PARTITIONS 4;

Table created.

Then I gather the table statistics with DBMS_STATS package. And check the global level table statistics.

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER,'T_PARTDEMO', CASCADE=>TRUE);

PL/SQL procedure successfully completed.

SQL> SELECT TABLE_NAME, BLOCKS
  2  FROM USER_TABLES WHERE TABLE_NAME='T_PARTDEMO';

TABLE_NAME                         BLOCKS
------------------------------ ----------
T_PARTDEMO                              0

Then I insert few rows and run "ANALYZE" command, and check the statistics data again, I found that the partition level statistics data was changed, but the global level was not changed.

Read Full Text of【Analyze or DBMS_STATS, choose one but don't mix them】

High performance for Oracle text unload or export with ociuldr (3)

Oracle Bug : exp-00003 when import tables with LOB columns (0)

Integrite RMAN data file copy feature into Shell/Perl script (0)

How to compile Oracle Call Interface (OCI) program? (0)

Leave New Comment(Current: 0)

Link: http://www.dbatools.net/experience/mixed_analyze_and_dbms_stats.html

21:12 Oracle Bug : exp-00003 when import tables with LOB columns (2486 Bytes) » DBA Tools

When you use old version of exp to export tables with LOB column from Oracle 9.2.0.5 or higher version, you will get an error "EXP-00003 : no storage definition found for segment .....", actually this is an Oracle bug, you could temporary get it resolved by replace a view "exu9tne", as following:

Before exporting, run the following SQL under sys:

CREATE OR REPLACE VIEW exu9tne (
tsno, fileno, blockno, length) AS
SELECT ts#, segfile#, segblock#, length
FROM sys.uet$
WHERE ext# = 1
UNION ALL
SELECT * FROM SYS.EXU9TNEB
/

After exporting, run the following to restore the view definition according to Metalink Notes.

CREATE OR REPLACE VIEW exu9tne (
tsno, fileno, blockno, length) AS
SELECT ts#, segfile#, segblock#, length
FROM sys.uet$
WHERE ext# = 1
/

Of cause, if you make the change, you should roll back the view definition after your work done.

Unload LOBs to OS files or upload OS files to LOBs in Oracle (0)

The LONG RAW data type support of text unload (export) utility (0)

How to specify query option of Oracle export utility. (0)

Is it dangerous to change the table owner in this way? (3)

Leave New Comment(Current: 0)

Link: http://www.dbatools.net/experience/oracle_bug_exp_lob.html

21:07 Integrite RMAN data file copy feature into Shell/Perl script (2938 Bytes) » DBA Tools

We really like RMAN's data file copy feature, and we like to automate the standby build job with our own script. When we use RMAN script to copy the data file, we got few problems, first is a Oracle bug (2391697). As the database is huge (about 1000 data files) and busy, RMAN need to record the data file copy information into control file even if you specify the NOCATALOG option.

RMAN> copy datafile 'a.dbf' to 'b.dbf';
Starting copy at 06-SEP-06
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=644 devtype=DISK
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of copy command at 09/06/2006 19:31:09
ORA-00235: controlfile fixed table inconsistent due to concurrent update
RMAN-06010: error while looking up datafile: a.dbf

And the second is that the control file will become larger and larger. So we directly call the Oracle package to do the job.

Read Full Text of【Integrite RMAN data file copy feature into Shell/Perl script】

Analyze or DBMS_STATS, choose one but don't mix them (0)

Oracle Bug : exp-00003 when import tables with LOB columns (0)

How to compile Oracle Call Interface (OCI) program? (0)

The LONG RAW data type support of text unload (export) utility (0)

Leave New Comment(Current: 0)

Link: http://www.dbatools.net/experience/rman_copy_data_file.html

19:06 在路上在彩云之南在宣威 (2010 Bytes) » Oracle Life

©作者：eygle 发布在 eygle.com

这几天的运动轨迹，老爸已经在地图上标出，据说是比较的曲折。

27日下午，在经历的飞机的长时间延误之后，从成都飞到了昆明，当飞机降落时，心情的激动无以言表。
这篇土地曾经承载了我最宝贵的7年时光。

到达昆明，还要再坐车三个小时，才到达了客户的所在-宣威。一个以宣威火腿闻名的城市。

这里的阳光、空气、天空、饮食一切都好，而且在客户这里，想清楚了很多以前没想通的问题，实在是不虚此行。

彩云之南，我在路上。

相关文章|Related Articles

评论数量(3)|Add Comments

本文网址：http://www.eygle.com/archives/2007/09/onthetop_of_cloud.html

12:17 Flickr and Creative Commons (10028 Bytes) » Red Hat Magazine

This week a friend posted on her blog that she was marking all of her Flickr images “all rights reserved” (instead of with a Creative Commons license) and “friends and family only” (instead of publicly viewable) because of this story. A person uploaded photos of her daughter to Flickr. One was used without permission by Nerve Media’s Babble.com on an article about children and lead poisoning. The photo had been marked “all rights reserved,” and Nerve/Babble blamed the error on an ill-informed intern. The community responded that the company had been guilty of the same offense repeatedly in the past. Babble responded on their site that there wasn’t an alleged “long history” at all. He said/she said.

Babble isn’t the first to be accused of not respecting artists’ rights on Flickr. The list includes Microsoft and Ticketmaster. This week it’s Virgin Mobile, in a lawsuit that may be a test for Creative Commons licensing. But the cases aren’t all the same. There are two very different issues: actual violations and perceived violations.

Hey, that’s mine!

Let’s start with actual violations. Not only that, let’s start with somebody who was blatant and unapologetic about it: Ben Popken at The Consumerist, an “anti-stupid-capitalist” blog. It’s published by Gawker Media, which is also responsible for popular sites like Lifehacker, Defamer, and Gizmodo–in other words, you’d think they’d know better.

You’ll notice that the first thing under the headline on this post is the phrase, “Image redacted.” You’ll find that on a lot of Consumerist posts these days. After being called out for using a Flickr picture without credit on that post, Popken said that he had gone back and added a link to the Flickr user and would do so in the future. Only half an hour later, he changed his mind:

I have now removed the link because we realize that this policy would be more trouble than it’s worth. If people want credit, they can ask for it. If people want their photo down, they can ask us. Otherwise, we’ll just go back to using the best photos we can find in order to illustrate our posts. If you guys want a bunch of ugly ass retarded stock photos all the time, you’re in the [expletive deleted] wrong place.

Because, of course, everyone who uploads photos to the Internet reads The Consumerist religiously and will know when their photos have been used inappropriately. He later explained further that it’s just not “worth the trouble” of crediting people:

Credit is more trouble than its worth because then we would have to deal with people bitching all day that we didn’t spell their name correctly, or they want their name and not their Flickr ID and so on and so forth… when my time is better spent looking for the next post to write. The next post that will save you time or money, or reveal some corporate skulduggery or whatnot. What is the greater good? To use the best photo possible to illustrate the post and move on!

Several other users pointed out that this is exactly the sort of thing The Consumerist posts about–companies taking advantage of people. This wasn’t a case of misunderstanding the meaning of Creative Commons. The picture in question was marked “all rights reserved,” but other uncredited photos had been uploaded to Flickr with Creative Commons licenses. Popken and The Consumerist actively disrespected artists’ rights.

Two months later, Popken recanted. Five months after that, he posted about a stolen photo that was used for porn. Better late than never.

But you said I could use it!

Perceived and accidental violations happen when there’s a gap between what is actually required by the Creative Commons license an artist chose and what the artist believes is required. Just the phrase “Creative Commons” alone doesn’t tell you what uses the artist has given permission for. Artists can specify use in four ways: attribution, noncommercial, no derivatives, and share alike.

Often the perceived violation is the fault of the person who chose to use a CC license without understanding what it meant. There are posts on message boards across the web from users who found their CC-licensed images used in the way their specified licenses required, but their complaint is, “They didn’t ask me!” As explained on the CcSearch page, “If you respect the rights they have reserved…then you can use the work without having to contact them and ask.” Creative Commons is meant to simplify copyright.

If someone uses your CC-licensed photo within the parameters you specified (Attribute-ShareAlike, for example—they gave you credit and released their work under the same license), they’ve done their part. Your responsibility is to make sure you understand the license you’re assigning before that happens.

The excuse of the photographer in the Virgin Mobile case is a perceived violation: he didn’t fully understand the Creative Commons license when he chose it. He is charging Creative Commons with negligence in failing to properly inform him about the meaning of a commercial use license and the ramifications of entering into such an agreement. You can read Creative Commons’ point of view on the situation.

On the flip side, there are artists who know what they’re selecting, but users who don’t understand the license. That is Babble’s defense: their intern thought Flickr meant free and that she didn’t need to worry about it. Many photos on Flickr do carry Creative Commons terms, but not all. Also, users select the specific type of CC license per photo, so if you’re going to use pictures from Flickr, you must check the license every time and understand what each version requires.

So what’s a shutterbug supposed to do?

I already mentioned a friend who has chosen to restrict access to her photos on Flickr and to remove the CC licenses. I don’t think that’s the solution. That’s like saying that the answer to mp3 piracy is for musicians to stop making music, or to send new tracks only to their friends.

We’ve posted a few things on Red Hat Magazine lately about communities. The Internet has proven over and over that communities work. Even loosely-organized, open communities with little hierarchy work well, sometimes much better than the micromanaged, tightly closed ones. But they only work when the members are involved. We must actively participate in our communities for them to thrive.

Thomas Hawk, who has blogged extensively about the variety of problems photographers encounter, posted earlier this year about an infraction by Forbes and what he thinks about the situation pro-am photographers are in thanks to web sharing. Hawk doesn’t post on Flickr anymore. He has moved to Zooomr, a site for which he is now the CEO and “chief evangelist.” It’s not Flickr, and it’s not stock photography. His goal for Zooomr is to create “an interesting hybrid that allows a broader way for photographers to share their work while receiving compensation for it at the same time when appropriate.”

Is Zoomr going to be the magical fix? Maybe. Maybe not. But if it’s not, the photo community will find the next answer. That’s the strength of all web communities, whether it’s Flickr, or the communities around Creative Commons and open source. They find answers, and when today’s answer doesn’t work, they have a new one tomorrow.

10:47 Customized spins of Fedora (7609 Bytes) » Red Hat Magazine

When Fedora 7 was released, one of the big features that we talked about was the idea of customized spins of the distribution.

Now that Fedora 8 is on the way, it’s useful to look and see how we have done, and what sort of custom spins have been created.

Custom Spins of Fedora

The Fedora Project released both a GNOME- and a KDE-based spin for Fedora 7. For Fedora 8, once again we have those two spins, and we add to them a developer spin, and an electronics lab spin. These spins can be placed on a CD, DVD, or USB key and are “live,” meaning that they can be booted and run off of that media, without having to touch a hard disk. Each spin ncludes an icon on the desktop that the user may double-click in order to initiate an full installation. We also provide detailed instructions to assist in the creation of bootable USB drives that run Fedora.

As always, Fedora tries to stay as close to the latest upstream as possible. Fedora 8 is no different, as almost every package has been updated.

The rest of this article will discuss the four spins that were made available with Fedora 8 Test 2. The Fedora Project wiki includes a larger list of spins that have been created.

Fedora 8 Test 2

Fedora 8 Test 2 is available via BitTorrent and mirrors.

There will be a third Fedora 8 test release in October, and the final version of Fedora 8 will be released in early- to mid-November.

Fedora 8 GNOME

GNOME 2.19.90, which provides all the main desktop functionality
Firefox 2.0.0.6, a premier web browser
Evolution 2.11.92, an email, calendar, and contact management suite
OpenOffice 2.3.0 (on the DVD) and AbiWord 2.4.6 (on the LiveCD)
GIMP 2.4.0 RC2, an image composition and editing program
Pidgin 2.1.1, a premier instant messenging client
Compiz 0.5.2, a 3-D window manager using OpenGL
NetworkManager 0.6.5, a tool for configuring network connections
Rhythmbox 0.11.2, an audio player and catalog
Totem 2.19.90, a movie and video player
Apache 2.2.4, a premier web server
Kernel 2.6.23, the heart and soul of the Linux system
Pulse Audio, and improved Linux sound system
RPM 4.4.2.2, the package manager used by Fedora, Red Hat, and others
X.Org 7.2, which provides the underlying graphical user interface
Wireless firmware for Intel 3945, 4965, 2100, and 2200 cards
Updates to most of the system libraries, etc
Updated SELinux policies
New default desktop artwork

Fedora 8 KDE

KDE 3.5.7, which provides all the main desktop functionality
Firefox 2.0.0.6, a premier web browser
KOffice 1.6.3, a suite of office applications
Beryl 0.2.1, a 3-D window manager using OpenGL
NetworkManager 0.6.5, a tool for configuring network connections
Amarok 1.4.7, an audio and media player
Kaffeine 0.8.5, a video and media player
Kernel 2.6.23, the heart and soul of the Linux system
RPM 4.4.2.2, the package manager used by Fedora, Red Hat, and others
X.Org 7.2, which provides the underlying graphical user interface
Wireless firmware for Intel 3945, 4965, 2100, and 2200 cards
Updates to most of the system libraries, etc
Updated SELinux policies
New default desktop artwork

Fedora 8 Developer
This spin includes everything in the Fedora 8 GNOME spin, as well as:

Eclipse 3.3.0, a graphical development environment
Autoconf 2.61.9, a tool for configuring source code and Makefiles
Automake 1.10, a tool for automatically creating Makefiles
Ant 1.7.0, a platform-independent build tool for Java
Avalon Framework 4.1.4, a Java components interface
GCJ 1.5.0, a JPackage compatible runtime environment
JUnit 3.8.2, a unit-testing framework for Java
Log4j 1.2.14, libraries for adding logging to Java software
Lucene 1.9.1, a Java-based text search engine
Xalan 2.7.0, a Java-based XSLT parser
Xerces 2.7.1, a Java-based XML parser
Axis 1.2.1, a Simple Object Access Protocol implementation in Java
Tomcat 5.5.23, a servlet and JSP container
Flex 2.5.33, a a tool for recognizing lexical patterns in text
Bison 2.3, a tool that takes a grammar and generates a parser for it
CVS 1.11.22, source code management and version control
Subversion 1.4.4m, source code management and version control
GCC 4.1.2, a collection of source code compilers
Glade 2.12.1, a GTK+ graphical user interface builder
OpenLDAP 2.3.37, Lightweight Directory Access Protocol suite
SystemTap 0.5.14, a tool for gathering information from the kernel
Lots of GNOME, Perl, Python, kernel, RPM, etc. development libraries

Fedora 8 Electronics Lab
This spin is based off of the Fedora 8 KDE spin, and also includes:

Kicad 2007.01.15, electronic schematic diagrams and circuit boards
Alliance 5.0, a tool for Computer Assisted Design (CAD)
XCircuit 3.4.26, a graphical circuit schematic drawing program
Ngspice 17-13, a general purpose circuit layout program
Toped 0.8.5, an integrated circuit layout editor
Atlas 3.6.0, linear algebra software
FreeHDL 0.0.4, a VHDL simulator
GHDL 0.25, a VHDL simulator that uses GCC
gEDA 20070902, a program for working with circuit schematics
IRSIM 9.7.50, a tool for simulating digital circuits
Icarus Verilog 0.9.20070608, a verilog compiler and simulator
Magic 7.4.35, a VLSI layout tool
Octave 2.9.13, a language for numerical computation
OpenEXR 1.4.0a, tools for working with high dynamic range files
Piklab 0.14.5, a development environment for microcontrollers

The Fedora repositories provide a wealth of software. Allowing people to remix and customize it to suit their purposes provides users with freedom, choice, and power.

09:43 Heikki Tuuri to answer your in depth Innodb questions (2806 Bytes) » MySQL Performance Blog

Have you ever had a question about Innodb internal design or behavior which is not well covered in MySQL manual ? I surely had.
Now you have a great chance to have them answered !

Heikki Tuuri, Creator of Innodb will answer your Questions about Innodb at MySQL Performance Blog.

Please leave your questions as comments to this post by 5th of October and I will pass them to Heikki to reply merging with questions I have myself.

Note: due to Oracle policies Heikki will likely be unable to answer your questions about Innodb new features or timetables.

Entry posted by peter | One comment

Add to: | | | |

09:15 Log Buffer #64: a Carnival of the Vanities for DBAs (317 Bytes) » Pythian Group Blog

Keith Murphy has published the 64th edition of Log Buffer, the weekly review of database blogs, on Diamond Notes. Frank Wiles is standing by to do LB#65 next week on his Revolution Systems Blog. Everyone’s doing it, and you can too! Read the Log Buffer guidelines and send me, the LB coordinator, a note [...]

08:54 Using VIEW to reduce number of tables used » MySQL Performance Blog