analogy http://www.kods.net/ analogy Fri, 30 Jul 2010 04:00:55+0000 http://www.kods.net/ en oracle in exists, and in brief usage description http://www.kods.net/oracle-in-exists-and-in-brief-usage-description/ http://www.kods.net/oracle-in-exists-and-in-brief-usage-description/#comments Fri, 20 Nov 2009 01:38:08+0000 kods.net http://www.kods.net/oracle-in-exists-and-in-brief-usage-description/



oracle in exists, and in brief usage description





There are two simple examples to illustrate the "exists" and the "in" the efficiency of the

1) select * from T1 where exists (select 1 from T2 where T1. A = T2. A);

T1 and T2 a small amount of data very large amounts of data, T1 <<T2, when, 1) the query efficiency.

2) select * from T1 where T1.a in (select T2.a from T2);

T1 and T2 the amount of data very large amount of data an hour, T1>> T2, when, 2) the query efficiency.

exists Usage:

Please note that 1) sentence, part of colored fonts, to understand its meaning;

One "select 1 from T2 where T1. A = T2. A" is equivalent to an association table query, which is equivalent

"Select 1 from T1, T2 where T1. A = T2. A"

However, if you Dangdang the implementation of a) sentence of the statement in brackets, it will report a syntax error, which exists also used to note areas.

"Exists (xxx)" says that the statement in brackets can detect record, it is to check the record exists.

Therefore, "select 1" where "a" is actually irrelevant, replaced by "*" It's no problem, it only cares about the data in brackets can find out whether there is such a record, if it exists, this one) sentence The where condition is.

in the usage:

To continue the example cited above,

"2) select * from T1 where T1.a in (select T2.a from T2)"

Here "in" in parentheses after the statement search out the contents of the field must correspond, in general, T1 and T2 the two tables of a field of significance of the expression should be the same, otherwise, check no sense that .

An analogy: T1, T2 table has a field, said the work order number, but the T1 indicated a single issue of the field work called "ticketid", T2 was "id", but the significance of the expression is the same, and the data format is the same. At this time, with 2) can be written like this:

"Select * from T1 where T1.ticketid in (select T2.id from T2)"

Of course, exists, and in other usage, do not be introduced here,
Related Posts
]]> http://www.kods.net/oracle-in-exists-and-in-brief-usage-description/ 64-bit linux oracle SGA is too large Baocuo http://www.kods.net/64-bit-linux-oracle-sga-is-too-large-baocuo/ http://www.kods.net/64-bit-linux-oracle-sga-is-too-large-baocuo/#comments Fri, 30 Oct 2009 19:27:17+0000 kods.net http://www.kods.net/64-bit-linux-oracle-sga-is-too-large-baocuo/ SQL> startup
ORA-27102: out of memory
Linux-x86_64 Error: 28: No space left on device

kernel.shmall ----

kernel.shmall parameter is to control the shared memory pages. Linux shared memory page size is 4KB, the size of shared memory segment is shared memory page size of an integer multiple of. A shared memory segment has a maximum size is 16G, then the need to share memory pages is 16GB/4KB = 16777216KB/4KB = 4194304 (pages), which is under the 64Bit system
16GB of physical memory, set only to meet the requirements kernel.shmall = 4194304 (almost the original setting 2097152
The two times). Shmmax parameter can then be adjusted to 16G, and at the same time you can modify SGA_MAX_SIZE and
SGA_TARGET for 12G (you want to set the maximum size of SGA, of course, can also be 2G ~ 14G, etc., but also the coordination of PGA
Parameters and OS, and other memory usage, can not be set too full, for example 16G). Analogy

sysctl-p to make the kernel take effect!
Related Posts
]]> http://www.kods.net/64-bit-linux-oracle-sga-is-too-large-baocuo/ Physically established under oracle data guard (8): Accident http://www.kods.net/physically-established-under-oracle-data-guard-8-accident/ http://www.kods.net/physically-established-under-oracle-data-guard-8-accident/#comments Mon, 14 Sep 2009 19:39:12+0000 kods.net http://www.kods.net/physically-established-under-oracle-data-guard-8-accident/
The success of configuration files before listening, TNS file, will also be launched to mount the database state, but the way life and death is not registered to the database through the network appears the error from the following:

ORA-01031: insufficient privileges

