---Truoc mov: Name Position High Value Tablespace Compression Num Rows Blocks Empty Blocks Avg Space Last Analyzed
--DATA20180101 1 TO_DATE(' 2018-01-02 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') DATA2018 DISABLED 238,883 Block: 43,776 Empty blocks: 441 avg Space;879 05/09/2023 10:13:06 PM
--Sau move: Name Position High Value Tablespace Compression Num Rows Blocks Empty Blocks Avg Space Last Analyzed
--DATA20180101 1 TO_DATE(' 2018-01-02 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') DATA2018 DISABLED 238,883 2,877 (giam 20 lan) 441 879 06/09/2023 10:43:03 PM
--Sau shrink: ALTER TABLE mc_owner.TABLE_CORE MODIFY PARTITION DATA20180101 SHRINK SPACE; --> Van khong thay doi
--Name Position High Value Tablespace Compression Num Rows Blocks Empty Blocks Avg Space Last Analyzed
--DATA20180101 1 TO_DATE(' 2018-01-02 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') DATA2018 DISABLED 238,883 2,877 441 879 06/09/2023 11:58:20 PM
-- Check dung luong
--+Truoc:
--MC_OWNER TABLE_CORE 425.28
--MC_OWNER TABLE_CORE_I4 81.81
--MC_OWNER TABLE_CORE_PK 142.14
select owner, segment_name,round(sum(bytes)/1024/1024/1024,2) "GB" from dba_segments
where owner='MC_OWNER' and segment_name in ('TABLE_CORE','_TABLE_CORE_I4','_TABLE_CORE_PK')
group by owner, segment_name;
--+sau
--**** SCRIPT STARTED Session: SYS@GOLD(4) 06/09/2023 10:36:43 PM ****
--ALTER TABLE mc_owner.TABLE_CORE MOVE PARTITION DATA20180101 UPDATE INDEXES
--==>
--line 1: ORA-03114: not connected to ORACLE
----------------------------------------------------------------------------------
--**** SCRIPT ENDED 06/09/2023 10:37:13 PM ****
--^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
--**** SCRIPT STARTED Session: SYS@GOLD(4) 06/09/2023 10:38:50 PM ****
--ALTER TABLE mc_owner.TABLE_CORE MOVE PARTITION DATA20180101 UPDATE INDEXES
--==>
ALTER TABLE mc_owner.TABLE_CORE MOVE PARTITION DATA20180101 UPDATE INDEXES;
-- Build scripts
select 'alter table ' ||table_owner||'.'||table_name || ' move partition ' || partition_name ||' UPDATE INDEXES;'
from dba_tab_partitions
where table_owner='MC_OWNER' and table_name='TABLE_CORE'
and to_date(substr(partition_name,5,8),'yyyymmdd')<=trunc(sysdate);
--Monitor
SELECT /*1.ActiveSession*/ distinct s.inst_id i#, s.username, s.SID SID, s.osuser, s.machine,DECODE(S.WAIT_TIME, 0, S.EVENT, 'CPU') ACTION,
s.sql_id, SUBSTR(DECODE(SS.SQL_TEXT, NULL, AA.NAME, SS.SQL_TEXT), 1, 1000) SQLTEXT,s.logon_time,S.PROGRAM,s.p1text, S.P1, s.p2text, S.P2, s.p3text, S.P3
FROM GV$SESSION S, GV$SQLSTATS SS, AUDIT_ACTIONS AA
WHERE
S.STATUS = 'ACTIVE' AND
S.SQL_ID = SS.SQL_ID (+) AND AA.ACTION = S.COMMAND and s.type != 'BACKGROUND' AND S.TYPE = 'USER'
and s.username NOT in ('SYSMAN','DBSNMP','GGATE','GOLDENGATE')
--AND username in 'PAYMENT'
--and DECODE(S.WAIT_TIME, 0, S.EVENT, 'CPU') like '%cell single block physical read%'
--and lower(ss.sql_text) like lower('%parallel%')
--and s.sid=4588
--and s.machine like '%BINHTV%'
--and s.sql_id ='ccwg0nqr1zbu7'
ORDER BY username,sql_id;
select /* 2.Total_ActiveSessioins*/ USERNAME,count(*) from gv$session where status='ACTIVE' group by USERNAME order by count(*) desc;
select /* 3.Total_Session*/ username,status, count(*) from gv$session group by username,status order by count(*) desc;
select inst_id, count(*) from gv$session group by inst_id;
Select /*4.Blocking_session*/ inst_id,blocking_session, sid, serial#, sql_id, wait_class, seconds_in_wait, username,STATUS,SCHEMANAME,OSUSER,MACHINE,PROGRAM,TYPE,LOGON_TIME
From gv$session where blocking_session is not NULL and type not like 'BACKGROUND' order by inst_id;
SELECT /*5.SID*/ 'kill -9 ' || spid a, a.INST_ID,A.SQL_ID,A.SID, A.SERIAL#, a.USERNAME, a.STATUS,A.SCHEMANAME,a.OSUSER,A.MACHINE,A.PROGRAM,A.TYPE,A.LOGON_TIME,a.prev_exec_start,BACKGROUND
FROM gv$session a, gv$process b
WHERE b.addr = a.paddr
AND a.inst_id=b.inst_id
--and b.inst_id=2
AND a.sid in (
4588
)
and type='USER'
order by inst_id;
--Gather partition
BEGIN
SYS.DBMS_STATS.GATHER_TABLE_STATS (
OwnName => 'MC_OWNER'
,TabName => 'MC_ACTION_AUDIT'
,PartName => 'DATA20180101'
,Granularity => 'PARTITION'
,Estimate_Percent => SYS.DBMS_STATS.AUTO_SAMPLE_SIZE
,Method_Opt => 'FOR ALL COLUMNS SIZE AUTO '
,Degree => NULL
,Cascade => DBMS_STATS.AUTO_CASCADE
,No_Invalidate => DBMS_STATS.AUTO_INVALIDATE
,Force => FALSE);
END;
/
-- Kiem tra index
select * from dba_ind_partitions
where index_owner='MC_OWNER' and index_name in ('TABLE_CORE_I4','TABLE_CORE_PK')
and status!='USABLE';
-- Xac dinh process tu inst_id, status, username, sql_id, machine, event,
SELECT /*username*/ 'kill -9 ' || SPID A ,a.INST_ID,A.SID,A.SQL_ID, a.USERNAME, a.STATUS,A.SCHEMANAME,a.OSUSER,A.MACHINE,A.PROGRAM,A.TYPE,A.LOGON_TIME,BACKGROUND, A.EVENT
FROM gv$session a, gv$process b
WHERE b.ADDR = a.paddr
AND a.inst_id=b.inst_id
--AND B.inst_id = 4
--and a.status='INACTIVE'
--and A.USERNAME LIKE 'SYS'
--AND A.USERNAME not in ('SYS','GGATE','GOLDENGATE','CUS_CARE_GATEWAY','ORA_RECO_070361')
--and a.username not in ('SYS','SYSTEM','SYSMAN','BINHTV','DBSNMP','GGATE','GOLDENGATE')
AND a.program LIKE '%sqlnavigator.exe%'
--AND sql_id in ('gbmyfjjdcyk75')
--and machine like '%ADMIN-DB%'
and OSUSER='binhtv'
--and a.event in ('library cache lock','library cache load lock','library cache: mutex X','cursor: pin S wait on X','library cache pin','gc buffer busy acquire','enq: TS - contention','enq: TX - row lock contention','enq: TM - contention','db file parallel read','row cache lock','enq: DX - contention','enq: US - contention','enq: TX - allocate ITL entry')
--and round(to_number(sysdate-a.prev_exec_start)*1440) >30
and type='USER'
order by a.inst_id;
-- Du phong can rebuild index partition unusable
select 'alter index ' || index_owner ||'.'|| index_Name ||' rebuild partition ' || partition_name || ' online parallel 8 online'
from dba_ind_partitions
where index_owner='MC_OWNER' and index_name in ('TABLE_CORE_I4','TABLE_CORE_PK')
and status!='USABLE';
-- Set noparallel
alter index MC_OWNER._TABLE_CORE_I4 noparallel;
alter index v._TABLE_CORE_PK noparallel;
--Tham khao shrink
-- uncompress partition
ALTER TABLE DEV_DW.TEST_TABLE move PARTITION SYS_P21822227 NOCOMPRESS UPDATE INDEXES;
--Yeu cau shrink space
ALTER TABLE TEST_TABLE enable row movement;
--Shrink partition de thu hoi dung luong khong su dung
ALTER TABLE TEST_TABLE MODIFY PARTITION SYS_P21822227 SHRINK SPACE;
--compress partition
ALTER TABLE DEV_DW.TEST_TABLE move PARTITION SYS_P21822227 COMPRESS FOR QUERY HIGH UPDATE INDEXES;
--gather stats
BEGIN
DBMS_STATS.GATHER_TABLE_STATS('DEV_DW', 'TEST_TABLE',
estimate_percent=>dbms_stats.auto_sample_size,
block_sample=>TRUE,
degree=>8) ;
END;
commit;
select
table_owner
,table_name
,partition_name
,high_value
,compression
,compress_for
,avg_row_len
,round(((blocks*16/1024)),2)/1024 "TOTAL_SIZE_GB" --The amount of space used by the partition in gigabytes.
,round((num_rows*avg_row_len/1024/1024),2)/1024 "ACTUAL_SIZE_GB" -- The amount of space used by the partition in gigabytes, calculated based on the number of rows and the average row length.
,round(((blocks*16/1024)-(num_rows*avg_row_len/1024/1024)),2)/1024 "FRAGMENTED_SPACE_GB" --The amount of space that is not used by the partition, in gigabytes.
,decode(round(((blocks*16/1024)),2),0,0, (round(((blocks*16/1024)-(num_rows*avg_row_len/1024/1024)),2)/round(((blocks*16/1024)),2))*100) "percentage" --The percentage of unused space in the partition, calculated by dividing the unused space by the total space and multiplying by 100.
from dba_tab_partitions
WHERE
1=1
and table_owner='MC_ONWER'
and table_name='TABLE_CORE'
and partition_name='DATA20180101'
and round(((blocks*16/1024)-(num_rows*avg_row_len/1024/1024)),2)/1024 > 0
order by 6 desc
;
Website không bao giờ chứa bất kỳ quảng cáo nào, mọi đóng góp để duy trì phát triển cho website (donation) xin vui lòng gửi về STK 90.2142.8888 - Ngân hàng Vietcombank Thăng Long - TRAN VAN BINH
=============================
Nếu bạn muốn tiết kiệm 3-5 NĂM trên con đường trở thành DBA chuyên nghiệp thì hãy đăng ký ngay KHOÁ HỌC ORACLE DATABASE A-Z ENTERPRISE, được Coaching trực tiếp từ tôi với toàn bộ kinh nghiệm, thủ tục, quy trình, bí kíp thực chiến mà bạn sẽ KHÔNG THỂ tìm kiếm trên Internet/Google giúp bạn dễ dàng quản trị mọi hệ thống Core tại Việt Nam và trên thế giới, đỗ OCP.
- 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
=============================
2 khóa học online qua video giúp bạn nhanh chóng có những kiến thức nền tảng về Linux, Oracle, học mọi nơi, chỉ cần có Internet/4G:
- Oracle cơ bản: https://bit.ly/admin1_1200
- Linux: https://bit.ly/linux_1200
=============================
KẾT NỐI VỚI CHUYÊN GIA TRẦN VĂN BÌNH:
📧 Mail: binhoracle@gmail.com
☎️ Mobile/Zalo: 0902912888
👨 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: https://www.youtube.com/@binhguru
👨 Tiktok: https://www.tiktok.com/@binhguru
👨 Linkin: https://www.linkedin.com/in/binhoracle
👨 Twitter: https://twitter.com/binhguru
👨 Podcast: https://www.podbean.com/pu/pbblog-eskre-5f82d6
👨 Đị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
=============================
Sau khi delete cần Move partition để giảm dung lượng trong Oracle Database, oracle tutorial, họ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,sql tutorial, khóa học pl/sql tutorial, 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 RAC, ASM, oracle dataguard, oracle goldengate, mview, oracle exadata, oracle oca, oracle ocp, oracle ocm , oracle weblogic, postgresql tutorial, mysql tutorial, mariadb tutorial, ms sql server tutorial, nosql, mongodb tutorial, oci, cloud, middleware tutorial, hoc solaris tutorial, hoc linux tutorial, hoc aix tutorial, unix tutorial, securecrt, xshell, mobaxterm, putty
