oracle 碎片的测试

-- 第一步：创建测试表
CREATE TABLE EMP_TEST (
    EMPLOYEE_ID   NUMBER PRIMARY KEY,
    FIRST_NAME    VARCHAR2(50),
    LAST_NAME     VARCHAR2(50),
    EMAIL         VARCHAR2(100),
    HIRE_DATE     DATE,
    DEPARTMENT_ID NUMBER,
    SALARY        NUMBER
) TABLESPACE USERS; -- 请确保使用你有权限的表空间

-- 第二步：插入测试数据并制造“碎片”
-- 先插入一批数据
BEGIN
  FOR i IN 1..10000 LOOP
    INSERT INTO EMP_TEST 
    VALUES (i, 
            'First_' || i, 
            'Last_' || i, 
            'email' || i || '@company.com',
            SYSDATE - MOD(i, 3650),
            MOD(i, 10) + 1,
            3000 + MOD(i, 20000)
           );
  END LOOP;
  COMMIT;
END;
/

-- 模拟删除操作，制造不连续的空洞（碎片）
DELETE FROM EMP_TEST WHERE MOD(EMPLOYEE_ID, 5) = 0; -- 删除20%的数据
COMMIT;

-- 再插入一些新数据，这些数据可能无法完全填充之前删除留下的空间
BEGIN
  FOR i IN 10001..12000 LOOP
    INSERT INTO EMP_TEST 
    VALUES (i, 
            'New_First_' || i, 
            'New_Last_' || i, 
            'new.email' || i || '@company.com',
            SYSDATE,
            MOD(i, 5) + 1,
            5000 + MOD(i, 15000)
           );
  END LOOP;
  COMMIT;
END;
/

-- 第三步：在LAST_NAME列上创建索引
CREATE INDEX IDX_EMP_TEST_LNAME ON EMP_TEST (LAST_NAME) TABLESPACE USERS;

set linesize 300
col ADVISOR_NAME for a40
col task_name for a40
SELECT task_id, task_name, advisor_name, created, status
FROM user_advisor_tasks;


-- 删除指定的任务
BEGIN
DBMS_ADVISOR.DELETE_TASK('advisor_test tab Advice'); -- 将此处替换为你的任务名
DBMS_OUTPUT.PUT_LINE('任务已删除。');
END;
/
commit;

 

 

-- 第四步：手动调用Segment Advisor分析该表（其上的索引也会被分析）
set serveroutput on
declare
  my_task_id number;
  obj_id number;
  my_task_name varchar2(100);
  my_task_desc varchar2(500);
begin
  my_task_name :='advisor_test tab Advice';   --执行任务名，能够随意指定，只是建议为有意义的名称
  my_task_desc :='Manual Segment Advisor Run';--执行任务描写叙述，能够随意指定，只是建议为有意义的描写叙述
  dbms_advisor.create_task(
  advisor_name => 'Segment Advisor', 
  task_id => my_task_id,
  task_name => my_task_name,
  task_desc =>my_task_desc);

  dbms_advisor.create_object(
  task_name=>my_task_name,
  object_type=>'TABLE',       
  attr1=>user,                
  attr2 => 'EMP_TEST',        
  attr3 => NULL,
  attr4=>null,
  attr5=>null,
  object_id=>obj_id);

  dbms_advisor.set_task_parameter(
  task_name => my_task_name,
  parameter=>'recommend_all', 
  value=>'TRUE');

  dbms_advisor.execute_task(my_task_name);
