Differences between the COMMIT and ROLLBACK commands

 
COMMIT and ROLLBACK commands is significant because committing is always faster than rolling back.
What happens when a change is made to data in a table?Redo log entries are written, storing log entries for data that is about to be changed.
The database is changed, modifying the current value of data in tables.
Rollback entries are created, storing a list of rollback entries to be applied in case a transaction is rolled back.
What happens when a COMMIT command is executed?
Rollback entries are deleted. Changes are already made. Nothing else is required.
What happens when a ROLLBACK command is executed?
Rollback entries are written to the redo logs, namely “undo” redo log entries.
Rollback is applied to the database undoing previously made changes.
Rollback entries are deleted.

JOINS IN ORACLE

Cross-join or Cartesian product.
Merges all data selected from both tables into a single result set.
Inner join.
Combines rows from both tables using matching column
names and column values. The result set includes only rows that
match.
Outer join.
Selects rows from both tables as with an inner join but
including rows from one or both tables that do not have matching
rows in the other table. Missing values are replaced with null values.
Left outer join.
All rows from the left table plus all matching rows from the right table. Column values from the right table are
replaced with null values when the matching right-side row does not exist in the left-side table.
Right outer join.
All rows from the table on the right plus matching rows from the left table, the opposite of the left outer join.
Full outer join.
All rows from both tables, with null values replacing missing values.
Self-join.
This joins a table to itself.
Equi-joins, anti-joins, and range joins.An equi-join combines table data based on equality (=), an anti-join matches data based on inequality (!=, <> or NOT), and a range join compares data using a range of values (<, > or BETWEEN).
Mutable and complex joins.
A mutable join is a join of more than two tables. A complex join is a mutable join with added filtering.

Oracle Database Physical Architecture

Tablespaces are logical structural overlays containing one or more datafiles. A
schema (owner or Oracle user) is also a logical overlay structure. A schema can be spread across multiple table spaces and contains segments or  Database objects such as rollback segments,temporary segments or tables, and indexes.Segments are physical or logical subdivisions within schemas and can be accessed across different schemas. Segments are physically broken up into extents. An extent is a number of blocks added to a segment (datafile) when an object requires more space.

 

To view database users

To view database users,account_status  & other details in Oracle 10g database.
Syntax:
select * from dba_users;
 

To view data files path and details in Oracle 10g database

To view data files path & details in Oracle 10g database
Syntax:
SELECT * FROM DBA_DATA_FILES

different between procedure and function

 
What is difference between a PROCEDURE & FUNCTION ?
A FUNCTION is always returns a value using the return statement.
        A  PROCEDURE   may  return one or more values through parameters or may not return at all.
1. Function is mainly used in the case where it must return a value. Where as a procedure may or may not return a value or may return more than one value using the OUT parameter.
В
2. Function can be called from SQL statements where as procedure can not be called from the sql statements
3. Functions are normally used for computations where as procedures are normally used for executing business logic.
4. You can have DML (insert,update, delete) statements in a function. But, you cannot call such a function in a SQL query.
5.В Function returns 1 value only. Procedure can return multiple values (max 1024).
6.Stored Procedure: supports deferred name resolution. Example while writing a stored procedure that uses table named tabl1 and tabl2 etc..but actually not exists in database is allowed only in during creation but runtime throws error Function wont support deferred name resolution.
7.Stored procedure returns always integer value by default zero. where as function return type could be scalar or table or table values
8. Stored procedure is precompiled execution plan where as functions are not.
9.A procedure may modify an object where a function can only return a value The RETURN statement immediately completes the execution of a subprogram and returns control to the caller.

difference between truncate and delete

