Re: Table acess and growth

[Quoted] "Ana C. Dent" <anacedent_at_hotmail.com> wrote in message news:MbDsa.601$MJ5.320_at_fed1read03...
> Take a look at V$TRANSACTION where execution counts are stored.

No they aren't. From our finance system
Connected to:
[Quoted] Oracle8i Release 8.1.7.3.0 - Production
JServer Release 8.1.7.3.0 - Production

SQL> desc v$transaction;

 Name                                      Null?    Type
 ----------------------------------------- -------- -------------------
 ADDR                                               RAW(4)
 XIDUSN                                             NUMBER
 XIDSLOT                                            NUMBER
 XIDSQN                                             NUMBER
 UBAFIL                                             NUMBER
 UBABLK                                             NUMBER
 UBASQN                                             NUMBER
 UBAREC                                             NUMBER
 STATUS                                             VARCHAR2(16)
 START_TIME                                         VARCHAR2(20)
 START_SCNB                                         NUMBER
 START_SCNW                                         NUMBER
 START_UEXT                                         NUMBER
 START_UBAFIL                                       NUMBER
 START_UBABLK                                       NUMBER
 START_UBASQN                                       NUMBER
 START_UBAREC                                       NUMBER
 SES_ADDR                                           RAW(4)
 FLAG                                               NUMBER
 SPACE                                              VARCHAR2(3)
 RECURSIVE                                          VARCHAR2(3)
 NOUNDO                                             VARCHAR2(3)
 PTX                                                VARCHAR2(3)
 PRV_XIDUSN                                         NUMBER
 PRV_XIDSLT                                         NUMBER
 PRV_XIDSQN                                         NUMBER
 PTX_XIDUSN                                         NUMBER
 PTX_XIDSLT                                         NUMBER
 PTX_XIDSQN                                         NUMBER
 DSCN-B                                             NUMBER
 DSCN-W                                             NUMBER
 USED_UBLK                                          NUMBER
 USED_UREC                                          NUMBER
 LOG_IO                                             NUMBER
 PHY_IO                                             NUMBER
 CR_GET                                             NUMBER
 CR_CHANGE                                          NUMBER

SQL> select count(*)
  2 from v$transaction;

  COUNT(*)

         0

we may be public sector but we don't in fact do nothing. V$transaction shows details for currently running transactions. For the object sizing questions as Daniel says you can collect data periodically, for example one report that people here like is the one below which gives data about number of records in a table. You could equally collect size or extent information in a similar way.

DECLARE /*

Row count procedure

Niall Litchfield 12/08/2002

modify v_limit to change the cut off point

below which row counts are not given.

*/

v_rowcount integer;

v_limit integer := -1;

BEGIN For v in (select table_name from user_tables order by table_name) loop

EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM '||v.table_name into v_rowcount;

if v_rowcount > v_limit then

DBMS_OUTPUT.PUT_LINE(rpad(v.table_name,55,' ')||to_char(v_rowcount,'99999999')||' record(s)');

end if;

end loop;

END; /

> If you have a decent amount of available disk space,
> you can enable SQL TRACE at the instance level & collect trace
> files for a day. Then with tkprof, you can get EXPLAIN PLANS
> for every SQL statement.

[Quoted] A better approach for collecting information about sql statements would generally be STATSPACK but I think that the OP is limited to UTLBSTAT/UTLESTAT on 806.

-- 
Niall Litchfield
Oracle DBA
Audit Commission UK