Basic differences between Oracle and SQL Server

ORACLE

• Oracle provides password complexity enforcement rule.
• Connect with one Schema and can work with other schema.
• Oracle implemented the row versioning on the DB block level.
• Oracle metadata is managed in the same manner as table data.So during data querying most of online DDL statements on the table can be performed simultaneously.
• Starting from Oracle 9i the configuration of ROLLBACK SEGMENTS is automatic.
• Oracle is well rich with Index Options.
• Oracle provides Automatic Storage Management (ASM), which assists with RAC/Grid based cluster deployment.
• Oracle provides proprietary cluster file system on Linux and Windows platforms that helps in avoiding the use of raw devices.
• Oracle provides Materialized Views for performance improvements of Stored Data with multiple Tables.
• Oracle has more recovery options for corrupted database, redo log or datafile than MSSQL.

MS SQL
Microsoft implemented row level lock based on each single row.
SQL Server

• all DDL operations that are currently running on tables belong to database "Snapshot Isolation". "Snapshot Isolation" queries are prohibited.
• SQL requires a complex setup of ROLLBACK Segments and Transaction Level use on it.
• SQL has just BTREE Index while compare to Oracle.
• SQL has limitations using Materialized Views.

Forward declaration in Oracle Functions with Example

Example of forward declaration in package :

CREATE OR REPLACE PACKAGE Functions_exmple_package
AS
PROCEDURE create_drop_seq
( p_CountryCode IN country.country_code TYPE
p_state IN p_type.p_type TYPE);

END Functions_exmple_package;
/

CREATE OR REPLACE PACKAGE BODY Functions_exmple_package
AS
/*Forward declaration for procedure as this is private to package and defined later*/
PROCEDURE create_drop_seq
( p_CountryCode IN country.country_code TYPE
p_state IN p_type.p_type TYPE
);

PROCEDURE create_drop_seq
( p_CountryCode IN country.country_code TYPE
p_state IN p_type.p_type TYPE)
AS
BEGIN
create_drop_seq(p_CountryCode cur_LegVeh);
/*procedure is called here only because it is forward declared (not defined till now)*/
END create_drop_seq;

PROCEDURE create_drop_seq ( p_CountryCode IN
country.country_code TYPE
p_state IN p_type.p_type TYPE)
AS
BEGIN
............................/*Procedure defined here onwards....*/
END create_drop_seq;

PRAGMA EXECPTION_INIT In Oracle

Pragma exception_init  Allow you to handle the Oracle predefined message by you'r own message. means you can instruct compiler toassociatethe specific message to oracle predefined message at compile time.This way you Improve the Readbility of your program,and handle it accoding to your own way.

It should be declare at the DECLARE section.

example

declare

salary number;

FOUND_NOTHING exception;

Pragma exception_init(FOUND_NOTHING ,100);

begin

select sal in to salaryfrom emp where ename ='ANURAG';

dbms_output.put_line(salary);

exception

WHEN FOUND_NOTHING THEN

dbms_output.put_line(SQLERRM);

end;

Difference between Union and Minus in Oracle

Let's consider the difference between Minus and Union using
following examples.

1.create TABLE A AND B With similar structure
2.insert records in Table A and B.
3.Keep some records identical(here 2 rows).
4.find out the difference betwn 2 looking into the output.


CREATE TABLE A(NAME VARCHAR2(30));
  INSERT INTO A VALUES('A');
   INSERT INTO A VALUES('B');
   INSERT INTO A VALUES('C');
   INSERT INTO A VALUES('D');
COMMIT;
   
CREATE TABLE B(NAME VARCHAR2(30));
INSERT INTO b VALUES('A')
INSERT INTO b VALUES('B')
INSERT INTO b VALUES('Y')
INSERT INTO b VALUES('X')
COMMIT;



1) SELECT * FROM A
 MINUS
 SELECT * FROM B

NAME                         
------------------------------
C                            
D                            
2 rows selected



 2)SELECT * FROM A
 UNION
 SELECT * FROM B


NAME                         
------------------------------
A                            
B                            
C                            
D                            
Y                            
x                            
6 rows selected

Shell script for oracle export/imp

