Oracle SQL Commands: Part 4

13. ALTER USER Command

We generally we use the ALTER USER Command to create users in the database and once the users are created we need to do some changes, such as Password Change, which is one of the common changes we do and for this we use the ALTER USER Command.
 
Syntax

ALTER USER Username IDENTIFIED BY New_Password;


Example

ALTER USER Joseph IDENTIFIED BY JJJPh;


14. ALTER TRIGGER Command

In response to the specific event a trigger fires as a code block. ALTER TRIGGER changes the property of a trigger.

Syntax

ALTER TRIGGER trigger_name COMPILE;

ALTER TRIGGER trigger_name ENABLE;

ALTER TRIGGER trigger_name DISABLE;
 
Example

To Alter a Trigger first we create a table "STUDENTS" and then create a trigger as in the following:

Table
 

CREATE TABLE Product

(

  Prod_ID Number(10),

  Prod_Name Varchar2(20),

  Quantity Number(5),

  Price number(5,2),

  Create_date date,

  Created_by varchar2(10)

);


STUDENTS

 

Stu_ID Stu_Name RollNo Stream
101 Shreya 5 Commerce
102 Rohan 9 Science
103 Ronit 17 Science

  
Trigger

CREATE OR REPLACE TRIGGER Product_before_insert
BEFORE INSERT
ON Products
FOR EACH
ROW
DECLARE
username varchar2(15);
BEGIN
-- Find username of person performing INSERT into table

SELECT user INTO username
FROM dual;
  
-- Update create_date field to current system date
:new.create_date := systemdate;
  
-- Update created_by field to the username of the person performing the INSERT
:new.created_by := username;       
END;

Now, Alter the trigger Product_before_insert as in the following:

ALTER TRIGGER Product_before_insert DISABLE;
 
15. ALTER SEQUENCE Command

To change the behavior of an existing sequence the ALTER SEQUENCE COMMAND is used. It will alter the values of an existing Oracle sequence.

Syntax
 

ALTER SEQUENCE seq_name

increment by Value(i);


Example
 

ALTER USER Username IDENTIFIED BY New_Password;

ALTER SEQUENCE Master

Increment by 124

SELECT Master.nextval from dual;

ALTER SEQUENCE master

IMZCREMENT BY 1;
 
16. ALTER SESSION Command

ALTER SESSION Command is used to modify any parameter or condition that can affect the connection to our database. Also it is used when the database has multiple schema owners.

Syntax and Example

  • To change the Schema

    To add "SYSADM" to all your SQL statements, change the current schema setting to "SYSADM":

    ALTERSESSION SETCURRENT_SCHEMA =SYSADM;
     
  • To change NLS_DATE_FORMAT

    To set the date format instead of using the "TO_DATE" function in every SQL statement:

    ALTERSESSION SETNLS_DATE_FORMAT ='YYYY-MM-DD';

17. DROP TABLE Command

To remove the objects from the database we use the DROP Command. Here we are talking about the DROP TABLE Command, while dropping a table all the rows, indexes and privileges will also be removed. It may also be used to remove a database link.

Syntax

DROP TABLE "table_Name";
 
Example

Suppose we want to drop the following table created as in the following:
 

CREATE TABLE Student

(

   Stu_ID Number(10),

   Stu_Name varchar2(20),

   RollNo Number(10),

   Stream varchar2(20)

);      
 
STUDENTS

 

Stu_ID Stu_Name RollNo Stream
101 Shreya 5 Commerce
102 Rohan 9 Science
103 Ronit 17 Science


DROP TABLE "Student";
 
18. DROP VIEW Command

If a view exists in in the database and we want to drop it, then a DROP VIEW command allows the user to select a view and drop it.

Syntax
 

DROP VIEW [IF EXISTS]

view_name [, view_name] ...

[RESTRICT | CASCADE]

  
OR
 

DROP VIEW [schema.]view [CASCADE CONSTRAINTS]


Example

Suppose we want to drop the View created for the following table.
 
