Useful Oracle Commands

These are some of the oracle commands I use frequently. You should be logged on as SYS.

This SQL statement shows the users/systems that are connected to the database:

select * from v$session;

The SQL statement shows the SQL statements that are being run and also historical statements:

select * from v$sql;

It can be useful to check what sql a session is executing by using the sql_id key.

Will show you all the queries that took a long time to run:

select * from v$_longops;

It can be annoying when looking dates in the database that the dates do not show the time. The following command is a quick
way to display the time:

alter session set nls_date_format=‘yyyy-mm-dd hh24:mi:ss’;

To create a tablespace:

create tablespace [tablespace_name] datafile ‘[tablespace_name].dbf’ size [database_size] reuse autoextend on next 10M maxsize unlimited extent management local uniform size 1M;

The tablespace_name is the name you want to give the tablesapce
The database_size is the size you want to designate e.g. 2G

You will also need to create a user for the tablespace:

create user [user] identified by “[password]” default tablespace [tablespace_name] temporary tablespace TEMPprofile DEFAULT;

The user will also need permissions:

grant dba to [user]; grant connect, resource to [user]; grant unlimited tablespace to [user];

Note you would not want to grant dba to the user. You will need to make your own permissions.

Import/Export

The standard import functions are run from the command line and are as follows:

imp [user]/[password]@[tns_name] fromuser=[from_user] touser=[to_user] file=file_name.dmp log=file_name.log

The standard export functions are run from the command line and are as follows:

exp [user]/[password]@[tns_name] file=file_name.dmp owner=[user] log=file_name.log statistics=none compress=y

Datapump

Create a database directory by logging in as sys[dba] and run

CREATE DIRECTORY [directory_name] AS ‘[directory_path]’;
GRANT read, write ON DIRECTORY [directory_name] TO [user];

To drop the directory

DROP DIRECTORY [directory_name];

To export

expdp [user]/[password]@[tns_name] directory=[directory_name] dumpfile=[dumpfile_name].dmp schemas=[owner] logfile=[logfile_name].log

To import

impdp [user]/[password]@[tns_name] directory=[directory_name] dumpfile=[dumpfile_name].dmp logfile=[logfile_name].log 

other parameters

tables= comma separated list of table names 

table_exists_action=truncate