bhaskarrampalli@kamalraj: 2011

DBA Responsibilities

The job of the DBA seems to be everything that everyone else either doesn't want to do, or doesn't have the ability to do. DBAs get the enviable task of figuring out all of the things no one else can figure out. More seriously though, here is a list of typical DBA responsibilities:

Installation, configuration and upgrading of Oracle server software and related products
Evaluate Oracle features and Oracle related products
Establish and maintain sound backup and recovery policies and procedures
Take care of the Database design and implementation
Implement and maintain database security (create and maintain users and roles, assign privileges)
Perform database tuning and performance monitoring
Perform application tuning and performance monitoring
Setup and maintain documentation and standards
Plan growth and changes (capacity planning)
Work as part of a team and provide 7x24 support when required
Perform general technical trouble shooting and give consultation to development teams
Interface with Oracle Corporation for technical support.
Patch Management and Version Control

DBA Skills Required

Good understanding of the Oracle database, related utilities and tools
A good understanding of the underlying operating system
A good knowledge of the physical database design
Ability to perform both Oracle and operating system performance tuning and monitoring
Knowledge of ALL Oracle backup and recovery scenarios
A good knowledge of Oracle security management
A good knowledge of how Oracle acquires and manages resources
A good knowledge Oracle data integrity
Sound knowledge of the implemented application systems
Experience in code migration, database change management and data management through the various stages of the development life cycle
A sound knowledge of both database and system performance tuning
A DBA should have sound communication skills with management, development teams, vendors and systems administrators
Provide a strategic database direction for the organisation
A DBA should have the ability to handle multiple projects and deadlines
A DBA should possess a sound understanding of the business

Difference between SQL & PL/SQL:

What is Oracle?

I cover this answer in the entry, Learn Oracle - What is Oracle?. I also cover SQL and PL/SQL very briefly in that article. I'll try to be a little more explicit in this entry.

What is SQL?

SQL is the Structured Query Language. Actually, I think I covered SQL pretty nicely in the article Sudhat commented on, Intro to basic SQL.

I'll just add that SQL is composed of DML and DDL. DML are the keywords you use to access and manipulate data, hence the name Data Manipulation Language. DDL are the keywords you use to create objects such as views, tables and procedures, hence the name Data Definition Language. Examples of DML are SELECT, UPDATE, INSERT, MERGE, DELETE, etc. Examples of DDL are CREATE TABLE, ALTER VIEW, CREATE OR REPLACE PROCEDURE, etc.

Example DDL:

Example DML:

What are SQL Commands?

SQL commands are commands that aren't actually a part of the SQL standard but are supported by the tools that support SQL. For example, SQL*Plus has been around a long time and many tools that allow scripting also allow SQL*Plus commands. When someone asks me about a SQL command, I first clarify if the mean a SQL keyword or a SQL*Plus (or some other tool) command.

SQL commands are usually meant to help format output: BREAK, BTITLE, COLUMN, PRINT or they are meant to create or store data or scripts: COMPUTE, DEFINE, STORE, SAVE. There are also commands that interact with the database: SHUTDOWN, CONNECT, COPY. And there is at least one that interacts with data: XQUERY.

So when you hear someone refer to a SQL command, first ask if they really mean SQL or if they mean the SQL tool they are using.

What is PL/SQL?

The quick answer is from the PL/SQL User Guide:

PL/SQL, Oracle's procedural extension of SQL, is an advanced fourth-generation programming language (4GL). It offers software-engineering features such as data encapsulation, overloading, collection types, exceptions, and information hiding. PL/SQL also supports rapid prototyping and development through tight integration with SQL and the Oracle database.

But what does that mean? The key here are the words: procedural extension of SQL. PL/SQL is a procedural language like C++, Java, ADA, etc. If has variables and variable declarations, conditional controls like IF and CASE. It has looping structures such as LOOP, FOR LOOP and the WHILE LOOP. PL/SQL uses SQL to use, manipulate and save data to the database.

If I wanted to create my own, very short, definition of PL/SQL it would be this: PL/SQL is the Oracle native programming language that provides database-centric application development. It can natively call static SQL and provides multiple methods of calling dynamic SQL.

Example PL/SQL:

And now the answer to the main question: what is the difference between SQL and PL/SQL? SQL is a data oriented language for selecting and manipulating sets of data. PL/SQL is a procedural language to create applications. You don't normally have a "SQL application". You normally have an application that uses SQL and a relational database on the back-end. PL/SQL can be the application language just like Java or PHP can. SQL may be the source of data for your screens, web pages and reports. PL/SQL might be the language you use to build, format and display those screens, web pages and reports.

Think of it like this: The code that makes your program function is PL/SQL. The code that manipulates the data is SQL DML. The code that creates stored database objects is SQL DDL. DDL compiles the code that is written in PL/SQL. PL/SQL may call SQL to perform data manipulation. The commands that format the output of a tool are not related to the SQL standard or to PL/SQL.

