Thứ Tư, 20 tháng 11, 2019

Quản lý dữ liệu LOB cơ sở dữ liệu Oracle

Dữ liệu LOB rất khó quản lý nhưng nếu hiểu bản chất và thao tác 1 vài lần thì lại đơn giản, sau khi đọc xong bài viết này bạn đã có trong tay các câu lệnh thường dùng nhất về LOB:
Kết quả hình ảnh cho lob oracle database

CHECK
-- D/s View
select * from dba_lobs;
select * from dba_lob_partitions;
select * from dba_lob_subpartitions;
select * from dba_segments where segment_type like '%LOB%';

-- Size segment_name, table, tablespace
select c.*, d.tablespace_name from (select a.owner,b.table_name,b.column_name, a."GB", b.segment_name from (select owner, segment_Name, round(sum(bytes)/1024/1024/1024,2) "GB" from dba_segments
where
--owner in ('BINHTV_OWNER') and
segment_type like '%LOB%'
--and tablespace_name='DATA_LOB'
group by owner, segment_name
order by owner, segment_name) a,
(select owner, table_name, column_name,segment_name from dba_lobs
where table_name not like '%$'
--where owner in ('BINHTV_OWNER')
group by owner, table_name,column_name, segment_name) b
where a.owner=b.owner and a.segment_Name=b.segment_name and a.gb>10) c, (select distinct owner, segment_name, tablespace_name from dba_segments) d
where c.owner=d.owner and c.segment_name=d.segment_name
order by d.tablespace_name,c.owner,c."GB";

-- Theo doi tang truong TBS
SELECT  a.tablespace_name,100 - ROUND ( (NVL (b.bytes_free, 0) / a.bytes_alloc) * 100) "%Usage",   ROUND
(a.bytes_alloc / 1024 / 1024) "Size MB",   ROUND (NVL (b.bytes_free, 0) / 1024 / 1024) "Free MB",
      (ROUND (a.bytes_alloc / 1024 / 1024)- ROUND (NVL (b.bytes_free, 0) / 1024 / 1024)) "Used MB", ROUND ( (NVL (b.bytes_free, 0) / a.bytes_alloc) * 100) "%Free", ROUND (maxbytes / 1048576)  "Max MB",
       ROUND (ROUND ( (a.bytes_alloc - NVL (b.bytes_free, 0)) / 1024 / 1024)/  ROUND (maxbytes / 1048576) * 100) "%Used of Max"
  FROM (  SELECT f.tablespace_name, SUM (f.bytes) bytes_alloc,  SUM (DECODE (f.autoextensible, 'YES', f.maxbytes, 'NO', f.bytes)) maxbytes
            FROM dba_data_files f
        GROUP BY tablespace_name) a,
       (  SELECT f.tablespace_name, SUM (f.bytes) bytes_free  FROM dba_free_space f  GROUP BY tablespace_name) b
 WHERE a.tablespace_name = b.tablespace_name(+) and a.tablespace_name in ('DATA_LOB')
 order by "%Used of Max" desc;


-- D/s segment_name cua table
select a.*, b.* from (select owner, segment_Name, round(sum(bytes)/1024/1024/1024,2) "GB" from dba_segments
where
--owner in ('BINHTV_OWNER') and
segment_type like '%LOB%'
--and tablespace_name='DATA_LOB'
group by owner, segment_name
order by owner, segment_name) a,
(select owner, table_name, column_name,segment_name from dba_lobs
where table_name not like '%$'
--where owner in ('BINHTV_OWNER')
group by owner, table_name,column_name, segment_name) b
where a.owner=b.owner and a.segment_Name=b.segment_name
order by a."GB" desc;

--Check thong tin phan vung LOB
select * from dba_segments where
--owner in ('BINHTV_OWNER') and
segment_type like '%LOB%' 
--and tablespace_name='DATA_LOB'
order by owner, segment_name, partition_name;

-- Size LOB: 
select round(sum(bytes)/1024/1024/1024,2) "GB" from dba_segments where
--owner in ('BINHTV_OWNER') and
segment_type like '%LOB%' 
--and tablespace_name='DATA_LOB'
order by owner, segment_name, partition_name;

