Script gen partition theo ngày trong Oracle Database

/*  2.3.GEN PARTITION THEO NGAY */

--2.3.1.Gen data partition theo ngay_MAIN_OWNER

-- Chay vao ban dem > 22h, lock ~200, active session ~ 1000

-- Có thể gặp  Event: library cache lock, cursor: pin S wait on X

DECLARE

   v_tablename    VARCHAR2 (50);

   v_date_from   date;

   v_date_to     date := to_date('31/12/2023','dd/mm/yyyy');

   v_numday     number;

   v_tablespace varchar2(50):='DATA';

   cursor c1 is

      select table_owner,table_name,MAX(PARTITION_NAME) par_name from dba_tab_partitions where (table_owner, table_name, partition_name) in (

            select table_owner,table_name,MAX(PARTITION_NAME) par_name

            from dba_tab_partitions            

            where LENGTH(PARTITION_NAME)>=11

            and table_name not like '%$%' and table_name not like 'XXX%'

            and table_owner= 'MAIN_OWNER'   

            group by table_owner, table_name

            )

        and partition_name like 'DATA20221231%'

        GROUP BY table_owner,table_name  order by table_owner,table_name;  

BEGIN

    for r1 in c1 loop

       v_tablename:=r1.table_owner||'.'||r1.table_name;

       v_date_from:=to_date(substr(r1.par_name,5,8),'YYYYMMDD')+1;

       v_numday:=v_date_to-v_date_from;

       FOR i IN 0 .. v_numday

       LOOP

          DBMS_OUTPUT.put_line ('alter table '|| v_tablename || ' add PARTITION DATA'||to_char(v_date_from+i,'YYYYMMDD')||' VALUES LESS THAN (TO_DATE('''|| to_char(v_date_from+i+1,'YYYY-MM-DD')||' 00:00:00'', ''SYYYY-MM-DD HH24:MI:SS'', ''NLS_CALENDAR=GREGORIAN'')) LOGGING TABLESPACE DATA2020;');

       END LOOP;

    end loop;  

END;

-- 2.3.1.1.Rebuild Index theo ngay MAIN_OWNER

DECLARE

   v_date_from   date    := to_date('01/01/2023','dd/mm/yyyy');

   v_date_to     date    := to_date('31/12/2023','dd/mm/yyyy');

   v_numday     number;

   v_tablespace varchar2(50):='INDX';

   cursor c1 is

     select a.* from DBA_PART_INDEXES a, DBA_TAB_PARTITIONS b where a.owner=B.TABLE_OWNER and a.table_name=B.TABLE_NAME

     and a.owner='CUS_OWNER' and a.index_name not like '%$%' and b.partition_name like '%20191231'  order by a.owner,a.index_name;

BEGIN

   v_numday:=v_date_to-v_date_from;

   FOR i1 in c1

   LOOP

       FOR i IN 0 .. v_numday

       LOOP

            DBMS_OUTPUT.put_line ('alter index '||i1.owner||'.'||i1.index_name || ' REBUILD PARTITION DATA'||to_char(v_date_from+i,'YYYYMMDD')||' TABLESPACE '||v_tablespace||to_char(v_date_from+i,'YYYY')||' nologging parallel 8 online;');

       END LOOP;

      

   END LOOP;

END;

--2.3.2.Gen data partition theo ngay_User khac

