Notes on DEPTREE

After writing my own scripts to find either all parents or children of an object, I took a look at Oracle’s solution to find dependencies:

$ORACLE_HOME/rdbms/admin/utldtree.sql

It was actually written about 20 years ago, in 1991:
Rem rkooi 10/19/91 – Creation

Running the script, creates a number of objects:

OBJECT_AND_OBJECT_ID
----------------------------------------------------------------------------------------------------
PROCEDURE   SYS.DEPTREE_FILL    3304377
SEQUENCE   SYS.DEPTREE_SEQ    3304375
TABLE   SYS.DEPTREE_TEMPTAB    3304376
VIEW   SYS.DEPTREE    3304378
VIEW   SYS.IDEPTREE    3304379

7 rows selected.

 

The Parents And The Order Of Operations

When you build objects in a new environment, you need to build them in the right order of operations.

Until you have all the objects in place, you can’t create a procedure that references them all. And if those objects require more parent objects, they must be created first too. For example:

procedure reads
    view which reads a
       table which is composed of a
          type

—-

Recently I wrote in my other post about the parent and child dependencies. They give a lot of great information. But they only go one level in either direction. As you know, there can be many levels of objects.

What is the order of operations to build them? I’ve written some complex scripts here to find all the successive parents of an object.

Very Useful Scripts To Find Object Dependencies

For DBAs and Developers.

When you are developing schemas, a lot of objects get created. It can be really confusing to keep track of them all. Especially when you now need to create a few dozen in a new environment.

What is even worse, is when you start to maintain databases and schemas that you didn’t build yourself. If you drop a table or unsuccesfully modify a view, all their children will now become invalid. Suddenly child procedures and packages that reference them will not work.

Here you can find some sqlplus scripts that I have used for years to see the dependencies in schema objects. Run the script, and enter in the object you have questions on. Very fast and effective.

They have been extremely useful to determine things like:
– The order of operations to create objects. Parents first. Then children.
– The impact of dropping a view, package, procedure.
– Causes of object invalidation such as another invalid object.
– All the tables that a package is accessing directly. For those multiple 18 table joins.
– Whether a package, function or procedure calls other prodecures.
– Obscure issues, such as references to variables in packages.

————————————————————
————————————————————
————————————————————

To Find Just The Parents of an Oracle Database Object:

Parent objects must be created first, before creating the child object. If the parent object is dropped, all subsequent child objects will become invalid.

This info is very important in new environments, such as moving from Dev to Test. Or Test to Prod.

Script To List Objects That Won’t Extend

Here is an Oracle DBA script to list objects that won’t extend due to no more room in the tablespace.

If the tablespace is set to autoextend (via the datafile clause(s)) this will not be a big issue. But if the datafiles are not set to autoextend, processes can stop until the DBA intervenes.

Use this script proactively.

/*
This script will list objects that won't be able to extend
due to insufficient space in the tablespace
*/

Select TableSpace_Name
, Owner
, Segment_Name
, Segment_Type
, To_Char((Next_Extent / 1024 / 1024), '999,999') || 'M' Nxt_Ext
From Sys.DBA_Segments A
Where Not Exists
(Select 'x'
From Sys.DBA_Free_Space B
Where A.TableSpace_Name = B.TableSpace_Name
And B.Bytes >= (A.Next_Extent * (1 + (A.Pct_Increase / 100)))
)
Order By TableSpace_Name, Segment_Name

Output will look similar to:

TABLESPACE_NAME OWNER
------------------------------ ------------------------------
SEGMENT_NAME SEGMENT_TYPE
--------------------------------------------------------------------------------- ------------------
NXT_EXT
---------

XDB XDB
XDB$CHOICE_MODEL TABLE
1M

XDB XDB
XDB$COLUMN_INFO TABLE
1M

DBA Script: How Full Are The Tablespaces?

Here is a useful script to show how full the tablespaces are. I’ve used it many times!  It’s ordered by Percent full, but you can order it any way you like.

/*
Script to show how full the tablespaces are:
By Rodger Lepinsky
ltsu.sql  for:  List TableSpace Use. 

*/

SELECT ts.tablespace_name,
to_char ( ( nvl( ts.total_bytes , 0 ) - nvl(used_bytes, 0) ) / 1024 / 1024 , '999,999' )
as free_megs,
to_char ( nvl(used_bytes , 0) / 1024 / 1024 , '999,999' )
as used_megs,
to_char ( nvl(ts.total_bytes, 0) / 1024 / 1024 , '999,999' )
as total_megs ,
to_char ( (( nvl(used_bytes, 0) / ( nvl(ts.total_bytes,0) )) * 100) , '999.99')
as Percent_Full
FROM
( select TABLESPACE_NAME,
sum( nvl(BYTES,0) ) as total_bytes
from dba_data_files
group by TABLESPACE_NAME
) ts,
( SELECT tablespace_name,
nvl(SUM(bytes), 0 ) used_bytes
FROM dba_extents
GROUP BY tablespace_name
) used_space
WHERE ts.tablespace_name = used_space.tablespace_name (+)
order by 5

Output will be similar to:

TABLESPACE_NAME                FREE_MEG USED_MEG TOTAL_ME PERCENT
------------------------------ -------- -------- -------- -------
TS_DP                                50        0       50     .00
TS_SALES_DATA_ENGINE_X               50        0       50     .00
DW_REFERENCE_TBS                    134        0      134     .00
DEMANTRA                          5,400        0    5,400     .00
DW_REFERENCE_IDX                    100        0      100     .00
BIA_RTL                           1,000        0    1,000     .00
DW_LOOKUP_TBS                       100        0      100     .00
DW_AGGREGATE_IDX                    100        0      100     .00
DW_MVLOG_TBS                        100        0      100     .00
OLAP_BAAD                         1,500        0    1,500     .00
TS_SIM_X                             50        0       50     .00
OWAPUB                               10        0       10     .00
...

B2B_RT                                4       38       42   90.33
APPS_TS_MEDIA                       497    4,715    5,211   90.47
RBS_MIG                               4       39       43   90.70
UDDISYS_TS                            2       19       21   90.77
CWMLITE                               2       21       23   91.85
ORABPEL                               1       11       12   95.31

69 rows selected.

Note:  this script can take a long time, if you have a slow server, many tablespaces, objects, and of course, extents, which the objects are made out of.

Oracle Applications R12 Schema Analysis

Oracle Apps R12 Schema Analysis

Continuing on my series on analysis, here is some of the initial analysis I did after installing Oracle applications R12.  Much of the analysis involves queries to the Oracle data dictionary under the covers.  This analysis is on the sample “database”, VIS, which is actually composed of many schemas.

I also did a fair bit of analysis from a DBA standpoint: tablespaces, logs, processes, DBMS_JOBS, etc.  And other analysis on the unix and middle tier side.  Tuning.  I’ll try to keep it all organized in different posts.

When I’ve started a new position, these are the types of queries I might run on the schemas to get a feel for the systems.  Just how was it made? Does it use PK, triggers, or external code to enforce data integrity?  What are the most important tables?  What are potential performance issues? Any issues?  And so on.

Many of these queries you won’t find the GUIs like SQL Developer, or Toad.  You have to write them yourself.  It pays big dividends to know the data dictionary!

(Sorry for the odd font. WordPress has not made changing the font for select sets of text easy or obvious. If anyone knows the secret, on how to change fonts in WordPress A LA Wordpad, please let me know. )

------------------------------------------------
------------------------------------------------

How many tables are there?