Shrinking tablespaces.

[MarkWilliamson]

We are rapidly running out of disk space on our server and want to resize our tablespaces. There have been millons of records deleted from various tables and we would like tot reclaim the space.

I am an Oracle novice so could someone please advise on how this can be done ( or if it can ) ?

Thanks in advance to all who reply !!!

 

[SantaMufasa]

Mark,

Are you the Database Administrator despite your Oracle "noviceness"? To do all the tasks that you require, it will take DBA privileges on the database.

Some provisions to accomplish what you want are:
1) You need enough free disk space to hold the resulting, defragmented objects from your original badly fragmented tablespace. Once you have MOVEd all the objects from the old tablespace, you can "rm"/erase the files/disk space that the old tablespace consumed in order to re-use the freed-up space by other tablespaces or other applications. (See code, below, for how to MOVE your objects to defragment.)

2) The best results for defragmenting via MOVE-ing occur when you have the database to yourself, i.e., you can place the database in RESTRICTed mode.

Here is how you can resize and defrag your existing tablespaces:

1) Create a new, tablespace(s) that will replace the old tablespace(s):

CREATE TABLESPACE <name> DATAFILE <'file_name'> SIZE <nnM>
AUTOEXTEND ON NEXT <nnM> MAXSIZE <nnnnM>
EXTENT MANAGEMENT LOCAL AUTOALLOCATE;

2) Move your tables and indexes from the old tablespace to the new tablespace. The best/easiest method is to use a SQL script that creates a SQL script to do all your moving/rebuilding for you:

set echo off
set feedback off
accept old_tsname prompt "Enter the name of the old (source) tablespace: "
accept new_tsname prompt "Enter the name of the new (target) tablespace: "
set pagesize 0
set verify off
set trimspool on
spool TempMove&old_tsname..sql
prompt Set echo on
prompt set feedback off
select 'alter table '||owner||'.'||segment_name||' /* size: '||sum(bytes)||
' */ move parallel nologging tablespace &new_tsname;'
from dba_extents
where tablespace_name = upper('&old_tsname') and segment_type = 'TABLE'
group by owner,segment_name
order by sum(bytes) desc,owner, segment_name
/
select 'alter index '||owner||'.'||segment_name||' /* size: '||sum(bytes)||
' */ rebuild parallel tablespace &new_tsname;'
from dba_extents
where tablespace_name = upper('&old_tsname') and segment_type = 'INDEX'
group by owner,segment_name
order by owner, sum(bytes) desc, segment_name
/
prompt set feedback on
spool off
prompt
prompt Wrote 'TempMove&old_tsname..sql'
prompt
set pagesize 35

3) If you have any tables with LONG columns, they will not "MOVE"; you must export them, then import them. The following script does the following:
a) Creates scripted commands to export any remaining tables in the old tablespace that did not move as a result of the previous script. (Resulting script: "TempExp<old_tsname>.bat")
b) Creates scripted commands to drop the remaining tables from the old, tablespace and to modify the DEFAULT TABLESPACE for users from the old tablespace to the new tablespace. (Resulting script: "TempDropLongs<old_tsname>.sql")
c) Creates scripted commands to import the objects dumped into "TempExp<old_tsname>.bat". (Resulting script: "TempImp<old_tsname>.bat")

set pagesize 0
set trimspool on
set linesize 500
set feedback off
set verify off
set echo off
accept tsname prompt "Enter the tablespace name that holds the tables with LONGs: "
accept newtsname prompt "Enter the new, target tablespace name: "
accept syspw prompt "Enter the password for SYS: "
spool TempExp&tsname..bat
select 'exp buffer=15000000 compress=n grants=y feedback=1000 consistent=y '
||'file='
||owner||'.'||segment_name||'.dump tables='||owner||'.'||segment_name
||' userid=sys/&syspw@'||instance_name
from dba_extents, v$instance
where tablespace_name = upper('&tsname') and segment_type = 'TABLE'
group by owner,segment_name,instance_name
order by sum(bytes),owner,segment_name
/
spool off
prompt
prompt Wrote "TempExp&tsname..bat"
prompt
spool TempDropLongs&tsname..sql
prompt set echo on
prompt set feedback off
select 'drop table '||owner||'.'||table_name||' cascade constraints;'
from dba_tables
where tablespace_name = upper('&tsname')
order by owner,table_name
/
select 'alter user '||username||' default tablespace &newtsname;'
from dba_users
where default_tablespace = upper('&tsname')
order by username
/
spool off
prompt
prompt Wrote "TempDropLongs&tsname..sql"
prompt
spool TempImp&tsname..bat
select 'imp buffer=15000000 grants=y feedback=1000 full=Y file='
||owner||'.'||segment_name||'.dump '
||' userid=sys/&syspw@'||instance_name
from dba_extents, v$instance
where tablespace_name = upper('&tsname') and segment_type = 'TABLE'
group by owner,segment_name,instance_name
order by sum(bytes),owner,segment_name
/
spool off
set feedback on
set linesize 180
prompt
prompt 'Wrote "TempImp&tsname..bat"'
prompt

4) Successfully run script "TempExp<old_tsname>.bat".
Successfully run script "TempDropLongs<old_tsname>.sql".
Successfully run script "TempExp<old_tsname>.bat"
5) Drop the old, tablespace.
6) From the operating system prompt, rm/erase files that supported the old tablespace.

We do this sort of thing regularly. The TABLE MOVE and INDEX REBUILDS work remarkably quickly.

Let us know if this meets your need.

Mufasa
(aka Dave of Sandy, Utah, USA @ 17:52 (20Sep04) UTC (aka "GMT" and "Zulu"), 10:52 (20Sep04) Mountain Time)