Shrink tablespace

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.
msimons4Asked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
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
 
anumosesConnect With a Mentor Commented:
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
 
msimons4Author Commented:
I want to reclaim the OS space.
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
msimons4Author Commented:
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
 
sdstuberConnect With a Mentor Commented:
if you resized the datafile, then you have reclaimed the OS space
0
 
msimons4Author Commented:
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
 
anumosesCommented:
Is this ( 14,735 )what you want to reclaim? Then YES
0
 
msimons4Author Commented:
When I issue that command I get the error:

ORA-03297: file contains used data beyond requested RESIZE value
0
 
anumosesConnect With a Mentor Commented:
There is a Metalink Note: 380473.1.  You can refer
0
 
anumosesConnect With a Mentor Commented:
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
 
anumosesConnect With a Mentor Commented:
0
 
msimons4Author Commented:
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
All Courses

From novice to tech pro — start learning today.