Shulou(Shulou.com)05/31 Report--

This article mainly explains "Oracle related data Dictionary Analysis". Interested friends may wish to have a look. The method introduced in this paper is simple, fast and practical. Now let the editor take you to learn "Oracle-related data Dictionary Analysis"!

Problem description: alert log found error primary key conflict

ORA-12012: error in automatic execution of job 24

ORA-00001: violation of unique constraints (ADDEDTAXUSER.PK_PT_INOUTFLOW)

ORA-06512: in "DWETL.SP_ETL_CONTROL", line 519

ORA-06512: in line 7

Through the error report, we can see that the PK_PT_INOUTFLOW primary key under the ADDEDTAXUSER user is violated, and the error is reported when executing the SP_ETL_CONTROL things under the DWETL user.

Then find out what the relevant objects are and their creation statements through the data dictionary:

1. First, find out which table this primary key belongs to.

SQL > select OWNER,CONSTRAINT_NAME,TABLE_NAME from dba_constraints where CONSTRAINT_NAME='PK_PT_INOUTFLOW'

OWNER CONSTRAINT_NAME TABLE_NAME

-ADDEDTAXUSER PK_PT_INOUTFLOW PT_INOUTFLOW

two。 To query which field of which table the primary key is located, you can use dba_ind_columns or dba_cons_columns

SQL > select INDEX_OWNER,INDEX_NAME,TABLE_NAME,COLUMN_NAME from dba_ind_columns where INDEX_NAME='PK_PT_INOUTFLOW'

INDEX_OWNER INDEX_NAME COLUMN_NAME TABLE_NAME

ADDEDTAXUSER PK_PT_INOUTFLOW PT_INOUTFLOW PK_INOUTFLOW

Or

SQL > select owner,CONSTRAINT_NAME,table_name,COLUMN_NAME from dba_cons_columns where CONSTRAINT_NAME='PK_PT_INOUTFLOW'

OWNER CONSTRAINT_NAME TABLE_NAME COLUMN_NAME

-

ADDEDTAXUSER PK_PT_INOUTFLOW PT_INOUTFLOW PK_INOUTFLOW

3. Then check what is the SP_ETL_CONTROL under the DWETL user?

You can see that it is a stored procedure, as follows:

SQL > select OWNER,OBJECT_NAME,OBJECT_TYPE from dba_objects where OBJECT_NAME='SP_ETL_CONTROL'

OWNER OBJECT_NAME OBJECT_TYPE

DWETL SP_ETL_CONTROLPROCEDURE

4. Look at the creation statement of this stored procedure, with the help of the get_ddl function of the dbms_metadata package:

Set line 200

Set pagesize 0

Set long 99999

Select dbms_metadata.get_ddl ('PROCEDURE','SP_ETL_CONTROL','DWETL') from dual

At this point, all the wrong information has been found out, and it can be communicated to the developer.

Through this question, by the way, the commonly used query statements are summarized:

One: query the creation statement of the object:

Select dbms_metadata.get_ddl ('TABLE','TABLE_NAME','TABLE_OWNER') from dual

Select dbms_metadata.get_ddl ('INDEX','INDEX_NAME','INDEX_OWNER') from dual

Select dbms_metadata.get_ddl ('VIEW','VIEW_NAME','VIEW_OWNER') from dual

Select dbms_metadata.get_ddl ('PROCEDURE','PROCEDURE_name','PROCEDURE_owner') from dual

Select dbms_metadata.get_ddl ('FUNCTION','FUNCTION_NAME','FUNCTION_OWNER') from dual

Second, query the information with log field in the database, and query it through DBA_TAB_COLUMNS and dba_lobs data dictionary:

SQL > select owner, table_name, COLUMN_NAME from dba_lobs where rownum select OWNER,TABLE_NAME,COLUMN_NAME,DATA_TYPE from DBA_TAB_COLUMNS where DATA_TYPE like'% LOB%' and rownum

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.