Oracle important DDL Statements

Commit statement:

This statement enables all changes to be saved to the database.

COMMIT;

Rollback statement:

This statement enables all changes to be rolled back (undo action) from the database.

ROLLBACK;

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;

Truncate Table:

This command deletes all records permanently. You cannot rollback.

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.

CL SCR;

Spool:

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.

SPOOL ON;
SPOOL D:/SPOOL_TABLES.TXT;
SELECT TABLE_NAME FROM DICT;
SPOOL OFF;

How to find out the schema name on which you are working?

SHOW USER;

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

STA D:/db.txt;

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.

CONNECT USER2/USER2@ORACLEDB;

If you want to hide the password

connect user2@oracledb;
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. */