Oracle SQL

Oracle Notes
This is so far a start of a little set of sysadmins beginners guide to Oracle SQL, more ot come... Most of this was just found around the web.

Wild cards
%

Logic
SELECT

Matching
WHERE

LIKE

AND NOT

Output
ORDER BY, eg "SELECT itemname,id FROM schema.table ORDER by item_name"

Functions
count(query), eg "count(SELECT item FROM schema.table WHERE user='test')" distinct query, eg "distinct transactions.transaction_id" in(query), eg "in(select id from schema.table)" in(list) eg "in('item1','item2',(item3')"

sysdate

Show current database

 * > SELECT * FROM global_name;

Show who I am:

 * > SHOW USER;

Change a user's password

 * > 	ALTER USER user IDENTIFIED BY password;

Unlock an account

 * > 	ALTER USER user ACCOUNT UNLOCK;

Formating
http://download.oracle.com/docs/cd/B19306_01/server.102/b14357/ch12013.htm http://download.oracle.com/docs/cd/B19306_01/server.102/b14357/ch6.htm

Reset a format

 * > COLUMN column_name CLEAR

Temp clear

 * > COLUMN column_name OFF
 * > COLUMN column_name ON

Formating a column as text

 * > col Col_name for a40
 * > COLUMN LAST_NAME FORMAT A20 HEADING 'EMPLOYEE|NAME'
 * > COLUMN REMARKS FORMAT A20 WRAP
 * > COLUMN REMARKS FORMAT A20 WORD_WRAP
 * > COLUMN REMARKS FORMAT A20 TRUNCATE

Formating a column as $$

 * >COLUMN SALARY FORMAT $9,999,990.99

Headings

 * > COLUMN column_name HEADING column_heading
 * > COLUMN SALARY HEADING 'MONTHLY|SALARY'


 * > SET UNDERLINE =
 * > SET UNDERLINE '-'

Set display rows

 * > SET PAGESIZE 66;

To turn pause on

 * > SET PAUSE ON;

chosing line width

 * > set lines 200

chosing number of lines per page
?? what are these two exactly??


 * > set pages 200


 * > set pagesize 6

List Schemas

 * > SELECT username FROM all_users ORDER BY username;

List tablespaces

 * > SELECT tablespace_name FROM dba_tablespaces;

List tables current user has access to

 * > SELECT table_name FROM all_tables;

List tables in a Schema

 * > SELECT Table_Name FROM All_Tables where Owner = 'TRANSACTIONS';

List all tables in current schema

 * > SELECT table_name FROM user_tables;

Current schema

 * > 	SELECT constraint_name,search_condition FROM user_constraints WHERE table_name='tablename';

AnOther schema

 * > SELECT constraint_name,search_condition FROM all_constraints WHERE table_name='tablename' AND owner='schemma_name' ;

List a tables coloms and of what data type they are

 * > DESC tablename;

all FROM a local table

 * > SELECT * FROM tablename;

all FROM a table in schema

 * > SELECT * FROM schema.tablename;

specific FROM a table in schema

 * > SELECT name,value FROM schema.tablename;