COMMIT [WORK]: Makes changes permanent

Ex:
INSERT INTO EMPLOYEE  (Empl_ID,  FirstName,  LastName, Department) VALUES (‘SA00120’,’Max‘, ‘Johnson’, ‘Sales’);
COMMIT;
INSERT INTO EMPL_Salaries (Empl_ID, Amount , Vacation ) VALUES (‘SA00120’,1500,5);
COMMIT WORK;

COMMIT COMMENT [Comment_Text]; Add a text to the commit instruction. The maximum size for Comment_Text=255. Oracle stores COMMIT COMMENT into data dictionary.

Ex:
INSERT INTO EMPLOYEE  (Empl_ID,  FirstName,  LastName, Department) VALUES (‘HR0457’,’Michelle‘, ‘LEE’, HR’);
COMMIT COMMENT 'New employee in the house!';

COMMIT WRITE FORCE [Text], [Number]; This command Manually force changes to be permanent. It is mostly used in case of doubtful distributed transactions.

Ex:
INSERT INTO EMPLOYEE  (Empl_ID,  FirstName,  LastName, Department) VALUES (‘ACT45210’,’Vincent‘, ‘DeAndre’, ‘Accounting’);
COMMIT WRITE FORCE;

COMMIT WRITE BATCH [WAIT | NOWAIT]; This commit statement is perform while redo instructions are written in the buffer redo to be batch move into the online redo log.

Ex:
INSERT INTO EMPLOYEE  (Empl_ID,  FirstName,  LastName, Department) VALUES (‘SA45010’,Jackie‘, ‘Fullton’, ‘Sales’);
COMMIT WRITE BATCH WAIT;

COMMIT WRITE [IMMEDIATE | WAIT | NOWAIT]; Making a commit with these options defines the way Oracle log writer process will proceed towrite the redo instruction in the redo log.

COMMIT WRITE IMMEDIATE; Makes a commit and forces Oracle Log writer process to write the redo instruction in the online redo log immediately.

Ex:
INSERT INTO EMPLOYEE  (Empl_ID,  FirstName,  LastName, Department) VALUES (‘SA004520’,’Denise‘, ‘Brown’, ‘Sales’);
COMMIT WRITE IMMEDIATE;

COMMIT WRITE NOWAIT: When this command is run, Commit statement will not wait for the redo instruction to be written into the redo log before it takes effects.

Ex:
INSERT INTO EMPLOYEE  (Empl_ID,  FirstName,  LastName, Department) VALUES (‘SA450120’,’Eunice‘, ‘Hernandez’, ‘Sales’);
COMMIT WRITE NOWAIT;

COMMIT WRITE WAIT: This command forces Commit instruction to return the handle only when the corresponding redo statement is permanently written in the redo lo.
INSERT INTO EMPLOYEE  (Empl_ID,  FirstName,  LastName, Department) VALUES (‘SA451450’,’Gerard‘, ‘Dufour’, ‘Sales’);
COMMIT WRITE WAIT;

The GRANT command is use to assign Object Privileges to users or group of users.

Syntax:
GRANT [privilege|privilege list|ALL|EXECUTE] ON [object] TO [schema];

Specify just one privilege name to Grant A Single Privilege
If multiple comma separated privilege are specify as a list the Grant will be made for Multiple Privileges.
Use the keyword ALL to Grant All Privileges
Use the keyword EXECUTE to Grant Execute privilege to a schema.

Example:
GRANT INSERT, UPDATE ON Empl TO HR_Clerk;
GRANT ALL ON Payroll TO HR_Manager;
GRANT EXECUTE ON fCalculateTaxes TO HR_Assc;

Grant Column Privileges

Syntax:
GRANT [privilege] ([column]) ON [table] TO [schema];

Example:
GRANT UPDATE (Salary, Taxes)ON Payroll TO HR_Assc;

The REVOKE command is use to retrieve Object Privileges from  users or group of users.

Syntax:
REVOKE [privilege|privilege list|ALL|EXECUTE] ON [object] FROM [schema];

Specify just one privilege name to Revoke A Single Privilege
If multiple comma separated privilege are specify as a list the Revoke will be made for Multiple Privileges.
Use the keyword ALL to Revoke All Privileges
Use the keyword EXECUTE to Revoke Execute privilege from a schema.

Example:
REVOKE INSERT, UPDATE ON Empl FROM HR_Clerk;
REVOKE ALL ON Payroll FROM HR_Manager;
REVOKE EXECUTE ON fCalculateTaxes FROM HR_Assc;

Revoke Column Privileges

Syntax:
REVOKE [privilege] ([column]) ON [table] FROM [schema];

Example:
REVOKE UPDATE (Salary, Taxes) ON Payroll FROM HR_Assc;

ROLLBACK [WORK]: Ends the current transaction and will undo any changes made since the transaction began. If used with save point it will undo part of a transaction.

Ex:
INSERT INTO EMPLOYEE  (Empl_ID,  FirstName,  LastName, Department) VALUES (‘SA457580’,’Amanda‘, ‘Suarez’, ‘Sales’);
ROLLBACK;

SAVEPOINT [Point_Id]: Marks the current point in the processing of a transaction. For Rollback to work with savepoint use the complete word ‘ROLLBACK’, just ‘ROLL’ will fail.

BEGIN
INSERT INTO EMPLOYEE  (Empl_ID,  FirstName,  LastName, Department) VALUES (‘HR12054’,’Jeremie‘, ‘Yates’, ‘HR’);
  SAVEPOINT Benefits;

 INSERT INTO EMPL_Salaries (Empl_ID, Amount , Vacation ) VALUES (‘HR12054’,1750,3);
 INSERT INTO EMPL_Parking (Empl_ID, Days , Amount ) VALUES (‘HR12054’,5,600);
 INSERT INTO EMPL_Lunch (Empl_ID, Amount ) VALUES (‘HR12054’,450);

COMMIT;
EXCEPTION
  WHEN OTHERS THEN
    ROLLBACK TO SAVEPOINT Benefits;
    COMMIT;
END RegisterEmployee;