Solved

Shrink tablespace

Posted on 2010-11-24
13
833 Views
Last Modified: 2012-06-21
After I truncate a table how do I regain the space. I have resized the datafile, coalesed the tablespace. I cannot issue Alter tablespace tablespace_name resize 12000M since it is not a big file tablespace.
0
Comment
Question by:msimons4
13 Comments
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 72 total points
ID: 34205368
what do you want to do, actually?
reclaim space from the tablespace? datafile? os file system?

when you to a truncate, the tablespace/datafile space IS reclaimed, however not from the os file system.

now, you usually don't resize a tablespace, but a datafile?!
what is the exact error/problem you get
0
 
LVL 6

Assisted Solution

by:anumoses
anumoses earned 356 total points
ID: 34205395
So, assuming your undo tablespace name is TBS1, you can ry the below
- Create a new undo tablespace as :
SQL> create undo tablespace TBS2 datafile '<complete file path>' size <smaller size>;

- Change parameter UNDO_TABLESPACE
SQL> alter system set UNDO_TABLESPACE=TBS2;

- Drop TBS1
SQL> drop tablespace TBS1 including contents and datafiles;

Try this
0
 

Author Comment

by:msimons4
ID: 34205409
I want to reclaim the OS space.
0
 

Author Comment

by:msimons4
ID: 34205430
It's not an undo tablespace. I don't want to drop it because I still have datafiles in it. I just want to reclaim the space on my OS from the table that I truncated.
0
 
LVL 73

Assisted Solution

by:sdstuber
sdstuber earned 72 total points
ID: 34205450
if you resized the datafile, then you have reclaimed the OS space
0
 
LVL 6

Assisted Solution

by:anumoses
anumoses earned 356 total points
ID: 34205495
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:msimons4
ID: 34205680
when I run

col "Tablespace" for a22
col "Used MB" for 99,999,999
col "Free MB" for 99,999,999
col "Total MB" for 99,999,999

select df.tablespace_name "Tablespace",
totalusedspace "Used MB",
(df.totalspace - tu.totalusedspace) "Free MB",
df.totalspace "Total MB",
round(100 * ( (df.totalspace - tu.totalusedspace)/ df.totalspace))
"Pct. Free"
from
(select tablespace_name,
round(sum(bytes) / 1048576) TotalSpace
from dba_data_files
group by tablespace_name) df,
(select round(sum(bytes)/(1024*1024)) totalusedspace, tablespace_name
from dba_segments
group by tablespace_name) tu
where df.tablespace_name = tu.tablespace_name
order by 3 desc;
 

I get for my first row:


Tablespace                 Used MB     Free MB    Total MB  Pct. Free
---------------------- ----------- ----------- ----------- ----------
tablespace_name                  12,765      14,735      27,500         54

So I should resize the datafile to 12,765 an I should reclaim 14,735 to the OS, is that correct?
0
 
LVL 6

Expert Comment

by:anumoses
ID: 34205708
Is this ( 14,735 )what you want to reclaim? Then YES
0
 

Author Comment

by:msimons4
ID: 34205741
When I issue that command I get the error:

ORA-03297: file contains used data beyond requested RESIZE value
0
 
LVL 6

Assisted Solution

by:anumoses
anumoses earned 356 total points
ID: 34205764
There is a Metalink Note: 380473.1.  You can refer
0
 
LVL 6

Assisted Solution

by:anumoses
anumoses earned 356 total points
ID: 34205790
If this is an index, just rebuild it and declare the target tablespace with the tablespace clause. In case it is a table you can use the alter table move command (in case if this contains no long columns) in case this table has long columns, issue an export/import command.
0
 
LVL 6

Assisted Solution

by:anumoses
anumoses earned 356 total points
ID: 34205803
0
 

Author Comment

by:msimons4
ID: 34206228
I ran the script:



REM Script is meant for Oracle version 9 and higher
REM -----------------------------------------------

set serveroutput on
exec dbms_output.enable(1000000);

declare

cursor c_dbfile is
select f.tablespace_name,f.file_name,f.file_id,f.blocks,t.block_size
from dba_data_files f,
dba_tablespaces t
where f.tablespace_name = t.tablespace_name
and t.status = 'ONLINE'
order by f.tablespace_name,f.file_id;

cursor c_freespace(v_file_id in number) is
select block_id, block_id+blocks max_block
from dba_free_space
where file_id = v_file_id
order by block_id desc;

/* variables to check settings/values */
dummy number;
checkval varchar2(10);
block_correction number;

/* running variable to show (possible) end-of-file */
file_min_block number;

/* variables to check if recycle_bin is on and if extent as checked is in ... */
recycle_bin boolean:=false;
extent_in_recycle_bin boolean;

/* exception handler needed for non-existing tables note:344940.1 */
sqlstr varchar2(100);
table_does_not_exist exception;
pragma exception_init(table_does_not_exist,-942);

begin

/* recyclebin is present in Oracle 10.2 and higher and might contain extent as checked */
begin
select value into checkval from v$parameter where name = 'recyclebin';
if checkval = 'on'
then
recycle_bin := true;
end if;
exception
when no_data_found
then
recycle_bin := false;
end;

/* main loop */
for c_file in c_dbfile
loop
/* initialization of loop variables */
dummy :=0;
extent_in_recycle_bin := false;
file_min_block := c_file.blocks;

begin


<<check_free>>

for c_free in c_freespace(c_file.file_id)
loop
/* if blocks is an uneven value there is a need to correct with -1 to compare with end-of-file which is even */
block_correction := (0-mod(c_free.max_block,2));
if file_min_block = c_free.max_block+block_correction
then

/* free extent is at end so file can be resized */
file_min_block := c_free.block_id;

else
/* no more free extent at end of file, file cannot be further resized */
exit check_free;
end if;
end loop;
end;

/* check if file can be resized, minimal size of file 16 blocks */
if (file_min_block = c_file.blocks) or (c_file.blocks <= 16)
then

dbms_output.put_line('Tablespace: '||c_file.tablespace_name||' Datafile: '||c_file.file_name);
dbms_output.put_line('cannot be resized no free extents found');
dbms_output.put_line('.');

else

/* file needs minimal no of blocks which does vary over versions */
if file_min_block < 16
then
file_min_block := 16;
end if;

dbms_output.put_line('Tablespace: '||c_file.tablespace_name||' Datafile: '||c_file.file_name);
dbms_output.put_line('current size: '||(c_file.blocks*c_file.block_size)/1024||'K'||' can be resized to: '||round((file_min_block*c_file.block_size)/1024)||'K (reduction of: '||round(((c_file.blocks-file_min_block)/c_file.blocks)*100,2)||' %)');

/* below is only true if recyclebin is on */
if recycle_bin
then
begin
sqlstr:='select distinct 1 from recyclebin$ where file#='||c_file.file_id;
execute immediate sqlstr into dummy;

if dummy > 0
then

dbms_output.put_line('Extents found in recyclebin for above file/tablespace');
dbms_output.put_line('Implying that purge of recyclebin might be needed in order to resize');
dbms_output.put_line('SQL> purge tablespace '||c_file.tablespace_name||';');
end if;
exception
when no_data_found
then null;
when table_does_not_exist
then null;
end;
end if;
dbms_output.put_line('SQL> alter database datafile '''||c_file.file_name||''' resize '||round((file_min_block*c_file.block_size)/1024)||'K;');
dbms_output.put_line('.');

end if;

end loop;

end;
/



And I get for the tablespace and datafile that showed 14735 free:

Tablespace: tablespace_name Datafile: datafile_path
cannot be resized no free extents found






0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now