--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, GW_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: Tao bang tam select du lieu 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;
2. 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>
3. MOVE LOB
- Index luôn di kèm dữ liệu LOB do dó không cần rebuild lại
- Bang 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';
4. Xóa dữ liệu LOB cũ
-- Script:
SELECT 'alter table ' || TABLE_OWNER || '.' || TABLE_NAME || ' truncate partition ' || partition_name || ' drop storage;'
FROM DBA_LOB_PARTITIONS
WHERE TABLE_OWNER = 'USER1'
and (table_name='TABLE1' or table_name='TABLE2') and partition_name like 'DATA2025%'
uniton
SELECT 'alter table ' || TABLE_OWNER || '.' || TABLE_NAME || ' drop partition ' || partition_name || ';'
FROM DBA_LOB_PARTITIONS
WHERE TABLE_OWNER = 'USER1'
and (table_name='TABLE1' or table_name='TABLE2') and partition_name like 'DATA2025%';
-- Thủ tục:
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;
/
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
