--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;
-- 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;
/