DECLARE

   v_tablename    VARCHAR2 (50);

   v_date_from   date;

   v_date_to     date := to_date('31/12/2023','dd/mm/yyyy');

   v_numday     number;

   v_tablespace varchar2(50):='DUMP_DATA';

   cursor c1 is

      select table_owner,table_name,MAX(PARTITION_NAME) par_name

        from dba_tab_partitions

        WHERE (LENGTH(PARTITION_NAME)>=12 AND PARTITION_NAME LIKE '%20221231'

        and table_name not like '%$%' and table_name not like 'XXX%' and table_name not like '%TEST%' and table_name not like '%DAILY_20%'

        and  table_owner not in ('MAIN_OWNER')

        GROUP BY table_owner,table_name  order by table_owner,table_name;  

BEGIN

    for r1 in c1 loop

       v_tablename:=r1.table_owner||'.'||r1.table_name;

       v_date_from:=to_date(substr(r1.par_name,5,8),'YYYYMMDD')+1;

       v_numday:=v_date_to-v_date_from;

       if r1.table_name in ('REQUEST_LOG','RESPOND_LOG') then

       FOR i IN 0 .. v_numday

       LOOP

          DBMS_OUTPUT.put_line ('alter table '|| v_tablename || ' add PARTITION DATA'||to_char(v_date_from+i,'YYYYMMDD')||' VALUES LESS THAN (TO_DATE('''|| to_char(v_date_from+i+1,'YYYY-MM-DD')||' 00:00:00'', ''SYYYY-MM-DD HH24:MI:SS'', ''NLS_CALENDAR=GREGORIAN'')) LOGGING TABLESPACE  LOB noparallel;');

       END LOOP;

       elsif r1.table_name in ('AUDIT_LOG') then

       FOR i IN 0 .. v_numday

       LOOP

          DBMS_OUTPUT.put_line ('alter table '|| v_tablename || ' add PARTITION DATA'||to_char(v_date_from+i,'YYYYMMDD')||' VALUES LESS THAN (TO_DATE('''|| to_char(v_date_from+i+1,'YYYY-MM-DD')||' 00:00:00'', ''SYYYY-MM-DD HH24:MI:SS'', ''NLS_CALENDAR=GREGORIAN'')) LOGGING TABLESPACE  ' || substr(r1.par_name,1,8)|| ' noparallel;');

       END LOOP;      

       else

       LOOP

          DBMS_OUTPUT.put_line ('alter table '|| v_tablename || ' add PARTITION DATA'||to_char(v_date_from+i,'YYYYMMDD')||' VALUES LESS THAN (TO_DATE('''|| to_char(v_date_from+i+1,'YYYY-MM-DD')||' 00:00:00'', ''SYYYY-MM-DD HH24:MI:SS'', ''NLS_CALENDAR=GREGORIAN'')) LOGGING TABLESPACE  ' || substr(r1.par_name,1,8) ||' noparallel;');

       END LOOP;       

       end if;

    end loop;  

END;

-- 2.3.2.1.Rebuild Index theo ngày user khac

-- Lưu tại DUMP_INDX

DECLARE

   v_date_from   date    := to_date('01/01/2023','dd/mm/yyyy');

   v_date_to     date    := to_date('31/12/2023','dd/mm/yyyy');

   v_numday     number;

   v_tablespace varchar2(50):='DUMP_INDX';

   cursor c1 is

     select a.*,b.partition_name from DBA_PART_INDEXES a, DBA_TAB_PARTITIONS b where a.owner=B.TABLE_OWNER and a.table_name=B.TABLE_NAME

     and a.owner!='USER_OWNER' and a.index_name not like '%$%' and b.partition_name like '%20221231' 

     order by a.owner,a.index_name;

BEGIN

   v_numday:=v_date_to-v_date_from;

   FOR i1 in c1

   LOOP

    if i1.owner='TEST_GATEWAY' then

       FOR i IN 0 .. v_numday

       LOOP

            DBMS_OUTPUT.put_line ('alter index '||i1.owner||'.'||i1.index_name || ' REBUILD PARTITION DATA'||to_char(v_date_from+i,'YYYYMMDD')||' TABLESPACE  LOB nologging parallel 8 online;');

       END LOOP;

       DBMS_OUTPUT.put_line ('alter index '||i1.owner||'.'||i1.index_name || ' noparallel;');

     else

       FOR j IN 0 .. v_numday

       LOOP

            DBMS_OUTPUT.put_line ('alter index '||i1.owner||'.'||i1.index_name || ' REBUILD PARTITION DATA'||to_char(v_date_from+j,'YYYYMMDD')||' TABLESPACE  INDX' || substr(i1.partition_name,5,4) ||' nologging parallel 8 online;');

       END LOOP;

       DBMS_OUTPUT.put_line ('alter index '||i1.owner||'.'||i1.index_name || ' noparallel;');

     end if;

   END LOOP;

END;

-- 2.3.4.Set noparallel index

DECLARE

   cursor c1 is

        select distinct a.index_owner, a.index_name from DBA_ind_partitions a where  a.index_owner='USER_OWNER' and a.index_name not like '%$%' and a.partition_name like '%20221231' 

        order by a.index_owner,a.index_name;

BEGIN

   FOR i1 in c1

   LOOP

            DBMS_OUTPUT.put_line ('alter index '||i1.index_owner||'.'||i1.index_name || ' noparallel;');

   END LOOP;

END;

-- Một số câu lệnh khác hỗ trợ trong quá trình thực hiện

--Optional: Rebuild INDX PARTITION theo ngay, 1 bang

DECLARE

   v_tablename    VARCHAR2 (50) := 'REQUEST';

   v_owner        VARCHAR2 (50) := 'USER_OWNER';

   v_date_from   date    := '01/01/2023';

   v_date_to     date    := '31/12/2023';

   v_numday     number(2);

   v_tablespace varchar2(50):='INDX';

   cursor c1 is

     select * from DBA_PART_INDEXES where table_name = v_tablename and owner=v_owner;

BEGIN

   v_numday:=v_date_to-v_date_from;

   FOR i1 in c1

   LOOP

       FOR i IN 0 .. v_numday

       LOOP

            DBMS_OUTPUT.put_line ('alter index '||v_owner||'.'||i1.index_name || ' REBUILD PARTITION DATA'||to_char(v_date_from+i,'YYYYMMDD')||' TABLESPACE '||v_tablespace||to_char(v_date_from+i,'YYYYMM')||';');

       END LOOP;

   END LOOP;

END;

-- MONITORING: Neu active session > 600, lock > 50 thi kill tien trinh

-- Trong quá trình thêm partition cần MONITOR chặt chẽ tránh tình trạng lock, cao tải gây timeout ứng dụng, nếu gặp lock, cao tải cần dừng lại và chọn thời điểm THẤP TẢI khác để chạy (ví dụ 1h,2h,...)

-- Active session

SELECT 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.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 ('SYS','SYSMAN')

--AND username LIKE 'SYS'

--and s.sid=1234

--and s.machine like '%app-2%'

--and DECODE(S.WAIT_TIME, 0, S.EVENT, 'CPU') like '%cell single block physical read%'

--and s.sql_id ='cb66zngs8xz5j'

--and lower(ss.sql_text) like lower('%test_customer%')

ORDER BY i#,username,sql_id,machine,S.SID;

Note: Nếu bảng nào gây tải cao thì chọn giờ thấp điểm hẳn, 1-2h sáng hoặc yêu cầu ứng dụng tắt nghiệp vụ đi đẻ thực hiện vì cố làm sẽ gây treo DB

-- Kill gấp nếu gây cao tải

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 (

    select sid from (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.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 ('SYS','SYSMAN','DBSNMP','GGATE','GOLDENGATE')

    AND username like 'SYS%'

    --and DECODE(S.WAIT_TIME, 0, S.EVENT, 'CPU') like '%cell single block physical read%'

    and lower(ss.sql_text) like lower('%alter table%')

    and lower(ss.sql_text) not like lower('%ACTIVE, LOCK%'))

    --and s.sid=4588 

    --and s.machine like '%BINHTV%'

    --and s.sql_id ='ccwg0nqr1zbu7'

)

and type='USER'

order by inst_id;

/********************************************  LOCK SESSION, WAITING *************************************************/

select status, count(*) from gv$session  group by status order by status;

Select /*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 --order by blocking_session;

SELECT /*blocking_session*/ inst_id, sid, DECODE (request, 0, 'Holder: ', 'Waiter: ') || sid sess, id1,id2,lmode, request,TYPE

FROM GV$LOCK WHERE (id1, id2, TYPE) IN (SELECT id1, id2, TYPE  FROM GV$LOCK WHERE request > 0) ORDER BY id1, request;

/***********************************************  SESSIONS ***********************************************/

select count(*) from gv$session ;

select /* count , status*/ username,status, count(*) from gv$session group by username,status order by count(*) desc;

select USERNAME,count(*) from gv$session group by USERNAME order by count(*) desc;

select machine,count(*) from v$session group by machine order by count(*) desc;

select inst_id, count(*) from gv$session group by inst_id;

select /*Thong ke theo status*/  username,status,count(*) from gv$session where username like 'TEST_OWNER%' group by username,status order by count(*) desc;

select /*Thong ke theo inst_id*/ inst_id,count(*), username from gv$session where username like 'TEST_OWNER%' group by inst_id, username order by username;

select /* user theo machine */ machine,count(*), username from gv$session where username like 'TEST_OWNER%' group by machine, username order by username;

/********************************************** KILLER *********************************************/

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 = 1

--and a.status='INACTIVE'

--and A.USERNAME LIKE 'TEST%'

--AND A.USERNAME not  in ('SYS','SYSTEM','SYSMAN','BINHTV','DBSNMP','GGATE')

--and a.username not in ('SYS','SYSTEM','SYSMAN','BINHTV','DBSNMP','GGATE','APP1','APP2','APP3')

AND a.program not LIKE '%Toad.exe%'

--AND sql_id in ('44t0dk94q3xqg','6uac7nnud2gfa')

and machine  like '%TCTK-BINHTV%'

--and a.event in ('library cache lock','brary cache load lock','cursor: pin S wait on X','library cache pin','gc buffer busy acquire','enq: TS - contention','enq: TX - row lock contention','db file parallel read')

and type='USER'

order by a.inst_id;

SELECT /*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,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 (

5812

)

and type='USER'

order by inst_id;

--3.Check partition Theo NGAY,  max partiton 20261231 la OK

select table_owner,table_name,partition_name from dba_tab_partitions where (table_owner,table_name,partition_position) in

(select table_owner,table_name,max(partition_position) from dba_tab_partitions

where (table_name not like '%$%' and table_name not like 'XXX%')

and table_owner in  ('U1','U2','U3')

group by table_name,table_owner

) 

and length(partition_name)>=11

and  table_name not like '%$%' and table_name not like '%TEST%' and table_name not like 'XXX%' and table_name not like '%BAK' and table_name not like 'BK%'

and partition_name not like '%20261231'

--and table_owner in   ('U1','U2','U3')  

order by table_owner, table_name, partition_name;

--+ Xoa %DAILY_20% 3 thang: OK 31/01/2026 19h51

select 'drop table ' || table_owner || '.' || table_name || ' purge;' 

from dba_tab_partitions

where table_name like '%DAILY_20%'

and to_date(substr(table_name,-8,8),'yyyymmdd') <= sysdate-93

order by table_owner, table_name;

THAM KHẢO

Quy trình tạo partion hết năm 2023 cho CẢ cơ sở dữ liệu Oracle (theo năm, theo tháng, theo ngày)?

=============================
TƯ VẤN: Click Here hoặc Hotline/Zalo 090.29.12.888
=============================
Website không 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 không muốn bị AI thay thế và tiết kiệm 3-5 NĂM trên con đường trở thành DBA chuyên nghiệp hay làm chủ Database 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ộ bí kíp thực chiến, thủ tục, quy trình của gần 20 năm kinh nghiệm (mà bạn sẽ KHÔNG THỂ tìm kiếm trên Internet/Google) từ đó 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/admin_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

=============================
cơ sở dữ liệu, cơ sở dữ liệu quốc gia, database, AI, trí tuệ nhân tạo, artificial intelligence, machine learning, deep learning, LLM, ChatGPT, DeepSeek, Grok, 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/21c/23c/23ai, 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, docker, k8s, micro service, hoc solaris tutorial, hoc linux tutorial, hoc aix tutorial, unix tutorial, securecrt, xshell, mobaxterm, putty