Table analyzed.

# 2. 验证表和关联索引结构
SQL> analyze table scott.emp validate structure cascade;

Table analyzed.

# 3. 验证索引结构
SQL> analyze index scott.pk_emp validate structure;

Index analyzed.

# 4. 验证分区表结构
SQL> analyze table scott.fgfgfgsales validate structure;

Table analyzed.

# 5. 验证簇结构
SQL> analyze cluster scott.emp_dept validate structure;

Cluster analyzed.

# 6. 创建INVALID_ROWS表
SQL> @?/rdbms/admin/utlvalid.sql

Table created.

# 7. 验证表结构并记录损坏行
SQL> analyze table scott.emp validate structure into invalid_rows;

Table analyzed.

# 8. 查看验证结果
SQL> select * from invalid_rows;

OWNER_NAME TABLE_NAME PARTITION_NAME HEAD_ROWID ANALYZE_TIMESTAMP
———- ———- ————— —————— ——————–
SCOTT EMP AAASdqAAEAAAAInAAA 2026-03-31 10:00:00

# 9. 验证所有表
SQL> begin
for rec in (select owner, table_name from dba_tables where owner = ‘SCOTT’) loop
execute immediate ‘analyze table ‘ || rec.owner || ‘.’ || rec.table_name || ‘ validate structure cascade’;
end loop;
end;
/

PL/SQL procedure successfully completed.

# 10. 验证所有索引
SQL> begin
for rec in (select owner, index_name from dba_indexes where owner = ‘SCOTT’) loop
execute immediate ‘analyze index ‘ || rec.owner || ‘.’ || rec.index_name || ‘ validate structure’;
end loop;
end;
/

PL/SQL procedure successfully completed.

Table created.

# 2. 分析链式行
SQL> analyze table scott.emp list chained rows;

Table analyzed.

# 3. 查看链式行
SQL> select * from chained_rows;

OWNER_NAME TABLE_NAME CLUSTER_NAME PARTITION_NAME HEAD_ROWID ANALYZE_TIMESTAMP
———- ———- ————- ————— —————— ——————–
SCOTT EMP AAASdqAAEAAAAInAAA 2026-03-31 10:00:00
SCOTT EMP AAASdqAAEAAAAInAAB 2026-03-31 10:00:00

# 4. 统计链式行数量
SQL> select table_name, count(*) as chained_count
from chained_rows
group by table_name;

TABLE_NAME CHAINED_COUNT
———- ————-
EMP 2

# 5. 分析所有表的链式行
SQL> begin
for rec in (select owner, table_name from dba_tables where owner = ‘SCOTT’) loop
execute immediate ‘analyze table ‘ || rec.owner || ‘.’ || rec.table_name || ‘ list chained rows’;
end loop;
end;
/

PL/SQL procedure successfully completed.

# 6. 查看所有链式行
SQL> select owner_name, table_name, count(*) as chained_count
from chained_rows
group by owner_name, table_name;

OWNER_NAME TABLE_NAME CHAINED_COUNT
———- ———- ————-
SCOTT EMP 2
SCOTT DEPT 1

# 7. 消除链式行
SQL> create table emp_temp as select * from emp where rowid in (
select head_rowid from chained_rows where table_name = ‘EMP’
);

Table created.

SQL> delete from emp where rowid in (
select head_rowid from chained_rows where table_name = ‘EMP’
);

2 rows deleted.

SQL> insert into emp select * from emp_temp;

2 rows created.

SQL> drop table emp_temp;

Table dropped.

SQL> commit;

Commit complete.

# 8. 验证链式行已消除
SQL> truncate table chained_rows;

Table truncated.

SQL> analyze table scott.emp list chained rows;

Table analyzed.

SQL> select * from chained_rows;

no rows selected

#!/bin/bash
# from:www.itpux.com.qq113257174.wx:itpux-com
# web: http://www.fgedu.net.cn
# from:www.itpux.com.qq113257174.wx:itpux-com
# web: http://www.fgedu.net.cn
# from:www.itpux.com.qq113257174.wx:itpux-com
# web: http://www.fgedu.net.cn
# 表分析验证监控脚本

