Niklas Tech Blog

When working with many different databases in CLI mode a “cheat sheet” for each is really necessary to keep you from going bonkers. Here is mine for Oracle 10g (from inside SQLPlus):

Show table
Tables you own

select table_name from user_tables;

Tables you own and tables you have been granted select rights on

select table_name from all_tables;

To list all tables you are granted to see

select table_name from dba_tables;

Show primary key of table

SELECT cols.column_name, cols.position, cons.status
FROM all_constraints cons, all_cons_columns cols
WHERE cols.table_name = 'YOUR_TABLE_NAME'
AND cons.constraint_type = 'P'
AND cons.constraint_name = cols.constraint_name
AND cons.owner = cols.owner
ORDER BY cols.table_name, cols.position;

This will show you the name of the key/s, order (if more then one) and status of the key/s

Create tables

create table tablename ( columnname type, columnname type …, primary key(keycolumn,keycolumn,...));

Get table information

DESCRIBE tablename

Alter table
Add column

alter table tablename add(  columnname datatype, ...);

Change datatype of column

alter table tablename modify ( column newdatatype);

Drop column

alter table drop column columnname;

Delete table

drop table tablename;

INSERT

INSERT INTO tablename (column1, column2,... ) VALUES (1, 2,...');

SELECT

SELECT column1, column2,... FROM tablename WHERE column1 = 1 AND column2 = 2 ORDER BY column1, column2,...;

UPDATE

UPDATE tablename SET column1 = 1, column2 = 2,... WHERE column1 = 1 AND column2 = 2 AND ...;

DELETE
Delete all rows

DELETE FROM tablename;

Delete rows

DELETE FROM tablename WHERE column1 = 1 AND column2 = 2, ...

Write query result to file
From inside SQLPlus:

SQL>SPOOL /path/to/file
SQL>SELECT * FROM users;
SQL>SPOOL OFF

The result from the query will now be in the file specified on the fist line

Google+