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;
>> 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
>> create sequence SEQ_MY_TABLE start with 1 increment by 1 nomaxvalue;
* show last sequence value
>> SELECT sequence_name, last_number from user_sequences;
* rename column
>> 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
>> startup
* stop database
>> shutdown immediate
* start database without opening
>> startup mount
* open database
>> alter database open
* disable archivelog (not on an opened database)
>> alter database noarchivelog;
* enable archivelog (not on an opened database)
>> alter database archivelog;
Backup
* run backup utility
>>rman target sys@$ORACLE_SID
* show configurations
>> show all;
* create full backup
>> backup database;
* show retention policy
>> show retention policy;
* configure retention policy with multiple copy of datafiles
>> configure retention policy to redundancy 2;
* configure retention policy to 7days, made up with a weekly full backup and archivelog
>> configure retention policy to recovery window of 7 days;
* show obsolete files and backups;
>> report obsolete;
* remove obsolete files and backups;
>> 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