24. DROP SCHEMA Command
A group of database objects owned by a database user that has the name of the database user is known as a Schema. If you decide to delete all objects of a certain schema then it is better to drop the schema because it will save time as well as it is easy to process. To delete the existing Schema we use the DROP SCHEMA Command.
Syntax
DROP SCHEMA Schema;
GO
Example
Assume the following Schema:
CREATE SCHEMA AUTHORIZATION Student
CREATE TABLE BOOK
(
Book_id NUMBER PRIMARY KEY,
Book_Price NUMBER,
Author Varchar2(10),
Seller Varchar2(10),
Publications Varchar2(30)
)
CREATE VIEW New_Book AS
SELECT Book_id, Author FROM Book WHERE Book_Price = 500
GRANT SELECT ON New_Book TO Scott;
Now, drop the Schema as in the following:
DROP SCHEMA Student;
GO
25. DROP FUNCTION Command
To drop a standalone stored function from the database we use the DROP FUNCTION Command.
Syntax
DROP FUNCTION [function name]
Example
Assume that we have the following table with a function total_students.
STUDENT
| Stu_ID | Name | Age | Stream |
| 1 | Rajat | 18 | Science |
| 2 | Jaya | 20 | Commerce |
| 3 | joseph | 19 | Arts |
| 4 | Maria | 18 | Science |
CREATE OR REPLACE FUNCTION total_students
RETURN number IS
total_number(2) :=0;
BEGIN
SELECT count(*) into total
FROM Students;
RETURN total;
END;
Now, to drop the function execute the following statement:
DROP FUNCTION <total_students>;
26. DROP SEQUENCE Command
An Oracle sequence is an object that generates incrementing and decrementing numbers. They can be cast as a Primary Key of a table. To drop the existing sequence we use a DROP SEQUENCE Command.
Syntax
DROP SEQUENCE [sequence name]
Example
Assume that we have the following created Sequence:
CREATE SEQUENCE dba_seq
MINVALUE 1
MAXVALUE 40000
START WITH 1
INCREMENT BY 1
CACHE 100
To drop the dba_seq we write the statement as:
DROP SEQUENCE dba_seq;
27. DROP SENONYM Command
If you are going to change the definition of the Schema and want to recreate it, then you need to drop it first or if you are going to permanently delete the synonym, for both the cases use the DROP SENONYM Command.
Syntax
DROP [PUBLIC] SYNONYM [schema.]synonym
Example
Assume we have the following Synonym:
CREATE PUBLIC SYNONYM buyers
FOR app.buyers;
Now, drop the Synonym as in the following:
DROP SYNONYM buyers;
28. CREATE JAVA Command
To create a schema object containing a Java source, class, or resource in your own schema, you must have CREATE PROCEDURE system privilege.
Syntax
Use the following to create a Java Class Object:
CREATE JAVA CLASS USING BFILE (java_dir, 'Agent.class')
/
Example
The following statement creates a Java resource schema object named "MyJava":
CREATE JAVA RESOURCE NAMED "MyJava"
USING BFILE (java_dir, 'JavaBundle.dat')
/
29. DROP JAVA Command
To create a Schema object containing a Java source, class, or resource in your own schema, you must have CREATE PROCEDURE system privilege. The Java source, class, or resource must be in your own schema or you must have the DROP ANY PROCEDURE system privilege. You also must have the EXECUTE object privilege on Java classes to use this command.
Syntax
Use the following to drop a Java Class Object:
DROP JAVA CLASS Class_name;
Example
The following statement drops a Java Class named "MyJava":
DROP JAVA CLASS "MyJava";
30. TRUNCATE TABLE Command
To delete all the rows from the table that are occupying more space and don't let the other rows to enter without dropping the entire table, we use the TRUNCATE TABLE Command.
Syntax
TRUNCATE TABLE table_name;
Example
Assume that we have a table named "MyTable".
Join the conversation! Your thoughts help the community grow.