Today, when i checking migration reliability i got an error,
ORA-02070. When i analyzed that i realized that columns which are LONG
data-type were caused of this problem:
Connected to Oracle Database 10g Enterprise Edition Release 10.1.0.5.0
Connected as DBUSER
SQL>
SQL> SELECT *
2 FROM all_constraints@UAT md
3 WHERE owner = 'DBUSER'
4 AND constraint_type = 'R'
5 AND NOT EXISTS
6 (SELECT 1
7 FROM all_constraints db
8 WHERE md.owner = db.owner
9 AND md.constraint_type = db.constraint_type
10 AND md.constraint_name = db.constraint_name);
SELECT *
FROM all_constraints@UAT md
WHERE owner = 'DBUSER'
AND constraint_type = 'R'
AND NOT EXISTS
(SELECT 1
FROM all_constraints db
WHERE md.owner = db.owner
AND md.constraint_type = db.constraint_type
AND md.constraint_name = db.constraint_name)
ORA-02070: veritabanı UAT bu içerik içinde operator USERENV desteklemez
database UAT does not support USERENV in this context
SQL> describe all_constraints@UAT;
Object all_constraints@UAT does not exist.
SQL> describe all_constraints;
Name Type Nullable Default Comments
----------------- ------------ -------- ------- ---------------------------------------------------------------------------
OWNER VARCHAR2(30) Owner of the table
CONSTRAINT_NAME VARCHAR2(30) Name associated with constraint definition
CONSTRAINT_TYPE VARCHAR2(1) Y Type of constraint definition
TABLE_NAME VARCHAR2(30) Name associated with table with constraint definition
---->>>>
SEARCH_CONDITION LONG Y Text of search condition for table check
----<<<<
R_OWNER VARCHAR2(30) Y Owner of table used in referential constraint
R_CONSTRAINT_NAME VARCHAR2(30) Y Name of unique constraint definition for referenced table
DELETE_RULE VARCHAR2(9) Y The delete rule for a referential constraint
STATUS VARCHAR2(8) Y Enforcement status of constraint - ENABLED or DISABLED
DEFERRABLE VARCHAR2(14) Y Is the constraint deferrable - DEFERRABLE or NOT DEFERRABLE
DEFERRED VARCHAR2(9) Y Is the constraint deferred by default - DEFERRED or IMMEDIATE
VALIDATED VARCHAR2(13) Y Was this constraint system validated? - VALIDATED or NOT VALIDATED
GENERATED VARCHAR2(14) Y Was the constraint name system generated? - GENERATED NAME or USER NAME
BAD VARCHAR2(3) Y Creating this constraint should give ORA-02436. Rewrite it before 2000 AD.
RELY VARCHAR2(4) Y If set, this flag will be used in optimizer
LAST_CHANGE DATE Y The date when this column was last enabled or disabled
INDEX_OWNER VARCHAR2(30) Y The owner of the index used by this constraint
INDEX_NAME VARCHAR2(30) Y The index used by this constraint
INVALID VARCHAR2(7) Y
VIEW_RELATED VARCHAR2(14) Y
SQL>
SQL> SELECT constraint_name, table_name
2 FROM all_constraints@UAT md
3 WHERE owner = 'DBUSER'
4 AND constraint_type = 'R'
5 AND NOT EXISTS
6 (SELECT 1
7 FROM all_constraints db
8 WHERE md.owner = db.owner
9 AND md.constraint_type = db.constraint_type
10 AND md.constraint_name = db.constraint_name);
CONSTRAINT_NAME TABLE_NAME
------------------------------ ------------------------------
.... .....
.... .....
.... .....
26 rows selected
SQL>
No comments:
Post a Comment