Comment récupérer de l'espace dans un tablespace ?

Un tablespace prenant trop d’ampleur ?

Suivez la procédure ci-dessous pour récupérer de l’espace :

set lines 170
set pages 500
SELECT t.tablespace_name,NVL (a.freesize, 0) FREEMB,b.sizemb, b.maxsizemb
FROM dba_tablespaces t
LEFT OUTER JOIN (
SELECT tablespace_name,ROUND (SUM (bytes) / 1024 / 1024)freesize
FROM dba_free_space
GROUP BY tablespace_name) a
ON a.tablespace_name = t.tablespace_name
JOIN (
SELECT tablespace_name,SUM (bytes) / 1024 / 1024 sizemb,SUM (maxbytes) / 1024 / 1024 maxsizemb
FROM dba_data_files
GROUP BY tablespace_name) b
ON b.tablespace_name = t.tablespace_name
WHERE t.contents = ‘PERMANENT’
AND t.status = ‘ONLINE’;

CHERCHER les segments qui pourraient faire l’objet d’une récupération d’espace : Remplacer les valeur des paramètres : task_name, task_desc, object_type, attr1

DECLARE
l_object_id NUMBER;
BEGIN
DBMS_ADVISOR.create_task (
advisor_name => ‘Segment Advisor’,
task_name => »,
task_desc => ‘Segment Advisor pour
‘);DBMS_ADVISOR.create_object (
task_name => »,
object_type => ‘TABLESPACE’,
attr1 => », —
attr2 => NULL,
attr3 => NULL,
attr4 => ‘null’,
attr5 => NULL,
object_id => l_object_id);DBMS_ADVISOR.set_task_parameter (
task_name => »,
parameter => ‘RECOMMEND_ALL’,
value => ‘TRUE’);DBMS_ADVISOR.execute_task(task_name => »);
END;
/

SELECT f.impact,
o.type AS object_type,
o.attr1 AS schema,
o.attr2 AS object_name,
f.message,
f.more_info
FROM dba_advisor_findings f
JOIN dba_advisor_objects o
ON f.object_id = o.object_id
AND f.task_name = o.task_name
WHERE f.task_name = »
AND f.message not like ‘%ORA-10663%’
AND f.message <> ‘The free space in the object is less than 10MB.’
ORDER BY f.task_name, f.impact DESC;SELECT f.task_name,
f.impact,
o.type AS object_type,
o.attr1 AS schema,
o.attr2 AS object_name,
f.message,
f.more_info
FROM dba_advisor_findings f
JOIN dba_advisor_objects o
ON f.object_id = o.object_id
AND f.task_name = o.task_name
WHERE f.task_name = »
ORDER BY f.task_name, f.impact DESC;

Extraire des rapports à partir de ces résultats :

==============================
Liste générale
==============================

col object_type format a10
col schema format a10
col object_name format a20
col message format a40
col reclaim_pct format a4spool ‘.lst’
SELECT
o.type AS object_type,
o.attr1 AS schema,
o.attr2 AS object_name,
f.message,
decode(instr(f.more_info,’:’,1,1),0, null,to_char(substr(f.more_info,instr(f.more_info,’:’,1,1)+1,instr(f.more_info,’:’,1,2)-1-instr(f.more_info,’:’,1,1)),’999G999G999G999′)) alloc,
decode(instr(f.more_info,’:’,1,1),0, null,to_char(substr(f.more_info,instr(f.more_info,’:’,1,3)+1,instr(f.more_info,’:’,1,4)-1-instr(f.more_info,’:’,1,3)),’999G999G999G999′)) used,
decode(instr(f.more_info,’:’,1,1),0, null,to_char(substr(f.more_info,instr(f.more_info,’:’,1,5)+1,instr(f.more_info,’:’,1,6)-1-instr(f.more_info,’:’,1,5)),’999G999G999G999′)) reclaim,
decode(instr(f.more_info,’:’,1,1),0, substr(f.more_info,1,instr(f.more_info,’percent’,1,1)-1),
round((substr(f.more_info,instr(f.more_info,’:’,1,5)+1,instr(f.more_info,’:’,1,6)-1-instr(f.more_info,’:’,1,5))/substr(f.more_info,instr(f.more_info,’:’,1,1)+1,instr(f.more_info,’:’,1,2)-1-instr(f.more_info,’:’,1,1))) * 100)) reclaim_PCT
FROM dba_advisor_findings f
JOIN dba_advisor_objects o
ON f.object_id = o.object_id
AND f.task_name = o.task_name
WHERE f.task_name = »
AND f.message not like ‘%ORA-10663%’
AND f.message <> ‘The free space in the object is less than 10MB.’
ORDER BY 7 DESC, 8 DESC;spool off

==================================
Liste shrink
==================================

col object_type format a10
col schema format a10
col object_name format a20
col message format a40
set pages 500
set lines 170
col reclaim_pct format a4
set echo offspool ‘.lst’SELECT
o.type AS object_type,
o.attr1 AS schema,
o.attr2 AS object_name,
f.message,
decode(instr(f.more_info,’:’,1,1),0, null,to_char(substr(f.more_info,instr(f.more_info,’:’,1,1)+1,instr(f.more_info,’:’,1,2)-1-instr(f.more_info,’:’,1,1)),’999G999G999G999′)) alloc,
decode(instr(f.more_info,’:’,1,1),0, null,to_char(substr(f.more_info,instr(f.more_info,’:’,1,3)+1,instr(f.more_info,’:’,1,4)-1-instr(f.more_info,’:’,1,3)),’999G999G999G999′)) used,
decode(instr(f.more_info,’:’,1,1),0, null,to_char(substr(f.more_info,instr(f.more_info,’:’,1,5)+1,instr(f.more_info,’:’,1,6)-1-instr(f.more_info,’:’,1,5)),’999G999G999G999′)) reclaim,
decode(instr(f.more_info,’:’,1,1),0, substr(f.more_info,1,instr(f.more_info,’percent’,1,1)-1),
round((substr(f.more_info,instr(f.more_info,’:’,1,5)+1,instr(f.more_info,’:’,1,6)-1-instr(f.more_info,’:’,1,5))/substr(f.more_info,instr(f.more_info,’:’,1,1)+1,instr(f.more_info,’:’,1,2)-1-instr(f.more_info,’:’,1,1))) * 100)) reclaim_PCT
FROM dba_advisor_findings f
JOIN dba_advisor_objects o
ON f.object_id = o.object_id
AND f.task_name = o.task_name
WHERE f.task_name = »
AND f.message not like ‘%ORA-10663%’
AND f.message <> ‘The free space in the object is less than 10MB.’
AND instr(f.more_info,’:’,1,1) > 0
ORDER BY 7 DESC, 8 DESC;spool off

RECUPERER de l’espace dans les segments :
ATTENTION ! Génère des archives log et de l’UNDO

ALTER TABLE …. SHRINK SPACE [COMPACT];
ALTER INDEX ….. SHRINK SPACE [COMPACT];

Blog

Comment récupérer de l'espace dans un tablespace ?

Articles récents