You are here: Home Data Modeling & Database Oracle important DDL Statements
This statement enables all changes to be saved to the database.
This statement enables all changes to be rolled back (undo action) from the database.
Grant and Revoke Commands:
For the following statements to work, you should have another schema "USER2" in the oracle database. Otherwise, this command will fail with an error message.
You are granting all privileges like insert, delete, update etc to USER2 on table EMPLOYEE_DTL.
GRANT ALL ON EMPLOYEE_DTL TO USER2;
You are revoking all privileges like insert, delete, update etc from USER2 on table EMPLOYEE_DTL.
REVOKE ALL ON EMPLOYEE_DTL FROM USER2;
This command deletes all records permanently. You
TRUNCATE TABLE EMPLOYEE_DTL;
How to rename a table?
RENAME EMPLOYEE_DTL TO EMPLOYEE_DTL_BKP;
RENAME EMPLOYEE_DTL_BKP TO EMPLOYEE_DTL;
To clear Oracle screen:
This is not deleting the records from the database. This command clears the screen on which you are working.
This command helps to save the query results executed from sql prompt to a file. After executing this command, open the file SPOOL_TABLES.TXT. You will see the results of the query.
SELECT TABLE_NAME FROM DICT;
How to find out the schema name on which you are working?
How to execute (run) a file?
Procedures, Functions, Packages, Triggers etc are created in a file and then created on the database. To create those object, run those files, by using the following command. Here db.txt is the file name that contains the programmatical code and stored in D: drive
How to switch from one schema to another schema?
How to switch from one schema(user1/user1@oracledb) to another
schema(user2/user2@oracledb). Assume you have logged in a user1/user1@oracledb
where username = user1, password = user1 and oracle instance =
oracledb. After typing the following command, you will be switched to USER2 schema.
If you want to hide the password
Oracle will prompt for password and you enter the password "user2.
How to comment?
If you want to comment a single line the used Double hyphens(--).
If you want to comment group of lines start with /* and end with */
E.g. -- This code is used for creating table?
E.g. /* This code is used for creating table, sequences, packages, etc.
Further it provides useful information about the function of each and every program used in databases. */
DDL Statements - Drop Command
Data Dictionary Commands