Oracle 10g compressed export import

Sometime happened that I needed to export a database (either partially or ‘FULL’) not having enough disk free space available for all the operations: the export file by itself could barely fit into the few free disk space available so that I could not even think to compress it, because almost at the end of compression (gzip) the scenario would have been: export_file.dmp added to the almost 99.9% of the compressed export_file.dmp would exceed the free disk space available.

The following examples show how to do an export into a compressed file (gzip) in one single step avoiding wasted disk space.

momo@cujox:~ $ mkfifo fifo_dbexport_20081119.dmp
momo@cujox:~ $ nohup exp scott/tiger tables=(TABLE_1, TABLE_2, TABLE_3) FILE=fifo_dbexport_20081119.dmp LOG=dbexport_20081119.log &
momo@cujox:~ $ nohup gzip < fifo_dbexport_20081119.dmp > real_db_export_20081119.dmp.gz &
momo@cujox:~ $ rm fifo_dbexport_20081119.dmp

momo@cujox:~ $ mkfifo fifo_dbimport_20081119.dmp
momo@cujox:~ $ nohup gzip -d < real_db_export_20081119.dmp.gz > fifo_dbimport_20081119.dmp &
momo@cujox:~ $ nohup imp scott/tiger tables=(TABLE_1, TABLE_2, TABLE_3) IGNORE=Y FILE=fifo_dbimport_20081119.dmp LOG=dbimport_20081119.log &
momo@cujox:~ $ rm dbimport_20081119.dmp

Moving a table to a new tablespace on Oracle 10g

On Oracle 10g you can move a table from one tablespace to another as shown below. Keep in mind that everything associated with the table is moved as well (e.g. privileges, constraints, synonyms, etc.), but NOT indexes.

SQL> ALTER TABLE <table_name> MOVE TABLESPACE <new_tablespace_name>;

WARNING: By doing so, all indexes associated to the moved table will no longer useful and must be rebuilt as the following:

SQL> ALTER INDEX <index_name> REBUILD;

be available and must be recreated.

Moving an index to a different table space on Oracle 10g

An index can be moved from one tablespace to another as follows:

SQL> ALTER INDEX <index_name> REBUILD TABLESPACE <new_tablespace> [ STORAGE (storage_clause) ];

In the need of moving all indexes from one tablespace to another, the SQL script below can
be used to generate a final SQL script that does all moves sequentially.

set heading off
set feedback off
set linesize 160;
set pagesize 60;

connect system/system

!rm -f rebuildIndexes.sql
spool rebuildIndexes.sql
prompt connect system/system
SELECT ‘alter index ‘ || owner || ‘.’ || index_name ||
‘ rebuild tablespace NEW_TS STORAGE (INITIAL 64K);
FROM all_indexes
WHERE OWNER in (‘AA’, ‘BB’, ‘CC’, ‘DD’) and
TABLESPACE_NAME = ‘MY_TS’ AND PARTITIONED = ‘NO’
ORDER BY NUM_ROWS ASC
/
spool off

The above statement will produce the final SQL script for moving all indexes at once.

Colored prompt on bash console

It can be nice to have different color  prompt on the terminal screen to differentiate different login. the following code simply set up PS1 to have yellow prompt on the terminal screen, which can be appended in your .bash_profile

# set our custom yellow prompt
PS1=’\[33[1;33m\]\u\[33[1;37m\]@\[33[1;33m\]\h\[33[1;37m\]:\[33[1;33m\]\W \[33[1;33m\]\$ \[33[0m\]‘

# set our custom red prompt to visually identified root
PS1=’\[33[1;31m\]\u\[33[1;37m\]@\[33[1;31m\]\h\[33[1;31m\]:\[33[1;31m\]\W \[33[1;31m\]\$ \[33[0m\]‘

Keep Oracle SID’s handy

A trick to always keep andy all SID’s of all the different DB instance in your system’s can belike the following script.

I recommend to save into a separate text file (say orasids.sh) in your oracle $HOME directory and source from .bash_profile as its last line: or in alternatives, you can source at any time as follows

# source orasids.sh

Of course this file must be chown’ed 750.

#!/bin/bash
#
function showsid  () { echo -en “ORACLE_SID: $ORACLE_SID\n” ;};
function db01test  () { export ORACLE_SID=db01test; showsid ;};
function prod  () { export ORACLE_SID=prod; showsid ;};
function nosid    () { unset ORACLE_SID; showsid ;};
showsid
#
# end-of-file