I hope that clears it up some! In the next few days, I am going to repost a couple of articles I wrote last year on basic SQL syntax. I already posted part 1 here (and that was the post that Sudhat was commenting to). I figure to complete the set, I'll post the other two and if they get a decent response, I may continue the series.

There are so many variants of SQL that it is hard sometimes to figure out what to use. Here is a short article that briefly explains the difference between SQL and PL/SQL. Enjoy!
What is SQL?
SQL (pronounced “sequal”) stands for Structured Query Language. Withe SQL, you can view data – called Data Definiton Language or DDL and manipulate data – called Data Manipulation Languate or DML. All of the above are just a fancy way to say that with SQL, the user can both view and alter records in the database. To help , here are a couple of queries:
DDL or View
SELECT * FROM employees
View all of the records in the employee table
DML or Manipulate
UPDATE employees SET employeefirstname = ‘John’ WHERE employeeid = 101
Find the record for employee ID 101 and change the first name to John
What is PL/SQL?
The official answer is from the PL/SQL User Guide:
PL/SQL, Oracle’s procedural extension of SQL, is an advanced fourth-generation programming language (4GL). It offers software-engineering features such as data encapsulation, overloading, collection types, exceptions, and information hiding. PL/SQL also supports rapid prototyping and development through tight integration with SQL and the Oracle database.
Huh? That is what I thought at the beginning. But at a high level, all this means is that it can do all of the things that regular SQL can do, but also, it is procedural and can be used like a programming language (C++, Java, etc.) For instance, you can use loops and If . . . Then statements in your PL/SQL statements (Programs).
Here is a definition of PL/SQL from Lewis Cunningham (an Oracle database expert):
“If I wanted to create my own, very short, definition of PL/SQL it would be this: PL/SQL is the Oracle native programming language that provides database-centric application development. It can natively call static SQL and provides multiple methods of calling dynamic SQL.
Mr. Cunningham also does a very good job of spelling out the differences between SQL and PL/SQL.
SQL is a data oriented language for selecting and manipulating sets of data. PL/SQL is a procedural language to create applications. You don’t normally have a “SQL application”. You normally have an application that uses SQL and a relational database on the back-end. PL/SQL can be the application language just like Java or PHP can. SQL may be the source of data for your screens, web pages and reports. PL/SQL might be the language you use to build, format and display those screens, web pages and reports.
Think of it like this: The code that makes your program function is PL/SQL. The code that manipulates the data is SQL DML. The code that creates stored database objects is SQL DDL. DDL compiles the code that is written in PL/SQL. PL/SQL may call SQL to perform data manipulation. The commands that format the output of a tool are not related to the SQL standard or to PL/SQL.
To muddy the waters a little more, while you cannot write an application in SQL, you can use a procedural language to automate parts. These are called Stored Procedures and here is a link to all you want to know about stored procedures on this site.

ORACLE ---------- * It is a software package, that is used to handle or to maintaine the databse. * It is a software to give the structure of database storage. * Oracle 7, Oralce 8, Oracle 8i, Oracle 9i, Oracle 11i are available in market. * It is a company called" Oracle " to produce this software.

SQL is an ANSI (American National Standards Institute) standard computer language for accessing and manipulating database systems. SQL statements are used to retrieve and update data in a database. SQL works with database programs like MS Access, DB2, Informix, MS SQL Server, Oracle, Sybase, etc.

Unfortunately, there are many different versions of the SQL language, but to be in compliance with the ANSI standard, they must support the same major keywords in a similar manner (such as SELECT, UPDATE, DELETE, INSERT, WHERE, and others).

PL/SQL is Oracle's Procedural Language extension to SQL. PL/SQL stands for Procedural Language/SQL. PL/SQL extends SQL by adding constructs found in procedural languages, resulting in a structural language that is more powerful than SQL. The basic unit in PL/SQL is a block. Typically, each block performs a logical action in he program. Oracle can invoke a PL/SQL program by placing it in the SQLPLUS.
PL/SQL's language syntax, structure and data types are similar to that of ADA. The PL/SQL language includes object oriented programming techniques such as encapsulation, function overloading, information hiding (all but inheritance). PL/SQL is commonly used to write data-centric programs to manipulate data in an Oracle database.

the best is ORACLE as it is platform dependant and can run in UNIX and windows. whereas SQL only can run in windows.

easiest? i would say that, if you know SQL, it would be easy to know Oracle as Oracle is just an expansion of SQL.

bhaskarrampalli@kamalraj

Thursday 1 December 2011

Top DBA Shell Scripts for Monitoring the Database

DBA Responsibilities

Wednesday 30 November 2011

Difference between SQL & PL/SQL

Thursday 17 November 2011

Recommended Books For Oracle DBA