export ORACLE_HOME=/oracle/app/oracle/product/19c/dbhome_1
export ORACLE_SID=FGEDUDB
export PATH=$ORACLE_HOME/bin:$PATH

LOG_DIR=”/home/oracle/scripts/analyze_logs”
mkdir -p $LOG_DIR

echo “$(date): Starting table analysis validation…” > $LOG_DIR/analyze_monitor.log

# 获取所有用户表
sqlplus -s / as sysdba < /tmp/tables.txt
set heading off
set feedback off
set linesize 200
select owner || ‘,’ || table_name from dba_tables where owner in (‘SCOTT’, ‘HR’, ‘SH’) order by owner, table_name;
exit
EOF

# 验证每个表
while IFS=’,’ read -r owner table_name; do
echo “$(date): Analyzing $owner.$table_name…” >> $LOG_DIR/analyze_monitor.log

sqlplus -s / as sysdba <> $LOG_DIR/analyze_monitor.log
set serveroutput on
declare
v_count number;
begin
execute immediate ‘analyze table $owner.$table_name validate structure cascade’;
dbms_output.put_line(‘Table $owner.$table_name validated successfully’);
exception
when others then
dbms_output.put_line(‘Error validating table $owner.$table_name: ‘ || sqlerrm);
end;
/
exit
EOF

done < /tmp/tables.txt echo "$(date): Table analysis validation completed." >> $LOG_DIR/analyze_monitor.log

# 发送报告
mail -s “Table Analysis Validation Report” admin@fgedu.net.cn < $LOG_DIR/analyze_monitor.log # 2. 设置脚本权限 $ chmod +x /home/oracle/scripts/monitor_analyze.sh # 3. 测试脚本 $ /home/oracle/scripts/monitor_analyze.sh # 4. 设置定期监控 $ crontab -e # 每周日凌晨3点验证表结构 0 3 * * 0 /home/oracle/scripts/monitor_analyze.sh >> /home/oracle/scripts/analyze_monitor.log 2>&1

#!/bin/bash
# from:www.itpux.com.qq113257174.wx:itpux-com
# web: http://www.fgedu.net.cn
# from:www.itpux.com.qq113257174.wx:itpux-com
# web: http://www.fgedu.net.cn
# from:www.itpux.com.qq113257174.wx:itpux-com
# web: http://www.fgedu.net.cn
# 链式行监控脚本

export ORACLE_HOME=/oracle/app/oracle/product/19c/dbhome_1
export ORACLE_SID=FGEDUDB
export PATH=$ORACLE_HOME/bin:$PATH

LOG_DIR=”/home/oracle/scripts/chained_logs”
mkdir -p $LOG_DIR

echo “$(date): Starting chained rows analysis…” > $LOG_DIR/chained_monitor.log

# 分析所有用户表的链式行
sqlplus -s / as sysdba <> $LOG_DIR/chained_monitor.log
set serveroutput on

— 清空CHAINED_ROWS表
truncate table chained_rows;

— 分析所有表
begin
for rec in (select owner, table_name from dba_tables where owner in (‘SCOTT’, ‘HR’, ‘SH’)) loop
begin
execute immediate ‘analyze table ‘ || rec.owner || ‘.’ || rec.table_name || ‘ list chained rows’;
exception
when others then
dbms_output.put_line(‘Error analyzing ‘ || rec.owner || ‘.’ || rec.table_name || ‘: ‘ || sqlerrm);
end;
end loop;
end;
/

— 统计链式行
set heading on
set feedback on
column owner_name format a15
column table_name format a30
column chained_count format 999,999

select owner_name, table_name, count(*) as chained_count
from chained_rows
group by owner_name, table_name
order by count(*) desc;

exit
EOF

echo “$(date): Chained rows analysis completed.” >> $LOG_DIR/chained_monitor.log

# 发送报告
mail -s “Chained Rows Analysis Report” admin@fgedu.net.cn < $LOG_DIR/chained_monitor.log # 2. 设置脚本权限 $ chmod +x /home/oracle/scripts/monitor_chained_rows.sh # 3. 测试脚本 $ /home/oracle/scripts/monitor_chained_rows.sh # 4. 设置定期监控 $ crontab -e # 每月1日凌晨4点分析链式行 0 4 1 * * /home/oracle/scripts/monitor_chained_rows.sh >> /home/oracle/scripts/chained_monitor.log 2>&1

