Oracle Notes[edit]
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[edit]
Wild cards[edit]
%
Logic[edit]
SELECT
Matching[edit]
WHERE
LIKE
AND NOT
Output[edit]
ORDER BY, eg "SELECT itemname,id FROM schema.table ORDER by item_name"
Functions[edit]
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[edit]
Start a DB instance[edit]
you can start you DB instance (amusing the DB init files are already set up) more info here
As the Oracle user #> plus :> startup
or As the Oracle user
#> sqlplus /NOLOG @> CONNECT / AS SYSDBA SYS@DB_INST> startrup
Show current database[edit]
- > SELECT * FROM global_name;
Show who I am:[edit]
- > SHOW USER;
Change a user's password[edit]
- > ALTER USER user IDENTIFIED BY password;
Unlock an account[edit]
- > ALTER USER user ACCOUNT UNLOCK;
Formating[edit]
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[edit]
- > COLUMN column_name CLEAR
Temp clear[edit]
- > COLUMN column_name OFF
- > COLUMN column_name ON
Formating a column as text[edit]
- > 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 $$[edit]
- >COLUMN SALARY FORMAT $9,999,990.99
Headings[edit]
- > COLUMN column_name HEADING column_heading
- > COLUMN SALARY HEADING 'MONTHLY|SALARY'
- > SET UNDERLINE =
- > SET UNDERLINE '-'
Set display rows[edit]
- > SET PAGESIZE 66;
To turn pause on[edit]
- > SET PAUSE ON;
chosing line width[edit]
- > set lines 200
chosing number of lines per page[edit]
?? what are these two exactly??
- > set pages 200
- > set pagesize 6
working with Schemas / tables[edit]
List Schemas[edit]
- > SELECT username FROM all_users ORDER BY username;
List tablespaces[edit]
- > SELECT tablespace_name FROM dba_tablespaces;
List tables current user has access to[edit]
- > SELECT table_name FROM all_tables;
List tables in a Schema[edit]
- > SELECT Table_Name FROM All_Tables where Owner = 'TRANSACTIONS';
List all tables in current schema[edit]
- > SELECT table_name FROM user_tables;
Read field constraints[edit]
Current schema[edit]
- > SELECT constraint_name,search_condition FROM user_constraints WHERE table_name='tablename';
AnOther schema[edit]
- > SELECT constraint_name,search_condition FROM all_constraints WHERE table_name='tablename' AND owner='schemma_name' ;
Working with Tables[edit]
Describe table[edit]
List a tables coloms and of what data type they are[edit]
- > DESC tablename;
List content of a table[edit]
all FROM a local table[edit]
- > SELECT * FROM tablename;
all FROM a table in schema[edit]
- > SELECT * FROM schema.tablename;
specific FROM a table in schema[edit]
- > SELECT name,value FROM schema.tablename;