end;
/


    
SQL> select 
  2    /* "|chr(13)||chr(10)"为windows平台的换行符，假设是linux等其他平台，请用"chr(10)"取代 */
  3   'Task name     :'||f.task_name||chr(13)||chr(10)||  
  4   'Segment name  :'||o.attr2    ||chr(13)||chr(10)||
  5   'Sement type   :'||o.type     ||chr(13)||chr(10)||
  6   'partition name:'||o.attr3    ||chr(13)||chr(10)||
  7   'Message       :'||f.message  ||chr(13)||chr(10)||
  8   'More info     :'||f.more_info TASK_ADVICE
  9   from dba_advisor_findings f,dba_advisor_objects o
 10  where o.task_id=f.task_id
 11    and o.object_id=f.object_id
 12    and f.task_name = 'advisor_test tab Advice'
 13  order by f.task_name;

TASK_ADVICE
--------------------------------------------------------------------------------
Task name     :advisor_test tab Advice
Segment name  :EMP_TEST
Sement type   :TABLE
partition name:
Message       :The free space in the object is less than 10MB.
More info     :Allocated Space:917504: Used Space:764401: Reclaimable Space :153
103:


SQL> 



#另外的方案：

CREATE TABLE y_test_t ( id NUMBER, name VARCHAR2(50));
CREATE INDEX test_t_n1_i ON y_test_t( id );

begin
    for i in 1 .. 1000000 loop
    insert INTO y_test_t(id, name) VALUES(i, 'name' || i);
    end loop;
    commit;
end;
/


analyze index IDX_EMP_TEST_LNAME validate structure online;

SELECT t.name as 索引名称,
       t.height as 高度, -- >=4时建有整理
       t.pct_used as 使用空间百分比, -- <50%时建有整理
       t.del_lf_rows_len as 被删除行的长度,
       t.lf_rows_len as 叶子行的总长度,
       round(( t.del_lf_rows_len / t.lf_rows_len ), 4)* 100 || '%' AS 碎片率 -- >20%时建有整理
FROM index_stats t
WHERE t.name = 'IDX_EMP_TEST_LNAME';




关键如下：
set serveroutput on
declare
  my_task_id number;
  obj_id number;
  my_task_name varchar2(100);
  my_task_desc varchar2(500);
begin
  my_task_name :='advisor_test tab Advice';   
  my_task_desc :='Manual Segment Advisor Run';

  dbms_advisor.create_task(
  advisor_name => 'Segment Advisor', 
  task_id => my_task_id,
  task_name => my_task_name,
  task_desc =>my_task_desc);

  dbms_advisor.create_object(
  task_name=>my_task_name,
  object_type=>'TABLE',       
  attr1=>user,                
  attr2 => 'EMP_TEST',        
  attr3 => NULL,
  attr4=>null,
  attr5=>null,
  object_id=>obj_id);

  dbms_advisor.set_task_parameter(
  task_name => my_task_name,
  parameter=>'recommend_all', 
  value=>'TRUE');

  dbms_advisor.execute_task(my_task_name);
end;
/



set serveroutput on
declare
v_unformatted_blocks number;
v_unformatted_bytes number;
v_fs1_blocks number;
v_fs1_bytes number;
v_fs2_blocks number;
v_fs2_bytes number;
v_fs3_blocks number;
v_fs3_bytes number;
v_fs4_blocks number;
v_fs4_bytes number;
v_full_blocks number;
v_full_bytes number;
begin
dbms_space.space_usage ('<schema>', '<table name>', 'TABLE', v_unformatted_blocks,
v_unformatted_bytes, v_fs1_blocks, v_fs1_bytes, v_fs2_blocks, v_fs2_bytes,
v_fs3_blocks, v_fs3_bytes, v_fs4_blocks, v_fs4_bytes, v_full_blocks, v_full_bytes);
dbms_output.put_line('Unformatted Blocks = '||v_unformatted_blocks);
dbms_output.put_line('FS1 Blocks = '||v_fs1_blocks);
dbms_output.put_line('FS2 Blocks = '||v_fs2_blocks);
dbms_output.put_line('FS3 Blocks = '||v_fs3_blocks);
dbms_output.put_line('FS4 Blocks = '||v_fs4_blocks);
dbms_output.put_line('Full Blocks = '||v_full_blocks);
end;
/