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