Oracle Basic Command List

1.  First Connect to Oracle Database using  sqlplus cliet:

sqlplus scott/tiger 

or 

sqlplus profilemgr/profilemgr@APM 

APM = SID or database name

2. To list all your tables in Oracle server, use the following command:

SQL>  SELECT * FROM cat;

or

SQL> select * from tab


3. Get the database name from oracle

SQL> select ora_database_name from dual;


or determine the currently connected database:


SQL>  select global_name from global_name;


4. To see all users in the database system:

SQL> select * from all_users;



 5. To see the DBA user or normal user access, role and privileges:

DBA_TAB_PRIVS, DBA_SYS_PRIVS and DBA_ROLE_PRIVS

USER_TAB_PRIVS, USER_SYS_PRIVS and USER_ROLE_PRIVS


e.g.   SQL> select * from USER_TAB_PRIVS


6. Create USER command :

SQL>  CREATE USER sidney IDENTIFIED BY out_standing1 DEFAULT TABLESPACE example QUOTA 10M ON example TEMPORARY TABLESPACE temp QUOTA 5M ON system PROFILE app_user PASSWORD EXPIRE.

7. Grant System Privileges to user

SQL> grant create session to test;

or  SELECT ANY TABLE,  INSERT ANY TABLE etc.


8. Select the schema for the new user, since every table is under schema so you must need to assign a schema for the new user:

SQL> alter session set current_schema=starting

to check the current schema:

SQL> select sys_context( 'userenv', 'current_schema' ) from dual;



9. Grant (Object) all privileges to database or tables: 

SQL> grant select, insert, update, delete on starting.suppliers to smithj;

SQL> grant all on starting.suppliers to smithj;

SQL> revoke privileges on object from user; 

SQL> REVOKE CREATE ANY INDEX FROM Robert;

SQL> REVOKE ALL PRIVILEGES FROM Robert;

10. List all the table space in oracle DB:

SQL> select tablespace_name from dba_tablespaces;