The Ji Village News

季庄新闻–Haidong Ji’s Blog

In Oracle, there are 3 places that I know of that are important for monitoring: the bdump, where background process error is stored; udump, where user trace error is dumped; and cdump, the core dump, where Oracle internal error is dumped. cdump is in the binary format, you can use “strings -a” to look at things inside. All other trace and log files are text files that you can open up and read for yourself.

On Unix/Linux/Solaris based systems, these folders are located under:

$ORACLE_BASE/admin/$ORACLE_SID/bdump, udump, and cdump

To find out your $ORACLE_BASE and $ORACLE_SID, you can do:

echo $ORACLE_BASE and echo $ORACLE_SID

There is also the alert log, usually in the format of alert_$ORACLE_SID.log format, under the bdump folder.

There can be numerous trace and log files in these folders. To troubleshoot and correlate events with some problems, you can do:

ls -ltr

to sort files in ascending order according to date and time, and open up the file that is closest to the time when problem occurred. Those trace and log files may hold telltale signs of what you need to examine further.

For Sql Server, by default, the error log file is at C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG. And that is the only place you need to worry about. If you turn on certain trace, the trace info will also be logged into error log.

Don’t confuse Oracle’s .trc file with Sql Server’s .trc file. Oracle’s trace file is in text format, generated when certain trace flags are turned on. Sql Server’s trace file are in binary format, and can be read using Sql Server Profiler.

I mentioned here that SELECT INTO in Sql Server is functionally similar to CREATE TABLE AS in Oracle. Oracle also has SELECT INTO, but it is used for assigning query results to a variable.

Here is a PL/SQL code snippet:

declare MyVariable varchar2(20);
Begin
select ColumnName into MyVariable from MyTable where MyID = SomeInteger;
dbms_output.put_line('Hello ' || MyVariable);
End

In the above example, a column value for a particular record is assigned to MyVariable and printed out.

How do you assign select results into T-Sql variables in Sql Server then? Here is a code sample that does the same thing above:

declare @MyVariable varchar(20)
select @MyVariable = ColumnName from MyTable where MyId = SomeInteger
print 'Hello ' + @MyVariable

In both Oracle and MySQL, you can do:

create table T1 as select * from T1

This CREATE TABLE AS statement basically clones table T1 with its structure and data in T2. This can be pretty handy at times.

The equivalent of that in Sql Server is SELECT INTO. For example, you can do:

select * into T2 from T1

to achieve similar results.

I mentioned when grant statements take into effect in Sql Server, MySql, and Oracle here.

I found out recently that there are some implementation differences when you grant only delete permission on a table to a user. MySql and Sql Server do this the same way, whereas Oracle is different.

Suppose you have:

1. Table t1: create table t1 (c1 int);
2. User TestLogin. The only permission of this TestLogin is delete on t1.

In all 3 database platforms, TestLogin can find out what columns t1 has by default, using either

desc t1

or

sp_columns t1

In both Sql Server and MySql, the only thing you can do is:

delete from t1;

which essentially wipes out the whole table. You can do the same thing in Oracle.

However, if you do:

delete from t1 where c1 = 1;

you will get a select permission denied in both Sql Server and MySql, but Oracle will allow you to do it.

Personally, I think Oracle’s implementation is wrong on this one, because this gives TestLogin select permissions on this table. For example, suppose the table is a salary table, TestLogin can find out columns using desc, then it can do something like:

delete from SalaryTable where FirstName = ‘John’ and LastName = ‘Doe’ and SalaryAmount >= 50000 and SalaryAmount <= 50050;

If the statement returns “one row affected”, then the person would know John Doe’s salary. This person can then issue

rollback

If “no row affected” is returned, the person can continue until s/he can find it out.

I did a quick search on ANSI SQL 92 standard, but didn’t find anything, so I am not sure which way is consistent with ANSI 92. My check was brief, though, so I may have overlooked it.

In both Sql Server and Oracle, permission changes to a user take into effect right away, even when said user is connected at the time you made the change.

In MySql, it is a little different, depending on how the permissions are given. If you use the GRANT statement, then it takes into effect right away. However, if you create user and give it permissions by manipulating the user table in the mysql system database directly, that is, using Sql statements, then you need to issue:

flush privileges

for those changes to be picked up.

Sql Server 2005 has a command line tool named sqlcmd. MySQL has a command line tool named mysql. Oracle has a command line tool called sqlplus. They can all be used for interactive query processing and batch scripts processing. They do similar things, albeit in different ways. They are functionally equivalent.

