How to locate unknown objects

 

A few days ago, I saw someone asking me how to find the table referenced by a view, or if a table was referenced by those objects, I did not pay attention to it. Today, my friend asked me.
I think there is a view to express this dependency, So I open DBA studio, enter solution --- view --- sys, and then quickly browse the view like DBA, judging from experience that many views are not needed, we finally see a view: dba_dependencies, which is not a good choice in English. I guess it may be.

SQL> DESC dba_dependencies
Name null? Type
-----------------------------------------------------------------------------
Owner not null varchar2 (30)
Name not null varchar2 (30)
Type varchar2 (17)
Referenced_owner varchar2 (30)
Referenced_name varchar2 (64)
Referenced_type varchar2 (17)
Referenced_link_name varchar2 (128)
Dependency_type varchar2 (4)

Select U. Name, O. Name,
Decode (O. Type #, 0, 'next object', 1, 'index', 2, 'table', 3, 'cluster ',
4, 'view', 5, 'synonym', 6, 'sequence ', 7, 'Procedure ',
8, 'function', 9, 'package', 10, 'non-existent ',
11, 'package body', 12, 'trigger ',
13, 'type', 14, 'Type body ',
28, 'java source', 29, 'java class', 56, 'java data ',
'Undefined '),
Decode (PO. linkname, null, PU. Name, Po. remoteowner), Po. Name,
Decode (PO. Type #, 0, 'next object', 1, 'index', 2, 'table', 3, 'cluster ',
4, 'view', 5, 'synonym', 6, 'sequence ', 7, 'Procedure ',
8, 'function', 9, 'package', 10, 'non-existent ',
11, 'package body', 12, 'trigger ',
13, 'type', 14, 'Type body ',
28, 'java source', 29, 'java class', 56, 'java data ',
'Undefined '),
Po. linkname,
Decode (D. Property, 2, 'ref ', 'hard ')
From SYS. OBJ $ o, SYS. disk_and_fixed_objects Po, SYS. Dependency $ D, SYS. User $ U,
SYS. User $ Pu
Where o. OBJ # = D. d_obj #
And O. Owner # = U. User #
And po. OBJ # = D. p_obj #
And po. Owner # = pu. User #

At this point, we have already confirmed it by 99% and tested it again. This view records the dependencies.

Next, let's talk about a common method. It's troublesome to remember so many things, and I cannot remember so much. Sometimes it's troublesome to open OEM/DBA studio, if I want to check the related role

SQL> select object_name from dba_objects where object_name like '% role % ';

Object_name
--------------------------------------------------------------------------------
Dba_roles
Dba_role_privs
Defrole $
I _defrole1
JIS $ role_trigger $
Role_role_privs
Role_sys_privs
Role_tab_privs
Session_roles
User_role_privs
Dba_roles

Object_name
--------------------------------------------------------------------------------
Dba_role_privs
Role_role_privs
Role_sys_privs
Role_tab_privs
Session_roles
User_role_privs
Vbz $ rg_role_oidx
Vbz $ role_grants

19 rows selected.

As you can see in the results, do you know which views have recorded the relevant content? Most of the time, you can check it at DESC or test it again. You can also check the document and locate it easily, this is a long-term method for individuals who are not familiar with views. It is simple to say, but effective !!!!