获取Oracle number类型分区表最后一个分区是否存在数据

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