Script To Quickly Find Object Info

Here is a script I’ve used to quickly hunt down objects and information about them.

Very useful to find things like: exact spellings, other objects with a similar spelling, or owned by a different user, synonyms, etc.

 

/*

foinfo.sql:  find object info

Script to find objects and information about them.
By Rodger Lepinsky

*/

accept ls_object_name prompt "Enter the object name to find info about:  " ;

select     object_type  ||  '   '  ||  owner || '.' || object_name   ||
               '    ' || object_id 
          as object_and_object_id
/*  ,  CREATED
  ,   TIMESTAMP
  ,   LAST_DDL_TIME    */
from all_objects    /* By Rodger Lepinsky */
/* where upper(object_name) = trim(upper('&ls_object_name'))  */
where upper(object_name) like trim(upper('%&ls_object_name%'))      
/* and object_type IN ('TABLE', 'VIEW')  */ 
order by object_type, owner, object_name
/

In some environments, the different timestamps have been useful. Ie. Has the object just been recompiled?

You can vary the script as you see fit. Change it to find only with an exact spelling. Restrict it to objects types such as a TABLE or VIEW. Check the timestamps. Etc.

Enjoy!

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.

 

Finding All Generations Of An Object’s Children

Say you have an object, such as a table. What are all objects are dependent on it? That is, all generations. Children, Grandchildren. Great-Grandchildren. Great-Great-Grandchildren. Etc. ??

Four Generations

If you drop that object, all generations of its children will become invalid.

The classic case is a table. There are generations of objects that are built referencing them.

Table
  View
    Package
      Function

Table
  Trigger

etc.

This script will be useful for:
– Determining the Order Of Operations when building objects.
– Determining Multiple dependencies
– Assessing the impact of changing a table structure, or dropping a table.

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

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
 

Oracle Apps – Analysis of the Files and Filesystems

Continuing my analysis of Oracle Apps R12.  This time, the actual files on the server.  There are over 1/2 million files in APPL_TOP and COMMON_TOP!  Wow!

—————-

On my installation of Apps R12, there is:

APPL_TOP
/oapps/applmgr/VIS/apps/apps_st/appl

COMMON_TOP
/oapps/applmgr/VIS/apps/apps_st/comn/

ORACLE_HOME
/oapps/oracle/VIS/db/tech_st/11.1.0

—————-

The top parts of the directory structure:

find /oapps  -maxdepth 5 -type d

Oracle Applications R12: More Schema Analysis

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!

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?

Script to List Tables With No Foreign Keys

More on Database Design Mistakes to Avoid

Related to referential integrity, in addition to systems that do not have primary keys, there are database “designs” where there were no foreign keys!  Much like a Cobol system.

Here is an Oracle script to find the tables that don’t have foreign keys.  Tables that are neither a parent table, or a child table.

Oracle Script To Find Tables With No Primary Key

Primary keys are so important in databases.  I keep trying to write an effective post about why they are so important.  But I don’t feel I’m doing the subject justice.  So let me start with a related, but shorter post.

At a number of shops, I’ve come across so many tables that did not have a primary key.  Some of these were very important tables in the system!   Bad, bad, bad.

After discovering so many tables that no primary key, at so many shops, I finally wrote an Oracle script to list all the tables that did not have a PK.