# 批量更新语法
UPDATE fgedu.orders
SET status = ‘COMPLETED’
WHERE order_date < TO_DATE('2026-01-01', 'YYYY-MM-DD'); # 批量更新示例 SQL> UPDATE fgedu.orders
2 SET status = ‘COMPLETED’
3 WHERE order_date < TO_DATE('2026-01-01', 'YYYY-MM-DD'); 10000 rows updated. Elapsed: 00:00:30 # 分批更新示例 SQL> BEGIN
2 FOR i IN 1..10 LOOP
3 UPDATE fgedu.orders
4 SET status = ‘COMPLETED’
5 WHERE order_date < TO_DATE('2026-01-01', 'YYYY-MM-DD') 6 AND rownum <= 1000; 7 COMMIT; 8 DBMS_LOCK.SLEEP(1); -- 暂停1秒，减少系统压力 9 END LOOP; 10 END; 11 / PL/SQL procedure successfully completed. Elapsed: 00:01:40 # 使用并行更新 SQL> UPDATE /*+ PARALLEL(4) */ fgedu.orders
2 SET status = ‘COMPLETED’
3 WHERE order_date < TO_DATE('2026-01-01', 'YYYY-MM-DD'); 10000 rows updated. Elapsed: 00:00:15

# 批量删除语法
DELETE FROM fgedu.orders
WHERE order_date < TO_DATE('2025-01-01', 'YYYY-MM-DD'); # 批量删除示例 SQL> DELETE FROM fgedu.orders
2 WHERE order_date < TO_DATE('2025-01-01', 'YYYY-MM-DD'); 5000 rows deleted. Elapsed: 00:00:20 # 分批删除示例 SQL> BEGIN
2 LOOP
3 DELETE FROM fgedu.orders
4 WHERE order_date < TO_DATE('2025-01-01', 'YYYY-MM-DD') 5 AND rownum <= 1000; 6 EXIT WHEN SQL%ROWCOUNT = 0; 7 COMMIT; 8 DBMS_LOCK.SLEEP(1); -- 暂停1秒，减少系统压力 9 END LOOP; 10 END; 11 / PL/SQL procedure successfully completed. Elapsed: 00:00:55 # 使用TRUNCATE语句（适用于清空表） SQL> TRUNCATE TABLE fgedu.orders;

Table truncated.

Elapsed: 00:00:01

# 创建分区表
SQL> CREATE TABLE fgedu.orders (
2 order_id NUMBER PRIMARY KEY,
3 customer_id NUMBER,
4 order_date DATE,
5 status VARCHAR2(20)
6 ) PARTITION BY RANGE (order_date) (
7 PARTITION p2025 VALUES LESS THAN (TO_DATE(‘2026-01-01’, ‘YYYY-MM-DD’)),
8 PARTITION p2026 VALUES LESS THAN (TO_DATE(‘2027-01-01’, ‘YYYY-MM-DD’)),
9 PARTITION p2027 VALUES LESS THAN (MAXVALUE)
10 );

# 批量删除分区数据
SQL> ALTER TABLE fgedu.orders DROP PARTITION p2025;

Table altered.

Elapsed: 00:00:05

# 批量更新分区数据
SQL> UPDATE fgedu.orders PARTITION (p2026)
2 SET status = ‘COMPLETED’
3 WHERE order_date < TO_DATE('2026-06-01', 'YYYY-MM-DD'); 5000 rows updated. Elapsed: 00:00:10

# 查看操作进度
SQL> SELECT * FROM v$session_longops;

# 查看会话信息
SQL> SELECT sid, serial#, username, program, status, event FROM v$session;

# 查看数据库状态
SQL> SELECT * FROM v$instance;

# 查看表空间使用情况
SQL> SELECT tablespace_name, used_percent FROM dba_tablespace_usage_metrics;

# 查看事务日志状态
SQL> SELECT * FROM v$log;

# 查看系统资源使用情况
SQL> SELECT * FROM v$sysstat WHERE name LIKE ‘%update%’ OR name LIKE ‘%delete%’;

