Some Helpfule DB Commands

Grant and Revoke Commands:
---------------------------------------------
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.

Select statement - To find out the oracle's views:
--------------------------------------------------------------------------

This select statement retrieves views from Oracle's data dictionary.

SELECT * FROM DICT;

Select statement - To find out the tables under your schema:
---------------------------------------------------------------------------------------------

SELECT * FROM TAB;

There are several categories of dictionary views like "ALL", "GV$", "V$", "USER", "DBA", "SYS" etc. If you have proper privileges, you can get to know about the databases, schemas, tablespace, segments, extents, blocks, partitions and so on. Here are some of the important views, that a data modeler has to know about. The following information has been sourced from www.oracle.com. For details, please go to their official website www.oracle.com.

Prefix the following views with "Select * from " to see the results specified under the description column.

Important Data Dictionary Views:
-----------------------------------------------------------

View Name                                    Description
----------------------                                ------------------------
ALL_USERS               : Information about all users of the database
USER_OBJECTS       : Objects owned by the user
USER_DB_LINKS       : Database links owned by the user
USER_TABLES               :Description of the user's own relational tables
USER_COL_COMMENTS    :Comments on columns of user's tables and views
USER_VIEWS                     :Description of the user's own views
USER_VIEWS                     :Description of the user's own views
USER_MVIEWS              :All materialized views in the database
USER_SYNONYMS              :The user's private synonyms
USER_TRIGGERS              :Triggers owned by the user
USER_PROCEDURES      :Description of the users own procedures
USER_SEQUENCES               :Description of the user's own SEQUENCEs
USER_COL_PRIVS            :Grants on columns for which the user is the owner, grantor or grantee
USER_COL_PRIVS_MADE    :All grants on columns of objects owned by the user
USER_COL_PRIVS_RECD            :Grants on columns for which the user is the grantee
USER_ROLE_PRIVS                    :Roles granted to current user
USER_CONSTRAINTS            :Constraint definitions on user's own tables
USER_CONS_COLUMNS            :Information about accessible columns in constraint definitions
USER_DEPENDENCIES             :Dependencies to and from a users objects
USER_INDEXES                     :Description of the user's own indexes
USER_IND_COLUMNS              :COLUMNs comprising user's Indexes and Indexes on user's TABLES
USER_SOURCE                     :Source of stored objects accessible to the user