Thread: Geeks - Tip of the Day

Database Shutdown -I

You must be connected as SYSDBA or SYSOPER to shutdown a database.

Shutdown Modes

I.Shutdown Normal
1.No New Connections are allowed
2.The server waits for all users to disconnect before completing the shutdown.
3.Database and redo buffers are written to disk.
4.The SGA memory allocation is released and background processes are terminated.
5.The database is closed and dismounted.

II.Shutdown Transactional
1.No new connections are allowed
2.No connected client can start a new transaction
3.Clients are disconnected as soon as the current transaction ends.
4.Showdown proceeds when all the transactions are finished.

Database Shutdown -II


III.Shutdown Immediate
1.No New connections are allowed.
2.Connected clients are disconnected and Sql Statements in process are not completed.
3.Oracle rollback active transactions.
4.Oracle closes and dismounts the database

IV.Shutdown abort
1.Current Sql statements are immediately terminated.
2.Users are disconnected.
3.Database and redo buffers are not written to disk.
4.Uncommitted transactions are not rolled back.
5.The instance is terminated without closing files.
6.The database is not closed and dismounted.
7.Database recovery by SMON must occur on the next startup.

Choosing Between Having and Where

Where puts condition on the table rows.
Having puts condition on grouped results.

The processing order is as follows.
1. Select rows with WHERE
2. Divide rows into sets with GROUP BY
3. Calculate accurate values for each group.
4. Eliminate unwanted group result rows with having.

Any rows you can remove with where rather than having make your query more efficient.

Ex:- Wrong Statement

Code:

SQL> SELECT job, count(*)
  2  FROM   emp
  3  GROUP BY job
  4  HAVING job<>'SALESMAN';

JOB        COUNT(*)
--------- ---------
ANALYST           2
CLERK             3
MANAGER           4
PRESIDENT         1

Better Statement

Code:

SQL> SELECT job, count(*) 
  2  FROM   emp
  3  WHERE  job<>'SALESMAN'
  4  GROUP BY job;

JOB        COUNT(*)
--------- ---------
ANALYST           2
CLERK             3
MANAGER           4
PRESIDENT         1

If you use DROP TABLE or DROP VIEW to remove triggering tables or views from the database, all triggers on those tables or views are also dropped.

The table on which you create a trigger must exist in the current database.

Firing sequence of Database trigger on a single row

Before statement trigger
Before row trigger
After row trigger
After statement trigger

Firing sequence of Database triggers on multiple rows

Before statement trigger
Before row trigger for first row
After row trigger for first row
Before row trigger for second row
After row trigger for second row
.........
After statement trigger

In triggers , intregrity constraints are applied before executing after row triggers.

Example

Code:

 
create table test_11 (num number(10)); 

-- To create primary key constraint 
alter table test_11 add constraint test_11_num_pk primary key (num) 

create or replace trigger tr_before_tab_inst 
before insert on test_11 
begin 
dbms_output.put_line('before insert on table'); 
end; 

create or replace trigger tr_after_tab_inst 
after insert on test_11 
begin 
dbms_output.put_line('after insert on table'); 
end; 

create or replace trigger tr_before_row_inst 
before insert on test_11 
for each row 
begin 
dbms_output.put_line('before insert on row'); 
end; 

create or replace trigger tr_after_row_inst 
after insert on test_11 
for each row 
begin 
dbms_output.put_line('after insert on row'); 
end; 

--for the first statement all the four triggers will be fired insert into test_11 values(1); 
before insert on table 
before insert on row 
after insert on row 
after insert on table 

--for the second statement only before statement and before row triggers were fired. Error message is raised before firing after row trigger 
-- I have executed this in toad 

insert into test_11 values(1); 
before insert on table 
before insert on row 

After firing first two triggers it will display error message 
ora-00001: unique constraint (cmsjan.test_11_num_pk) violated

Join Types - I

Join is a process of combining data from two or more tables using matching columns


Equi Join or Inner Join or Simple Join
It used to combine data from two or more tables using equivalent operator (=)

Code:

Ex:- SELECT a.empno, a.ename, a.job, a.sal, a.deptno, b.dname
     FROM   emp a, dept b
     WHERE  a.deptno = b.deptno;
ANSI Syntax
Ex:- SELECT a.empno, a.ename, a.sal , a.deptno, b.dname
     FROM   emp a INNER JOIN dept b
     ON     a.deptno  = b.deptno;

Non Equi Join or Theta Join
It uses relational operators other than = or between to match rows from different tables.

Code:

Ex:- SELECT a.ename, a.job, a.sal , b.grade 
     FROM   emp a, salgrade b
     WHERE  a.sal between b.losal and b.hisal;
ANSI SYNTAX       
       SELECT a.ename, a.job, a.sal , b.grade 
       FROM   emp a INNER JOIN salgrade b
       ON     a.sal BETWEEN  b.losal and b.hisal;

JOIN TYPES - II

Cartesian Join or Cross Join
It joins every row of one table with every row of another table. Cartesian join is obtained when you give invalid join condition or omit join condition. To avoid Cartesian join a should have at least N-1 join condition.