-- Size theo TBS DATA_LOB , DATA   , CCGW_CLOB  
select tablespace_name,round(sum(bytes)/1024/1024/1024,2) "GB" from dba_segments where
--owner in ('BINHTV_OWNER') and
segment_type like '%LOB%' 
--and tablespace_name='DATA_LOB'
group by tablespace_name
order by "GB" desc;

-- Size >100G theo segment_name
select owner, segment_Name, round(sum(bytes)/1024/1024/1024,2) "GB" from dba_segments
where
--owner in ('BINHTV_OWNER') and
segment_type like '%LOB%'
--and tablespace_name='DATA_LOB'
group by owner, segment_name
order by "GB" desc;
Truy xuất CLOB
declare
    l_data varchar2(10000);
    l_clob clob;
begin
    select in_message into l_clob from CUS_OWNER.API_AUDIT_LOG where id=8108941;
    l_data := dbms_lob.substr( l_clob, 4245, 1 );
    dbms_output.put_line( 'length = ' || length(l_data) );
    dbms_output.put_line( 'l_data = ' || l_data );
end;
SELECT dữ liệu LOB qua DBLink
(ORA-22992: cannot use LOB locators selected from remote tables)
P1: Tạo bảng tạm select dữ liệu liệu qua DB link
Create table xxx_request select * from test.request@dblink1
Select * from  xxx_request;
P2: Tạo mview
Create MATERIALIZED  VIEW mv_request
REFRESH FAST START WITH SYSDATE
   NEXT SYSDATE + 1
     AS select * from test.request@dblink1;
Tạo bảng có trường LOB
CREATE TABLE COMMON.TEST_AAA2_ARH
(
  ID           INTEGER,
  TEKST        VARCHAR2(200 CHAR),
  UPDATESTAMP  DATE,
  OBJEKAT      CLOB
)
LOB (OBJEKAT) STORE AS SECUREFILE (
  TABLESPACE COMMON_ARCHIVE

  ENABLE       STORAGE IN ROW
  CHUNK       8192
  RETENTION
  NOCACHE
  LOGGING)
