Ora 20000 что за ошибка

ORA-20000 Unable to Set Values for Index XX: Does Not Exist or Insufficient Priv is Raised While Executing Impdp (Doc ID 2176364.1)

There are two users (USER_A / USER_B) and each user has objects as below and statistics are gotten for them.

USER_A:
1. btree_tbl(table) and btree_index(normal index)
2. bitmap_tbl(table) and bitmap_index(bitmap index)

USER_B:
1. btree_tbl(table) and btree_index(normal index)

Then execute expdp as USER_A:

expdp USER_A/USER_A directory=test_dir dumpfile=exp_data.dmp include=statistics tables=btree_tbl reuse_dumpfiles=y

And execute impdp as USER_B:

impdp USER_B/USER_B directory=test_dir dumpfile=exp_data.dmp remap_schema=user_a:user_b

Because expdp is using «tables=btree_tbl», statistics for only this table should be exported.

But the error is for bitmap_index of bitmap_tbl table which is not imported.

Testcase below reproduces the problem:

conn / as sysdba

— Create user and directory
create user USER_A identified by USER_A default tablespace users;
grant dba to USER_A;
grant unlimited tablespace to USER_A;
create user USER_B identified by USER_B default tablespace users;
grant dba to USER_B;
grant unlimited tablespace to USER_B;
create or replace directory TEST_DIR as ‘/tmp’;
grant read, write on directory TEST_DIR to USER_A;
grant read, write on directory TEST_DIR to USER_B;

— Create objects for USER_A
conn USER_A/USER_A
show user
create table btree_tbl (col1 number(1), col2 number(2));
create index btree_index on btree_tbl (col1);
create table bitmap_tbl (col1 number(1), col2 number(2));
create bitmap index bitmap_index on bitmap_tbl (col1);
exec DBMS_STATS.GATHER_TABLE_STATS(ownname => ‘USER_A’ ,tabname => ‘BTREE_TBL’);
exec DBMS_STATS.GATHER_TABLE_STATS(ownname => ‘USER_A’ ,tabname => ‘BITMAP_TBL’);

— Create objects for USER_B
conn USER_B/USER_B
show user
create table btree_tbl (col1 number(1), col2 number(2));
create index btree_index on btree_tbl (col1);

— Execute expdp and impdp
!expdp USER_A/USER_A directory=test_dir dumpfile=exp_data.dmp include=statistics tables=btree_tbl reuse_dumpfiles=y
!impdp USER_B/USER_B directory=test_dir dumpfile=exp_data.dmp remap_schema=user_a:user_b

Changes

Cause

To view full details, sign in with your My Oracle Support account.

Don’t have a My Oracle Support account? Click to get started!

In this Document

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.

Oracle offers a comprehensive and fully integrated stack of cloud applications and platform services. For more information about Oracle (NYSE:ORCL), visit oracle.com. � Oracle | Contact and Chat | Support | Communities | Connect with us | | | | Legal Notices | Terms of Use

Ora 20000 что за ошибка

Programs that rely on PL/SQL can often be hit with run-time errors that occur due to faults in design, problems with coding and a number of other issues. However, one of the great aspects of working with PL/SQL in Oracle is that the user can plan for the errors that frequently arise by creating warnings, or exceptions, to signal them.

The user can have exceptions for items in a database such as “insufficient_budget” that signal when more funding is allocated to a particular budget category than what is owned. When the error occurs, an exception is raised and users can write routines called ‘exception handlers’ that essentially skip over the procedure to allow continuous running. The ORA-20000 concerns these type of user-defined errors as well as other errors that are artificially tacked onto a program to facilitate a database manager’s needs.

The Problem

The ORA-20000 is a generic error that almost always accompanies another error or a stack of errors. It is part of the reserved section of PL/SQL user-defined errors. The error is caused when a stored procedure (‘raise_application_error’) is called upon. Oracle raises exceptions from the innermost to the outermost error, so when the ORA-20000 is seen in front of a stack of errors, the user knows that the innermost error, or bottom, is the block that can serve as the catalyst.

The amount of information available on the ORA-20000 is minimal due primarily to its open-endedness. Essentially, when a user sees an ORA-20000, their goal is not necessarily to correct the ORA-20000. Instead, they need to resolve the error accompanying an ORA-20000, regardless of whether it is a user-created error or a reserved error. Because the error accompanies several other error messages, let us look at some of the more common combinations for the ORA-20000.

The Solution

One example of the ORA-20000 conjoined with another set of errors is shown below. Suppose the following stack of exceptions are thrown together:

ORA-20000: ORA-20000: ORA-0000: normal, successful completion
Update failed for the ch_clnt_mast
Line: 632 Execution of ap_old_ib_terms_xfer_dr failed Line: 1045
ORA-06512: at “AEPRDFCRH.ORA_RAISERROR”, line 16
ORA-06512: at “AEPRDFCRH.AP_OL_IB_TERMS_XFER_DR”, line 935

To review, the ORA-06512 is an error caused when the stack is unwound by unhandled exceptions in the code. As previously mentioned, the ORA-06512 error and ORA-20000 error are often triggered together. To fix these errors, the user would need to correct the condition causing the errors or write an exception handler.

To begin correcting the stack of errors, check the code in the lines indicated in the error message. In this particular case, the user-defined error likely occurred due to being place in a WHEN OTHERS exception. Check over the code in line 632 (update failed for the ch_clnt_mast) as well as line 1045 (ap_old_ib_terms_xfer_dr failed). The user will have to remove or work with the exception handlers that are masking the real error message so they can rerun the code to discover what is occurring in the system.