DELETE
-------
The DELETE command is used to remove rows from a table. A WHERE clause can be used to only remove some rows. If no WHERE condition is specified, all rows will be removed. After performing a DELETE operation you need to COMMIT or ROLLBACK the transaction to make the change permanent or to undo it. Note that this operation will cause all DELETE triggers on the table to fire.
TRUNCATE
---------
TRUNCATE removes all rows from a table. The operation cannot be rolled back and no triggers will be fired. As such, TRUCATE is faster and doesn't use as much undo space as a DELETE
DROP
----
The DROP command removes a table from the database. All the tables' rows, indexes and privileges will also be removed. No DML triggers will be fired. The operation cannot be rolled back.
DROP and TRUNCATE are DDL commands, whereas DELETE is a DML command. Therefore DELETE operations can be rolled back (undone), while DROP and TRUNCATE operations cannot be rolled back
Difference between TRUNCATE and DELETE commands
==================================================
1>TRUNCATE is a DDL command whereas DELETE is a DML command.
2>TRUNCATE is much faster than DELETE.
Reason:When you type DELETE.all the data get copied into the Rollback Tablespace first.then delete operation get performed.Thatswhy when you type ROLLBACK after deleting a table ,you can get back the data(The system get it for you from the Rollback Tablespace).All this process take time.But when you type TRUNCATE,it removes data directly without copying it into the Rollback Tablespace.Thatswhy TRUNCATE is faster.Once you Truncate you cann't get back the data.
3>You cann't rollback in TRUNCATE but in DELETE you can rollback.TRUNCATE removes the record permanently.
4>In case of TRUNCATE ,Trigger doesn't get fired.But in DML commands like DELETE .Trigger get fired.
5>You cann't use conditions(WHERE clause) in TRUNCATE.But in DELETE you can write conditions using WHERE clause
about truncate and drop
========================
Drop command will delete the entire row also the structure.But truncate will delete the contenets only not the strucure, so no need to give specifications for another table creation.
DELETE,DROP,TRUNCATE
=====================
DELETE
-------
Delete is the command that only remove the data from the table. It is DML statement. Deleted data can be rollback. By using this we can delete whole data from the table(if use without where clause).If ew want to remove only selected data then we should specify condition in the where clause
SQL>delete from employee;(this command will remove all the data from table)
SQL>delete from employee where employee_name='JOHN';(This command will remove only that row from employee table where employee_name is JOHN');
DROP:
------
Drop command remove the table from data dictionary. This is the DDL statement. We can not recover the table before Oracle 10g. But Oracle 10g provide the command to recover it by using the command (FLASHBACK)
TRUNCATE:
---------
This is the DML command. This command delete the data from table. But there is one difference from ordinary delete command. Truncate command drop the storage held by this table. Drop storage can be use by this table again or some other table. This is the faster command because it directly drop the storage

difference between translate and replace

Both Replace and Translate are single row functions in Oracle 9i.
The Replace Function replaces single character with multiple characters.
But in Translate Function replaces sinlge character with sinlge character only.
--
replace chages the record in database.
but translate won't change it the database content is not changed
E.g . 1
--------
For Example:
SQL> SELECT translate('So What' 'o' 'ay')
2 FROM dual;
TRANSLATE
-------
Sa What
SQL> SELECT REPLACE('So What' 'o' 'ay')
2 FROM dual;
REPLACE
--------
Say What
E.g . 2
--------
SQL> select replace('missisippi' 'is' '12') replace translate('missisippi' 'is' '12') translate from dual;
REPLACE TRANSLATE
---------- ----------
m12s12ippi m122121pp1

How does one put a database into ARCHIVELOG mode?

The main reason for running in archivelog mode is that one can provide 24-hour availability and guarantee complete data recoverability. It is also necessary to enable ARCHIVELOG mode before one can start to use on-line database backups.
Issue the following commands to put a database into ARCHIVELOG mode:
SQL> CONNECT sys AS SYSDBASQL> STARTUP MOUNT EXCLUSIVE;SQL> ALTER DATABASE ARCHIVELOG;SQL> ARCHIVE LOG START;SQL> ALTER DATABASE OPEN;

How does one do off-line database backups?

Shut down the database from sqlplus or server manager. Backup all files to secondary storage (eg. tapes). Ensure that you backup all data files, all control files and all log files. When completed, restart your database.
Do the following queries to get a list of all files that needs to be backed up:
SYNTAX:
select name from sys.v_$datafile;
select member from sys.v_$logfile;
select name from sys.v_$controlfile;

Oracle 10G Installation in Windows

An Oracle database is a collection of data in one or more files. The database contains physical and logical structures.The below PDF explains how to install Oracle 10G in windows XP.I linked the PDF,given below.
You can download from the below link
link:
http://www.zumodrive.com/share/9yy5MjE0Yj