For Sql Server 2005, when in interactive mode of sqlcmd, use

:r c:\MyFolder\MyScript.sql

to read and execute a script file. You may have to type

go

afterwards, if the last line of the script file does not end with the word go.

To use sqlcmd in batch mode, that is, to run a sql script and then get out, use:

sqlcmd -i c:\MyFolder\MyScript.sql -S MyServerName -E

Replace -E with -U LoginName if you use Sql authentication

For MySQL, while in interactive mode of mysql, use

\. c:\MyFolder\MyScript.sql (on Windows)

Note there should be a backward slash right before the dot. In fact, please pay attention to all backward slashes in Windows directory names. Or

source ~/MyFolder/MyScript.sql (on *nix)

to run a script file.

For batch mode, use

mysql -h MyHost -u MyUser -t -p < ~/MyFolder/MyScript.sql

The -t is not necessary here, but it presents the results in table/grid format, therefore more readable.

In Oracle sqlplus, while in interactive mode, use

start c:\MyFolder\MyScript.sql (on Windows)

or

@ ~./MyFolder/MyScript.sql (on *nix)

Note that if the script file name has the .sql extension at the end, it is not necessary to type .sql. sqlplus assumes the .sql extension.

For batch mode, make sure you have exit as the last line of the script file, and run this

sqlplus MyUser@OracleSID @c:\MyFolder\MyScript.sql

In Oracle and MySql, to get the column name and data type of a table, you can use:

desc MyTable

or

describe MyTable

The equivalent of desc in Sql Server is sp_columns. Therefore, run the command below will get similar results:

sp_columns MyTable

In both Oracle and Sql Server, when creating a foreign key constraint on a child table, the default option is NO ACTION, meaning that this constraint will prevent the deletion or update of a parent key if there is a row in the child table that references the key.

One popular option is CASCADE, meaning when you delete a row in the parent table, the child table’s records that reference the key value of that row will be deleted automatically also.

However, if you try to change a foreign key constraint from CASCADE to NO ACTION in Oracle, you will have to drop the constraint first and recreate it. For example, the code below will not work:

ALTER TABLE Employee MODIFY (
CONSTRAINT Department_ID_FK
FOREIGN KEY (DepartmentID)
REFERENCES Department (DepartmentID)
ON DELETE NO ACTION)

You will get error ORA-00905: missing keyword if you run the script above.

Instead, you need to drop the constraint first using something like below:

ALTER TABLE Employee DROP CONSTRAINT department_id_fk;

Then, add the NO ACTION using the script below:

ALTER TABLE Employee ADD (constraint Department_ID_FK
FOREIGN KEY (DepartmentID)
REFERENCES Department (DepartmentID));

Now, if you try to delete a row in the parent table that is referenced in the child, you will get the error below:
ORA-02292: integrity constraint (Schema.ConstraintName) violated – child record found.

It took me hours of banging my head on the wall before somebody enlightened me that NO ACTION cannot be used in the script to define a NO ACTION constraint in Oracle.

If you are inclined, you can use the scripts below to do some testing of your own. One more way to verify is to browse through Schema view in Toad to see what kind of constraint it is.

create table Department (DepartmentID NUMBER(2) primary key,
DepartmentName varchar(20));

insert into department values (1, ‘Legal’);
insert into department values (2, ‘IT’);
commit;

create table Employee (EmployeeID number(4) primary key,
DepartmentID number(2),
FName varchar2(20),
LName varchar2(20));

alter table Employee add (constraint Department_ID_FK
foreign key (DepartmentID)
references Department (DepartmentID)
on delete cascade);

insert into employee values (1, 1, ‘Bob’, ‘Smith’);
insert into employee values (2, 2, ‘Susan’, ‘Smith’);
commit;

In sqlcmd, you can connect to a different server by this command:

:connect MyServer

The above command will attempt to use integrated / Windows Active Directory authentication. To use traditional Sql Server authentication, use this instead:

:connect MyServer -U MyLogin

You can do the same kind of thing in Oracle Sql Plus by using this command:

conn[ect] MyLogin@OracleSID

For mysql command line, use this:

connect -h MyServer -u MyLogin -p

That is assuming that you are allowed to login to the MySql server from whereever you are now.

SQL Server 2005 was released in November, 2005. I actually played with it much earlier than that. Overall, I am pretty happy with it. I think it is a great product, and will give Oracle’s namesake RDBMS a run for its money.