Tips do not have sufficient permissions, is there a mistake? I used the sys user login, sys not to have the highest authority Mody? And the password and there is no input errors. Carefully examined the listener.ora and the tnsnames.ora file, verify that there is no problem. Because in another environment is one such set. Try to look at a user has SYSDBA privileges:

SQL> select * from v $ pwfile_users;

USERNAME SYSDB SYSOP
------------------------------ ----- -----

no rows selected.

See here, the problem clearer, and the password file does not play a role. Because the registration database through the network is to read the password file, and SYS is the default with SYSDBA privileges, shown here not come out, indicating the password file has not been successfully read, and naturally can not be registered to the database. To play a simple analogy, Hsiao-ming before the final exam in a language you want to cheat, cheating way is by spending a point of silver to buy the answer, the results get a false answer, and that examination of the naturally Guobuliaoguan. Faced with this situation, roads lead to Rome.

After the recall system, building a database by default generates a correct password file, but my hand and I offered, to the deleted this file, re-created one and named orapw10gstandby.ora. The naming committed two errors, two errors let me re-examine password files to the awareness level of standardization. First of all, the password file is not. Ora suffix; Second, the suffix should be preceded by a standard "orapwSID" method name. As a result, I have deleted the password file named this error, re-establish the password file:

orapwd file = / home/db/oracle/10g/dbs/orapwora password = oracle entries = 10

To re-query in the database has a SYSDBA user:

SQL> select * from v $ pwfile_users;

USERNAME SYSDB SYSOP
------------------------------ ----- -----
SYS TRUE TRUE

This does not, come back again, and then check the network registration database, and all normal, so this issue can be considered came to an end.

The reason to write such a long one, or in order to emphasize the importance of password file is often out where exactly we ignore is the most worthy of attention.
Related Posts
]]> http://www.kods.net/physically-established-under-oracle-data-guard-8-accident/ Usage tracking errostack events http://www.kods.net/usage-tracking-errostack-events/ http://www.kods.net/usage-tracking-errostack-events/#comments Tue, 21 Jul 2009 22:25:04+0000 kods.net http://www.kods.net/usage-tracking-errostack-events/
This time we can use to track events this errorstack diagnosis. Examples are as follows:

1. Application of reported ORA-00904: "ADSAF": invalid identifier error

2. To stop the application of

3. Sysdba with the registry, the implementation of
SQL> alter system set events'904 trace name errorstack forever, level 3 ';

4. To start the implementation of the application error the application of the steps to reproduce the error, such error occurred, can udump directory, you should see a trc file generated up-to-date

5. To open the file, we can see the contents of the following

*** 2009-07-16 19:37:11.983
ksedmp: internal or fatal error
ORA-00904: "ADSAF": invalid identifier
Current SQL statement for this session:
select adsaf from test
Through the contents of the above, we can determine the form of test queries, but there is no test form adsaf this field, the error, so that we can quickly positioning error of

This situation occurred in the application of the majority of the upgrade, perhaps in a test environment to increase the field of the table, but the production of omission or negligence of the environment, and are prone to such errors.

errorstack this very useful to track events, you can trace a lot of other mistakes, we can learn by analogy use.
Related Posts
]]> http://www.kods.net/usage-tracking-errostack-events/ Oracle in the relationship between User and Schema http://www.kods.net/oracle-in-the-relationship-between-user-and-schema/ http://www.kods.net/oracle-in-the-relationship-between-user-and-schema/#comments Thu, 12 Mar 2009 10:34:25+0000 kods.net http://www.kods.net/oracle-in-the-relationship-between-user-and-schema/
In SQL Server2000 in architecture because of the reason, User and Schema there is a layer of the overall relationship between implied, let us realize that in fact rarely User and Schema are two completely different concepts, but at this architecture in SQL Server2005 break , User and Schema are also separated.

First of all, I make an analogy, what is a Database, What is a Schema, What is the Table, What is out, what is the line, what is the User? We can put Database can be seen as a big warehouse, the warehouse has a lot of hours a lot of room, Schema is one of the rooms, a Schema on behalf of a room, Table can be seen as a Schema in the bed each, Table (bed) was Add to each room, can not be placed outside the room, it would mean that the homeless sleep at night J. , Then a lot of items can be placed in bed, like a lot of Table can be placed on column and row, the data stored in the database are the basic unit of Table, in reality to place items in each warehouse is the basic unit of bed, User is the owner of each Schema , (Schema contains are so Object, rather than User), in fact, are the corresponding User and the database (that is, User is the owner of each of the corresponding database), there is now that the operation database (warehouse) the right, you certainly have every database operation In Schema (room) right, that is, each database has each User Mapping Schema (room) key, in other words, if he is the owner of a warehouse, then the right to use the warehouse and the warehouse all what are his (including the room), he has the right to complete the operation, you can not throw away things from each room, you can put some useful things to a room, huh, huh, and reality is too similar to you. User can also give me a specific distribution of authority, that is, to a room he can do is only look (Read-Only), or can be the same as the owner has control over all the (R / W), this on This depends on the role of User corresponding to a Role for the distribution rights of the question, I stay at a later detailed in a separate blog. Analogy here, I believe we all clear now.

