--SELECT HOST_NAME  FROM v$instance;

DBMS_OUTPUT.PUT_LINE(CHR(27) || '[31m' || '这是红色文字' || CHR(27) || '[0m');

查看分区表分区键类型为number的

SELECT a.TABLE_NAME ,b.OWNER  FROM ALL_TAB_COLUMNS a,dba_PART_KEY_COLUMNS b

WHERE  a.owner='username' AND b.object_type='TABLE' AND a.TABLE_NAME =b.name AND a.COLUMN_NAME =b.COLUMN_NAME

AND a.DATA_TYPE IN ('NUMBER')

查看所有分区表及分区键类型

SELECT b.*,a.DATA_TYPE  FROM ALL_TAB_COLUMNS a,dba_PART_KEY_COLUMNS b

WHERE  a.owner='username' AND b.object_type='TABLE' AND a.TABLE_NAME =b.name AND a.COLUMN_NAME =b.COLUMN_NAME

ORDER BY a.DATA_TYPE

获取分区键为非时间的分区表：

DECLARE

    CURSOR c_tables IS

        SELECT a.TABLE_NAME ,b.OWNER  FROM ALL_TAB_COLUMNS a,dba_PART_KEY_COLUMNS b

WHERE  a.owner='username' AND b.object_type='TABLE' AND a.TABLE_NAME =b.name AND a.COLUMN_NAME =b.COLUMN_NAME

AND a.DATA_TYPE IN ('NUMBER'); -- 替换为你的模式名称

    v_table_name ALL_TAB_COLUMNS.table_name%TYPE;

    v_owner ALL_TAB_COLUMNS.owner%TYPE;

    v_partition_name VARCHAR2(100);

    v_count INTEGER;

BEGIN

    FOR r_table IN c_tables LOOP

        v_table_name := r_table.table_name;

        v_owner := r_table.owner;

        -- 获取分区信息，查找倒数第10个分区

        SELECT partition_name

        INTO v_partition_name

        FROM (

            SELECT partition_name

            FROM all_tab_partitions

            WHERE table_name = v_table_name AND TABLE_OWNER = v_owner

            ORDER BY partition_position DESC

        )

        WHERE ROWNUM < 21

        OFFSET 9 ROWS FETCH NEXT 1 ROWS ONLY;  -- 取倒数第10个分区

        -- 检查该分区是否有数据 set serveroutput on

       

        EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM ' || v_owner || '.' || v_table_name || ' PARTITION(' || v_partition_name || ')' INTO v_count;

        IF v_count > 0 THEN

        DBMS_OUTPUT.ENABLE(1000000);

--DBMS_OUTPUT.PUT_LINE(CHR(27) || '[31m' || '注意，倒数第10个分区存在数据' || CHR(27) || '[0m');

            DBMS_OUTPUT.PUT_LINE(CHR(27) || '[33m' || 'Table: ' || v_table_name || ', Partition: ' || v_partition_name || ' has ' || v_count || ' rows.'  || CHR(27) || '[0m');

        ELSE

        DBMS_OUTPUT.ENABLE(1000000);

--DBMS_OUTPUT.PUT_LINE(CHR(27) || '[34m' || '倒数第10个分区不存在数据' || CHR(27) || '[0m');

            DBMS_OUTPUT.PUT_LINE(CHR(27) || '[32m' || 'Table: ' || v_table_name || ', Partition: ' || v_partition_name || ' is empty.'  || CHR(27) || '[0m');

        END IF;

    END LOOP;

END;

/

查看固定表的分区信息

DECLARE

    CURSOR c_tables IS

        SELECT table_name, owner

        FROM all_part_tables

        WHERE owner = 'username' AND PARTITION_COUNT > 30 and table_name in ('table1','table2'); -- 替换为你的模式名称

    v_table_name all_part_tables.table_name%TYPE;

    v_owner all_part_tables.owner%TYPE;

    v_partition_name VARCHAR2(100);

    v_count INTEGER;

BEGIN

    FOR r_table IN c_tables LOOP

        v_table_name := r_table.table_name;

        v_owner := r_table.owner;

        -- 获取分区信息，查找倒数第10个分区

        SELECT partition_name

        INTO v_partition_name

        FROM (

            SELECT partition_name

            FROM all_tab_partitions

            WHERE table_name = v_table_name AND TABLE_OWNER = v_owner

            ORDER BY partition_position DESC

        )

        WHERE ROWNUM < 21

        OFFSET 9 ROWS FETCH NEXT 1 ROWS ONLY;  -- 取倒数第10个分区

        -- 检查该分区是否有数据 set serveroutput on

       

        EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM ' || v_owner || '.' || v_table_name || ' PARTITION(' || v_partition_name || ')' INTO v_count;

        IF v_count > 0 THEN

        DBMS_OUTPUT.ENABLE(1000000);

--DBMS_OUTPUT.PUT_LINE(CHR(27) || '[31m' || '注意，倒数第10个分区存在数据' || CHR(27) || '[0m');

            DBMS_OUTPUT.PUT_LINE(CHR(27) || '[33m' || 'Table: ' || v_table_name || ', Partition: ' || v_partition_name || ' has ' || v_count || ' rows.'  || CHR(27) || '[0m');

        ELSE

        DBMS_OUTPUT.ENABLE(1000000);

--DBMS_OUTPUT.PUT_LINE(CHR(27) || '[34m' || '倒数第10个分区不存在数据' || CHR(27) || '[0m');

            DBMS_OUTPUT.PUT_LINE(CHR(27) || '[32m' || 'Table: ' || v_table_name || ', Partition: ' || v_partition_name || ' is empty.'  || CHR(27) || '[0m');

        END IF;

    END LOOP;