Previously, there were a lot of arguments against Sql Server for its perceived lack of features and sophistication. With this new release, you can almost see SQL Server 2005 is aimed squarely at those charges, and by extension, Oracle. Let’s just look at a few new or enhanced things:

1. Dynamic Management Views

Dynamic Management Views, or DMVs, is a new feature introduced to SQL Server 2005. You can use it to get server state information such as CPU, memory, IO, index usage, etc. These information will be helpful for troubleshooting, diagnosis, and performance tuning.

Oracle has had similar things for a while, except it is called Dynamic Performance Views. Those are usually views whose names start with v$, such as v$session, v$sysstat, etc.

Yes, v$ views are handy in Oracle and I expect DMVs in Sql Server to be equally useful.

2. The enhanced concept of schema

The concept of schema is not new to Sql Server. However, the implementation of it is quite different in SQL Server 2005. In SQL Server 2005, schema will be object owners, not users. That’s a departure from SQL Server 2000. However, schema in Sql Server and Oracle are still different. In my understanding, a schema in Oracle is like a database in Sql Server.

3. The introduction of synonym object

Oracle has had synonyms for a while. To SQL Server 2005, this is a new concept. The implementation is very similar to Oracle.

Conceptually, synonyms are similar to views, in the sense that they are all sitting on top of base objects. Synonyms provide alternative names for all SQL Server 2005 base objects, such as tables, views, UDFs, stored procedures, assemblies, etc. As such, if you reference them in your application, they can provide a layer of abstraction, in case the name or location of the base objects change.

4. Enhanced partitions implementation

Partition creation and management is greatly enhanced in SQL Server 2005. In Sql Server 2000, Microsoft had the concept of horizontal partition and vertical partition. The concept is somewhat awkward and so is its implementation.

With SQL Server 2005, partition management has been enhanced greatly. Not only can you do table partition, index partition is also possible. Plus, the way to implement them is also more consistent with industry standard. I think this eliminated one gap between Oracle and Sql Server.

5. sqlcmd

Microsoft also totally revamped the command line tool. I’ve talked quite a bit of sqlcmd in my blog. I think it is a powerful utility for a lot of automation tasks. To compete with SQLPlus, sqlcmd introduced quite a few enhancements. The ed command will invoke default editor in both sqlcmd and SQLPlus.

6. More advanced query tuning options

Microsoft also introduced more query tuning options in this release. For starters, there is the newly introduced plan guides. Plan guides influence optimization of queries by attaching query hints to them, without changing the query itself.

Oracle has often touted its many ways to tune queries, guide execution path, and adding hints. I don’t necessarily think that is something to be proud of. I think once again, Microsoft is trying to close the gap (whether perceived or real) here.

All the above mentioned things are great stuff. (I haven’t even talked about SSIS yet.) But Microsoft has to maintain a good balance and do not over do it. Simplicity and ease of management is the great strength of Sql Server. It will be a mistake if Sql Server try to match against Oracle feature by feature, and vice versa for Oracle. After all, who wants to manage and tweak hundreds of parameters in init.ora file in Oracle? As I said, Sql Server has the reputation of easy management and “working out of the box”, and it is something that should be treated as a competitive edge. With the somewhat bloated Sql Server Management Studio, Business Intelligence Development Studio, the misleading Visual Studio program group in your program files if you do a full install, and the slowness of those tools, I actually wonder if Microsoft over-engineered things and made them more complicated than they need to be.

As mentioned in the beginning of this article, overall, I like SQL Server 2005. Combined with Windows 2003 64-bit, SQL Server 2005 will take market share away from Oracle for both low end and high end database management systems. Oracle’s DBMS has been plagued with bugs, over-complication, and pricing issues. I don’t think Oracle’s outlook is very bright in the near future.

Besides, what’s up with Larry Ellison? It seems that his only obsession is to surpass Bill Gates as the richest man in the world. Bill Gates, warts and all, at least contributed billions of dollars to world health and charity. Yeah, I think PR is not a small factor in Bill’s charitable contributions, but you still have to give the man some credit. The most noise I heard from Larry was his procurement of a MiG fighter, his yacht, his Japanese style villa, and his many egomaniac antics. What’s up with that?

Don’t get me wrong, though. I am not a big fan of Windows monopoly. And I don’t like Microsoft’s predatory practice based on its dominate position on desktop, especially in the late 90′s. But it does seem that Microsoft has backed away from that in recent years.