SQL Server2000 at, if we create a database user Bosco, according to the background for you at this time we create a default default Schema】 【Bosco. Schema of the User Name and the same first name, which is poorly demarcated our users and the reasons for Schema.

In SQL Server2005 in for backwards compatibility, when you create a stored procedure with sp_adduser when a user, SQL Server2005 but also created a user name and the same Schema, but the stored procedure are retained only for backward compatibility, we should become familiar with the new language of DDL and Create User to Create Schema database operation. In SQL Server2005, when we use the Create User to create the database users, we can for the existence of a user-specified as the default Schema of Schema, if we do not specify, the default user of the Schema is dbo Schema, dbo room ( Schema) is like a big public room, in the current user does not log in under the premise of Schema default, if you are in some warehouse operations, such as Create Tabe, If you do not specify a particular room (Schema), then you had the goods on dbo into the public room (Schema) of the. However, if the current logged-on user has the default Schema, then the operation is done on the Schema by default (such as the current user log in login1, the user's default Schema for login1, so all operations are done at the login1 carried out on the default Schema. Experiments have proved true). Estimated at this time there will be a little halo you, Why? Dbo I have just said is a Schema, but you can view in the database, dbo is also a user, the halo bar, huh, huh.

SQL Server2005 in a database created when there will be some Schema to include, be included into the Schema has: dbo, INFORMATION_SCHEMA, guest, sys and so on (also some of the role of Schema, not to mention, there is a halo).

I have already mentioned above, in the SQL Server2005 stored procedure in sp_adduser When creating a user at the same time our SQL Server2005 also created a default user name and the same Schema, this time out of the question, when we create table A, if there is no specific prefix Schema to do, the A table created in the Schema on which, or which has entered the room? The answer is:

1. If the current operation of the database users (Select current_user can be found out) there is the default Schema (in the creation of user-specified time), then Table A is created at the default on the Schema.

2. If the current operation of the database user does not have the default Schema (that is, create a User at a time when the default is empty), but there is one and the same name, user name Schema, then Table A still be created in the dbo Schema on, even if there is one and Schema user name the same name exist, the user because it is not the default Schema, so the time to create the table are not considered as a general Schema to handle, Do not look the same first name, but oh no relationship.

3. If at the time of the creation of Table A specific Schema specified prefix to do, then Table A is created at the specified Schema on (you have permission?)

Also out of the question now, in the current operation of the database user (with select current_user can see that the emphasis once again) not under the premise of Schema default, when we use the Create table A statement, A table will go looking for dbo Schema, and attempt to create a dbo Schema in on, but if you create A table dbo Schema users only read-only permissions, but not written in the power? A table at this time are not set up unsuccessful, this is what I will mention to the Login, User, Role and Schema of the relationship between the four. Here, in order to avoid confusion and improve the speed of operation of the database (in the framework of a small amount of data to our naked eye can not see the difference is almost), to operate at our best every time, when database objects are explicitly designated specific Schema most prefix.

If users log in now to Sue, the user has a default Schema for Sue, then if there is a query as Select * from mytable, then search each room (Schema) of the order, how are we doing? Sequence is as follows:

1. First of all search sys.mytable (Sys Schema)

2. And search Sue.mytable (Default Schema)

3. Finally the search dbo.mytable (Dbo Schema)

Now that the U.S. implementation of the order clear, then query the database after the data in the table below, it is best to specify a specific prefix Schema, so the database will not take up the Sys Schema scan, of course, can improve the speed of the query.

In addition some of the tips are necessary for each database created, there are four essential Schema (can not delete are deleted), 4 Schema for this: dbo, guest, sys and INFORMATION_SCHEMA, the rest can be deleted Schema.
Related Posts
]]> http://www.kods.net/oracle-in-the-relationship-between-user-and-schema/