#!/bin/ksh
# I assume that no other environment variables
# depend on ORACLE_SID
# I also assume that ORACLE_SID is used
# instead of TWO_TASKS
#
# Example:

ORACLE_SID=$1
PARFILE=$2

exp / ... parfile=$PARFILE

Sql script for exporting schema of an instances in Oracle

set serveroutput on
set trimspool on
set line 500
set head off
set feed off

spool exp_test.sql

declare
username varchar2(30);
dba_users varchar2(30);
user_id varchar2(30);
begin

select username,user_id
from dba_users;

dbms_output.put_line('spool exp.'||username||'.'||
to_char(sysdate, 'ddMonyy')||'.log');


-- Loop through schemas
for c1 in (select username us
from sys.dba_users)
loop
dbms_output.put_line(exp 'sys/||sys||'owner='||c1.us||' file=testdump.dmp log='||username||'.log');

end loop;
dbms_output.put_line('spool off');
end;
/

Unix Shell Script for Importing data using Oracle

Step 2 IMPORTING
---------------------

again create a Parameter file called c:\Import\parfile.txt

in the parfile give following

userid=raj/@
fromuser=scott
touser=raj
file=c:\Export\exportfile.dmp
--this is the export file to be imported
log=c:\Import\import.log
buffer=10000
statistics=none

then in command prompt give

imp parfile=c:\import\parfile.txt

this will start
Import: Release 9.2.0.1.0 - Production on Wed Feb 27 17:20:51 2002
(c) Copyright 2002 Oracle Corporation. All rights reserved.
Connected to: Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
Export file created byblah blah

Unix Shell Script for Exporting data using Oracle

step 1 :EXPORTING

Get an export dump of your scott schema
I usually perform an export using a parameter file

for that you need to create a parameter file save it in any location

Before you perform an export make sure you have calculated size of your scott schema and see if you have enogh space left in you local hard disk for the dump file


eg i created a paramter file c:\Export\Parfile.txt

in the Parfile.txt give the following

userid=scott/password@databasename
owner=scott
file=c:\Export\exportfile.dmp
log=c:\Export\exportlog.log
buffer=10000

then in command prompt give

exp parfile=c:\Export\Parfile.txt

this will start a series on operation
you will see various messages like
Export: Release 9.2.0.1.0 - Production on Wed Feb 27 16:52:15 2002
(c) Copyright 2002 Oracle Corporation. All rights reserved.
Connected to: Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
exporting table abc..
exporting table someother etc

once it is over an export file has been created in c:\Export\ by name exportfile.dmp

now you are ready for importing

Oracle/PLSQL: To_Char Function | Oracle Functions Tutorial

In Oracle/PLSQL, the to_char function converts a number or date to a string.

The syntax for the to_char function is:

to_char( value, [ format_mask ], [ nls_language ] )

value can either be a number or date that will be converted to a string.

format_mask is optional. This is the format that will be used to convert value to a string.

nls_language is optional. This is the nls language used to convert value to a string.



Applies To:

Oracle 8i, Oracle 9i, Oracle 10g, Oracle 11g


Examples - Numbers

The following are number examples for the to_char function.

to_char(1210.73, '9999.9') would return '1210.7'
to_char(1210.73, '9,999.99') would return '1,210.73'
to_char(1210.73, '$9,999.00') would return '$1,210.73'
to_char(21, '000099') would return '000021'

Oralce Dynamic SQL Debugging |Oracle PL/SQL Interview Questions for TCS,Wipro,Infosys,IBM,and Accenture

You need to store each and every line of the dynamic sql in a file using UTL_FILE Package before you could execute it so that you can refer the same file after executing the code this here serves you as a log file for debugging.

For Ex:

UTL_FILE.PUT_LINE(v_filehandle p_string_in);

where p_string_in is dynamic SQL for each line.

Suppose if my dynamic SQL consists of following 3 lines.

------

BEGIN

EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS''

END;

------

then you will call UTL_FILE.PUT_LINE function 3 times

as follows:

UTL_FILE.PUT_LINE(v_filehandle 'BEGIN');

UTL_FILE.PUT_LINE(v_filehandle 'EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS''');

UTL_FILE.PUT_LINE(v_filehandle 'END');

Oralce PL/SQL Interview Questions

PL-SQL Interview Questions with Answers

1. Describe the difference between a procedure, function and anonymous pl/sql block.
Level: Low
Expected answer : Candidate should mention use of DECLARE statement, a function must return a value while a procedure doesn't have to.

2. What is a mutating table error and how can you get around it?
Level: Intermediate
Expected answer: This happens with triggers. It occurs because the trigger is trying to update a row it is currently using. The usual fix involves either use of views or temporary tables so the database is selecting from one while updating the other.

3. Describe the use of %ROWTYPE and %TYPE in PL/SQL
Level: Low
Expected answer: %ROWTYPE allows you to associate a variable with an entire table row.
The %TYPE associates a variable with a single column type.

4. What packages (if any) has Oracle provided for use by developers?
Expected answer: Oracle provides the DBMS_ series of packages. There are many
which developers should be aware of such as DBMS_SQL, DBMS_PIPE, DBMS_TRANSACTION,
DBMS_LOCK, DBMS_ALERT, DBMS_OUTPUT, DBMS_JOB, DBMS_UTILITY, DBMS_DDL, UTL_FILE. If they can mention a few of these and describe how they used them, even better. If they include the SQL routines provided by Oracle, great, but not really what was asked.

5. Describe the use of PL/SQL tables
Expected answer: PL/SQL tables are scalar arrays that can be referenced by a
binary integer. They can be used to hold values for use in later queries
or calculations. In Oracle 8 they will be able to be of the %ROWTYPE designation, or RECORD.

6. When is a declare statement needed ?
The DECLARE statement is used in PL/SQL anonymous blocks such as with stand alone, non-stored PL/SQL procedures. It must come first in a PL/SQL stand alone file if it is used.

7. In what order should a open/fetch/loop set of commands in a PL/SQL block be implemented if you use the NOTFOUND cursor variable in the exit when statement? Why?
Expected answer: OPEN then FETCH then LOOP followed by the exit when. If not specified in this order will result in the final return being done twice because of the way the %NOTFOUND is handled by PL/SQL.

8. What are SQLCODE and SQLERRM and why are they important for PL/SQL developers?
Expected answer: SQLCODE returns the value of the error number for the last error encountered. The SQLERRM returns the actual error message for the last error encountered. They can be used in exception handling to report, or, store in an error log table, the error that occurred in the code. These are especially useful for the WHEN OTHERS exception.

9. How can you find within a PL/SQL block, if a cursor is open?
Expected answer: Use the %ISOPEN cursor status variable.

10. How can you generate debugging output from PL/SQL?
Expected answer: Use the DBMS_OUTPUT package. Another possible method is to just use the SHOW ERROR command, but this only shows errors. The DBMS_OUTPUT package can be used to show intermediate results from loops and the status of variables as the procedure is executed. The new package UTL_FILE can
also be used.

11. What are the types of triggers?
Expected Answer: There are 12 types of triggers in PL/SQL that consist of
combinations of the BEFORE, AFTER, ROW, TABLE, INSERT, UPDATE, DELETE and
ALL key words:
BEFORE ALL ROW INSERT
AFTER ALL ROW INSERT
BEFORE INSERT
AFTER INSERT etc.

Oracle vs. Sybase: 10 reasons to use Sybase on Linux

Sybase products are generally perceived within the database administrator (DBA) community as very reliable and easy to maintain, particularly compared to Oracle. Any move from Sybase to another DBMS (database management system) has got to be justified in terms of the current level of dissatisfaction with Sybase and the level of desire to use the other. I cannot recall anywhere where this is valid.

1. The latest Sybase flagship product, ASE 15, has filled much of the perceived functionality gap between ASE and other databases.
2. Linux is an ideal and cost-effective platform for development teams and many companies. With the availability of heterogeneous dump and load of Sybase databases across different operating systems, Sybase -- by virtue of its modularity and ease of use -- is an ideal DBMS for Linux. This needs to be contrasted with Oracle which is, pound for pound, a far heavier beast and resource-hungry.
3. Sybase has a well-established and skilled workforce, offering infrastructure and development teams who are fully familiar with database architectures and Sybase products.
4. Applications developed using Sybase have been running for a while and providing adequate service.

Continue Reading on SearchOracle.com

By Mich Talebzedah
SearchOracle.com

How to Kill All Processes That Have Open Connection in a SQL

The below code block can be used to kill all the processes which are connected to the database named @dbname except the process that the code block is running in the scope of. You can also set the database name by the DB_NAME() property.

DECLARE @dbname nvarchar(50)
DECLARE @SPId int

SET @dbname = N'Works'
--SET @dbname = DB_NAME()
DECLARE my_cursor CURSOR FAST_FORWARD FOR
SELECT SPId FROM MASTER..SysProcesses
WHERE DBId = DB_ID(@DatabaseName) AND SPId <> @@SPId

OPEN my_cursor

FETCH NEXT FROM my_cursor INTO @SPId

WHILE @@FETCH_STATUS = 0
BEGIN
KILL @SPId

FETCH NEXT FROM my_cursor INTO @SPId
END

CLOSE my_cursor
DEALLOCATE my_cursor

10 Reasons why DBAs Should Use Native XML

James Gosling leaves Oracle

James Gosling, the creator of the Java programming language, has resigned from Oracle, he announced in a blog entry on Friday
Gosling resigned on April 2 and has not yet taken a job elsewhere, he reported.
"As to why I left, it's difficult to answer: just about anything I could say that would be accurate and honest would do more harm than good," he wrote.
Source:www.businessweek.com

Top 10 Reasons for poor Oracle performance

   1.   Bad Connection Management

The application connects and disconnects for each database interaction. This problem is common with stateless middleware in application servers. It has over two orders of magnitude impact on performance, and it is totally unscalable.

    2. Bad Use of Cursors and the Shared Pool

Not using cursors results in repeated parses. If bind variables are not used, then there is hard parsing of all SQL statements. This has an order of magnitude impact in performance, and it is totally unscalable. Use cursors with bind variables that open the cursor and execute it many times. Be suspicious of applications generating dynamic SQL.

         3. Getting Database I/O Wrong

Many sites lay out their databases poorly over the available disks. Other sites specify the number of disks incorrectly, because they configure disks by disk space and not I/O bandwidth.

         4. Redo Log Setup Problems

Many sites run with too few redo logs that are too small. Small redo logs cause system checkpoints to continuously put a high load on the buffer cache and I/O system. If there are too few redo logs, then the archive cannot keep up, and the database will wait for the archive process to catch up.

         5. Serialization of data blocks in the buffer cache due to lack of free lists, free list groups, transaction slots (INITRANS), or shortage of rollback segments.

This is particularly common on INSERT-heavy applications, in applications that have raised the block size to 8K or 16K, or in applications with large numbers of active users and few rollback segments.

         6. Long Full Table Scans

Long full table scans for high-volume or interactive online operations could indicate poor transaction design, missing indexes, or poor SQL optimization. Long table scans, by nature, are I/O intensive and unscalable.

         7. In Disk Sorting

In disk sorts for online operations could indicate poor transaction design, missing indexes, or poor SQL optimization. Disk sorts, by nature, are I/O-intensive and unscalable.

         8. High Amounts of Recursive (SYS) SQL

Large amounts of recursive SQL executed by SYS could indicate space management activities, such as extent allocations, taking place. This is unscalable and impacts user response time. Recursive SQL executed under another user ID is probably SQL and PL/SQL, and this is not a problem.

         9. Schema Errors and Optimizer Problems

In many cases, an application uses too many resources because the schema owning the tables has not been successfully migrated from the development environment or from an older implementation. Examples of this are missing indexes or incorrect statistics. These errors can lead to sub-optimal execution plans and poor interactive user performance. When migrating applications of known performance, export the schema statistics to maintain plan stability using the DBMS_STATS package.

Likewise, optimizer parameters set in the initialization parameter file can override proven optimal execution plans. For these reasons, schemas, schema statistics, and optimizer settings should be managed together as a group to ensure consistency of performance.

        10. Use of Nonstandard Initialization Parameters

These might have been implemented based on poor advice or incorrect assumptions. In particular, parameters associated with SPIN_COUNT on latches and undocumented optimizer features can cause a great deal of problems that can require considerable investigation.

Oracle buying Sun will change the IT industry

1) MySQL is dead. Long live MySQL. Oracle doesn't have much to say about MySQL. Why should they? They're going to quietly kill the open-source DBMS as fast as possible.

