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:

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 ·         Bảng 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';        ·         Bảng 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 bảng 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;