Code:

Ex:- SELECT a.ename,a.job,a.sal , b.dname 
     FROM   emp a , dept b;
ANSI SYNTAX        
        SELECT ename, job, sal, dname 
        FROM   emp CROSS JOIN dept;

Natural Join
Natural join combines data from two tables based upon all common columns in both the table.

Code:

Ex:- SELECT ename, job, sal, dname  
     FROM   emp  NATURAL JOIN dept;
     -  Natural joins between two tables without at least one
        common column results in Cartesian product.
     -  We can’t join more than two tables using this method.
     -  It may cause problems if columns are added or renamed

Self Join
Join the table with itself is called self join.

Code:

 
Ex:- SELECT a.ename, a.job, a.sal, b.ename Manager 
     FROM   emp a, emp b
     WHERE  a.mgr  = b.empno;
 ANSI SYNTAX
        SELECT a.ename, a.job, a.sal, b.ename Manager 
        FROM   emp a JOIN emp b
        ON     a.mgr = b.empno;

Join Types - III

Outer Join
outer join extends the results of inner join. Along with inner join results it will also return non matched rows from the table with outer join operator(+). Missing values are filled with null.

Left Outer Join
It returns row that meets the join condition + rest of the rows from left table

Code:

 
Ex:- select b.dname, a.ename, a.job, a.sal 
     from   emp a , dept b 
     where  b.deptno = a.deptno(+); 
Ansi Syntax 
     select b.dname, a.ename, a.job, a.sal 
     from   dept b left outer join emp a 
     on a.deptno = b.deptno;

Right Outer Join
It returns row that meets the join condition + rest of the records from table on the right side.

Code:

 
Ex:- select b.dname, a.ename, a.job, a.sal 
     from emp a , dept b 
     where a.deptno(+) = b.deptno; 
Ansi Syntax 

    select b.dname, a.ename, a.job, a.sal 
    from   emp a right outer join dept b 
    on     a.deptno = b.deptno;

Full Outer Join
It returns rows that meet the join codition + rest of the records from both the tables

Code:

 
Ansi Syntax 
select b.dname, a.ename, a.job, a.sal 
from   emp a full outer join dept b 
on     a.deptno = b.deptno;

JOIN TYPES - IV

Anti Join
It returns rows from first table for which there are no corresponding rows in the second table. Anti joins are written using NOT EXISTS or NOT IN.

Code:

Ex:-   SELECT a.dname 
       FROM   dept a
       WHERE  NOT EXISTS ( SELECT 1 FROM emp b WHERE b.deptno = a.deptno);
Which is same as 
        SELECT dname
        FROM   dept 
        MINUS
        SELECT b.dname
        FROM   emp a, dept b
        WHERE  a.deptno = b.deptno;

Semi Join
It return rows from the first table for which one or more joins are found in the second table. It is written using IN or EXISTS.

Code:

Ex:- SELECT a.dname 
     FROM   dept a
     WHERE EXISTS ( SELECT 1 FROM emp b WHERE b.deptno = a.deptno);

Triggers cannot be created on SYS-owned objects.

A hint is code embedded into a SQL statement suggesting to Oracle how it should be processed.The comment containing hints must follow the SELECT, UPDATE, INSERT, MERGE, or DELETE keyword and only one hint is allowed in a statement block.

Roles predefined by Oracle are
CONNECT
RESOURCE
DBA
EXP_FULL_DATABASE
IMP_FULL_DATABASE

To know default tablespace, temporaray tablespace associated with an user

select
temporary_tablespace,
default_tablespace
from dba_users
where username='username';

What is a schema?

A schema is collection of database objects of user.
It has same name as that of user.
It is owned by database user.
Objects of same schema can be in different tablespaces and a tablespace can contain objects from different schema.

PRODUCT_COMPONENT_VERSION VIEW

This view shows you at a glance the version numbers of all the major components of your Oracle database.

Code:

SQL> SELECT * FROM PRODUCT_COMPONENT_VERSION;
PRODUCT                                  VERSION              STATUS
---------------------------------------- -------------------- --------------
NLSRTL                                   9.2.0.6.0            Production
Oracle9i Enterprise Edition              9.2.0.6.0            Production
PL/SQL                                   9.2.0.6.0            Production
TNS for 32-bit Windows:                  9.2.0.6.0            Production

DIFFERENCE BETWEEN STATIC AND DYNAMIC VIEWS

Static views don’t change continuously while the database operates. All views starts with DBA_, ALL_ and USER_ are static views.
Ex:- DBA_TABLES, USER_OBJECTS, ALL_SOURCE


Dynamic view are updated continuously while the database is running.
All dynamic views have prefix V_$.
Oracle creates synonyms for these dynamic views whose prefix is simply V$.
EX:- V$CONTROLFILE, V$DATABASE, V$INSTANCE
( These three are synonyms for V_$DATABASE, V_$INSTANCE, V_$CONTROLFILE)