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
0 Comments.