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.
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.
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\]‘
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
