【Oracle】ORA-04043の原因と解決方法｜object does not exist・存在しないオブジェクトを参照した時の確認ポイント

SELECT object_name,
       object_type,
       status,
       created,
       last_ddl_time
FROM user_objects
WHERE object_name = UPPER('target_object')
ORDER BY object_type, object_name;

SELECT owner,
       object_name,
       object_type,
       status,
       last_ddl_time
FROM all_objects
WHERE object_name = UPPER('target_object')
ORDER BY owner, object_type;

SELECT owner,
       object_name,
       object_type,
       status,
       last_ddl_time
FROM dba_objects
WHERE object_name = UPPER('target_object')
ORDER BY owner, object_type;

-- スキーマ修飾して参照する
SELECT * FROM app_owner.orders;

-- PL/SQLオブジェクトもスキーマ修飾できる
BEGIN
  app_owner.order_pkg.create_order;
END;
/

SELECT USER AS login_user,
       SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA') AS current_schema,
       SYS_CONTEXT('USERENV', 'CURRENT_USER') AS current_user
FROM dual;

-- セッション内の名前解決先を変更する例
ALTER SESSION SET CURRENT_SCHEMA = app_owner;

-- この後は app_owner.orders を orders として参照できる
SELECT * FROM orders;

SELECT owner,
       synonym_name,
       table_owner,
       table_name,
       db_link
FROM all_synonyms
WHERE synonym_name = UPPER('target_object')
ORDER BY owner, synonym_name;

SELECT owner,
       synonym_name,
       table_owner,
       table_name,
       db_link
FROM all_synonyms
WHERE owner IN (USER, 'PUBLIC')
  AND synonym_name = UPPER('target_object')
ORDER BY CASE owner WHEN USER THEN 1 WHEN 'PUBLIC' THEN 2 ELSE 3 END;

SELECT o.owner,
       o.object_name,
       o.object_type,
       o.status
FROM all_synonyms s
LEFT JOIN all_objects o
  ON o.owner = s.table_owner
 AND o.object_name = s.table_name
WHERE s.synonym_name = UPPER('target_object');

-- ダブルクォートで小文字名の表を作成
CREATE TABLE "orders" (
  id NUMBER
);

-- これは別名 ORDERS として解釈されるため見つからない
DESC orders;

-- 作成時と同じ表記で参照する
DESC "orders";

SELECT owner,
       object_name,
       object_type
FROM all_objects
WHERE LOWER(object_name) = LOWER('target_object')
ORDER BY owner, object_name;

SELECT owner,
       table_name,
       privilege,
       grantor,
       grantee
FROM all_tab_privs
WHERE table_name = UPPER('target_object')
ORDER BY owner, grantee, privilege;

SELECT role,
       owner,
       table_name,
       privilege
FROM role_tab_privs
WHERE table_name = UPPER('target_object')
ORDER BY role, owner, privilege;

-- 参照権限を直接付与する例
GRANT SELECT ON app_owner.orders TO app_user;

-- PL/SQLから使う場合はロール経由ではなく直接権限が必要になることがある
GRANT EXECUTE ON app_owner.order_pkg TO app_user;

SELECT owner,
       name,
       type,
       referenced_owner,
       referenced_name,
       referenced_type
FROM all_dependencies
WHERE name = UPPER('target_package')
ORDER BY referenced_owner, referenced_name;

SELECT name,
       type,
       line,
       position,
       text
FROM user_errors
WHERE name = UPPER('target_package')
ORDER BY sequence;

SELECT owner,
       object_name,
       object_type,
       status
FROM all_objects
WHERE object_name = UPPER('target_object')
ORDER BY owner, object_type;