Creating a new database #Oracle # Database Configuration Management

Click Next

You can select the Database name , SID and other options from the above.

Connect to the database using sqldeveloper

SQL basics for beginners / Oracle Database / Examples / Practical Session / With Queries

SQL is a standard language for accessing databases.

SQL basics - ( SELECT ) - (For absolute beginners) - Tutorial 1

SQL basics - ( SELECT ) - (For absolute beginners) - Tutorial 2 

SQL basics - ( SELECT - DISTINCT ) - (For absolute beginners) - Tutorial 3

SQL basics - ( SELECT - WHERE) - (For absolute beginners) - Tutorial 4

SQL basics - ( SELECT - WHERE - COMPARISON) - (For absolute beginners) - Tutorial 5

SQL basics - ( SELECT - WHERE - BETWEEN | IN | LIKE) - (For absolute beginners) - Tutorial 6

SQL basics - ( SELECT - WHERE - NULL| AND/OR| NOT) - (For absolute beginners) - Tutorial 7

SQL basics - (How to create a table , Primary Key, Foreign key , How to insert data to a table) - (For absolute beginners) - Tutorial 8

More ...

Database Transactions / (Oracle Database ) / COMMIT / ROLLBACK 

How to Rename a Table / Oracle Database / ALTER

Triggers / Oracle / Oracle SQL Developer

In this tutorial we are going to create a simple trigger to update the count when a new row is added.

(Please note that this tutorial is just to demonstrate a simple  trigger,)

Create a table called EMP1. Refer this for more details about creating tables and inserting data

And create EMPCOUNT1 table too . 

Create the trigger as depicted in the image.

select * from EMP1

insert into emp1 values ('7','sad','3000')

select * from empcount1

Above commands can be used to demonstrate the Trigger. Once a new row is inserted the count is incremented by 1. (Refer the trigger)

create or replace TRIGGER "COUNTTEST" BEFORE INSERT ON EMP1
FOR EACH ROW
BEGIN
 update empcount1
 set EMP_COUNT = EMP_COUNT +1;
END;

Database Transactions / (Oracle Database ) / COMMIT / ROLLBACK

A transaction is a logical unit of work that contains one or more SQL statements. A transaction is an atomic unit. The effects of all the SQL statements in a transaction can be either all committed (applied to the database) or all rolled back (undone from the database).

A transaction begins with the first executable SQL statement. A transaction ends when it is committed or rolled back, either explicitly with a COMMIT orROLLBACK statement or implicitly when a DDL statement is issued.

Open sql developer and enter the following 

CREATE TABLE emp22

(

  emp_id NUMBER(2) CONSTRAINT emp_col1_pk22 PRIMARY KEY,

  emp_name  VARCHAR2(20),

  emp_salary NUMBER(5)

);

Next input data to the table

INSERT INTO "HR"."EMP22" VALUES('1','Achala','1500');

INSERT INTO "HR"."EMP22" VALUES('2','ach','1000');

INSERT INTO "HR"."EMP22" VALUES('3','Tendulkar','20000');

Open another sql developer and enter the following

select * from emp22;

The result will be as follows

It shows that the transaction is not committed. Now lets commit the transaction ( from sql developer first window). Enter the following.

COMMIT;

Now go to the sql developer window 2 and use the following query

Open sql developer 1 and enter following

INSERT INTO "HR"."EMP22" VALUES('4','Jayasuriya','2000');

Lets do a ROLLBACK now.

If you use the following query before and after the ROLLBACK you will notice the difference

select * from emp22;

All Oracle transactions obey the basic properties of a database transaction, known as ACID properties. ACID is an acronym for the following:

• Atomicity
  All tasks of a transaction are performed or none of them are. There are no partial transactions. For example, if a transaction starts updating 100 rows, but the system fails after 20 updates, then the database rolls back the changes to these 20 rows.
• Consistency
  The transaction takes the database from one consistent state to another consistent state. For example, in a banking transaction that debits a savings account and credits a checking account, a failure must not cause the database to credit only one account, which would lead to inconsistent data.
• Isolation
  The effect of a transaction is not visible to other transactions until the transaction is committed. For example, one user updating the hr.employeestable does not see the uncommitted changes to employees made concurrently by another user. Thus, it appears to users as if transactions are executing serially.
• Durability
  Changes made by committed transactions are permanent. After a transaction completes, the database ensures through its recovery mechanisms that changes from the transaction are not lost.

Reference : http://docs.oracle.com/cd/B19306_01/server.102/b14220/transact.htm

How to unlock a user in Oracle

Following command will unlock the user