Thứ Ba, 1 tháng 12, 2020

Quản lý dữ liệu LOB trong Oracle Database

--1.CHECK    

dba_lobs, dba_lob_partitions,dba_lob_subpartitions,dba_segments
    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 ('USER1','USER2') 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 ('USER1','USER2')
    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 ('USER1','USER2') 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 ('USER1','USER2')
    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 ('USER1','USER2') and 
    segment_type like '%LOB%'  
    --and tablespace_name='DATA_LOB' 
    order by owner, segment_name, partition_name;
    
    -- Size LOB: 4.3TB 
    select round(sum(bytes)/1024/1024/1024,2) "GB" from dba_segments where 
    --owner in ('USER1','USER2') and 
    segment_type like '%LOB%'  
    --and tablespace_name='DATA_LOB' 
    order by owner, segment_name, partition_name;
    
    -- Size theo TBS DATA_LOB    3177.33, DATA    718.05, CCGW_CLOB    471.57
    select tablespace_name,round(sum(bytes)/1024/1024/1024,2) "GB" from dba_segments where 
    --owner in ('USER1','USER2') and 
    segment_type like '%LOB%'  
    --and tablespace_name='DATA_LOB'
    group by tablespace_name 
    order by "GB" desc;
    
    -- Size >100G theo segment_name
    --USER2    SYS_LOB0002191742C00007$$    1146.73
    --USER1    SYS_LOB0002220827C00005$$    735.4
    --USER2    SYS_LOB0002140537C00007$$    709.35
    --USER1    SYS_LOB0002220062C00004$$    559.57
    --USER1    SYS_LOB0002220827C00003$$    549.59
    --USER1    SYS_LOB0001730166C00005$$    471.57
    --USER1    SYS_LOB0002220827C00004$$    185.14
    select owner, segment_Name, round(sum(bytes)/1024/1024/1024,2) "GB" from dba_segments 
    where 
    --owner in ('USER1','USER2') and 
    segment_type like '%LOB%'
    --and tablespace_name='DATA_LOB' 
    group by owner, segment_name
    order by "GB" desc;
    
--TRUY XUAT CLOB    
declare
        l_data varchar2(10000);
        l_clob clob;
    begin
        select in_message into l_clob from USER2.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 user1.request@dblink1
    Select * from  xxx_request;
    
P2: T?o mview
    Create MATERIALIZED  VIEW mv_gw_request 
    REFRESH FAST START WITH SYSDATE
       NEXT SYSDATE + 1
         AS select * from user2.request@dblink1;

--TAO BANG CÓ TRUONG 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;
    
    From <https://community.oracle.com/thread/2219934> 
MOVE LOB    

- Index luôn di kèm d? li?u LOB do dó không c?n rebuild l?i
    - B?ng non-partition: Khong can rebuild index
    ALTER TABLE HLRGW2.GW_DISPATCHER_PARAM MOVE 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 HLRGW2.GW_REQUEST 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 = 'USER1' 
    and (table_name='TABLE1' or table_name='TABLE2') and partition_name like 'DATA2017%'
    --AND TABLESPACE_NAME = 'TBS_LOB'
    AND SECUREFILE='NO';
Th? t?c xóa d? li?u LOB cu    declare
        v_date date:=sysdate;
        date_num1t        INT := 31;                         -- Chi luu giu 40 ngay, PROM_CHARGE_DAILY_HIS,..
        date_num2t        INT := 62;                         -- Chi luu giu 40 ngay
        date_num3t        INT := 93;
        date_num6t        INT := 186;                        -- Chi luu giu 180 ngay, 
        --date_num4        INT := 300;                        --log 
    
    --    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 ('PRO'))
    --                 OR (sysdate - to_date(SUBSTR(partition_name,5,6),'yyyymm') > date_num2t
    --                    AND table_name IN ('LOG'))
    --                 OR (sysdate - to_date(SUBSTR(partition_name,length(partition_name)-7,8),'yyyy/mm/dd') > date_num3t
    --                    AND table_name IN ('PACK'))
    --                 OR (sysdate - to_date(SUBSTR(partition_name,length(partition_name)-7,8),'yyyy/mm/dd') > date_num6t
    --                    AND table_name IN ('CP'))
    --                 OR (sysdate - to_date(SUBSTR(partition_name,5,6),'yyyymm') > date_num6t
    --                    AND table_name IN ('REG'))
    --                 OR (sysdate - to_date(SUBSTR(partition_name,length(partition_name)-7,8),'yyyy/mm/dd') > 366
    --                    AND table_name IN ('LOG_WS','SEND_LOG','TRANSACTION_LOG'));
        CURSOR c_gw
        IS        
              select  *
                from dba_segments where segment_name in ('REQUEST_LOG','RESPOND_LOG')
                and to_date(substr(partition_name,5,8),'yyyymmdd')<=to_date(to_char(sysdate-30,'yyyymmdd'),'yyyymmdd');
        cursor c_api 
        is
            select *
            from dba_segments where segment_name in ('AUDIT_LOG')
            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_gw
        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_api
        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;
    /
    

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/OracleDBAVietNam 👨 Website: http://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 #OracleTutorial #OracleDBA #OracleDatabaseAdministration #học oracle database #oca #ocp #oce #ocm

ĐỌC NHIỀU

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