As I don’t usually work on Oracle…

Misc

* limit resultset to 10 rows
> SELECT … WHERE rownum<10

* formatting spool for eg. csv
> set linesize 2000
> set colsep , — separate columns with a comma
> set pagesize 0 — No header rows
> set trimspool on — remove trailing blanks
> set headsep off — this may or may not be useful…depends on your headings.
> set numw 10 — number of digits to avoid exponential notation

* dump output to file
> SPOOL /tmp/file.sql
> SELECT * from my_table;
> SPOOL OFF

Tables

* show tables
> SELECT table_name FROM user_tables;

* list table informations
> SELECT table_name, compression, compress_for FROM user_tables where table_name like ‘%MYSUFFIX’;

* describe tables;
> DESCRIBE $table; — not a valid sql statement, usable only by sqlplus

* show create table;
&gt> select dbms_metadata.get_ddl( ‘TABLE’, ‘MYTABLE’) from dual;

* show free space in tablespace
> SELECT file_name, tablespace_name, ROUND(MAXBYTES/1024000) FB, ROUND(bytes/1024000) MB from dba_data_files

* show lines in partitions
> SELECT partition_name, tablespace_name, compression, num_rows FROM dba_tab_partitions WHERE num_rows > 0 and table_name LIKE ‘%MYSUFFIX’;

* show sequences
> SELECT * from user_sequences; — all_sequences;

* create sequence
&gt> create sequence SEQ_MY_TABLE start with 1 increment by 1 nomaxvalue;

* show last sequence value
&gt> SELECT sequence_name, last_number from user_sequences;

* rename column
&gt> ALTER TABLE table_name RENAME COLUMN oldname TO newname;
Procedures

* list procedures
> SELECT * FROM all_procedures;
* list user procedures
> SELECT * FROM user_procedures;
* list names
> SELECT DISTINCT name FROM user_source WHERE type=’PROCEDURE‘;
* list content
> SELECT text FROM user_source WHERE type = ‘PROCEDURE‘ AND name=’ProcName’

* list a stored procedure code without headers

> set serveroutput ON
> set feedback off
> set linesize 200
> set ver off
> set pagesize 5000
> set heading off

Partitions & Tablespaces

* list tables and tablespaces
> SELECT TABLE_NAME,TABLESPACE_NAME FROM user_tables WHERE table_name LIKE ‘MY_PRJ_%’;

* list partitions and tablespaces
> SELECT PARTITION_NAME,TABLESPACE_NAME FROM dba_tab_partitions WHERE table_name LIKE ‘MY_PRJ_%’;

* drop partition
> alter table MY_TABLE drop partition MY_TABLE_201202161605

* create tablespace with datafile attached.
> CREATE TABLESPACE my_tbs DATAFILE ‘tbs_perm_01.dat’ SIZE 20M ONLINE;

* resize a datafile (provided enough free space in tail).
> ALTER DATABASE DATAFILE ‘/oradata/DB1/tbs_perm_01.dat’ RESIZE 6G;

Schema (aka user;)
* create schema
> create user MYSCHEMA identified by sEcReT;

* create schema with default (existing) tablespace. Optional clause in bracket.
> create user MYSCHEMA identified by sEcReT default tablespace my_tbs [quota 200G on my_tbs];

* drop schema (user and its tables)
> drop user MYSCHEMA cascade;

* drop user tablespace (including datafiles)
> drop tablespace my_tbs cascade including contents and datafiles;

* unlock user
> alter user MYSCHEMA account unlock;
(optional)> grant connect, resource to MYSCHEMA;

* grant schema further tablespaces
> alter user MYSCHEMA quota unlimited on my_tbs_1;

Dumping
The oracle exp command is used to export a database. You can find under your ORACLE_HOME.

* a full logical backup of your database
# /opt/oracle/bin/exp user/pass@schema file=/tmp/ora_schema.sql

* save just the schema definition
# /opt/oracle/bin/exp user/pass@schema file=/tmp/ora_schema.sql rows=n

* expdp – requiring a DUMP_HOME to be set
#mkdir /backup;
#sqlplus / as sysdba <<< ”
/* Define test_dir and grant access to user */
CREATE OR REPLACE DIRECTORY test_dir AS ‘/backup/’;
GRANT READ, WRITE ON DIRECTORY test_dir TO scott;

”
#expdp SCOTT/pass schemas=SCOTT dumpfile=SCOTT.dmp directory=test_dir logfile=expdp.log

Basic Administration
* start database
&gt> startup

* stop database
&gt> shutdown immediate

* start database without opening
&gt> startup mount

* open database
&gt> alter database open

* disable archivelog (not on an opened database)
&gt> alter database noarchivelog;

* enable archivelog (not on an opened database)
&gt> alter database archivelog;

Backup
* run backup utility
&gt>rman target sys@$ORACLE_SID

* show configurations
&gt> show all;

* create full backup
&gt> backup database;

* show retention policy
&gt> show retention policy;

* configure retention policy with multiple copy of datafiles
&gt> configure retention policy to redundancy 2;

* configure retention policy to 7days, made up with a weekly full backup and archivelog
&gt> configure retention policy to recovery window of 7 days;

* show obsolete files and backups;
&gt> report obsolete;

* remove obsolete files and backups;
&gt> delete obsolete;

Formatting

A column with 20 char
>COL my_text_column FOR a20

A column with 3 digits +2decimal (9 is just a placeholder)
> COL my_number_column FOR 999.99