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

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'

Oracle - Enabling Parallelism

If your database server is running on multiple processor system. You can make all your processors conspire to run a query. You might want to do this when the query is known to take a long time and there is no way it would return in sane time. At the same time, be careful to run this on production systems as it may hamper performance of the database.

select /*+ parallel(x 4) */ count(1)
from huge_table
where huge_table.x = 'somevalue'

Bind Parameters in a complex query

Oracle Data dictionary tip :

This is pretty useful when you are running a huge pl/sql code or a complex query and you need to know what bind parameters are being used for a query. helps figure out the iteration you are in amongst many other.

select name, position, datatype, 
       value_string, value_anydata 
from V$SQL_BIND_CAPTURE where sql_id 
IN 
 ( select a.sql_id 
   from v$sql a where sql_id in 
    ( select sql_id 
      from v$session 
      where osuser = 'ssinghal' 
      and machine = 'my_machine_name 
      and status = 'ACTIVE'
     )
 ); 

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


Formatting Oracle Timestamp

create table temp (a timestamp)

insert into temp values(sysdate)

select to_char(a,'MM/DD/YYYY HH24:MI:SS:FF3') from temp

http://www.databasejournal.com/features/oracle/article.php/2234501

Oracle Procedure Source code from Dictionary

SELECT to_char(text)
FROM user_source
WHERE type IN ('PACKAGE' ,'PROCEDURE','TRIGGER')

Vim and Sqlplus

open the following file :
$ORACLE_HOME/sqlplus/admin/glogin.sql
Add following line :
DEFINE _EDITOR='gvim -c "set filetype=sql"'

Typing edit will now open your favorite editor