# 分析步骤

# 1. 查看表和索引信息
SQL> select table_name, status from dba_tables where owner = ‘SCOTT’ and table_name = ‘EMP’;

TABLE_NAME STATUS
———- ——–
EMP VALID

SQL> select index_name, status from dba_indexes where owner = ‘SCOTT’ and table_name = ‘EMP’;

INDEX_NAME STATUS
———- ——–
PK_EMP VALID
IDX_EMP_ENAME VALID

# 2. 单独验证表结构
SQL> analyze table scott.emp validate structure;

Table analyzed.

# 3. 单独验证索引结构
SQL> analyze index scott.pk_emp validate structure;

Index analyzed.

SQL> analyze index scott.idx_emp_ename validate structure;
analyze index scott.idx_emp_ename validate structure
*
ERROR at line 1:
ORA-01499: table/index cross reference failure

# 4. 分析错误原因
# 错误代码：ORA-01499
# 错误信息：table/index cross reference failure
# 问题索引：IDX_EMP_ENAME
# 错误原因：索引与表数据不一致

# 5. 解决方案：重建索引
SQL> alter index scott.idx_emp_ename rebuild;

Index altered.

# 6. 验证修复结果
SQL> analyze table scott.emp validate structure cascade;

Table analyzed.

# 7. 验证索引状态
SQL> select index_name, status from dba_indexes where owner = ‘SCOTT’ and table_name = ‘EMP’;

INDEX_NAME STATUS
———- ——–
PK_EMP VALID
IDX_EMP_ENAME VALID

# 8. 预防措施
# – 定期验证表和索引结构
# – 监控索引状态
# – 及时重建失效索引

Table analyzed.

# 2. 查看链式行数量
SQL> select count(*) from chained_rows;

COUNT(*)
———-
1000

# 3. 查看链式行详情
SQL> select * from chained_rows where rownum <= 10;

OWNER_NAME TABLE_NAME CLUSTER_NAME PARTITION_NAME HEAD_ROWID ANALYZE_TIMESTAMP
———- ———— ————- ————— —————— ——————–
SCOTT LARGE_TABLE AAASdqAAEAAAAInAAA 2026-03-31 10:00:00
SCOTT LARGE_TABLE AAASdqAAEAAAAInAAB 2026-03-31 10:00:00

# 4. 分析链式行原因
SQL> select
table_name,
avg_row_len,
chain_cnt,
round(chain_cnt / num_rows * 100, 2) as chain_pct
from dba_tables
where owner = ‘SCOTT’ and table_name = ‘LARGE_TABLE’;

TABLE_NAME AVG_ROW_LEN CHAIN_CNT CHAIN_PCT
———— ———– ———- ———-
LARGE_TABLE 2000 1000 10.00

# 5. 查看表空间块大小
SQL> select tablespace_name, block_size
from dba_tablespaces
where tablespace_name = ‘USERS’;

TABLESPACE_NAME BLOCK_SIZE
—————– ———-
USERS 8192

# 6. 解决方案1：增加块大小（需要重建表空间）
# 解决方案2：消除链式行

# 7. 消除链式行
SQL> create table large_table_temp as
select * from large_table
where rowid in (select head_rowid from chained_rows where table_name = ‘LARGE_TABLE’);

Table created.

SQL> delete from large_table
where rowid in (select head_rowid from chained_rows where table_name = ‘LARGE_TABLE’);

1000 rows deleted.

SQL> insert into large_table select * from large_table_temp;

1000 rows created.

SQL> drop table large_table_temp;

Table dropped.

SQL> commit;

Commit complete.

# 8. 验证链式行已消除
SQL> truncate table chained_rows;

Table truncated.

SQL> analyze table scott.large_table list chained rows;

Table analyzed.

SQL> select count(*) from chained_rows;

COUNT(*)
———-
0

# 9. 更新表统计信息
SQL> exec dbms_stats.gather_table_stats(‘SCOTT’, ‘LARGE_TABLE’);

PL/SQL procedure successfully completed.