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.

query Basics

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()

Global

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

working with Schemas / tables

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;

Read field constraints

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' ;