EMPLOYEE TABLE

Emp_ID Emp_Name ContactNo Designation Salary
      111 Jimmy CommercelNo Soft. Eng 50Km
      222 Alberta ScienceNo Tech Lead 65KM
      333 Harrison Scienceo Sr. Soft. Engg 60km

 

CREATE VIEW MaterView AS

SELECT *

FROM Employee

WITH READ ONLY CONSTRAINT Master_view_read_only;


Now, drop the preceding View as in the following:
 

DROP VIEW MasterView;


19. DROP USER Command

The DROP USER command is used to drop the user that was created earlier. The user can be dropped using the following syntax:

Syntax

  • If the user does not own any object in the Schema:

    DROPUSER user_name;
     
  • If the user owns an object in the Schema:

    DROPUSER user_name[ CASCADE ];

Example

Suppose we have a user that ws created using the following:

CREATE USER Joseph
IDENTIFIED BY JSH123;
 
Now, If Joseph owns the objects in the Schema then use:

DROP USER Joseph CASCADE;
 
Otherwise:

DROP USER Joseph;


20. DROP INDEX Command

The purpose of the DROP INDEX Command is to delete the previously created index from the database. Dropping an index from an Oracle Database invalidates all objects that depend on the underlying table, including functions, procedures, views, packages and package bodies.

Syntax

DROP INDEX Index_Name;
 
Example

Assume the following Index created for a Customer Table:
 
CUSTOMERS

Cust_ID Stu_Name City
1001 Jimmy Jalandar

Index

CREATE INDEX MyIndex
ON Customer (Cust_Name);

This query will create an index called "MyIndex" on the Customer table. Now, drop the index called "MyIndex".
 

DROP INDEX MyIndex; DROP TRIGGER trigger_name;

21. DROP TRIGGER Command

The DROP TRIGGER Command removes one or more triggers from the database. But, it is a necessity for the trigger to be dropped to be in your own schema and if you want to drop any trigger from another's schema then you must have ADMIN rights.

Syntax

DROP TRIGGER trigger_name;

Example: The following is the trigger created for the products table:

CREATE TABLE Product

(

  Prod_ID Number(10),

  Prod_Name Varchar2(20),

  Quantity Number(5),

  Price number(5,2),

  Create_date date,

  Created_by varchar2(10)

 );
 
PRODUCTS

Prod_id Prod_Name Quantity Price Create_date CCreated_by
           


Trigger using BEFORE UPDATE
 

CREATE OR REPLACE TRIGGER Products_before_update

BEFORE UPDATE

ON Products

FOR EACH ROW

DECLARE

username varchar2(15);

BEGIN

--To search username of person performing UPDATE on the table

SELECT user INTO username

FROM dual;

-- Update updated_date field to current system date

:new.updated_date := systemdate;

-- Update updated_by field to the username of the person performing the UPDATE

:new.updated_by := username;

END;


Now to drop it use the following query.

DROP TRIGGER Product_Before_Update;


22. DROP PROCEDURE Command

If you want to drop any procedure that is no longer in use you can remove using the DROP PROCEDURE Command.

Syntax
 

DROP PROCEDURE [schema.]procedure [,...n];


Example

The following is the procedure creating the Employee table.

Emp_ID Emp_Name Designation Salary
TCS008 Vivan Project Manager 1Lac
TCS030 Seema Tech Lead 80K

 

CREATE OR REPLACE PROCEDURE
Employee_details
IS
Cursor Emp_Cur IS
SELECT Name, Designation, Salary FROM Employee
emp_rec emp_cur%rowtype;
BEGIN

FOR Emp_rec in Sales_Cur

LOOP

Dbms_output.put_line(Emp_cur.Name ||  '||emp_cur.Designation ||' '||Emp_cur.Salary);

END LOOP

END;
 
The procedure for the preceding table also created. Let's drop the procedure.
 

DROP PROCEDURE Employee_details;

Previous article: Oracle SQL Commands : Part 3
Next article:Oracle SQL Commands : Part 5