Unfortunately for Oracle, it's too late. MySQL, under Sun's mismanagement, had already forked. MySQL founder, Michael 'Monty' Widenius left Sun and started his own community branch of MySQL, MariaDB. His purpose? "To provide a community developed, stable, and always Free branch of MySQL that is, on the user level, compatible with the main version."

That's one of the things that Ellison, and Microsoft for that matter, don't get. You can't kill open-source projects. Companies come and go, but popular open-source programs like MySQL just keep rolling on.

2) Solaris/OpenSolaris. Oracle is making sounds like it wants to do something with Solaris. Just don't ask me what. Solaris has been declining for years. Oracle uses Linux internally, and it even has its own rip-off of RHEL (Red Hat Enterprise Linux), Oracle Unbreakable Linux.

Ellison can talk all he wants about Sun's Solaris operating system being "by far the best Unix technology," but so what? Unix is dying. Linux has been eating away at the Unix market for more than a decade. Ellison's support of Unix makes a good sound-bite, but as a business move it makes no sense. I predict death by neglect for Solaris.

3) Java. Java has value, but Sun's done a poor job over the years of turning that value into money. Oracle, which uses Java in many of its applications, can certainly put Java to good work in supporting its own products. My question is, "What is Oracle going to do with the Java Community and vice-versa?"