TABLESPACE COMMON_ARCHIVE
LOGGING
PARTITION BY RANGE (UPDATESTAMP)
(
  PARTITION P_201012 VALUES LESS THAN (TO_DATE(' 2011-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    LOGGING
    NOCOMPRESS
    TABLESPACE COMMON_DATA
    LOB (OBJEKAT) STORE AS (
      TABLESPACE USERS
      ENABLE       STORAGE IN ROW
      CHUNK       8192
      PCTVERSION  10
      NOCACHE
      LOGGING),
  PARTITION P_201101 VALUES LESS THAN (TO_DATE(' 2011-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    LOGGING
    NOCOMPRESS
    TABLESPACE COMMON_DATA
    LOB (OBJEKAT) STORE AS (
      TABLESPACE USERS
      ENABLE       STORAGE IN ROW
      CHUNK       8192
      PCTVERSION  10
      NOCACHE
      LOGGING),
  PARTITION P_MAXVALUE VALUES LESS THAN (MAXVALUE)
    LOGGING
    NOCOMPRESS
    TABLESPACE COMMON_ARCHIVE
    LOB (OBJEKAT) STORE AS (
      TABLESPACE COMMON_ARCHIVE
      ENABLE       STORAGE IN ROW
      CHUNK       8192
      PCTVERSION  10
      NOCACHE
      LOGGING)
)
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;
 
Move LOB
·         Index luôn đi kèm dữ liệu LOB do đó không cần rebuild lại index
·         Bng non-partition: Khong can rebuild index
ALTER TABLE TEST_USER.TEST_LOB TABLESPACE TBS_LOB
LOB (VALUE) STORE AS SYS_LOB0000249937C00004$$
(TABLESPACE DATA_LOB);

alter table t move lob(y) store as ( tablespace users ) 

--Script chuyen lab non-partition
SELECT 'ALTER TABLE ' || owner ||'.'||TABLE_NAME||' MOVE TABLESPACE TBS_LOB'||CHR(10)||
                   'LOB ('||COLUMN_NAME||') STORE AS '||SEGMENT_NAME||CHR(10)||
                   '(TABLESPACE LOB);' SQL_STATEMENTS FROM DBA_LOBS
                   WHERE                   TABLESPACE_NAME ='TBS_LOB';       

·         Bng partition: Khong can rebuild index
alter table TEST_USER.TEST_LOB_PART move partition DATA20171008 lob (REQ_CONTENT) store as  (tablespace LOB);

-- Script chuyển các partiton của bng partition
SELECT 'alter table ' || TABLE_OWNER || '.' || TABLE_NAME || ' move partition ' || partition_name || ' lob (' || COLUMN_NAME || ') store as  (tablespace LOB);'
FROM DBA_LOB_PARTITIONS
WHERE TABLE_OWNER = 'TEST_OWNER'
and (table_name='TEST_LOB_PART ' or table_name='TEST_LOB_PART2') and partition_name like 'DATA2017%'
--AND TABLESPACE_NAME = 'TBS_LOB'
AND SECUREFILE='NO';

Thủ tục xóa dữ liệu LOB cũ
declare
    v_date date:=sysdate;
    date_num1t        INT := 31;                         -- Chi luu giu 31 ngay, TEST_TAB_1m,..
    date_num2t        INT := 62;                         -- Chi luu giu 62 ngay, TEST_TAB_2m,...
    date_num3t        INT := 93;
    date_num6t        INT := 186;                        -- Chi luu giu 180 ngay, TEST_TAB_6m.,,,
    --date_num4        INT := 300;                        --log dau noi, TEST_TAB_10m

--    CURSOR c_partition
--    IS
--        SELECT   table_name, partition_name
--          FROM   user_tab_partitions
--         WHERE   (sysdate - to_date(SUBSTR(partition_name,length(partition_name)-7,8),'yyyy/mm/dd') > date_num1t
--                    AND table_name IN ('TEST_TAB_1m'))
--                 OR (sysdate - to_date(SUBSTR(partition_name,5,6),'yyyymm') > date_num2t
--                    AND table_name IN ('TEST_TAB_2m'))
--                 OR (sysdate - to_date(SUBSTR(partition_name,length(partition_name)-7,8),'yyyy/mm/dd') > date_num3t
--                    AND table_name IN ('TEST_TAB_3m'))
--                 OR (sysdate - to_date(SUBSTR(partition_name,length(partition_name)-7,8),'yyyy/mm/dd') > date_num6t
--                    AND table_name IN ('TEST_TAB_6m'))
--                 OR (sysdate - to_date(SUBSTR(partition_name,5,6),'yyyymm') > date_num6t
--                    AND table_name IN ('TEST_TAB_6m'))
--                 OR (sysdate - to_date(SUBSTR(partition_name,length(partition_name)-7,8),'yyyy/mm/dd') > 366
--                    AND table_name IN ('TEST_TAB_1Y'));
    CURSOR c_app1
    IS       
          select  *
            from dba_segments where segment_name in ('TEST_LOB')
            and to_date(substr(partition_name,5,8),'yyyymmdd')<=to_date(to_char(sysdate-30,'yyyymmdd'),'yyyymmdd');

    cursor c_app2
    is
        select *
        from dba_segments where segment_name in ('TEST_LOB2')
        and to_date(substr(partition_name,5,6),'yyyymm')<to_date(to_char(sysdate-30,'yyyymm'),'yyyymm');
    v_sql_command   VARCHAR2 (2400);

BEGIN
  /*  

    FOR v_data1 IN c_partition
    LOOP
        BEGIN
            EXECUTE IMMEDIATE   ' Alter table '
                             || v_data1.table_name
                             || ' drop partition '
                             || v_data1.partition_name;
                            
            dbms_output.put_line(' Alter table '
                             || v_data1.table_name
                             || ' drop partition '
                             || v_data1.partition_name);              
        EXCEPTION
            WHEN OTHERS
            THEN
                DBMS_OUTPUT.put_line (SQLERRM);
        END;
    END LOOP;*/

    FOR v_data2 IN c_app
    LOOP
        BEGIN
            EXECUTE IMMEDIATE    'alter table ' || v_data2.owner ||'.' || v_data2.segment_name || ' truncate partition ' || v_data2.partition_name ;
            dbms_output.put_line('alter table ' || v_data2.owner ||'.' || v_data2.segment_name || ' truncate partition ' || v_data2.partition_name );
            EXECUTE IMMEDIATE    'alter table ' || v_data2.owner ||'.' || v_data2.segment_name || ' drop partition ' || v_data2.partition_name ;
            dbms_output.put_line('alter table ' || v_data2.owner ||'.' || v_data2.segment_name || ' drop partition ' || v_data2.partition_name);
        EXCEPTION
            WHEN OTHERS
            THEN
                DBMS_OUTPUT.put_line (SQLERRM);
        END;
    END LOOP; 
   
    FOR v_data3 IN c_app2
    LOOP
        BEGIN
            EXECUTE IMMEDIATE    'alter table ' || v_data3.owner ||'.' || v_data3.segment_name || ' truncate partition ' || v_data3.partition_name ;
            dbms_output.put_line('alter table ' || v_data3.owner ||'.' || v_data3.segment_name || ' truncate partition ' || v_data3.partition_name );
            EXECUTE IMMEDIATE    'alter table ' || v_data3.owner ||'.' || v_data3.segment_name || ' drop partition ' || v_data3.partition_name ;
            dbms_output.put_line('alter table ' || v_data3.owner ||'.' || v_data3.segment_name || ' drop partition ' || v_data3.partition_name );
        EXCEPTION
            WHEN OTHERS
            THEN
                DBMS_OUTPUT.put_line (SQLERRM);
        END;
    END LOOP; 
END;
/

Có thể đặt Scheduler Job để tự dộng xóa hàng ngày vào 00h

Muốn thu hồi dung lượng luôn thì dùng script:

1. Chạy thủ tục
declare
   cursor c1 is select tablespace_name,file_id,max(block_id) mbid from dba_extents
--   where tablespace_name in ('DATA')
   group by tablespace_name,file_id;
   fs number;
   fn varchar2(100);
   ts number;
   x number;
   gtot number:=0;
   tffs number:=0;
   ffrags number:= 0;
   begin
   for v1 in c1 loop
      select sum(bytes)/1024/1024 into fs from dba_free_space where tablespace_name = v1.tablespace_name and
           file_id = v1.file_id and block_id>v1.mbid;
           select file_name,bytes/1024/1024 into fn,ts from dba_data_files where file_id=v1.file_id and tablespace_name=v1.tablespace_name;
           select sum(bytes/1024/1024) into tffs from
           (select a.bytes/1024/1024 as bytes from dba_free_space a where file_id=v1.file_id and tablespace_name=v1.tablespace_name
            union all
            select 0.000001 as bytes from dual);
   x:=(ts-fs)+64;
   if (ts>x) and (x>10) then
   dbms_output.put_line('alter database datafile ' || '''' || fn || '''' || ' resize ' ||  round(x+1,0) || 'm;' || '--total size' || round(ts) || 'm;' );
   --EXECUTE IMMEDIATE 'alter database datafile ' || '''' || fn || '''' || ' resize ' ||  round(x) || 'm;' || '--total size' || round(ts) || 'm;';
   gtot :=  gtot + (ts-x);
   end if;

   ffrags := nvl(tffs - fs,0);
   dbms_output.put_line('alter database datafile ' || '''' || fn || '''' || ' resize ' ||  round(x+1,0) || 'm;' || '--frag size' || round(ffrags) || 'm;');
   --EXECUTE IMMEDIATE 'alter database datafile ' || '''' || fn || '''' || ' resize ' ||  round(x) || 'm;' || '--frag size' || round(ffrags) || 'm;';
   end loop;

   dbms_output.put_line('Total space reclaimation:' || gtot || 'MB');
  end;

2. Lấy script ra chạy từng bước 1
Chuyển từ LONG sang CLOB
--1. Tim du lieu cu nhat
select * from  user1.table1 order by end_datetime asc;

--2. Tao bang moi chuyen LONG sang CLOB
create table user1.table1_clob as
select
   SUB_ID    ,
    MONTH    ,
    STA_DATETIME    ,
    END_DATETIME    ,
    to_lob(V_SQL) v_sql
 from  user1.table1;

 -- 3.Test lai du lieu
 select * from  user1.table1_clob where sub_id=123456789; 
 minus
 select * from user1.table1 where sub_id=123456789;



ĐỌC NHIỀU

Trần Văn Bình - Oracle Database Master