Re: Running Out of Tablespace

Re: Running Out of Tablespace

 

  

You can deallocate the freespace in a tablespace up to the highwater mark.
You can also exp/imp to coalesce
the extents and get all the freespace back at the end of the tablespace ...
below is a little script that I use to
see the blocks and where the freespace is ...

Change the value of "define ts='ININDX'" to your tablespace name ...

set pages 47 lines 132 verify off feedback off
--SET PAGES 25 LINES 132
column file_id heading 'File|id'
column value new_value dbblksiz noprint
column object format a25
column owner format a15
column meg format 9,999.99
COLUMN SEGMENT_NAME FORMAT A25
define ts='ININDX'
select value from v$parameter where name='db_block_size';
--spool D:\1\PRCHDATA_MAPPING.TXT
select
'free space' owner,
' ' object,
file_id,
block_id,
blocks,
(blocks*&dbblksiz)/(1024*1024) meg
from dba_free_space
where tablespace_name=upper('&&ts')
union
select
substr(owner,1,20),
substr(segment_name, 1,32) OBJECT,
file_id,
block_id,
blocks,
(blocks*&dbblksiz)/(1024*1024) meg
from dba_extents
where tablespace_name = upper('&&ts')
order by 3,4;
--spool off
undef ts
set pages 22 lines 80 verify on feedback on newpage 1
clear columns
ttitle off
set verify on
set feedback on
REM order by 4; -- This gives you order by block id


----- Original Message -----
From: "Przybyszewski Jaroslaw "
<oracledba-ezmlmshield-x11589636.[Email address protected]
To: "LazyDBA Discussion" <[Email address protected]
Sent: Thursday, February 01, 2007 9:20 AM
Subject: RE: Running Out of Tablespace


> I think you cannot rebuild table. You can only MOVE the table. The
> correct statement is:
>
> Alter table <table_name> move;
>
> And if you want to move it to another tablespace you should use:
>
> Alter table <table_name> move tablespace <tablespace_name>;
>
> Regards,
> Jarek
>
> -----Original Message-----
> From: Hudspith Adam
> [mailto:oracledba-ezmlmshield-x93118274.[Email address protected]
> Sent: Thursday, February 01, 2007 3:16 PM
> To: LazyDBA Discussion
> Subject: RE: Running Out of Tablespace
>
> Alter table <table_name> rebuild tablespace <tablespace_name>
>
> If it has any indexes you will need to rebuild them as well.
>
> Select index_name, tablespace_name from user_indexes where table_name =
> '<table_name>';
>
> Alter index <index_name> rebuild tablespace <tablespace_name>;
>
> You can also rebuild the table's indexes ONLINE (the method above is
> offline) ... but this will add complexity and it takes a lot longer to
> do it
> that way.
>
>
> Adam
>
> -----Original Message-----
> From: sean [mailto:oracledba-ezmlmshield-x78043104.[Email address
> protected]
> Sent: 01 February 2007 14:11
> To: LazyDBA Discussion
> Subject: Running Out of Tablespace
>
>
> I have a situation where I keep running out of space in my tablespace.
> This situation is caused by my "delete from <table>" statement in a
> script I am using and the space not freeing up in the table(s) like it
> does in a "truncate table" command.
>
> I am unable to do truncates on these table(s) as I need to maintain the
> data in these tables. How do I get back all the freespace in these
> tables without having to truncate them? What commands do I use in order
> to accomplish this?
>
> Thank you for your help in this matter!
>
> Sean
>
>
>
> ---------------------------------------------------------------------
> TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
> To post a dba job: http://jobs.lazydba.com
> To Subscribe : http://www.LazyDBA.com
> To unsubscribe: http://www.lazydba.com/unsubscribe.html
>
> ************************************************************************
> ****
> ****
> IMPORTANT NOTICE
>
> House of Fraser (Stores) Limited
> Registered office: Granite House, 31 Stockwell Street, Glasgow G1 4RZ
> Company Number: 10677
> Registered in Scotland
>
> If you have received this e-mail in error, please notify the sender and
> delete the e-mail and attachments immediately. This e-mail (including
> any
> attachments) may contain confidential and/or privileged information. If
> you
> are not the intended recipient, any reliance on, use, disclosure,
> dissemination, distribution or copying of the e-mail or attachments is
> strictly prohibited.
> We have checked for viruses but the content of an attachment may still
> contain software viruses, which could damage your computer system. We do
> not
> accept liability for any damage you sustain as a result of a virus
> introduced by this e-mail or attachment and you are advised to use
> up-to-date virus checking software. E-mail transmission cannot be
> guaranteed
> to be secure or error free.
> This e-mail is not intended nor should it be taken to create any legal
> relations, contractual or otherwise.
> To view our latest in-store news click on:
> http://www.houseoffraser.co.uk
>
> ************************************************************************
> ****
> ****
>
>
> ---------------------------------------------------------------------
> TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
> To post a dba job: http://jobs.lazydba.com
> To Subscribe : http://www.LazyDBA.com
> To unsubscribe: http://www.lazydba.com/unsubscribe.html
>
>
>
>
> ---------------------------------------------------------------------
> TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
> To post a dba job: http://jobs.lazydba.com
> To Subscribe : http://www.LazyDBA.com
> To unsubscribe: http://www.lazydba.com/unsubscribe.html
>
>
>


Oracle LazyDBA home page