END;

/

查看分区数量大于30的表

DECLARE

    CURSOR c_tables IS

        SELECT table_name, owner

        FROM all_part_tables

        WHERE owner = 'username' AND PARTITION_COUNT > 30 ; -- 替换为你的模式名称

    v_table_name all_part_tables.table_name%TYPE;

    v_owner all_part_tables.owner%TYPE;

    v_partition_name VARCHAR2(100);

    v_count INTEGER;

BEGIN

    FOR r_table IN c_tables LOOP

        v_table_name := r_table.table_name;

        v_owner := r_table.owner;

        -- 获取分区信息，查找倒数第10个分区

        SELECT partition_name

        INTO v_partition_name

        FROM (

            SELECT partition_name

            FROM all_tab_partitions

            WHERE table_name = v_table_name AND TABLE_OWNER = v_owner

            ORDER BY partition_position DESC

        )

        WHERE ROWNUM < 21

        OFFSET 9 ROWS FETCH NEXT 1 ROWS ONLY;  -- 取倒数第10个分区

        -- 检查该分区是否有数据 set serveroutput on

       

        EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM ' || v_owner || '.' || v_table_name || ' PARTITION(' || v_partition_name || ')' INTO v_count;

        IF v_count > 0 THEN

        DBMS_OUTPUT.ENABLE(1000000);

--DBMS_OUTPUT.PUT_LINE(CHR(27) || '[31m' || '注意，倒数第10个分区存在数据' || CHR(27) || '[0m');

            DBMS_OUTPUT.PUT_LINE(CHR(27) || '[33m' || 'Table: ' || v_table_name || ', Partition: ' || v_partition_name || ' has ' || v_count || ' rows.'  || CHR(27) || '[0m');

        ELSE

        DBMS_OUTPUT.ENABLE(1000000);

--DBMS_OUTPUT.PUT_LINE(CHR(27) || '[34m' || '倒数第10个分区不存在数据' || CHR(27) || '[0m');

            DBMS_OUTPUT.PUT_LINE(CHR(27) || '[32m' || 'Table: ' || v_table_name || ', Partition: ' || v_partition_name || ' is empty.'  || CHR(27) || '[0m');

        END IF;

    END LOOP;

END;

/

SELECT partition_name

            FROM all_tab_partitions

            WHERE table_name = 'table1' AND TABLE_OWNER = 'username'

            ORDER BY partition_position DESC

select count(*) from username.table2 partition(P35);

SELECT partition_name

                FROM (

            SELECT partition_name

            FROM all_tab_partitions

            WHERE table_name = 'table2' AND TABLE_OWNER = 'username'

            ORDER BY partition_position DESC

        )

        WHERE ROWNUM < 20

        OFFSET 2 ROWS FETCH NEXT 1 ROWS ONLY;

SELECT table_name, owner,PARTITION_COUNT

        FROM all_part_tables

        WHERE owner = 'username' AND PARTITION_COUNT > 30;

cat check_partition.sh

seq1=`sqlplus -S "/ as sysdba"<<'EOF'

set heading off

set pagesize 0;

set feedback off;

set verify off;

set echo off;

select count(1) from user1.EDC_LOT_SUM_NEW           partition(EDC_LOT_SUM_NEW_202910);

quit;

EOF`

seq3=`sqlplus -S "/ as sysdba"<<'EOF'

set heading off

set pagesize 0;

set feedback off;

set verify off;

set echo off;

select count(1) from user1.OFFLINE_LOT_SUM_NEW       partition(OFFLINE_LOT_SUM_NEW_202910);

quit;

EOF`

seq6=`sqlplus -S "/ as sysdba"<<'EOF'

set heading off

set pagesize 0;

set feedback off;

set verify off;

set echo off;

select count(1) from user1.LOT_QTIME_HIST            partition(LOT_Q_TIME_HIST_202910);

quit;

EOF`

seq10=`sqlplus -S "/ as sysdba"<<'EOF'

set heading off

set pagesize 0;

set feedback off;

set verify off;

set echo off;

select count(1) from user1.OFFLINE_SHOT_NEW          partition(OFFLINE_SHOT_NEW_202910);

quit;

EOF`

seq11=`sqlplus -S "/ as sysdba"<<'EOF'

set heading off

set pagesize 0;

set feedback off;

set verify off;

set echo off;

select count(1) from user1.EDC_WAFER_SUM_NEW         partition(EDC_WAFER_SUM_NEW_202910);

quit;

EOF`

seq18=`sqlplus -S "/ as sysdba"<<'EOF'

set heading off

set pagesize 0;

set feedback off;

set verify off;

set echo off;

select count(1) from user1.EQP_STATUS_HIST           partition(EQP_STATUS_HIST_202910);

quit;

EOF`

if [ $seq1 -eq 0 ]&& [ $seq3 -eq 0 ]&& [ $seq6 -eq 0 ]&& [ $seq10 -eq 0 ]&& [ $seq11 -eq 0 ]&& [ $seq12 -eq 0 ]&& [ $seq13 -eq 0 ]&& [ $seq14 -eq 0 ]&& [ $seq15 -eq 0 ]&& [ $seq18 -eq 0 ] ; then

    echo 0 > /tmp/chk_partition_monitor.log

else

    echo 1 > /tmp/chk_partition_monitor.log

fi