# 批量插入语法
INSERT INTO fgedu.orders (order_id, customer_id, order_date, status)
VALUES
(1, 1001, TO_DATE(‘2026-04-01’, ‘YYYY-MM-DD’), ‘PENDING’),
(2, 1002, TO_DATE(‘2026-04-01’, ‘YYYY-MM-DD’), ‘PENDING’),
(3, 1003, TO_DATE(‘2026-04-01’, ‘YYYY-MM-DD’), ‘PENDING’),
…
(1000, 2000, TO_DATE(‘2026-04-01’, ‘YYYY-MM-DD’), ‘PENDING’);

# 批量插入示例
SQL> INSERT INTO fgedu.orders (order_id, customer_id, order_date, status)
2 VALUES
3 (1, 1001, TO_DATE(‘2026-04-01’, ‘YYYY-MM-DD’), ‘PENDING’),
4 (2, 1002, TO_DATE(‘2026-04-01’, ‘YYYY-MM-DD’), ‘PENDING’),
5 (3, 1003, TO_DATE(‘2026-04-01’, ‘YYYY-MM-DD’), ‘PENDING’);

3 rows created.

# 批量插入性能测试
# 插入1000行数据
SQL> BEGIN
2 FOR i IN 1..1000 LOOP
3 INSERT INTO fgedu.orders (order_id, customer_id, order_date, status)
4 VALUES (i, 1000 + i, SYSDATE, ‘PENDING’);
5 END LOOP;
6 COMMIT;
7 END;
8 /

PL/SQL procedure successfully completed.

Elapsed: 00:00:01.23

# 使用批量插入语句
SQL> INSERT INTO fgedu.orders (order_id, customer_id, order_date, status)
2 SELECT level, 1000 + level, SYSDATE, ‘PENDING’
3 FROM dual
4 CONNECT BY level <= 1000; 1000 rows created. Elapsed: 00:00:00.45

# 准备数据文件
$ cat /tmp/orders.csv
1,1001,2026-04-01,PENDING
2,1002,2026-04-01,PENDING
3,1003,2026-04-01,PENDING
…
1000,2000,2026-04-01,PENDING

# 使用COPY命令导入数据
SQL> COPY fgedu.orders FROM ‘/tmp/orders.csv’ DELIMITER ‘,’ CSV;

1000 rows copied.

Elapsed: 00:00:00.12

# 使用COPY命令的选项
# 指定分隔符
SQL> COPY fgedu.orders FROM ‘/tmp/orders.txt’ DELIMITER ‘|’;

# 指定NULL值
SQL> COPY fgedu.orders FROM ‘/tmp/orders.csv’ DELIMITER ‘,’ CSV NULL ‘NULL’;

# 指定编码
SQL> COPY fgedu.orders FROM ‘/tmp/orders.csv’ DELIMITER ‘,’ CSV ENCODING ‘UTF8’;

# 创建外部表
SQL> CREATE EXTERNAL TABLE fgedu.orders_ext (
2 order_id NUMBER,
3 customer_id NUMBER,
4 order_date DATE,
5 status VARCHAR2(20)
6 )
7 ORGANIZATION EXTERNAL (
8 TYPE ORACLE_LOADER
9 DEFAULT DIRECTORY data_dir
10 ACCESS PARAMETERS (
11 RECORDS DELIMITED BY NEWLINE
12 FIELDS TERMINATED BY ‘,’
13 MISSING FIELD VALUES ARE NULL
14 (order_id, customer_id, order_date DATE ‘YYYY-MM-DD’, status)
15 )
16 LOCATION (‘orders.csv’)
17 );

# 从外部表插入数据
SQL> INSERT INTO fgedu.orders (order_id, customer_id, order_date, status)
2 SELECT order_id, customer_id, order_date, status
3 FROM fgedu.orders_ext;

1000 rows created.

Elapsed: 00:00:00.23

# 查看插入进度
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 ‘%insert%’;

# 大批量插入监控脚本

# 输出文件
output_file=”/tmp/batch_insert_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 vsysstat WHERE name LIKE ‘%insert%’;
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 ‘%insert%’;

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

# 7. 检查网络连接
$ ping fgedu.net.cn
$ telnet fgedu.net.cn 1521

# 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-04030: out of process memory when trying to allocate 16777216 bytes

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

# 错误3：事务日志空间不足
ERROR: ORA-00312: online log 1 thread 1: ‘/yashandb/fgdata/fgedudb/redo01.log’

# 解决方法：增加重做日志文件
SQL> ALTER DATABASE ADD LOGFILE GROUP 4 (‘/yashandb/fgdata/fgedudb/redo04.log’) SIZE 200MB;

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

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

# 错误5：网络超时
ERROR: ORA-3113: end-of-file on communication channel

# 解决方法：检查网络连接
$ ping fgedu.net.cn