Dependencies between database objects describe the references between database objects. This topic describes how to create and view dependencies between database objects in OceanBase Database in Oracle mode.
Create and view dependencies between database objects
Some schema objects can reference other objects in their definitions. For example, a view can be defined as a query that references a table or another view. An SQL statement in the procedure body of a stored procedure can reference other database objects. If object B is referenced in the definition of object A, A is a dependent object of B, and B is a referenced object of A.
Dependencies between database objects are useful in some scenarios. For example, to migrate data between databases, you must rebuild a schema object in the destination database. If the schema object to be rebuilt is a view, you must rebuild other objects on which the view depends before you rebuild the view. Otherwise, an error message is returned. This means that the objects on which the view depends do not exist. You can easily rebuild database objects if dependencies between database objects are known.
You can view dependencies between database objects in the USER_DEPENDENCIES, ALL_DEPENDENCIES, and DBA_DEPENDENCIES views.
Example 1: Create view2 that references tbl1 and view1.
obclient> CREATE TABLE tbl1(col1 INT, col2 INT);
Query OK, 0 rows affected
obclient> CREATE TABLE tbl2 (col1 INT, col2 INT);
Query OK, 0 rows affected
obclient> CREATE VIEW view1 AS SELECT * FROM tbl2;
Query OK, 0 rows affected
obclient> CREATE VIEW view2 AS SELECT t.col1 AS col1, t.col2 AS col2 FROM tbl1 t, view1 v WHERE
t.col1 = v.col2;
Query OK, 0 rows affected
Example 2: View dependencies between database objects in the USER_DEPENDENCIES view.
obclient> SELECT * FROM USER_DEPENDENCIES;
+------+------+------------------+-----------------+-----------------+----------------------+------------------+-----------------+
| NAME | TYPE | REFERENCED_OWNER | REFERENCED_NAME | REFERENCED_TYPE | REFERENCED_LINK_NAME | SCHEMAID | DEPENDENCY_TYPE |
+------+------+------------------+-----------------+-----------------+----------------------+------------------+-----------------+
| VIEW1 | VIEW | SYS | TBL2 | TABLE | NULL | 1100611139403782 | HARD |
| VIEW2 | VIEW | SYS | TBL1 | TABLE | NULL | 1100611139403782 | HARD |
| VIEW2 | VIEW | SYS | VIEW1 | VIEW | NULL | 1100611139403782 | HARD |
+------+------+------------------+-----------------+-----------------+----------------------+------------------+-----------------+
3 rows in set
In the preceding example, the query results show the dependencies between database objects. For example, view1 references tbl2, and view2 references tbl1 and view1.
Usage notes
Dependencies between database objects are not maintained in the database in real time and therefore not absolutely accurate. When a referenced object is deleted or rebuilt, the corresponding dependency is updated only in a related Data Manipulation Language (DML) operation or in the next query for the object that depends on the referenced object.
In the following example, view1 that references tbl1 is created. After tbl1 is deleted, their dependency can still be found in the USER_DEPENDENCIES view. Then, a DML operation is performed on view1, and their dependency is deleted from the USER_DEPENDENCIES view.
obclient>CREATE TABLE tbl1 (col1 INT, col2 INT);
Query OK, 0 rows affected
obclient>CREATE VIEW view1 AS SELECT * FROM tbl1;
Query OK, 0 rows affected
obclient>SELECT * FROM USER_DEPENDENCIES;
+------+------+------------------+-----------------+-----------------+----------------------+------------------+-----------------+
| NAME | TYPE | REFERENCED_OWNER | REFERENCED_NAME | REFERENCED_TYPE | REFERENCED_LINK_NAME | SCHEMAID | DEPENDENCY_TYPE |
+------+------+------------------+-----------------+-----------------+----------------------+------------------+-----------------+
| VIEW1 | VIEW | SYS | TBL1 | TABLE | NULL | 1100611139403782 | HARD |
+------+------+------------------+-----------------+-----------------+----------------------+------------------+-----------------+
1 row in set
obclient>DROP TABLE tbl1;
Query OK, 0 rows affected
obclient>SELECT * FROM USER_DEPENDENCIES;
+------+------+------------------+------------------------------+-----------------+----------------------+------------------+-----------------+
| NAME | TYPE | REFERENCED_OWNER | REFERENCED_NAME | REFERENCED_TYPE | REFERENCED_LINK_NAME | SCHEMAID | DEPENDENCY_TYPE |
+------+------+------------------+------------------------------+-----------------+----------------------+------------------+-----------------+
| VIEW1 | VIEW | __recyclebin | RECYCLE_$_1_1635668004963688 | TABLE | NULL | 1100611139403782 | HARD |
+------+------+------------------+------------------------------+-----------------+----------------------+------------------+-----------------+
1 row in set
obclient>SELECT * FROM view1;
ORA-04063: view 'SYS.view1' has errors
obclient>SELECT * FROM USER_DEPENDENCIES;
Empty set