| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Newbie - Need help increasing tablespace..
Hi
Your tablespace may have bubble fragmentation, lots of little free space between extents occupied by object segments, and even though you may have lots of space but it is not continous.
Try this to find out.
The following SQL*Plus script report will tell you whether you have fragmentation that is contiguous or not. If it is contiguous then you can use the following command to COALESCE the free space.
Run the script, check the fragmentation.
alter tablespace t_name coalesce;
Run the script and check if it has helped.
If it has helped then,do the following for all tablspaces ( not the tables ) except Oracle created tablespaces such as SYSTEM, RBS, TOOLS etc.
alter tablespace t_name default storage ( pctincrease 1 );
What this does is sets the pctincrease for tablespaces to other than 0, in which case the Oracle background process SMON, COALESCEs the contiguous free space autoamtically every time it wakes up which is about every couple of minutes or so.
Good luck !!!
Oracleguru
www.oracleguru.net
oracleguru_at_mailcity.com
set dof off pause off
/************************************************************************
* *
* AUTHOR : Oracleguru *
* FILE NAME : analyze_tablespace_usage.sql *
* LANGUAGE : SQL/SQL*Plus/PL-SQL *
* INPUT PARAM : This script expects 1 parameter: Tablespace_name *
* OUTPUT PARAM: None. *
* INPUT FILES : None. *
* OUTPUT FILES: $HOME/rep/analyze_tablespace_names_TNAME.lst *
* where TNAME = Tablespace_name entered on *
* command line to run this script *
* *
* DESCRIPTION : This script generates tablespace usage for a *
* TABLESPACE name entered on the command line. *
* *
************************************************************************/
set pause off termout off verify off wrap on serveroutput on size 1000000 set newpage 0 pagesize 58 linesize 80
/*
Generate tablespace analysis report only on Wednesdays. Uncomment the following statements if this is what you want to do.
whenever sqlerror exit
select 1/0
from dual
where to_char(sysdate, 'day') not in ('wednesday');
*/
clear breaks
column today new_value today noprint column time new_value time noprint
/*
Remove report file if exists, otherwise in case of problem, you may
receive a print out of an old file.
*/
host rm -f $HOME/rep/analyze_tablespace_usage_&1..lst
spool $HOME/rep/analyze_tablespace_usage_&1..lst
set feedback off
ttitle today center 'TABLE AND INDEX SPACE USAGE IN TABLESPACE &1' -
right 'Page ' format 990 sql.pno skip 1 -
time -
skip 1 -
analyze_tablespace_usage.sql
select to_char(sysdate, 'DD-MON-YYYY') today,
to_char(sysdate, 'HH:MI:SS AM') time from dual;
set feedback on
/*
Compute total and unused disk space in tablespace. */
DECLARE
total_blocks number; total_bytes number; unused_blocks number; unused_bytes number;/* Above High water mark */ last_used_extent_file_id number; last_used_extent_block_id number; last_used_block number; free_space number;/* Below High water mark */ user_id all_tables.owner%type; object_name all_tables.table_name%type; object_type varchar2 ( 5);--
cursor c1
is
select owner, table_name, 'TABLE'
from all_tables
where tablespace_name = upper('&1')
union
select owner, index_name, 'INDEX'
from all_indexes
where tablespace_name = upper('&1');
BEGIN
open c1;
LOOP
fetch c1
into user_id, object_name, object_type;
exit when c1%notfound;
dbms_space.unused_space
( user_id,
object_name,
object_type,
total_blocks,
total_bytes,
unused_blocks,
unused_bytes,
last_used_extent_file_id,
last_used_extent_block_id,
last_used_block
);
dbms_space.free_blocks
( user_id,
object_name,
object_type,
0,
free_space);
dbms_output.put_line
( object_type||' Name = '||user_id||'.'||object_name ); dbms_output.put_line
( '**********' );
dbms_output.put_line
( 'Total Bytes = '||to_char(total_bytes, '999,999,990')||
' Free Bytes above Water Mark = '||
to_char(unused_bytes,'999,999,990') );
dbms_output.put_line
( 'Bytes to W.Mark= '||to_char(total_bytes -
unused_bytes,'999,999,990')||
' Free Bytes below Water Mark = '||
to_char(free_space*8192, '999,999,990') );
dbms_output.put_line
(
'-------------------------------------------------------------------------
-------');
END LOOP;
END;
...
/
column object format a26 heading 'OBJECT' column file_id format 99990 heading 'FILE|ID ' column block_id format 999990 heading 'BLOCK|ID ' column blocks format 999990 heading 'BLOCKS' column bytes format 9,999,999,999 heading 'BYTES'
ttitle today center 'POSSIBLE FRAGMENTATION IN TABLESPACE &1' -
right 'Page ' format 990 sql.pno skip 1 -
time -
skip 2 -
analyze_tablespace_usage.sql skip 2
select 'freespace' owner, ' ' object,
file_id, block_id, blocks, bytes bytes
from sys.dba_free_space
substr(segment_name, 1, 31),
file_id, block_id, blocks, bytes bytes
from sys.dba_extents
break on bytes on report
compute sum of bytes on report
column segment_name format a26
column tablespace_name format a15
ttitle today center 'SPACE USAGE BY OBJECTS IN TABLESPACE &1' -
right 'Page ' format 990 sql.pno skip 1 -
time -
skip 2 -
analyze_tablespace_usage.sql skip 2
select segment_name, tablespace_name, sum(bytes) bytes
from sys.dba_extents
where tablespace_name = '&1'
group by segment_name, tablespace_name
/
set feedback off
column space heading ' SPACE' column name heading ' INDEX NAME' column btree_space format 9,999,999,999 heading 'TOTAL SPACE' column used_space format 9,999,999,999 heading 'USED SPACE'
ttitle today center 'USED AND FREE SPACE USAGE FOR TABLESPACE &1' -
right 'Page ' format 990 sql.pno skip 1 -
time -
skip 2 -
analyze_tablespace_usage.sql skip 2
select 'Free Space ' space, sum(bytes ) bytes
from sys.dba_free_space
where tablespace_name = upper('&1')
union
select 'Used Space ' space, sum(bytes ) bytes
from sys.dba_extents
where tablespace_name = upper('&1')
/
prompt
prompt
prompt. ********** END OF REPORT *********
spool off
exit
bmolish_at_my-dejanews.com wrote in article <6udv9p$lhg$1_at_nnrp1.dejanews.com>...
> > > > Our DBA has left, and I am the new admin person.. > We are using Oracle 7.34 on a NT 4.0 box. > > Currently when I run a SQL query I am getting the following error.. > > > ORA-01652: unable to extend temp segment by 1598 in tablespace BTS1_7NS3; > > When I check out the tablespace this is what I get.. > By running the following.. > > select > tablespace_name, > sum(bytes), > max(bytes), > count(*) > from dba_free_space > group by tablespace_name > order by tablespace_name; > > TABLESPACE_NAME SUM(BYTES) MAX(BYTES) COUNT(*) > ------------------------------ ---------- ---------- --------- > BTS1_7NS3 44541952 40589312 5 > SYNCHRO_7NS3 5855232 5640192 3 > SYSTEM 10455040 10455040 1 > > What is the exact command that I need to correct this error.. > > Thanks.. > > -----== Posted via Deja News, The Leader in Internet Discussion ==----- > http://www.dejanews.com/rg_mkgrp.xp Create Your Own Free Member Forum >Received on Fri Sep 25 1998 - 09:59:12 CDT
 |
 |