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
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