Database Day 05: sqlplus Oracle commands (1)

1. To connect to a different database from a current command-line session
sql>connect username
E.g. sql>connect system;
sql>connect hr;

2. Change user's password
ALTER USER USER IDENTIFIED BY password;

3. Change user's password and unlock the user account
ALTER USER USER IDENTIFIED BY password ACCOUNT UNLOCK;

4. Show all parameters
sql>show all;

5. Describe a table
desc <table_name>

6. Create a user with permissions using grant command
sql>create user <username that you want to set> identified by <password you want to set>;

User Created.
E.g. sql>create user foo identified by 123;

but again after user is created, you need to grant few rights for him/her.

SQL>grant connect,resource to <created username>;

Grant Succeeded.
E.g. sql>grant connect,resource to foo;

7. Show user
E.g. sql>show user;

8. To list all the tables you currently own
E.g. sql>select * from cat;

9 To show the contents of the table use the SQL SELECT command:
E.g. sql>select * from table_name;

10. To remove a table use the SQL DROP command:
E.g. drop table table_name purge;
Tables must be dropped in the reverse order from which they were created to protect the parent-child relationship (referential integrity) [1].

Basic ORACLE data types

Basic Oracle data types [1].

Oracle remark command (rem) for comments

REM command to create a REMARK line in the script [1].

Bibliographies and References
1. FIT5132 Introduction to databases, S2-2014, Monash University