Tag Archives: Oracle

Oracle: Export tables with the exp command and a parfile

Posted by on July 1, 2011 1 comment

This is an example of an export of selected tables with the Oracle DB command exp:

Parfile:

BUFFER=100000
TABLES=(table1,table2,table3)
FILE=/path/to/exportfile.dmp
LOG=/path/to/logfile.log

Let’s name this file ‘export.conf’

Command:

exp <user>/<password>@<sid> PARFILE=export.conf

This will export all data needed to recreate the tables: table1, table2 and table3 into exportfile.dmp. This file can later be used with the imp command to restore the tables

To see more options of the exp command try:

exp help=y

This will show you a list of commands and settings

SQLPlus: Dump table data to csv file

Posted by on July 1, 2011 No comments

Since this is quite a cumbersome operation (in contrast to for example with MySQL) I have put together a script to modify and use every time I need to dump some table data to a CSV file.
NOTE: This only works with text and simple number fields. More complex field types will just come out truncated or as gibberish. 

SET echo off
SET feedback off
SET linesize 100
SET pagesize 0
SET sqlprompt ''
SET trimspool on
SPOOL filename.csv

SELECT field1 || ',' || field2 || ',' || field3 FROM tablename;

SPOOL off
exit

As you can see the trick is to turn off any extra output and to use the string concatenation operator|,|to insert commas (or any separator you want) between the fields.

The SPOOL command opens a file and dump all output made until the SPOOL off command

To run the script use: 

sqlplus <user>/<password>@<SID> @scriptname.sql

Tested with SQL*Plus: Release 10.1.0.2.0

Oracle DB syntax cheat sheet

Posted by on February 10, 2011 No comments

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