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.

 

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.

Big Advances In Oracle Data Storage

Recently I went to see an Oracle presentation on storage.  Since they have bought Sun, they have been doing some really cool things with data storage.

One concept is to use flash memory, rather than hard disks.  This is something that I’ve thought of since 2001, when I was attempting to insert the billion rows a day into Oracle.   It’s good to see that the idea is finally becoming mainstream.  Perhaps they should have hired me years ago.  🙂

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.