title: how big is that table in the window? link: https://onemoretech.wordpress.com/2009/05/07/how-big-is-that-table-in-the-window/ author: lembobro description: post_id: 324 created: 2009/05/07 19:18:21 created_gmt: 2009/05/07 19:18:21 comment_status: open post_name: how-big-is-that-table-in-the-window status: publish post_type: post

how big is that table in the window?

We kept having this recurring problem in one of our databases. The archive filesystem was filling up. Since this was an Oracle AS infrastructure db, it was pretty obviously the Identity Management team’s fault. After all, we own OID (Oracle Internet Directory).

Time to learn something about the database behind OID.

(Note: Oracle devotes exactly 0 time to infrastructure database administration in their Identity Management classes. Amazing when you consider their products are so database centric)

First, we needed to see if any of our tables was taking up an unusual amount of space.

There are lots of ways to do this, including writing a script to go through and query every table. Instead we just went into Grid Control and eyeballed the tablespaces.

OLTS_DEFAULT was at, like, 12 Gb.

Here’s the sql, which can be invoked as ODS schema user (the owner of the tables that backend Oracle Internet Directory, whose password is the same as the root “cn=orcladmin”):

(to connect as ODS, do sqlplus ODS/odspw@infradb)

SQL> select tablespace_name,user_bytes from 2 dba_data_files;

OLTS_DEFAULT jumps right out at 1.2110E+10 bytes, or 12.11 Gb.

So the obvious next question is: what tables are in the OLTS_DEFAULT tablespace?

Log onto the infra db using sqlplus as the SYS user and do this query:

`

SQL> select table_name from dba_tables
  2  where tablespace_name = 'OLTS_DEFAULT';

`

The list includes ODS_CHG_LOG, which is all I needed to see.

How big is ODS_CHG_LOG?

Go into the infra db as ODS (the OID schema user) and run:

`

SQL> select sum(BYTES/1024/1024) as TOTAL_MB
   2 from user_segments where
   3 segment_name = 'ODS_CHG_LOG';
	
  TOTAL_MB
--------------
   9446

`

Oh, that is no fun at all.

Copyright 2004-2019 Phil Lembo