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