Basic Commands of Oracle9

Importing .dmp file to database:

# imp username/passwd  file=<path of dmp file> full=y

To Start Oracle:

# sqlplus /nolog
SQL> connect / as sysdba
SQL> startup

To Start Listner:

# lsnctr
LSNCTR> start

To see the Database name:
SQL> SELECT NAME FROM v$database;

To find out datafiles used by Tablespace:
SQL> SELECT NAME FROM v$datafile;

To find out  Tablespace Names:
SQL> SELECT NAME FROMv$tablespace;

To see the Previlages given to Users:
SQL> SELECT * FROM db_role_privs;

To create Tablespace name USERS:
SQL> CREATE TABLESPACE USERS
            DATAFILE ‘<path of dbf file>’
            SIZE 100M;

To add a datafile to USERS tablespace:
SQL> Alter TABLESPACE USERS
            ADD DATAFILE  ‘<path of dbf file>’
            SIZE 1000M
            AUTOEXTEND ON
            NEXT 100M
            MAXSIZE 1000M;

To create user TEST:
SQL> CREATE USER TEST
            IDENTIFIED BY <password>
            DEFAULT TABLESPACE USERS;

To change password of user TEST:
SQL> Alter USER TEST IDENTIFIED BY <new password>;

Previlages to TEST:
SQL> GRANT CREATE SESSION TO TEST;

SQL> GRANT DBA TO TEST WITH ADMIN OPTION;
           

To Drop tablespace USERS:
SQL> DROP TABLESPACE USERS INCLUDING CONTENTS;