I know some things will happen. NetBeans, for example, is history. Oracle is a big-time Eclipse supporter. As for the rest? I honestly don't know what Oracle will do with the JCP (Java Community Process). If they're smart, they'll get everyone together as soon as possible to spell out their future plans for Java. If Oracle doesn't, they'll have Java developers running, not walking, away from the Sun/Oracle Java as fast as they can.

4) SPARC. Oracle can talk all it wants about taking a step back to the past where companies sell hardware and software bundles, but I don't see it. Fujitsu will continue to make SPARC boxes for that dwindling market, and I expect to see Sun's x86-server based business getting either spun out as an independent company or sold to Dell or HP. I just can't see Oracle in the hardware business.

5) Sun's other open-source programs. I have a bad, bad feeling that Oracle is going to let popular and powerful open-source projects like OpenOffice and VirtualBox wither on the vine. Oracle is willing to spend money on open-source projects that it uses. For example, Oracle is a top contributor to ldLinux. But, I don't see these, or Sun's other open-source projects, contributing to Oracle's bottom-line, so I don't see them getting much support.

Over the years, Sun has contributed, albeit reluctantly at times, many great advances in operating systems and open-source software. With this acquisition, those days are done. Good-bye Sun, it was nice to have known you.
Courtesy:ComputerWorld

What is difference between oracle and sqlserver ?

Oracle:

1. Oracle runs on many platforms

2. Oracle includes IFS (Internet File System), Java integration

3. Oracle requires client install and setup (Not difficult, but very UNIX-like for Windows users)

4. Oracle is well rich with Index Options.

5. Oracle provides Materialized Views for performance improvements of Stored Data with multiple Tables.

6. Failover support in SQL is much, much easier

7.Oracle provides password complexity enforcement rule..

8.Connect with one Schema and can work with other schema.

SQl Server:

1.All DDL operations that are currently running on tables belong to database "Snapshot Isolation". "Snapshot Isolation" queries are prohibited.

2.SQL requires a complex setup of ROLLBACK Segments and Transaction Level use on it.

3.SQL has just BTREE Index while compare to Oracle.

4.SQL has limitations using Materialized Views.

5.SQL Analysis Services is included (A very powerful OLAP server)

6.SQL is ANSI-SQL '92 compliant, making it easier to convert to another ANSI compliant database, theoretically anyway (truth is every database has proprietary extensions). Oracle is generally more proprietary and their main goal is to keep their customers locked-in.