# 查看锁状态
SQL> SELECT * FROM v$lock;

# 大批量更新和删除监控脚本

# 输出文件
output_file=”/tmp/batch_ud_monitor_$(date +%Y%m%d_%H%M%S).log”

echo “开始监控大批量更新和删除” > ${output_file}
echo “监控时间: $(date)” >> ${output_file}
echo “” >> ${output_file}

# 查看操作进度
echo “===== 操作进度 ====” >> ${output_file}
sqlplus -s / as sysdba << EOF >> ${output_file}
SELECT * FROM vsession_longops;
EXIT;
EOF
echo “” >> ${output_file}

# 查看会话信息
echo “===== 会话信息 ====” >> ${output_file}
sqlplus -s / as sysdba << EOF >> ${output_file}
SELECT sid, serial#, username, program, status, event FROM vsession;
EXIT;
EOF
echo “” >> ${output_file}

# 查看表空间使用情况
echo “===== 表空间使用情况 ====” >> ${output_file}
sqlplus -s / as sysdba << EOF >> ${output_file}
SELECT tablespace_name, used_percent FROM dba_tablespace_usage_metrics;
EXIT;
EOF
echo “” >> ${output_file}

# 查看锁状态
echo “===== 锁状态 ====” >> ${output_file}
sqlplus -s / as sysdba << EOF >> ${output_file}
SELECT * FROM vlock;
EXIT;
EOF
echo “” >> ${output_file}

echo “监控完成，结果保存至：${output_file}” >> ${output_file}
echo “监控完成，结果保存至：${output_file}”

# 1. 检查操作状态
SQL> SELECT * FROM v$session_longops;

# 2. 检查会话信息
SQL> SELECT sid, serial#, username, program, status, event FROM v$session;

# 3. 检查错误日志
SQL> SELECT * FROM v$diag_info;
$ tail -n 100 /yashandb/app/log/fgedudb_alert.log

# 4. 检查表空间使用情况
SQL> SELECT tablespace_name, used_percent FROM dba_tablespace_usage_metrics;

# 5. 检查系统资源使用情况
SQL> SELECT * FROM v$sysstat WHERE name LIKE ‘%update%’ OR name LIKE ‘%delete%’;

# 6. 检查数据库参数
SQL> SHOW PARAMETER shared_buffers;
SQL> SHOW PARAMETER work_mem;

# 7. 检查锁状态
SQL> SELECT * FROM v$lock;

# 8. 解决问题
# 终止卡住的会话
SQL> ALTER SYSTEM KILL SESSION ‘sid,serial#’;

# 增加表空间
SQL> ALTER TABLESPACE fgedutbs ADD DATAFILE ‘/yashandb/fgdata/fgedudb/fgedutbs02.dbf’ SIZE 100GB;

# 错误1：表空间不足
ERROR: ORA-01653: unable to extend table FGEDU.ORDERS by 128 in tablespace FGEDUTBS

# 解决方法：增加表空间
SQL> ALTER TABLESPACE fgedutbs ADD DATAFILE ‘/yashandb/fgdata/fgedudb/fgedutbs02.dbf’ SIZE 100GB;

# 错误2：回滚段不足
ERROR: ORA-01562: failed to extend rollback segment number 1

# 解决方法：增加回滚段
SQL> ALTER ROLLBACK SEGMENT rbs01 ONLINE;

# 错误3：锁冲突
ERROR: ORA-00060: deadlock detected while waiting for resource

# 解决方法：检查并解决锁冲突
SQL> SELECT * FROM v$lock_conflicts;

# 错误4：内存不足
ERROR: ORA-04030: out of process memory when trying to allocate 16777216 bytes

# 解决方法：增加内存或调整参数
SQL> ALTER SYSTEM SET work_mem = ’64MB’ SCOPE=spfile;

# 错误5：事务超时
ERROR: ORA-01013: user requested cancel of current operation

# 解决方法：增加事务超时时间或分批操作