The relationships and references between database objects are known as dependencies. This topic describes how to create and view dependencies between database objects in OceanBase Database's Oracle mode.
Create and view dependencies between database objects
Some types of 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, when you migrate data between databases, you must recreate schema objects in the destination database. If the schema objects to be recreated are included in a view, you must first recreate the objects on which the view depends. Otherwise, an error message is returned when you recreate the view, indicating that the dependent objects 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 a view named view2 that references the tbl1 table and the view1 view.
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: Query the USER_DEPENDENCIES view for dependencies between database objects.
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 dependencies between database objects are returned in the query result of the view. For example, the view1 view references the tbl2 table, and the view2 view references the tbl1 table and the view1 view.
Considerations
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