Hello gurus,
I have a problem with directoryies, db links and synonyms that are created as "invalid".
If I try to create a table or a view, it is OK, I have an error message.
SQL> create table ZZT as select * from testmmmmmm;
create table ZZT as select * from testmmmmmm
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> create view ZZV as select * from zz02;
create view ZZV as select * from zz02
*
ERROR at line 1:
ORA-00942: table or view does not exist
But if I create synonym, directory or db link with false information, I have no error message.
SQL> create synonym ZZsyn for dksjhfsdj;
Synonym created.
SQL> create directory ZZDIR as '/vgtf65465464565';
Directory created.
SQL> CREATE PUBLIC DATABASE LINK ZZDBlink USING 'sdjfhksdjfhkjsdfh';
Database link created.
The status in DBA_OBJECTS in VALID, not INVALID.
SQL> select owner, object_name, object_type, status from dba_objects where TO_CHAR(CREATED, 'DD-MM-YYYY') = '22-08-2020' order by object_name;
OWNER OBJECT_NAME OBJECT_TYPE STATUS
-------------------- -------------------- ----------------
PUBLIC ZZDBLINK DATABASE LINK VALID
SYS ZZDIR DIRECTORY VALID
HR ZZSYN SYNONYM VALID
If I try to use these objetcs : I have an error message, this is normal.
SQL> create table ZZT as select * from testmmmmmm@zzdblink;
create table ZZT as select * from testmmmmmm@zzdblink
*
ERROR at line 1:
ORA-12154: TNS:could not resolve the connect identifier specified
SQL> select * from ZZSYN;
select * from ZZSYN
*
ERROR at line 1:
ORA-00980: synonym translation is no longer valid
SQL> ! expdp SYSTEM SCHEMAS=HR DIRECTORY=ZZDIR DUMPFILE=EXP_DATAPUMP.dmp LOGFILE=EXP_DATAPUMP.log
Export: Release 12.2.0.1.0 - Production on Sat Aug 22 06:10:32 2020
Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
ORA-39002: invalid operation
ORA-39070: Unable to open the log file.
ORA-29283: invalid file operation
ORA-29283: invalid file operation
What I don't understand is taht the status of these objects remains VALID.
SQL> select owner, object_name, object_type, status from dba_objects where TO_CHAR(CREATED, 'DD-MM-YYYY') = '22-08-2020' order by object_name;
OWNER OBJECT_NAME OBJECT_TYPE STATUS
-------------------- -------------------- ----------------------- --------------------
PUBLIC ZZDBLINK DATABASE LINK VALID
SYS ZZDIR DIRECTORY VALID
HR ZZSYN SYNONYM VALID
Objects are not INVALID.
SQL> select OBJECT_NAME from DBA_INVALID_OBJECTS where TO_CHAR(CREATED, 'DD-MM-YYYY') = '22-08-2020' order by object_name;
no rows selected
If I complile the whole schema, what happens?
SQL> EXEC DBMS_UTILITY.COMPILE_SCHEMA(schema => 'HR', compile_all => TRUE);
PL/SQL procedure successfully completed.
No change for my objects...
SQL> select owner, object_name, object_type, status from dba_objects where TO_CHAR(CREATED, 'DD-MM-YYYY') = '22-08-2020' order by object_name;
OWNER OBJECT_NAME OBJECT_TYPE STATUS
-------------------- ---------------------------------------- ----------------------- --------------------
SYS SYS_IL0000014347C00016$$ INDEX PARTITION VALID
SYS SYS_LOB0000014347C00016$$ LOB PARTITION VALID
SYS UTL_RECOMP_SEQ SEQUENCE VALID
SYS UTL_RECOMP_SORT_IDX1 INDEX VALID
SYS WRI$_OPTSTAT_HISTGRM_HISTORY TABLE PARTITION VALID
SYS WRI$_OPTSTAT_HISTHEAD_HISTORY TABLE PARTITION VALID
PUBLIC ZZDBLINK DATABASE LINK VALID
SYS ZZDIR DIRECTORY VALID
HR ZZSYN SYNONYM VALID
In the documentation of ALL_OBJECTS, I read only :
STATUS VARCHAR2(7)
Status of the object:
VALID
INVALID
N/A
So, can you tell me what happens with that column STATUS for these type of objects?
And, more important, how can I identify invalid craeted objects in my database?
Best regards,
David D. from Paris
Directories and database links both reference external objects. At any time, someone could come along and:
- Delete/rename the directory or change its permissions in the OS
- Change the TNS details you're using, shutdown the remote database/listener, ...
Database links are particularly troublesome because you have the network to consider too. Temporary network issues may make the remote database inaccessible for a few seconds.
This gives no practical way to ensure these are valid in the sense that the remote thing exists. The database would need to continually poll to check the directory/DB link is there. This is a bad idea in general and still imperfect.
It's more arguable that synonyms should validate when pointing to local objects. But they can also reference remote objects, e.g. :
create synonym s
for some_tab@remote_db;
Checking this is valid has similar problems to the above.
Note that synonyms can be invalidated when you drop/alter (local) objects they reference. Compiling will revalidate them again, even if you've dropped the thing it points to:
create table t (
c1 int
);
create synonym s
for t;
drop table t;
select status from user_objects
where object_name = 'S';
STATUS
INVALID
alter synonym s compile;
select status from user_objects
where object_name = 'S';
STATUS
VALID
Ultimately a synonym is just an alias - another name for a thing.
So to know if synonyms, directories or database links point to real objects, you need to access them to verify this.