Notes on DEPTREE

January 5, 2012

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.

 Read the rest of this entry »

The Parents And The Order Of Operations

December 29, 2011

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.

Read the rest of this entry »


Script To List Objects That Won’t Extend

April 19, 2011

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?

April 19, 2011

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 Apps R12: DBA Analysis Of TableSpaces, Log, And Control Files

April 19, 2011

Oracle Apps R12: More DBA Analysis Of TableSpaces, Log, And Control Files

Lots of tablespaces! 

select TABLESPACE_NAME
from dba_tablespaces
order by TABLESPACE_NAME

TABLESPACE_NAME
------------------------------
APPS_CALCLIP
APPS_OMO
APPS_TS_ARCHIVE
 Read the rest of this entry »

Oracle Applications R12: More Schema Analysis

December 18, 2010

Oracle Applications R12: More Schema Analysis

Some more analysis of the schemas and objects in Oracle Applications R12 sample database, VIS.

Lots of objects, triggers, and packages.

And over 57 million lines of source code in the APPS schema alone!

Read the rest of this entry »


Poor Or No Error Trapping

October 19, 2010

More Architecture Mistakes To Avoid

One thing that should be blatantly obvious to anyone who has ever had to fix some code on the weekend, or in the middle of the night, is the importance of excellent error trapping and logging routines.

When you need to fix something, now, you don’t want to be reverse engineering the code or the whole system.  You want to know what happened, and what to do.  Now.

You need what I call, instantly digestible error messages.  I don’t want to hunt it. I don’t want to kill it.  I don’t want to clean it.  I don’t want to cook it.  I just want to eat it.  I want both the cause, and the solution, in the same message.

The error trapping routine must be totally reliable.   Let me list some strange things that I’ve seen in production.

Complex and Useless:
Read the rest of this entry »


%d bloggers like this: