This is a draft cheat sheet. It is a work in progress and is not finished yet.
Optimization
ANALYZE TABLE <table> ESTIMATE STATISTICS [SAMPLE # ROWS|PERCENT];
Execute DBMS_UTILITY.ANALYZE_SCHEMA('<name>','COMPUTE');
ANALYZE TABLE <table> DELETE STATISTICS; |
Index types
B-Tree Best used on foreign keys and small selections |
CREATE INDEX <indexname> ON <table> ; |
Compressed Only used on several columns indexes |
CREATE [UNIQUE] INDEX <indexname> ON <table> COMPRESS; |
Bitmap Used on columns with recurrent values |
CREATE BITMAP INDEX <indexname> ON <table> (column) STORAGE (INITIAL #K NEXT #K); |
Reversed Key When the last values are the more important |
CREATE INDEX UNIQUE <indexname> ON <table>(column); |
ALTER INDEX REBUILD [REVERSE|ONLINE|COMPRESS]; can also be used to modify the type.
|
|
Boot/Shutdown
STARTUP; (assumes statup open) |
SHUTDOWN; |
STARTUP MOUNT; |
SHUTDOWN IMMEDIATE; (disconnects users) |
STARTUP OPEN; |
Tablespace
CREATE TABLESPACE <name> DATAFILE </path/to/datafile> SIZE #K|M AUTOEXEND ON
SEGMENT SPACE MANAGEMENT AUTO
EXTENT MANAGEMENT LOCAL UNIFORM SIZE #K|M; |
* A table space is a logical storage unit. It can handle tables and data.
|
|
RMAN
RMAN TARGET=[user/pass@]<basename>;
BACKUP [HOW] WHAT [OPTION];
ex: BACKUP DATABASE PLUS ARCHIVELOG DELETE INPUT; |
info:RMAN is launched from the OS CLI and assumes SYSDBA as default user.
RMAN - options
INCREMENTAL LEVEL [0|1]
VALIDATE
DATABASE
TABLESPACE <name>
DATAFILE <name>
CURRENT CONTROLFILE
ARCHIVELOG <name>
NOT BACKED UP [since] |
RMAN - incremental levels
Level 0 does a full backup since the last complete backup. A level 1 does it since the last incremental backup.
|
