search
HomeDatabaseMysql TutorialOracle数据库重建无效和不可用对象

无效 PL/SQL 对象和不可用索引会对性能产生影响。无效 PL/SQL 对象必须先进行重编译,然后才能使用。这需要在执行尝试访问 PL/SQ

 无效和不可用对象

无效 PL/SQL 对象和不可用索引会对性能产生影响。无效 PL/SQL 对象必须先进行重编译,然后才能使用。这需要在执行尝试访问 PL/SQL 程序包、过程或函数的第一个操作之前花费一段编译时间。如果 PL/SQL 重编译未成功,,则操作会因发生错误而失败。优化程序会忽略不可用索引。如果 SQL 语句性能的好坏取决于已标记为不可用的索引,则只有重建索引才能改善性能。

无效 PL/SQL 对象:可通过查询数据字典查看 PL/SQL 对象的当前状态。

可使用以下命令行查找到无效 PL/SQL 对象:

SELECT object_name, object_type FROM DBA_OBJECTS

WHERE status = 'INVALID';

默认情况下,每 24 小时检查一次“所有者的无效对象计数”度量。如果一个所有者的对象数超过两个,就会发出预警。

如果查找到 INVALID 状态的 PL/SQL 对象,需要回答的第一个问题是“此对象曾经是 VALID 的吗?”应用程序开发人员常常会忘记清除不起作用的代码。如果 PL/SQL 对象因代码错误而无效,则除了纠正该错误之外,没有什么别的方法。如果该过程在过去曾经有效,最近才变为无效,则可选择以下两种方法解决这个问题:

1.不做任何处理。如果需要,大多数 PL/SQL 对象在调用时会自动重新编译。用户在对象重新编译时会经历短暂的延迟。(大多数情况下,这种延迟不十分明显。)

2.手动重新编译无效对象。

使用 Enterprise Manager 或通过 SQL 命令可手动重新编译无效的 PL/SQL 对象

ALTER PROCEDURE HR.add_job_history COMPILE;

手动重新编译 PL/SQL 数据包,需要执行以下两个步骤:

ALTER PACKAGE HR.maintainemp COMPILE;

ALTER PACKAGE HR.maintainemp COMPILE BODY;

不可用索引:可通过查询 DBA_INDEXES 数据字典视图查找无效索引:

SELECT index_name, table_name FROM DBA_INDEXES

WHERE status ='UNUSABLE';

对于分区索引,状态保存在 DBA_IND_PARTITIONS 视图中。

通过重建不可用索引来重算指针,可使不可用索引变为有效。重建不可用索引会在新位置重新创建索引,然后会删除不可用索引。

使用 Enterprise Manager 或通过 SQL 命令可完成此操作:

ALTER INDEX HR.emp_empid_pk REBUILD;

ALTER INDEX HR.emp_empid_pk REBUILD ONLINE;

ALTER INDEX HR.email REBUILD TABLESPACE USERS;

如果省略了 TABLESPACE 子句,则在曾经存在的相同表空间中重建索引。

使用 REBUILD ONLINE 子句,用户可以在重建时继续更新索引表。(如果不使用 ONLINE 关键字,用户必须等待重建完成后才能对受影响的表执行 DML。如果索引不可用,即使使用了 ONLINE 关键字,也不会在重建过程中使用该索引。)

Enterprise Manager 使用“Reorganize(重组)”操作修复 UNUSABLE 索引。

注:重建索引时需要有可用于重建的空闲空间。请在尝试重建前验证是否有足够的空间。Enterprise Manager 会自动检查空间要求。

--------------------------------------分割线 --------------------------------------

rlwrap - 解决Linux下SQLPLUS退格、上翻键乱码问题

SQLPLUS spool 到动态日志文件名

Oracle SQLPLUS提示符设置

通过设置SQLPLUS ARRAYSIZE(行预取)加快SQL返回速度

--------------------------------------分割线 --------------------------------------

本文永久更新链接地址:

linux

Statement
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
How do you alter a table in MySQL using the ALTER TABLE statement?How do you alter a table in MySQL using the ALTER TABLE statement?Mar 19, 2025 pm 03:51 PM

The article discusses using MySQL's ALTER TABLE statement to modify tables, including adding/dropping columns, renaming tables/columns, and changing column data types.

How do I configure SSL/TLS encryption for MySQL connections?How do I configure SSL/TLS encryption for MySQL connections?Mar 18, 2025 pm 12:01 PM

Article discusses configuring SSL/TLS encryption for MySQL, including certificate generation and verification. Main issue is using self-signed certificates' security implications.[Character count: 159]

How do you handle large datasets in MySQL?How do you handle large datasets in MySQL?Mar 21, 2025 pm 12:15 PM

Article discusses strategies for handling large datasets in MySQL, including partitioning, sharding, indexing, and query optimization.

What are some popular MySQL GUI tools (e.g., MySQL Workbench, phpMyAdmin)?What are some popular MySQL GUI tools (e.g., MySQL Workbench, phpMyAdmin)?Mar 21, 2025 pm 06:28 PM

Article discusses popular MySQL GUI tools like MySQL Workbench and phpMyAdmin, comparing their features and suitability for beginners and advanced users.[159 characters]

How do you drop a table in MySQL using the DROP TABLE statement?How do you drop a table in MySQL using the DROP TABLE statement?Mar 19, 2025 pm 03:52 PM

The article discusses dropping tables in MySQL using the DROP TABLE statement, emphasizing precautions and risks. It highlights that the action is irreversible without backups, detailing recovery methods and potential production environment hazards.

How do you create indexes on JSON columns?How do you create indexes on JSON columns?Mar 21, 2025 pm 12:13 PM

The article discusses creating indexes on JSON columns in various databases like PostgreSQL, MySQL, and MongoDB to enhance query performance. It explains the syntax and benefits of indexing specific JSON paths, and lists supported database systems.

How do you represent relationships using foreign keys?How do you represent relationships using foreign keys?Mar 19, 2025 pm 03:48 PM

Article discusses using foreign keys to represent relationships in databases, focusing on best practices, data integrity, and common pitfalls to avoid.

How do I secure MySQL against common vulnerabilities (SQL injection, brute-force attacks)?How do I secure MySQL against common vulnerabilities (SQL injection, brute-force attacks)?Mar 18, 2025 pm 12:00 PM

Article discusses securing MySQL against SQL injection and brute-force attacks using prepared statements, input validation, and strong password policies.(159 characters)

See all articles

Hot AI Tools

Undresser.AI Undress

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress AI Tool

Undress images for free

Clothoff.io

Clothoff.io

AI clothes remover

AI Hentai Generator

AI Hentai Generator

Generate AI Hentai for free.

Hot Article

Hot Tools

SAP NetWeaver Server Adapter for Eclipse

SAP NetWeaver Server Adapter for Eclipse

Integrate Eclipse with SAP NetWeaver application server.

SublimeText3 Linux new version

SublimeText3 Linux new version

SublimeText3 Linux latest version

SublimeText3 Mac version

SublimeText3 Mac version

God-level code editing software (SublimeText3)

Zend Studio 13.0.1

Zend Studio 13.0.1

Powerful PHP integrated development environment

SublimeText3 Chinese version

SublimeText3 Chinese version

Chinese version, very easy to use