Another common error combination is the ORA-20000: ORU-10027: buffer overflow. DBMS_OUTPUT has various default buffer sizes that all depend on the user’s version of Oracle. In the system, the buffer size limit is 2000 bytes. The user can extend the buffer all the way to 1,000,000 bytes by issuing the statement below:

DBMS_OUTPUT.ENABLE(1000000);

The comparable SQL*Plus statement looks like this:

set serveroutput on size 1000000

If the user is working with Oracle’s 10g release or something more recent, unlimited buffer settings can be set with the following:

DBMS_OUTPUT.ENABLE (buffer_size => NULL);

And the SQL*Plus version:

set serveroutput on size unlimited

This should offset the ORA-20000: ORU-10027, but, if the user conducts this approach and is still triggering the error, it is recommended to look back through the code in full to see if any items are overriding the buffer settings.

Looking forward

The ORA-20000 can be confusing and has such a wide range of responses that it would be impossible to cover them all here. If you find that you are having a difficult time managing the stack, contact your database manager or a licensed Oracle consultant to receive further instruction on correcting the error.

Русские Блоги

1. Раскройте ошибку.
Следующая ошибка была обнаружена в файле alert.log Oracle10.2.0.1.

Тщательный осмотр, об этой ошибке сообщают почти каждую ночь в 10 часов. Очевидно, что-то пошло не так.

2. Проверьте файл trc и найдите следующую ошибку

Задача Oracle AUTO_SPACE_ADVISOR_JOB.

Воспроизведите и проверьте ошибки

Конечно же, произошла та же ошибка.

4, найдите проблемное имя табличного пространства: см. ( сообщение от otn ）

Конечно, есть несоответствие (это вызвано ошибкой в ​​Oracle, Oracle 10.2.0.1 не обновляет автоматически таблицу словаря).

5. Решить проблему в три этапа ( Справочный документ ）
Создайте табличное пространство, которое существует в DBA_AUTO_SEGADV_CTL, но на самом деле не существует (достаточно 100 КБ, цель заимствовать его «имя»):

—- Конечно, об ошибке не сообщается
Удалить табличное пространство

6. Проверьте еще раз
пробег

Об ошибках по-прежнему не сообщается, что свидетельствует о том, что проблема решена.

Еще одна заметка:
Невозможно напрямую удалить записи в DBA_AUTO_SEGADV_CTL.

После запуска exec dbms_space.auto_space_advisor_job_proc; TBS_DNINMSV30 появится снова

Интеллектуальная рекомендация

Реализация JavaScript Hashtable

причина Недавно я смотрю на «Структуру данных и алгоритм — JavaScript», затем перейдите в NPMJS.ORG для поиска, я хочу найти подходящую ссылку на библиотеку и записывать его, я могу исполь.

MySQL общие операции

jdbc Транзакция: транзакция, truncate SQL заявление Transaction 100 000 хранимая процедура mysql msyql> -определить новый терминатор,Пробелов нет mysql>delimiter // mysql> -создание хранимой .

Используйте Ansible для установки и развертывания TiDB

жизненный опыт TiDB — это распределенная база данных. Настраивать и устанавливать службы на нескольких узлах по отдельности довольно сложно. Чтобы упростить работу и облегчить управление, рекомендуетс.

Последняя версия в 2019 году: использование nvm под Windows для переключения между несколькими версиями Node.js.

С использованием различных интерфейсных сред вы можете переключаться между разными версиями в любое время для разработки. Например, развитие 2018 года основано наNode.js 7x версия разработана. Тебе эт.

Шаблон проектирования — Создать тип — Заводской шаблон

Заводская модель фабрикиPattern Решать проблему: Решен вопрос, какой интерфейс использовать принципСоздайте интерфейс объекта, класс фабрики которого реализуется его подклассом, чтобы процесс создания.

ORA-20000: Insufficient privileges to Analyze an object in Schema during Gather DBMS_STATS

Sometimes You can get ” ORA-20000: Insufficient privileges to Analyze an object in Schema ” error during Gather DBMS_STATS Database, Schema, Table and Dictionary Stats.

ORA-20000: Insufficient privileges

Details of error are as follows.

ORA-20000: Insufficient privileges to Analyze an object in Schema

When you start the gather stats job without sys user ( sysdba priviliges ), you can get this error.

To solve this error, you should grant ANALYZE ANY DICTIONARY,ANALYZE ANY priviliged to the the related user as follows.

Gather DBMS_STATS

You can gather dictionary stats as follows.

You can gather any schema stats as follows.

You can gather any table stats as follows.

Do you want to learn more details about Database Stats , Schema Stats & Dictionary and Fixed Object Statistics, then read the following post.

Do you want to learn more details about RMAN, then read the following articles.

2,613 views last month, 4 views today

About Mehmet Salih Deveci

4 comments

I used to be recommended this website via my cousin. I am not sure whether this submit is written via him as nobody else
know such designated approximately my trouble. You’re amazing!
Thanks!

Thank you for sharing superb informations. Your web-site is so cool

thanks for your nice comments.

Hello! I was facing an oracle error (ORA-20000: Insufficient privileges to Analyze an object in Schema), I found the solution in your blog. In one of the steps you recommend taking the statistics of the fixed views, why? how do these views affect tables that do not correspond to the oracle catalog? Thank you very much