Getting tablespace Size

SELECT tablespace_name,
       SUM (bytes) / 1024 / 1024 current_size,
       autoextensible,
       SUM (maxbytes) / 1024 / 1024 max_size
FROM dba_data_files
WHERE tablespace_name like '%'
GROUP BY tablespace_name, autoextensible
ORDER BY 1

Common sqlplus commands

ACCEPT - Get input from the user 
DEFINE - Declare a variable (short: DEF) 
DESCRIBE - Lists the attributes of tables and other objects (short: DESC) 
EDIT - Places you in an editor so you can edit a SQL command (short: ED) 
EXIT or QUIT - Disconnect from the database and terminate SQL*Plus 
GET - Retrieves a SQL file and places it into the SQL buffer 
HOST - Issue an operating system command (short: !) 
LIST - Displays the last command executed/ command in the SQL buffer (short: L) 
PROMPT - Display a text string on the screen. Eg prompt Hello World!!! 
RUN - List and Run the command stored in the SQL buffer (short: /) 
SAVE - Saves command in the SQL buffer to a file. Eg "save x" will create a script file called x.sql 
SET - Modify the SQL*Plus environment eg. SET PAGESIZE 23 
SHOW - Show environment settings (short: SHO). Eg SHOW ALL, SHO PAGESIZE etc. 
SPOOL - Send output to a file. Eg "spool x" will save STDOUT to a file called x.lst 
START - Run a SQL script file (short: @)

source :

http://www.orafaq.com/wiki/SQL*Plus_FAQ

Oracle - Default Location for data files

To get the existing location

sql > show parameter db_create;

To set a new location

sql> alter system set db_create_file_dest='/home/ssinghal/oradata';

Oracle dba_jobs : Breaking

exec dbms_job.broken(690,true);

where 690 is the job number obtained from
select * from dba_jobs

Oracle databases - startup and shutdown

Shutdown:

$ export ORACLE_SID=db_sid
$ sqlplus /nolog
sql> connect / as sysdba
sql> shutdown immediate

Startup :

$ export ORACLE_SID=db_sid
$ sqlplus /nolog
sql> connect / as sysdba
sql> startup mount
sql> alter database open;

Note: This needs to be done by logging on the machine where oracle database is installed

Database Objects - Finding Dependencies

You come across tables in databases that are being populated by other pl/sql procs, triggers e.t.c what becomes difficult is to figure out which process is actually updating a table.

The following query will list objects that refer a table "TABLE_A" which belongs to a schema "SCHEMA_A"

select name,owner
from dba_dependencies
where referenced_name='TABLE_A' 
and referenced_owner='SCHEMA_A'

Long Operations in Oracle

It is nice to know what queries are taking time to finish. The following sql figures out the "long operations" currently being processed


SELECT username,time_remaining,sofar/totalwork,message,target_desc
FROM gv$session_longops where sofar<totalwork
ORDER BY message