|
Managing Objects Dependency
Hands-On
introduction
In this
Hands-On, you will view some of the Oracle
directory views and experience the Oracle
object dependencies (timestamp).
Go to
�MS-DOS.�
Login to �sqlplus� as "oracle/learning�
Use the
"column" command to change the output format
length of the Object name to 30 characters.
SQL> COL object_name FORMAT a30
Object
dependency and use of USER_OBJECTS table
An object may
be created based on the existence of another
object or objects. The purity of the created
object depends on the status of the other
objects that have already been created. If
any of those objects changed or deleted, the
new object can not perform its task
completely. Therefore, Oracle will change
its status to an INVALID mode.
Query all the
object names, their type and status from
user_objects where their object names start
with �D-E-P-T.�
SQL> SELECT
object_name, object_type, status
FROM
user_objects
WHERE
object_name LIKE �DEPT%�;
Describe the
dept table.
SQL> DESC dept
Then change
the �l-o-c� column size from 13 to 14
characters. Notice that when you change a
column attribute in a table, its modify date
will be recorded. It is called a timestamp.
Now any objects that were using this table
are going to have an invalid status. Since
the timestamp shows a date that is after
creation of those objects.
SQL> ALTER
TABLE dept
MODIFY (loc
VARCHAR2(14));
Describe the
dept table again.
SQL> DESC dept
Notice that
the location column size was changed.
Query all the
object names, their type and status from
user_objects where their object names start
with �D-E-P-T.�
SQL> SELECT
object_name, object_type, status
FROM
user_objects
WHERE
object_name LIKE �DEPT%�;
Notice that
the dept_name function became invalid. The
reason was that the dept_name function was
dependent on the dept table.
Compile a
function
You compile
the function to change its modification
timestamp to current one.
Compile the
dept_name function.
SQL> ALTER FUNCATION dept_name COMPILE;
Then query the
object names, their type and status from
user_objects where their object names start
with �D-E-P-T.�
SQL> SELECT
object_name, object_type, status
FROM
user_objects
WHERE
object_name LIKE �DEPT%�;
Now, both are
valid.
That was an
example of object dependencies.
Questions:
Q: What is an
object dependency in the Oracle database?
Q: What is a
timestamp?
Q: How do you
query all the objects that was create by you
(your schema)?
Q: How do you
change a datatype of a column in a table?
Q: How do you
compile a PL/SQL function? |
