Here is a convenient PL/SQL script to run with SqlPlus to change the tablespace of the objects (tables, indexes, lobs) of a specific user in a blink. It can also be use to fix inconsistency in tablespace used by the user objects.
We will then see how to use a bash script to make its use less error prone and non interractive.
The PL/SQL script¶
The PL/SQL script below changes the tablespace of the tables, indexes and lob objects of the current user. It supports using a different tablespace for data and indexes.
Create file, e.g. called change_tablespaces.sql, with the following content.
declare
cursor tab_cur is select table_name from user_tables where tablespace_name != '&&Data';
cursor ind_cur is select index_name from user_indexes where tablespace_name != '&&Index' and index_type != 'LOB';
cursor lob_cur is select table_name, column_name from user_lobs where tablespace_name != '&&Index';
cursor ind2_cur is select index_name from user_indexes where status != 'VALID';
begin
for tab_val in tab_cur
loop
execute immediate 'alter table ' || tab_val.table_name || ' move tablespace &&Data';
end loop;
for ind_val in ind_cur
loop
execute immediate 'alter index ' || ind_val.index_name || ' rebuild tablespace &&Index';
end loop;
for lob_val in lob_cur
loop
execute immediate 'alter table ' || lob_val.table_name || ' move lob(' || lob_val.column_name || ') store as (tablespace &&Index)';
end loop;
-- some indexes need to be rebuilt a second time after their tablespace has been changed
for ind2_val in ind2_cur
loop
execute immediate 'alter index ' || ind2_val.index_name || ' rebuild';
end loop;
end;
/
-- exits SqlPlus
quit
Please notice that the script uses SqlPlus variables prefixed with &&: &&Data and &&Index. The former holds the name of the tablespace for Data (ie. tables and lobs) and the later the name of the tablespace for Indexes.
Sqlplus variables can also be defined with a single
&but in such case SqlPlus will ask for a value each time the variable is used. Sqlplus prompts for the value of variables defined with&&only once.
Use it¶
Connect to the server hosting the Oracle database or to a machine with remote access configured to the Oracle database for SqlPlus.
To change the tablespace for a specific user, connect to that user with SqlPlus and run the script.
You can do that in a single command line (e.g. for user MYUSER):
sqlplus MYUSER/MYUSER @change_tablespaces.sql
Sqlplus will prompt the user to provide a value for each variables (first &&Data, then &&Index). It is best to input the name of the tablespace in upper case.
Here is what it should look like.
%> sqlplus MYUSER/MYUSER @change_tablespace.sh
SQL*Plus: Release 11.2.0.3.0 Production on Thu Nov 13 13:02:49 2014
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
Enter value for data: CAA
old 2: cursor tab_cur is select table_name from user_tables where tablespace_name != '&&Data';
new 2: cursor tab_cur is select table_name from user_tables where tablespace_name != 'CAA';
Enter value for index: XAA
old 4: cursor ind_cur is select index_name from user_indexes where tablespace_name != '&&Index' and index_type != 'LOB';
new 4: cursor ind_cur is select index_name from user_indexes where tablespace_name != 'XAA' and index_type != 'LOB';
old 6: cursor lob_cur is select table_name, column_name from user_lobs where tablespace_name != '&&Index';
new 6: cursor lob_cur is select table_name, column_name from user_lobs where tablespace_name != 'XAA';
old 15: execute immediate 'alter table ' || tab_val.table_name || ' move tablespace &&Data';
new 15: execute immediate 'alter table ' || tab_val.table_name || ' move tablespace CAA';
old 22: execute immediate 'alter index ' || ind_val.index_name || ' rebuild tablespace &&Index';
new 22: execute immediate 'alter index ' || ind_val.index_name || ' rebuild tablespace XAA';
old 29: execute immediate 'alter table ' || lob_val.table_name || ' move lob(' || lob_val.column_name || ') store as (tablespace &&Index)';
new 29: execute immediate 'alter table ' || lob_val.table_name || ' move lob(' || lob_val.column_name || ') store as (tablespace XAA)';
PL/SQL procedure successfully completed.
SQL>
The bash script¶
Using the above script with SqlPlus directly has several issues coming from the fact it is interactive:
- it is open to typing errors and there is no way to double check
- it can not be scripted
So, here is a bash script which will solve these problems by wrapping the sqlplus command line.
The following script:
- assumes the
change_tablespace.sqlis available and in the same directory as the script - does not run until all parameters have been specified
- displays how to it should be used if at least one parameter is missing
- displays a message with the information gathered from the command line and ask for confirmation before proceeding
- could obviously be improved but does the job, feel free to customize it to meet your needs
- use several sqlplus options to avoid verbose SqlPlus logs (see comments in the script)
#!/bin/bash
if [ $# -lt 4 ]; then
echo "$(basename $0) <user_login> <user_pwd> <data_tablespace_name> <index_tablespace_name>"
exit 0
fi
function proceedOrExit() {
proceed="";
while true; do
case $proceed in
'y')
break;
;;
'n')
exit 0;
;;
*)
echo -n "Enter 'y' to proceed, 'n' to exit script : ";
read proceed;
;;
esac
done
}
user=$1
password=$2
data=$3
index=$4
echo "changing tablespace of user $user (password=$password)
- tablespace for data is $data
- tablespace for indexes is $index"
proceedOrExit
# -s argument suppresses sqlplus welcome and quit messages
sqlplus -s $user/$password << EOF
-- this suppresses the value substitution echoed by sqlplus
set verify off
define data = $data
define index = $index
@change_tablespace.sql
quit
EOF
exit 0
use it¶
Assuming you created a script called change_tablespace.sh (and made it executable chmod +x change_tablespace.sh), you can run the script with a command as the following:
./change_tablespace.sh MYUSER MYUSER CAA XAA
and you will get an output such as the following:
changing tablespace of user MYUSER (password=MYUSER)
- tablespace for data is CAA
- tablespace for indexes is XAA
Enter 'y' to proceed, 'n' to exit script : y
PL/SQL procedure successfully completed.