Hi
Oracle 12c
Question about why all_tables reports tables when missing from remote schema
SHO USER
SHO PARAMETER DB_NAME
USER is "HR"
NAME TYPE VALUE
------- ------ ------
db_name string dbfour
SELECT * FROM USER_DB_LINKS;
DB_LINK | USERNAME | PASSWORD | HOST | CREATED | HIDDEN |
HRDBFIVE | HR | | localhost:1521/dbfive | 10-Oct-17 | NO |
SCOTTDBFIVE | SCOTT | | localhost:1521/dbfive | 10-Oct-17 | NO |
SELECT OWNER,TABLE_NAME FROM ALL_TABLES WHERE OWNER IN ('SCOTT','HR') ORDER BY OWNER;
OWNER | TABLE_NAME |
HR | REGIONS |
HR | COUNTRIES |
HR | LOCATIONS |
HR | DEPARTMENTS |
HR | JOBS |
HR | EMPLOYEES |
HR | JOB_HISTORY |
SCOTT | DEPT |
SCOTT | EMP |
SCOTT | BONUS |
SCOTT | SALGRADE |
select dbms_metadata.get_ddl('TABLE',table_name,owner) from all_tables@SCOTTDBFIVE where owner in ('SCOTT') and table_name in ('EMP');
DBMS_METADATA.GET_DDL('TABLE',TABLE_NAME,OWNER)
"
CREATE TABLE "SCOTT"."EMP"
( "EMPNO" NUMBER(4,0),
"ENAME" VARCHAR2(10),
"JOB" VARCHAR2(9),
"MGR" NUMBER(4,0),
"HIREDATE" DATE,
"SAL" NUMBER(7,2),
"COMM" NUMBER(7,2),
"DEPTNO" NUMBER(2,0),
CONSTRAINT "PK_EMP" PRIMARY KEY ("EMPNO")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS" ENABLE,
CONSTRAINT "FK_DEPTNO" FOREIGN KEY ("DEPTNO")
REFERENCES "SCOTT"."DEPT" ("DEPTNO") ENABLE
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS" "
SHO USER
SHO PARAMETER DB_NAME
USER is "SCOTT"
NAME TYPE VALUE
------- ------ ------
db_name string dbfive
SELECT TABLE_NAME FROM USER_TABLES
TABLE_NAME |
DEPT |
EMP |
BONUS |
SALGRADE |
CREATE TABLE ABC AS SELECT * FROM EMP
Table ABC created.
SHO USER
SHO PARAMETER DB_NAME
USER is "HR"
NAME TYPE VALUE
------- ------ ------
db_name string dbfour
select dbms_metadata.get_ddl('TABLE',table_name,owner) from all_tables@SCOTTDBFIVE where owner in ('SCOTT') and table_name in ('ABC');
ORA-31603: object "ABC" of type TABLE not found in schema "SCOTT"
ORA-06512: at "SYS.DBMS_METADATA", line 6478
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 105
ORA-06512: at "SYS.DBMS_METADATA", line 2421
ORA-06512: at "SYS.DBMS_METADATA", line 2660
ORA-06512: at "SYS.DBMS_METADATA", line 3423
ORA-06512: at "SYS.DBMS_METADATA", line 4760
ORA-06512: at "SYS.DBMS_METADATA", line 5088
ORA-06512: at "SYS.DBMS_METADATA", line 6449
ORA-06512: at "SYS.DBMS_METADATA", line 9202
ORA-06512: at line 1
- 00000 - "object \"%s\" of type %s not found in schema \"%s\""
*Cause: The specified object was not found in the database.
*Action: Correct the object specification and try the call again.
Thank you