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;

Hy vọng hữu ích với bạn.

=============================
* KHOÁ HỌC ORACLE DATABASE A-Z ENTERPRISE trực tiếp từ tôi giúp bạn bước đầu trở thành những chuyên gia DBA, đủ kinh nghiệm đi thi chứng chỉ OA/OCP, đặc biệt là rất nhiều kinh nghiệm, bí kíp thực chiến trên các hệ thống Core tại VN chỉ sau 1 khoá học.
* CÁCH ĐĂNG KÝ: Gõ (.) hoặc để lại số điện thoại hoặc inbox https://m.me/tranvanbinh.vn hoặc Hotline/Zalo 090.29.12.888
* Chi tiết tham khảo:
https://bit.ly/oaz_w
=============================
KẾT NỐI VỚI CHUYÊN GIA TRẦN VĂN BÌNH:
📧 Mail: binhoracle@gmail.com
☎️ Mobile: 0902912888
⚡️ Skype: tranbinh48ca
👨 Facebook: https://www.facebook.com/BinhOracleMaster
👨 Inbox Messenger: https://m.me/101036604657441 (profile)
👨 Fanpage: https://www.facebook.com/tranvanbinh.vn
👨 Inbox Fanpage: https://m.me/tranvanbinh.vn
👨👩 Group FB: https://www.facebook.com/groups/DBAVietNam
👨 Website: https://www.tranvanbinh.vn
👨 Blogger: https://tranvanbinhmaster.blogspot.com
🎬 Youtube: http://bit.ly/ytb_binhoraclemaster
👨 Tiktok: https://www.tiktok.com/@binhoraclemaster?lang=vi
👨 Linkin: https://www.linkedin.com/in/binhoracle
👨 Twitter: https://twitter.com/binhoracle
👨 Địa chỉ: Tòa nhà Sun Square - 21 Lê Đức Thọ - Phường Mỹ Đình 1 - Quận Nam Từ Liêm - TP.Hà Nội

=============================
Quản lý dữ liệu LOB trong Oracle Databasehọc oracle database, Tự học Oracle, Tài liệu Oracle 12c tiếng Việt, Hướng dẫn sử dụng Oracle Database, Oracle SQL cơ bản, Oracle SQL là gì, Khóa học Oracle Hà Nội, Học chứng chỉ Oracle ở đầu, Khóa học Oracle online,khóa học pl/sql, học dba, học dba ở việt nam, khóa học dba, khóa học dba sql, tài liệu học dba oracle, Khóa học Oracle online, học oracle sql, học oracle ở đâu tphcm, học oracle bắt đầu từ đâu, học oracle ở hà nội, oracle database tutorial, oracle database 12c, oracle database là gì, oracle database 11g, oracle download, oracle database 19c, oracle dba tutorial, oracle tunning, sql tunning , oracle 12c, oracle multitenant, Container Databases (CDB), Pluggable Databases (PDB), oracle cloud, oracle security, oracle fga, audit_trail, oracle dataguard, oracle goldengate, mview, oracle exadata, oracle weblogic, middleware, hoc solaris, hoc linux, hoc aix, unix, oracle oca, oracle ocp, oracle ocm