ORACLE BASIC Developer & DBA COMMANDS

After DB installation Ensure the following services are running.

1. Oracle service<Instance Name>

2. Oracle listener service

Goto sqlplus enter userid/pwd or userid/pwd@service_name

If the username and pwd is correct, we would be logged into the oracle and will get the SQL Prompt

SQL>

To Create Table Space:

Create a folder in local drive like BOSE_DBF

SQL> CREATE TABLESPACE KK DATAFILE 'E:\BOSE_DBF\BOSE.DBF' SIZE 50M;

Tablespace created.

To Increase the Tablespace Size:

• Increasing the Existing Datafile

SQL> ALTER DATABASE DATAFILE 'E:\BOSE_DBF\BOSE.DBF' RESIZE 100M;

Database altered.

Appending additional DATA file to the Tablespace

SQL> alter tablespace kk add datafile 'e:\bose_dbf\bose1.dbf' size 20M;

Tablespace altered.

To create DB user:

SQL> create user <username> identified by <password> default tablespace <Tablespace Name>

Temporary tablespace <Temporary tablespace name>;

SQL> create user bose identified by bose default tablespace bose temporary tablespace temp;

User created.

SQL> show user;

USER is "SYS"

Granting Access to the USER:

SQL> grant create session,imp_full_database to <user name>

SQL> grant create session,imp_full_database to bose;

Grant succeeded.

Granting DBA Access to the USER:

SQL> grant dba to user

SQL> grant dba to bose;

Grant Succeeded

Altering USER:

SQL> alter user bose quota unlimited on <username>;

SQL> alter user bose quota unlimited on bose;

User altered.

Now we will exit from the sys user and will connect our newly created to user to import or export the db objects

SQL>conn bose/bose

Syntax for Import:

• C:\>imp <username>/<password> file=<dumpname.dmp> log=<logfilename.log> full=y

C:\>imp bose/bose file=bose.dmp log=boseimp.log full=y

• C:\>imp <username>/<password> file=<dumpname.dmp> log=<logfilename.log> fromuser=<fromusername> touser=<tousername>

• Before import we need to check the tablespace free space.

SYSTEM -200M

INDX -100M

DEFAULT TABLESPACE (BOSE) 

C:\>imp bose/bose file=bose.dmp log=boseimp.log fromuser=hr touser=bose

Import completed successfully with warnings.

After Import Connect to the Respective DB user

C:\>sqlplus

Enter username: bose/bose

SQL>@c:\compile – it will compile all the Invalid objects run the compile script atlease 4 times.

Exporting DB user:

Syntax:

C:\> exp <username>/<password> file=<filename.dmp> log=<logname.log>

C:\>exp bose/bose file=bose.dmp log=kk.log

.

.

Export terminated successfully without warnings.

Taking full DB Export:

 C:\>exp system/<system pwd> file=<filename.dmp log=logfile.log full=y

C :\> exp system/system file=fulldump.dmp log=fulldump.log full=y

To drop DB user:

SQL> drop user kk cascade;

              User dropped.

To drop DB user object:

Connect to specified Database

SQL> CONN bose/bose

SQL> SHOW USER

BOSE

SQL>@C:\drop.sql

To Drop Tablespace:

SQL> drop tablespace bose including contents;

Tablespace dropped.

SQL> select username from dba_users:

It will display all the username in the DB

SQL> select name from v$database;

NAME

ORCL

It will display the DB name

SQL> select count(*) from user_objects;

           It will display the number of objects in that schema.

Cheers!

Bose