• Home
• Contacts
• Slides

Helpful Oracle Queries

A list of helpful Oracle queries and tips.

Returns a list of all tables (System and User)
select * from all_tables;

Returns a list of all db_links (System and User)
select * from all_db_links;

Returns a list of all views (System and User)
select * from all_views;

Returns a list of all table columns (System and User)
select * from all_tab_columns;

List current sessions and do some stuff
SELECT * FROM v$session s;
SELECT s.sid,s.serial#,s.osuser,s.username FROM v$session s;
ALTER SYSTEM KILL SESSION ’sid,serial#’;

Converts a number or date to a string
select to_char(INSERT_DT,’YYYY-MM-DD’) the_date
from CREDIT_REPORT where
CREDIT_REPORT_ID = 8000076;

Converts a string to a date
select to_date(’2007-01-30′,’YYYY-MM-DD’) the_date
from CREDIT_REPORT where
CREDIT_REPORT_ID = 8000076;

Substitutes a value when a null value is encountered
select NVL(supplier_city, ‘n/a’) from supplier;

The functionality of an IF-THEN-ELSE statement
SELECT supplier_name, decode(supplier_id, 10000, ‘IBM’, 10001, ‘Microsoft’, 10002, ‘Hewlett Packard’, ‘Gateway’) result
FROM suppliers;

Testing for an empty BLOB
select credit_report_id, BLOB_COLUMN
from CREDIT_REPORT
where
BLOB_COLUMN is not null and dbms_lob.getLength(BLOB_COLUMN) > 0;

Converting BLOB to VARCHAR
select
utl_raw.cast_to_varchar2(dbms_lob.substr(BLOB_COLUMN, dbms_lob.getLength(BLOB_COLUMN), 1)) BLOB_AS_VARCHAR
from
CREDIT_REPORT
where
CREDIT_REPORT_ID = 8000056;

• Oracle Forensics

Did you enjoy this post? Why not leave a comment below and continue the conversation, or subscribe to my feed and get articles like this delivered automatically to your feed reader.

© 2003-2009 lonerunners.net