Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 855
  • Last Modified:

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.
0
msimons4
Asked:
msimons4
7 Solutions
 
Guy Hengel [angelIII / a3]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
 
anumosesCommented:
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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
 
sdstuberCommented:
if you resized the datafile, then you have reclaimed the OS space
0
 
anumosesCommented:
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
 
anumosesCommented:
There is a Metalink Note: 380473.1.  You can refer
0
 
anumosesCommented:
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
 
anumosesCommented:
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

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now