###################################################################################################################################################### # Procedimentos e pacotes relevantes para o Segment Advisor - DBMS_ADVISOR # ###################################################################################################################################################### CREATE_TASK : Criar uma tarefa para o Segment Advisor. Especifique "Segment Advisor", como o valor do parâmetro ADVISOR_NAME. CREATE_OBJECT : Cria a indentificação para o objeto-alvo que sera analisado pelo Segment Advisor. Os valores dos parâmetros deste processo depende do tipo de objeto. Na tabela "Entrada para DBMS_ADVISOR.CREATE_OBJECT" estão os valores dos parâmetros para cada tipo de objeto. SET_TASK_PARAMETER : Parâmetros de entrada do processo, estão listados na tabela "Entrada para DBMS_ADVISOR.SET_TASK_PARAMETER". EXECUTE_TASK : Executa a tarefa para análise do Segment Advisor ###################################################################################################################################################### # Entrada para DBMS_ADVISOR.CREATE_OBJECT # ###################################################################################################################################################### |------------------------------|------------------------|-------------------------------|-------------------------------|----------------------------| | OBJECT_TYPE | ATTR1 | ATTR2 | ATTR3 | ATTR4 | |------------------------------|------------------------|-------------------------------|-------------------------------|----------------------------| | TABLESPACE | tablespace name | NULL | NULL | Unused. Specify NULL. | |------------------------------|------------------------|-------------------------------|-------------------------------|----------------------------| | TABLE | schema name | table name | NULL | Unused. Specify NULL. | |------------------------------|------------------------|-------------------------------|-------------------------------|----------------------------| | INDEX | schema name | index name | NULL | Unused. Specify NULL. | |------------------------------|------------------------|-------------------------------|-------------------------------|----------------------------| | TABLE PARTITION | schema name | table name | table partition name | Unused. Specify NULL. | |------------------------------|------------------------|-------------------------------|-------------------------------|----------------------------| | INDEX PARTITION | schema name | index name | index partition name | Unused. Specify NULL. | |------------------------------|------------------------|-------------------------------|-------------------------------|----------------------------| | TABLE SUBPARTITION | schema name | table name | table subpartition name | Unused. Specify NULL. | |------------------------------|------------------------|-------------------------------|-------------------------------|----------------------------| | INDEX SUBPARTITION | schema name | index name | index subpartition name | Unused. Specify NULL. | |------------------------------|------------------------|-------------------------------|-------------------------------|----------------------------| | LOB | schema name | segment name | NULL | Unused. Specify NULL. | |------------------------------|------------------------|-------------------------------|-------------------------------|----------------------------| | LOB PARTITION | schema name | segment name | lob partition name | Unused. Specify NULL. | |------------------------------|------------------------|-------------------------------|-------------------------------|----------------------------| | LOB SUBPARTITION | schema name | segment name | lob subpartition name | Unused. Specify NULL. | |------------------------------|------------------------|-------------------------------|-------------------------------|----------------------------| ###################################################################################################################################################### # Entrada para DBMS_ADVISOR.SET_TASK_PARAMETER # ###################################################################################################################################################### |------------------------------|----------------------------------------------|-----------------------------------------------------|----------------| | Input Parameter | Description | Possible Values | Default Value | |------------------------------|----------------------------------------------|-----------------------------------------------------|----------------| | time_limit | The time limit for the Segment Advisor run, | Any number of seconds | UNLIMITED | | | specified in seconds. | | | |------------------------------|----------------------------------------------|-----------------------------------------------------|----------------| | | | TRUE: Findings are generated on all segments | | | | | specified, whether or not space reclamation is | | | recommend_all | Whether the Segment Advisor should generate | recommended. | TRUE | | | findings for all segments. | FALSE: Findings are generated only for those | | | | | objects that generate recommendations for space | | | | | reclamation. | | |------------------------------|----------------------------------------------|-----------------------------------------------------|----------------| ###################################################################################################################################################### # Exemplo - Segment Advisor ( TABLESPACE ) # ###################################################################################################################################################### variable id number; begin declare name varchar2(100); descr varchar2(500); obj_id number; begin name:='tablespace_imagem'; descr:='Segment Advisor Tablespace'; dbms_advisor.create_task ( advisor_name => 'Segment Advisor', task_id => :id, task_name => name, task_desc => descr); dbms_advisor.create_object ( task_name => name, object_type => 'TABLESPACE', attr1 => 'IMAGEM', attr2 => NULL, attr3 => NULL, attr4 => NULL, attr5 => NULL, object_id => obj_id); dbms_advisor.set_task_parameter( task_name => name, parameter => 'recommend_all', value => 'TRUE'); dbms_advisor.execute_task(name); end; end; / ###################################################################################################################################################### # Verifica o status da tarefa # ###################################################################################################################################################### select task_name, status from dba_advisor_tasks where advisor_name = 'Segment Advisor'; TASK_NAME STATUS ------------------------------ ----------- tablespace_imagem COMPLETED ###################################################################################################################################################### # Resultado da análise do Segment Advisor # ###################################################################################################################################################### set lines 200 col PARTITION for a20 col SEGNAME for a20 col SEGNAME for a20 col MESSAGE for a60 col TYPE for a20 select af.task_name, ao.attr2 segname, ao.attr3 partition, ao.type, af.message from dba_advisor_findings af, dba_advisor_objects ao where ao.task_id = af.task_id and ao.object_id = af.object_id and af.task_name = 'tablespace_imagem'; set lines 200 col PARTITION for a20 col SEGNAME for a20 col SEGNAME for a20 col MESSAGE for a60 col TYPE for a20 select af.task_name, ao.attr2 segname, ao.attr3 partition, ao.type, af.message from dba_advisor_findings af, dba_advisor_objects ao where ao.task_id = af.task_id and ao.object_id = af.object_id and af.task_name = 'tablespace_imagem' and af.message not like '%is%less%than%10MB%'; TASK_NAME SEGNAME PARTITION TYPE MESSAGE ------------------------ -------------------- -------------- ------------ ------------------------------------------------------------ tablespace_imagem TESTE_ATUAL TABLE Ative a movimentacao de linha da tabela SYS.TESTE_ATUAL e fa ca uma compactacao; a economia estimada e de 43172584 bytes. tablespace_imagem LOGADOS TABLE O espaco livre nos objetos e menor que 10MB. tablespace_imagem TESTE TABLE O espaco livre nos objetos e menor que 10MB. tablespace_imagem PK_NOME INDEX O espaco livre nos objetos e menor que 10MB. tablespace_imagem PK_DEPT INDEX O espaco livre nos objetos e menor que 10MB. tablespace_imagem PK_EMP INDEX O espaco livre nos objetos e menor que 10MB. 43172584/1024/1024 = 41MB ###################################################################################################################################################### # Recomendações - DBMS_SPACE.ASA_RECOMMENDATIONS # ###################################################################################################################################################### set lines 200 col TABLESPACE_NAME for a15 col SEGMENT_NAME for a15 col SEGMENT_TYPE for a15 col PARTITION_NAME for a15 col RECOMMENDATIONS for a50 select tablespace_name, segment_name, segment_type, partition_name, recommendations, c1 from table(dbms_space.asa_recommendations('FALSE', 'FALSE', 'FALSE')); TABLESPACE_NAME SEGMENT_NAME SEGMENT_TYPE PARTITION_NAME RECOMMENDATIONS C1 --------------- ------------ ------------ -------------- --------------------------------------- -------------------------------------------------- IMAGEM TESTE_ATUAL TABLE Ative a movimentacao de linha da tabela alter table "SYS"."TESTE_ATUAL" shrink space SYS.TESTE_ATUAL e faca uma compactacao; a economia estimada e de 43172584 bytes. ###################################################################################################################################################### # Removendo a tarefa # ###################################################################################################################################################### exec dbms_advisor.delete_task('tablespace_imagem');